目录
一、基础操作(库、表)
针对于库的基础操作
-- 创建：直接创建数据库语法格式:书写需去除[]
create database [if not exists] 数据库名称 [character set 字符集名称];
说明:中括号中命令为可选项;
if not exists 表示数据库不存在则创建,存在则不创建;
character set 字符集名称 表示创建过程指定数据库字符集;
-- 查询
1)查看所有数据库
show databases;
2)查看数据库的定义信息
show create database 数据库名称;
-- 修改
-- 1)修改数据库: 将数据库的字符集修改,语法格式:
alter database 数据库名称 character set 字符集;
-- 2)删除数据库
drop database 数据库名称;
-- 3)查看正在使用的数据库
select database();
-- 4)切换数据库
use 数据库名称;针对于表的基础操作
CREATE TABLE 表名 (
字段名1 字段类型1,
字段名2 字段类型2
);
-- 1)查看表信息
-- 1.1 查看某个库下的所有表
show tables;
-- 1.2 查看表结构包含字段信息
desc 表名;
-- 1.3 查看创建表的SQL信息
show create table 表名;
-- 2)删除表信息
drop table [if exists] 表名;
说明:
drop:删除关键字
table:表示删除的对象是表类型
if exists:表示如果存在就删除,不存在则不做操作(不会报错)mysql常用的类型
DDL修改表结构
-- 1.为表添加字段
ALTER TABLE 表名 ADD 字段名 类型;
-- 2.修改表中字段的类型
ALTER TABLE 表名 MODIFY 字段名 新的类型;
-- 3.修改列名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型;
-- 4.删除列
ALTER TABLE 表名 DROP 字段名;
-- 1.修改表名
RENAME TABLE 旧表名 TO 新表名;
-- 6.修改字符集
ALTER TABLE 表名 character set 字符集;DML表插入
-- 1.单条插入
INSERT INTO 表名 (字段名1, 字段名2,...) VALUES (字段值1, 字段值2,...);
-- 如果插入的是全部字段,表名后的字段名称可以忽略不写,格式如下
INSERT INTO 表名 VALUES (字段值1, 字段值2,...);
-- 2.批量插入
INSERT INTO 表名 VALUES (字段值1, 字段值2,...), (字段值1, 字段值2,...),......;DML表更新
-- 1.无条件全表更新,语法格式(开发中慎用):
UPDATE 表名 SET 字段名=新的值,字段名=新的值,..;
-- 2.带条件修改
UPDATE 表名 SET 字段名=新的值,字段名=新的值,.. WHERE 条件;DML表删除
-- 1.无条件全表删除,语法格式(开发中慎用):
DELETE FROM 表名;
-- 2.带条件删除
DELETE FROM 表名 WHERE 条件;truncate删除(DDL)
-- 格式
TRUNCATE TABLE 表名;
说明:truncate表示清空表的结构和数据面试:truncate和delete的区别
1)delete是将表中的数据一条一条删除数据,不影响表结构;
2)truncate是将整张表删除，然后重新创建一个新的表,新的表结构和原来表结构一致;
DQL数据查询
-- 查询指定列数据
SELECT 字段名1, 字段名2, ... FROM 表名;
-- 如果要查询表所有的列也可使用*匹配
select * from 表名 ;
-- 1. 查询时给列或者表取别名,可使用AS关键字
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;说明:AS关键字是给字段和查询结果取别名,同时as可以省略不写;
字段的去重Distinct
-- 查询指定列并且结果不出现重复数据,格式:
SELECT DISTINCT 字段名,字段名,.. FROM 表名;
-- 说明:distinct关键字后的所有字段值都相同,才去重数学运算
-- 格式举例:
select 字段1[+、-、*、/、%]字段2 from tab;
-- 查询姓名、年龄，将每个人的数学增加10分
select name,age,math+10 as 数学 from student;
-- 查询所有列与math + english的和并使用别名”总成绩”
select *,math+english as 总成绩 from student;蠕虫复制
在已有的数据基础之上，将原来的数据进行复制，并插入到对应的表中;
insert into 表名1 select * from 表名2;
-- 作用:将表名2中的数据复制到表名1中
说明:insert into 插入表名1的数据来自select * from 表名2的查询数据
前提:查询的数据要与插入的表中的字段类型和顺序要一致;DQL查询语句-条件查询
-- 条件查询格式:
select 字段1,字段2,...... from 表 where 条件;比较运算符
> 大于
< 小于
<= 小于等于
>= 大于等于
= 等于
<>、!= 不等于
使用举例:
-- 查询年龄小于18岁的学生信息
select * from student where age < 18;逻辑运算符
and(&&) 多个条件同时满足 一假即假 开发中用的最多个
or(||) 多个条件其中一个满足 一真即真
not(!) 不满足 取反
举例:
-- 查询名字等于张三且性别为男的学生:
select * from student where name='zhangsan' and sex='男';
select * from student3 where age >35 or sex='男';in关键字
in关键字：in里面的每个数据都作为一次查询条件,只要满足的就会显示;
-- 语法格式
SELECT * FROM 表名 WHERE 字段 in (值1, 值2, 值3);
查询id是1或3或5或者7的学生
-- 相同字段or关联查询 推荐使用in关键字
select * from student3 where id in(1,3,5,7);
查询id不是1或3或5或者7的学生(not in)
select * from student3 where id not in(1,3,5,7);between and 范围查询关键字
-- 语法格式
between 值1 and 值2; -- 表示从值1到值2范围，包头又包尾(包含边界值)
-- 比如查询年龄在80到100的用户:
select * from user where age BETWEEN 80 AND 100;
-- 相当于：
select * from user where age>=80 and age<=100;模糊查询like
-- 语法格式 说明:like是模糊查询关键字
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';满足通配符字符串规则的数据就会显示出来; 所谓的通配符字符串就是含有通配符的字符串; MySQL通配符有两个： %: 表示零到多个字符(任意多个字符) _: 表示一个字符
例如：
name like '张%' 所有姓张学员。 张,张三,张三丰,张三丰收,...
name like '%张%' 只要有张就可以。 张 小张 老张子
name like '张_' 所有姓张名字为两个字学员。张三
name like '张__' 必须三个字,且姓张
name like '_张_' 只有中间是张，前面一个字，后面一个字。DQL排序查询
通过ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序) ASC: 升序排序(默认,可省略不写) DESC: 降序排序
SELECT 字段1,字段2,... FROM 表名 where 条件 ORDER BY 排序的字段 [ASC|DESC];1.单列排序
select * from student3 order by age desc;2.组合排序
-- 格式
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
总结:order by多字段排序,首先根据第一个字段进行排序,如果第一个字段有相同的值,那么按照第二个字段进行排序,如果第二个字段也相同,一次类推;
得出最终结论:如果第一个字段都不相同,那么后边的字段就不会参与排序运算;DQL查询-聚合函数
count：在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。按照列去统计有多少行数据。count(字段)
sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max： 计算指定列的最大值
min： 计算指定列的最小值
avg： 计算指定列的平均值
SELECT 聚合函数(字段) FROM 表名;
统计数据条数count
-- 使用english字段统计学生树龄
select count(english) from student3;-- 7
select count(*) from student3;-- 8统计数值和sum
聚合函数中，若某值为null，则与该值做聚合结果的其他值，将不做处理，同一为null。
-- 方式1
select sum(math)+sum(english) from student3;-- 1206
/*
运算结果与真实值差86 原因:
在mysql中任何值与null进行数学运算结果都为null
在聚合函数中对于null不做处理,所以丢失了86
*/
select sum(math+english) from student3;-- 1120
-- ifnull(值1,值2) 如果值1为null,返回值2,如果值1不为null,那么本身
select sum(ifnull(math,0)+ifnull(english,0)) from student3;-- 1206最值max、min
select max(math) from student3;-- 99
select min(math) from student3;-- 56平均值avg
avg获取平均数小数会有较多位时可通过truncate或者round函数缩短小数为位;
select avg(math) from student3;-- 79.5000
-- 四舍五入的函数 round()
select round(avg(math),0) from student3;-- 80
-- 数字截取 truncate()，小数点后几位
select truncate(avg(math),3) from student3;-- 79.500DQL查询-分组
分组group by
-- 分组格式
select 字段,..... from 表名 group by 字段1,字段2,......
说明:group by后边的字段值相同才能划分为一组;过滤having
-- 语法格式
select 字段,.....from 表名 group by 字段 having 条件;
/*
错误用法:
Query : select color,sum(price) from car where sum(price)>30 group by color LIMIT 0, 1000
Error Code : 1111
Invalid use of group function
*/
select color,sum(price) from car where sum(price)>30 group by color;
-- 正确写法
select color,sum(price) from car group by color having sum(price)>30;where与having区别
1)where后不能跟聚合函数,但是having可用于group by 分组之后结果集的过滤;
2)where能做的having都可以做;
3)分组之前条件过滤用where 之后使用havingDQL查询语句-limit语句
select * from 表名 where 条件 limit offset ,row_count;
-- 说明:offset表示索引偏移量,索引位从0开始;row_count:返回的条数,从索引偏移量开始
limit 0 ,3;-- id=1,2,3
limit 4,2;-- id=5,6
/*
offset :表示查询结果的索引值,从0开始表示第一行数据记录,依次类推,当offset为0时,可以省略不写,如 limit 8 表示返回前8条数据;
row_count:表示要查询的记录数
*/-- 第一页
-- 如果首页索引位从0开始,那么可以不写索引位
select * from student3 limit 4;
-- 第二页
select * from student3 limit 4, 4;DCL用户创建,授权,撤销授权
生产环境中合理的分配数据库权限,对系统的正常运转起到非常好的促进作用!
-- 1.创建普通用户指令
create user '用户名'@'主机名称' identified by '密码';
-- 主机名称为当前用户的ip或者主机名,也可使用%匹配所有地址
-- 2.给用户授权
grant 权限1,权限2,.... on 数据库.表 to '用户名'@'主机名';
-- 3.撤销用户权限
revoke权限1,权限2,.... on 数据库.表名 from '用户名'@'主机名称';
需求1：创建u1用户，只能在localhost这个服务器登录mysql服务器，密码为1234.
create user 'u1'@'localhost' identified by '1234';
需求2：创建u2用户可以在任何电脑上登录mysql服务器，密码为1234
create user 'u1'@'%' identified by '1234';
需求3:给u1用户分配对stu这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询.
grant create,alter,insert,update,select on stu.* to 'u1'@'localhost';
需求4:撤销u1用户对heima01数据库所有(all)表的操作的权限;
revoke all on stu.* from 'u1'@'localhost'; DCL删除用户，修改用户密码
-- 1.删除普通用户命令
drop user '用户名'@'主机名称';
-- 2.修改超级管理员密码
mysqladmin -u root -p password 新密码;
-- 3.修改普通用户密码
set password for '用户名'@'主机名' =password('新密码');
需求1:删除u2
drop user 'u2'@'%';
需求2:修改管理员密码为123456 回车输入密码
需求3:将'u1'@'localhost'的密码改成'666'
set password for 'u1'@'localhost' =password('123456');数据库约束
约束是对表中的数据进一步限制,保证数据正确,有效和完整;
| 约束名称 | 关键字 | 作用 |
|---|---|---|
| 主键约束 | primary key | 唯一+非空 |
| 唯一约束 | unique | 唯一 |
| 非空约束 | not null | 非空 |
| 默认值约束 | default 默认的值 | 指定字段的默认值 |
| 外键约束 | foreign key | 用于多表之间的关系约束 |
主键约束(primary key)
主键能够唯一的标识表中的每一行数据,使用主键约束后,可是数据满足唯一且非空,方便数据管理;
主键(primary key)特点：非空+唯一
1.创建表时创建主键
字段名 类型 primary key
2.在已有表中添加主键
alter table 表名 add primary key(字段);
3.主键自增
字段名 字段类型 primary key auto_increment;
例子：
create table st6(
id int primary key auto_increment
);
-- 主键自增默认从1开始,如果插入了一个值,比如10之后,自增从10开始;唯一约束(unique)
字段值唯一,不可重复
DDL修改 用的不多
alter table 表名 add unique(字段);
创建表时指定
唯一约束可以插入多个null
create table 表名(
字段名 字段类型 unique,
......
);非空约束
约束某个字段不能为空
-- 修改字段非空约束,不常用
alter table 表名 alter 字段 set not null;
-- 创建表时指定
字段名 类型 not null
create table st8(
name varchar(20) not null
);默认值default
给字段添加默认值,如果字段没有被赋值,则使用默认值;
-- 格式1:修改表字段默认值
alter table 表名 alter 字段名 set default 默认值;
-- 创建表是指定默认值
字段名 字段类型 default 默认值;
create table st9(
sex char(2) default '男'
);面试题:非空+唯一约束与主键约束区别
区别:
1)主键可以添加自增,非空+唯一约束不能添加自增;
2)主键约束一张表只能有一个,但是非空+唯一约束可以多个;
3)主键约束底层维护了一个主键索引,而唯一约束维护了一个唯一索引;
外键约束
多对多表结构设计
在多对多的业务场景中,会设计一张中间表简化业务关系,将多对多转换成一对多。
例子：
-- 程序员表
create table coder(
id int primary key auto_increment,
name varchar(20),
salary double
);
-- 项目表
create table project(
id int primary key auto_increment,
name varchar(10)
);
-- 中间表
create table coder_project(
coder_id int,
project_id int
);
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);上述, 中间表的作用是为了维护多对多的关系,但是如果不加相关的约束,会出现以下问题:
1.中间表中可以插入不存在的程序员id或者项目id信息;
2.主表删除信息后,中间表信息依旧保存,不能统一;
3.主表主键更新后,对应的中间表数据没有同步;
添加外键约束格式
-- 方式1:修改字段为外键
alter table 表名 add constraint 外键名称 foreign key(当前表中的字段) references 主表(主表主键);
/*
说明:
constraint 外键名称 :表示约束,同时给约束取名称
foreign key(当前表中的字段):表示给当前表字段添加外键
references 主表(主表主键):表示关联主表中的主键
*/
-- 方式2:创建表时创建外键
create table coder_project(
constraint p_id_fk foreign key(project_id) references porject(id)
);例子：
drop table coder_project;
truncate table coder;
create table coder_project(
coder_id int,
project_id int,
constraint fk_coder foreign key(coder_id) references coder(id),
constraint fk_project foreign key(project_id) references project(id)
);
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);
-- 1.中间表插入不存在的程序员和项目的id，报错
insert into coder_project values(10,10);
-- 2.删除主表的关联的记录，报错
delete from coder where id=1;
-- 3.更新主表的主键，报错
update coder set id=10 where id=1;
-- 此时如果要更新coder中id=1的信息,那么先删除中间表中与之关联的数据
delete from coder_project where coder_id=1;
update coder set id=10 where id=1;
新增数据先增主表再增外键表；删除数据先删外键表中引用数据，再删主表数据。
外键的级联
级联操作就是在修改或者删除主键时可以同时对从表的外键进行修改删除;
on udpate cascade :代表主表进行更新操作后,从表对应的也要进行更新操作;
on delete cascade:代表主表进行主键删除后,从表外键对应的数据也要删除;为中间表添加级联操作，先删除中间表，再删另外两个表，重新建表插入数据。
-- 添加测试数据
-- 程序员表
create table coder(
id int primary key auto_increment,
name varchar(20),
salary double
);
-- 项目表
create table project(
id int primary key auto_increment,
name varchar(10)
);
-- 中间表
create table coder_project(
coder_id int,
project_id int
);
-- 添加测试数据2
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);
-- 添加外键
alter table coder_project add constraint fk_coder foreign key(coder_id) references coder(id) on update cascade on delete cascade;
alter table coder_project add constraint fk_project foreign key(project_id) references project(id) on update cascade on delete cascade;
-- 测试级联删除
delete from coder where id=1;
-- 测试级联更新
update coder set id=10 where id=2;二、数据库的三大范式
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
第一范式（1NF）
要求数据库表中的每一列保持原子性(不可分割),换句话说,第一范式要求数据库中的表都是二维表.
及同一字段值只能存在一种数据，反例：（本科，大四）存在一个字段值中。
第二范式
在满足第一范式的基础上,表中的每一个字段都完全依赖于主键;
定义:数据库的表中不存在非关键字段对任一候选关键字段的部分依赖.
特点:消除部分依赖;
说明：学生宿舍管理系统中每一条记录是由学生和宿舍联合才能确认唯一,此时学生姓名和宿舍编号就作为表的关键字段,而年龄和电话仅仅部分依赖关键字段,宿舍楼层,布局等信息也是如此,违反了第二范式;
解决：不能存在部分依赖。
第三范式
1.第三范式是在第二范式的基础上定义的;
2.如果数据表中不存在非关键字段对任意候选关键字段的传递依赖则符合第三范式.
换句话说就是表中不存在字段对主键的传递依赖（即不同时与主键和另外字段有关联）,则符合第三范式;
特点:在第二范式的基础上消除传递依赖;
说明:分类描述字段依赖于分类,然后分类又与商品信息关联,所以存在传递依赖;
解决方式:
反三范式
反三范式相对于三范式的，没有冗余的数据库未必是最好的数据库，有时为了提高数据库的运行效率，就必须降低范式标准，适当保留冗余数据。
在多表查询中,关联的表越多,性能开销越大,同时响应的时间越长;
符合3大范式设计的表结构不一定是最优的表结构,适当的加入一些冗余字段(反3大范式)之后,提供了数据查询的效率;
三、多表查询介绍和笛卡尔积
多表查询分类
笛卡尔积
数据：
-- 价格 1
create table price(
id int primary key auto_increment,
price double
);
-- 水果 n
create table fruit(
id int primary key auto_increment,
name varchar(20) not null,
price_id int,
foreign key(price_id) references price(id)
);
insert into price values(1,2.30);
insert into price values(2,3.50);
insert into price values(4,null);
insert into fruit values(1,'苹果',1);
insert into fruit values(2,'橘子',2);
insert into fruit values(3,'香蕉',null);
-- 一种水果有一个价格 一个价格对应多种水果
-- 价格 1 水果 n 水果将价格主键作为外键1.笛卡尔积现象
-- 运行sql
select * from fruit,price;
2.避免笛卡尔积
使用条件过滤
select * from fruit,price where fruit.price_id=price.id;
-- 条件过滤笛卡尔积内连接
查询的内容，若一表有数据，另外一表无对应数据，则不展示。
1.隐式内连接
看不到join关键字,条件使用where指定
语法：
select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;
select * from fruit,price where fruit.price_id = price.id;说明：在产生两张表的笛卡尔积的数据后，通过条件筛选出正确的结果。
2.显式内连接
显示内连接：使用INNER JOIN ... ON语句, 可以省略INNER
语法：
select * from 表名1 inner join 表名2 on 条件;
或者
select * from 表名1 join 表名2 on 条件3.测例：内连接
查询苹果的信息，显示苹果id,名字，和价格
-- 隐式
SELECT f.id , f.name , p.price FROM fruit f , price p WHERE f.price_id = p.id;
-- 显示
SELECT f.id , f.name , p.price FROM fruit f join price p on f.price_id = p.id;外连接
查询的内容，以左表为主，若左侧有数据，右侧无对应数据，仍然显示左侧数据，右表展示null。
1.左外连接
select * from 表1 left outer join 表2 on 条件;
把left关键字之前的表，定义为左表。 left关键字之后的表，定义为右表。
不管能否查到水果对应的价格，都要把水果显示出来
select * from fruit f left join price p on f.price_id = p.id;2.右外连接
如果右侧有数据，左侧没匹配到，把右侧的数据显示出来。
语法：select * from 表1 right outer join 表2 on 条件;
right之前的是左侧，right之后的是右侧。
不管能否查到价格对应的水果，都要把价格显示出来
SELECT * FROM fruit f RIGHT JOIN price p on f.price_id = p.id;子查询介绍
子查询就是一个SQL查询的结果作为另一个SQL查询语句语法的一部分;
子查询结果分类
一个部门有多个员工,一个员工只能属于一个部门,关系由员工维护;
-- 创建部门表 1
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
-- 创建员工表 n
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
foreign key(dept_id) references dept(id)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);1.单行单列
< > = ...
-- 查询工资最高的员工是谁？
select max(salary) from emp;
SELECT * from emp where salary = '9000';
SELECT * from emp where salary = (select max(salary) from emp);
-- 查询工资小于平均工资的员工有哪些？
SELECT avg(salary) FROM emp;
SELECT * from emp where salary < '5860';
SELECT * from emp where salary < (SELECT avg(salary) FROM emp);2.多行单列
in not in
-- 查询工资大于5000的员工，来自于哪些部门的名字
--1）查询工资大于5000的员工的部门id
SELECT DISTINCT dept_id from emp where salary > '5000';
--2）查询在该部门id中的名字
SELECT * FROM dept where id in (SELECT DISTINCT dept_id from emp where salary > '5000');
-- 查询开发部与财务部所有的员工信息
select distinct id from dept where name in ('开发部','财务部');
select * FROM emp where dept_id in (select distinct id from dept where name in ('开发部','财务部'));
3.多行多列
注意:子查询如果是多行多列,一般使用as取别名作为表进行关联查询
说明:对于多行多列,首先需要用as取别名,然后与其他表关联查询;
将查询结果作为一张表,**使用as取别名**后关联查询
-- 查询出2011年以后入职的员工信息，包括部门名称
-- 1）查询员工表中是2011年之后入职的员工信息
SELECT * from emp where join_date >= '2011-01-01';
-- 2）将上述查询结果取别名作为一张表与部门表关联查询
SELECT a.*,dept.name FROM (SELECT * from emp where join_date >= '2011-01-01') as a,dept where a.dept_id = dept.id;
-- 直接查
select emp.*,dept.name from emp,dept where emp.dept_id=dept.id and emp.join_date >'2011-01-01';四、常用函数
1、字符串函数
| 函数 | 描述 | 实例 |
|---|---|---|
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 | SELECT CHAR_LENGTH('嘀嘀哒哒') AS '长度'; |
| CONCAT(s1,s2,...,sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | SELECT CONCAT('于大爷','穿','貂丁'); |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 | SELECT LOWER('AOTEMAN'); |
| UPPER(s) | 将字符串转换为大写 | SELECT UPPER("aoteman"); |
| SUBSTR(s, start,length) | 从字符串 s 的 start 位置(从1开始)截取长度为 length 的子字符串 | SELECT SUBSTR("有多少艾克以重来",4,2); |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | SELECT TRIM(' 迪迦 ') |
select CHAR_LENGTH('嘀嘀嘀哒哒哒') '字符串长度';
select CONCAT('于大爷','穿','貂丁') '字符串合并';
SELECT LOWER('AOTEMAN') '大写转小写';
SELECT upper('aoteman') '小写转大写';
SELECT SUBSTR("有多少艾克以重来",4,2) '字符串截取';
SELECT trim(' 迪迦 ') '去前后空格';2、数字函数
| 函数 | 描述 | 实例 |
|---|---|---|
| RAND() | 返回 0 到 1 的随机数 | SELECT RAND(); |
| ROUND(小数 , 小数点后保留小数位数) | 四舍五入保留几位小数 | SELECT ROUND(3.1415926,2) ; |
| TRUNCATE(小数 , 小数点后保留小数位数) | 不会四舍五入保留几位小数 | SELECT TRUNCATE(3.1415926,3); |
| LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 | SELECT LEAST(13, 14, 521, 74, 1) |
| GREATEST(expr1, expr2,expr3, ...) | 返回列表中的最大值 | SELECT GREATEST(13, 14, 521, 74, 1) |
SELECT rand() '0-1之间随机小数';
SELECT round(3.1415926,3) '四舍五入保留几位小数';
SELECT TRUNCATE(3.1415926,3) '不四舍五入保留几位小数';
SELECT LEAST(13, 14, 521, 74, 1) '返回列表中的最小值';
SELECT GREATEST(13, 14, 521, 74, 1) '返回列表中的最大值';3、日期函数和高级函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| NOW() 和 SYSDATE() | 返回系统的当前日期和时间 | SELECT NOW(); 或 SELECT SYSDATE(); |
| CURDATE() | 返回当前日期 | SELECT CURDATE(); |
| CURTIME() | 返回当前系统时间 | SELECT CURTIME(); |
| YEAR(d) | 返回d的中的年份 | SELECT YEAR(NOW()); |
| MONTH(d) | 返回d的中的月份 | SELECT MONTH(NOW()); |
| DAY(d) | 返回d中的日 | SELECT DAY(NOW()); |
-- 获取当前时间
select now();-- 2022-09-16 09:40:39
select sysdate();
-- 获取当前的日期
select curdate();-- 2020-09-16
-- 获取当前的时间
select curtime();-- 09:42:00
-- 获取年份
select year(now());
select ename, year(hiredate) from emp;
select year('2009-09-09');-- 2009
select year('2009/09/09');-- 2009
select year('2009.09.09');-- 2009
-- 获取月份
select month('2009.09.09');-- 9
-- 获取日
select day(now());-- 16| 函数名 | 描述 | 实例 |
|---|---|---|
| CURRENT_USER() | 返回当前用户 | SELECT CURRENT_USER(); |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 | SELECT IFNULL(null,'Hello Word') |
| ISNULL(expression) | 判断表达式是否为 NULL,为空则为1,不为空则为0 | SELECT ISNULL(NULL); |
SELECT CURRENT_USER() '当前用户';
SELECT IFNULL(null,'Hello Word') '若第一个值非NULL则返回，否则返回第二个';
SELECT ISNULL(NULL) '判断表达式是否为NULL,为则1,不为则0';五、事务
事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。
1、应用场景
1.转账业务:比如a给b转账,那么a扣款和b加款两部操作做要么都成功,要么都失败;
2.比如:学生管理系统中,要删除一个学生，那么需要删除学生的基本资料同时，也要删除和该学生相关的选课、成绩等信息;
2、手动提交事务
MYSQL中可以有两种方式进行事务的操作：
-
手动提交事务：先开启，再提交
-
自动提交事务(默认的):即执行一条sql语句提交一次事务。
事务有关的SQL语句：
| SQL语句 | 描述 |
|---|---|
| start transaction; begin; | 开启手动控制事务 |
| commit; | 提交事务 |
| rollback; | 回滚事务 |
# 创建账号表
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
# 初始化数据
insert into account values (null,'a',1000);
insert into account values (null,'b',1000);
需求1:a给b转账100元,演示提交事务和回滚事务
-- 1)转账成功
start transaction;-- 开启事务
update account set money=money-100 where name='a';-- a扣款100
update account set money=money+100 where name='b';-- b收款100
commit;-- 提交事务
-- 2)失败回滚操作
start transaction;-- 开启事务
update account set money=money-100 where name='a';-- a扣款100
update account set money=money+100 where name='b';-- b收款100
rollback;-- 回滚事务3、自动提交事务
MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。
1.通过以下命令可以查看当前autocommit模式：
show variables like '%commit%';2.设置自动提交的参数为OFF:
set autocommit = 0; -- 0:OFF 1:ON4、事务的回滚点
上面的操作，如果回滚，直接回滚到事务开始前。有时我们只需要回滚到中间的某个位置，就可以设置回滚点。
| 回滚点的操作语句 | 语句 |
|---|---|
| 设置回滚点 | savepoint 名字 |
| 回到回滚点 | rollback to 名字 |
-- 1. 将数据还原到1000
START TRANSACTION; -- 2. 开启事务
-- 3. 让a用户先减100块
UPDATE account set money = money - 100 where id = '1';
SAVEPOINT p1; -- 4. 设置回滚点：p1
-- 5. 然后让a用户再次减100块
update account set money = money - 50 where id = '1';
ROLLBACK to p1; -- 6. 回到回滚点：rollback to p1
COMMENT; -- 7. 最后commit提交事务5、事务原理
1.当客户端连接mysql服务端后,经过校验后,会生成一个临时的事务日志文件;
2.当客户端直接发送sql时,数据会直接输入数据库中,不会写入临时事务日志文件;
3.当客户端手动开启事务时(start transaction),事务相关的会先写入临时事务日志文件,当客户端提交时,就会将临时事务日志文件中的数据写入数据库,同时写入成功后,会将写入的结果写入这个临时的事务日志文件;(redo.log,undo.log)
4.当客户端与服务端断开连接的时候,临时事务日志文件中的数据自动清空;
6、事务的四大特性(ACID)(面试)
数据库的事务必须具备ACID特性，ACID是指 Atomicity（原子性）、Consistensy（一致性）、Isolation（隔离性）和Durability（持久性）的英文缩写。
1、隔离性（Isolation）
多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
举例：临时事务文件互不影响。
2、持久性(Durability)
指一个事务一旦被提交，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据依然存在。
举例：
3、原子性(Atomicity)
原子性是指事务包装的一组sql(一组业务逻辑)是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
4、一致性(Consistency)
一致性是指数据处于一种语义上有意义且正确的状态；
事务一致性是指事务执行的结果必须是使数据从一个一致性状态变到另一个一致性状态。
事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性
7、事务的并发访问引发的三个问题(面试)
事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。
| 并发访问的问题 | 含义 |
|---|---|
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据。最严重，杜绝发生。 |
| 不可重复读 | 一个事务中多次读取的数据内容不一致，要求的是一个事务中多次读取时数据是不一致的，这是事务update时引发的问题 |
| 幻读（虚读） | 一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同。这是insert或delete时引发的问题 |
1、脏读：
指一个事务读取了另外一个事务未提交的数据。(非常危险)
说明:脏读强调一个事务读取了未提交的事务的数据;
2.不可重复读：
在一个事务内多次读取表中的数据，多次读取的内容不同。
说明:不可重复读就是一个事务读取了其他事务提交前后的数据,造成两次读取不一致的情况,多发生在事务update操作时;
说明：发生不可重复读的前提是1）事务多次读取相同条件下的数据2）另一个事务修改（update）完毕后事务进行了提交操作；
3.幻读（虚读）
一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同,多发生在delete或insert时；
8、事务的隔离级别
MySQL数据库规范规定了4种隔离级别，用于解决上述出现的事务并发问题;
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
|---|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
| 2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle和SQL Server |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL |
| 4 | 串行化 | serializable | 否 | 否 | 否 |
说明：其实三个问题，开发中最严重的问题就是脏读，这个问题一定要避免，而关于不可重复读和虚读其实只是感官上的错误，并不是逻辑上的错误。就是数据的时效性，所以这种问题并不属于很严重的错误。如果对于数据的时效性要求不是很高的情况下，我们是可以接受不可重复读和虚读的情况发生的。
1.安全和性能对比
安全: 串行化>可重复读>读已提交>读未提交
性能: 串行化<可重复读<读已提交<读未提交
小结:数据库有哪些隔离级别
1)读未提交: 脏读 不可重复读 幻读
2)读已提交: 不可重复读 幻读
3)可重复读:幻读
4)串行化
2.解决脏读
调高数据库隔离级别 >= 读已提交 read committed3.查看事务隔离级别
select @@tx_isolation;4.设置隔离级别
set global transaction isolation level 隔离级别名称;
设置隔离级别 读已提交
set global transaction isolation level read committed;
1841
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
