创建数据库
create database [名称] 例如
create database test;查看所有数据库
show databases;查看数据创建的数据库
show create database test\G查看数据库引擎
show engines \g查看数据库默认引擎
SHOW VARIABLES LIKE 'storage_engine';删除数据库 drop database [数据库名]; 例如
drop database test;使用数据库 use [数据库名称]; 例如
use test;创建表
create table test1(id INT(11),name VARCHAR(25), deptid INT(11),salary FLOAT);创建格式为create table +名称（），括号内为表的结构，主要的数据类型，INT,VARCHAR,`FLOAT``
查询所有表
show tables;设置主键约束 主键，是表中一列或者多列的组合。主键约束要求列的数据要唯一，而且不能为空。主键能够唯一的识别地标表中的一条记录，可以结合外键来定义不同的数据表之间的关系，并且可以加快查询数据库的速度。主键和记录之间关系如同身份证和人，他们是一一对应的。
单字主键约束：
定义前： mysql> create table test2(id INT(11) PRIMARY KEY, -> name VARCHAR(25), -> deptid INT(11), -> salary FLOAT); Query OK, 0 rows affected (0.53 sec)定义后： create table test3( id INT(11), name VARCHAR(25), depthId INT(11), salary FLOAT, PRIMARY KEY(id));多字段联合主键： 主键有多个字段组成，语法为PRIMARY KEY[字段1，字段2,.......字段n]
create table test4( id INT(11), name VARCHAR(25), depthId INT(11), salary FLOAT, PRIMARY KEY(id,name));
外键约束 外键用来在两个数据表之间的连接，它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性，一个表的外键可以是空值，若不为空值，则每个外键的值必须等于另一个表中的主键的某个值。 外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另一个表的主键。外键的作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联的行。
语法：[CONSTRAINT <外键名>] FROEIGN KEY 字段1[,字段2，.......] REFERENCES <主表名> 主键列1 [,主键列2,......] 一个表中不能有相同的外键。
例如： 定义数据表test1,表的结构为
create table test5 (id INT PRIMARY KEY, name VARCHAR(22) NOT NULL, location VARCHAR(50));定义数据表test5,让它的键deptId作为外键关联到test1表的主键id,代码如下
create table test5( id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT, CONSTRAINT wtest1 FOREIGN KEY(deptId) REFERENCES test1(id));外键约束在表test5添加名称为wtest1的外键约束，外键名称为deptId,其依赖于表test1的id
非空约束 指定不为空，语法如下： 字段名 数据类型 NOT NULL 在test1设过，不再创新表
唯一约束 唯一性约束指的是要求该列唯一，允许为空，但是只能有一个，保证列中不出现重复的值。 语法; 字段名 数据类型 UNIQUE 例如
在创建时设置 name VARCHAR(22) UNIQUE 在创建后设置 name VARCHAR(22), CONSTRAINT STH UNIQUE(name)默认约束
默认约束可以指定某列的默认值，例如设置默认性别为男，插入新的记录没有填性别字段值，那么自动补全为男
字段名 数据类型 DEFAULT 默认值
create table test6( id INT(11) PRIMARY KEY, name VARCHAR(25) NOT NULL, deptId INT(11) DEFAULT 1111, salary FLOAT);设置默认的编号为1111
设置属性值自动增加 在数据库应用中，经常希望在每次插入新记录时，系统会自动生成字段的主键值，这可设通过为表主键添加AUTO INCREMENT关键字来实现。默认的，在MySQL中AUTO INCREMENT的初始值为1,每新增一条记录， 字段值自动加1.一个表只能有一个字段使用AUTO INCREMENT约束，且该字段必须为主键的一部分。 AUTO NCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLINT、 INT、 BIGINT等)。 设置唯一性约束的语法规则如 下: 字段名 数据类型 AUTO_INCREMENT
create table test7( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL, deptId INT(11), salary FLOAT);增添三条记录
INSERT INTO test7 (name,salary) VALUES('Lucy',1000),('Lura',1200),('Kevin',1500);id会自动增加,查询的结果如下
mysql> select * from test7; +----+-------+--------+--------+ | id | name | deptId | salary | +----+-------+--------+--------+ | 1 | Lucy | NULL | 1000 | | 2 | Lura | NULL | 1200 | | 3 | Kevin | NULL | 1500 | +----+-------+--------+--------+insert 不是数据库sql的标准语法，mysql支持，但在其他数据库中不一定支持。
查看表结构 语句describe 表名 简写 desc 表名
mysql> describe test7; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(25) | NO | | NULL | | | deptId | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+查看表的详细结构你 show create table 表名\G
mysql> show create table test7\G Create Table: CREATE TABLE `test7` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci\G的目的主要是为了显示的信息整洁，不然会很很乱
修改表名 语法alter table <旧表名> rename [to] <新表名>
mysql> alter table test7 rename test8; show tables; +----------------+ | Tables_in_test | +----------------+ | test1 | | test2 | | test3 | | test4 | | test5 | | test6 | | test8 | +----------------+修改表中字段的类型
语法alter table <表名> modify <字段名> <数据类型>
alter table test8 modify name VARCHAR(30); desc test8; | name | varchar(30) | YES | | NULL |修改字段名 语法alter table <表名> change <旧字段名> <新字段名> <数据类型>;
alter table test8 change salary money INT(10); desc test8; mysql> DESC TEST8; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | deptId | int(11) | YES | | NULL | | | money | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+若是前后名称一样，它不会报错，会将改变数据类型
添加字段 语法：alter <表名> add <新字段名> <数据类型> [约束条件] [first |after 已存在字段名]
alter table test8 add sex VARCHAR(5) not null after name; mysql> select * from test8; +----+-------+-----+--------+-------+ | id | name | sex | deptId | money | +----+-------+-----+--------+-------+ | 1 | Lucy | | NULL | 1000 | | 2 | Lura | | NULL | 1200 | | 3 | Kevin | | NULL | 1500 | +----+-------+-----+--------+-------+first为在最前面添加，after在指定的字段的后面；不添加位置会默认在最后； 删除字段 语法alter table <表名> drop <字段名>; 将临时添的aaa字段删除
mysql> ALTER TABLE table drop aaa; desc test8; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | sex | varchar(5) | NO | | NULL | | | deptId | int(11) | YES | | NULL | | | money | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)修改字段位置
语法alter table <表名> modify <字段一> <数据类型> first|after <字段二>
alter table test8 modify deptId INT(11) after id; mysql> desc test8; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | deptId | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | sex | varchar(5) | NO | | NULL | | | money | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+first参数是将字段提至第一个，after参数是将提至某个的后面
更改表的存储引擎
引擎表
引擎名是否支持FEDERATED否MRG_MYISAM是MyISAM是BLACKHOLE是CSV是MEMORY是ARCHIVE是InnoDB默认PERFORMANCE_SCHEMA是语法alter table <表名> ENGINE=<更改后的数据引擎名>;
mysql> show create table test8\G; *************************** 1. row *************************** Table: test8 Create Table: CREATE TABLE `test8` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptId` int(11) DEFAULT NULL, `name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `sex` varchar(5) COLLATE utf8_unicode_ci NOT NULL, `money` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci可以看到我的表的默认为MyISAM，下面将它改为BLACKHOLE,并不是每个引擎都能改，亲测csv不行，而且我的默认引擎不是InnoDB
alter table test8 ENGINE=BLACKHOLE; mysql> show create table test8\G; *************************** 1. row *************************** Table: test8 Create Table: CREATE TABLE `test8` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptId` int(11) DEFAULT NULL, `name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `sex` varchar(5) COLLATE utf8_unicode_ci NOT NULL, `money` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci删除表的外键约束
语法alter table <表名> drop foreign key <外键名>
alter table test drop foreign key deeptest;删除外键约束后会解除主表与从表的关系。
删除数据表
drop table [f exists] <表一>,<表二>......
drop table if exists test ,test,test3;if exists 可选,不添加如果不存在这张表，会直接返回error 1051错误，但是加上后是警告语句能够执行。删除多个表用逗号隔开。 当删除的表之间有关联时，先解除关联，然后再删除
小结 当创建了两张表后，修改了引擎，使得两张表的引擎不一样的时候，是不能创建外键连接的。 并不是每个表都需要主键
整数类型
类型存储要求(字节)TINYINT1SMALLINT2MEDIUMINT3INT4BIGINT8字节的宽度跟最大的存储是有关系的，例如一字节为8bit,那么最大表示的数为28 -1为255，加上符号位时位-128~127，所以宽度为4, INT为11，若是查看默认的宽度，可以用如下查看默认宽度
create table test9(x TINYINT,y SMALLINT,z MEDIUMINT,m INT,n BIGINT);查看表结构
mysql> DESC test9; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | x | tinyint(4) | YES | | NULL | | | y | smallint(6) | YES | | NULL | | | z | mediumint(9) | YES | | NULL | | | m | int(11) | YES | | NULL | | | n | bigint(20) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)注意，当我i们创建例如INT(3),插入数据9999时，再去查询它并不会显示999,而是9999，宽度与最大显示数无关，限定宽度的作用在于如果小于最大显示数，位置空缺，会以空格补上。 浮点数类型
类型存储需求（字节）FLOAT4DOUBLE8DECIMAL(m,d)m+2DECIMAL 为定点浮点数，取值范围和double一样，但是有效值取值范围与m,d有关，精度由d决定，默认的精度为（10，0），float，与double的精度由计算机硬件和操作系统有关
create table flor (x FLOAT(5,1),y DOUBLE(5,1),z DEC(5,1)); create table flor1 (x FLOAT(5,2),y DOUBLE(5,2),z DEC(5,2));插入数据
INSERT INTO flor1 values(5.11,5.17,5.1112);查看警告
show warnings; Note | 1265 | Data truncated for column 'z' at row 1定点的浮点数警告后舍入处理后警告，而float和double是直接四舍五入处理
时间与日期型
类型名称日期格式日期范围存储需求(字节）YEARYYYY1901-21551TIMEHH:MM:SS-838:59:59~838:59:593DATAYYYY-MM-DD1000-01-01~9999-12-23DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00：00：00~9999-12-31 23：59：5981.YEAR YEAR 0定YEAR值、如下所示: 1)用以4位字符串或者4位数字格式表示的YEAR,范围为1901~2155,输入格式为： YYYY或’YYYY’例如，输入:2010’或 2010，插入到数据库的值均为2010
2)以2位字符审格式表示的 YEAR,范围为00到’99。‘00’~ ‘69’和’70’-‘99’-分别被转换为20 -2069和1970-1999范围的YEAR值。‘0’与’00’的作用相同，插入超出取值范围的值将被转换为2000.
3)以2位数字表示的 YEAR,范围为1~99。1~69和70~ 99 范围的值分别被转为20 01-2069和1970~1999范围的YEAR值。注意:在这里0值将被转转为0000 而不是2000
例如 创建表yaer
create table yaer(y YEAR);插入测试
insert into yaer values(2100),('2100'); mysql> insert into yaer values(2156),('2100'); ERROR 1264 (22003): Out of range value for column 'y' at row 1清空表中数据
delete from yaer; insert into yaer values('00'),('0'),(77),(0); mysql> select * from yaer; +------+ | y | +------+ | 2000 | | 2000 | | 1977 | | 0000 | +------+ 4 rows in set (0.00 sec)TIME (1) D HH:MM:SS’格式的字符串。还可以使用“非严格”的语法: 'HH:MM:SS、HH:MM、'D HH:MM’、 ‘D HH’或’SS’. 这里的D表示日，可以取0 ~ 34之间的值。在插入数据库时，D被转换为小时保存，格式为“D24 +HH”。 (2) HMMSS’格式的、没有间隔符的字符串或者HHMMSS格式的数值。例如，101112被理解为10:11:12"，但‘109712’ 是不合法的(它的分钟部分没有意义，存储时将变为00:00:00。
注意:如果没有冒号，MySQL解释值时，会假定最右边的两位表示秒。例如，读者可能认为“1112’ 和1112表示11:12:00(即11 点12分)，但MySQL将它们解释为0:11:12(即11分12秒)。同样’12’与12 被解00:00:12.相反，TIME 值中如果使用冒号，则肯定被当作当天时间“11:12’ 表示11:12:00， 而不是00:11:12。
例如
mysql> create table times( t TIME); mysql> INSERT INTO times values('10:05:05'),('23:23'),('2 10:10'),('3 02'),('10'); mysql> select * from times; +----------+ | t | +----------+ | 10:05:05 | | 23:23:00 | | 58:10:00 | | 74:00:00 | | 00:00:10 | +----------+注意：在使用‘D HH’格式时，小时要用双数值，如果小于十，前面补零。
例二： 首先把表中原有数据删除，再向表中插入如下数据：
DELETE FROM times; mysql> INSERT INTO TIMES values('101112'),(111213),('0'),(0); | 10:11:12 | | 11:12:13 | | 00:00:00 | | 00:00:00 | +----------+插入现在时间
mysql> insert into times values (CURRENT_TIME),(NOW());DATA 类型 1.基本格式： YYYY-MM-DD 表示年，月，日 例如：2000-03-23 和20000323储存的值是一样的。 2.当输入YY-MM-DD 或者YYMMDD表示日期，YY中的范围 0~69自动转换为2000-2069，而70~99 转为1970-1999，带上引号与不带引号转换一样。 3.CURRETN_DATE 或者NOW表示当前日期。
创建数据表，插入数据
mysql> create table dates(d DATE); mysql> insert into dates values('1998-08-23'),('39230812'),(10000221); mysql> select * from dates; +------------+ | d | +------------+ | 1998-08-23 | | 3923-08-12 | | 1000-02-21 | +------------+插入另外的数据
mysql> insert into dates values('99-09-09'),(990909),('990909'); | 1999-09-09 | | 1999-09-09 | | 1999-09-09 | +------------+注：mysql支持不严格的符号，例如，98-11-31，98.11.31，98/11/31
DATETIME 时间日期类型，格式为YYYY-MM-DD HH-MM-SS 分别表示年月日，小时，分钟，秒。 1.‘YYYY-MM-DD HH-MM-SS’ 与YYYYMMDDHHMMSS 插入效果一样，表示的范围1000-01-01 00:00:00~ 9999-12-31 23:59:59 。 2.省缺形的如上 TIME,YEAR省缺的结合。 3.允许插入现在的时间 例如： 创建表,插入数据
mysql> create table dt (dt DATETIME); mysql> insert into dt values('1998-08-08 08:08:08'),('19980808080808'); mysql> select * from dt; +---------------------+ | dt | +---------------------+ | 1998-08-08 08:08:08 | | 1998-08-08 08:08:08 | +---------------------+ 2 rows in set (0.01 sec) mysql> insert into dt values('1998-08-08 09-09-09'),(19980808090909); mysql> insert into dt values('98-08-08 08:08:08'),('190808080808'); | 1998-08-08 09:09:09 | 1998-08-08 09:09:09 | 1998-08-08 08:08:08 | 2019-08-08 08:08:08 mysql> insert into dt values(now());注：同样的，mysql的DATETIME也支持不严格的语法，任何标点符号都可以成为分隔符。
DATESTAMP 它的显示格式和DATETIME相同，但是表示的范围比较小，范围为1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 UTC,所以要保证范围在它能表示的范围。
创建表插入如下数据
mysql> insert into ts values('19950101010101'),('950505020202'),(now()); mysql> insert into ts values('97@03@03 03@03@03'); mysql> select * from ts; +---------------------+ | ts | +---------------------+ | 1995-01-01 01:01:01 | | 1995-05-05 02:02:02 | | 2019-12-27 23:14:12 | | 1992-02-03 02:02:03 | | 1997-03-03 03:03:03 | +---------------------+ mysql> insert into ts values('2049@03@03 03@03@03'); ERROR 1292 (22007): Incorrect datetime value: '2049@03@03 03@03@03' for column 'ts' at row 1TIMESTAMP与DATETIME最大的不同不是范围，而是TIMESTAMP的类型格式为UTC格式存储，查询时，时区不同，显示的时间是不同的，而DATETIME欸有这个特性。
删除表中数据
mysql> delete from ts; mysql> insert into ts values(now()); mysql> select * from ts; +---------------------+ | ts | +---------------------+ | 2019-12-27 23:25:38 | +---------------------+修改时区：
mysql> set time_zone='+10:00'; mysql> select * from ts; +---------------------+ | ts | +---------------------+ | 2019-12-28 01:25:38 | +---------------------+ 1 row in set (0.00 sec)字符串类型
类型名称说明存储需求CHAR(M)固定长度非二进制字符串M字节，1<=M<=255VARCHAR(M)变长非二进制字符串L+1字节，L<=MTINYTEXT非常小的非二进制字符串L+1字节，L<28TEXT小的非二进制字符串L+2字节，L<216MEDIUNTEXT中等的非二进制字符串L+3字节， L<224LONGTEXT大的非二进制字符串L+4字节， L<232ENUM枚举类型，只能有一个枚举字符串值1个或者两个字节SET字符串对象，可以有一个或者多个SET成员1，2，3，4，8个字节char 与varchar类型的最大区别在于它们的存储的宽度不同，例如char(4)的宽度固定为4，而varchar(4)的宽度不固定，尾部存在一个空格，而char类型不存在。
ENUML类型 ENUM为字符串枚举类型，每个枚举类型都会有一个索引值，mysql存储的是索引的编号，最多可以枚举65535个元素。 创建字段类型是语法为： 字段名 ENUM('值1'，'值2',.......'值n)' 如下创建表enumm,并插入数值
create table enumm(enm enum('first','second','third')); insert into enumm values('first'),('second'),('third'),('NULL');这里面的NULL是可以插入进去的，没有任何值，但是它的索引是零，可以通过如下查看索引：
mysql> select enm ,enm+0 from enumm; +--------+-------+ | enm | enm+0 | +--------+-------+ | first | 1 | | second | 2 | | third | 3 | | | 0 | +--------+-------+但是不能插入其他的值，直接会报出错误，例如：
mysql> insert into enumm values('fir'),('sec'),('th'); ERROR 1265 (01000): Data truncated for column 'enm' at row 1ENUM列有一个默认值，默认为NULL,如果该列为not NULL,那么默认就为允许值的第一个元素。 练习一个enum类型的表。
mysql> create table test10 (soc INT,level enum('excellent','good','bad')); mysql> insert into test10 values(70,'good'),(90,1),(75,2),(50,3),(86,'excellent'); mysql> select * from test10; +------+-----------+ | soc | level | +------+-----------+ | 70 | good | | 90 | excellent | | 75 | good | | 50 | bad | | 86 | excellent | +------+-----------+ 5 rows in set (0.00 sec) mysql> select level,level+0 from test10; +-----------+---------+ | level | level+0 | +-----------+---------+ | good | 2 | | excellent | 1 | | good | 2 | | bad | 3 | | excellent | 1 | +-----------+---------+SET类型 可以有零个或者多个值，最多有64个成员，如果成员的值有重复，则自动删除重复的值。特点是插入的数据只能从开始定义时的值中选取。 例如： 创建test11,并插入数值
mysql> create table test11 (s SET('a','b','c','d')); mysql> insert into test11 values('a'),('a,b,a'),('c,a,d'); mysql> insert into test11 values('a,e,f'); ERROR 1265 (01000): Data truncated for column 's' at row 1 mysql> select * from test11; +-------+ | s | +-------+ | a | | a,b | | a,c,d | +-------+因为只允许插入这个a,b,c,d,所以插入(a,e,f)报错，插入的重复数值会被自动清除，而且按照创建顺序排列。
二进制类型
类型名称说明存储需求BIT(M)位字段类型大约（M+7)/8个字节BINARY(M)固定长度二进制字符串M个字节VARBINARY(M)可变长度二进制字符串M+1个字节ITINYBLOB(M)非常小的BLOBL+1个字节，L<2^8BLOB(M)小的BLOBL+2个字节，L<2^16MEDIUMBLOB(M)中等大小的BLOBL+3个字节，L<2^24LONGBLOB(M)非常大的BLOBL+4个字节，L<2^32BIT 时位字段类型，范围位1~64 ，如果M省略，默认为1，插入的位数不够，会自动补零，但是不允许插入超过范围位数的数。 例如：
mysql> create table test12 (b BIT(4)); mysql> insert into test12 values(2),(9),(15);直接查询会失败
mysql> select * from test12; +------+ | b | +------+ | | | | | | +------+转为二进制形式查询
mysql> select bin(b) from test12; +--------+ | bin(b) | +--------+ | 10 | | 1001 | | 1111 | +--------+插入超过的数值：
mysql> insert into test12 values(16); ERROR 1406 (22001): Data too long for column 'b' at row 1BINARY与VARBINARY 与char和varchar类似，一个是不可变长度的二进制类型，一个是可变的二进制类型。
create table test13(b BINARY(3),vb VARBINARY(3)); INSERT INTO test13 values(5,5); mysql> select bin(b),bin(vb) from test13; +--------+---------+ | bin(b) | bin(vb) | +--------+---------+ | 101 | 101 | | 101 | 1001 | +--------+---------+插入字符串，显示位0
mysql> INSERT INTO test13 values(5,'a'); mysql> select bin(b),bin(vb) from test13; +--------+---------+ | bin(b) | bin(vb) | +--------+---------+ | 101 | 101 | | 101 | 1001 | | 101 | 111000 | | 101 | 0 | +--------+---------+注：这里面的varbinary(3)中的3只允许插入的最大数位999，也就是十进制的最大三位数。
BLOB类型 二进制类型的大对象，LONGBLOB可以存储最多4GB
’
