Mysql操作语句
-
登陆mysql数据库管理系统
在终端输入mysql -uroot -p
【注】没写u报错:Ignoring query to other database解决方法 -
查看有哪些数据库
show databases;
【注】别忘了分号
-
创建数据库
create database Test;
-
使用Test数据库
use Test;
-
查看数据库表的信息
show tables;
-
初始化信息(建完表保存为.sql文件后)
source xxxx
【注】xxxx为.sql文件路径
当一个文件的拓展名是.sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本
- 删除数据库
drop database XXX;
- 查看表结构
desc 表名;
- 查看表中数据
select * from 表名;
- 常用命令:
select version();
查看版本号select database();
查看当前使用哪个数据库exit
退出mysql
sql语句
【注】不区分大小写
简单的查询语句(DQL)
-
select 字段名1 ,字段名2 … from 表名;
【注】字段可以参与数学运算- 查询结果集的去重
select distinct birthDate from passenger;
【注】distinct 只能出现在所有字段的最前面,是后面所有字段联合起来去重。
e.g 统计岗位的数量:
select count(distinct job) from passenger;
-
给查询结果的列重命名
select 字段名1 ,字段名2 as xxx from 表名;
【注】别名中有中文,用 ‘ ’
条件查询
语法格式
select
字段,字段...
from
表名
where
条件
查找在区间范围内的值
select ID from depoit where deposit between 100 and 200;
【注】 between 为闭区间
is null & is not null
空不是一个值,不能用等号衡量,必须使用is null 或者is not null
and 和 or 联合起来用
注意 and 的优先级最高,需要加括号,否则查询出错。
in 等同于 or
select ename,job from emp where job = 'teacher' or job = 'manager';
select ename,job from emp where job in('teacher','manager');
not in 不在这几个值当中
模糊查询 like
% 代表任意多个字符
找出名字中有Z的:
select ename form emp where ename like '%Z';
_ 代表任意一个字符
找出名字中第三个字符为Z的:
select ename from emp where ename like '_ _Z%';
找出数据中包含下划线的:
select ename from emp where ename like '%\_%'
找出数据中最后一个字母为 S 的:
select ename from emp where ename like '%S'
排序
语法格式
select
字段,字段... 3
from
表名 1
where
条件 2
order by
条件 4
注意:默认是升序。order by 是最后执行的。
- asc表示升序
- desc表示降序
e.g
select ename,sal from emp order by sal ;//默认升序
select ename,sal from emp order by sal desc;//降序
查询时可以不写字段值,写该字段值所在的列数,但不推荐
多个字段排序
越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。
e.g 按照工资的降序排列,当工资相同时再按照名字的升序排列。
select ename,sal from emp order by sal desc,ename asc ;
分组函数/聚合函数/多行处理函数
分组函数(多行处理函数)
- count 计数
- sum 求和
- avg 平均
- max 最大值
- min 最小值
所有的分组函数都是对“某一组” 数据进行操作的。输入多行,最终输出一行。且 分组函数自动忽略 null
【注】分组函数不能直接出现在 where 语句中
e.g 找出工资高于平均工资的员工
- 第一步:找出平均工资
select avg(val) from emp;
- 第二步:找出高于平均工资的员工
select ename,sal from emp where sal > xxx;
- 整合(子查询)
select ename,sal from emp where sal > (select avg(val) from emp);
select
字段,字段... 5
from
表名 1
where
条件 2
group by
.. 3
having
.. 4
order by
条件 6
- count(*):不是统计某个字段数据的个数,而是统计总记录条数。(和某个字段无关)
- count(某个字段):表示统计该字段中不为 null 的数据总数量。
- 分组函数也能组合起来用
单行处理函数
单行处理函数:输入一行,输出一行
只要有 null 参与的运算,结果一定为 null
- ifnull(可能为 null 的数据,被当作什么处理)
group by & having
- group by:按照某个字段或者某些字段进行分组
- having:分组之后的数据进行再次过滤
e.g 找出每个工作岗位的最高薪资
select max(sal) from emp group by job;
【注】分组函数一般都会和 group by 联合使用。
任何一个分组函数(count sum avg max min)都是在 group by 语句执行完之后才会执行。当一条 sql 语句没有group by 的话,整张表的数据会自成一组。
【规则】:当一条语句中有 group by 的话,select 后面只能跟分组函数和参与分组的字段。
- 多字段分组查询
e.g 找出每个部门 不同工作岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno,job;
- having 的使用
e.g 找出每个部门薪资大于2900的
select max(sal),deptno from emp group by deptno having max(sal) > 2900; 效率低
select max(sal),deptno from emp where sal > 2900 group by deptno ; 效率高
【注:有时候必须要使用 having 】
e.g 找出每个部门的平均薪资,要求显示薪资大于 2000 的数据。
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。
连接查询的分类
【根据表的连接方式来划分】
- 内连接:
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全连接
笛卡尔乘积现象
当两张表进行连接查询的时候,没有任何条件进行限制,最终查询结果条数是两张表记录条数的乘积。
内连接之等值连接
最大特点:条件是等量关系
语法:
select
...
from
A
join
B
on
连接条件
where
...
e.g 查询每个员工的部门名称,要求显示员工名和部门名
select
e.name,d.dname
from
emp e
inner join //inner可以省略
dept d
on
e.deptno = d.deptno
内连接之非等值连接
on 后的条件是一个不等关系
自连接
一张表看作两张表。自己连接自己。
e.g 找出每个员工的上级领导,要求显示员工名和对应的领导名
select
a.ename as '员工名' , b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno;
外连接
假设A和B表进行连接,使用外连接的话,AB两张表有一张是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出 null 与之匹配。
- 左外连接(左连接):表示左边这张表是主表
- 右外连接(右连接):表示右边这张表是主表
- 左连接有右连接的写法,右连接也有左连接的写法
e.g 找出每个员工的上级领导,要求显示员工名和对应的领导名
select
a.ename as '员工名' , b.ename as '领导名'
from
emp a //以a为主表,若a中有 null,自动模拟出 null 匹配
left join
emp b
on
a.mgr = b.empno;
- 外连接最重要的特点是:主表的数据无条件的全部查询出来
多表连接查询
语法结构:
...
from
A
join
B
join
C
on
...;
e.g 找出每一个员工的部门名称以及工资等级
select
e.ename,d.dname,s.grade
from
emp p
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
子查询
select 语句当中嵌套 select 语句,被嵌套的 select 语句是子查询
- where 后嵌套子查询
- from 后嵌套子查询
e.g 找出每个部门平均薪水的薪资等级
第一步:找出每个部门的平均薪水(按部门编号分组,求 sal 的平均值)
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步:将以上查询结果当作临时表 t ,让 t 表和 salgrade s 表连接,连接的条件是:t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptnot) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
- 在 select 后面嵌套子查询
union
union 可以将结果集相加
e.g 找出工作岗位是 SALESMAN 和MANAGER 的员工
select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman';
limit分页查询
limit 是 mysql 特有的,其他数据库没有,不通用。
limit 作用:取结果集中的部分数据
语法机制:
limit startIndex,length //startIndex 表示起始位置,从0开始,0表示第一条数据
//length 表示取几个
【注】limit 是 sql 语句执行的最后一个环节
e.g 找出工资排名在第4到第9名的员工
select ename,sal from emp order by sal desc limit 3,6;
- 通用的标准分页 sql
每页显示 pageSize 条记录:
第 pageNo 页:(pageNo - 1)* pageSize,pageSize
数据定义语言DDL
创建表
- 创建表的语句
create table 表名(
字段名1 数据类型 约束
字段名2 数据类型 约束
字段名3 数据类型 约束
...);
- 常见的字段的数据类型
数据类型 | 含义 |
---|---|
int | 整数型 |
bigint | 长整型(java 中的 long) |
float | 浮点型 |
char | 定长字符串 |
varchar | 可变字符串 |
date | 日期类型(对应 java 中的 java.sql.Date 类型) |
BLOB | 进制大对象(存储图片、视频等流媒体信息) |
CLOB | 字符大对象(存储较大文本,比如,可以存储4G的字符串) |
- char 和 varchar 怎么确定
- 若某个字段的数据长度不发生改变的时候,是定长的,例如:性别、生日,采用 char
- 若某个字段的数据长度不确定,例如:简介、姓名等都是采用 varchar
- 表名在数据库当中一般建议以:t_ 或者 tbl_ 开始
create table t_student(
num bigint,
name varchar(255),
birth char(10)
);
插入数据 insert
- 语法格式:
insert into 表名(字段名1,字段名2,...)values(值1,值2,...)
【注】字段的数量和值的数量相同,并且数据类型要对应相同。
insert into t_student(num,name,birth) values(1,'wangwu','1980-9-10');
【注】
- 可以调换字段的顺序、或者只添加一个字段。但是待 insert 语句执行成功之后,记录当中其他字段是则是 null,后期也没有办法在执行 insert 语句插入数据了,只能用 update 进行更新。
- 若字段省略,value 就要按照顺序写
insert into t_student values(1,'wangwu','1980-9-10');
- 一次插入多行
insert into t_student(num,name,birth) values(1,'wangwu','1980-9-10'),(2,'zhangsan','1981-9-10');
表的复制
- 语法
create table 表名 as select 语句; //将查询结果当作表创建
- 将查询结果插入到一张表中
insert into 表名1 select * from 表名2;
修改数据 update
- 语法格式
update 表名 set 字段名1 = 值1,字段名1 = 值1... where 条件; //若全改,则不加where
删除数据 delete
- 语法格式
delete from 表名 where 条件; //若删除全部,则不加where
- 删除大表 (重点)
truncate table 表名; //表被截断,不可回滚,永久丢失
约束
什么是约束
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束
- 非空约束 (not null):约束的字段不能为空
- 唯一约束 (unique):约束的字段不能重复
- 主键约束 (primary key):约束的字段既不能为 null ,也不能重复(简称PK)
- 外键约束(foreign key):…(简称FK)
- 检查约束(check):Oracle 数据库有,mysql 没有
唯一性约束
- 唯一约束修饰的字段具有唯一性,不能重复。但可以为 null
- 给多个列添加 unique
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255)
unique(usercode,username) //多个字段联合起来添加1个约束【表级约束】
);
【注】和在usercode、username 后分别加 unique 的区别!(列级约束)分别加只要有一个相同即重复,而联合则是判断连在一起是否重复。
主键约束
主键字段中的数据不能为 null ,也不能重复
drop table if exists t_user;
create table t_user(
id int primary key,
usercode varchar(255),
username varchar(255)
);
- mysql提供主键值自增
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
usercode varchar(255),
username varchar(255)
);
外键约束
A 表中的字段引用 B 表中的字段,则称 A 表为子表,B 表叫父表
- 顺序要求
1、删除数据时,先删除子表,再删除父表。
2、添加数据时,先添加父表,再添加子表。
3、创建表时,先创建父表,再创建子表。
4、删除表时,先删除子表,再删除父表。
create table t_user(
id int ,
usercode varchar(255),
foreign key(classno) references t_class(cno)
);
【注】
- 外键值可以为 null
- 被引用的字段不一定是主键,但至少有 unique 约束
存储引擎
存储引擎这个名字只有在 mysql 中存在。mysql 支持很多存储引擎,每一个存储引擎都对应了一种不同的存储方式
- 查看当前的存储引擎
show engines XX
事务
什么是事务
一个事务是一个完整的业务逻辑单元,不可再分
事务的存在是为了保证数据的完整性,安全性
【注】和事务相关的语句只有 DML 语句 (insert delete update)
因为他们三个语句都是和数据库表当中的“数据”相关
事务的特性
事务包括四大特性:ACID
A:原子性:事务是最小的工作单元,不可再分
C:一致性:事务必须保持多条DML语句同时成功或者失败
I:隔离性:事务A与事务B之间具有隔离性
D:持久性:最终数据必须持久化到硬盘文件中,事务才算成功
事务的隔离性
事务隔离性存在隔离级别,理论上包括4个级别:
- 第一级别:读未提交(read uncommiteed)
我们当前事务可以读到对方未提交的数据
读未提交存在脏读现象:表示读到了脏数据 - 第二级别:读已提交
对方事务提交之后的数据我方可以读到
存在的问题:不可重复读 - 第三级别:可重复读(repeatable read)
这种隔离级别解决了 不可重复读
存在的问题:读取到的数据是幻想 - 第四级别:序列化/串性化读
解决了所有问题
存在的问题:效率低
oracle 数据库默认的隔离级别是:读已提交
mysql 数据库默认的隔离级别是:可重复读
演示事务
【注】mysql 事务默认情况下是自动提交的(只要执行任意一条DML语句则提交一次)
如何关闭:start transaction
索引
什么是索引
在数据库中,查询一张表的时候有两种检索方式:
- 全表扫描
- 根据索引检索(效率高)
索引采用的底层数据结构是:B + Tree
怎么创建、删除索引对象
创建:
create index 索引名称 on 表名(字段名);
删除
create index 索引名称 on 表名(字段名);
什么时候考虑给字段添加索引
- 数据量庞大
- 该字段很少有 DML 操作
- 该字段经常出现在 where 字句中
【注】主键和具有 unique 的约束字段会自动添加索引
视图(view)
站在不同的角度去看待数据
怎么创建、删除视图
create view myview as select name from student;
drop view myview;
【注】只有 DQL 语句才能以视图对象的方式创建出来
对视图进行增删改查,会影响到原表数据
视图的作用
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图。
作业题
部门表
员工表
薪资水平表
- 取得每个部门最高薪水的人员名称
- 取得每个部门最高薪水
select max(sal),deptno from emp group by deptno;
- 将以上结果当作临时表 t ,t 表和 emp e 表进行连接,条件是 t.deptno = e.deptno and t.maxsal = e. sal
select
e.ename, t.*
from
emp e
join
(select deptno,max(sal) as maxsal from emp group by deptno) t
on
t.deptno = e.deptno and t.maxsal = e.sal;
- 哪些人的薪水在部门的平均薪水之上
- 找出部门的平均薪水
select deptno,avg(sal) as avg from emp group by deptno t
- 将以上查询结果当做t表,t 和 emp 表连接
条件:部门编号相同,并且 emp 的 sal 大于 t 表的 avg
select
-> e.ename,e.deptno,e.sal
-> from
-> emp e
-> join
-> (select deptno,avg(sal) as avg from emp group by deptno) t
-> on e.deptno = t.deptno and e.sal > t.avg ;
3. 取得部门中(所有人的)平均的薪水等级
- 找出每个人的薪水等级 条件:e.sal between s.losal and s.hisal
select
-> e.ename,e.sal,e.deptno,s.grade
-> from
-> emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal;
- 基于以上的结果继续按照deptno分组,求grade的平均值。
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
4. 不准用组函数(Max ),取得最高薪水
- 第一种 sal降序,limit 1
select ename,sal from emp order by sal desc limit 0,1; //0可以省略
- 第二种 用 max 函数
select max(sal) from emp;
- 第三种 表的自连接
select sal
from emp
where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
- 取得平均薪水最高的部门的部门编号
- 找出每个部门的平均薪水(表 t)
select avg(sal) as avg from emp group by deptno;
- 找出以上 avg 的最大值
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
- 整合
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
having
avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
- 取得平均薪水最高的部门的部门名称
- 连接两个表