MySQL数据库-3

一、MySQL 内置函数

1、字符串函数

拼接字符串 concat(str1,str2...)

拼接字符串 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('我和你');

例 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;

去除空格

ltrim(str) 返回删除左侧空格的字符串 str；

例 1：去除字符串' abcd '左侧空格
select ltrim(' abcd ');

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;

随机数 rand()

随机产生一个值为 0-1.0 的浮点数

例 4：返回一个从 0 到 1.0 的小数
select rand(); 

-- 小技巧：从学生表中随机抽出一个学生
select * from students order by rand() limit 1;

3、日期时间函数

当前日期 current_date()

当前时间 current_time()

当前日期时间 now() 

例 1：返回当前日期
select current_date();

当前时间 current_time()
例 2：返回当前时间
select current_time();

当前日期时间 now()
例 3：返回当前日期和时间
select now();

二、存储过程

1、定义：

存储过程 PROCEDURE，也翻译为存储程序，是一条或者多条 SQL 语句的集合。

2、创建存储过程

语法：
create procedure 存储过程名称(参数列表)
begin
sql 语句
end

例 1：创建查询过程 stu()，查询 students 表所有学生信息
CREATE PROCEDURE stu()
BEGIN
SELECT * FROM students;
END
使用存储过程
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;

三、视图

1、定义

• 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改 sql 语句，则需要在多个地方进行修改，维护起来非常麻烦；
• 解决：定义视图；
• 视图本质就是对查询select的封装；
• 视图可以理解为一张只读的表，针对视图只能用select，不能用delete和update

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;

四、事务

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;

说明将修改的数据写入实际的表中。

• 没有写begin代表没有事务，没有事务的表操作都是实时生效
• 如果只写了begin，没有rollback，也没有commit，系统退出，结果是rollback

例 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;

五、索引

1、index

当表中数据量很大时，如果没有索引，查找数据会变得很慢；

如果一个表记录很少，几十条或者几百条不到，不用索引；

可以给表建议一个类似书籍中的目录，从而加快数据查询效率，这在数据库 中叫索引（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;

例 4：查询表中 name 等于'张飞'的学生
select * from students where name='张飞';

例 5：查询表中 sex 等于'男'的学生
select * from students where sex='男';

例 3 中的 SELECT 语句 mysql 会自动调用索引 age_index, 从而提升查询效率

例 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%, 所以为表建立索引是必要的。
• 在大量数据插入时, 可以先删除索引,再批量插入数据,最后再添加索引，这样 就可以提高数据插入的效率。

六、MySQL 命令行

1、MySQL 命令行使用简介

连接到 MySQL 后，如果要操作表，一定要先选择表所在的数据库

语法：
use [数据库名]
例 1：选择数据库 abc
use abc

命令行中要执行的 SQL 语句以;号结尾

例 1：在 mysql 命令行里，查询 students 表
select * from students;

退出 MySQL 命令行语法：
exit

七、基于命令行的数据库管理操作

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;