目录
字符串函数
数值函数
日期函数
流程函数
多表查询
多表查询 -- 内连接
多表查询 -- 自连接
多表查询 -- 外连接
子查询
合并查询
关键字all-any
蠕虫复制测试效率
表中数据去重
主键和约束
主键
约束
自增长约束
索引
事务
事务的使用
事务的隔离级别
存储引擎
视图
字符串函数
常用的字符串函数有：
-- 字符串拼接
concat(s1,s2,s3...);
-- 字符串转大写
upper(str);
-- 字符串转小写
lower(str);
-- 字符串左填充
lpad(target,total_len,src);
-- 字符串右填充
rpad(target,total_len,src);
-- 字符串取出首尾空格
trim(str);
-- 字符串截取（截取str第start_pos -> dest_pos位置的字符,下标从1开始）
substring(str,start_pos,dest_pos);
-- 判断字串是否在主串中
instr(dest,src);
-- 字符串替换 -- dest代替换串 --src用来替换的串
replace(str,'dest','src');
-- 字符串加密，生成一个32位的加密后的字符串
MD5(str);
-- 字符串的另一种加密方式(SQL-8.0已经弃用)
password(str);
数值函数
常用的数值函数有：
-- 向上取整,返回最近的比原始数大的整数
ceil();
-- 向下取整，返回最近的比该数小的整数
floor();
-- 产生0-1之间的随机数 -- 产生0-1之间一个随机数，此后只要seed的值不变，则该随机数是固定的
rand(); rand(seed);
-- 保留小数位数(四舍五入)
format(number,decimal_count);
-- 四舍五入保留y位小数
round(x,y);
-- 求x/y的余数
mod(x,y);
-- 绝对值
abs(m);
-- 求多个数的最小值
least(val,val,val...);日期函数
常用的日期函数有：
-- 返回当前日期(年月日)
curdate();
-- 返回当前时间(时分秒)
curtime();
-- 返回当前日期和时间
now();
-- 获取指定date的年份、月份、天数
year(date)、month(date)、day(date);
-- 返回一个时间值date加上一个时间值expr后的时间值,type为expr的单位
date_add(date,interval expr type);
--- 返回一个时间值减去一个时间值后的时间值
date_sub(date1,interval expr type);
-- 返回两个时间值之间的天数
datediff(date1,date2);
-- 返回两个时间值之间相差的小时数
timediff(datetime,datetime);
-- 返回1970-1-1到现在经过的秒数
unix_timestamp();
-- 将unix_timestamp转换为指定的格式进行显示
from_unix(int val,'%Y-%m-%d %H-%i-%s');
流程函数
-- 如果表达式expr为真，则返回expr2，否则返回expr3
if(expr1,expr2,expr3);
-- 如果表达式expr1的值不为空，则返回；如果为空，则返回expr2
ifnull(expr1,expr2);
-- 类似于if-else if-...-else
-- 如果expr1为真，则返回expr2；
-- 否则：如果expr3为真，则返回expr4
-- ......
-- 否则，返回else 后的expr
-- 注意最后的end不要省略！！！
case
when expr1 then expr2
when expr3 then expr4
...
else expr end; 例如，有表emp_exer，使用上述的流程函数进行表的需求查询：
多表查询
使用n多表查询，查询的条件不能少于n-1个，否则会使查询结果出现笛卡尔乘积。
例如：
使用：
查询emp_exer表中的雇员姓名和薪水以及dept表中该职员的部门名称和编号：
select emp_exer.ename, emp_exer.sal, dept.dname, dept.deptno from emp_exer, dept where emp_exer.deptno = dept.deptno;
多表查询 -- 内连接
多表查询的内连接查询的是两个表中有交集的部分，分为显示内连接和隐式内连接(sql的写法不同).
例如：查询emp_exer表中的雇员姓名和薪水以及dept表中该职员的部门名称和编号：
显示内连接：
select emp_exer.ename, emp_exer.sal, dept.dname, dept.deptno from emp_exer, dept where emp_exer.deptno = dept.deptno;隐式内连接：
-- inner可以省略，on后边跟的是多表查询的条件 select e.ename, e.sal, d.dname, d.deptno from emp_exer e inner join dept d on e.deptno = d.deptno;
多表查询 -- 自连接
自链接通常指查询同一张表中的不同数据并组合，特点是把一张表当做两张表来使用。在使用的过程中应注意需要给当做两张表使用的表起不同的别名。
由于表的查询先执行的是from语句，因此如果在from时给表起了别名，则在之后使用该表中的字段或者设置限制条件只能通过表名过来访问字段名称。
例如：
使用：
select workers.ename '职员名称', bosses.ename '上级名称' from emp_exer as workers, emp_exer as bosses where workers.mgr = bosses.empno;*出现十二条而非十三条数据的原因是表中的'KINGS'没有上级。
多表查询 -- 外连接
外连接分为左连接 left [outer] join ... on...和右连接 right [outer] join ...on ...。左外连接用来查询左表中的全部数据与左表和右表中的交集数据；右外连接用来查询右表中的全部数据和左表和右表中交集的数据（包括空值）。
例如：左外连接查询emp_exer表中的所有数据以及emp_exer表中员工的上级信息；使用右外连接查询dept表中的所有数据以及该部门对应的员工信息。
使用：
左外连接：
-- outer可以省略 select e1.*, e2.ename '上级名称' from emp_exer as e1 left outer join emp_exer e2 on e1.mgr = e2.empno;b.empno;右外连接：
select e.* from emp_exer e right join dept d on e.deptno = d.deptno;在实际开发中左外连接使用较多，通常的右外连接都可以通过表的顺序互换实现向左外连接的转换，例如将上述的右外连接转换为左外连接：
select e.*,d.dname from dept d left join emp_exer e on d.deptno = e.deptno;
子查询
查询的结果可以作为另一个查询的条件或者字段、临时表等使用。
子查询作为查询条件使用 -- 例如：查询emp_exer表中与部门10号中职位相同的员工的信息，但不包括10号部门的员工。
使用:
- 查询部门10所有的职位类型
- 将查询到的职位类型作为下一次查询的判断条件
select * from emp_exer where job in (select distinct job from emp_exer where deptno = 10) -- 返回10号部门的所有job类型，作为下次查询的判断条件 and deptno <> 10; -- 过滤掉自身(10号部门)的员工
子查询作为临时表使用 -- 例如：查询ecs_goods表中每一类商品中价格最高的商品的信息。
使用：
- 查询每一类商品中商品的最高价格和该类商品的编号
- 利用多表查询，根据条件：商品的id相同以及价格相同，返回商品的其他属性信息
select goods_id, ecs_goods.cat_id, goods_name, shop_price from ecs_goods, (select cat_id, max(shop_price) max_price from ecs_goods group by cat_id) temp where ecs_goods.cat_id = temp.cat_id and ecs_goods.shop_price = temp.max_price;
合并查询
合并查询使用到的关键字为union all或者union. union all将全部数据合并，union将重复的数据删除在合并，具有去重的功能。
关键字all-any
使用：
查询emp表中比30号部门所有员工的最高工资高的员工信息：
查询emp表中比30号部门任何一个员工的工资高的员工信息：
select * from emp_exer where sal > all(select sal from emp_exer where deptno = 30); --all select * from emp_exer where sal > any(select sal from emp_exer where deptno = 30); --any当然也可以使用子查询：
-- 大于30号部门最高工资 select * from emp_exer where sal > (select max(sal) from emp_exer where deptno = 30); -- 大于30号部门最低工资 select * from emp_exer where sal > (selct min(sal) from emp_exer where deptno = 30);
蠕虫复制测试效率
-- 表的蠕虫复制测试某条sql语句的效率步骤 -- 创建一张与待测试表结构相同的表 create table tab01 like emp_exer; -- 将待测试表中的数据拷贝到这张表中,完成后对该表进行循环式的自我拷贝 insert into tab01 (select * from emp_exer); insert into tab01 (select * from tab01);
表中数据去重
-- 表中数据去重 -- 创建一张与待去重数据表结构类似的表 create table temp_tab01 like tab01; -- 去重查询带有重复数据的表,将去重查询的结果拷贝到新建的数据表 insert into temp_tab01 (select distinct * from tab01); -- 清空带有重复数据的表 delete from tab01; -- 将新建表中的数据拷贝到原表中，删除工具表 insert into tab01 (select * from temp_tab01); drop table temp_tab01;
主键和约束
主键
主键的使用细节：
- 主键primary key所在列的值不能重复且不能为空
- 一张表最多有一个主键，但是可以是复合主键（多个列组成的主键，这些列不能同时相同，且任何一个键值都不能为空）
- 主键的设定有两种方式：在字段后直接使用primary key指定；在表的定义最后使用primary key(字段1，字段2...),且设置的和主键只能使用第二种方式定义。
- 使用desc表名，可以查看主键字段
- 在实际开发中，每个表通常都有一个主键
例如：
创建含有主键或复合主键的表：
-- 含有主键 id字段，该字段所在列的值不饿能为空且不能重复 create table tab02 ( id int primary key, name varchar(15), email varchar(15) ) comment 'table02'; -- 含有复合主键（id,name），这两个字段所在的列不能同时相同，且任意一个值不能为null create table tab03 ( id int, name varchar(15), email varchar(15), primary key (id, name) ) comment 'table03';约束
unique、not null约束：
unique：该字段的所在列的值唯一，不能重复
not null：该字段所在列的值不能为空
使用：
create table tab06 ( id int unique, -- 该列的值不能重复 name varchar(15), email varchar(15) not null -- 该列的值不能为空 ) comment 'table06';foreign key 外键约束：
使用细节：
- 外键指向的表的字段，要求是primary key或者是unique
- 表的类型是innodb，这样才支持外键
- 外键字段的类型要和主键字段的类型一致
- 外键字段的值，必须在主键字段中出现过，或者为null（外键所在字段的值可以为空）才能添加成功
- 建立起外键关系后，主键中的内容要想删除，需要等外键中的内容删除后才能删除成功
例如，创建一个外键约束的表（foreign key (本表字段名) references 外表(主键名)）：
-- 主表 create table tab07_class( id int primary key , name varchar(20) not null default '', loc varchar(20) ); -- 带有外键约束的表，从表 create table tab08_stu( id int primary key , name varchar(20) not null default '', class_id int, foreign key (class_id) references tab07_class(id) -- 指定外键关系 ) comment '学生表';向从表中添加数据，外键的值需要在主表对应的列之中出现过：
删除主表中的数据，需要确保从表的外键数据没有与之对应的，才能删除成功：
check约束：
在MySQL5.7版本支持check的语法但是并不会起作用。
自增长约束
在创建表时，可以通过在字段后添加 autu_increament设置该列为自增长约束；自增长约束需要与主键primary key或者unique约束搭配使用。
可以通过以下语法设置起始自增长索引为m：
alter table XXX auto_increament = m;向表中插入自增长字段的方式有两种，例如向tab09中插入数据：
-- tab09表 create table tab09( id int primary key auto_increment, -- 创建自增长约束的字段 `name` varchar(32) not null default 'XXX', email varchar(32) not null default 'XXX@XXX' ) comment '自增长约束测试表'; -- 第一种方式，插入全部字段值时，给该字段空值，会默认以自增长的方式添加该字段的值 insert into tab09 values(null,'Jack','Jack.@qq.com'); -- 第二种方式：不给该字段值，默认自增长添加 insert into tab09(name, email) value('Chec','Chec@qq.com');
索引
索引机制的一种解析：
将待查询的数据从头至尾构建成一颗搜索(排序)二叉树进行存储，节省存储效率，例如：4,3,6,5,1，9，7这一组数据，将其构建成搜索二叉树如下，其进行查找的效率大大提高。
索引的类型：
- 主键索引，主键primary key默认为索引类型的一种
- 唯一索引，unique
- 普通索引，index
在实际开发中通常使用Solr或者ElasticSearch(ES).
索引的创建：
-- 给emp表的empno字段创建index索引： create index index_empno on emp(empno); -- 或者 alter table emp add index index_empno (empno); -- 给emp表的empno字段创建primary key索引 alter table emp add primary key (empno);索引的删除：
-- 删除emp表中empno的普通index索引： drop index index_empno on emp; -- 删除表中的index索引（比较特别） alter table emp drop primary key;查询某个表中的索引：
show indexes from table_name; show keys from table_name; desc table_name; -- key列显示
事务
事务的使用
事务的介绍：
事务有一相关的DML语句组成，可以控制该组DML语句的执行和回退，当执行事务操作时，mysql会在进行事务操作的表上加上锁，防止其他用户修改表的数据，当执行commit操作后，该事务就被视为确定状态，不能再进行回退。
事务的理解：
事务的基本操作：
- 启动事务: start transaction;
- 设置保存点：savepoint point_name;
- 回退至某个保存点：rollback to point_name;
- 回退所有保存点并清除事务：rollback；
回退事务可以通过指定保存点回退到指定的保存点- 提交事务：commit；
当提交后事务会结束并自动删除该事务所定义的保存点，释放这个表的锁，数据生效并不可回退。commit之后其他会话可以查看事物变化后的新数据。
事务的使用细节：
- 如果没有手动创建事务，DML是自动提交的，不能回滚。
- 在创建事务后，会默认创建一个保存点，该保存点可以使rollback回退清除该事务
- MySQL的事务机制需要innodb引擎才可以使用，myisam不好使
事务的隔离级别
-- 脏读，不可重复读和幻读：
- 脏读(dirty read)：当一个事务读取另一个事物尚未提交的修改时，产生脏读
- 不可重复读（nonrepeatable read）：同一查询在统一事务中多次进行，由于其他事务所做的修改或删除（commit后），而导致每次返回不同的结果集，发生不可重复读
- 幻读（phantom read）：同一查询在同一事务中多次进行，由于其他事务所做的插入操作（commit后），每次返回不同的结果集，发生脏读
事务的ACID：
- 原子性（Atomicity）：指事务是一个不可分割的工作单位，在事务中要么操作都发生要么都不发生。
- 一致性（consistency）：事务使得数据库从一个一致性状态变为另一个一致性状态
- 隔离性（isolation）：多个用户并发访问数据时，数据库为每一个用户开启事务，该事务不能被其他事务的操作所干扰，多个事务之间要相互隔离
- 持久性（durablity）：数据库一旦被提交，对数据库中数据的改变就是永久性的
事务隔离级别的种类：
√：会产生×：不会产生
事务隔离级别的种类 隔离级别名称 脏读 不可重复读 幻读 锁 读-未提交
read uncommitted
√ √ √ 未加锁 读-已提交
read committed
× √ √ 未加锁 可重复读
repeatable read
× × × 未加锁 可串行化
serializable
× × × 加锁 隔离级别的操作：
- *mysql默认的事务隔离级别是可重复读（repeatable read），一般情况下，该级别可以满足绝大部分的项目需求，没有特殊要求不需要进行更改。
- 设置系统的默认隔离级别有以下两种方式：
set global transaction isolation level [隔离级别名称]；其他操作：
-- 查看当前会话隔离级别 select @@transaction_isolation; -- 查询当前系统的默认隔离级别 select @@global.transaction_isolation; -- 设置当前会话隔离级别 set session transaction isolation level [隔离级别的名称];四种隔离级别的使用过程：
首先在bxs_db_01中有一张account表如下：
①读-未提交（read uncommitted）：
- 新开两个cmd窗口，设置第二个窗口会话的隔离级别为read uncommitted（读未提交）,在两个窗口都新开事务：
- 使用会话一在account表中添加或者修改数据，在会话二窗口的事务中可以看到会话一修改还未提交的内容；当事务一所做的修改删除或增加提交后，事务二仍可以读取到，事务二发生了不可重复读和幻读：
②读-已提交（read committed）：
- 新开两个cmd窗口，设置第二个窗口会话的隔离级别为read committed（读已提交）,在两个窗口都新开事务：
事务二不会发生脏读，但会发生不可重复读和幻读：
③ 可重复读（repeatable read）
- 新开两个cmd窗口，设置第二个窗口会话的隔离级别为repeatable read（可重复读）,在两个窗口都新开事务：
则事务二不会产生脏读，幻读和不可重复读：
④可串行化（serializable）
- 新开两个cmd窗口，设置第一个窗口会话的隔离级别为serializable（可串行化）,在第一个窗口开启一个新事务，在第一个表的事务处理期间会给正在操作的表上加锁，其他事务会处于等待状态，在事务1访问该表期间其他事务无法访问该表，也不会有脏读，不可重复读和幻读的现象发生：
存储引擎
MySQL中表的存储引擎共有6种，可以使用以下指令进行查看：
show engines;
可以通过以下指令修改表的存储引擎：alter table table_name engine = [name_engine];常用的存储引擎有：InnoDB、MYISAM、memory三种，每种的特点如下：
- InnoDB：支持事务和外键， 写的效率比MYISAM底，占用磁盘空间。
选用场景：需求需要支持事务- MYISAM：不支持事务和外键，但其访问速度快，占用磁盘空间
选用场景：不需要处理事务，只是基本的CRUD操作- memory：数据存放在内存中，使用内存中的数据来创建表，速度巨快，支持Hash索引，但是所有的数据都会在服务器关闭或重启后丢失！
选用场景：经常变化且无需长久保存的数据表使用memory引擎
视图
当不希望数据库中的数据在查询时全部显示出来时，可以为该表(也可以是多张表)创建视图，显示该表中特定字段的列的信息。视图和表的关系如下：
通过视图可以修改基表的数据，通过修改基表的数据也会影响到视图。 文章来源：https://www.toymoban.com/news/detail-406008.html
视图语句的使用：文章来源地址https://www.toymoban.com/news/detail-406008.html
-- 创建视图 create view view_name as select语句; -- 修改视图的映射源表 alter view view_name as select语句; -- 显示视图定义时的语句 show create view view_name; -- 删除视图 drop view view_name1,view_name2...
到了这里，关于SQL函数、约束、查询、索引和事务的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
