五天搞定Mysql基础知识-Day05

学习目标：

        1、MySQL常用内置函数

        2、了解存储过程

        3、了解视图

        4、了解事务

        5、了解索引

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

第一章 MySQL内置函数

一、字符串函数

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

例1：把12,34，‘ab’拼接位一个字符串‘1234ab’

select concat(12, 34, 'ab');

2、包含字符个数 length(str)

如果字符串中包含 utf-8 格式的汉字，一个汉字 length 返回 3

例2：计算字符串'abc'的长度

SELECT LENGTH('abc');

例3：计算字符串'我和你'的长度

SELECT LENGTH('我和你');

例4：查询表 students 中 name 长度等于9（三个 UTF-8 格式的汉字）的学生信息

SELECT * FROM students WHERE LENGTH(name) = 9;

3、截取字符串

left(str,len) 返回字符串 str 的左端 len 个字符，中文和英文字母个数 len 一致；

例5：截取‘我和你 abc’的左端三个字符

select LEFT('我和你abc', 3);

right(str,len) 返回字符串 str 的右端 len 个字符，中文和英文字母个数 len 一致；

例6：截取‘我和你 abc’的右端三个字符

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 * from students ORDER BY SUBSTRING(card,7,8);

4、去除空格

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

例1：去除字符串' abc '左侧空格

SELECT LTRIM(' abc ');

rtrim(str) 返回删除右侧空格的字符串 str；

例2：去除字符串' abc '右侧空格

SELECT RTRIM(' abc ');

trim(str) 返回删除两侧空格的字符串 str；

例3：去除字符串' abc '两侧空格

SELECT TRIM(' abc ');

二、数学函数

1、四舍五入值 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(age),2) from students;

2、随机数 rand()

值为 0-1.0 的浮点数

例1：返回一个 0到1.0 的小数

SELECT rand();

小技巧：从学生表中随机抽出一个学生

SELECT * FROM students ORDER BY RAND() LIMIT 1;

三、日期时间函数

1、当前日期 current_date()

例1：返回当前日期

SELECT CURRENT_DATE();

2、当前时间 current_time()

例2：返回当前时间

SELECT CURRENT_time();

3、当前日期和时间 now()

例3：返回当前日期和时间

SELECT now();

第二章 存储过程

一、定义

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

二、创建存储过程

语法：

create procedure 存储过程名称(参数列表)

begin

sql 语句

end

-- 例1：创建存储过程 stu() 查询 students 表所有学生信息 CREATE PROCEDURE stu() BEGIN SELECT * FROM students; END -- 调用存储过程 CALL stu(); -- 删除存储过程 DROP PROCEDURE stu; DROP PROCEDURE if EXISTS stu;

第三章 视图

一、定义

1、对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改 sql 语句，则需要在多个地方进行修改，维护起来非常麻烦；

2、解决：定义视图；

3、视图本质就是对查询的封装；

二、创建视图

语法：

create view 视图名称 as select 语句；

例1：创建视图，名叫 stu_nan, 查询所有男生信息

CREATE VIEW stu_nan as

SELECT * FROM students WHERE sex = '男';

三、使用视图

SELECT * FROM stu_nan INNER JOIN scores

on stu_nan.studentNo = scores.studentNo;

SELECT * FROM stu_nan WHERE age > 30;

四、删除视图

语法：

drop view 视图名称;

drop view if exists 视图名称;

例1：删除视图 stu_nan

drop VIEW stu_nan;

drop VIEW if EXISTS stu_nan;

第四章 事务

一、为什么要有事务

事务广泛的应用于订单系统、银行系统等多种场景。

二、什么是事务

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

三、事务命令

1、开启事务

命令	说明
begin；	开启事务后执行修改UPDATE或删除DELETE记录语句，变更会写到缓存中，而不会立刻生效

2、回滚事务

命令	说明
rollback；	放弃修改

3、提交事务

命令	说明
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;

SELECT * FROM students;

SELECT * FROM scores;

第五章 索引

一、思考

当表中数据量很大的时候，查找数据会变得很慢；

可以给表建立一个类似书籍中的目录，从而加快数据查询效率，这在数据库中叫索引（index）；

二、创建索引

语法：

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

不需要显示的写调用索引的语句，只要where条件后面用到的字段建立了索引，那么系统会自动调用

-- where条件后面的字段，数据库系统会自动查找是否有索引

-- 这里会自动调用age_index

SELECT * FROM students WHERE age = 30;

-- 自动调用name_index

SELECT * FROM students WHERE name = '李白';

-- 不会调用任何索引，因为sex字段没有索引

SELECT* FROM students WHERE sex = '女';

三、查看索引

语法：

show index from 表名;

例1：查看 students 表的所有索引;

SHOW INDEX FROM students;

四、删除索引

语法：

drop index 索引名称 on 表名;

例1：删除 students 表的索引 age_index

DROP INDEX age_index ON students;

五、索引优缺点

1、优点：

索引大大提高了 select 语句的查询速度；

2、缺点：

虽然索引提高了查询速度，同时却会降低更新表的速度，例如对表进行INSERT、UPDATE和DELETE操作，因为更新表时，不仅要保存数据，换药保存索引文件。

3、在实际应用中，执行SELECT语句的次数远远大于执行INSERT、UPDATE和DELETE语句的次数，甚至可以占到80%~90%，所以为表建立索引是必要的。

4、在大量数据插入时，可以先删除索引，再批量插入数据，最后再添加索引，这样就可以提高数据插入的效率。