SQL语句

1、新增

（1）insert into 库名称.表名

（2）Insert into 表名称 values（1，“name”,“word”）;

（3）Insert into 表名称 select * from 其他表；

（4）Insert into 表 value（）， （）

2、删除

（1）delete from 表名

（2）Delete from tablename where id=3;

（3）Delete from tablename where age>8;

（4）Delete from tablename where name on （“a”，“b”,“c”）;

3、修改

（1）update mysql.user set host=’%’ where name=”root”;

（2）Update user set password=’abc’ where username=”daning”;

4、查询

（1）select * from student；

（2）Select id,name from student；

远程连接数据库：url mysql ip | 域名 数据库端口 密码

一、聚合函数

1、概述

只有 select ⼦句和 having ⼦句、order by ⼦句中能使⽤聚合函数，where ⼦句不能使⽤聚合函数。当使⽤聚合查询以后，不能使⽤where条件，如果要添加条件，就使⽤having。

常⽤聚合``函数
统计表中数据的⾏数或者统计指定列不为空 值的数据⾏个数	select count(*或表头名) from 表名；
计算指定列的最⼤值，如果指定列是字符串 类型(⽂字类型)则使⽤字符⾸拼排序	select max(表头名) from 表名;
计算指定列的最⼩值，如果指定列是字符串 类型(⽂字类型)则使⽤字符⾸拼排序	select min(表头名) from 表名;
计算指定列的数值和，如果指定列不是数值 类型则计算结果为 0	select sum(表头名) from 表名;
计算指定列的平均值，如果指定列不是数值 类型则计算结果为 0	select avg(表头名) from 表名;

2、其他常⽤函数

（1）⽇期函数

⽇期时间函数
获取当前⽇期和时间	select now();（查看时间的，没什么⽤）
显示输⼊⽇期的年份	select year('date');（没⽤）例如：select year('1998-08-24');（只显示 1998 年）
计算输⼊的⽇期到年初的周数	select weekofyear ('date');（有点⽤但不多）例 如：select year('2023-01-31');（会显示 5，也 就是 5 周）
计算输⼊的⽇期到年初的天数	select dayofyear ('date');（有点⽤但不多）例 如：select dayofyear('2023-01-31');（会显示 31，也就是 31 天）
显示输⼊⽇期的⽉份值	select month('date');（没⽤）例如：select month('1998-08-24');（只显示 8 ⽉）
显示输⼊⽇期是⼏号	select day('date');（没⽤）例如：select day('1998-08-24');（只显示 24 号）
显示输⼊⽇期是⼀周中的第⼏天（从周⽇开始）	select dayofweek('date');（有点⽤但不多）例 如：select dayofweek('1998-08-24');（显示 2，也就是周⼀）
显示输⼊⽇期的⽉份的最后⼀天	select last_day('date');（没⽤）例如：select last_day('2023-09-24');（会显示 2023 年 9 ⽉最后⼀天是⼏号）
显示输⼊⽇期加多少天的结果	select adddate(current_date,需要加的天数);（有点⽤）例如：select adddate(current_date,44);（会显示当前⽇期加上 44 天的结果）
显示输⼊⽇期减多少天的结果	select subdate(current_date,需要减的天数); （有点⽤）例如：select subdate(current_date,44);（会显示当前⽇期减 去 44 天的结果）
显示两个⽇期中间隔了多少天	select datediff ('date1','date2');例如：select datediff ('2023-09-24','1998-08-24');（会显示 9162，也就是两个⽇期之间隔了 9162 天）
显示输⼊时间已经过了多少⼩时	select hour('time');（没⽤）例如：select hour('18:41:44');（会显示已经过去了 18 个⼩时）
显示输⼊时间已经过了多少分钟	select minute('time');（time 为时间标准格式， 没⽤）显示过了多少秒 select second('time');（time 为时间标准格式，没⽤）
显示输⼊⽇期时间的时间值	select time('datetime');（datetime：标准⽇期 格式，⾮常没⽤）显示输⼊⽇期 select date('datetime');（datetime：标准⽇期 格式，⾮常没⽤）

（2）数字函数

数字函数
显示⼩于输⼊数字的最⼤整数值或输⼊⼀个算式	select floor(数字);例： select floor(1+2-3*5/6);
显示⼤于输⼊数字的最⼩整数值或输⼊⼀个算式	select ceiling(数字);例： select ceiling(1+2-3*5/6);
显示带⼩数点数字的四舍五⼊的整数值或输⼊⼀个结果带⼩数点的算式	select round(数字);例： select round(651351/52);
保留多少位⼩数	select truncate(带⼩数点的数字，保留的位数);例：select truncate(12345.6789,2);（结果为12345.67）

（3）字符串函数

字符串函数
去掉字符串 str 开头的空格 select ltrim(str); （没⽤）	select ltrim(str); （没⽤）
去掉字符串 str 尾部的空格 select rtrim(str); （没⽤）	select rtrim(str); （没⽤）
去掉字符串⾸部和尾部的所有空格	select trim(str); （没⽤）
指定字符串的 x 位，y 个字符⻓的字 符串替换为其他字符	select insert('指定字符串',x,y,'替换字符串'); （有点⽤但不多）
将字符串从 x 位分开，分成 y 个字符⻓的字符串（x 可⽤负数，表示从右往左数）	select substring(str,x,y);（有点⽤但不多）

二、MySQL 分组查询

1、语法

（1）select 聚合函数(表头名 1),表头名 2 from 数据表名 group by 表头名;

（2）select 聚合函数(表头名) 临时表头名,真实表头名 from 数据表名group by 真实表头名;

（3）若⽤ group by 分组查询语句，必须加⼊聚合函数，否则报错（踩坑）

2、实例

mysql> create table 学⽣表(序号 int,姓名 char(16),年龄 int,班级 int);

Query OK, 0 rows affected (0.01 sec)



insert into 学⽣表 values(1,'tom',15,101);

insert into 学⽣表 values(2,'mike',16,102);

insert into 学⽣表 values(3,'john',14,103);

insert into 学⽣表 values(4,'lili',15,102);

insert into 学⽣表 values(5,'jack',15,101);

insert into 学⽣表 values(6,'xiaoming',17,103);

 三、MySQL ⼦语句查询

1、概述

（1）⼦查询是指⼀个查询语句嵌套在另⼀个查询语句内部的查询;

（2）该查询语句可以嵌套在⼀个 SELECT、SELECT...INTO、INSERT...INTO 等语句中。

（3）在执⾏查询时，⾸先会执⾏⼦查询中的语句,再将返回的结果作为外层查询的过滤条件。

（4）在⼦查询中通常可以使⽤⽐较运算符和 IN、EXISTS、ANY、ALL 等关键字。

例如：select * from class where cid=(select classid from

student where sname='张三');

2、⽐较运算符的⼦查询

⽤“=”、“<”、“>”这类⽐较运算符

 3、exists 关键字的⼦查询

exists 关键字后⾯的参数可以是任意⼀个⼦查询， 它不产⽣任何数据只返回 true 或 false。⽽当返回值为 true 时外层查询才会执⾏。

相当于内层句⼦是⼀个判断句式。

四、MySQL 多表联合查询

1、交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据⾏的笛卡尔积;

⽐如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)。所以，交叉连接也被称为笛卡尔连接。

语法：select * from 表1 cross join 表2;

 

2、多表联合查询

语法：select 查询字段1,查询字段2, ... from 表1 join 表2 join ...on 表1.关系字段=表2.关系字段=...；

join：联合；on：条件

五、MySQL 授权

1、MySQL 基础权限

权限	说明
all	设置 grant option 之外的所有权限（授权选项）
alter	允许使⽤ alter table（修改表的权限）
create	允许使⽤ create table（添加表的权限）
create user	允许使⽤ create user（添加⽤户权限）
delete	允许使⽤ delete（删除权限）
index	允许使⽤ index（索引权限）
insert	允许使⽤ insert（插⼊权限）
select	允许使⽤ select（选择权限）
update	允许使⽤ update（更新权限）
drop	允许使⽤ drop table（删除表权限）
replication slave	允许从主服务器中读取⼆进制⽇志⽂件
Show databases	允许显示所有数据库

2、权限语法

grant all on test.user

为客户机授权	grant all on 库.* to ⽤户@客户机地址identified by '密码';all：表示所有权限，可替换为其他权限库.*：表示某个数据库下所有数据表⽤户@客户机地址：表示希望给予哪个⽤户权限，前⾯的⽤户是临时⾃建⽤户，当客户机登录时，⽤该⽤户登录'密码'：临时⾃建⽤户的密码，也是临时⾃写的
查看给予客户机的权限	show grants for ⽤户@客户机地址
取消权限	revoke 权限列表 on 库.* from ⽤户@客户机地址；
刷新权限	flush privileges;（重新加载权限表）
删除临时用户	drop user '⽤户名'@'客户机地址';

客户机地址可以有以下表现形式：%：表示所有地址；192.168.33.%：表示 33 ⽹段下所有主机，⼀般为你想为哪个⽹段授权，或为⾃⼰⽹段下的主机授权

mysql> grant all on *.* to haha@'192.168.100.%' identified by '123456';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

mysql> drop user haha@'192.168.100.%';

Query OK, 0 rows affected (0.00 sec)

六、MySQL 触发器

1、概念

触发器是⼀种特殊的存储过程，它在插⼊，删除或修改特定表中的数据时触发执⾏，它⽐数据库本身标准的功能有更精细和更复杂的数据控制能⼒。

2、触发器语法

create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态

1. 语法的红字为固定格式
2. 触发器名称：⾃定义
3.  触发的时机：before(之前) 或 after(之后)，在执⾏动作之前还是之后
4. 触发的动作：指的激发触发程序的语句类型<insert,update,delete>（插⼊、更新、删除表或数据等）
5. each row：操作的每⼀⾏都会被监控
6. 触发器状态：在触发的动作之前或之后做什么事情，⽐如当我删了表 1 的某条数据后，⾃动清空表 2。

3、触发器创建的四要素

（1）监视地点：table（表）

（2）监视事件：insert（插⼊）、update（更新）、delete（删除）等动作

（3）触发时间：before（之前）、after（之后）

（4）触发事件：在监视事件之前或之后，对当前表或其他表的插⼊、更新、删除等动作。

4、触发器实例

mysql> create trigger deltable after delete on class for each row delete from student;

Query OK, 0 rows affected (0.00 sec)

#删除触发器

mysql> drop trigger deltable;

Query OK, 0 rows affected (0.00 sec)

七、MySQL 基本优化操作

1、忘记 MySQL 密码

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

skip-name-resolve #添加该⾏，表示本机跳过MySQL密码验证

skip-grant-tables #添加该⾏，表示登录时，忽略所有的⽤户认证信息，包括⽤户名、密码和权限。

[root@localhost ~]# systemctl restart mysqld.service

# mysql #免密时，直接使⽤MySQL命令登录

mysql> use mysql;

mysql> update user set

authentication_string=password('123') where user="root";

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

2、MySQL 存储引擎

查看数据库表使⽤的存储引擎	show table status where name='表名';
创建表并指定存储引擎	create table 表名(表头名) engine=存储引擎;
修改已经存在表的存储引擎	alter table 表名 engine=存储引擎;
修改 MySQL 服务的默认存储引擎

（1）查看数据库使⽤的存储引擎

mysql> use yh; #查看存储引擎时，必须要切换到某个数据库

 

（2）创建表并指定存储引擎

mysql> create table haha(id int) engine=MyISAM;

#创建表haha，并添加数字形式的表头id，指定haha表的存储引擎为MyISAM

（3）修改已存在数据表的存储引擎

mysql> alter table haha engine=InnoDB; #修改已存在的数据表haha的存储引擎

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

（4）修改 MySQL 服务的默认存储引擎

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

default-storage-engine=InnoDB #添加此⾏，当以后再创建表时，存储引擎将改为InnoDB