MySQL數(shù)據(jù)庫基礎(chǔ)03

• 事務(wù)
• • 事務(wù)介紹
  • 事務(wù)的理解
  • 事務(wù)細(xì)節(jié)討論
• 事務(wù)隔離級別
• • 事務(wù)隔離級別介紹
  • 查看事務(wù)的隔離級別
  • 事務(wù)隔離級別
  • 事務(wù)ACID特性
  • 課后作業(yè)
• 表類型和存儲引擎
• • 基本介紹
  • 主要的存儲引擎/表類型特點(diǎn)
  • 細(xì)節(jié)說明
  • 三種存儲引擎案例
  • 修改存儲引擎
• 視圖
• • 看一個(gè)需求
  • 視圖基本概念
  • 視圖的基本使用
  • 視圖的細(xì)節(jié)討論
  • 視圖最佳實(shí)踐
  • • 課后練習(xí)
• mysql管理
• • mysql用戶
  • 創(chuàng)建用戶
  • 刪除用戶
  • 用戶修改密碼
  • mysql中的權(quán)限
  • 給用戶授權(quán)
  • 回收用戶授權(quán)
  • 權(quán)限生效指令
  • • 課堂練習(xí)
  • 用戶管理細(xì)節(jié)
• Mysql作業(yè)練習(xí)

事務(wù)

事務(wù)介紹

事務(wù)的理解

-- 事務(wù)的重要概念和具體操作 CREATE TABLE t27 (`id` INT, `name` VARCHAR(32) ); -- 開啟事務(wù) START TRANSACTION; -- 設(shè)置保存點(diǎn) a SAVEPOINT a; -- 執(zhí)行dml操作 INSERT INTO t27 VALUES (100,'tom');SELECT * FROM t27; -- 設(shè)置保存點(diǎn) b SAVEPOINT b;INSERT INTO t27 VALUES (200,'jack');-- 想要回滾，回到b ROLLBACK TO b;-- 想要回滾，回到a ROLLBACK TO a;SELECT * FROM t27;-- 如果這樣寫，表示直接回退到事務(wù)開始的狀態(tài),回退全部事務(wù) ROLLBACK;-- COMMIT提交事務(wù)，所有操作生效 COMMIT;

事務(wù)細(xì)節(jié)討論

-- 討論事務(wù)細(xì)節(jié) -- 如果不開啟事務(wù)，默認(rèn)情況下DML自動(dòng)提交，不會(huì)回滾INSERT INTO t27 VALUES (100,'tom');SELECT * FROM t27;-- 2.如果開始一個(gè)事務(wù)，你沒有創(chuàng)建保存點(diǎn).你可以執(zhí)行rollback,默認(rèn)就是回退到 -- 你事務(wù)開始的狀態(tài). START TRANSACTION;INSERT INTO t27 VALUES (400,'king');SELECT * FROM t27;INSERT INTO t27 VALUES (500,'scott');ROLLBACK; -- 表示回退到事務(wù)開始的狀態(tài) COMMIT;SELECT * FROM t27;-- 3.你也可以在這個(gè)事務(wù)中(還沒有提交時(shí))，創(chuàng)建多個(gè)保存點(diǎn)比如: savepoint -- aaa; 執(zhí)行dmI，savepoint bbb;-- 4.你可以在事務(wù)沒有提交前，選擇回退到哪個(gè)保存點(diǎn). ROLLBACK TO a; -- 5. mysql的事務(wù)機(jī)制需要innodb的存儲引擎才可以使用,myisam不好使.-- 6.開始一個(gè)事務(wù)的寫法:start transaction, set autocommit=off; SET autocommit = off;INSERT INTO t27 VALUES (400,'king');SELECT * FROM t27;INSERT INTO t27 VALUES (500,'scott');ROLLBACK; -- 表示回退到事務(wù)開始的狀態(tài) SELECT * FROM t27; COMMIT;

事務(wù)隔離級別

事務(wù)隔離級別介紹

查看事務(wù)的隔離級別

事務(wù)隔離級別

CREATE TABLE `accout` (`id` INT, `name` VARCHAR(32), `money` INT );-- 查看當(dāng)前會(huì)話隔離級別 SELECT @@tx_isolation;-- 查看系統(tǒng)當(dāng)前隔離級別 SELECT @@global.tx_isolation;-- 設(shè)置當(dāng)前會(huì)話隔離級別 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 設(shè)置系統(tǒng)當(dāng)前隔離級別 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- mysql默認(rèn)的事務(wù)隔離級別是repeatable read

事務(wù)ACID特性

課后作業(yè)

表類型和存儲引擎

基本介紹

主要的存儲引擎/表類型特點(diǎn)

細(xì)節(jié)說明

三種存儲引擎案例

-- 表類型和存儲引擎 SHOW ENGINES;-- innodb前面一直在使用，支持事務(wù)和外鍵，支持行級鎖 -- myisam -- 1.添加速度塊 -- 2.不支持外鍵和事務(wù) -- 3.支持表級鎖 -- 4.不支持事務(wù) 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內(nèi)存級的,關(guān)閉musql服務(wù)，表里的數(shù)據(jù)就丟失了，但是表結(jié)構(gòu)還在 -- 1.數(shù)據(jù)存儲在內(nèi)存中 -- 2.執(zhí)行速度很快(沒有IO讀寫) -- 3.默認(rèn)支持索引(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;

視圖

看一個(gè)需求

視圖基本概念

視圖的基本使用

-- 視圖 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;-- 查看視圖創(chuàng)建語句 SHOW CREATE VIEW emp_view01;-- 刪除視圖 DROP VIEW emp_view01;

視圖的細(xì)節(jié)討論

-- 視圖 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;-- 查看視圖創(chuàng)建語句 SHOW CREATE VIEW emp_view01;-- 刪除視圖 DROP VIEW emp_view01;SELECT * FROM emp;-- 視圖的細(xì)節(jié) -- 1. 創(chuàng)建視圖后，到數(shù)據(jù)庫去看，對應(yīng)視圖只有一個(gè)視圖結(jié)構(gòu)文件(形式: 視圖名.frm) -- emp_view01.frm-- 2. 視圖的數(shù)據(jù)變化會(huì)影響到基表，基表的數(shù)據(jù)變化也會(huì)影響到視圖[insert update delete ] -- 修改視圖會(huì)影響基表 UPDATE emp_view01 a SET a.`job` = 'MANAGER' WHERE a.`empno` = 7369;SELECT * FROM emp_view01;SELECT * FROM emp;-- 修改基表也會(huì)影響視圖 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;

視圖最佳實(shí)踐

課后練習(xí)

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.hisalSELECT * FROM db_02.`salgrade`; SELECT * FROM db_02.`dept`;DESC emp_view03;SELECT * FROM emp_view03;

mysql管理

mysql用戶

創(chuàng)建用戶

刪除用戶

用戶修改密碼

-- mysql用戶管理 SELECT * FROM mysql.user;-- 原因:做項(xiàng)目開發(fā)的時(shí)候，可以根據(jù)不同的開發(fā)人員， -- 賦權(quán)給他相應(yīng)的mysql操作 -- 所以，mysql數(shù)據(jù)庫管理人員(root)根據(jù)需要，創(chuàng)建不同的用戶，供開發(fā)使用-- 1.創(chuàng)建新用戶 -- -- '1234' 是密碼 CREATE USER 'hsp'@'localhost' IDENTIFIED BY '1234';SELECT PASSWORD('1234') FROM DUAL; *A4B6157319038724E3560894F7F932C8886EBFCF *A4B6157319038724E3560894F7F932C8886EBFCFSELECT `host`,`user`,`authentication_string` FROMmysql.`user` ;-- 刪除用戶 DROP USER 'hsp'@'localhost';-- 登錄 -- 默認(rèn)情況下，不同的數(shù)據(jù)庫用戶，能操作的庫和表不同-- 用戶修改密碼 -- 修改自己的密碼 SET PASSWORD = PASSWORD('123');-- 用root用戶修改他人的密碼 SET PASSWORD FOR 'hsp'@'localhost' = PASSWORD('1234');

mysql中的權(quán)限

給用戶授權(quán)

回收用戶授權(quán)

權(quán)限生效指令

課堂練習(xí)

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用戶

-- 演示用戶權(quán)限管理 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;

用戶管理細(xì)節(jié)

-- 用戶管理細(xì)節(jié) -- 1.在創(chuàng)建用戶的時(shí)候，如果不指定Host,則為% , %表示表示所有IP都有連接權(quán)限 -- create user XXX; CREATE USER ty;SELECT * FROM mysql.user;-- 可以看到Host 是% 表示所有IP都有連接權(quán)限-- 2.你也可以這樣指定 -- create user 'xxx @' 192.168.1.%'表示xxx用戶在192.168.1.*的ip可以登 -- 錄mysql CREATE USER 'ty'@'192.168.%.%';-- 3.在刪除用戶的時(shí)候，如果host不是%,需要明確指定'用戶@'host值' DROP USER ty;DROP USER 'ty'@'192.168.%.%';

Mysql作業(yè)練習(xí)

-- 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 '年收入' FROMdb_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). 列出至少有一個(gè)員工的所有部門 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 FROMdb_02.emp a,db_02.emp b WHERE a.`mgr` = b.`empno` AND a.`hiredate` > b.`hiredate` ;-- (4).列出部門名稱和這些部門的員工信息，同時(shí)列出那些沒有員工的部門。 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.根據(jù): -- 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).列出在每個(gè)部門工作的員工數(shù)量、平均工資和平均服務(wù)期限。 SELECT a.`deptno`,COUNT(DISTINCT a.`empno`),AVG(a.`sal` + IFNULL(`comm`, 0)),ROUND(AVG(DATEDIFF(NOW(),a.`hiredate`))) AS diffday FROMemp a GROUP BY a.`deptno`;-- (12).列出所有員工的姓名、部門名稱和工資。 SELECT a.ename,b.`dname`,a.`sal` + IFNULL(a.comm, 0) FROMemp a,dept b ;-- (13).列出所有部門的詳細(xì)信息和部門人數(shù)。 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 (經(jīng)理)的最低薪金。 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 ;# 學(xué)生 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,'軟件','計(jì)算機(jī)',1995,20), (102,'微電子','計(jì)算機(jī)',1996,30), (111,'無機(jī)化學(xué)','化學(xué)',1995, 29), (112,'高分子化學(xué)','化學(xué)',1996,25), (121,'統(tǒng)計(jì)數(shù)學(xué)','數(shù)學(xué)',1995, 20), (131,'現(xiàn)代語言','中文',1996,20), (141,'國際貿(mào)易','經(jīng)濟(jì)',1997,30), (142,'國際金融','經(jīng)濟(jì)',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','數(shù)學(xué)'), ('002','計(jì)算機(jī)'), ('003','化學(xué)'), ('004','中文'), ('005','經(jīng)濟(jì)');SELECT * FROM department; -- 3.1 SELECT * FROM student a WHERE a.name LIKE '李%';-- 3.2 DESC class;SELECT deptname FROM classGROUP BY deptnameHAVING COUNT(SUBJECT) > 1;-- 3.3SELECT a.`deptname`,b.`departmentid`,SUM(a.`num`)rsFROM class a,department bWHERE a.`deptname` = b.`deptname`GROUP BY a.`deptname`HAVING rs >= 30;-- 3.4 SELECT * FROM department; INSERT INTO department VALUES(006,'物理系');-- 3.5DESC STUDENT;START TRANSACTION;UPDATE class aSET a.num = a.num - 1WHERE a.id = 101;DELETE FROM STUDENTWHERE NAME = '張三'COMMIT;SELECT * FROM class;

總結(jié)

以上是生活随笔為你收集整理的MySQL数据库基础03 韩顺平 自学笔记的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。