MySQL的相关操作
目录
一. 字符串函数
二. group by分组
2.1 作用
2.2 格式
2.3 举例
三. order by排序
3.1 格式
3.2 举例
四. limit
4.1 作用
4.2 举例
五. having
5.1 作用
5.2 举例
六. 正则表达式
七. 多表查询
7.1 定义
7.2 子查询
7.3 联合查询 纵向合并
7.4 交叉连接 横向合并（不常用）
7.5 内连接
7.5 外连接
7.6 自连接
7.7 三表查询
八. 视图
8.1 创建视图
8.2 删除视图
九. 函数
十. 存储过程（了解）
十一. 用户管理
11.1 新建用户
11.2 查看用户信息
11.3 重命名用户
11.4 删除用户
11.5 修改当前登录用户密码
11.6修改其他用户密码
11.7 破解密码
11.8 远程登录
十二. 数据库用户授权
12.1 授予权限
12.2 查看权限
12.3 撤销权限
一. 字符串函数
| 函数名 | 函数意义 |
|---|---|
| concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
| substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串 |
| substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串 |
| length(x) | 返回字符串 x 的长度 |
| replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
| left(x,y) | 返回字符串 x 的前 y 个字符 |
| right(x,y) | 返回字符串 x 的后 y 个字符 |
| repeat(x,y) | 将字符串 x 重复 y 次 |
| space(x) | 返回 x 个空格 |
| strcmp(x,y) | 比较x和y，返回的值可以为-1,0,1 |
| reverse(x) | 将字符串x反转 |
| upper(x) | 将字符串 x 的所有字母变成大写字母 |
| lower(x) | 将字符串 x 的所有字母变成小写字母 |
示例：
#concat
select concat(name,classid) from students where stuid=1; #将姓名字段和班级字段合在一起
select concat(name,' ',classid) from students where classid=1; #加空格 
#substr
select substr(name,1,3) from students;
#获取students表中name字段的前1到3个字符select substr(name,3) from students where stuid=25;
#从students表中查找stuid为25的学生记录,提取该学生name字段的子字符串，从第3个字符开始直到结束select substr(name,1,3) from students where stuid=25;
#从students表中查找stuid为25的学生记录,提取该学生name字段的子字符串，从第1个字符开始，总共提取3个字符运行结果如下：
#了解select length(name) from students where stuid=1; #返回数据的长度
select replace(name,"y",11) from students where stuid=1; #将name字段中的y换成11
select left(name,3) from students where stuid=1; #显示name字段 左边三个字符 即最开始的三个
select right(name,3) from students where stuid=1; #显示name字段 右边三个字符 即最后三个
select repeat(name,2) from students where stuid=1; #将name字段重复显示2次
select lower(name) from students; #返回结果全是小写字母
select reverse(name) from students where stuid=1; #反向显示字符串二. group by分组
2.1 作用
根据指定的一个或多个列的值将查询结果分成多个组，通常与聚合函数(如COUNT, SUM, AVG, MAX, MIN等)一起使用
2.2 格式
SELECT "字段1", 聚合函数("字段2") FROM "表名" GROUP BY "字段1";2.3 举例
select classid from students group by classid; #以classid字段进行分组
select gender from students group by gender; #以性别进行分组
select age from students group by age; #以年龄进行分组select classid,count(*) from students group by classid; #按classid分组，并显示每一组的人数
select age,count(*) from students group by age; #按年龄分组，并显示每一组的人数select gender,avg(age) from students group by gender; #按性别分组，求男女的平均年龄运行结果如下
注意：凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面
#错误示例
select classid,name from students group by classid;
三. order by排序
3.1 格式
SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。3.2 举例
select * from students order by age; #年龄升序排列
select * from students order by age desc; #年龄降序排select * from students where classid=1 order by age; #找出1班的人按升序排序
四. limit
4.1 作用
对查询的结果进行输出行数数量限制
4.2 举例
select * from students limit 5; #取前5个数据
select * from students limit 3,5; #跳过前3个，往后取 5个
select * from students order by stuid desc limit 3; #显示最后三个 
五. having
5.1 作用
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足
5.2 举例
select classid,count(classid) from students group by classid having classid > 3;
#先对所有数据按classid分组，然后使用筛选出classid大于3的组。select classid,count(classid) from students where classid > 3 group by classid;
#先使用筛选出classid大于3的记录，然后再对这些记录按classid分组。
六. 正则表达式
匹配 描述
^ 匹配文本的开始字符
$ 匹配文本的结束字符
. 匹配任何单个字符
* 匹配零个或多个在它前面的字符
+ 匹配前面的字符 1 次或多次字符串 匹配包含指定的字符串
p1|p2 匹配 p1 或 p2
[…] 匹配字符集合中的任意一个字符
[^…] 匹配不在括号中的任何字符
{n} 匹配前面的字符串 n 次
{n,m} 匹配前面的字符串至少 n 次，至多 m 次
{,m} 最多m次
{n,} 最少n次
？ 匹配一个字符举例
select name from students where name regexp '^s'; #查找所有以字母's'开头的学生姓名
select name from students where name regexp 's'; #查找所有姓名中包含字母's'的学生select name from students where name regexp 's.i';
#查找姓名中包含模式's+任意一个字符+i'的学生
select name from students where name regexp '^s|l';
#查找所有以's'开头或者包含字母'l'的学生姓名
七. 多表查询
7.1 定义
多表查询是指在单个SQL查询中同时从两个或多个表中检索数据的操作。
7.2 子查询
定义
子查询是嵌套在另一个查询中的查询。
举例
select avg(age) from students; #求平均年龄
select * from students where age > (select avg(age) from students); #再找出比平均年龄大的
#类似的SELECT avg(Age) FROM teachers #先计算出 教师表的 平均年龄
update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25; # 再把第25个学生的年龄改成 教师表的平均年龄7.3 联合查询 纵向合并
返回两个表的笛卡尔积（即第一个表的每一行与第二个表的每一行的所有可能组合）。
格式：
-- 去除重复行
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;-- 保留重复行
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;举例：
select name,age from students union select name,age from teachers;
#将students表和teachers表中的name和age列合并在一起。 
#默认union是会自动去重的，union all是不会去重的select * from teachers union select *from teachers;
select * from teachers union all select *from teachers;
7.4 交叉连接 横向合并（不常用）
通过cross join操作将两个或多个表中的相关数据组合在一起，基于指定的连接条件。
交叉连接生成的记录可能会非常多,建议慎用
格式：
SELECT * FROM table1 CROSS JOIN table2;举例：
select * from students cross join teachers;
#students表和teacher表合并
7.5 内连接
返回两个表中满足连接条件的记录。
格式：
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;举例：
select * from students inner join teachers on students.teacherid=teachers.tid;
#学生表中的teacherid和教师表中的tid相同的行 
7.5 外连接
外连接可以分为：左连接和右连接
左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充
格式：
-- 左外连接：保留左表所有行
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;-- 右外连接：保留右表所有行
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;举例：
select * from students s left join teachers t on s.teacherid=t.tid;
#左外连接学生表都有，教师表留一部分select * from students s right join teachers t on s.teacherid=t.tid;
#右外连接教师表全部，学生表一部分
7.6 自连接
自连接, 即表自身连接自身
格式：
-- 使用内连接的自连接
SELECT a.column, b.column
FROM table a INNER JOIN table b ON a.column = b.related_column;-- 使用左外连接的自连接
SELECT a.column, b.column
FROM table a LEFT JOIN table b ON a.column = b.related_column;举例：
#构建新表，如下图所示
create table emp (id int, name varchar(10),leaderid int);
insert emp values(1,'cxk',null),(2,'wyf',1),(3,'zhang',2),(4,'li',3);
select * from emp e left join emp l on e.leaderid=l.id;
#查找每个员工及其对应的领导信息。
7.7 三表查询
select * from students inner join scores on students.stuid=scores.stuid;
select st.name,sc.CourseID,sc.score from students st inner join scores sc on st.stuid=sc.stuid;
select st.name,co.CourseID,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;
select * from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;八. 练习
导入hellodb.sql生成数据库
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
select name,age from students where age>25 and gender='m'; 2. 以ClassID为分组依据，显示每组的平均年龄
select classid,avg(age) from students group by classid; 3. 显示第2题中平均年龄大于30的分组及平均年龄
select classid,avg(age) from students group by classid having avg(age) > 30; 4. 显示以L开头的名字的同学的信息
select *from students where name like "l%";5. 显示TeacherID非空的同学的相关信息
select * from students where TeacherID is not null; 6. 以年龄排序后，显示年龄最大的前10位同学的信息
select * from students order by age desc limit 10 ;7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
select * from students where age >= 20 and age <= 25; 8. 以ClassID分组，显示每班的同学的人数select classid,count(*) from students group by classid;9. 以Gender分组，显示其年龄之和
select gender,sum(age) from students group by gender;10. 以ClassID分组，显示其平均年龄大于25的班级
select classid,avg(age) from students group by classid having avg(age) > 25;11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
select gender from students where age >25 group by gender;八. 视图
将复杂的 SQL 查询封装成一个简单的虚拟表，用户只需查询视图即可获取所需数据，无需重复编写复杂的查询语句。
8.1 创建视图
语法：
create view 视图名字 as 查询语句 举例：
create view v1 as select * from students where stuid=5;
#创建一个查询stuid为5的学生的视图select * from v1;
#运行视图，效果与执行代码select * from students where stuid=5;一样
8.2 删除视图
语法：
drop view 视图名称举例：
drop view v1;
九. 函数
MySQL中的函数分为内置函数和自定义函数
系统内置函数参考网页：
https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html自定义函数
语法：
create function 函数名字
执行语句
return 举例：
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED)
RETURNS VARCHAR(20)
DETERMINISTIC
MODIFIES SQL DATA
BEGINDELETE FROM students WHERE stuid = id;RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;函数作用：
接收一个无符号小整数类型的参数id
从students表中删除stuid等于传入id值的记录
删除操作完成后，计算并返回students表中剩余的记录总数（作为VARCHAR(20)类型返回）
十. 存储过程（了解）
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量
存储过程与自定义函数的区别
存储过程实现的过程要复杂一些,而函数的针对性较强存储过程可以有多个返回值,而自定义函数只有一个返回值存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用无参数的存储过程执行过程中可以不加(),函数必须加 ( )
十一. 用户管理
11.1 新建用户
create user '用户名'@'源地址' identified by '密码';'用户名'
指定将创建的用户名'来源地址'
指定新创建的用户可在哪些主机上登录
可使用IP地址、网段、主机名的形式
本地用户可用localhost
允许任意主机登录可用通配符%'密码'
若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；若使用加密密码，需要先使用select password('密码'); 获取密文，再在语句中添加 password '密文';若省略“identified by”部分，则用户的密码将为空（不建议使用）11.2 查看用户信息
#创建后的用户保存在 mysql 数据库的 user 表里
use mysql;
select user,authentication_string,Host from user;11.3 重命名用户
rename user 'zhangsan'@'localhost' to 'lisi'@'localhost';11.4 删除用户
drop user 'lisi'@'localhost';11.5 修改当前登录用户密码
#修改当前用户的密码
set password = password('abc123');11.6修改其他用户密码
#方式一
set password for 'user1'@'localhost' = PASSWORD('abc123');#方式二
alter user '用户名'@'源地址' identified by '密码';#例子
alter user 'root'@'%' indentified by 'abc123'
11.7 破解密码
常用于忘记 root 密码
方法一. 修改 mysql配置文件/etc/my.cnf ，不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加，使登录mysql不使用授权表
skip-networking #MySQL8.0不需要systemctl restart mysqldmysql #直接登录方法二. 使用 update 修改 root 密码，刷新数据库
use mysql;update user set authentication_string=password('密码') where user='root';FLUSH PRIVILEGES;
quitmysql -u root -pabc123#注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。11.8 远程登录
mysql -utest -h要登陆的mysql服务IP -p'密码' -P端口号十二. 数据库用户授权
12.1 授予权限
GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。
grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [indentified by '密码'];#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。
#来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.byyb.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。#举例
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
#允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。
12.2 查看权限
show grants for 用户名@来源地址;#举个例子
show grants for root;12.3 撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;#例子
revoke all on *.* from zhou@"192.168.91.%";
#撤销用户"zhou"从192.168.91.x网段连接时对整个MySQL的所有权限。