01. MySQL其他操作

1. MySQL内置函数

1. 字符串函数

拼接字符串 concat(str1,str2...)
- 例 1：把 12, 34, 'ab' 拼接为一个字符串 '1234ab'
```
select concat(12, 34, 'ab'); 
```
包含字符个数 length(str)
- 如果字符串中包含 utf8 格式的汉字，一个汉字 length 返回 3
- 例 2：计算字符串'abc'的长度
```
select length('abc');
```
- 例 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);
```
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       ')
```
rtrim(str)返回删除右侧空格的字符串 str；
- 例 2：去除字符串' abcd '右侧空格
```
  select rtrim(' abcd ');
```
- trim(str)返回删除左右两侧空格的字符串 str；
  - 例 3：去除字符串' abcd '左右空格
  select trim(' abcd ')

2. 数学函数

求四舍五入值 round(n,d)

n 表示原数，d 表示小数位置，默认为 0
- 例 1：1.653 四舍五入，保留整数位
```
select round(1.653);
```
例 2：1.653 四舍五入，保留小数点后 2 位
```
  select round(1.653,2);
```
例 3：查询 students 表中学生的平均年龄，并四舍五入
```
select round(avg(age)) from students;
```

-- 练习：查询 students 表中学生的平均年龄，并从小数点后 2 位开始四舍五入
SELECT ROUND(AVG(IFNULL(age,0)),2) FROM students;

随机数 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();
```
当前时间 current_time()
例 2：返回当前时间
```
select current_time();
```
当前日期时间 now()
- 例 3：返回当前日期和时间
```
select now();
```

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;

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 元，那么需要做以下几件事：
  1. 检查 A 的账户余额>500 元；
  2. A 账户中扣除 500 元;
  3. 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
```
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;

01. MySQL其他操作

# 01. MySQL其他操作

# 1. MySQL内置函数

# 1. 字符串函数

# 2. 数学函数

# 3. 日期时间函数

# 2. 了解存储过程

# 1. 定义

# 2. 创建存储过程

# 3. 使用存储过程

# 4. 删除存储过程

# 3. 了解视图

# 1. 定义

# 2. 创建视图

# 3. 使用视图

# 4. 删除视图

# 4. 了解事务

# 1. 为什么要有事务

# 2. 什么是事务

# 3. 事务命令

# 5. 了解索引

# 1. 思考

# 2. 创建索引

# 3. 查看索引

# 4. 删除索引

# 5. 索引优缺点

# 6. 掌握基于命令行的SQL使用

# 1. windows cmd 命令窗口连接到 mysql

# 2. MySQL 命令行使用简介

# 7. 基于命令行的数据库管理操作

# 1. 显示已有数据库

# 2. 创建数据库

# 3. 删除数据库

# 4. 增加新用户

# 5. 修改用户密码

# 6. 删除用户

01. MySQL其他操作

1. MySQL内置函数

1. 字符串函数

2. 数学函数

3. 日期时间函数

2. 了解存储过程

1. 定义

2. 创建存储过程

3. 使用存储过程

4. 删除存储过程

3. 了解视图

1. 定义

2. 创建视图

3. 使用视图

4. 删除视图

4. 了解事务

1. 为什么要有事务

2. 什么是事务

3. 事务命令

5. 了解索引

1. 思考

2. 创建索引

3. 查看索引

4. 删除索引

5. 索引优缺点

6. 掌握基于命令行的SQL使用

1. windows cmd 命令窗口连接到 mysql

2. MySQL 命令行使用简介

7. 基于命令行的数据库管理操作

1. 显示已有数据库

2. 创建数据库

3. 删除数据库

4. 增加新用户

5. 修改用户密码

6. 删除用户