DML. DCL语言

DML （Database Modification Language）数据操作语言

1
2
3
4
5
6
7
8
#更改密码调用password函数对密码进行加密
update mysql.user set password=PASSWORD('123') where user='root' and host='localhost'
调用函数就是函数名+()

#增：inster
#删：delete
#改：update
#查：select

数据的操作

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
#增：inster
库.表   #不在库里面
表      #在库里面

root@localhost:student > desc stu300;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| id        | int(11)             | NO   | PRI | NULL              | auto_increment |
| name      | varchar(20)         | NO   |     | NULL              |                |
| age       | tinyint(4) unsigned | NO   |     | NULL              |                |
| gender    | enum('m','f')       | NO   |     | m                 |                |
| phone     | char(11)            | NO   | PRI | NULL              |                |
| come_time | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+---------------------+------+-----+-------------------+----------------+

1、#不规范  往stu300表里面增加数据
root@localhost:student > insert into stu300 values(5,'ccc',23,'f',12312312388,now());   #就是按照顺序写所有字段，少一个都不行

2、#规范写法  往stu300表里面增加数据
root@localhost:student > insert into stu300(name,age,phone,class) values('ddd',18,12312312399);       #你要往哪个字段添加数据，就写上哪个字段

3、#插入多条数据
inster stu2 表名(字段1,字段2,字段3)  values(1，2，3),(1,2,4),(1,3,5);

root@localhost:student > insert into stu300(name,age,phone) values('eee',27,2312312389),('fff',27,2313312389),('bbb',27,3312312389);

4、#into可以省略
inster 表名(字段1,字段2,字段3)  values(1，2，3); 

root@localhost:student > insert stu300(name,age,phone) values('ggg',21,2333312389);

手机号位数不对，都是前端打印的，数据库做不到手机号位数不对报错 jwt匹配库


###删：delete + where
root@localhost:student > select * from stu300;                               
+----+------+-----+--------+-------------+---------------------+
| id | name | age | gender | phone       | come_time           |
+----+------+-----+--------+-------------+---------------------+
|  1 | aaa  |  13 | m      | 12345465666 | 2024-08-12 21:20:05 |
|  3 | cc   |  12 | m      | 23456781234 | 2024-08-12 21:22:25 |
|  5 | ccc  |  23 | f      | 12312312388 | 2024-08-13 14:08:27 |
|  6 | ddd  |  18 | m      | 12312312399 | 2024-08-13 14:10:08 |
|  7 | eee  |  27 | m      | 2312312389  | 2024-08-13 14:12:43 |
|  8 | fff  |  27 | m      | 2313312389  | 2024-08-13 14:12:43 |
|  9 | bbb  |  27 | m      | 3312312389  | 2024-08-13 14:12:43 |
| 10 | ggg  |  21 | m      | 2333312389  | 2024-08-13 14:15:08 |
+----+------+-----+--------+-------------+---------------------+

dellete删除必须接条件  打完delete 就要打where
delete from 表 where id=5 
1、#不规范的写法,谨慎使用，会删除表里面的全部数据
delete from stu300;

2、#规范写法  PRIMARY KEY (`Host`,`User`) 联合主键，就要2个组件一起删除
root@localhost:student > delete from stu300 where id=8;

3、#多条件 and  
root@localhost:student > delete from stu300 where id=10 and name='ggg';

4、#为了养成一个好习惯delete + where，若要清除全部数据，表名不清除
root@localhost:student >delete form stu1 where 1=1;  #接一个永远成立的条件




#改：update + where
1、#不规范写法  如果不加where 所有的name都叫一样的名字
update stu300 set name='lisi' 

2、#将id为1的用户改名为张三
root@localhost:student > update stu300 set name='zhangsan' where id=1;

3、#将id是7，和9 的用户名字改为李四
root@localhost:student > update stu300 set name='lisi' where id=7 or id=9;

4、#要全部改，后面接一个成立的条件
update stu300 set name='zhang3' where 1=1;

#查：select
select * from stu300;

select * from stu300 where status='1';

使用update代替delete代替伪删除

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
1、#需要添加一个状态列(添加一个表示状态的字段)
root@localhost:student > alter table stu300 add status enum('0','1') not null default '0' comment '皮肤购买状态,0代表未购买，1代表已购买';


2、#大家都花钱了 买了
root@localhost:student > update stu300 set status='1' where 1=1;
root@localhost:student > select * from stu300;
+---+----------+-----+--------+-------------+---------------------+--------+
| id| name     | age | gender | phone       | come_time           | status |
+---+----------+-----+--------+-------------+---------------------+--------+
|  1| zhangsan |  13 | m      | 12345465666 | 2024-08-12 21:20:05 | 1      |
|  3| cc       |  12 | m      | 23456781234 | 2024-08-12 21:22:25 | 1      |
|  5| ccc      |  23 | f      | 12312312388 | 2024-08-13 14:08:27 | 1      |
|  6| ddd      |  18 | m      | 12312312399 | 2024-08-13 14:10:08 | 1      |
|  7| lisi     |  27 | m      | 2312312389  | 2024-08-13 14:12:43 | 1      |
|  9| lisi     |  27 | m      | 3312312389  | 2024-08-13 14:12:43 | 1      |
+---+----------+-----+--------+-------------+---------------------+--------+

3、#使用update代替delete   张三买的皮肤赠送给别人了 
root@localhost:student > update stu300 set status='0' where id=1;

4、#查询的时候接上条件
root@localhost:student > select * from stu300 where status='1';
+----+------+-----+--------+-------------+---------------------+--------+
| id | name | age | gender | phone       | come_time           | status |
+----+------+-----+--------+-------------+---------------------+--------+
|  3 | cc   |  12 | m      | 23456781234 | 2024-08-12 21:22:25 | 1      |
|  5 | ccc  |  23 | f      | 12312312388 | 2024-08-13 14:08:27 | 1      |
|  6 | ddd  |  18 | m      | 12312312399 | 2024-08-13 14:10:08 | 1      |
|  7 | lisi |  27 | m      | 2312312389  | 2024-08-13 14:12:43 | 1      |
|  9 | lisi |  27 | m      | 3312312389  | 2024-08-13 14:12:43 | 1      |
+----+------+-----+--------+-------------+---------------------+--------+

当前端拿到status是0的时候就是没有皮肤，前端怎么写是前端的事，update并没有删除，只是接了条件状态筛选了，如果真有客户要求恢复，把状态页修改一下就可以，数据并没有真正的删除，这样的效率比较高

#selete * 不接条件所有数据都还在

DCL(Databases Control Language)数据控制语言

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
1、#给开发授权
root@localhost:student > grant insert,update,select on student.* to dev@'%' identified by '123';

2、#with grant option赋予用户grant权限，一般超级用户可以给他
root@localhost:student > grant all  on  *.* to leader@'%' identified by '123' with grant option;


3、#revote 回收权限  #企业中很少用
查看dev雅虎的权限
root@localhost:student > show grants for dev@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for dev@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE ON `student`.* TO 'dev'@'%'                                           |
+----------------------------------------------------------------------------------------------------+
 
4、#回收dev用户的select权限
root@localhost:student > revoke select on student.* from dev@'%';

5、#再次查看权限
root@localhost:student > show grants for dev@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for dev@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT INSERT, UPDATE ON `student`.* TO 'dev'@'%'                                                   |
+----------------------------------------------------------------------------------------------------+


#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量


1h执行一次查询语句  连接数据库的时候会进行一次查询
5、# max_queries_per_hour：一个用户每小时可发出的查询数量
grant insert,update,select on dev.* to dev3@'%' identified by '123' with
max_queries_per_hour 3;

6、# max_updates_per_hour：一个用户每小时可发出的更新数量，update先查询在修改
grant insert,update,select on dev.* to dev4@'%' identified by '123' with
max_queries_per_hour 3 max_updates_per_hour 1;

7、# max_connections_per_hour：一个用户每小时可连接到服务器的次数
grant insert,update,select
on dev.*
to dev5@'%'
identified by '123'
with max_queries_per_hour 3
max_updates_per_hour 1
max_connections_per_hour 1;

8、# max_user_connections：允许同时连接数量
grant insert,update,select
on dev.*
to dev6@'%'
identified by '123'
with max_user_connections 1;

DQL （Database Query Language）数据查询语言

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1、下载数据并导入数据库
[root@db01 ~]# wget http://test.driverzeng.com/MySQL_File/world.sql
[root@db01 ~]# mysql -uroot -p123 < /root/world.sql 

#查看有几张表，几个库，每个表的结构 字段是什么意思
库：world
root@localhost:world > show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+

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
#show
1、#查看所有库
root@localhost:student > show databases;

2、#查看当前库下的所有表
root@localhost:student > show tables;
root@localhost:student > show tables from student;

3、#查看建库语句
root@localhost:student > show create database student;

4、#查看建表语句
root@localhost:student > show create table stu300;

5、#查看创建用户的语句
root@localhost:student > show grants for dev@'%';

6、#查看MYSQL自带的存储引擎
root@localhost:student > show engines;

7、#字符集
root@localhost:student > show variables like '%char%';

8、#查看字符集支持的校验规则
root@localhost:student > show collation;

9、#查看sql语句执行状态，拿这个命令查看哪条sql语句执行的慢
root@localhost:student > show processlist;
root@localhost:student > show full processlist;
SHOW FULL PROCESSLIST是SHOW PROCESSLIST的一个扩展，它提供了更多信息

10、#查看mysql内置所有的变量
root@localhost:student > show variables;



###desc 
1、#查看表结构
root@localhost:student > desc stu1;

#desc可以做分析，分析语句，慢查询语句
desc select * from stu1;


表结构：建表的要求
表空间：就是在表里面存储的数据

select查询

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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
#select（比较难）

root@localhost:student > use world;

1、#全表扫描
root@localhost:world > select * from city;  #这个语句不要出现，就好比一个cat一个大文件，会占用内存

2、#全表扫描，接条件
root@localhost:world > select * from city where id=10;

3、#多条件查询 or 中国和美国的
root@localhost:world > select * from city where CountryCode='CHN' or CountryCode='USA';

root@localhost:world > select * from city where CountryCode='and' or CountryCode='afg';


4、#成员查询
root@localhost:world > select * from city where CountryCode in ('CHN','USA');
#多条件和成员查询效率一样的

5、#联合查询     效率最高 联合查询，他扫描表的方式是：...
root@localhost:world > select * from city where CountryCode='CHN' union all select * from city where CountryCode='USA';

6、#范围查询 >、<、=、!=、>=、<=
root@localhost:world > select * from city where id<10;

7、#模糊查询
sroot@localhost:world > select * from city where CountryCode like '%H%'; #只要带H的就可以，不管H在前面还是后面，%不占位
'%H'  结尾
'H%'  H开头

8、#排序查询  order by
#查询CountryCode带H的，按人口数量排序  默认正序
root@localhost:world > select * from city where countrycode like '%H%' order by Population;
#倒序
root@localhost:world > select * from city where countrycode like '%H%' orfer by Population desc;

以class降序查询Student表的所有记录。
select * from stu1 order by class desc;

以sno升序、mark降序查询Score表的所有记录
select * from score order by sno,mark desc;

9、#分页查询
京东  一页商品60个，如果把所有商品加载出来，肯定不行，所以京东展示商品的时候是使用了分页查询
select * from city limit 60,60 ;  
select * from city limit 120,60;
select * from city limit 180,60;
写变量自增60，所以一页商品显示60个

#查看前10个数据
root@localhost:world > select * from city limit 10; 查看前10个数据  20 就是查看20个


root@localhost:world > select * from city limit 1;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | 城市名 | 国家代码     | 省       |    总人口数 |
+----+-------+-------------+----------+------------+
#分组查询 group by  先了解函数
MySQL自带的函数：
password()
now()
distinct()
#group by + 聚合函数   聚合函数种类：
max()：最大值
min()：最小值
avg()：平均值
sum()：求和
count()：统计有几行数据
#口诀
1、遇到统计想函数
2、形容词前group by
3、函数中央是名词
4、列名select后添加

10、#统计世界上每个国家的总人口数据
分析：我要的是国家、人口数 这两个字段
1、遇到统计想函数        sum()
2、形容词前group by     '国家的'是形容词 ..的  ..的   group by CountryCode
3、函数中央是名词        sum(Population)  形容词后面是名词，所以名词就是"总人口数"
4、列名select后添加     国家、人口数 这两个字段：select CountryCode Population

select CountryCode,sum(Population) from city group by CountryCode;

11、#统计中国各个省的人口数量
分析：我要的是国家、省、人口数 这3个字段
1、遇到统计想函数       sum()
2、形容词前group by    group by District
3、函数中央是名词       sum(Population)
4、列名select后添加    select CountryCode District sum(Population)

select CountryCode,District,sum(Population) from city where CountryCode='CHN' group by District;

12、#统计每个国家的城市数量  
1、遇到统计想函数        count()
2、形容词前group by     group by CountryCode
3、函数中央是名词        count(Name)
4、列名select后添加     select CountryCode,count(Name)

select CountryCode,count(Name) from city group by CountryCode order by count(Name);

13、#统每个国家的每个省的城市数量(练习)
1、遇到统计想函数         count()
2、形容词前group by      group by CountryCode,District 形容词国家的,省的
3、函数中央是名词         count(Name)
4、列名select后添加

select CountryCode,District,count(Name) from city group by CountryCode,District order by count(Name);

14、#统中国的每个省的城市数量(练习)
1、遇到统计想函数         count()
2、形容词前group by      group by CountryCode,District 形容词中国的,省的
3、函数中央是名词         count(Name)
4、列名select后添加       

select CountryCode,District,count(Name) from city where CountryCode='CHN' group by CountryCode,District  order by count(Name);