2-1 SQL介绍和分类 1 2 3 4 5 6 7 8 SQL介绍： 结构化查询语言，关系型数据库通用的语言 SQL分类： DDL：数据定义语言Data Definition; create/alter/drop DCL：数据控制语言Data Contrl; grant DML：数据操作语言Data Manipulation; insert/update/delete DQL：数据的查询语言(非官方);
2-2 库的简单操作 1 2 3 4 5 6 7 8 字符集charset： 常用的字符集有两种分别是utf8和utf8mb4，utf8的最大存储长度，单个字符最多支持3个字节，utf8mb4支持的编码比utf8更多，比如emoji表情字符，utf8不支持，emoji字符一个字符占4个字节，utf8存不下 创建数据库的规范： 库名不建议有大写字母 创建数据库时建议指定字符集 库名不能以数字开头 库名要和实际业务相关
1 2 3 4 5 6 7 8 9 10 11 12 增create - 创建数据库： 语法：create database 库名(charset=字符集); 查show： show databases; -- 查看当前MySQL中所有的库 show create database 库名; -- 查看指定数据库的创建规则 改alter - 修改数据库的字符集： alter database 库名 charset 字符集; -- 修改后的字符集必须是原字符集的严格超集，当数据库为空的时候，可以修改，但是一旦当数据库中有了数据就必须遵循修改后的字符集必须是原字符集的严格超集这一原则。 删drop： drop database 库名;
2-3 创建表的简单语法及步骤 1 2 3 4 数据库的逻辑关系 --- windows系统文件逻辑关系 库 ---- 文件夹 表 ---- 文件 数据 ---- 文件中的数据
1 2 3 4 5 6 7 8 创建表的规范： 表名小写(windows平台不区分大小写，Linux严格区分大小写，目的是为了平台兼容性)，不要过长 不建议以数字开头 必须设置字符集和存储引擎 表名和业务有关 为表中的字段选择合适的数据类型 建议每个列都有注释 建议每个列都设置为非空，不知道填什么可以先设定默认值
1 2 3 4 5 6 7 创建表的步骤和语法： 1.查看当前所有的库：show databases; 2.切换到需要创建表的库：use 目标数据库; 3.创建表(简单版)：create table 表名(字段1 数据类型(宽度), 字段2 数据类型(宽度), …) 例：create table stu_info(name char(4), age int); 4.查看当前库下所有的表：show tables; 5.补充：也可以使用绝对路径的方式创建表; create table 目标数据库.表名(字段 数据类型);
2-4 表的简单操作 - 插入数据 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 标准的插入数据的SQL： insert into 表名(字段1, 字段2,...) values(值1, 值2, ...); -- 注意字段需要和表中的字段相符，不能出现表中不存在的字段，字段也不能比表中实际的字段多，值和字段顺序相对应 省略的插入数据的SQL： insert into 表名 values(值1, 值2, ...); -- 值的个数必须和表中字段的数量一致，值和表中字段默认顺序相对应 针对性的为某个字段插入数据： insert into 表名(指定字段1, 指定字段2, ...) values(值1, 值2)； 同时插入多条数据： insert into 表名(字段1, 字段2,...) values (值1, 值2, ...),(值1, 值2, ...),...; -- 指定字段或者全部字段插入多条数据 insert into 表名 values (值1, 值2, ...),(值1, 值2, ...),...; -- 所有字段插入多条数据
1 2 3 4 5 6 7 8 9 10 11 12 -- 表中插入数据小练习 create database info charset=utf8; -- 创建保存学生信息的数据库 create table info.stu_info (id int, name char(5), QQ char(12)); -- 创建学生信息表 -- 插入数据 insert into info.stu_info(id, name, QQ) values(1, 'lili', '123456');-- 标准语法 insert into info.stu_info values(2, '梨花', '12222'); -- 简略写法 insert into info.stu_info(name, id, QQ) values('丽丽', 3, '789456'); -- 针对性的插入数据 insert into info.stu_info(id, name, QQ) values (4, 'qq', '12345'), (5, 'wx', '7894') ; -- 同时插入多条数据
2-5 存储引擎 1 2 3 4 5 6 7 存储引擎定义：不同的文件需要不同的存储格式和处理方式；数据库中的储存引擎就是对存储数据的处理机制。不同的存储引擎，对数据的处理方式不同。 MySQL中主要的存储引擎： Innodb,，是MySQL5.5版本及之后默认的存储引擎，存储数据更加的安全(第八章会详细介绍该存储引擎) myisam，是MySQL5.5版本之前默认的存储引擎，速度要比Innodb更快，是我们更加注重的是数据的安全 memory，内存引擎(数据全部存放在内存中) 断电数据丢失 blackhole，无论存什么，都将数据丢失(黑洞)
1 2 3 4 5 6 7 8 9 10 11 -- 查看MySQL的存储引擎 show engines; -- 验证不同存储引擎在硬盘上对应的数据文件也不相同 create database test charset=utf8mb4; create table test.t1(id int) engine=innodb; create table test.t2(id int) engine=myisam; create table test.t3(id int) engine=blackhole; create table test.t4(id int) engine=memory; -- 在MySQL安装目录下的Data文件夹的test(创建的数据库)的目录下会分别出现t1 t2 t3 t4表对应的数据文件
2-6 创建表的完整语法 1 2 3 4 5 6 7 8 复习：创建表的规范 表名小写(windows平台不区分大小写，Linux严格区分大小写，目的是为了平台兼容性)，不要过长 不建议以数字开头 必须设置字符集和存储引擎 表名和业务有关 为表中的字段选择合适的数据类型 建议每个列都有注释 建议每个列都设置为非空，不知道填什么可以先设定默认值
1 2 3 4 5 6 7 8 9 10 create table 表名( 字段1 数据类型(宽度) 约束条件 COMMENT 字段1注释, 字段2 数据类型(宽度) 约束条件 COMMENT 字段2注释, ... ) engine=存储引擎 charset=字符集 comment 表注释; -- 注意： -- 1. 在同一张表中字段名不能重复 -- 2. 字段名、字段类型是必须的，宽度、约束条件是可选的，约束条件支持写多个 -- 3. 最后一个字段不能以逗号结尾否则会报错
1 2 3 4 约束条件：null/not null null：表示该字段可以为空 not null：表示该字段不能为空 例：create table t1(id int,name char(4) not null);
1 2 宽度和约束条件的关系： 宽度可以理解为字符的个数，是用来限制数据的存储的，而约束条件是在款段的基础之上增加的额外的约束。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -- 创建表的完整语法小练习 create database info charset=utf8; -- 创建库 create table info.stu_info( id int not null comment '编号', name char(4) not null comment '姓名', age int not null comment '年龄', sex char(1) null comment '性别', qq char(12) not null comment 'QQ号' ) engine=innodb charset=utf8 comment '信息表'; -- 向表中写入数据 use info; insert into stu_info(id, name, age, sex, qq) values(1, 'lili', 18, '男', '123456789'); -- 标准插入数据 insert into stu_info values(2, 'nini', 18, '女', '123456789'); -- 省略插入数据 insert into stu_info(id, name, age, qq) values(3, 'wx', 18, '123456789'); -- 指定字段插入数据
2-7 查看表结构 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 desc 表名； -- 查看表结构 +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | char(4) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ -- Field：字段名 -- Type：字段的数据类型 -- Null：字段的约束条件是否接受空值 -- Key：是否是key -- Default：默认值 show create table 表名 \G; -- 查看创建表的完整语法，\G为了更加清楚的查看结果 *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int DEFAULT NULL, `name` char(4) NOT NULL, `age` int NOT NULL COMMENT '年龄' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 1 row in set (0.00 sec)
2-8 MySQL数据类型 - 数字类型 MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
常用的数字类型 - 整型，一般使用int就可以满足开发需求，除非涉及到极大的数据量使用bigint
类型
范围(有符号)
范围(无符号unsigned)
用途
tinyint
(-128，127)
(0，2^8-1)
小整数值
int
(-2^31 , 2^31-1)
(0，2^32-1)
大整数值
bigint
(-2^63, 2^63-1)
(0, 2^64-1)
极大整数值
常用的数字类型 - 浮点型，一般使用float就可以满足开发需求，除非是工程计算需要的精度就是非常高的了
类型
用途
存储限制
float
单精度，精度最小
float(255,30) # 总共255位 小数部分占30位
double
双精度，精度高于float
double(255,30) # 总共255位 小数部分占30位
decimal
小数值，精度高于doucle
decimal(65,30) # 总共65位 小数部分占30位
2-9 MySQL数据类型 - 字符类型
char(宽度)
varchar(宽度)
宽度可省略，默认是1个宽度，宽度就是字符的个数
宽度不能省略
定长的字符串类型，在存储字符串时，比如char(11)，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，就使用空格进行填充。
在存储数据是会先判断字符长度，然后合理分配存储空间，char类型不会判断，立即分配空间，当然在固定长度的列中，选择char类型会更加合适。varchar类型除了会存储字符串之外还会额外使用1-2字节(bytes)来存储这个字符串的长度。
查询速度快
节省空间，但是查询速度慢
1 2 3 4 5 6 补充： 1B(byte) = 8bit 1KB = 1024Bytes 1MB = 1024KB 1GB = 1024MB 1TB = 1024GB
2-10 MySQL数据类型 - 时间类型
类型
大小 ( bytes)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
‘-838:59:59’/‘838:59:59’
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
4
1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07 ，格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS
混合日期和时间值，时间戳
1 2 3 4 5 6 7 8 9 -- 补充 LOCALTIME() -- MySQL时间函数，返回当前时间的 年月日时分秒 select LOCALTIME(); -- 查看当前时间的年月日时分秒 -- 向表中写入当前时间的时分秒 create table t1(curent_time datetime); insert into t1 values(LOCALTIME()); -- SQL语句可以大写也可以小写，但是需要注意表名的大小写情况。
2-11 MySQL数据类型 - 枚举和集合
enum(选项1, 选项2, …)
set(选项1, 选项2, …)
枚举字段，向表中写入对应字段的数据时只能存所有选项中的某一个
集合字段，向表中写入对应字段的数据时只能存所有选项中的一个或者多个
不能存枚举中没有列出来的选项
不能存集合中没有列出来的选项
用途：比如性别、年级
比如：爱好
2-12 MySQL数据类型总结 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- 数字类型 tinyint int(常用) bigint -- 整数型 float(常用) double decimal -- 小数型 -- 字符类型 char(宽度) -- 宽度可省略，查询速度快 varchar(宽度) -- 宽度不能省略，节省存储空间 -- 时间类型 date:年月日 2020-5-4 datetime:年月日时分秒 2020-5-4 11:11:11 time:时分秒11:11:11 Year:2020 -- 枚举(enum)和集合(set)类型 enum(选项1, 选项2, ...) -- 多选一 set(选项1, 选项2, ...) -- 多选多
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 -- 课后练习 mysql> create database student charset=utf8; -- 创建库 mysql> create table student.info( id int unsigned, name varchar(6) not null, age int not null, sex enum('male', 'female') not null default 'male', QQ varchar(12), hobby set('read', 'play', 'sleep'), create_time datetime not null default localtime() ) engine=innodb charset=utf8 comment='信息表'; mysql> insert into info(id, name, age, QQ, hobby) values(1, '丽丽', 18, '1277770', 'read,sleep'); Query OK, 1 row affected (0.01 sec) mysql> select * from info; +------+--------+-----+------+---------+------------+---------------------+ | id | name | age | sex | QQ | hobby | create_time | +------+--------+-----+------+---------+------------+---------------------+ | 1 | 丽丽 | 18 | male | 1277770 | read,sleep | 2022-02-28 09:34:06 | +------+--------+-----+------+---------+------------+---------------------+ 1 row in set (0.00 sec)
2-13 约束条件 - default 补充：在创建库和表以及表的字段的时候不能使用MySQL的关键字(SQL语法中的除了库名、表名、字段外的)作为库名/表名
约束条件：限制和约束字段的条件，如果字段值违反了字段的约束条件，MySQL就会报错
1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- default默认值，在创建表的时候可以为字段设定一个默认值 -- ps：在创建表的规范时，最后一条建议字段设为非空，可以使用默认值进行填充。 -- 注意：在使用简略的插入数据的SQL语法(insert into 表名 values(值1, ...))时必须所有字段都有值，即使是设定了默认值的情况下。 mysql> create table t1(id int, sex enum('male', 'female', 'unkonwn') not null default 'unkonwn'); Query OK, 0 rows affected (0.03 sec) mysql> desc t1; +-------+---------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | sex | enum('male','female','unkonwn') | NO | | unkonwn | | +-------+---------------------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
2-14 约束条件 - unique 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 -- unique 唯一约束条件(key)，插入数据唯一不能重复，比如说学生编号 mysql> create table t2(id int unique, name varchar(6)); Query OK, 0 rows affected (0.04 sec) mysql> desc t2; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int | YES | UNI | NULL | | | name | varchar(6) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t2 values(1, 'lili'); Query OK, 1 row affected (0.01 sec) mysql> insert into t2 values(2, 'lili'); -- name字段重复不会报错 Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values(2, 'lili'); -- id因为有了unique约束条件会提示用重复(Duplicate)的数据 ERROR 1062 (23000): Duplicate entry '2' for key 't2.id' -- 可以使用联合唯一，几个字段连在一起时是唯一的 mysql> create table t3(id int, name varchar(5), unique(id, name)); -- id+name唯一 Query OK, 0 rows affected (0.04 sec) mysql> desc t3; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int | YES | MUL | NULL | | | name | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t3 values(1, 'lili'); Query OK, 1 row affected (0.01 sec) mysql> insert into t3 values(2, 'lili'); -- name重复不会报错 Query OK, 1 row affected (0.01 sec) mysql> insert into t3 values(1, 'qq'); -- id重复也不会报错 Query OK, 1 row affected (0.08 sec) mysql> insert into t3 values(1, 'lili'); -- name+id重复就会报错提示重复了 ERROR 1062 (23000): Duplicate entry '1-lili' for key 't3.id'
2-15 约束条件 - primary key 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 -- primary key，key的一种，主键效果等同于not null+unique，即非空且唯一 mysql> create table t4(id int primary key, name varchar(6) unique); Query OK, 0 rows affected (0.63 sec) mysql> desc t4; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(6) | YES | UNI | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) -- 主键除了能够对字段进行约束之外，还是innodb存储引擎组织数据的依据，这个在详细介绍innodb时再详细介绍 -- 使用innodb存储引擎创建表时必须要有主键，并且可以通过主键提升查询效率 -- 在一张表中有且只有一个主键 mysql> create table t5(id int primary key, name char(6) primary key); ERROR 1068 (42000): Multiple primary key defined -- 如果没有设置主键，则按照创建表时的字段顺序第一个非空且唯一的字段将自动升级为主键。 mysql> create table t5(id int, name char(6) not null unique, age int not null unique); Query OK, 0 rows affected (0.03 sec) mysql> desc t5; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | char(6) | NO | PRI | NULL | | | age | int | NO | UNI | NULL | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) -- 如果表中没有设置逐渐和非空且唯一的字段，则Innodb默认提供一个隐藏的主键，但是这个默认隐藏的主键无法提供快速查询的服务。 mysql> create table t6(id int, name char(6)); Query OK, 0 rows affected (0.03 sec) mysql> desc t6; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | char(6) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) -- 一般，表中的主键应该设置为id字段，id字段一般在创建表的时候是必须指定的，并且是主键字段 -- 主键可以为一个字段，也可以是联合主键（多个字段联合起来作为表的主键，本质还是一个主键） mysql> create table t7(id int, name varchar(5), primary key(id, name)); Query OK, 0 rows affected (0.03 sec) mysql> desc t7; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(5) | NO | PRI | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
2-16 约束条件 - auto_increment 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 -- auto_increment 自增约束条件，通常是主键字段使用，普通字段不使用该约束条件，可以标识当前数据是是表中d第几条数据 -- 同一张表中只能有一个字段使用该约束条件。 mysql> create table t8(id int primary key auto_increment, cid int auto_increment); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- 提示只能有一个列是自增列 -- 向表中写入数据时，可以省略主键，因为主键是非空唯一且自增的 -- 主键的初始值是1，每向表中写入一条数据，主键值自动+1 mysql> create table t9(id int primary key auto_increment, name char(6)); Query OK, 0 rows affected (0.04 sec) mysql> desc t9; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | char(6) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> insert into t9 values('lili'); -- 当不手动写入主键字段时，不能使用简略的插入数据的sql语法 ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into t9(name) values('lili'); Query OK, 1 row affected (0.01 sec) mysql> select * from t9; +----+------+ | id | name | +----+------+ | 1 | lili | +----+------+ 1 row in set (0.00 sec)
2-17 约束条件 - 总结 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 null/not null：空/非空 unsigned：无符号 default：为字段设置默认值 unique：唯一键(约束条件)，可以单列唯一，也可以联合唯一 primary key：主键约束，非空且唯一(unique+not null) 主键除了能够对字段进行约束之外，还是innodb存储引擎组织数据的依据 用innodb存储引擎创建表时必须要有主键，并且可以通过主键提升查询效率 在一张表中有且只有一个主键 如果没有设置主键，则按照创建表时的字段顺序第一个非空且唯一的字段将自动升级为主键。 如果表中没有设置逐渐和非空且唯一的字段，则Innodb默认提供一个隐藏的主键，但是这个默认隐藏的主键无法提供快速查询的服务。 一般，表中的主键应该设置为id字段，id字段一般在创建表的时候是必须指定的，并且是主键字段 主键可以为一个字段，也可以是联合主键（多个字段联合起来作为表的主键，本质还是一个主键） auto_increment：自增约束条件，通常和主键一起使用。 同一张表中只能有一个字段使用该约束条件。 主键的初始值是1，每向表中写入一条数据，主键值自动+1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 -- 小练习 mysql> create database info charset=utf8mb4; -- 创建库 mysql> create table student( id int primary key auto_increment comment '主键', name varchar(6) not null unique, age int unsigned not null, sex enum('male', 'female') not null default 'male', hobby set('read', 'play', 'sleep') null, create_time datetime default localtime() ) engine=innodb charset=utf8mb4; -- 创建表 mysql> insert into student(name, age, hobby) values('丽丽', 18, 'read,sleep'); Query OK, 1 row affected (0.02 sec) mysql> select * from student; +----+--------+-----+------+------------+---------------------+ | id | name | age | sex | hobby | create_time | +----+--------+-----+------+------------+---------------------+ | 1 | 丽丽 | 18 | male | read,sleep | 2022-03-01 09:27:00 | +----+--------+-----+------+------------+---------------------+ 1 row in set (0.00 sec)
2-18 表增加字段 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 -- 表准备 mysql> create database test; mysql> create table test.t1(id int primary key auto_increment); -- 添加1个字段 alter table 表名 add 字段名 数据类型(宽度) 约束条件 comment '字段注释'; mysql> alter table t1 add name varchar(5); mysql> desc t1; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+----------------+ -- 添加多个字段 alter table 表名 add (字段名1 数据类型(宽度) 约束条件 comment '字段注释', 字段名2 数据类型(宽度) 约束条件 comment '字段注释'); mysql> alter table t1 add (age int, sex enum('male', 'female') default 'male'); mysql> desc t1; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(5) | YES | | NULL | | | age | int | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ -- 向指定位置添加列(在第一个字段的位置写入新的字段，只能添加一个字段) alter table 表名 add 字段名 字段类型(宽度) 约束条件 comment '字段注释' first; mysql> alter table t1 add fir_name int unsigned first; mysql> desc t1; +----------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------------+------+-----+---------+----------------+ | fir_name | int unsigned | YES | | NULL | | | id | int | NO | PRI | NULL | auto_increment | | name | varchar(5) | YES | | NULL | | | age | int | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +----------+-----------------------+------+-----+---------+----------------+ -- 向指定位置添加列(在某个字段之后写入新的字段) alter table 表名 add 字段名 字段类型(宽度) 约束条件 comment '字段注释' after 字段名; mysql> alter table t1 add after_name char(6) default 'sss' after age; mysql> desc t1; +------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------------+------+-----+---------+----------------+ | fir_name | int unsigned | YES | | NULL | | | id | int | NO | PRI | NULL | auto_increment | | name | varchar(5) | YES | | NULL | | | age | int | YES | | NULL | | | after_name | char(6) | YES | | sss | | | sex | enum('male','female') | YES | | male | | +------------+-----------------------+------+-----+---------+----------------+
2-19 表的其他修改和删除操作 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 -- 表准备 create table t1(id int primary key auto_increment, name varchar(6) not null unique); desc t1; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(6) | NO | UNI | NULL | | +-------+------------+------+-----+---------+----------------+ -- 修改表名，不能修改库名但是可以修改表名 alter table 表名 rename 新表名; alter table t1 rename t0; show tables; -- 修改字段数据类型、约束条件，需要将不需要修改的约束条件同时写上，否则约束条件可能会产生变化 alter table 表名 modify 字段名 字段类型(宽度) 约束条件； mysql> alter table t1 modify name char(6); -- 只修改字段的数据类型，约束条件变化了 mysql> desc t1; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | char(6) | YES | UNI | NULL | | +-------+---------+------+-----+---------+----------------+ -- 修改字段名，如果约束条件不变的话也是需要同时写上修改前的约束条件，否则约束条件可能会产生变化 alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件; mysql> alter table t1 change name new_name char(6); mysql> desc t1; +----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | new_name | char(6) | YES | UNI | NULL | | +----------+---------+------+-----+---------+----------------+ -- 删除字段，删除字段后，如果表中有数据那么对应该字段的数据都会被删除。 alter table 表名 drop 字段名; alter table t1 drop name; desc t1; -- 删除表 drop table 表名;
2-20 数据的修改 1 2 3 4 5 6 7 8 9 -- 表准备 create table t1(id int primary key auto_increment, name char(6), age int); insert into t1(name, age) values ('lili', 18), ('nini', 19), ('nana', 20); -- 数据的修改操作,如果不指明修改哪一行的话会将所有数据行的字段值都进行修改操作。 update 表名 set 字段名=新字段值, 字段名=新字段值,... where 条件;
2-21 数据的删除 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 -- 提示：删除操作非常危险，在实际开发中尽量不要执行删除操作 -- 删除数据，删除一条数据 delete from 表名 where 条件; -- 删除数据，删除表中所有数据 drop table 表名; -- 删除表就表示表中所有的数据都被删除 delete from 表名; -- 清空表中所有数据 truncate table 表名; -- 清空表中所有数据 -- delete和truncate的区别 1.delete清空表之后向表中再写入数据主键的值是在已经删除的数据的基础上+1，而truncate清空表之后，再写入数据，主键值是从1开始的。 2.delete是DML操作也就是数据库修改操作, 是逻辑性质删除,逐行进行删除,速度慢.并未真正在磁盘上删除，磁盘空间不会立即释放。 3.truncate是DDL操作也就是数据库定义的操作,对表的数据进行清空,速度快.磁盘空间立即释放 -- 补充小知识 在实际开发场景中，通常使用一个字段来标识数据是否删除，而不是真正的将数据从我们的表中删除，比如说我们可以定义一个字段is_delete来表示该条数据是否被删除了，我们可以使用0表示数据被删除1表示数据没有被删除。 像这种只有两种状态的数据我们都可以使用布尔类型进行定义。 在MySQL中MySQL内置其实并没有布尔类型，但是MySQL支持在定义字段的时候将数据类型定义为布尔类型叫BOOLEAN或BOOL，布尔类型只有两种状态，一种是真一种是假。 在MySQL中0被认为是假(FALSE/False/false)，非零值被认为是真(true/True/TRUE)，当然也可以使用布尔文本也就是FALSE/False/false和true/True/TRUE表示真假，但是在显示数据的时候布尔文本只显示0和1这两种状态 alter table t1 add(is_delete bool default true); alter table t1 add(is_delete1 boolean default 1); mysql> desc t1; +------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | new_name | char(6) | YES | UNI | NULL | | | is_delete | tinyint(1) | YES | | 1 | | | is_delete1 | tinyint(1) | YES | | 1 | | +------------+------------+------+-----+---------+----------------+ mysql> insert into t1(new_name, is_delete) values('11', FALSE); mysql> select * from t1; +----+----------+-----------+------------+ | id | new_name | is_delete | is_delete1 | +----+----------+-----------+------------+ | 1 | 11 | 0 | 1 | +----+----------+-----------+------------+ 我们可以通过查看创建表的完整SQL看一下MySQL的布尔类型到底是什么类型。可以发现定义字段时使用的布尔类型变成了tinyint(1)。 mysql> show create table t1 \G; Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `new_name` char(6) DEFAULT NULL, `is_delete` tinyint(1) DEFAULT '1', `is_delete1` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`), UNIQUE KEY `name` (`new_name`), UNIQUE KEY `name_2` (`new_name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
2-22 库的操作总结 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SQL：SQL语句对大小写不敏感，SQL语法大写小写都可以，但是库名、表名、字段名定义的时候是小写的话在SQL语句中也是小写。 创建数据库的规范： 库名不建议有大写字母 创建数据库时建议指定字符集 库名不能以数字开头 库名不能使用MySQL关键字命名 库名要和实际业务相关 增create - 创建数据库： 语法：create database 库名(charset=字符集); 查show： show databases; -- 查看当前MySQL中所有的库 show create database 库名; -- 查看指定数据库的创建规则 改alter - 修改数据库的字符集，不支持直接修改库名： alter database 库名 charset 字符集; 删drop： drop database 库名;
2-23 表的操作总结 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 创建表的规范： 表名小写(windows平台不区分大小写，Linux严格区分大小写，目的是为了平台兼容性)，不要过长 不建议以数字开头 不能使用MySQL关键字作为表名和字段名 必须设置字符集和存储引擎 表名和业务有关 为表中的字段选择合适的数据类型 建议每个列都有注释 建议每个列都设置为非空，不知道填什么可以先设定默认值 增 - 新建表 create table 表名( 字段1 数据类型(宽度) 约束条件 COMMENT 字段1注释, 字段2 数据类型(宽度) 约束条件 COMMENT 字段2注释, ... ) engine=存储引擎 charset=字符集 comment 表注释; 改 - 修改表 alter table 表名 add 字段名 数据类型(宽度) 约束条件 comment '字段注释'; -- 新增1个字段 alter table 表名 add (字段名1 数据类型(宽度) 约束条件 comment '字段注释', 字段名2 数据类型(宽度) 约束条件 comment '字段注释'); -- 添加多个字段 alter table 表名 add 字段名 字段类型(宽度) 约束条件 comment '字段注释' first; -- 向指定位置添加字段，字段插入到表中第一个字段的位置，只能添加一个字段 alter table 表名 add 字段名 字段类型(宽度) 约束条件 comment '字段注释' after 字段名; -- 向指定位置添加字段，在指定字段之后添加字段，只能添加一个字段 alter table 表名 rename 新表名; -- 修改表名 alter table 表名 modify 字段名 字段类型(宽度) 约束条件； -- 修改字段属性 alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件; -- 修改字段名 alter table 表名 drop 字段名; -- 删除表中的字段 查 - 查询 show tables; -- 查看当前库中有哪些表 desc 表名; -- 查看表结构 show create table 表名; -- 查看创建表的完整语法 删 - 删除 drop table 表名;
2-24 数据的操作总结 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 增 - 增加数据 insert into 表名(字段1, 字段2,...) values(值1, 值2, ...); -- 标准的插入数据的SQL insert into 表名 values(值1, 值2, ...); -- 省略的插入数据的SQL insert into 表名(指定字段1, 指定字段2, ...) values(值1, 值2)； -- 针对性的为某个字段插入数据 -- 同时插入多条数据： insert into 表名(字段1, 字段2,...) values (值1, 值2, ...),(值1, 值2, ...),...; -- 指定字段或者全部字段插入多条数据 insert into 表名 values (值1, 值2, ...),(值1, 值2, ...),...; -- 所有字段插入多条数据 改 - 修改数据 update 表名 set 字段名=新字段值, 字段名=新字段值,... where 条件; -- 数据的修改操作,如果不指明修改哪一行的话会将所有数据行的字段值都进行修改操作。 删 - 删除数据 delete from 表名 where 条件; -- 删除数据，删除一条数据 -- 删除数据，删除表中所有数据 drop table 表名; -- 删除表就表示表中所有的数据都被删除 delete from 表名; -- 清空表中所有数据 truncate table 表名; -- 清空表中所有数据 查 - 查询数据 select * from 表名; -- 查询表中所有数据
