第三本书《Mysql数据库总结》

第一，二章

第一章

1.什么是数据库（Database，简称DB）：长期存放在机算计内，有组织，可共享的大量数据的集合，是一个数据仓库；

2.作用：保存，管理数据

3.什么数据库管理系统（database management system，简称DBMS）：数据管理软件，科学组织和存储数据，高效地获取和维护数据

4.MySQL:是现在流行的开源关系型数据库；特点：免费，开源数据库，小巧，功能齐全，使用便捷，可运行于Windows或Linux操作系统，可适用于中小型甚至大型网站应用

5.SQL的组成：

名称	解释	命令
DML（数据操纵语言）	用来插入（INSERT）、修改（UPDATE）、删除（DELETE）数据库中的数据	INSERT、UPDATE、DELETE
DDL（数据定义语言）	用来建立数据库、数据库对象，定义数据表结构等，大部分以CREATE开头的命令	CREATE、DROP、ALTER
DQL（数据查询语言）	用来对数据库中的数据进行查询	SELECT
DCL（数据控制语言）	用来控制数据库组件的存取许可、存取权限等	GRANT、REVOKE、COMMIT

6.：功能说明（在DOC命令窗口中使用）

功能	说明
启动 / 关闭 MYSQL	以管理员身份进入dos命令窗口（windows+r），输入：net start/stop mysql
登陆MYSQL（前提已启动mysql）	输入mysql -uroot (用户名) -pyxc（登陆密码）
显示数据库	show databases;
查看数据库版本	select version();
使用数据库	use mysql（mysql为可选项）
查看表格	select * from 表格名
在user表里查看所有的记录	select * from user \G
修改用户信息（刷新后重登即可）	update user set password=password(加密)‘123’ where user=‘root’
修改信息后再刷新	flush privileges
退出	exit
查询	？+需要查找的命令

7：功能说明（使用SQLyog工具）

（1）create table [if not exsits] 表名（

​ 字段1 数据类型 字段属性|约束 索引 注释

​ 字段2 数据类型 字段属性|约束 索引 注释

）表类型 表字符集 注释

#创建数据库
CREATE DATABASE IF NOT EXISTS school;

（2）字段属性约束

字段属性约束名	关键字	说明
非空约束	NOT NULL	不允许为空
默认约束	DEFUL	赋予某字段默认值
唯一约束	UNIQUE KEY(UK)	设置字段值是唯一的，允许为空，但只能有一个空值
主键约束	PRIMARY KEY	设置字段为表的主键，可以作为该表的记录的唯一标识
外键约束	FOREIGN KEY(FK)	InnoDB支持外键，MyISAM不支持
自动增长	AUTO_INCREMENT	默认自增1，通常用于设置主键，且为整数类型，可设置初始值和步长
注释	COMMENT	为表或字段添加说明文字

（3）编码格式设置

CREATE TABLE test(

​ 代码

)CHARSET=字符集名

（4）删除表

DROP TABLE [IF EXISTS]表名

（5）修改表

ALTER TABLE <旧表名>RENAME[TO]<新表名>

例：ALTER TABLE ·student·RENAME teacher;

第三、四章总结

第三章

1、常用的存储引擎

功能	InnoDB	MyISAM
支持事务	支持	不支持
支持全文索引	不支持	支持
外键约束	支持	不支持
表空间大小	较大	较小
数据行锁定	支持	不支持

2、MySQL5.5以上版本默认的存储引擎是InnoDB，查看当前默认的存储引擎：

​ show variables like storage_engine%

3、指定表的存储引擎

​ create table 表名（

​

​ ）engine=MyISAM;

4、表文件

MyISAM类型的表文件	说明
.frm文件	表结构定义文件。主要存放表的元数据，包括表结构定义信息等。该文件与存储引擎无关，即任何存储类型的表都会有这个文件
MYI文件	索引文件。主要存放MyISAM类型表的索引信息，每个MyISAM类型的表有一个.MYI文件，存放的位置与.frm文件一样
.MYD文件	数据文件。存放表中数据的文件
InnoDB类型的表文件
.frm文件	表结构定义文件
ibdatal	数据文件。保存所有InnoDB类型表的数据。这个文件保存位置与.firm文件不同

5、插入数据记录

（1）插入单行数据

语法：insert into 表名[(字段名列表)] values(值列表)

其中：

a、表的字段是可选的，如果省略，则依次插入所有字段

b、多个列表和多个值之间使用逗号分隔

c、值列表必须和字段名列表数量相同，且数据类型相同

d、如果插入的是表中部分列的数据，字段名列表必须填写

CREATE TABLE IF NOT EXISTS test(
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	sex CHAR(2) DEFAULT 1,
	address VARCHAR(20),
	phone VARCHAR(11) 
);
#使用语句增加数据
#指定加在test表中name的位置
INSERT INTO test(`name`) VALUES('jack');

#指定加在test表中，但不指定位置，则表示表里面的所有都添加内容，且字段名与值一一对应
INSERT INTO test VALUES(2,'tom');

（2）插入多行数据

语法：insert into 表名[(字段名列表)] values(值列表1)，(值列表2)，…，(值列表N);

CREATE TABLE IF NOT EXISTS test(
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	sex CHAR(2) DEFAULT 1,
	address VARCHAR(20),
	phone VARCHAR(11) 
);

#在指定的表里添加多个数据
INSERT INTO test(`name`) VALUE('daweu'),('kit');

#在指定表里添加多行数据
INSERT INTO test(id,`name`,sex,address,phone)
VALUE(1,'李四',DEFAULT,'深圳','12345678901'),(2,'李wu',DEFAULT,NULL,'12345678901');

（3）将查询结果插入到新表中

语法：create table 新表（select字段1，字段2，…from原表）；

create table ‘phoneList’(select ‘studentName’,'phone’from ‘student’);

（4）更新数据记录

语法：update 表名 set 列名=更新值[where 更新条件]

​ 其中：

a、set后面可以紧随多个“列名=更新值”以修改多个数据列的值，不同列之间使用逗号分隔

b、where子句是可选的，用来限制更新数据的条件。若不限制，则整个表的所有数据行将被更新

c、使用update语句可能更新一行数据，也可能更新多行数据，还可能不会更新任何数据

#使用语句修改数据
#将李四的地址修改为广州
UPDATE test SET address='广州'
WHERE id=1;
UPDATE test SET address='深圳'
WHERE id=2;

#同时修改多列
UPDATE test SET sex=2,phone='123321123'
WHERE id=1;

#条件使用运算符
UPDATE test SET sex=1
WHERE id=1 OR id=2;

UPDATE test SET sex=2
WHERE id>=1 AND id<=2;

UPDATE test SET sex=1
WHERE id BETWEEN 1 AND 2;

#使用函数
UPDATE test SET `name`=CONCAT("姓名：",`name`);

（5）删除数据记录

a、使用delete删除数据

语法：delete[from]表名 [where<删除条件>]

注意：删除主键值被其他表引用时，要先删除子表，再删除主表；delete删除数据后表的标识列不会重新开始编号，延续前面的编号

#删除语句
DELETE FROM test WHERE id=2;

b、使用truncate table删除数据

truncate table比delete执行的速度快，使用发系统资源和事务日志资源更少，并且删除数据后表的标识列会重新开始编号

（6）数据查询语句

​ （1）使用select语句进行查询

select<列名、表达式、函数、常量> from<表名>

[where<查询条件表达式>]

[order by<排序的列名>][ASC(降序)或DESC（升序）]

​ （2）查询所有的数据行和列

​ select * from student

#打开表的所有列（效率低）
SELECT * FROM tab1
#查询指定列（姓名，地址）
SELECT `name` ,address FROM tab1;
#去别名（as可省略）
SELECT `name`AS 姓名,address AS 地址 FROM tab1;

SELECT `name` 姓名,address  地址 FROM tab1;

#使用as为表取名（as可省略）
SELECT `name` 姓名,address  地址 FROM tab1 AS tab3;
SELECT `name` 姓名,address  地址 FROM tab1  tab3;

#使用as，为查询结果娶一个新的名字
SELECT CONCAT('姓名：',`name`) AS 新名字 FROM tab1;


#去除重复项
SELECT DISTINCT studentno FROM result;

#select 查询中可以使用表达式
SELECT @@auto_increment_increment
SELECT VERSION();
SELECT 100*3-1 AS 计算结果;

#学员考试成绩集体提高1分
SELECT studentno, studentresult+1 AS '提分后' FROM result;

#满足条件的查新（where）考试成绩95-100
SELECT studentno ,studentresult FROM result
WHERE studentresult>=95 AND studentresult<=100;

#模糊查询
SELECT studentno,studentresult FROM result
WHERE studentresult BETWEEN 95 AND 100;

#精确查询
SELECT studentno,studentresult FROM result
WHERE studentno=1000;

#and可以写成&&
SELECT studentno,studentresult FROM result
WHERE studentno>=95 && studentresult<=100;

#除了1000号同学，要其他学生的成绩
SELECT studentno,studentresult FROM result
WHERE studentno！=1000;

SELECT studentno,studentresult FROM result
WHERE NOT studentno=1000;


#模糊查询有：between  and\  like\ in\  null有：(if \if null )
#查询姓李的同学的学号及姓名
#Like 结合使用的统配符：% ：（李后面跟0到任意个字符） _：（李后面只跟一个字符）
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '李%';

#查姓李的，但是李后面只跟一个字符
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '李_';


#查姓李的，但是李后面跟两个字符,两个_
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '李__';


#查姓名中含有“文”字的同学
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%文%';

#查询学员姓名中有%这个字的同学学号、姓名时需用到转义符\
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%\%%';

SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%\_%';

#使用自己自定义的转义符如“：”，则需加escape
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%:_%' ESCAPE ':';

#模糊查询:in	 null
SELECT studentno,studentresult FROM result
WHERE studentno=1000 OR studentno=1001 OR studentno=1003;

SELECT studentno,studentresult FROM result
WHERE studentno IN(1000,1001,1002,1003);

SELECT studentno,studentresult FROM result
WHERE studentno IN('深圳','广州','河源','佛山');

#查询出生日期没有填写的同学（若where条件写成=null是错误的）需写成is null
SELECT studentno,studentresult FROM result
WHERE borndate IS NULL;

#查询出生日期填写的同学
SELECT studentno,studentresult FROM result
WHERE borndate IS NOT NULL;

#区别空字符串（原来存在后来被删除了）与null（意思为空）
SELECT studentname FROM student
WHERE address ='' OR address IS NULL;

第四章

#模糊查询有：between  and\  like\ in\  null有：(if \if null )
#查询姓李的同学的学号及姓名
#Like 结合使用的统配符：% ：（李后面跟0到任意个字符） _：（李后面只跟一个字符）
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '李%';

#查姓李的，但是李后面只跟一个字符
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '李_';


#查姓李的，但是李后面跟两个字符,两个_
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '李__';


#查姓名中含有“文”字的同学
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%文%';

#查询学员姓名中有%这个字的同学学号、姓名时需用到转义符\
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%\%%';

SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%\_%';

#使用自己自定义的转义符如“：”，则需加escape
SELECT studentno,studentresult FROM result
WHERE studentname LIKE '%:_%' ESCAPE ':';

#模糊查询:in	 null
SELECT studentno,studentresult FROM result
WHERE studentno=1000 OR studentno=1001 OR studentno=1003;

SELECT studentno,studentresult FROM result
WHERE studentno IN(1000,1001,1002,1003);

SELECT studentno,studentresult FROM result
WHERE studentno IN('深圳','广州','河源','佛山');

#查询出生日期没有填写的同学（若where条件写成=null是错误的）需写成is null
SELECT studentno,studentresult FROM result
WHERE borndate IS NULL;

#查询出生日期填写的同学
SELECT studentno,studentresult FROM result
WHERE borndate IS NOT NULL;

#区别空字符串（原来存在后来被删除了）与null（意思为空）
SELECT studentname FROM student
WHERE address ='' OR address IS NULL;



#连接查询
#内连接：inner join ：查询两个表中的结果集中的交集（共同部分）
#外连接：outer join
	#左外连接：left (outer可省略)join:以左表作为基准，与右表来一一匹配，匹配不上的，返回左表的记录（内容），右表结果以null填充
	#右外连接：right (outer可省略)join：以右表作为基准，与左表来一一匹配，匹配不上的，返回右表的记录（内容），左表结果以null填充
#自连接
#等值连接	非等值连接（不加where,返回值为左表*右表）


#思路：
#(1)分析需求，确定查询的列来源于两个表student  result 连接查询
#（2）确定使用哪一种连接查询？---内连接
SELECT s.studentNo,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS a
ON a.studentNo=s.studentNo
#右外连接
SELECT s.studentNo,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS a
ON a.studentNo=s.studentNo

#左外连接
SELECT s.studentNo,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS a
ON a.studentNo=s.studentNo

#查询缺考的同学
SELECT s.studentNo,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS a
ON a.studentNo=s.studentNo
WHERE studentresult IS NULL


#等值连接(相当于内连接)
SELECT s.studentNo,studentname,subjectno,studentresult
FROM student AS s,result AS a
WHERE a.studentNo=s.studentNo
#非等值连接（去掉where条件）
SELECT s.studentNo,studentname,subjectno,studentresult
FROM student AS s,result AS a

#自连接
CREATE TABLE IF NOT EXISTS category(
	categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	pid INT(10) NOT NULL,
	categoryName VARCHAR(50) NOT NULL,
	PRIMARY KEY(categoryid)
)

INSERT INTO category VALUE (2,1,"美术设计"),
(3,1,"软件开发"),
(4,3,"数据库基础"),
(5,2,"PHP基础"),
(6,3,"java基础"),
(7,2,"色彩搭配"),
(8,2,"ps基础");

SELECT * FROM category

#编写SQL语句，将栏目的父子关系呈现出来，（父类栏目名称，子栏目名称）
#父栏目       字栏目
#美术设计	色彩搭配
#美术设计	ps基础
#软件开发	数据库基础
#软件开发	PHP基础
#软件开发	java基础
#把category 表看作两张一模一样的表，然后将两张表连接查询
SELECT a.categoryName AS'父栏目', b.categoryName AS'子栏目'
FROM category AS a,category AS b
WHERE a.categoryid=b.pid




#查询参加过考试的同学信息（学号  姓名  科目名称  成绩）
SELECT s.studentNo,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result AS a
ON a.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON a.subjectNo=sub.subjectno

#查询一门科目《数据库-1》的所有考试结果（学号  姓名  科目名称  成绩）
SELECT s.studentNo,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result AS a
ON a.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON a.subjectNo=sub.subjectno
WHERE subjectname='数据库结构-1'


#查询一门科目《数据库-1》的所有考试结果（学号  姓名  科目名称  成绩）对成绩进行排序
SELECT s.studentNo,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result AS a
ON a.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON a.subjectNo=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC,studenton DESC#asc默认为升序,不写也为asc；desc为降序,若成绩相同则可加多排序条件

#常见错误：order  by studentresult,studenton desc 理解为:按学生成绩升序，又按学号降序


#分页必须会用到（用户体验，网络传输，查询压力）
#查询一门科目《数据库-1》的所有考试结果（学号  姓名  科目名称  成绩）进行降序
#每页显示5条信息记录
SELECT s.studentNo,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result AS a
ON a.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON a.subjectNo=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC
LIMIT 0,5;#等同于 limit 5 offset
 0;
#limit 0,5; #0为从哪条开始显示，0为起始行，5为显示几条，第一页
#limit 5,5; #第二页
#LIMIT 10,5;#第四页
#LIMIT 15,5;#第五页
#limit (pageno-1)*pagesize,pagesize    pageno:为第几页；pagesize：为要查的行数(当前页码-1)*页容量，也页容量

#查询《JAVA第一学年》课程成绩前10名且分数大于80的学生信息（学号、姓名、课程名、分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studenton =r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
WHERE subjectname='java第一学年' AND studentresult>80
ORDER BY studentResult DESC
LIMIT 0,10;

#子查询
#课本练习
#一、
#查询李斯文的birthday
SELECT birthday FROM student WHERE studentName='李斯文';//1993-7-23

#查询比李斯文年龄小的信息
SELECT * FROM student WHERE birthday >(
	SELECT birthday FROM student WHERE studentName='李斯文'
);


#二、
#查询课程编号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java';

#查询学生学号
SELECT studentNo FROM result WHERE `subjectNo`=? AND `studentResult`=60;

#查询考试成绩刚好等于60分的学生
SELECT * FROM student WHERE studentNo =?;

#最终
SELECT * FROM student WHERE studentNo IN
(
	SELECT studentNo FROM result WHERE `subjectNo`=
	(
		SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'
	)
	 AND `studentResult`=60

 );

#三、
#查询课程编号
SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java';//1

#查询最近的考试日期
SELECT MAX(examDate) FROM result WHERE subjectNo=1;//2017-6-17

#查询学生编号
SELECT studentNo FROM result WHERE examDate='2017-6-17';//10004

#查询学生信息
SELECT studentNo,studentName FROM student WHERE studentNo='10004';

#最终
SELECT studentNo,studentName FROM student WHERE studentNo=(
	SELECT studentNo FROM result WHERE examDate=(
		SELECT MAX(examDate) FROM result WHERE subjectNo=(
			SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'
		)
	)
);

#视频练习
#查询《Java OOP》的所有考试结果（学号、科目编号、成绩）并按成绩降序排列
SELECT stu.studentNo,r.subjectNo,studentResult
FROM student AS stu
INNER JOIN result AS r
ON stu.studentNo=r.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Java OOP'
ORDER BY studentResult DESC

#方法二：使用子查询
#查找课程编号
SELECT subjectNo FROM `subject` WHERE subjectName='Java OOP';
#查找学号
SELECT studentNo FROM result WHERE subjectNo=3;
#查找学号、科目编号、成绩
SELECT studentNo,subjectNo,studentResult FROM result WHERE
studentNo IN ('10003','10004','10005','10007');
#综合子查询
SELECT studentNo,subjectNo,studentResult FROM result WHERE
studentNo IN 
(
	SELECT studentNo FROM result WHERE subjectNo=
	(
		SELECT subjectNo FROM `subject` WHERE subjectName='Java OOP'
	)
)ORDER BY studentResult DESC;
	

#查询课程为HTML且分数不小于80分的学号与姓名
#方法一：使用连接查询
SELECT r.studentNo,studentName
FROM student AS stu
INNER JOIN result AS r
ON stu.studentNo=r.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='HTML' AND studentResult>80

#方法二：使用子查询与连接查询（将需求拆分、细化）
SELECT stu.studentNo,studentName 
FROM student AS stu
INNER JOIN result AS r
ON stu.studentNo=r.studentNo
WHERE studentResult>80
AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='HTML');

#方法三：使用子查询（查询的结果不只有一个值时使用IN）
SELECT studentNo,studentName FROM student WHERE studentNo IN(10002,10006);#李斯文，肖梅

SELECT studentNo FROM result WHERE studentResult>=80 AND subjectNo=2;#10002,10006

SELECT subjectNo FROM `subject` WHERE subjectName='HTML';#2

#综合方法四：分布写简单SQL语句，然后将其嵌套
SELECT studentNo,studentName FROM student WHERE studentNo IN(
	SELECT studentNo FROM result WHERE studentResult>=80 AND subjectNo=(
		SELECT subjectNo FROM `subject` WHERE subjectName='HTML'
	)
);


#查询《Logic Java》的前5名学生的成绩信息：学号，姓名，分数
#查询学号，姓名，分数
SELECT stu.studentNo,stu.studentName,studentResult
FROM student AS stu
INNER JOIN result AS r
ON stu.studentNo=r.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Logic Java'
ORDER BY studentResult DESC
LIMIT 0,5;

#数学函数
SELECT ABS(-8);#绝对值
SELECT CEILING(9.3);#大于等于我的最小的整数
SELECT FLOOR(8.9);#小于等于我的最大的整数
SELECT RAND();#返回0-1之间的随机数
SELECT RAND(10);#以某个数作为种子，返回重复随机数
SELECT SIGN(0);#符号函数，正数返回1，负数返回-1，零返回0

#字符串函数
SELECT CHAR_LENGTH('好好学习');#返回字符串中包含的字符串
SELECT CONCAT('改','革','春','风','吹','满','地');#合并字符串
SELECT INSERT('改革春风吹满地',1,7,'中国人民真争气');##替换字符串，从某个位置开始，替换某个长度
SELECT INSERT('改革春风吹满地',10,7,'中国人民真争气');#如果起始位置超过类字符串长度，则返回原字符串
SELECT LOWER('I LOVE YOU');#大写转小写
SELECT UPPER('I love you');#小写转大写
SELECT LEFT('这个世界太疯狂',3);#从左边截取几个
SELECT RIGHT('这个世界太疯狂',3);#从右边截取几个
SELECT REPLACE('世界太疯狂,疯狂','疯狂','美好');##替换，把‘疯狂‘替换成’美好‘
SELECT SUBSTR('五十六个民族',1,3);##从哪个位置开始截取，截取多长？

#日期和时间函数
#获取当前日期
SELECT CURRENT_DATE;
SELECT CURDATE();
#获取当前的日期和时间
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();
#分别获取日期中的某部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#系统信息函数
SELECT VERSION();#获取系统版本号
SELECT USER();#获取使用者

#查询姓李的同学
SELECT studentName FROM student 
WHERE studentName LIKE '李%';
#查询姓李的同学，替换成姓杨的同学
SELECT REPLACE (studentName,'李','杨')AS 新名字 FROM student 
WHERE studentName LIKE '李%';
#聚合函数
SELECT COUNT(studentName) FROM student;#非空值的统计数
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;

SELECT SUM(studentResult)AS 综合 FROM result;
SELECT AVG(studentResult)AS 平均分 FROM result;
SELECT MAX(studentResult)AS 最高分 FROM result;
SELECT MIN(studentResult)AS 最低分 FROM result;

#查询不同课程的平均分，最高分，最低分
#前提：根据不同课程进行分组
SELECT subjectName,AVG(studentResult)AS 平均分,MAX(studentResult)AS 最高分,
MIN(studentResult)AS 最低分 
FROM result AS r INNER JOIN `subject`AS sub
ON r.subjectNo=sub.subjectNo
GROUP BY r.subjectNo
HAVING 平均分>64
ORDER BY 平均分 DESC
LIMIT 0,1;

第五章、第六章、第七章总结

第五章

一、备份

图片为：在doc命令下的数据备份

1、mysqldump备份的语法：

​ mysqldump -h（主机名） -u（用户名） -p（密码） 数据库名[table1 table2 table3]>path/filename.sql(预存文件目录)

2、备份数据库：schooldb

c:\WINDOWS\system32> mysqldump -proot -uyxc schooldb>c:/yangxichang/schooldb.sql

3、备份数据库中特定的表：student表和score表

c:\WINDOWS\system32> mysqldump -proot -uyxc schooldb student score>c:/yangxichang/schooldb.sql

4、备份数据库时少写大括号的常见错误：误以为c:/yangxichang/schooldb.sql为表名

c:\WINDOWS\system32> mysqldump -proot -uyxc schooldb student score c:/yangxichang/schooldb.sql

5、在DOC命令下输入：mysqldump --help|more可查看mysqldump的可选参数，如图：

c:\WINDOWS\system32> mysqldump --help|more

6、DOC命令下跳过某些数据再进行备份:c:\WINDOWS\system32> mysqldump -proot -uyxc --skip-add-drop-table schooldb student score>c:/yangxichang/schooldb.sql

7、DOC命令下输入：c:\WINDOWS\system32> mysqldump -proot -uyxc --skip-add-drop-table schooldb student score>c:/yangxichang/schooldb.sql（insert into value省略了列名，在恢复备份时不好进行对比）

DOC命令下输入：c:\WINDOWS\system32> mysqldump -proot -uyxc --skip-add-drop-table -c schooldb student score>c:/yangxichang/schooldb.sql（insert into value添加列名）

二、恢复

1、使用source命令恢复
进入mysql下使用：mysql -uroot -pyxc
a、mysql>use +数据库名
b、mysql>source +脚本路径

2、使用mysql命令恢复
在DOC下面使用：c:\WINDOWS\system32> mysql -uroot -pyxc + 数据库名（要恢复到的数据库名，要先存在）<脚本路径

3、使用SQLyog工具来备份和恢复

a、备份：

注意：

若在使用SQLyog工具来备份时，写入文件选项中“包括USE database语句”和“包括CREATE database语句”两者都打勾时，备份出去的文件在恢复时则会出现问题，比如打勾USE database的时，USE 的是school database，而在恢复database的时候并不是在school这个数据库下恢复，而是在yxc 数据库恢复时即使恢复备份成功，在yxc 数据库下也不存在恢复的数据（打勾的选项看清楚，别影响恢复）

b、恢复：

4、使用SQL命令备份与恢复

#使用SQL命令备份与恢复
#例：（1）将school数据库studen表中的学号、姓名两列备份出去
#使用哪个数据库
USE school;
#注意：备份出去的文件不能提前存在，INTO（放到） OUTFILE（外面的文件）
SELECT pid,categoryName INTO OUTFILE 'c:/yangxichang/category.sql' FROM category;

#例：（2）将备份出去的数据恢复到test数据库的stutab表中来
USE test;
CREATE TABLE stutab(
	id INT(4),#类型要与备份出去的类型相同
	sname VARCHAR(20)
)
#把在c:/yangxichang/category.sql路径下的文件加载出来，放到stutab表的id和sname这两列中
LOAD DATA INFILE 'c:/yangxichang/category.sql' INTO TABLE stutab(id,sname);

SELECT * FROM stutab;

#注意：备份出去的文件不能提前存在INTO（放到） OUTFILE（外面的文件）
SELECT pid,categoryName INTO OUTFILE ‘c:/yangxichang/category.sql’ FROM category;

三、事务

1、什么是事务：

​ 事务时一种机制，一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要都执行，要么都不执行。因此事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务时作为最小的控制单元来使用的，特别使用于多用户同时操作的数据库系统。

2、事务的四个属性

a、原子性：事务是一个完整的操作。事务的各元素是不可分的。

b、一致性：当事务完成时，数据必须处于一致状态。

c、隔离性：对数据进行修改的所有并发事务时彼隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

d、持久性：一个事务成功完成之后，它对于数据库的改变是永久性的。一旦事务被提交，事务的效果会被永久地保留在数据库中

3、如何执行事务

​ 默认设置下，每条SQL语句就是一个事务，即执行SQL语句后自动提交。

四、视图

1、什么是视图

​ 视图是一种查看数据库中一个或多个表中数据的放法。视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。视图充当着查询中的表筛选器的角色。定义视图的查询可以基于一个或多个表，也可以基于其他视图、当前数据库或其他数据库。

2、如何创建和使用视图

#（1）使用SQL语句创建视图，语法如下：
CREATE VIEW 视图名
AS
[SELECT 语句]；
#注意：在SQL语句命名规范中，视图一般以view_xxx或v_xxx的样式来命名
	#在创建视图之前，如果在数据库中已存在同名视图，需要先删除再创建
#（2）使用SQL语句删除视图
DROP VIEW IF EXISTS +视图名;
#例如：
DROP VIEW IF EXISTS `view_student`;
CREATE VIEW `view_student`
AS
SELECT `studentNo`,`studentName`,`phone`
FROM `student`;
#（3）使用SQL语句查看视图语句
SELECT 字段1，字段2......FROM view_name;

#通过view来查看所有视图：
USE information_schema;
SELECT*FROM views\G;
    

五、索引

1、什么是索引

​ 在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需的数据，相当于书本的目录部分；

根据索引的存储类型，可以将索引分为B-树索引（BTREE）和哈希索引（HASH）。InnoDB和MyISAM存储引擎支持B-树索引。

​ 索引的作用是通过使用索引，大大提高数据库的检索速度，改善数据库性能。

2、索引分类：

a、普通索引 b、唯一索引 c、主键索引 d、复合索引 e、全文索引 f、空间索引

因为索引自身也需要维护，并占用一定的资源，可以按照下列标准选择建立索引的列

a、经常用作查询选择条件的列。

b、经常排序、分组的列

c、经常用作连接的列（主键/外键）

不要使用下面的列创建索引：

a、仅包含几个不同值的列

b、表中仅包含几行。为小型表创建索引可能不太实用，因为在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

#索引分类：（1）主键索引（primary key）（2）唯一索引（unique） （3）常规索引（index）（4）全文索引（fulltext）
#添加索引方式一：创建表，声明列属性时添加上
CREATE TABLE test(
	id INT(4) PRIMARY KEY,
	testno VARCHAR(10) UNIQUE,
	a VARCHAR(29),
	b VARCHAR(20),
	c TEXT,
	INDEX `index_a`(a,b),#取名为`index_a`
	FULLTEXT(c)
)ENGINE=MYISAM;#因为全文索引只能用于MYISAM类型的数据表

#添加索引方式二：创建表，将所有列都声明完后，再添加索引
CREATE TABLE test1(
	id INT(4),
	testno VARCHAR(10) ,
	a VARCHAR(29),
	b VARCHAR(20),
	c TEXT,
	PRIMARY KEY(id),
	UNIQUE KEY(testno),
	INDEX `index_a`(a,b),
	FULLTEXT(c)
)ENGINE=MYISAM;


#添加索引方式三：先创建表，创建表完毕后，修改表添加索引
CREATE TABLE test2(
	id INT(4),
	testno VARCHAR(10),
	a VARCHAR(29),
	b VARCHAR(20),
	c TEXT
)ENGINE=MYISAM;

ALTER TABLE test2 ADD PRIMARY KEY(id);
ALTER TABLE test2 ADD UNIQUE KEY(testno);
ALTER TABLE test2 ADD INDEX(a,b);
ALTER TABLE test2 ADD FULLTEXT(c);

EXPLAIN SELECT * FROM category WHERE categoryid='5';#explain查询当前查找列是否用了索引

#全文索引的用法
#错误用法：
SELECT * FROM category 
ALTER TABLE category ENGINE=MYISAM;
ALTER TABLE category ADD FULLTEXT(categoryName);

EXPLAIN SELECT * FROM category WHERE categoryName LIKE'美%';

#正确用法：条件：需要查找的数据不能超过全文的50%，超过则全文索引无效
EXPLAIN SELECT * FROM category WHERE MATCH(categoryName) AGAINST('java');

#显示索引信息
SHOW INDEX FROM test2;

#删除索引
DROP INDEX testno ON test2;
ALTER TABLE test2 DROP INDEX a;
ALTER TABLE test2 DROP PRIMARY KEY;#删除主键

第六章

1、什么事数据库设计：

数据库设计就是将应用中涉及的数据实体及这些数据实体之间的关系，，进行规划和结构化的过程。

2.数据库设计的重要性：

数据库中创建的数据结构的种类，以及在数据实体之间建立的关系是决定数据库系统效率的重要因素。糟糕的数据库设计表现为：a、效率底低下 b、更新和检索数据时会出现许多问题

而良好的数据库设计表现在三个方面：a、效率高 b、便于进一步扩展 c、使应用程序的开发变得更容易

3、数据库设计的步骤：

项目开发需要经过需求分析、概要设计、详细设计、代码编写、运行测试和部署上线这几个阶段

a、需求分析阶段：分析客户的业务和数据处理需求

b、概要设计阶段：绘制数据库的E-R图，用于在项目团队内部，设计人员和客户之间进行沟通，确认需求信息的正确性和完整性

c、详细设计阶段：将E-R图转换为多张表，进行逻辑设计确认各表的主外键，并应用数据库设计的三大范式进行审核。经项目组讨论确定后，还需根据项目的技术实现、团队开发能力及项目的成本预算，选择具体的数据库（如MYSQL或Oracle）进行物理实现。

3、进行数据库的系统分析步骤：

a、收集信息：创建数据库之前，必须充分理解数据库需要完成的任务和功能。如数据库需存储哪些信息，实现哪些功能。

b、标识实体：必须标识数据库要管理的关键对象或实体，实体一般是名词，一个实体只描述一件事情，不能重复出现含义相同的实体

c、标识每个实体需要存储的详细信息：将数据库中的主要实体标识为表的候选实体以后，就要标识每个实体存储的详细信息，也成为该实体的属性，这些属性将组成表中的列。

d、标识实体之间的关系：关系型数据库能够关联数据库中各个项目的相关信息。不同类型的信息可以单独存储，若需要，数据库引擎还可以根据需要将数据组合起来。

4、映射关系：

a、一对多：一般来说，一对多关系是一个表中的主键对应另外一个表中可重复字段，主键的值是不能重复的，而关联的字段是可以重复的，就会存在一个值对应一个值活着一个值对应多个值两种可能，即一对多。

b、多对多：要表示多对多关系，除类将多对多关系中的两个实体各自转换为表外，一般还会创建第三个表，称为连接表，他将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。因此，第三个表用来记录关系的每个匹配项或实例。

5、规范设计的三大范式：

a、第一范式：要求每列必须是最小的原子单元，不能再细分。

b、第二范式：在第一范式的基础上更进一层，要求每列都必须和主键相关，不相关的列放入别的表中，即要求一个表只描述一件事情

c、第三范式：在第二范式的基础上更进一层，要求表中各列必须和主键直接相关，不能间接相关。

第七章

银行ATM存取款机系统

1、创建普通用户

语法：create user ‘username’@‘host’[IDENTIFIED BY [PASSWORD] ‘password’];

其中：

username：表示创建的用户名

host：表示指定用户登录的主机名，如果只是本地用户可使用"localhost"，如果该用户可登陆任何远程主机，可使用通配符"%"

PASSWORD:表示使用哈希值设置密码，为可选项。

password:表示用户登录时使用的明文密码。

##例：创建本地用户yangxichang ，密码为666888
create user'yangxichang'@ 'localhost' identified by '666888';
##创建本地用户xichang 不用密码
create user'xichang'@ 'localhost' ；

2、执行grant语句创建用户并授权

语法：(自我理解)

grant+赋予权限 +on+ 哪个数据库.表名+to+哪个用户（‘yangxichang’@‘localhost’）+by identified PASSWORD ‘password’;

语法：（课本标准版）

GRANT priv_type ON databasename.tablename TO ‘username’@‘host’ [IDENTIFIED BY [PASSWORD]‘password’]

[WITH GRANT OPTION];

其中：

priv_type：表示设置的用户操作权限，如果授予所有权限可使用ALL。MYSQL中的权限有很多，常用的数据库或表操作权限如下：

a、CREATE和DROP权限，可以创建数据库和表，或删除已有数据库和表

b、INSERT、DELETE、SELECT和UPDATE权限，允许在一个数据现有的表上实施增、删、查、改操作

c、ALTER权限，可以用ALTER TABLE来更改表的结构和重新命名表。

databasename.tablename：表示所创建用户账号的权限范围，即只能在指定数据库和表上使用此权限，如果给所有数据库和表授权，则可使用 * . * ;

WITH GRANT OPTION：表示对新建立的用户赋予GRANT权限，可选。

##例：创建名为yangxichang ，密码为666888的本地用户账号，并给该用户赋予myschool 数据库中student表的增加数据和查询数据权限：
GRANT INSERT,SELECT ON myschool.student TO 'yangxichang'@ 'localhost' IDENTIFIED BY PASSWORD('666888');

3、使用mysqladmin命令修改ROOT账户密码

语法：

mysqladmin -u username -p password (需要空格) “newpassword”;

其中：newpassword为新密码，使用双引号（“”）括起来

例如：将root密码修改为1234，在DOS窗口中执行为：
mysqladmin -uroot -p yxc  "1234";

4、使用SET命令修改用户密码：

用户登录MYSQL服务器后，可使用SET命令修改当前用户密码，语法：

SET PASSWORD [FOR ‘username’@‘host’]=PASSWORD(“newpassword”);

其中：PASSWORD()函数用于对密码加密，"newpassword"是设置的新密码。如果修改非当前登陆用户的密码，则需要用FOR关键字指定要修改的帐户名。

#例：当登陆用户为root时，使用SET命令将root密码修改为"0000"，将teacher账户密码修改为”8888“：
#修改当前用户为root的密码：
SET PASSWORD =PASSWORD("0000");
#修改本地用户但非当前用户teacher的密码：
SET PASSWORD FOR 'teacher'@'localhost'=PASSWORD('8888');

5、删除普通用户

在mysql数据库中，使用DROP UDER语句删除用户，语法：

DROP USER ‘username1’@‘host’ [,‘username2’@‘host’…];

该语句可删除一个或多个MYSQL账户。

#前提需有删除的权限
#例：删除本地用户student用户：
DROP USER 'student'@'localhost' ;

#需创建一个新的用户
DROP DATABASE IF EXISTS bankdb;
CREATE DATABASE bankdb ;

USE bankdb;

DROP TABLE IF EXISTS userInfo;
CREATE TABLE userInfo(
	customerID INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT'客户编号',
	customerName VARCHAR(10) NOT NULL COMMENT'开户名',
	PID CHAR(18) UNIQUE KEY NOT NULL COMMENT'身份证号码',
	telephone CHAR(11) NOT NULL COMMENT'联系电话',
	address VARCHAR(255) NULL COMMENT'地址'
)COMMENT='顾客信息';

DROP TABLE IF EXISTS deposit;
CREATE TABLE deposit(
	savingID INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT'类型编号',
	savingName VARCHAR(20) NOT NULL COMMENT'类型名称',
	descrip VARCHAR(255) NULL COMMENT'描述'
)COMMENT='存款类型';

DROP TABLE IF EXISTS cardInfo;
CREATE TABLE cardInfo(
	cardID CHAR(16) PRIMARY KEY COMMENT'卡号',
	curId VARCHAR(10) DEFAULT 'RMB' COMMENT'币种',
	savingID INT(4) NOT NULL COMMENT'存款类型编号',#外键
	openDate TIMESTAMP DEFAULT NOW() COMMENT'开户日期',
	openMoney DECIMAL(18,2) DEFAULT 1 COMMENT'开户金额',
	balance DECIMAL(18,2) DEFAULT 1 COMMENT'开户余额',
	`password` CHAR(6) DEFAULT '888888' COMMENT'账户密码',
	IsreportLoss BIT DEFAULT 0 COMMENT'是否挂失',
	customerID INT(4) NOT NULL COMMENT'顾客编号'#外键
)COMMENT='银行卡信息';

DROP TABLE IF EXISTS tradeInfo;
CREATE TABLE tradeInfo(
	transDate TIMESTAMP DEFAULT NOW() COMMENT'交易日期',
	cardID CHAR(16) NOT NULL COMMENT'卡号',#外键
	transType CHAR(10) NOT NULL COMMENT'交易类型',
	transMoney DECIMAL(18,2)NOT NULL COMMENT'交易金额',
	remark TEXT NULL COMMENT'备注'

)COMMENT='交易信息';

ALTER TABLE cardInfo ADD CONSTRAINT fk_cardInfo_userInfo_customerID FOREIGN KEY(customerID)
	REFERENCES userInfo(customerID);
ALTER TABLE cardInfo ADD CONSTRAINT fk_cardInfo_deposit_savingID FOREIGN KEY(savingID)
	REFERENCES deposit(savingID);
ALTER TABLE tradeInfo ADD CONSTRAINT fk_tradeInfo_cardInfo_cardID FOREIGN KEY(cardID)
	REFERENCES cardInfo(cardID);



#存款类型
INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息');
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年');
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年');
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年');
INSERT INTO deposit (savingName) VALUES ('定活两便');
INSERT INTO deposit (savingName) VALUES ('通知');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年');
INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息');
SELECT * FROM deposit;


INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
	VALUES(DEFAULT,'张三','123456789012345678','010-6789897','北京海淀');

INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
	VALUES(DEFAULT,'李四','133456789012345678','075-6789897','广州天河');

INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
	VALUES(DEFAULT,'王五','145456789012345678','000-6789897','深圳坂田');

INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
	VALUES(DEFAULT,'丁一','543216789012345678','010-6879897','广州海珠区');
	
INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
	VALUES(DEFAULT,'丁六','123459876012345678','010-6789897','惠州淡水');

SELECT * FROM userInfo;

SELECT * FROM cardInfo;

INSERT INTO cardInfo(cardID,curId,savingID,openDate,openMoney,balance,`password`,IsreportLoss,customerID)
	VALUES('1010357612345678',DEFAULT,'2',DEFAULT,DEFAULT,DEFAULT,DEFAULT,1);


INSERT INTO tradeInfo(transDate,cardID,transType,transMoney) VALUES('张三','123456789012345678','支取','900');