一、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;