数据库原理与设计（MySQL版）
各章SQL语句代码
第2章 关系数据库标准语言SQL
【例2-1】 创建company数据库。
CREATE DATABASE company;
【例2-2】 选择company数据库。
USE company;
【例2-3】 删除company数据库。
DROP DATABASE company;
【例2-4】示例。
CREATE TABLE product
( p_code DECIMAL(6),
p_name VARCHAR(30),
p_price DECIMAL(5,2)
);
【例2-5】示例。
CREATE TABLE ord
( id INT AUTO_INCREMENT PRIMARY KEY,
ordno DECIMAL(8),
p_code DECIMAL(6),
s_code DECIMAL(6),
ordate DATETIME DEFAULT CURRENT_TIMESTAMP,
price DECIMAL(8,2)
);
【例2-6】根据dept表，生成新表dept_c。
CREATE TABLE dept_c
SELECT * FROM dept;
SELECT * FROM dept_c;
【例2-7】为dept_c表增加一个新列telephone。
ALTER TABLE dept_c
ADD telephon VARCHAR(11);
DESC dept_c;
【例2-8】对dept_c表中的telephone列进行修改，数据类型不变，长度改为13，默认值为0431-86571302。
ALTER TABLE dept_c
MODIFY telephon VARCHAR(13) DEFAULT '0431-86571302';
DESC dept_c;
【例2-9】删除dept_c表中的telephon列。
ALTER TABLE dept_c
DROP telephon;
DESC dept_c;
【例2-10】示例。
CREATE TABLE dept_bk
SELECT * FROM dept;
TRUNCATE TABLE dept_bk;
SELECT * FROM dept_bk;
DROP TABLE dept_bk;
【例2-11】查询表的全部数据。
SELECT * FROM dept;
DESC dept;
SELECT * FROM emp;
DESC emp;
SELECT * FROM salgrade;
DESC salgrade;
【例2-12】查询dept表的部门编号deptno和部门名称dname信息。
SELECT deptno,dname FROM dept;
【例2-13】查询雇员表emp中各部门的职务信息。
SELECT deptno,job FROM emp;
SELECT DISTINCT deptno,job FROM emp;
【例2-14】在emp查询每个员工的empno、ename、hiredate，输出的列名为雇员编号、雇员姓名、雇佣日期。
SELECT empno AS 雇员编号,ename 雇员姓名,hiredate 雇佣日期 FROM emp;
【例2-15】查询SMITH的雇佣日期。
SELECT ename,hiredate FROM emp WHERE ename='SMITH';
【例2-16】查询emp表中在部门10工作的、工资高于1000或岗位是CLERK的所有雇员的姓名、工资、岗位的信息。
SELECT ename,job,sal FROM emp
WHERE deptno='10' AND (sal>1000 OR job='CLERK');
【例2-17】查询emp表工资在2500~3000之间，1981年聘用的所有雇员的姓名、工资、聘用日期信息。
SELECT ename,sal,hiredate FROM emp
WHERE sal BETWEEN 2500 AND 3000
AND hiredate BETWEEN '1981-01-01' AND '1981-12-31';
【例2-18】查询emp表中所以姓名以K开头或姓名第2个字母为C的员式的姓名、部门号及工资的信息。
SELECT ename,deptno,sal FROM emp
WHERE ename LIKE 'K%' OR ename LIKE '_C%';
【例2-19】查询emp表中1981年聘用没有补助的员工的姓名和职位信息。
SELECT ename,job FROM emp
WHERE hiredate>='1981-01-01' AND hiredate<='1981-12-31'
AND comm IS NULL;
【例2-20】查询emp表中部门20和30中的，岗位是CLERK的所有雇员的部门号、姓名、工资的信息。
SELECT deptno,ename,sal FROM emp
WHERE deptno IN (20,30) AND job='CLERK';
【例2-21】以部门号的降序、姓名升序，查询emp表中工资在2000~3000员工的部门号、姓名、工资、补助信息。
SELECT deptno,ename,sal,comm FROM emp
WHERE sal BETWEEN 2000 AND 3000
ORDER BY deptno DESC,ename;
【例2-22】使用列的别名、列的位置进行排序，改写2-21的例子。
SELECT deptno AS 部门编号,ename,sal,comm FROM emp
WHERE sal BETWEEN 2000 AND 3000
ORDER BY 部门编号 DESC,2;
【例2-23】统计deptno为30的部门的平均工资、总补助款，总人数，补助人数，最高工资和最低工资。
SELECT AVG(sal) AS 平均工资,SUM(comm) 总补助款,
COUNT(*) AS 总人数,COUNT(comm) 补助人数,
MAX(sal) AS 最高工资,MIN(sal) 最低工资
FROM emp WHERE deptno=30;
【例2-24】查询emp表中每个部门的平均工资和最高工资，并按部门编号升序排序。
SELECT deptno,AVG(sal) 平均工资,MAX(sal) 最高工资 FROM emp
GROUP BY deptno
ORDER BY deptno;
【例2-25】查询emp表中每个部门、每种岗位的平均工资和最高工资。
SELECT deptno,job,AVG(sal) 平均工资,MAX(sal) 最高工资 FROM emp
GROUP BY deptno,job
ORDER BY deptno;
【例2-26】查询部门编号在30以下的各个部门的部门编号、平均工资，要求只显示平均工资大于等于2000的信息。
SELECT deptno,AVG(sal) 平均工资 FROM emp
WHERE deptno<30
GROUP BY deptno
HAVING AVG(sal)>=2000;
或
SELECT deptno,AVG(sal) 平均工资 FROM emp
WHERE deptno<30
GROUP BY deptno
HAVING 平均工资>=2000;
【例2-27】查询工资大于等于3000的员工的员工编号、姓名、工资、所在部门编号及部门所在地址，结果按部门编号进行排序。
SELECT empno,ename,sal,e.deptno,loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND sal>=3000
ORDER BY e.deptno;
或
SELECT empno,ename,sal,e.deptno,loc
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno
WHERE sal>=3000
ORDER BY e.deptno;
【例2-28】查询emp表中在部门20工作的雇员的姓名及其管理员的姓名。
SELECT e.ename 雇员,m.ename 管理员
FROM emp e,emp m
WHERE m.empno=e.mgr
AND e.deptno=20;
【例2-29】salgrade表中存放着工资等级的信息，查询在部门编号为20工作的雇员的工资及工资等级的信息。
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND e.deptno=20;
【例2-30】示例。
SELECT loc,dept.deptno,emp.deptno,ename,empno
FROM dept LEFT OUTER JOIN emp
ON dept.deptno=emp.deptno
WHERE dept.deptno=10 OR dept.deptno=40;
【例2-31】示例。
SELECT empno,ename,emp.deptno,dept.deptno,loc
FROM emp RIGHT OUTER JOIN dept
ON emp.deptno=dept.deptno
WHERE dept.deptno=10 OR dept.deptno=40;
【例2-32】查询与SCOTT工作岗位相同的员工的员工编号、姓名、工资、岗位信息。
SELECT empno,ename,sal,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT');
【例2-33】查询工资大于平均工资而且与SCOTT工作岗位相同员工的信息。
SELECT empno,ename,sal,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT')
AND sal>(SELECT AVG(sal) FROM emp);
【例2-34】查询工资为所任岗位最高的员工的职工编号、姓名、岗位和工资的信息，不包含岗位为CLERK和PRESIDENT的员工。
SELECT empno,ename,job,sal FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY job)
AND job<>'CLERK' AND job<>'PRESIDENT';
【例2-35】查询高于部门20的所有雇员工资的雇员信息。
SELECT ename,sal,job FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=20);
或
SELECT ename,sal,job FROM emp
WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=20);
【例2-36】查询高于部门10的任意雇员工资的雇员信息。
SELECT ename,sal,job FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10);
或
SELECT ename,sal,job FROM emp
WHERE sal>(SELECT MIN(sal) FROM emp WHERE deptno=10);
【例2-37】查询工作在NEW YORK的雇员姓名、部门编号、工资、岗位的信息。
SELECT ename,deptno,sal,job FROM emp
WHERE EXISTS
( SELECT * FROM dept
WHERE dept.deptno=emp.deptno AND loc='NEW YORK');
或
SELECT ename,deptno,sal,job FROM emp
WHERE deptno IN
( SELECT deptno FROM dept
WHERE loc='NEW YORK');
【例2-38】示例。
SELECT empno,ename,deptno,job FROM emp WHERE job='MANAGER'
UNION
SELECT empno,ename,deptno,job FROM emp WHERE deptno=10;
SELECT empno,ename,deptno,job FROM emp WHERE job='MANAGER'
UNION ALL
SELECT empno,ename,deptno,job FROM emp WHERE deptno=10;
【例2-39】对合并后的查询结果排序。
SELECT empno,ename,deptno,job FROM emp WHERE job='MANAGER'
UNION
SELECT empno,ename,deptno,job FROM emp WHERE deptno=10
ORDER BY deptno;
或
SELECT empno,ename,deptno AS 部门编号,job FROM emp WHERE job='MANAGER'
UNION
SELECT empno,ename,deptno,job FROM emp WHERE deptno=10
ORDER BY 部门编号;
【例2-40】示例。
INSERT INTO dept_c(deptno,dname,loc)
VALUES(50,'PERSONNEL','HONGKONG');
或
INSERT INTO dept_c
VALUES(50,'PERSONNEL','HONGKONG');
【例2-41】拟新建一个部门，编号为80，地址为“SHANGHAI”，但并没有确定该部门的名字，完成此条记录的插入。
INSERT INTO dept_c(deptno,loc)
VALUES(80,'SHANGHAI');
或
INSERT INTO dept_c
VALUES(80,NULL,'SHANGHAI');
【例2-42】拟新建两个部门，一个部门deptno为60，dname为“SALES”，loc为“BEIJING”，另一个部门deptno为70，dname
为“RESEARCH”，loc为“XIAN”。
INSERT INTO dept_c
VALUES(60,'SALES','BEIJING'),(70,'RESEARCH','XIAN');
【例2-42】先将dept_c表中的记录全部删除，再使用INSERT命令将dept表中的记录插入到dept_c表中。
TRUNCATE TABLE dept_c;
INSERT INTO dept_c
SELECT * FROM dept
WHERE deptno=10 OR deptno=20 OR deptno=40;
SELECT * FROM dept_c;
【例2-43】更新dept_c表中部门10的地址为CHINA。
SET SQL_SAFE_UPDATES=0;
UPDATE dept_c
SET loc='CHINA'
WHERE deptno=10;
SELECT * FROM dept_c;
【例2-44】将dept_c表中所有部门的地址改为CHICAGO。
UPDATE dept_c
SET loc='CHICAGO';
SELECT * FROM dept_c;
【例2-45】根据dept表更新dept_c表中部门40的部门名称。
UPDATE dept_c
SET dname=(SELECT dname FROM dept WHERE deptno=40)
WHERE deptno=40;
SELECT * FROM dept_c;
【例2-46】删除 dept_c表中部门10的记录。
DELETE FROM dept_c
WHERE deptno=10;
SELECT * FROM dept_c;
【例2-47】删除dept_c表中所有记录。
DELETE FROM dept_c;
或
TRUNCATE TABLE dept_c;
【例2-48】根据emp表创建其副本emp_c，删除emp_c表中工作在RESEARCH部门的员工的数据行。
CREATE TABLE emp_c
SELECT * FROM emp;
DELETE FROM emp_c
WHERE deptno=(SELECT deptno
FROM dept WHERE dname='RESEARCH');
【例2-49】为emp_c表按员工的名字（ename）建立索引，索引名为emp_ename_idx。
CREATE INDEX emp_ename_idx
ON emp_c(ename);
【例2-50】为emp_c表按工作和工资建立索引，索引名为emp_job_sal_idx。
CREATE INDEX emp_job_sal_idx
ON emp_c(job,sal);
【例2-51】查看表emp_c的索引信息。
SHOW INDEX FROM emp_c;
【例2-52】删除emp_c表中已建立的索引emp_job_sal_idx。
DROP INDEX emp_job_sal_idx ON emp_c;
【例2-53】创建带有WITH CHECK OPTION选项的视图。
CREATE VIEW v_dept_chk
AS
SELECT empno,ename,job,deptno FROM emp
WHERE deptno=10
WITH CHECK OPTION;
INSERT INTO v_dept_chk(empno,ename,deptno)
VALUES(1000,'Mary',20);
INSERT INTO v_dept_chk(empno,ename,deptno)
VALUES(1000,'Rose',10);
【例2-54】修改例2-53建立的视图v_dept_chk，取消约束条件检查。
CREATE OR REPLACE VIEW v_dept_chk
AS
SELECT empno,ename,job,deptno FROM emp
WHERE deptno=10;
【例2-55】修改例2-53建立的视图v_dept_chk，取消约束条件检查。
ALTER VIEW v_dept_chk
AS
SELECT empno,ename,job,deptno FROM emp
WHERE deptno=10;
【例2-56】删除已创建的视图v_dept_chk。
DROP VIEW v_dept_chk;
第3章 数据库编程
【例3-1】查询表emp中ename值为SCOTT的雇员信息。
SELECT * FROM emp
WHERE ename='SCOTT';
SELECT * FROM emp
WHERE 'ename'='SCOTT';
【例3-2】将表emp中，SCOTT雇员的comm值改为1250（要求用科学记数法表示）。
UPDATE emp SET COMM=1.25E+3 WHERE ename='SCOTT';
SELECT * FROM emp WHERE ename='SCOTT';
【例3-3】查询表emp中1981年以后雇用员工的ename和hiredate信息。
SELECT ename,hiredate FROM emp
WHERE hiredate>'1981/12/31';
【例3-4】查询表emp中所有雇员的姓名ename和工资sal是否大于等于2000的判断结果。
SELECT ename,sal>2000 FROM emp;
【例3-5】将表emp雇员SCOTT的comm列值改为NULL值，然后再在NULL值的基础上加1250元，请考虑最终comm列值是什么?
UPDATE emp SET comm=NULL WHERE ename='SCOTT';
UPDATE emp SET comm=comm+1250 WHERE ename='SCOTT';
SELECT * FROM emp WHERE ename='SCOTT';
【例3-6】查询表emp中雇员’SMITH’的工资sal的值给变量salary，并显示其值。
SET @salary=(SELECT sal FROM emp WHERE ename='SMITH');
SELECT @salary;
【例3-7】查询表emp中雇员’SMITH’的job和hiredate值赋给变量job_v、hiredate_v，并显示两个变量的值。
SELECT job,hiredate INTO @job_v,@hiredate_v
FROM emp WHERE ename='SMITH';
SELECT @job_v,@hiredate_v;
【例3-8】根据name变量所给的值查询指定员工的信息。
SET @name='SCOTT';
SELECT * FROM emp WHERE ename=@name;
【例3-9】查看MySQL的版本信息。
SELECT @@version;
【例3-10】示例。
SELECT CHAR_LENGTH('CHINA'),LENGTH('CHINA');
SELECT CHAR_LENGTH('中国') 字符数,LENGTH('中国') 字符串长度;
【例3-11】示例。
SELECT CONCAT('MySQL版本：',@@version) 版本信息;
【例3-12】示例。
SET @name='sCOtt';
SELECT * FROM emp WHERE UPPER(ename)=UPPER(@name);
【例3-13】示例。
SET @name=' SCOtt ';
SELECT * FROM emp WHERE UPPER(ename)=TRIM(UPPER(@name));
【例3-14】返回emp中ename值以’S’开头的雇员信息。
SELECT * FROM emp WHERE SUBSTRING(ename,1,1)='S';
或
SELECT * FROM emp WHERE ename LIKE 'S%';
【例3-15】示例。
SELECT SQRT(ROUND(ABS(-4.01*4.01),0)),MOD(-10,3),MOD(10,-3)
【例3-16】示例。
SELECT CURDATE(),YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE());
【例3-17】查询表emp员工SMITH的工作年限。
SELECT ename 姓名,YEAR(CURDATE())-YEAR(hiredate) 工作年限 FROM EMP
WHERE ename='SMITH';
【例3-18】示例。
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
【例3-19】上面例3-17也可用如下命令方式实现。
SELECT ename 姓名,YEAR(SYSDATE())-YEAR(hiredate) 工作年限 FROM EMP
WHERE ename='SMITH';
【例3-20】示例。
SELECT CONCAT('MySQL版本号：',VERSION(),';用户：',USER()) AS 登录信息;
【例3-21】查询表emp前5条记录，显示ename和comm字段的值，当comm字段值为NULL时，显示值为0，否则显示当前字段的值。
SELECT ename,IF(comm IS NULL,0,comm) 奖金
FROM emp LIMIT 5; ##LIMIT 5为显示前5条记录
【例3-22】查询’SMITH’所在部门名称。
SELECT ename 姓名,
CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
END 部门名称
FROM emp
WHERE ename='SMITH';
或
SELECT ename 姓名,dname 部门名称 FROM dept,emp
WHERE dept.deptno=emp.deptno AND ename='SMITH';
【例3-23】示例。
SELECT ename,sal INTO @name,@salary FROM EMP
WHERE ename='SMITH';
SELECT CONCAT(@name,'的工资是',CAST(@salary AS CHAR(7))) 信息;
【例3-24】示例。
##取两个数的最大值
SET @x=5,@y=6; ##定义两个变量并赋值
SELECT IF(@x>@y,@x,@y) 最大值;
【例3-25】示例。
SET SQL_SAFE_UPDATES=0;
UPDATE dept_c SET deptno=50 WHERE deptno=10;
【例3-26】示例。
DELIMITER @@
SELECT * FROM emp@@
DELIMITER ;
SELECT * FROM emp;
【例3-27】创建存储函数name_fn，根据所给的部门编号deptno值，函数返回该部门的部门名称dname。
DELIMITER @@
CREATE FUNCTION name_fn(dno DECIMAL(2))
RETURNS VARCHAR(14)
BEGIN
RETURN(SELECT dname FROM dept
WHERE deptno=dno);
END@@
DELIMITER ;
SELECT name_fn(20);
【例3-28】删除例3-27创建的name_fn存储函数。
DROP FUNCTION name_fn;
【3-29】创建求任意两个数和的存储函数sum_fn()。
DELIMITER @@
CREATE FUNCTION sum_fn(a DECIMAL(5,2),b DECIMAL(5,2))
RETURNS DECIMAL
BEGIN
DECLARE x,y DECIMAL(5,2); ##声明两个整型变量,注意变量名前没有@
SET x=a,y=b; ##给两个整型变量赋值,注意变量名前没有@
RETURN x+y;
END@@
DELIMITER ;
SELECT sum_fn(7,3);
【例3-30】 创建函数max_int，判断整型变量a和b 的大小。
DELIMITER @@
CREATE FUNCTION max_fn(a int,b int)
RETURNS INT
BEGIN
IF a>b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END@@
DELIMITER ;
SELECT CONCAT('最大值:',CONVERT(max_fn(7,8),CHAR(3))) ;
【例3-31】 创建判断某一年是否为闰年的函数leap_year()。
闰年的判断条件为：年值能被4整除但不能被100整除，或者能被400整除。
DELIMITER @@
CREATE FUNCTION leap_year(year_date INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE leap BOOLEAN;
IF MOD(year_date,4)<>0 THEN
SET leap=FALSE;
ELSEIF MOD(year_date,100)<>0 THEN
SET leap=true;
ELSEIF MOD(year_date,400)<>0 THEN
SET leap=FALSE;
ELSE
SET leap=TRUE;
END IF;
IF leap THEN
RETURN (CONCAT(CONVERT(year_date,CHAR(4)),'年是闰年'));
ELSE
RETURN(CONCAT(CONVERT(year_date,CHAR(4)),'年是平年'));
END IF;
END@@
DELIMITER ;
SELECT leap_year(2012);
【例3-32】判断显示emp表中前3条记录的姓名和职务。
SELECT ename 姓名,CASE job
WHEN 'SALESMAN' THEN '销售员'
WHEN 'CLERK' THEN '管理员'
ELSE '经理'
END AS 职务
FROM EMP LIMIT 3;
【例3-33】 判断显示emp表前3条记录的姓名ename、基本工资sal和工资等级。
SELECT ename,sal,CASE
WHEN sal BETWEEN 700 AND 1200 THEN '一级'
WHEN sal BETWEEN 1201 AND 1400 THEN '二级'
WHEN sal BETWEEN 1401 AND 2000 THEN '三级'
WHEN sal BETWEEN 2001 AND 3000 THEN '四级'
ELSE '五级'
END 工资等级
FROM emp LIMIT 3;
【例3-34】LOOP循环语句示例。创建sum_fn()存储函数，返回1~n的和。
DELIMITER @@
CREATE FUNCTION sum_fn(n int)
RETURNS INT
BEGIN
DECLARE s,i INT;
SET s=0,i=1;
loop_label: LOOP ##指明LOOP循环标签loop_label
SET s=s+i;
SET i=i+1;
IF i>n THEN
LEAVE loop_label; ##通过标签结束LOOP循环
END IF;
END LOOP;
RETURN s;
END@@
DELIMITER ;
SELECT sum_fn(5);
【例3-35】WHILE循环语句示例。创建sum_fn()存储函数，返回1~n的和。
DELIMITER @@
CREATE FUNCTION sum_fn(n int)
RETURNS INT
BEGIN
DECLARE s,i INT;
SET s=0,i=1;
WHILE i<=n DO
SET s=s+i;
SET i=i+1;
END WHILE;
RETURN s;
END@@
DELIMITER ;
SELECT sum_fn(5);
【例3-36】REPEAT循环语句示例。创建sum_fn()存储函数，返回1~n的和。
DELIMITER @@
CREATE FUNCTION sum_fn(n int)
RETURNS INT
BEGIN
DECLARE s,i INT;
SET s=0,i=1;
REPEAT
SET s=s+i;
SET i=i+1;
UNTIL i>n
END REPEAT;
RETURN s;
END@@
DELIMITER ;
SELECT sum_fn(5);
【例3-37】创建存储过程emp_p，在emp表中查询职工编号为7369员工的姓名和工作。
DELIMITER @@
CREATE PROCEDURE emp_p()
BEGIN
SELECT ename,job FROM emp
WHERE empno=7369;
END@@
【例3-38】调用执行例3-37所创建的存储过程。
DELIMITER ;
CALL emp_p();
【例3-39】创建一个向DEPT表中插入新记录的存储过程dept_p1。
DELIMITER @@
CREATE PROCEDURE dept_p1(
IN p_deptno DECIMAL(2,0),
IN p_dname VARCHAR(14),
IN p_loc VARCHAR(13)
)
BEGIN
INSERT INTO dept
VALUES(p_deptno,p_dname,p_loc);
END@@
DELIMITER ;
CALL dept_p(50,'HR','CHINA');
SELECT * FROM dept WHERE deptno=50;
【例3-40】创建存储过程dept_p2，该过程根据提供的部门编号，返回部门的名称和地址。
DELIMITER @@
CREATE PROCEDURE dept_p2(
IN i_no DECIMAL(2,0),
OUT o_name VARCHAR(14),
OUT o_loc VARCHAR(13)
)
BEGIN
SELECT dname,loc INTO o_name,o_loc FROM dept
WHERE deptno=i_no;
END@@
DELIMITER ;
CALL dept_p2(10,@v_dname,@v_loc);
SELECT @v_dname,@v_loc;
【例3-41】使用INOUT参数实现两个数的交换。
DELIMITER @@
CREATE PROCEDURE swap(
INOUT p_num1 int,
INOUT p_num2 int
)
BEGIN
DECLARE var_temp int;
SET var_temp=p_num1;
SET p_num1=p_num2;
SET p_num2=var_temp;
END@@
DELIMITER ;
SET @v_num1=1;
SET @v_num2=2;
CALL swap(@v_num1,@v_num2);
SELECT @v_num1,@v_num2;
【例3-42】删除已创建的存储过程emp_p。
DROP PROCEDURE emp_p;
【例3-43】创建存储过程emp_p，用游标提取emp表中7788雇员的姓名和职务。
DELIMITER @@
CREATE PROCEDURE emp_p()
BEGIN
DECLARE v_ename VARCHAR(14); ##定义存放姓名值的变量
DECLARE v_job VARCHAR(13); ##定义存放工作值的变量
DECLARE emp_cursor CURSOR ##声明游标
FOR SELECT ename,job FROM emp
WHERE empno=7788;
OPEN emp_cursor; ##打开游标
FETCH emp_cursor INTO v_ename,v_job; ##提取游标数据到变量
CLOSE emp_cursor; ##关闭游标
SELECT v_ename,v_job;
END@@
DELIMITER ;
CALL emp_p();
【例3-44】创建存储过程emp_p1，用游标显示工资最高的前3名雇员的姓名和工资。
DELIMITER @@
CREATE PROCEDURE emp_p1()
BEGIN
DECLARE v_ename VARCHAR(14);
DECLARE v_sal DECIMAL(7,2);
DECLARE i INT;
DECLARE mycursor CURSOR
FOR SELECT ename,sal FROM emp
ORDER BY sal DESC
LIMIT 3;
SET i=1;
CREATE TABLE result(
ename VARCHAR(14),
sal DECIMAL(7,2)
);
OPEN mycursor;
WHILE i<=3 DO
FETCH mycursor INTO v_ename,v_sal;
INSERT INTO result VALUES(v_ename,v_sal);
SET i=i+1;
END WHILE;
CLOSE mycursor;
SELECT * FROM result;
END@@
DELIMITER ;
CALL emp_p1();
【例3-45】创建存储函数emp_ins_fun，向emp表插入一条记录,empno和ename字段的值为7396, ‘MARY’，已知7369雇员编号已存在于emp表中，违背了主键约束。
DELIMITER @@
CREATE FUNCTION emp_ins_fun(no DECIMAL(4,0),name VARCHAR(14))
RETURNS VARCHAR(20)
BEGIN
INSERT INTO emp(empno,ename)
VALUES(no,name);
RETURN '插入成功';
END@@
DELIMITER ;
SELECT emp_ins_fun(7369,'MARY');
加入了错误处理机制的存储程序
DELIMITER @@
CREATE FUNCTION emp_ins_fun(no DECIMAL(4,0),name VARCHAR(14))
RETURNS VARCHAR(20)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
RETURN '违反主键约束！';
INSERT INTO emp(empno,ename)
VALUES(no,name);
RETURN '插入成功';
END@@
DELIMITER ;
SELECT emp_ins_fun(7369,'MARY');
DELIMITER ;
SELECT emp_ins_fun(7000,'MARY');
【例3-46】创建存储过程emp_up_pro，使用游标更新emp_c表（与emp表相同）中的comm值。
DELIMITER @@
CREATE PROCEDURE emp_up_pro()
BEGIN
DECLARE v_empno DECIMAL(4,0);
DECLARE v_sal DECIMAL(7,2);
DECLARE v_comm DECIMAL(7,2);
DECLARE flag BOOLEAN DEFAULT TRUE;
DECLARE comm_cur CURSOR
FOR SELECT empno,sal FROM emp_c;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET flag=FALSE;
OPEN comm_cur;
WHILE flag DO
FETCH comm_cur INTO v_empno,v_sal;
IF v_sal<500 THEN SET v_comm=v_sal*0.25;
ELSEIF v_sal<1000 THEN SET v_comm=v_sal*0.2;
ELSEIF v_sal<3000 THEN SET v_comm=v_sal*0.15;
ELSE SET v_comm=v_sal*0.12;
END IF;
UPDATE emp_c SET comm=v_comm
WHERE empno=v_empno;
END WHILE;
CLOSE comm_cur;
END@@
DELIMITER ;
SET SQL_SAFE_UPDATES=0;
CALL emp_up_pro();
SELECT * FROM emp_c;
第4章 关系模型基本理论
【例4-2】建立主键约束示例。
（1）列级主键约束
CREATE TABLE employee
( empno DECIMAL(2) PRIMARY KEY,
name VARCHAR(8),
age DECIMAL(3),
deptno DECIMAL(2)
);
或
CREATE TABLE employee
( empno DECIMAL(2),
name VARCHAR(8),
age DECIMAL(3),
deptno DECIMAL(2),
CONSTRAINT pk_no PRIMARY KEY(empno)
);
（2）表级PRIMARY KEY约束
CREATE TABLE employee
( empno DECIMAL(2),
name VARCHAR(8),
age DECIMAL(3),
deptno DECIMAL(2),
CONSTRAINT pk_no PRIMARY KEY(empno,deptno)
);
【例4-3】修改主键约束示例。
（1）删除主键约束
ALTER TABLE employee
DROP PRIMARY KEY;
（2）创建表后添加主键约束
ALTER TABLE employee
ADD CONSTRAINT pk_no PRIMARY KEY(empno);
【例4-4】示例。
（1）建立employee表，在employee表中定义一个phone字段，并为phone字段定义指定名称的唯一约束。
CREATE TABLE employee
( empno DECIMAL(2) PRIMARY KEY,
name VARCHAR(8),
age DECIMAL(3),
phone VARCHAR(12),
deptno DECIMAL(2),
CONSTRAINT emp_phone UNIQUE(phone)
);
（2）删除唯一约束emp_phone。
ALTER TABLE employee
DROP INDEX emp_phone;
（3）为已有表employee根据phone字段创建唯一约束，约束名为emp_phone。
ALTER TABLE employee
ADD CONSTRAINT emp_phone UNIQUE(phone);
【例4-5】示例。
（1）建立employee表，限制age字段的值必须大于20且小于60。
CREATE TABLE employee
( empno DECIMAL(2) PRIMARY KEY,
name VARCHAR(8),
age DECIMAL(3),
phone VARCHAR(12) UNIQUE,
deptno DECIMAL(2),
CONSTRAINT age_CK CHECK (age>20 AND age<60)
);
（2）为已有表employee增加一个新字段address。然后再为employee表创建CHECK约束，限制每条记录age字段的值必须大于20小于60，而且address字段值以‘北京市’开头。
ALTER TABLE employee
ADD address VARCHAR(30);
ALTER TABLE employee
ADD CONSTRAINT age_add_CK
CHECK (age>20 AND age<60 AND address LIKE '北京市%');
【例4-6】示例。
（1）建立employee和department表，实现两表间的外键约束，并指定为级联更新。
CREATE TABLE department
( deptno DECIMAL(5) PRIMARY KEY,
dept_name VARCHAR(16)
);
CREATE TABLE employee
( empno DECIMAL(5) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
age DECIMAL(3),
deptno DECIMAL(5),
CONSTRAINT FK_ID
FOREIGN KEY(deptno) REFERENCES department(deptno)
ON UPDATE CASCADE
);
（2）删除employee表上的FK_ID约束。
ALTER TABLE employee
DROP FOREIGN KEY FK_ID;
（3）为employee表和department表设置外键约束，并指定为级联删除。
ALTER TABLE employee
ADD CONSTRAINT FK_ID FOREIGN KEY(deptno)
REFERENCES department(deptno)
ON DELETE CASCADE;
【例4-7】创建触发器delete_trigger触发器，触发器将记录哪些用户删除了department表中的数据，以及删除的时间。
CREATE TABLE merch_log
( who VARCHAR(30),
oper_date DATE
);
CREATE TRIGGER delete_trigger
AFTER DELETE
ON department
FOR EACH ROW
INSERT INTO merch_log(who,oper_date) VALUES(USER(),SYSDATE());
DELETE FROM department WHERE deptno=10;
SELECT * FROM merch_log;
【例4-8】本例实现级联更新。在修改department表中的deptno之后（AFTER）级联地、自动地修改employee表中原来在该部门的雇员的deptno。
CREATE TRIGGER tr_dept_emp
AFTER UPDATE
ON department
FOR EACH ROW
UPDATE employee SET deptno=NEW.deptno
WHERE deptno=OLD.deptno;
【例4-9】删除【例4-8】建立的tr_dept_emp触发器。
DROP TRIGGER tr_dept_emp;
第5章 数据库的安全性
【例5-1】创建用户TEMPUSER，其口令为test。
CREATE USER tempuser@localhost IDENTIFIED BY 'temp';
【例5-2】修改用户账号tempuser的密码为root。
SET PASSWORD FOR tempuser@localhost='root';
【例5-3】修改root超级用户的密码为root。
SET PASSWORD FOR root@localhost='root';
【例5-4】修改普通用户tempuser的用户名为temp_U。
RENAME USER tempuser@localhost TO temp_U@localhost;
USE mysql;
SELECT * FROM user WHERE user='temp_U' and host='localhost';
【例5-5】删除用户temp_U。
DROP USER temp_U@localhost;
USE mysql;
SELECT * FROM user WHERE user='temp_U' and host='localhost';
【例5-6】示例。
USE mysql;
CREATE USER column_user@localhost IDENTIFIED BY 'password';
GRANT SELECT(ename,sal,empno),UPDATE(sal)
ON TABLE scott1.emp
TO column_user@localhost
WITH GRANT OPTION;
SELECT * FROM columns_priv;
以column_user用户连接MySQL服务器，执行如下语句。
SELECT ename,sal FROM scott1.emp;
SELECT job,comm FROM scott1.emp;
【例5-7】示例。
USE mysql;
CREATE USER table_user@localhost IDENTIFIED BY 'password';
GRANT ALTER,SELECT,INSERT(empno,ename)
ON TABLE scott1.emp
TO table_user@localhost;
SELECT * FROM tables_priv
WHERE host='localhost' and user='table_user';
以table_user用户连接MySQL服务器，执行如下语句。
DESC scott1.emp;
ALTER TABLE scott1.emp
MODIFY COLUMN empno INT;
DESC scott1.emp;
【例5-8】示例。
USE mysql;
CREATE USER proc_user@localhost IDENTIFIED BY 'password';
GRANT EXECUTE ON PROCEDURE scott1.emp_p
TO proc_user@localhost;
GRANT ALTER ROUTINE,EXECUTE ON FUNCTION scott1.sum_fn
TO proc_user@localhost;
SELECT * FROM procs_priv;
以proc_user用户连接MySQL服务器，执行如下语句。
CALL scott1.emp_p;
SELECT scott1.sum_fn(3); ##sum_fn(n)返回1~n的和
DROP FUNCTION scott1.sum_fn;
【例5-9】示例。
USE mysql;
CREATE USER database_user@localhost IDENTIFIED BY 'password';
GRANT CREATE,SELECT,DROP
ON scott1.*
TO database_user@localhost;
SELECT * FROM db
WHERE host='localhost' and db='scott1';
以database_user用户连接MySQL服务器，执行如下语句。
CREATE TABLE scott1.employee
( empno INT NOT NULL PRIMARY KEY,
ename VARCHAR(10)
);
DROP TABLE scott1.employee;
【例5-10】示例。
USE mysql;
CREATE USER server_user@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES
ON *.*
TO server_user@localhost;
SELECT * FROM user
WHERE host='localhost' and user='server_user';
以sever_user用户连接MySQL服务器，执行如下语句。
CREATE DATABASE student;
【例5-11】撤消【例5-6】用户column_user@localhost的所有权限。
SELECT * FROM mysql.columns_priv;
REVOKE ALL PRIVILEGES,GRANT OPTION
FROM column_user@localhost;
SELECT * FROM mysql.columns_priv;
SELECT * FROM mysql.user
WHERE host='localhost' and user='column_user';
【例5-12】撤消【例5-9】用户database_user@localhost的CREAT和DROP权限。
SELECT * FROM db
WHERE host='localhost' and user='database_user';
REVOKE CREATE,DROP
ON scott1.*
FROM database_user@localhost;
SELECT * FROM db
WHERE host='localhost' and user='database_user';
【例5-13】分别在本地主机上创建应用程序角色app、运维人员角色ops、开发人员读角色dev_read、开发人员写角色dev_write。
USE mysql;
CREATE ROLE 'app'@'localhost','ops'@'localhost',
'dev_read'@'localhost','dev_write'@'localhost';
SELECT * FROM USER
WHERE host='localhost'
and user IN('app','ops','dev_read','dev_write');
【例5-14】分别授予角色app数据读写权限、角色ops访问数据库权限、角色dev_read读取权限、角色dev_write写权限。
GRANT SELECT,INSERT,UPDATE,DELETE
ON SCOTT1.* TO 'app'@'localhost';
GRANT ALL PRIVILEGES
ON SCOTT1.* TO 'ops'@'localhost';
GRANT SELECT
ON SCOTT1.* TO 'dev_read'@'localhost';
GRANT INSERT,UPDATE,DELETE
ON SCOTT1.* TO 'dev_write'@'localhost';
SELECT * FROM db
WHERE host='localhost' and
user IN('app','ops','dev_read','dev_write');
【例5-15】分别将角色授予新用户app01、ops01、dev01、dev02、dev03。
#创建新的用户账号
CREATE USER 'app01'@'%' IDENTIFIED BY '000000';
CREATE USER 'ops01'@'%' IDENTIFIED BY '000000';
CREATE USER 'dev01'@'%' IDENTIFIED BY '000000';
CREATE USER 'dev02'@'%' IDENTIFIED BY '000000';
CREATE USER 'dev03'@'%' IDENTIFIED BY '000000';
#给用户账号分配角色
GRANT 'app'@'localhost' TO 'app01'@'%';
GRANT 'ops'@'localhost' TO 'ops01'@'%';
GRANT 'dev_read'@'localhost' TO 'dev01'@'%';
GRANT 'dev_read'@'localhost','dev_write'@'localhost'
TO 'dev02'@'%','dev03'@'%';
#验证角色是否正确分配，可使用SHOW GRANTS语句
SHOW GRANTS FOR 'dev01'@'%' USING 'dev_read'@'localhost';
注：用户使用角色权限前，必须先激活角色，设置语句为：
set global activate_all_roles_on_login=ON;
【例5-16】撤消用户app01的角色app。
REVOKE 'app'@'localhost' FROM 'app01'@'%';
SHOW GRANTS FOR 'app01'@'%' USING 'app'@'localhost';
【例5-17】删除角色app和ops。
DROP ROLE 'app'@'localhost','ops'@'localhost';
下面为图片补充
#验证角色是否正确分配，可使用SHOW GRANTS语句
SHOW GRANTS FOR 'dev01'@'%' USING 'dev_read'@'localhost';
注：用户使用角色权限前，必须先激活角色，设置语句为：
set global activate_all_roles_on_login=ON;
【例5-16】撤消用户app01的角色app。
REVOKE 'app'@'localhost' FROM 'app01'@'%';
第6章 事务与并发控制
【例6-1】创建表TEST，并为其添加一个主键约束和触发器。为表添加两行数据，随后对这行数据进行更新，使行的主键值相同。由于主键约束的存在，当事务结束时，更新操作会失败。
CREATE TABLE test(
i INT NOT NULL PRIMARY KEY
);
CREATE TRIGGER test_tgr
AFTER UPDATE
ON test
FOR EACH ROW
set @in_fo=CONCAT('更新前i=',old.i,' 更新后i=',new.i);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
SET SQL_SAFE_UPDATES=0;
UPDATE test SET i=2 WHERE i=1;
SELECT @in_fo;
UPDATE test SET i=i+2 WHERE i=1;
SELECT @in_fo;
【例6-2】假设银行存在两个借记卡账户（account）‘李三’与’王五’，要求这两个借记卡账户不能用于透支，即两个账户的余额（balance）不能小于0。创建存储过程tran_proc()，实现两个账户的转账业务。
#建立account表
CREATE TABLE account(
account_no INT AUTO_INCREMENT PRIMARY KEY,
account_name VARCHAR(10) NOT NULL,
balance INT UNSIGNED #balance不能取负值
);
#向account表插入记录
INSERT INTO account VALUES(null,'李三',1000);
INSERT INTO account VALUES(null,'王五',1000);
#创建存储过程tran_proc，实现转账业务
DELIMITER @@
CREATE PROCEDURE tran_proc(IN from_account INT,
IN to_account INT,
IN money INT)
BEGIN
DECLARE CONTINUE HANDLER FOR 1690
BEGIN
SELECT '余额小于0' 信息;
ROLLBACK; ##回滚事务
END;
START TRANSACTION; ##开始事务
UPDATE account SET balance=balance+money
WHERE account_no=to_account;
UPDATE account SET balance=balance-money
WHERE account_no=from_account;
COMMIT; ##提交事务
END@@
DELIMITER ;
CALL tran_proc(1,2,800);
SELECT * FROM account;
CALL tran_proc(1,2,800);
SELECT * FROM account;
【6-3】示例。下面创建的两个存储过程，分别对在同一个事务中创建两个账号相同的银行账户进行的不同处理。
创建save_p1_proc，仅仅撤消第二条insert语句，但提交了第一条insert语句。
DELIMITER @@
CREATE PROCEDURE save_p1_proc()
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
ROLLBACK TO b; ##事务回滚到保存点b
END;
START TRANSACTION;
INSERT INTO account VALUES(null,'赵四',1000);
SAVEPOINT b; ##设置保存点
##last_insert_id()获取'赵四'账户的账号
INSERT INTO account VALUES(last_insert_id(),'钱六',1000);
COMMIT;
END@@
DELIMITER ;
CALL save_p1_proc();
SELECT * FROM account;
创建save_p2_proc，先撤消第二条insert语句，然后撤消了所有的insert语句。
DELETE FROM account WHERE account_no=3;
SELECT * FROM account;
DELIMITER @@
CREATE PROCEDURE save_p2_proc()
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
ROLLBACK TO b;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO account VALUES(null,'赵四',1000);
SAVEPOINT b;
INSERT INTO account VALUES(last_insert_id(),'钱六',1000);
COMMIT;
END@@
DELIMITER ;
CALL save_p2_proc();
SELECT * FROM account;
【例6-7】脏读现象示例。
（1）打开MySQL客户机A，执行下面语句。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;
START TRANSACTION;
SELECT * FROM account;
（2）打开MySQL客户机B，执行下面语句。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE account SET balance=balance+1000 WHERE account_no=1;
（3）打开MySQL客户机A，执行下面语句。
SELECT * FROM account;
【例6-8】不可重复读现象示例。
（1）打开MySQL客户机A，执行下面语句。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
START TRANSACTION;
SELECT * FROM account;
（2）打开MySQL客户机B，执行下面语句。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE account SET balance=balance+1000 WHERE account_no=1;
COMMIT;
（3）打开MySQL客户机A，执行下面语句。
SELECT * FROM account;
【例6-9】幻影读现象示例。
（1）打开MySQL客户机A，执行下面语句。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;
START TRANSACTION;
SELECT * FROM account;
（2）打开MySQL客户机B，执行下面语句。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO account VALUES(10,'赵六',3000);
COMMIT;
SELECT * FROM account;
（3）打开MySQL客户机A，执行下面语句。
SELECT * FROM account;
（4）由于MySQL客户机A检测到account表中不存在account_no=10的账户信息，在MySQL客户机A继续执行下面INSERT语句。
INSERT INTO account VALUES(10,'赵六',3000);
【例6-10】避免幻影读现象示例。
（1）打开MySQL客户机A，执行下面语句。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;
START TRANSACTION;
SELECT * FROM account;
（2）打开MySQL客户机B，执行下面语句。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
INSERT INTO account VALUES(20,'马七',5000);
SELECT * FROM account;
【例6-14】表级锁示例。
（1）打开MySQL客户机A，执行下面的SQL语句。
USE scott;
LOCK TABLES account READ;
SELECT * FROM account;
INSERT INTO account VALUES('100','王小一',5000);
（2）打开MySQL客户机B，执行下面的SQL语句。
USE SCOTT;
LOCK TABLES account READ;
SELECT * FROM account;
UNLOCK TABLES;
LOCK TABLES account WRITE;
（3）打开MySQL客户机A，执行下面的SQL语句。
UNLOCK TABLES;
（4）打开MySQL客户机B，执行下面的SQL语句。
LOCK TABLES account WRITE;
INSERT INTO account VALUES(20,'马七',5000);
SELECT * FROM account;
UNLOCK TABLES;
例6-15】行级锁示例。
（1）在MySQL客户机A上执行下面的SQL语句，开启事务，并为account表施加行级写锁。
USE SCOTT;
START TRANSACTION;
SELECT * FROM account FOR UPDATE;
（2）在MySQL客户机B上执行下面的SQL语句，开启事务，并为account表施加行级写锁。此时，MySQL客户机B被阻塞。
USE SCOTT;
START TRANSACTION;
SELECT * FROM account FOR UPDATE;
（3）在MySQL客户机A上执行下面的SQL语句，为account表解锁。
COMMIT;
（4）在MySQL客户机B上执行下面的SQL语句。因为MySQL客户机A释放了account表的行级锁，MySQL客户机B被“唤醒”，得以继续执行。
SELECT * FROM account FOR UPDATE;
COMMIT;
第7章 故障恢复
【例7-1】建立数据库students_db。
为了更好理解mysqldump工具的使用，这里给出一个完整的数据库，按下面命令创建students_db数据库和各个表，并插入记录。
CREATE DATABASE students_db;
USE students_db;
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY,
sname VARCHAR(10),
ssex CHAR(2),
sage INT
);
CREATE TABLE sc(
sno INT,
cno INT,
score DECIMAL(5,2),
PRIMARY KEY(sno,cno)
);
CREATE TABLE course(
cno INT PRIMARY KEY,
cname VARCHAR(20),
credit INT
);
INSERT INTO student VALUES(1,'MARY','F',19);
INSERT INTO student VALUES(2,'JACK','M',20);
INSERT INTO sc VALUES(1,1,90);
INSERT INTO sc VALUES(1,2,80);
INSERT INTO sc VALUES(2,1,78);
INSERT INTO sc VALUES(2,2,81);
INSERT INTO course VALUES(1,'C-PROGRAME',3);
INSERT INTO course VALUES(2,'DATABASE-SYSTEM',4);
【例7-2】使用mysqldump命令备份数据库students_db中的所有表，存于f:\db_bak文件夹下，文件名为studentdb_bak.sql。
【例7-3】使用mysqldump命令备份数据库students_db中的student表、course表，存于f:\db_bak文件夹下，文件名为studentdb_table_bak.sql。
【例7-4】使用mysqldump命令备份数据库students_db和scott，存于f:\db_bak文件夹下，文件名为db_bak.sql。
【例7-5】使用mysqldump命令备份所有数据库，存于f:\db_bak文件夹下，文件名为all_db_bak.sql。
【例7-6】使用mysql命令将备份文件studentdb_bak.sql恢复到数据库students中。
（1）在MySQL服务器上创建students数据库。
CREATE DATABASE students;
（2）在CMD命令提示符窗口中执行如下命令。
【例7-7】使用SELECT …INTO OUTFILE语句备份students_db数据库中的student表的数据。要求字段之间用“｜”隔开，字符型数据用双引号括起来。
USE students_db;
SELECT * FROM student
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/table_bak.txt'
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
【例7-8】使用mysqldump命令将students_db数据库中的sc表中的记录导出到文本文件。
【例7-9】使用LOAD DATA INFILE语句将【例7-7】table_bak.txt文件中的数据导入到students_db数据库的student表。
（1）先将student表中的数据全部删除。
USE students_db;
SET SQL_SAFE_UPDATES=0;
DELETE FROM student;
SELECT * FROM student;
（2）从table_bak.txt文件恢复数据。
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/table_bak.txt'
INTO TABLE students_db.student
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
SELECT * FROM student;
【例7-10】使用mysqlimport命令将【例7-8】sc.txt文件中的数据导入到students_db数据库的sc表。
（1）先将sc表中的数据全部删除。
USE students_db;
SET SQL_SAFE_UPDATES=0;
DELETE FROM sc;
（2）从sc.txt文件恢复数据。
【例7-11】使用SHOW GLOBAL查看二进制日志设置。
SHOW GLOBAL VARIABLES LIKE '%log_bin%';
【例7-12】使用SHOW BINAR LOG查看二进制日志文件个数及文件名。
SHOW BINARY LOGS;
【例7-13】使用mysqlbinlog查看二进制日志。
【例7-14】使用mysqlbinlog恢复MySQL数据库到2018年11月5日0点时的状态。
第10章
数据库设计
【例10-11】查询MySQL服务器连接的次数。
SHOW STATUS LIKE 'Connections';
【例10-12】使用EXPLAIN语句分析一个查询语句。
EXPLAIN SELECT * FROM employee;
【例10-13】使用DESCIBE语句分析一个查询语句。
DESCRIBE SELECT * FROM employee WHERE 性别='女';
【例10-14】下面是查询语句中不使用索引和使用索引的对比。
EXPLAIN SELECT * FROM employee WHERE 姓名='刘东阳';
CREATE INDEX name_idx ON employee(姓名);
EXPLAIN SELECT * FROM employee WHERE 姓名='刘东阳';
【例10-15】查询语句中使用LIKE关键字，并且匹配的字符串中包含有“%”的两种查询情况比较。
EXPLAIN SELECT * FROM employee WHERE 姓名 LIKE '%阳';
EXPLAIN SELECT * FROM employee WHERE 姓名 LIKE '刘%';
【例10-16】下面在employee表的学号和性别两个字段上创建多列索引，然后验证多列索引的使用情况。
CREATE INDEX sno_sex_idx ON employee(学号,性别);
EXPLAIN SELECT * FROM employee WHERE 学号='0001';
EXPLAIN SELECT * FROM employee WHERE 性别='女';
【例10-17】查询语句中使用OR关键字示例。
EXPLAIN SELECT * FROM employee WHERE 学号='0001' OR 姓名='刘东阳';
EXPLAIN SELECT * FROM employee WHERE 学号='0001' OR 出生日期='1990-10-23';
【例10-18】假设employee表中有很多字段，其中“备注”字段存储着雇员的备注信息，有些备注信息的内容特别多。另外“备注”信息很少使用。对employee表进行分解。
SELECT 姓名,备注 FROM employee_info i,employee_extra e
WHERE i.学号=e.学号;
【例10-19】有employee表（雇员编号，姓名，职位，入职日期，工资，部门编号），department表（部门编号，部门名称，地址），实际中经常要查询雇员姓名、所在部门及工资信息。通过增加中间表，提高查询速度。
CREATE TABLE temp_emp(
姓名 VARCHAR(10),
部门名称 VARCHAR(14),
工资 DECIMAL(7,2)
);
INSERT INTO temp_emp
SELECT 姓名,部门名称,工资 FROM employee e,department d
WHERE e.部门编号=d.部门编号;
第11章 数据库应用系统设计实例
CREATE DATABASE JXGL;
USE JXGL;
CREATE TABLE TeachInfor(
Tcode varchar(10) NOT NULL,
Tname varchar(10) NOT NULL,
Nativeplace varchar(12),
Sex varchar(4),
Speciality varchar(16) NOT NULL,
Title varchar(16) NOT NULL,
Birthday date,
Faddress varchar(30),
Logincode varchar(10),
LoginIP varchar(15),
Lastlogin date,
CONSTRAINT tcode_PK PRIMARY KEY(Tcode)
);
CREATE TABLE SpeInfor(
Specode varchar(8) NOT NULL,
Spename varchar(30) NOT NULL,
Spechar varchar(20),
Speshort varchar(10),
Degree varchar(10),
CONSTRAINT Specode_PK PRIMARY KEY(Specode)
);
CREATE TABLE ClassInfor(
Classcode varchar(8) NOT NULL,
Classname varchar(20) NOT NULL,
Classshort varchar(10),
Specode varchar(8),
CONSTRAINT Classcode_PK PRIMARY KEY(Classcode),
CONSTRAINT Specode_FK FOREIGN KEY(Specode)
REFERENCES SpeInfor(Specode)
);
CREATE TABLE CourseInfor(
Ccode varchar(8) NOT NULL,
Coursename varchar(20) NOT NULL,
Period varchar(10),
Credithour decimal(4,1),
CONSTRAINT Ccode_PK PRIMARY KEY(Ccode)
);
CREATE TABLE ClassRoom(
Roomcode varchar(8) NOT NULL,
Capacity decimal(4),
Type varchar(20),
CONSTRAINT Rcode_PK PRIMARY KEY(Roomcode)
);
CREATE TABLE SchemeInfor(
Tcode varchar(10),
Ccode varchar(8),
Tname varchar(10),
Coursename varchar(20),
Year varchar(4),
Term varchar(4),
CONSTRAINT Tcode_FK FOREIGN KEY(Tcode)
REFERENCES TeachInfor(Tcode),
CONSTRAINT Ccode_FK FOREIGN KEY(Ccode)
REFERENCES CourseInfor(Ccode)
);
CREATE TABLE Courseplan(
Tcode varchar(10),
Ccode varchar(8),
Roomcode varchar(8),
TableTime varchar(10),
Tname varchar(10),
Coursename varchar(20),
Week decimal(2),
CONSTRAINT Tcode_FK1 FOREIGN KEY(Tcode)
REFERENCES TeachInfor(Tcode),
CONSTRAINT Ccode_FK1 FOREIGN KEY(Ccode)
REFERENCES CourseInfor(Ccode),
CONSTRAINT Rcode_FK1 FOREIGN KEY(Roomcode)
REFERENCES ClassRoom(Roomcode)
);
CREATE TABLE Examplan(
Tcode varchar(10),
Ccode varchar(8),
Roomcode varchar(8),
ExamTime varchar(10),
Tname varchar(10),
Coursename varchar(20),
Studnum decimal(2),
CONSTRAINT Tcode_FK2 FOREIGN KEY(Tcode)
REFERENCES TeachInfor(Tcode),
CONSTRAINT Ccode_FK2 FOREIGN KEY(Ccode)
REFERENCES CourseInfor(Ccode),
CONSTRAINT Rcode_FK2 FOREIGN KEY(Roomcode)
REFERENCES ClassRoom(Roomcode),
CONSTRAINT Snum_CK CHECK(Studnum>=1 and Studnum<=50)
);
1）学生表上聚簇索引的建立
CREATE TABLE StudInfor(
Scode varchar(10) NOT NULL,
Sname varchar(10) NOT NULL,
Nativeplace varchar(12),
Sex varchar(4),
Birthday date,
Faddress varchar(30),
Classcode varchar(8),
Logincode varchar(10),
LoginIP varchar(15),
Lastlogin date,
CONSTRAINT Scode_PK PRIMARY KEY(Scode),
CONSTRAINT Class_FK FOREIGN KEY(Classcode)
REFERENCES ClassInfor(Classcode),
CONSTRAINT Sex_CK CHECK(Sex='男' OR Sex='女')
);
CREATE UNIQUE INDEX s_cluster
ON StudInfor(Scode);
2）修课表上聚簇索引的建立
CREATE TABLE StudCourse(
Scode varchar(10) not null,
Tcode varchar(10) not null,
Ccode varchar(8) not null,
Sname varchar(10),
Tname varchar(10),
Coursename varchar(20),
CourseAudit varchar(8),
ExamGrade decimal(4,1),
GradeAudit varchar(10),
CONSTRAINT Scode_FK FOREIGN KEY(Scode)
REFERENCES StudInfor(Scode),
CONSTRAINT Tcode_FK3 FOREIGN KEY(Tcode)
REFERENCES TeachInfor(Tcode),
CONSTRAINT Ccode_FK3 FOREIGN KEY(Ccode)
REFERENCES CourseInfor(Ccode),
CONSTRAINT Grade_CK CHECK(ExamGrade>=0 and ExamGrade<=100),
CONSTRAINT s_c_cluster PRIMARY KEY(Scode,Tcode,Ccode)
);
扫一扫
1513
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
