一、DDL-操作数据库表结构表字段
1.ddl-数据库操作
- show databases; 查看有哪些数据库
- create database 数据库名;创建数据库
- use 数据库; 使用数据库
- select database; 查看当前数据库
- drop database数据库名; 删除数据库
2.ddl-表操作
- show tables;查看当前数据库表名
- create table 表名(字段 字段类型 【comment 注释】,字段 字段类型【comment 注释】)【comment 注释】;创建表
- desc 表名;查看当前表字段
- show create table 表名;查看建表语句
- alter table 表名 add/modify/change/drop/rename to;
- drop table 表名; 删除表
二、DML对数据表中数据进行增删改
1.添加数据insert 对指定字段添加值: insert into 表名(字段1,字段2,...)values(值1,值2,...)[(值1,值2...)...] ,对所有数据就不用写字段
2.修改数据update
update 表名 set 字段1=值1,字段2=值2 [where 条件];
3.删除数据delete
delete from 表名 [where 条件]
三、DQL-查询
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数
1.基本查询
1)查询多个字段
一、查询指定字段
select name,workno,age from emp;
二、查询所有字段
select * from emp;(尽量不用*)
2)设置别名
select workaddress as '工作地址' from emp;#as可省略
3)去除重复记录
select distinct workaddress from emp;
2、条件查询
#查询年龄鞥预报18或20或40的员工信息
select *
from emp
where age=18 or age=20 or age=40;
select *
from emp
where age in(18,20,40);
#年龄在15到20之间
select *
from emp
where age >= 15 && age <= 20;
select *
from emp
where age >= 15 and age <= 20;
select *
from emp
where age between 15 and 20;
#查询姓名为两个字的员工信息
select *
from emp
where name like '__';
#查询身份证号最后一位是x的员工信息
select *
from emp
where idcard like '%X'; #前面是什么无所谓,最后一个数是x即可
3.聚合函数
- 语法:select 聚合函数(字段)from 表名
- 注:所有的null值不参与聚合函数的运算
-- 统计企业员工数量 select count(*) from emp; select count(idcard) from emp; -- 统计该企业的平均年龄 select avg(age) from emp ; -- 求最大年龄 select max(age ) from emp; -- 最小年龄 select min(age ) from emp; -- 统计西安地区的年龄之和 select sum(age) from emp where workaddress='西安';
4.分组查询
语法:select 字段列表 from 表名[where 条件 ] group by 分组字段名[having 分组后过滤条件]
/*
where 和 having 区别
1.执行形式及不同:where是对分组前进行过滤,不满足条件,不参与分组,having是对分组后结果进行过滤
2.判断条件不同,where不可使用聚合函数,having可以
*/
-- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
-- • 执行顺序: where > 聚合函数 > having 。
-- • 支持多字段分组, 具体语法为 : group by columnA,columnB
-- A. 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender ,count(*) from emp group by gender;
-- B. 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender ,avg(age) from emp group by gender;
-- 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count
from emp
where age<45
group by workaddress
having address_count>=3;
5.排序查询
1)语法SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2
2)排序方式
- acs升序
- desc降序
6.分页查询
select 字段列表 from 表名 limit 起始索引,索引记录数
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
7.执行顺序
四、DCL-用来管理数据库用户,控制数据库的访问权限
1.用户管理
1)、查询用户
select * from mysql.user;
2).创建用户
create user '用户名'@‘主机名’ identified by ‘密码’;
主机名可以用%代替
3).修改用户密码
alter user ‘用户名’@‘主机名’ identified with mysql_native _password by '新密码';
4).删除用户
drop user ‘用户名’@‘主机名’;
2.权限控制
1)查询权限
show grants for '用户名'@‘主机名’
show grants for 'heima'@'%';
2)授予权限
grant 权限列表 on 数据库名.表名 to ‘用户名’@'主机名';
grant all on itheima.* to 'heima'@'%';
3)撤销权限
revoke 权限列表 on 数据库名.表名 from ‘用户名’@'主机名';
revoke all on itheima.* from 'heima'@'%';
五、外键
-- ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
-- 添加外键
alter table emp add constraint fk_emp_id foreign key (dept_id) references dept (id);
-- 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
alter table emp drop foreign key fk_emp_id;
-- CASCADE
-- 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则
-- 也删除/更新外键在子表中的记录
-- set null
-- 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表
-- 中该外键值为null(这就要求该外键允许取null)。
六、多表查询
1.多表关系
- 一对一
- 一对多
- 多对多
2.多表查询
1.笛卡尔积: 笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。
select *
from emp,dept;
2.多表查询分类
- 内连接:相当于查询A、B交集部分数据
1)隐式内连接
-- SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
select emp.name,dept.name
from emp,dept
where emp.dept_id=dept.id;
2)显示内连接
-- SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
select e.name,d.name
from emp e
inner join dept d on e.dept_id = d.id;
- 外连接:
1)左外连接:查询左表所有数据,以及两张表交集部分数据
-- SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
-- 查询emp所有数据,和对应的部门信息
select e.*,d.name
from emp e
left outer join dept d on d.id = e.dept_id;
2)右外连接:查询右表所有数据,以及两张表交集部分数据
-- SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
-- 查询dept所有数据,和对应的员工信息
select d.*,e.*
from emp e
right outer join dept d on e.dept_id = d.id
--右外可改为左外
select d.*,e.*
from dept d
left outer join emp d on e.dept_id = d.id
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
-- SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
-- 查询员工及其所属领导的名字
select a.name,b.name
from emp a,emp b
where a.managerid=b.id;
-- 查询所有员工emp,及其领导的名字 emp,如果员工没有领导也要查询出来
-- 外连接
select a.name '员工',b.name'领导'
from emp a
left join emp b on a.managerid=b.id;
联合查询union
select a.name '员工',b.name'领导'
from emp a
left join emp b on a.managerid=b.id;
-- A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
select *
from emp
where salary<5000
union
select *
from emp
where age>50;
- 子查询
1)根据结果分
- 标量子查询--子查询结果返回的是单个值
- 常用操作符:= <> > >= < <=
-- A. 查询 "销售部" 的所有员工信息
select *
from emp
where dept_id=(select id
from dept
where name='销售部');
--销售部id只有一个所以叫标量子查询
- 列子查询--子查询返回结果为一列
- 常用操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
-- A. 查询 "销售部" 和 "市场部" 的所有员工信息
select *
from emp
where dept_id in(select id from dept where name='销售部' or name='市场部');
any表示子查询返回列表中,有任意一个满足即可,
查询比研发部其中任意一人工资高的员工信息
select *
from emp
where salary>any(select salary
from emp
where dept_id=(select id
from dept
where name='研发部')
);
all表示子查询返回列表中所有值都满足
-- 比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );
- 行子查询-子查询返回结果是一行
-
常用的操作符: = 、 <> 、 IN 、 NOT IN
select *
from emp
where (salary,managerid)=(select salary,managerid
from emp
where name='张无忌');
- 表子查询:子查询返回的结果是多行多列,这种子查询称为表子查询
-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*,d.*
from (select *from emp where entrydate>'2006-01-01') e left join dept d on e.dept_id=d.id;
七、事务
1.概念:事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
2.事务操作
方式一:
SELECT @@autocommit ;
SET @@autocommit = 0 ;-- 变手动提交
commit;
rollback;
方式二:
start transaction;后begin;
commit;
rollback;
3.事务的四大特性
1)原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
2)一致性:事务完成时,必须使所有的数据都保持一致
3)隔离性:数据库系统提供的隔离机制,保证事务不受外部并发操作影响的独立环境下运行
4)持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
4.并发事务问题
脏读:一个事务读到另外一个事务还没有提交的数据。
不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据
5.事务的隔离级别
串行化:即只有a事务提交后,才能执行b事务
事务隔离级别越高,数据越安全,但是性能越低
查看事务隔离级别:
select @@transaction_isolation;
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }