mysql基本操作

一、 SQL分类

·数据库：database

·表：table，行：row 列：column

·索引：index

·视图：view

·存储过程：procedure

·存储函数：function

·触发器：trigger

·事件调度器：event scheduler，任务计划

·用户：user

·权限：privilege

二、SQL 语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写

SQL语句可单行或多行书写，默认以 " ; " 结尾

关键词不能跨多行或简写

用空格和TAB 缩进来提高语句的可读性

子句通常位于独立行，便于编辑，提高可读性

数据库对象和命名

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,tabble select show databases

 SQL语句分类

• DDL: Data Defination Language 数据定义语言

创建，删除，更改

• DML: Data Manipulation Language 数据操纵语言

插入，删除，更新

软件开发：CRUD

• DQL：Data Query Language 数据查询语言

选择

• DCL：Data Control Language 数据控制语言

授予，撤销

• TCL：Transaction Control Language 事务控制语言

提交，回滚，保存点

三、查看类基本命令

1、 查看帮助信息

2、查看支持的字符集、查看表，使用表

 

四、管理数据库

 

1、创建数据库

 

 

2、修改数据库

3、删除数据库

 

4、查看数据库列表

 

五、 数据类型

MySQL支持多种内置数据类型

数值类型

日期/时间类型

字符串(字符)类型

数据类型参考链接:

MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types

选择正确的数据类型对于获得高性能至关重要，三大原则：

1.更小的通常更好，尽量使用可正确存储数据的最小数据类型

2.简单就好，简单数据类型的操作通常需要更少的CPU周期

3.尽量避免NULL，包含为NULL的列，对MySQL更难优化

1、整数型

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bigint(m) 8个字节 范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

2、浮点型(float和double)，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以

实际为准，即6位

3、定点数

在数据库中存放的是精确值,存为十进制 decimal(5.2) 100.01 - 999.99

格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个

字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用

8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时

4、字符串(char,varchar,text)

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

char和varchar的比较:

参考： MySQL :: MySQL 8.0 Reference Manual :: 11.3.2 The CHAR and VARCHAR Types

附：常用的数据类型

int：整型                                                用于定义整数类型的数据

float：单精度浮点4字节32位                准确表示到小数点后六位                

double：双精度浮点8字节64位

char：固定长度的字符类型                用于定义字符类型数据。

varchar：可变长度的字符类型                

text：文本

image：图片

decimal(5,2)：5个有效长度数字，小数点后面有2位                指定长度数组

注：Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度

注：如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

六、实际操作

 

1、SQL语句

用于维护管理数据库，包括对数据查询，数据更新、访问控制、对象管理功能。

 

 

增删改“查”

 

SQL语言分类：

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等。（create ）

DML：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据(insert drop delete update )

DQL：数据查询语言，用于从数据表中查找符合条件的数据记录(select )

DCL：数据控制语言，用于设置或者更改数据库用户或角色权限(grant )

（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）

 

1、查看数据库列表

SHOW DATABASES;

2、删除数据库

 

  

 

3、创建数据库

 

4、查看数据库中包含的表

use 数据库名

use mysql;

选择数据库，切换库类似于cd

show tables;

show tables in mysql;

 

 

5、查看表结构(字段)

格式：describe [数据库名.]表名；

desc user;

desc servers;#查看表结构

desc mysql.servers;#不想先使用use进入数据库查看表可以使用上面的

6、创建及删除数据库和表

（1）创建新的数据库

create database(数据库名)；

（2）创建新的表

create table 表名 （字段1 数据类型，字段2 数据类型[,...] [,PRIMARY KEY (主键名)]）；

主键一般选择代表唯一性的字段不允许为空值（null）,且一个表只能有一个主键

 

 

（3）管理表中的数据

①向数据表中插入新的数据记录

insert into 表名（字段1，字段2，字段3） values（字段1的值，字段2的值，字段3的值）；

注：注意字符串要用引号引起来

也可以直接使用表名添加记录

（4）查询数据记录

select NAME,NUMBER from INFO #查看指定字段字段

格式：select 字段1,字段2 from 表名 [where 条件表达式]

可以使用where来写条件表达式

加入条件后

加上limit选项可以看前几行

而如果想看第二行之后的前三行可以加上“2，3”

 

（5）修改、更新数据表中的数据记录

update 表名 set 字段名1=修改字段值1 ，字段名2=修改的字段值2 [where 条件表达式]update 表名 set 字段名1=修改字段值1 ，字段名2=修改的字段值2 [where 条件表达式]

附：不加条件就是全部修改

 

（6）在数据表中删除指定的数据记录

delete from 表名 [where 条件表达式]

delete from 表名

将整个表中的数据整个删除

7、修改表名和表结构

（1）修改表名

alter table 旧表名 rename 新表名；

（2）扩展表结构（增加字段）

alter table 表名 add address varchar(50) default '居无定所';

 

（3）修改字段（列）名，添加唯一键

alter table 表名 change 旧列名 新列名 数据类型 [unique key];

unique key 唯一键，可以为多个

（4）删除字段

alter table lgy drop newaddress;

alter table 表名  drop 字段名；

(5)删除表

Drop table ky15.qjw

 

（6）案列扩展

use school;

create table if not exists qjw (id int(4) zerofill primary key auto_increment,    name varchar(10) not null,cardid int(18) not null unique key,hobby varchar(50));

#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建

#int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001

#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;

自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且

添加失败也会自动递增一次

#unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键

#not null：表示此字段不允许为NULL

 

8、数据表高级操作

（1）克隆表

create table fsh like lgy;#复制格式，通过LIKE方法，复制lgy表结构生成fsh表

insert into fsh select * from lgy;#备份内容

 

(2)清空表，删除表内的所有数据

①delete from yyy3;

#DELETE清空表后，返回的结果内有删除的记录条目;

DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用DELETE FROM 删除

所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。

②truncate table test01;

#TRUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，

因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后，

ID会从1开始重新记录

（3）创建临时表

临时表创建成功之后，使用SHOWTABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。

注:无法创建外键

CREATE TEMPORARY TABLE表名（字段1数据类型,字段2数据类型[，...J[,PRIMARY KEY(主健名)]);