MySQL数据库基础03
事务
事务介绍
事务的理解
-- 事务的重要概念和具体操作
CREATE TABLE t27
(`id` INT,
`name` VARCHAR(32)
);
-- 开启事务
START TRANSACTION;
-- 设置保存点 a
SAVEPOINT a;
-- 执行dml操作
INSERT INTO t27
VALUES
(100,'tom');
SELECT * FROM t27;
-- 设置保存点 b
SAVEPOINT b;
INSERT INTO t27
VALUES
(200,'jack');
-- 想要回滚,回到b
ROLLBACK TO b;
-- 想要回滚,回到a
ROLLBACK TO a;
SELECT * FROM t27;
-- 如果这样写,表示直接回退到事务开始的状态,回退全部事务
ROLLBACK;
-- COMMIT提交事务,所有操作生效
COMMIT;
事务细节讨论
-- 讨论事务细节
-- 如果不开启事务,默认情况下DML自动提交,不会回滚
INSERT INTO t27
VALUES
(100,'tom');
SELECT * FROM t27;
-- 2.如果开始一个事务,你没有创建保存点.你可以执行rollback,默认就是回退到
-- 你事务开始的状态.
START TRANSACTION;
INSERT INTO t27
VALUES
(400,'king');
SELECT * FROM t27;
INSERT INTO t27
VALUES
(500,'scott');
ROLLBACK; -- 表示回退到事务开始的状态
COMMIT;
SELECT * FROM t27;
-- 3.你也可以在这个事务中(还没有提交时),创建多个保存点比如: savepoint
-- aaa; 执行dmI,savepoint bbb;
-- 4.你可以在事务没有提交前,选择回退到哪个保存点.
ROLLBACK TO a;
-- 5. mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使.
-- 6.开始一个事务的写法:start transaction, set autocommit=off;
SET autocommit = off;
INSERT INTO t27
VALUES
(400,'king');
SELECT * FROM t27;
INSERT INTO t27
VALUES
(500,'scott');
ROLLBACK; -- 表示回退到事务开始的状态
SELECT * FROM t27;
COMMIT;
事务隔离级别
事务隔离级别介绍
查看事务的隔离级别
事务隔离级别
CREATE TABLE `accout`
(`id` INT,
`name` VARCHAR(32),
`money` INT
);
-- 查看当前会话隔离级别
SELECT @@tx_isolation;
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation;
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- mysql默认的事务隔离级别是repeatable read
事务ACID特性
课后作业
表类型和存储引擎
基本介绍
主要的存储引擎/表类型特点
细节说明
三种存储引擎案例
-- 表类型和存储引擎
SHOW ENGINES;
-- innodb前面一直在使用,支持事务和外键,支持行级锁
-- myisam
-- 1.添加速度块
-- 2.不支持外键和事务
-- 3.支持表级锁
-- 4.不支持事务
CREATE TABLE t28
(`id` INT,
`name` VARCHAR(32))ENGINE MYISAM;
START TRANSACTION;
SAVEPOINT a;
INSERT INTO t28 VALUES
(1,'jack');
SELECT * FROM t28;
ROLLBACK TO a;
-- Warning Code : 1196
-- Some non-transactional changed tables couldn't be rolled back
# 没有回滚成功
-- memory内存级的,关闭musql服务,表里的数据就丢失了,但是表结构还在
-- 1.数据存储在内存中
-- 2.执行速度很快(没有IO读写)
-- 3.默认支持索引(hash)
CREATE TABLE t29
(`id` INT,
`name` VARCHAR(32))ENGINE MEMORY;
INSERT INTO t29 VALUES
(1,'jack');
INSERT INTO t29 VALUES
(2,'tom');
INSERT INTO t29 VALUES
(3,'hsp');
SELECT * FROM t29;
修改存储引擎
-- 修改存储引擎
ALTER TABLE t29 ENGINE = INNODB;
视图
看一个需求
视图基本概念
视图的基本使用
-- 视图
CREATE VIEW emp_view01 AS
SELECT empno,ename,job,deptno FROM emp;
SELECT * FROM db_02.`emp`;
-- 查看视图
DESC emp_view01;
-- 查询视图
SELECT * FROM emp_view01;
SELECT a.`empno`,a.`ename`,a.`job`,a.`deptno` FROM emp_view01 a
-- 更新成新的视图
ALTER VIEW emp_view01 AS
SELECT empno,ename,job,deptno FROM emp;
-- 查看视图创建语句
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;
视图的细节讨论
-- 视图
CREATE VIEW emp_view01 AS
SELECT empno,ename,job,deptno FROM emp;
SELECT * FROM db_02.`emp`;
-- 查看视图
DESC emp_view01;
-- 查询视图
SELECT * FROM emp_view01;
SELECT a.`empno`,a.`ename`,a.`job`,a.`deptno` FROM emp_view01 a
-- 更新成新的视图
ALTER VIEW emp_view01 AS
SELECT empno,ename,job,deptno FROM emp;
-- 查看视图创建语句
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;
SELECT * FROM emp;
-- 视图的细节
-- 1. 创建视图后,到数据库去看,对应视图只有一个视图结构文件(形式: 视图名.frm)
-- emp_view01.frm
-- 2. 视图的数据变化会影响到基表,基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图会影响基表
UPDATE emp_view01 a
SET a.`job` = 'MANAGER'
WHERE a.`empno` = 7369;
SELECT * FROM emp_view01;
SELECT * FROM emp;
-- 修改基表也会影响视图
UPDATE db_02.emp a
SET a.`job` = 'CLERK'
WHERE a.`empno` = 7369;
-- 视图中可以再使用视图
DESC emp_view01;
-- 从emp_view01中选择出2列,做新视图
CREATE VIEW emp_view02
AS
SELECT empno,ename
FROM emp_view01;
SELECT * FROM emp_view02;
视图最佳实践
课后练习
CREATE VIEW emp_view03
AS
SELECT a.empno empno,
a.ename ename,
b.dname dname,
c.grade grade
FROM db_02.`emp` a,
db_02.`dept` b,
db_02.`salgrade` c
WHERE a.`deptno` = b.`deptno`
AND a.sal BETWEEN c.losal AND c.hisal
SELECT * FROM db_02.`salgrade`;
SELECT * FROM db_02.`dept`;
DESC emp_view03;
SELECT * FROM emp_view03;
mysql管理
mysql用户
创建用户
删除用户
用户修改密码
-- mysql用户管理
SELECT * FROM mysql.user;
-- 原因:做项目开发的时候,可以根据不同的开发人员,
-- 赋权给他相应的mysql操作
-- 所以,mysql数据库管理人员(root)根据需要,创建不同的用户,供开发使用
-- 1.创建新用户
--
-- '1234' 是密码
CREATE USER 'hsp'@'localhost' IDENTIFIED BY '1234';
SELECT PASSWORD('1234') FROM DUAL;
*A4B6157319038724E3560894F7F932C8886EBFCF
*A4B6157319038724E3560894F7F932C8886EBFCF
SELECT
`host`,
`user`,
`authentication_string`
FROM
mysql.`user` ;
-- 删除用户
DROP USER 'hsp'@'localhost';
-- 登录
-- 默认情况下,不同的数据库用户,能操作的库和表不同
-- 用户修改密码
-- 修改自己的密码
SET PASSWORD = PASSWORD('123');
-- 用root用户修改他人的密码
SET PASSWORD FOR 'hsp'@'localhost' = PASSWORD('1234');
mysql中的权限
给用户授权
回收用户授权
权限生效指令
课堂练习
shunping用户
SELECT * FROM testdb.news;
INSERT INTO testdb.news VALUES
(2,'Mary');
UPDATE testdb.news a
SET a.`name` = `Rose`
WHERE a.`id` = 1;
-- UPDATE command denied to user 'shunping'@'localhost' for table 'news'
root用户
-- 演示用户权限管理
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123';
CREATE DATABASE testdb;
CREATE TABLE testdb.news
(`id` INT PRIMARY KEY,
`name` VARCHAR(32));
INSERT INTO testdb.news VALUES
(1,'Jack');
GRANT SELECT,INSERT ON testdb.news TO 'shunping'@'localhost';
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');
REVOKE SELECT ON testdb.news FROM 'shunping'@'localhost';
SELECT * FROM news;
-- 删除用户
DROP USER 'shunping'@'localhost';
SELECT * FROM mysql.user;
用户管理细节
-- 用户管理细节
-- 1.在创建用户的时候,如果不指定Host,则为% , %表示表示所有IP都有连接权限
-- create user XXX;
CREATE USER ty;
SELECT * FROM mysql.user;
-- 可以看到Host 是% 表示所有IP都有连接权限
-- 2.你也可以这样指定
-- create user 'xxx @' 192.168.1.%'表示xxx用户在192.168.1.*的ip可以登
-- 录mysql
CREATE USER 'ty'@'192.168.%.%';
-- 3.在删除用户的时候,如果host不是%,需要明确指定'用户@'host值'
DROP USER ty;
DROP USER 'ty'@'192.168.%.%';
Mysql作业练习
-- 2.
DESC db_02.dept;
DESC db_02.emp;
SELECT * FROM db_02.emp;
-- 3.(1)
SELECT dname FROM db_02.dept;
-- 3.(2)
SELECT
a.ename,
(sal + IFNULL(comm, 0)) * 13 AS '年收入'
FROM
db_02.emp a ;
-- 4.(1)
SELECT ename,sal FROM emp
WHERE sal > 2850;
-- 4.(2)
SELECT ename,sal FROM emp
WHERE sal NOT BETWEEN 1500 AND 2850;
-- 4.(3)
SELECT ename,deptno
FROM emp
WHERE empno = 7566;
-- 4.(4)
SELECT ename,sal
FROM emp
WHERE sal > 1500
AND deptno IN (10,30);
-- 4.(5)
SELECT ename,job
FROM emp
WHERE mgr IS NULL;
-- 5.(1)
SELECT ename,job,hiredate
FROM emp
WHERE hiredate BETWEEN '1991-02-01'
AND '19910501'
ORDER BY hiredate;
-- 5.(2)
SELECT ename,sal,comm
FROM emp
WHERE comm IS NOT NULL
ORDER BY sal DESC;
# 6.
-- 1.
SELECT * FROM emp
WHERE deptno = 30;
-- 2.
SELECT ename,empno,deptno
FROM emp
WHERE job = 'CLERK';
-- 3.
SELECT * FROM emp
WHERE IFNULL(comm,0) > sal;
-- 4.
SELECT * FROM emp
WHERE IFNULL(comm,0) > (sal * 0.6);
-- 5.
SELECT * FROM emp
WHERE deptno = 10
AND job = 'MANAGER'
UNION ALL
SELECT * FROM emp
WHERE deptno = 20
AND job = 'CLERK';
-- 6.
SELECT * FROM emp
WHERE deptno = 10
AND job = 'MANAGER'
UNION ALL
SELECT * FROM emp
WHERE deptno = 20
AND job = 'CLERK'
UNION ALL
SELECT * FROM emp
WHERE job NOT IN ('MANAGER','CLERK')
AND sal >= 2000;
-- 7.
SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;
-- 8.
SELECT * FROM emp
WHERE comm IS NULL
OR IFNULL(comm,0) < 100;
-- 9.
SELECT * FROM emp
WHERE hiredate = DATE_SUB(LAST_DAY(DATE(hiredate)), INTERVAL 2 DAY);
-- 10.
SELECT * FROM emp
WHERE hiredate < DATE_SUB(NOW(), INTERVAL 12 YEAR);
-- 11.
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) FROM emp
-- 12.
SELECT ename
FROM emp
WHERE LENGTH(ename) = 5;
-- 13.
SELECT ename
FROM emp
WHERE ename NOT LIKE '%R%';
-- 14.
SELECT SUBSTRING(ename,1,3) FROM emp;
-- 15.
SELECT REPLACE(ename,'A','a') FROM emp;
-- 16.
SELECT ename,hiredate FROM emp
WHERE DATE_SUB(NOW(),INTERVAL 10 YEAR) >= hiredate;
-- 17.
SELECT * FROM emp
ORDER BY ename;
-- 18.
SELECT ename,hiredate
FROM emp
ORDER BY hiredate;
-- 19.
SELECT ename,job,sal
FROM emp
ORDER BY job,sal DESC;
-- 20.
SELECT ename,YEAR(hiredate) year_hiredate,MONTH(hiredate) month_hiredate
FROM emp
ORDER BY month_hiredate,year_hiredate;
-- 21.
SELECT FORMAT((sal/30),0) FROM emp
ORDER BY empno;
SELECT FLOOR((sal/30)) FROM emp
ORDER BY empno;
-- 22.
SELECT * FROM emp
WHERE MONTH(hiredate) = 2;
-- 23.
SELECT ename,DATEDIFF(NOW(),hiredate) DAY
FROM emp;
-- 24.
SELECT ename FROM emp
WHERE ename LIKE '%A%';
-- 25.
SELECT ename,
FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '年',
FLOOR(MOD(DATEDIFF(NOW(),hiredate),365)/31) AS '月',
MOD(MOD(DATEDIFF(NOW(),hiredate),365),31) AS '日'
FROM emp
-- (1). 列出至少有一个员工的所有部门
SELECT DISTINCT a.dname,a.deptno FROM dept a,emp b
WHERE a.deptno = b.deptno;
-- (2).列出薪金比"SMITH"多的所有员工。
SELECT * FROM emp
WHERE sal >(SELECT sal FROM emp
WHERE ename = 'SMITH');
-- (3). 列出受雇日期晚于其直接上级的所有员工。
SELECT
a.empno,
a.`mgr`,
a.`hiredate`,
b.`hiredate` boss
FROM
db_02.emp a,
db_02.emp b
WHERE a.`mgr` = b.`empno`
AND a.`hiredate` > b.`hiredate` ;
-- (4).列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
SELECT b.`dname`,a.*
FROM db_02.`emp` a RIGHT JOIN db_02.`dept` b
ON a.`deptno` = b.`deptno`
ORDER BY b.dname;
-- (5).列出所有"CLERK"(办事员)的姓名及其部门名称。
SELECT a.`ename`,b.`dname` FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
AND a.`job` = 'CLERK';
-- (6). 列出最低薪金大于1500的各种工作。
SELECT DISTINCT a.`job`,MIN(a.sal) AS minsal FROM emp a
GROUP BY a.job
HAVING minsal > 1500;
-- (7).列出在部门"SALES" (销售部)工作的员工的姓名。
SELECT a.`ename` FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
AND b.`dname` = 'SALES'
-- (8).列出薪金高于公司平均薪金的所有员工。
SELECT * FROM emp b
WHERE b.sal >(
SELECT AVG(a.`sal`) FROM emp a);
-- 7.根据:
-- emp员工表,dept部门表,工资=薪金+佣金写出正确SQL homework04.sql
-- (9).列出与"SCOTT"从事相同工作的所有员工。
SELECT a.* FROM emp a
WHERE a.`job` = (SELECT b.job FROM emp b
WHERE b.`ename` = 'SCOTT')
AND a.`ename` <> 'SCOTT';
-- (10).列出薪金高于所在部门30工作的所有员工的薪金的员工姓名和薪金。
SELECT a.`ename`,a.`sal` FROM emp a
WHERE a.`sal` > (SELECT MAX(b.sal) FROM emp b
WHERE b.`deptno` = 30);
-- (11).列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT
a.`deptno`,
COUNT(DISTINCT a.`empno`),
AVG(a.`sal` + IFNULL(`comm`, 0)),
ROUND(AVG(DATEDIFF(NOW(),a.`hiredate`))) AS diffday
FROM
emp a
GROUP BY a.`deptno`;
-- (12).列出所有员工的姓名、部门名称和工资。
SELECT
a.ename,
b.`dname`,
a.`sal` + IFNULL(a.comm, 0)
FROM
emp a,
dept b ;
-- (13).列出所有部门的详细信息和部门人数。
SELECT a.*,COUNT(b.*) FROM dept a LEFT JOIN emp b
ON a.`deptno` = b.`deptno`
GROUP BY a.`deptno`;
SELECT a.`deptno`,a.`loc`,COUNT(b.deptno) FROM emp b,dept a
WHERE a.`deptno` = b.`deptno`
GROUP BY b.`deptno`,a.loc;
SELECT a.deptno,a.`dname`,a.`loc`,COUNT(b.deptno) rs FROM dept a LEFT JOIN emp b
ON a.`deptno` = b.`deptno`
GROUP BY a.`deptno`,a.`dname`,a.`loc`;
-- (14).列出各种工作的最低工资。
SELECT job,MIN(sal) FROM emp
WHERE job IN (
SELECT DISTINCT job FROM emp)
GROUP BY job;
-- (15).列出MANAGER (经理)的最低薪金。
SELECT MIN(sal) FROM emp
WHERE job = 'MANAGER';
-- (16).列出所有员工的年工资按年薪从低到高排序。
SELECT (sal + IFNULL(comm,0))*12 AS salary FROM emp
ORDER BY salary;
CREATE DATABASE school;
USE school;
# 系
CREATE TABLE department
(`departmentid` INT NOT NULL DEFAULT 0 PRIMARY KEY,
`deptname` VARCHAR(20) UNIQUE NOT NULL DEFAULT ''
);
SELECT * FROM department;
ALTER TABLE department MODIFY `departmentid` VARCHAR(255) NOT NULL DEFAULT '0';
# 班级
CREATE TABLE class
(`classid` INT NOT NULL DEFAULT 0 PRIMARY KEY,
`subject` VARCHAR(20) NOT NULL DEFAULT '',
`deptname` VARCHAR(20) UNIQUE NOT NULL DEFAULT '', -- 外键
enrolltime CHAR(4) NOT NULL DEFAULT 9999,
num INT NOT NULL DEFAULT 0);
ALTER TABLE class MODIFY enrolltime INT NOT NULL DEFAULT 9999;
ALTER TABLE class MODIFY `deptname` VARCHAR(20) NOT NULL DEFAULT '';
ALTER TABLE class ADD FOREIGN KEY (`deptname`) REFERENCES department (`deptname`);
DESC class;
SHOW INDEX FROM class;
ALTER TABLE class DROP INDEX deptname ;
# 学生
CREATE TABLE student
(`studentid` INT NOT NULL DEFAULT 0 PRIMARY KEY,
`name` VARCHAR(20) NOT NULL DEFAULT '',
`age` INT NOT NULL DEFAULT 0,
`classid` INT NOT NULL DEFAULT 0
);
ALTER TABLE student ADD FOREIGN KEY(classid) REFERENCES class(`classid`);
INSERT INTO `class`
(`classid`,`subject`,`deptname`,enrolltime, num)
VALUES
(101,'软件','计算机',1995,20),
(102,'微电子','计算机',1996,30),
(111,'无机化学','化学',1995, 29),
(112,'高分子化学','化学',1996,25),
(121,'统计数学','数学',1995, 20),
(131,'现代语言','中文',1996,20),
(141,'国际贸易','经济',1997,30),
(142,'国际金融','经济',1996,14);
SELECT * FROM class;
INSERT INTO STUDENT VALUES
(8101,'张三',18,101),
(8102,'钱四',16,121),
(8103,'王玲',17,131),
(8105,'李飞',19,102),
(8109,'赵四',18,141),
(8110,'李可',20,142),
(8201,'张飞',18,111),
(8302,'周瑜',16,112),
(8203,'王亮',17, 111),
(8305,'董庆',19,102),
(8409,'赵龙',18,101),
(8510,'李丽',20,142 );
INSERT INTO department
VALUES
('001','数学'),
('002','计算机'),
('003','化学'),
('004','中文'),
('005','经济');
SELECT * FROM department;
-- 3.1
SELECT * FROM student a
WHERE a.name LIKE '李%';
-- 3.2
DESC class;
SELECT deptname FROM class
GROUP BY deptname
HAVING COUNT(SUBJECT) > 1;
-- 3.3
SELECT a.`deptname`,b.`departmentid`,SUM(a.`num`)rs
FROM class a,department b
WHERE a.`deptname` = b.`deptname`
GROUP BY a.`deptname`
HAVING rs >= 30;
-- 3.4
SELECT * FROM department;
INSERT INTO department VALUES
(006,'物理系');
-- 3.5
DESC STUDENT;
START TRANSACTION;
UPDATE class a
SET a.num = a.num - 1
WHERE a.id = 101;
DELETE FROM STUDENT
WHERE NAME = '张三'
COMMIT;
SELECT * FROM class;