mysql基础

DDL

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
# 创建数据库

create database hkjcpdd;

# 当数据库存在时忽略,不存在时创建

create database if not exists hkjcpdd;

# 创建数据库并制定utf8编码

create database hkjcpdd charset='utf8';

# 显示所有数据库

show databases;

# 使用数据库

use hkjcpdd;

# 删除数据库

drop database hkjcpdd;

# 创建表

create table hkjmjj(
id int primary key; # 主键约束
username varchar(100);
) engine=InnoDB default charset=utf8mb4

# 查看当前库的所有数据表

show tables;

# 查看指定表的结构

desc hkjmjj;

# 修改表名

rename table 旧表名 to 新表名;

# 删除表

drop table hkjmjj;

# 删除表,没有就不做操作

drop table if exists hkjmjj;

# 查看建表

show create table hkjmjj;

# 数据类型

# 整数:tinyint smallint mediumint int bigint

# 小数:decimal double float

# 字符串:varchar(不定长) char(定长) text

# 日期时间:date(年月日) time(时分秒) datetime(年月日时分秒)

# 数据约束

# 主键:primary key

# 非空:not null

# 唯一:unique:该字段的指不允许重复

# 默认:default :当不填写字段对应的指时候会使用默认值

# 外键:foreign key

# 自增:auto_increment

# 表字段操作
# 添加表字段
alter table hkjmjj add 列名 类型(长度) [约束];

# 修改表字段
alter table hkjmjj change 旧列名 新列名 类型(长度) 约束;
# 修改表字段约束
alter table hkjmjj modify 旧列名 新的数据类型 [新的约束]

# 删除表字段
alter table hkjmjj drop 列名;

DML

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
# 添加表记录
# 不指定
insert into hkjmjj values(值1, 值2, 值3...);
# 指定字段插入
insert into hkjmjj (字段1,字段2,字段3) values(值1,值2,值3);
# 不指定字段插入,一次性插入多条记录
insert into hkjmjj values(值1,值2,值3), (值1,值2,值3);
# 指定字段插入,一次性插入多条记录
insert into hkjmjj (字段1,字段2,字段3) values(值1,值2,值3), (值1,值2,值3);

# 更新表记录
update hkjmjj 字段名=值, 字段名=值,...;

# 更新满足条件的行
update hkjmjj set 字段名=值,字段名=值... where 条件;

# 删除表记录
delete from hkjmjj where 条件;
# 删除表中的所有数据,主键自增序列不清零
delete from 表名;
# 清空表数据,主键自增序列清零
truncate table 表名;

# 扩展:备份数据表
create table 备份表名 select * from hkjmjj where ...;
# 备份表存在,就往里面插数据
insert into 备份数据表 select * from hkjmjj where ...;

DQL

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
60
61
62
63
64
65
66
# 格式
select
[distinct] 列名 [as] 别名...
from
数据表名
where
组前筛选
group by
分组字段
having
组后筛选
order by
排序[asc desc], 排序的列2
limit
起始索引, 数据条数;

# 查询表中所有的数据
select * from 表名;
# 查询表中的指定列数据
select1, 列2, ... from 表名;
# 查询表中的指定列数据并给结果列起别名
select1 as 别名 from 表名;
# 查询表中的指定数据
select 表名.列1 from 表名;
# 查询表中指定的数据,给表起别名
select1 from 表名 as 表别名;

# 条件查询
select * from hkjmjj where 条件;

# 排序查询
select * from hkjmjj order by 排序的列 desc;

# 聚合函数
count(col): 计数
max(col): 最大值
min(col): 最小值
sum(col): 总和
avg(col): 平均数

# 聚合查询
select
分组字段,
聚合字段
from
表名
group by 分组字段, ...;
having
组后筛选

# wherehaving的区别是什么?
where 用于组前查询
having用于组后筛选
# 后边是否能跟聚合函数主要区别

# 去重查询
select
distinct name, price
from hkjmjj;

# 分页查询
select
字段列表
from 表名
limit M, N; # M是起始索引(当前页数 - 1 * N),N是总条数

多表关系

1
2
3
4
5
6
7
8
# 外键约束设置	(插入数据的时候不允许添加主表主键不存在的数据)
constraint foreign key (外键字段) references 主表名(主键);

# 删除外键约束
alter table 外表名 drop foreign key 外键约束名;

# 建表后添加外键约束,要求是,表之间数据必须合法
alter table 数据表名 add constraint foreign key (外键字段) references 主表名(主键);

多表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 有条件的查询
内连接 join in
左连接 left join
右连接 right join
全连接 full join我们可以用union合并在一起就是把左外和右外和一起

# 以下两种最好使用连接的,效率更高因为,上面的会在底层创建一个临时表所以效率不高
mysql> select name, price, category_id from product where price = (select max(price) from product);
+------------------+-------+-------------+
| name | price | category_id |
+------------------+-------+-------------+
| 华为MateBook X | 7999 | C001 |
+------------------+-------+-------------+
1 row in set (0.003 sec)

mysql> select * from product p join (select max(price) price from product) t1 on p.price = t1.price;
+----+------------------+-------+-------------+-------+
| id | name | price | category_id | price |
+----+------------------+-------+-------------+-------+
| 52 | 华为MateBook X | 7999 | C001 | 7999 |
+----+------------------+-------+-------------+-------+
1 row in set (0.001 sec)

窗口函数

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
# case when用法
# 格式1:通用与法:
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
when 条件4 then 结果4
...
else 结果n
end [as 别名]

# 格式2:针对于格式1的语法,要通过两点 -> 1.都是操作同一个字段。2.都是等于判断
case 字段名
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
when 条件4 then 结果4
...
else 结果n
end [as 别名]

# 例子
select *, case
when category_id = 'c001' then '电脑'
when category_id = 'c002' then '服装'
when category_id = 'c003' then '化妆品'
when category_id = 'c004' then '零食'
when category_id = 'c005' then '饮料'
else '未知'
end as categrory_name from product;