1.查询语句
select
列名列表
from
表名列表
where...
2.准备sql
USE DATABASE db2;# 创建部门表
CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20));
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');# 创建员工表
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);
3.笛卡尔积:
- 有两个集合A、B,取这两个集合的所有组成情况 (例如:SELECT * FROM emp,dept;)
- 要完成多表查询,需要消除无用的数据
4.多表查询的分类:
1.内连接查询:
1.隐式内连接:使用where条件消除无用数据
例子:
查询所有员工信息和对应的部门信息:SELECT * FROM emp,dept WHERE emp.`dept_id`= dept.`id`;
查询员工表的名称、性别,部门表的名称:SELECT emp.`name`,emp.`gender`,dept.`name` FROM emp,dept WHERE emp.`dept_id`= dept.`id`;
优化写法:
SELECT
t1.`name`, -- 员工表的姓名
t1.`gender`, -- 员工表的性别
t2.`name` -- 部门表的名称
FROM
emp t1,
dept t2
WHERE
t1.`dept_id`= t2.`id`;
2.显式内连接:
语法:select 字段列表 from 表名1 [inner]join 表名2 on 条件;
例如:SELECT * FROM emp INNER JOIN dept ON emp.`dept_id`= dept.`id`;
inner可省略:SELECT * FROM emp JOIN dept ON emp.`dept_id`= dept.`id`;
3.内连接查询:
1.从哪些表中查询数据
2.条件是什么
3.查询哪些字段
2.外连接查询:
1.左外连接:
- 语法:select 字段列表 from 表1 left [outer]join 表2 on 条件;
- 查询的是左表所有数据以及其交集部分
(例如:SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id`= t2.`id`;)
2.右外连接:
- 语法:select 字段列表 from 表1 right [outer]join 表2 on 条件;
- 查询的是右表所有数据以及其交集部分
(例如:SELECT t1.*,t2.`name` FROM emp t1 RIGHT JOIN dept t2 ON t1.`dept_id`= t2.`id`;)
3.子查询:
1.概念:查询中嵌套查询,称嵌套查询为子查询
例如:查询工资最高的员工信息:
两步做法:
1.查询最高的工资是多少 9000:SELECT MAX(salary) FROM emp;
2.查询员工信息,并且工资等于9000的:SELECT * FROM emp WHERE emp.`salary`= 9000;
一步做法(子查询):
SELECT * FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);
2.子查询不同情况
1.子查询的结果是单行单列的:
- 子查询可以作为条件,使用运算符去判断。运算符:>>=<<==
例如:查询员工工资小于平均工资的人:SELECT * FROM emp WHERE emp.`salary`<(SELECT AVG(salary) FROM emp);
2.子查询的结果是多行单列的:
- 子查询可以作为条件,使用运算符in来判断
例如:查询'财务部'和'市场部'所有的员工信息:
做法1:
SELECT id FROM dept WHERE NAME ='财务部' OR NAME ='市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;(优化:SELECT * FROM emp WHERE dept_id IN (3,2);)
子查询做法:
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN ('财务部','市场部'));
3.子查询的结果是多行多列的:
- 子查询可以作为一张虚拟表参与查询
例如:查询员工入职日期是2011-11-11日之后的员工信息和部门信息
普通内连接:
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id`= t2.`id` AND t1.`join_date`>'2011-11-11';
子查询:
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date`>'2011-11-11') t2 WHERE t1.`id`= t2.dept_id;
5.多表查询练习
1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
- 分析:
1.员工编号,员工姓名,工资需要查询emp表;职务名称,职务描述需要查询job表
2.查询条件 emp.job_id = job.id
- 实现:
SELECT
t1.`id`, -- 员工编号
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`jname`, -- 职务名称
t2.`description` -- 职务描述
FROM
emp t1,job t2
WHERE
t1.`job_id`= t2.`id`;
2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
- 分析:
1.员工编号,员工姓名,工资需要查询emp表;职务名称,职务描述需要查询job表;部门名称,部门位置需要查询dept表
2.查询条件:emp.job_id = job.id and emp.dept_id = dept.id
- 实现:
SELECT
t1.`id`, -- 员工编号
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`jname`, -- 职务名称
t2.`description`, -- 职务描述
t3.`dname`, -- 部门名称
t3.`loc` -- 部门位置
FROM
emp t1,job t2,dept t3
WHERE
t1.`job_id`= t2.`id` AND t1.`dept_id`= t3.`id`;
3.查询员工姓名,工资,工资等级
- 分析:
1.员工姓名,工资需要查询emp表;工资等级需要查询salarygrade表
2.查询条件:emp.salary >= salarygrade.losalary amd emp.salary <= salarygrade.hisalary
emp.salary between salarygrade.losalary and salarygrade.hisalary
- 实现:
SELECT
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`grade` -- 工资等级
FROM
emp t1,salarygrade t2
WHERE
t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
- 分析:
1.员工姓名,工资需要查询emp表;职务名称,职务描述需要查询job表;部门名称,部门位置需要查询dept表;工资等级需要查询salarygradet表
2.查询条件:emp.job_id = job.id and emp.dept_id = dept.id and emp.salary between salarygrade.losalary and salarygrade.hisalary
- 实现:
SELECT
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`jname`, -- 职务名称
t2.`description`, -- 职务描述
t3.`dname`, -- 部门名称
t3.`loc`, -- 部门位置
t4.`grade` -- 工资等级
FROM
emp t1,job t2,dept t3,salarygrade t4
WHERE
t1.`job_id`= t2.`id`
AND t1.`dept_id`= t3.`id`
AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
5.查询出部门编号、部门名称、部门位置、部门人数
- 分析:
1.部门编号、部门名称、部门位置需要查询dept表;部门人数需要查询emp表
2.使用分组查询,按照emp.dept_id完成分组,查询count(id)
3.使用子查询将第2步的查询结果和dept表进行关联查询
- 实现:
SELECT
t1.`id`,t1.`dname`,t1.`loc`,t2.total
FROM
dept t1,
(SELECT
dept_id,COUNT(id) total
FROM
emp
GROUP BY dept_id) t2
WHERE t1.`id`= t2.dept_id;
6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
- 分析:
1.姓名需要查询emp表;直接上级的姓名需要查询emp表(emp表的id和mgr是自关联)
2.查询条件:emp.id = emp.mgr
3.查询左表的所有数据,和交集数据(使用左外连接查询)
- 实现:
SELECT
t1.`ename`,
t1.`mgr`,
t2.`id`,
t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr`= t2.`id`;
事务
1.事务的基本介绍
1.概念:
如果一个包含多个步骤的业务操作,被事物管理,那么这些操作要么同时成功,要么同时失败
2.操作:
1.开启事务:start transaction;
2.回滚:rollback;
3.提交:commit;
3.例子:
-- 创建数据表
CREATE TABLE account (id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账500元
-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance = balance - 500 WHERE NAME ='zhangsan';
-- 2.李四账户+500
UPDATE account SET balance = balance + 500 WHERE NAME ='lisi';
-- 发现执行没有问题,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;
4.MySQL数据库中事务默认自动提交
1.事务提交的两种方式:
1.自动提交:
- mysql就是自动提交
- 一条DML(增删改)语句会自动提交一次事务
2.手动提交:
- Oracle数据库默认是手动提交事务
- 需要先开启事务,再提交
2.修改事务的默认提交方式:
- 查看事务的默认提交方式:SELECT @@autocommit; -- 1代表自动提交 0代表手动提交
- 修改默认提交方式:SET @@autocommit = 0;
2.事务的四大特征:
1.原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
2.持久性:当事务提交或回滚后,数据库会持久化的保存数据
3.隔离性:多个事务之间,相互独立
4.一致性:事务操作前后,数据总量不变
3.事务的隔离级别(了解)
1.概念:多个事务之间是隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
2.存在问题:
1.脏读:一个事务,读取到另一个事务中没有提交的数据
2.不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
3.隔离级别:
1.read uncommitted:读未提交
- 产生的问题:脏读、不可重复读、幻读
2.read committed:读已提交(Oracle默认)
- 产生的问题:不可重复读、幻读
3.repeatable read:可重复读(MySQL默认)
- 产生的问题:幻读
4.serializable:串行化
- 可以解决所有的问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- 数据库查询隔离级别:
select @@tx_isolation;
- 数据库设置隔离级别:
set global transaction isolation level 级别字符串;(例如:set global transaction isolation level read committed;)
4.演示:
set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id= 1;
update account set balance = balance + 500 where id= 2;
DCL:管理用户,授权
1.SQL分类:
1.DDL:操作数据库和表
2.DML:增删改表中数据
3.DQL:查询表中数据
4.DCL:管理用户,授权
2.DBA:数据库管理员
3.DCL:管理用户,授权
1.管理用户
1.添加用户:
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';(例如:CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123';)(例如:CREATE USER 'lisi'@'%' IDENTIFIED BY '123';)
2.删除用户:
语法:DROP USER '用户名'@'主机名';(例如:DROP USER 'zhangsan'@'localhost';)
3.修改用户密码:
语法1:
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER ='用户名';(例如:UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER ='lisi';)
语法2:
SET PASSWORD FOR '用户名'@'主机名'= PASSWORD('新密码');(例如:SET PASSWORD FOR 'root'@'localhost'= PASSWORD('123');)
- mysql中忘记了root用户的密码,怎么办?
1.cmd-->net stop mysql 停止mysql服务(需要管理员运行该cmd)
2.使用无验证方式启动mysql服务:mysql --skip-grant-tables
3.打开新的cmd窗口,直接输入mysql命令,敲回车,就可以登录成功
4.use mysql;
5.update user set password = password('你的新密码') where user ='root';
6.关闭两个窗口
7.打开任务管理器,手动结束mysqld.exe的进程
8.启动mysql服务
9.使用新密码登录
4.查询用户:
1.切换到mysql数据库
USE mysql;
2.查询user表
SELECT * FROM USER;
- 通配符:%表示可以在任意主机使用用户登录数据库
2.权限管理:
1.查询权限:
语法:SHOW GRANTS FOR '用户名'@'主机名';(例如:SHOW GRANTS FOR 'lisi'@'%';)
2.授予权限:
语法:GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';(例如:GRANT SELECT,DELETE,UPDATE ON db3.account TO 'lisi'@'%';)
给张三用户授予所有权限,在任意数据库任意表上:GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3.撤销权限:
语法:REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';(例如:REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';)