01. MySQL其他操作
01. MySQL其他操作
1. MySQL内置函数
1. 字符串函数
拼接字符串 concat(str1,str2...)
例 1：把 12, 34, 'ab' 拼接为一个字符串 '1234ab'
select concat(12, 34, 'ab') -- 1234ab
包含字符个数 length(str)
如果字符串中包含 utf8 格式的汉字，一个汉字 length 返回 3
例 2：计算字符串'abc'的长度
SELECT LENGTH('abc') -- 3例 3：计算字符串'我和你'的长度
SELECT LENGTH('我和你') -- 9例 4：查询表 students 中 name 长度等于 9（三个 utf8 格式的汉字）的学生信息
select * from students where length(name) = 9
截取字符串
left(str,len)返回字符串 str 的左端 len 个字符, 中文与英文字母个数 len 一致
例 5：截取字符串'我和你 abc'的左端 3 个字符
SELECT LEFT('我和你 abc', 3) -- 我和你
right(str,len)返回字符串 str 的右端 len 个字符, 中文与英文字母个数 len 一致；
例 6：截取字符串'我和你 abc'的右端 3 个字符
SELECT RIGHT('我和你 abc', 3) -- abc
substring(str,pos,len)返回字符串 str 的位置 pos 起 len 个字符，pos 从 1 开始计数；
例 7：截取字符串 '我和你 abc' 从第 2 个字符开始的的 3 个字符
SELECT SUBSTRING('我和你 abc', 2, 3) -- 和你例 8：截取 students 表中所有学生的姓
SELECT LEFT(NAME, 1) FROM students
练习
-- 1：查询 students 表的 card 字段，截取出生年月日，显示李白的生日 SELECT SUBSTRING(card, 7, 8) FROM students WHERE NAME="李白" -- 2：查询 students 表的所有学生信息，按生日从大到小排序(注意: 不能用age 排序,因为年龄相同的学生,生日可能不同) SELECT *, SUBSTRING(card, 7, 8) AS birthday FROM students ORDER BY birthday DESC;
去除空格
ltrim(str)返回删除左侧空格的字符串 str；
例 1：去除字符串' abc '左侧空格
select ltrim (' abc ') -- abc
rtrim(str)返回删除右侧空格的字符串 str；
例 2：去除字符串' abcd '右侧空格
select rtrim(' abcd ') -- abcdtrim(str)返回删除左右两侧空格的字符串 str；
例 3：去除字符串' abcd '左右空格
select trim(' abcd ') -- abcd
2. 数学函数
求四舍五入值 round(n,d)
n 表示原数，d 表示小数位置，默认为 0
例 1：1.653 四舍五入，保留整数位
select round(1.653) -- 2
例 2：1.653 四舍五入，保留小数点后 2 位
select round(1.653, 2) -- 1.65例 3：查询 students 表中学生的平均年龄，并四舍五入
select round(avg(age)) from students -- 22
-- 练习：查询 students 表中学生的平均年龄，并从小数点后 2 位开始四舍五入 SELECT ROUND(AVG(IFNULL(age,0)),2) FROM students -- 21.58随机数 rand()
值为 0 ~ 1.0 的浮点数
例 4：返回一个从 0 到 1.0 的小数
select rand()小技巧：从学生表中随机抽出一个学生
select * from students order by rand() limit 1
3. 日期时间函数
当前日期 current_date()
例 1：返回当前日期
select current_date() -- 2022-12-16
当前时间 current_time()
例 2：返回当前时间
select current_time() -- 12:09:12当前日期时间 now()
例 3：返回当前日期和时间
select now() -- 2022-12-16 12:09:45
2. 了解存储过程
1. 定义
存储过程 PROCEDURE，也翻译为存储程序，是一条或者多条 SQL 语句的集合
2. 创建存储过程
语法
create procedure 存储过程名称(参数列表) begin sql 语句 end例 1：创建查询过程 stu()，查询 students 表所有学生信息
-- 第一步：设置分割符(navicat中不需要，sqlyog需要) delimiter // -- 第二步：创建存储过程 create procedure stu() begin select * from students; end -- 第三步：还原分割符(navicat 中不需要,sqlyog需要) // delimiter; -- -------------------------------------------------------- DELIMITER // CREATE PROCEDURE stu() BEGIN SELECT * FROM students; END // DELIMITER; -- 执行查询(创建存储过程) call stu()
3. 使用存储过程
语法
- call 存储过程(参数列表);
例·1：使用存储过程 stu()
call stu();
4. 删除存储过程
语法：
drop PROCEDURE 存储过程 drop PROCEDURE if EXISTS 存储过程例 1：删除存储过程 stu
drop PROCEDURE stu drop PROCEDURE if EXISTS stu
3. 了解视图
1. 定义
- 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改 sql 语句，则需要在多个地方进行修改，维护起来非常麻烦
- 解决：定义视图
- 视图本质就是对查询的封装
2. 创建视图
语法：
create view 视图名称 as select 语句;例 1：创建视图,名叫 stu_nan，查询所有男生信息
create view stu_nan as select * from students where sex='男'
3. 使用视图
语法：
select * from 视图名例 1：使用视图 stu_nan
select * from stu_nan例 2：在视图 stu_nan 中查找年龄大于 25 岁的学生信息
select * from stu_nan where age > 25
4. 删除视图
语法：
drop view 视图名称 drop view if exists 视图名称例 1：删除视图 stu_nan
drop view stu_nan drop view if exists stu_nan
4. 了解事务
1. 为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景
例如：A 用户和 B 用户是银行的储户，现在 A 要给 B 转账 500 元，那么需要做以下几件事
- 检查 A 的账户余额>500 元；
- A 账户中扣除 500 元;
- B 账户中增加 500 元;
正常的流程走下来，A 账户扣了 500，B 账户加了 500，皆大欢喜。那如果 A 账户扣了钱之后，系统出故障了呢？A 白白损失了 500，而 B 也没有收到本该属于他的 500。
以上的案例中，隐藏着一个前提条件：A 扣钱和 B 加钱，要么同时成功，要么同时失败，事务的需求就在于此
2. 什么是事务
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位
例如：银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性
3. 事务命令
开启事务
命令 begin;
说明
- 开启事务后执行修改 UPDATE 或删除 DELETE 记录语句，变更会写到缓存中，而不会立刻生效。
回滚事务
命令 rollback;
说明放弃修改。
提交事务
命令 commit;
说明 将修改的数据
例 1：开启事务，删除 students 表中 studentNo 为 001 的记录，同时删除 scores
表中 studentNo 为 001 的记录, 回滚事务，两个表的删除同时放弃
begin; delete from students where studentNo = '001'; delete from scores where studentNo = '001'; rollback;例 2：开启事务，删除 students 表中 studentNo 为 001 的记录，同时删除 scores
表中 studentNo 为 001 的记录, 提交事务，使两个表的删除同时生效
begin; delete from students where studentNo = '001'; delete from scores where studentNo = '001'; commit;
5. 了解索引
1. 思考
看一本书，怎么快速知道要查看的内容在多少页？
- 给书建立一个目录
- 通过目录的索引，快速找到内容对应的页
当表中数据量很大时，查找数据会变得很慢
可以给表建议一个类似书籍中的目录，从而加快数据查询效率，这在数据库中叫索引（index）
2. 创建索引
语法
create index 索引名称 on 表名(字段名称(长度));如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致；
字段类型如果不是字符串，可以不填写长度部分。
例 1：为表 students 的 age 字段创建索引，名为 age_index
create index age_index on students(age);例 2：为表 students 的 name 字段创建索引，名为 name_index
create index name_index on students(name(10));例 3：查询表中 age 等于 30 的学生
select * from students where age=30; -- 例 3 中的 SELECT 语句 mysql 会自动调用索引 age_index, 从而提升查询效率例 4：查询表中 name 等于'张飞'的学生
select * from students where name='张飞'; -- 例 4 中的 SELECT 语句 mysql 会自动调用索引 name_index, 从而提升查询效率例 5：查询表中 sex 等于'男'的学生
select * from students where sex='男'; -- 例 5 中的 SELECT 语句查询效率不会提升,因为没有为字段 sex 建立任何索引.
3. 查看索引
语法
show index from 表名;例 1:查看 students 表的所有索引
show index from students;
4. 删除索引
语法
drop index 索引名称 on 表名;例 1:删除 students 表的索引 age_index
drop index age_index on students;
5. 索引优缺点
- 优点
- 索引大大提高了 SELECT 语句的查询速度；
- 缺点
- 虽然索引提高了查询速度，同时却会降低更新表的速度，例如对表进行 INSERT、UPDATE 和 DELETE 操作。因为更新表时，不仅要保存数据，还要保存索引文件;
- 在实际应用中, 执行 SELECT 语句的次数远远大于执行 INSERT、UPDATE 和 DELETE 语句的次数, 甚至可以占到 80%~90%, 所以为表建立索引是必要的。
- 在大量数据插入时, 可以先删除索引, 再批量插入数据, 最后再添加索引，这样就可以提高数据插入的效率。
6. 掌握基于命令行的SQL使用
1. windows cmd 命令窗口连接到 mysql
进入 mysql.exe 所在目录
如果安装的 mysql.exe 不在这个路径下,请根据实际路径填写.
cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
进入 mysql.exe 的所在目录后, 输入以下命令：
mysql -h [主机名] -u [用户名] -p参数说明
- -h [主机名]
- 指定要连接 mysql 的 IP 地址或者主机名称;
- 如省略-h [主机名]参数，mysql 会自动默认为本地连接;
- -u [用户名]
- 指定连接用户名
- -p
- 执行命令后会提示输入密码
# 例 1：连接到本地的 mysql，用户名 root mysql -u root -p # 例 2：连接到目标主机 192.168.93.129 的 mysql，用户名 root mysql -h 192.168.93.129 -u root -p- -h [主机名]
windows cmd 命令窗口显示 mysql 中 utf8 格式汉字乱码的解决方案
windows 的 cmd 窗口默认字符集为 gbk, 所以在 cmd 中执行 mysql 命令成功连接到 mysql 后, 需要执行如下命令:
set names gbk;注意: 后面一定要以分号结尾
如果通过 linux 命令行连接 mysql, 默认字符集为 utf-8，不需要单独设置字符集
2. MySQL 命令行使用简介
连接到 MySQL 后，如果要操作表，一定要先选择表所在的数据库
语法：
use [数据库名]例 1：选择数据库 abc
use abc命令行中要执行的 SQL 语句以;号结尾
例 1：在 mysql 命令行里，查询 students 表
select * from students;退出 MySQL 命令行语法：
exit例 2：通过命令行，登录到 MySQL，查询 students 表，之后退出命令行
-- 第一步：连接到 MySQL mysql -r root -p -- 第二步：使用 abc 数据库 use abc -- 第三步：执行 SQL 语句 select * from students; -- 第四步：退出 MySQL 命令行 exit
练习：通过 MySQL 命令行，登录到 MySQL，把 students 表中 studentNo 为'003' 的学生年龄 age 修改为 25，执行 select 语句, 查看修改是否正确后,退出 MySQL命令行；
7. 基于命令行的数据库管理操作
1. 显示已有数据库
命令格式
SHOW DATABASES;
2. 创建数据库
命令格式
CREATE DATABASE 数据库名 default charset [默认字符集];例 1：建立一个数据库，名为：mytest,默认字符集为 utf8
CREATE DATABASE mytest default charset utf8;
3. 删除数据库
命令格式
DROP DATABASE 数据库名;例 1：删除数据库 mytest
DROP DATABASE mytest;练习：使用 MySQL 命令行执行如下操作:
1. 登录 MySQL; 2. 创建数据库 mydb，默认字符集为 utf8; 3. 在 mydb 数据库中创建表 mytable, 建表语句如下： create table mytable(fal varchar(20));
4. 增加新用户
用 root 身份登录 mysql；
语法：
grant all on 数据库名.表名 to 用户名@'登录主机' identified by '密码' with grant option;grant all on:代表为用户赋权;
数据库名：可以是*，代表所有数据库；*
表名：可以是*，代表所有表，如:数据库.表名 写为 *.*代表可以对所有数据库和所有表进行操作；
to 用户名:指定要创建用户的名称;
@'登录主机'：@'localhost'：代表只能在本机登录，@'%'：代表可以远程登录；
identified by ‘密码'：指定用户登录密码；
with grant option：该用户是否能把权限分配给其他用户。
例 1：建立一个用户, 可以操作所有数据库和表, 用户名为 test, 该用户只能通过本地登录, 密码 123456, 该用户可以为其他用户分配权限
grant all on *.* to test@'localhost' Identified by '123456' with grant option;例 2：建立一个用户, 可以操作所有数据库和表, 用户名为 test, 该用户可以远程登录, 密码 123456, 该用户可以为其他用户分配权限
grant all on *.* to test@'%' Identified by '123456' with grant option;
5. 修改用户密码
如果用户名不存在，grant 语句创建新用户。如果用户名存在，grant 可以修改用户密码以及用户权限等；
例 1：修改用户本地登录 test 密码为 111111
grant all on *.* to test@'localhost' Identified by '111111' with grant option;例 2：修改用户远程登录 test 密码为 111111
grant all on *.* to test@'%' Identified by '111111' with grant option;
6. 删除用户
第一步：用 root 身份登录 mysql
- 语法: mysql -u root -p
第二步：选择 mysql 数据库
语法: use mysql
第三步：回收用户权限
revoke all on . from 代表回收指定用户权限;
语法: revoke all on . from 用户名@'登录主机';
第三步：删除用户
- 语法: delete from user where user='用户名';
第四步：刷新权限
- 语法: flush privileges;
例：删除用户 test
-- 第一步：用 root 身份登录 mysql mysql -u root -p -- 第二步：选择 mysql 数据库 use mysql; -- 第三步：回收用户 test 权限 revoke all on *.* from test@'localhost'; revoke all on *.* from test@'%'; -- 第四步：删除用户 test delete from user where user='test'; -- 第五步：刷新权限 flush privileges;