0. 认识

0-1 软件版本

MySQL版本：5.7.37

MySQL Command Line Client —> Hyper（3.2.0）[推荐]

0-2 使用

启用

mysql -u root -por mysql -u root -p123456(直接加密码)

image-20220303193717103

开/关服务

net start mysql57

image-20220303193613529

net stop mysql57

image-20220303193643745

显示编码集

show variables like 'character_set_%;'

如果出现编码问题，则将 client & results 改为gbk

set character_set_client=gbk;

1. 库的操作

Database

显示 show

show databases; -> 显示所有库；

创建 create

create database student; -> 创建库；

高级 create database if not exists teacher;

更高级 create database if not exists school charset=gbk -> 设置编码为GBK Windows使用，一般设置为utf8

删除 drop

drop database student -> 删除库

高级 drop database teacher if exists

修改 alter

alter database school charset=GBK; -> 修改编码集

show create database school -> 查看修改后的编码集

2. 表的操作

Table

查 show/desc/select

表
1
2
3
4
5
6
7
show tables;

show create table students;
# 创建表的过程

desc teacher; -> describe
# 查看表内字段的类型等
数据

select * from teacher;

创 create

1
2
3
4
5
6
create table students(
	-> id int,
	-> name varchar(30),
	-> age int
    	# 最后一个字段后无需加';'
);

高级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table students(
	-> id int auto_increment primary key comment '主键id',
    	# id 字段
        # auto_increment 自动增长
    	# 自动增长的表必须为主键
    	# 主键删除后无法再使用
    	# primary key 主键
    	# comment 注释
	-> name varchar(30) not null,
    	# 不能为空
	-> phone varchar(20) comment '电话号码',  
    -> address varchar(100) default '暂时未知' comment '住址'
    	#default 暂时未知
    -> )engine=innodb;
);

删 drop/delete

表
1
2
3
drop table students
->
drop table if exists stu,tea,play;
数据
1
2
3
4
5
6
7
8
9
delete from teacher where id=4;

-> where 限定删除
delete from student where age > 30;

-> 全部删除
delete from student; # 不建议，全部遍历，比较慢
# or
truncate table student; # 清空所有表项

改 alter/update

修改字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-> 增加字段
alter table student add phone varchar(20);

-> 增加字段在制定字段之后
alter table student add gender varchar(1) after name;

-> 增加字段在首位（key word之后）
alter table student add address varchar(100) first;

-> 删除字段
alter table student drop address;

-> 修改字段名&类型
alter table student drop address;

-> 修改类型
alter table student modify tel_phone varchar(13);

-> 修改表名
rename table student to students;
# or
rename table student to students;
修改数据
1
update teacher set name='Tom' where id=5;

增 insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
	insert into teacher(id,name,phone,address) values(1,'Bayyy','13866668888','Qingdao');
	-> 字段与数据对应即可

ps1: insert into teacher (phone,name) values ('13688886666','bay');
	-> 乱序&可省略单元

ps2: insert into teacher values(3,'Tom','13344445555','New York');
	-> 按顺序可不加字段名

ps3: insert into teacher values(null,'Tom',null,default);
	-> null & default

ps4: insert into teacher values(null,'bay_1',null,default),(null,'bay_2',null,default);
	-> 直接插入多条数据

3. 数据类型

3-1 整数类型

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-2^7^，2^7^-1) (0，2^8^) 小整数值
SMALLINT 2 Bytes (-2^15^，2^15^-1) (0，2^16^) 大整数值
MEDIUMINT 3 Bytes (-8×10^6^，8×10^6^) (0，1×10^7^) 大整数值
INT或INTEGER 4 Bytes (-2×10^9^，2 ×10^9^) (0，4 ×10^9^) 大整数值
BIGINT 8 Bytes 极大整数值
FLOAT 4 Bytes 单精度 浮点数值
DOUBLE 8 Bytes 双精度 浮点数值
DECIMAL M+2 / D+2 依赖于M和D的值 依赖于M和D 小数值 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table emp(
    -> id smallint unsigned auto_increment primary key comment 'id',
    -> age tinyint unsigned default '0' comment '年龄', 
    -> second int unsigned comment '活了几秒');
    
create table tab1(
    -> num1 float(3,1),
    -> num2 double(5,2));
    	# 需要标注浮点位数
    	# '5':总位数 '2':小数位

 create table bank(
    -> money decimal(20,20));
    	# 定点小数

3-2 小数类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串(*)
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

3-3 布尔类型

1
2
3
4
create table t_4(
-> buer boolean);

insert into t_4 values(true);

3-4 枚举类型

1
2
3
4
5
6
7
create table t_5(
   -> gender enum('man','women','?','it'));
   
   insert into t_5 values('man');
   
   insert into t_5 values(1);
   	# 可用整数代替（从1开始）

3-5 set 类型

1
2
3
4
create table t_6(
   -> hobby set('play','read','study','live'));
   
   insert into t_6 values('read,play');

3-6 日期和时间类型

<表内一般都要设置日期和时间>

类型 大小 ( 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
mysql> create table t_7(
    -> createdTime datetime);
	
mysql>insert into t_7 values('2021-03-02 11:00:00');

4. 属性约束

1
2
3
4
5
6
7
8
NOT NULL # 不能为空
DEFAULT 'default' # 默认
PRIMARY KEY # 主键
	# 删除后该主键不能使用
	# 唯一性、非空
	# 快速搜索
auto_increment # 自动增长
	# 自增属性必须为主键

4-1 主键

primary key 主键

  • 删除主键 alter table t_8 drop primary key;
  • 组合键 alter table t_8 add primary key(id, name);

unique 唯一键

  • 一张表可以有多个 -> 保证唯一
  • 查看 show create table 't_10'
  • 组合唯一键

删除 alter table t_9 drop index name

4-2 外键

创建

1
2
3
4
5
6
7
8
9
	# 创建时添加
mysql>create table eat(
    -> id int primary key,
    -> money decimal(10,4),
    -> stuID int(4),
    -> foreign key (stuID) references stu(stuID));
    
    # 后期添加
mysql>alter table eat add foreign key (stuID) references stu(stuID);

删除外键

需要首先查看外键的constraint值，删除此项

1
2
	# 查看表的属性
mysql>show creaete table eatery;

image-20220304130855822

1
2
	# 删除外键
mysql>alter table eatery drop foreign key eatery_ibfk_1;

外键的三种操作

置空：置为Null（一般删除选择）

级联：绑定外键数据全部更新/删除（一般更新选择）

1
2
3
4
5
6
mysql> create table eat(
    -> id int(20) primary key,
    -> money decimal(10,4),
    -> stuID int(4),
    -> foreign key(stuID) references stu(stuID) on delete set null on update cascade);
    	# mysql 对于varchar类型大小写不敏感？update时会重复！！

5. 查询

5-1 单表查询

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
1.as
mysql> select 'name_1' as 'name_2';	# 字段重命名

2.from	# 笛卡尔积
mysql> select * from t1; 
+------+------+
| id   | name |
+------+------+
|    1 | bay  |
|    2 | tom  |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t2; 
+--------+--------+
| score1 | score2 |
+--------+--------+
|    150 |     90 |
|     80 |    140 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from t1,t2;
+------+------+--------+--------+
| id   | name | score1 | score2 |
+------+------+--------+--------+
|    1 | bay  |    150 |     90 |
|    2 | tom  |    150 |     90 |
|    1 | bay  |     80 |    140 |
|    2 | tom  |     80 |    140 |
+------+------+--------+--------+
4 rows in set (0.00 sec)

3.where
3.1 in
mysql> select * from t4 where address='shanghai' or address='beijing';
	# or
mysql> select * from t4 where address in('beijing','shanghai');
	# or
mysql> select * from t4 where address not in('beijing','shanghai');

3.2 between
mysql> select * from student where age between 18 and 20;

3.3 is null/ is not null
mysql> select * from teacher where phone is null;

3.4 聚合函数
sum()/avg()/min()/max()/count()/

*客户端的使用

软件：Navicat Premium

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
4 模糊查询 like
mysql> select * from student where name like '张%';
		# % 多个字符
mysql> select * from student where name like '张_';
		# _ 一个字符
		
5 排序 order by asc/desc
	# 升序
mysql> select * from score order by chinese asc; 
	# 降序
mysql> select * from score order by chinese desc; 

6 分组查询 group by
	# 必须是聚合+多组查询
mysql> select avg(age) as '年龄',gender as '性别' from info group by gender;
	# 聚合分组
select group_concat(id),address from info group by address;

7 筛选 having
	# 对结果进行筛选
mysql> select avg(age) as age, address as '地区' from info group by address having age>23; 

8 limit
mysql> select * from info limit 0,2;  # 开始 ，跨度

9 distinct / all
mysql> select distinct address from info;   # 默认是 all

5-2 多表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1 union
mysql> select name from student union select Chinese from score;

2 inner join on	# 内连接
mysql> SELECT name, Chinese from student INNER JOIN score on student.id=score.stuID;

3 left join on	# 左连接

4 right join on	# 右连接

5 cross	join on# 交叉

6 natural join on # 自然连接 
	# 自动匹配字段

5-3 子查询

1
2
mysql> select * from student where id in (select stuID from score where Chinese >= 50);
	# exists 存在 (存在就输出整表)

6. 选修内容

6-1 视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1 创建
mysql> CREATE VIEW vw_stu2 as SELECT name, age, Chinese from student inner join score on student.id=score.stuID;

2 查看
mysql> desc vw_stu;
# or
mysql> show create view vw_stu;
# or
mysql> show table status where comment='view' \G;	# 查看所以引擎名

2 修改
mysql> alter view vw_stu as select name from student;

3 删除
mysql> drop view vw_stu;

4 视图算法
mysql> create algorithm = temptable view vm_stu select * from student;  # undifined/temptable/merge

6-2 事务

6-2-1 事务四大特性

ACID （引擎必须在innodb状态下！）

A：atomicity 原子性

C：consistency 一致性

I：isolation 隔离性

D：durability 持久性

6-2-2 指令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1 基础指令
1.1 开启事务
	start transaction；

1.2 回退
	rollback；
	
1.3 提交
	commit；
	
2 回滚点
2.1 设置回滚点
	savepoint four;
	
2.2 回到指定回滚点
	rollback to four；

6-3 索引

1
2
3
4
5
6
7
# index
	create index index_name on table(name);

# unique index	# 唯一索引
	create unique index index_name on table(name);
	
# draop/ alter

6-3 存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 修改程序语句结尾为//
	delimiter //
# 创建	
mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> update wallet set balance=balance+50;
    -> update t1 set name='dan';
    -> end//
mysql> delimiter ;
mysql> call proc();

# 删除
mysql> drop procedure proc;

# 显示
mysql> show create procedure proc;

6-4 小技巧

①number

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
1 随机数
mysql> select rand();

2 抽奖
mysql> select * from student order by rand() limit 3;

3 取整
ceil #上/ floor #下/ round # 四舍五入/ truncate # 截取数字
mysql> select truncate(3.1415926,2);

4 随机排序
mysql> select * from student order by rand();

5 大小写转换
mysql> select ucase('bay');
mysql> select lcase('BAY');

6 截取字符串
mysql> select left('BAYYY!',2);
mysql> select right('BAYYY!',2);

7 拼接字符串
mysql> select concat('123','bayyy');
	# 使用
mysql> select concat(name,'|',age) from student;

8 时间
mysql> select now();
mysql> select unix_timestamp();
mysql> select year(now()) year, month(now()) month, day(now()) day;

9 加密函数
mysql> select sha('bay');

7. 企业规范约束

7-1 库表字段规范约束

  • 字段为”是/否“含义

    • name=is_name
    • type=unsigned tinyint
    • long=1

    非负必须设置为unsigned

  • 字段名

    • 必须以小写字母开头
    • 不能出现数字
    • 不能出现大写字母
    • 不同单词以下划线 ‘_’ 分开

  • 表名

    • 不能出现负数
    • 不能出现关键字

  • 索引名

    • 主键 pk_xxx
    • 唯一键 uk_xxx
    • 普通索引名 idx_xxx

  • 小数类型

    • decimal！

  • 字符串类型

    • 长度很小则选用 char

  • 表内必须定义三个字段

    • id、create_time、update_time
    • id type=bigint、主键、非空、自增
    • time type=datatime

7-2 索引规范

7-3 SQL开发约束

  • 选用count（*） —> count（xxx、xxx……）（×）
  • 不能用 = 判断 null —> is null（√）

  • 高并发集群操作不能使用外键（级联）！

  • 实际开发中不得使用存储过程！

  • 删除更新操作前，先进行查询操作，确保数据正确性
  • in 操作尽可能避免
  • gbk（×） —> utf8（√）

7-4 其他约束

  • 实际开发中不能使用*进行查询
  • @Transaction框架尽可能避免