第二阶段： SQL数据库建库建表、CRUD操作案例

1、MySQL安装目录解释
bin:可执行文件。
include:放置了软件的.h头文件。
lib:放置了库文件
share:放置了字符集等信息
my-default.ini
这是配置文件：用来修改服务器的配置。一般修改完毕后要重新启动服务。
2、数据库的相关的概念
1、数据库管理系统
是由客户端和服务器端组成。数据存储在服务器，客户端是一个我们用来操作的用户界面而已。
2、数据库
数据库管理系统中包含了数据库。每个数据库包含有表、存储过程、视图、函数、触发器、事件。这些就是所谓的
数据库对象。用来管理、组织、存储数据的仓库。
3、表
表由1行1行的数据组成，列又有一个名字叫字段。它的结构是二维表。它是用来描述一个一个实体的，有时候实
体和实体之间有关系，这种关系我们也需要进行描述，它也是用二维表描述的。
3、数据库设计概念
1、域模型：一个对象用Java语言来描述。比如学生类
2、概念模型：用E-R图（实体关系图）来描述
3、物理模型：把E-R图转成数据库中的表。
设计数据库：很多时候不要有冗余数据
4、E-R图举例
1）员工 (编号、姓名、性别、年龄、部门的编号)
2）部门（部门的编号、部门的名称
说明：实体用矩形、实体的数学用椭圆，实体之间的关系有一对多，多对一、多对多、一对一
4、相关的命令操作
1、什么是SQL？
结构化查询语言。是1974年提出来的。它是关系数据库的标准语言。数据库分为关系数据库和非关系数据库。非
关系数据库典型的有Redis，它可以用来做缓存。
2、SQL的分类
1）DML语言：数据操作语言（数据操纵语句），比如插入操纵insert、删除操纵delete、修改操纵update。
2）DQL语言：数据查询语言，用来对数据库表中的数据进行查询。select
3）DDL语言：数据定义语言。建立数据库、删除数据库、建立表的结构、修改表结构等这就需要使用数据定义语
言。
4）DCL:数据控制语言,常常需要建立用户，用户有不同的角色和权限。所以我们要给用户去授权。
3、创建数据库
1）命令行：CREATE DATABASE [IF NOT EXISTS] k9506
2）手工操纵：单击创建
3）查询数据库列表：
show databases;
4）删除数据库
drop database k9506;
4、建立表
对于数据库来说，有一个很关键的问题就是要保证数据的完整性。所谓完整性，就i是数据的准确性和一致性。比
如学号要唯一，性别只能是男或者女，学生参加考试的课程必须是学校开设了的课程。员工必须属于某个部门。
1）主键:我们为了唯一的标识表中的记录，每条记录不一样，必须通过主键来保证。
2）外键：如果A表中的某个字段的取值是另外一个表B中的主键列表值中的一个。那么我们把A表叫做外键表，也
叫子表，刚才说的A中的那个字段就是外键。B表就是主键表。我们也会说A表参照B
create table if not exists 表名(
字段名1 数据类型 约束 [primary key],
字段名2 数据类型 约束,
…
对于外键的说明：
foregin key(外键字段名) references 主键表(主键)
5、修改表的结构
1、修改表名（了解）
alter table 原来的表名 rename [to] 新表名;
2、表结构中添加字段
alter table 表名 add 字段名 数据类型 约束;
3、修改字段的名称
alter table 表名 change 原字段名 新字段名 数据类型 ;
4、修改字段的类型
alter table 表名 modify 原字段名 新的数据类型 ;

CRUD操作

1、数据的插入操作
1)语法
insert into 表的名字(字段1，字段2,…,字段n) values(值1，值2,…,值n)
2）规则：
1)如果遇到列是auto_increment，可以把对应的值写null，也可以不写auto_increment的列，那么值也可以不
写。
2)有几个字段，就必须有几个值对应；个数要相等，顺序也要对应，数据类型要对应.
3）字段的值可以省略，要保证值的个数和表的字段的个数相同，要有对应关系。
4）默认的字段可以不写，如果写了在值列表中用default对应。
5）对应外键字段如果定义的时候没有写not null，那么插入的值的时候，可以为null
6）要现建立主键表，插入值，再到外键表中插入值，外键的值必须在主键中存在。
案例：在booktype表中插入数据
2、数据的修改操作
1）语法
update 表名 set 要修改的字段1=值1,字段2=值2，…，字段n=值n [where 条件表达式语句]
2）规则
1）如果没有where关键字和条件表达式语句，那么会修改表中的所有行
2）日期类型的修改，可以直接用字符串
UPDATE book SET price=price+10,publisher=‘中国少儿出版社’;
UPDATE book SET price=price+5 WHERE price<40;
3、数据的删除操作
1、语法
1）delete from tab_name [where 条件];//当不写where条件的时候会全部删除
2）truncate table tab_name;//删除表全部记录
两者的区别在于，上面的删除后，如果有自动增长的字段，继续添加的时候，自动增长的字段会从原来理论上的数
字+1;后面的truncate方式会从1开始。
2、规则
1)要删除数据的时候要先删除子表中和外键关联的数据，然后才能删除主键表中的数据
2)删除的时候一般要写where条件，否则就会删除全部数据。
4、简单查询
查询的语法
1）标准语法
select 字段1 [as 别名],字段2，…，字段n
from 表名 [as] 别名
where 条件语句
order by 排序的字段 asc|desc； //a’sc 升序 desc降序
2）模糊查询
通配符：_ 1个字符长度
%:任意长度
3、查询的案例
1）#查询tab_student表中的全部记录
#查询tab_student表中的全部记录#
SELECT * FROM tab_student;
2）#查询tab_student表中的全部记录,把字段用中文名表示
SELECT
s_number AS ‘学号’,
s_name AS ‘姓名’,
s_age ‘年龄’,
s_gender AS 性别
FROM tab_student
说明：字段的别名可以用as也可以不写as，别名可以用单引号也可以不用。
3）#查询性别为女，并且年龄小于50的记录
#查询性别为女，并且年龄小于50的记录#
SELECT * FROM tab_student
WHERE s_age<50
AND s_gender=‘female’;
说明：我们平时在写条件的时候，可以用如下的写法.<, >= ,<=, <>, is NULL ,is NOT NULL, and or not
4）#查询学号为S_1001，或者姓名为liSi的记录：#
SELECT * FROM tab_student
WHERE s_number=‘S_1001’
OR s_name=‘liSi’;
5）#查询学号为S_1001，或者姓名为liSi且性别为female的记录：#
SELECT * FROM tab_student
WHERE s_number=‘S_1001’
OR (s_name=‘liSi’ AND s_gender=‘female’);
6）#查询学号为S_1001，S_1002，S_1003的记录：#
第1种写法
SELECT * FROM tab_student
WHERE s_number
IN(‘S_1001’,‘S_1002’,‘S_1003’)第2种写法
SELECT * FROM tab_student
WHERE s_number=‘S_1001’
OR s_number=‘S_1002’
OR s_number=‘S_1003’;
7）#查询学号不是S_1001，S_1002，S_1003的记录：#
#查询学号不是S_1001，S_1002，S_1003的记录：#
SELECT * FROM tab_student
WHERE s_number
NOT IN(‘S_1001’,‘S_1002’,‘S_1003’);
SELECT * FROM tab_student
WHERE s_number<>‘S_1001’
AND s_number<>‘S_1002’
AND s_number<>‘S_1003’;
8）#查询年龄为null的记录：
#查询年龄为null的记录：#
SELECT * FROM tab_student
WHERE s_age IS NULL;
#下面的是错误的写法
SELECT * FROM tab_student
WHERE s_age=’’;
9）查询年龄不为null的学生记录
#查询年龄不为null的学生记录#
SELECT * FROM tab_student
WHERE s_age IS NOT NULL;
10）#查询年龄在20到40之间的学生记录：
SELECT * FROM tab_student
WHERE s_age BETWEEN 20 AND 40;
和下面的写法等价
SELECT * FROM tab_student
WHERE s_age>= 20 ANDs_age<=40;
11）#查询姓名由5个字母构成的学生记录：#
#查询姓名由5个字母构成的学生记录：#
SELECT * FROM tab_student
WHERE s_name LIKE ‘_____’;
12）#查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
#查询姓名由5个字母构成，并且第5个字母为“i”的学生记录#
SELECT * FROM tab_student
WHERE s_name LIKE ‘____i’;
13）#查询姓名以“z”开头的学生记i录：#
#查询姓名以“z”开头的学生记i录：#
SELECT * FROM tab_student
WHERE s_name LIKE ‘z%’;
14）#查询姓名中第2个字母为“i”的学生记录：#
#查询姓名中第2个字母为“i”的学生记录：#
SELECT * FROM tab_student
WHERE s_name LIKE ‘_i%’;
15)#查询姓名中包含“a”字母的学生记录：
#查询姓名中包含“a”字母的学生记录：#
SELECT * FROM tab_student
WHERE s_name LIKE ‘%a%’;
16)#查询所有学生记录，按年龄升序排序：
#查询所有学生记录，按年龄升序排序：
SELECT * FROM tab_student
WHERE s_age IS NOT NULL
ORDER BY s_age ASC
17)#查询所有学生记录，按年龄降序排序：
#查询所有学生记录，按年龄降序排序：#
SELECT * FROM tab_student
WHERE s_age IS NOT NULL
ORDER BY s_age DESC
1)#查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序：#
#查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序：#
SELECT * FROM emp
ORDER BY sal DESC,empno ASC;
2)#查询雇员信息和每个雇员的工资=月薪+奖金#
SELECT e.,IFNULL(sal,0)+IFNULL(comm,0) AS 工资 FROM emp e
3)#查询所有雇员，按月薪+佣金升序排序：#
#查询所有雇员，按月薪+佣金升序排序：#
SELECT ,IFNULL(sal,0)+IFNULL(comm,0) AS 工资 FROM emp
ORDER BY IFNULL(sal,0)+IFNULL(comm,0) ASC;
5、聚合函数
1)max()求最大值
2)min()求最小值
3)count()求记录的条数
4)sum()求和
5)avg()平均值
1)#查询emp表中记录数#
#查询emp表中记录数#
SELECT COUNT(1) FROM emp;
2)#查询emp表中有佣金的人数：
#查询emp表中有佣金的人数：#
SELECT COUNT() FROM emp
WHERE comm IS NOT NULL
3）#查询emp表中月薪大于2500的人数#
#查询emp表中月薪大于2500的人数#
SELECT COUNT() FROM emp
WHERE sal>2500;
4)#统计月薪与佣金之和大于2500元的人数：#
#统计月薪与佣金之和大于2500元的人数：#
SELECT COUNT() FROM emp
WHERE IFNULL(sal,0)+IFNULL(comm,0)>2500;
5)#查询有佣金的人数，以及有领导的人数
#查询有佣金的人数，以及有领导的人数：#
SELECT COUNT(comm) AS 佣金人数,COUNT(mgr) AS 领导人数 FROM emp
6)#查询所有雇员月薪和#
#查询所有雇员月薪和#
SELECT SUM(sal) FROM emp
7)#查询所有雇员月薪和，以及所有雇员佣金和：
#查询所有雇员月薪和，以及所有雇员佣金和：#
SELECT SUM(sal),SUM(IFNULL(comm,0)) AS 佣金和 FROM emp;
8)#查询所有雇员月薪+佣金和：
#查询所有雇员月薪+佣金和：#
SELECT SUM(IFNULL(comm,0)+sal) AS 佣金和 FROM emp;
9)#统计所有员工平均工资：#
#统计所有员工平均工资：#
SELECT AVG(sal) FROM emp;
10)#查询最高工资和低工资：#
SELECT MIN(sal) 最低工资,MAX(sal) 最高工资 FROM emp;
6、分组统计
1）分组统计的语法
select 字段1 [as 别名],字段2，…，字段n from 表名 [as] 别名
from 表名 as 别名
where 字句 //全表筛选
group by 分组字段
having 组内刷选字段
order by 排序字段
2）案例1：#查询每个部门的部门编号以及每个部门的人数#
SELECT deptno AS 部门编号,COUNT(empno) AS 部门人数 FROM emp
GROUP BY deptno;
3）#查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno,COUNT(empNo)
FROM emp
WHERE sal>1500
GROUP BY deptno;
4)#查询工资总和大于9000的部门编号以及工资和：#
#查询工资总和大于9000的部门编号以及工资和：#
SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal)>9000
5)#查询每个部门的部门编号，以及部门工资高于1500元的人数，如果人数大于2则显示#
#查询每个部门的部门编号，以及部门工资高于1500元的人数，如果人数大于2则显示#
SELECT deptno,COUNT(empNo)
FROM emp
WHERE sal>1500
GROUP BY deptno
HAVING COUNT(empno)>2;
7、连表查询
内连接、左外连接、右外连接、自连接
1、什么是连表查询？
如果根据业务的需要，发现要操作的表不是一张表，而是2个或者2个以上的表，这就是连表查询。比如要查询员
工的编号、姓名、入职时间、部门名称。很显然发现前面几个字段是emp表中的部分字段、后面的部门名称是dept
表中的部分字段。查询的结果集中的每行记录是两个表中字段进行的筛选。
2、笛卡尔集
如果有集合A={a,b}和集合B={1,2,3}，那么A和B的笛卡尔集是{(a,1),(a,2),(a,3),(b,1),{b,2},(b,3)};计算元组的个数是
23=6。
实际上进行连表查询，就是表和表先进行笛卡尔集，然后再对这个结果进行筛选。筛选的条件就是参照的主、外
键。 MySQL中2个表做笛卡尔集，它的行数是两个表的行数相乘，字段的个数为两个表的字段个数相加；就是把两
张表 合成一个大表。
#1999年 ANSI SQL规范 做笛卡尔集#
SELECT *
FROM emp,dept;
#2003年 ANSI SQL规范 做笛卡尔集#
SELECT e.,d.
FROM emp e
INNER JOIN dept d;
3、内连接查询
1）1999年的规范
select t1.星号,t2.星号,t3.星号
from tableName t1,
tableName t2
tableName t3
where t1.外键=t2.主键
and t2.外键=t3.主键
2) 2003年的规范
select select t1.星号,t2.星号,t3.星号
from tableName t1
inner join tableName t2
on t1.外键=t2.主键
inner join tableName t3
on t2.外键=t3.主键;
3）#查询员工的信息、部门的名称#
#查询员工的信息、部门的名称#
SELECT e.,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;
SELECT e.,d.dname
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;
4、左外连接
把内连接的inner换成left,左表是left前面的表，右表是left后面的表；筛选后规则如下：
坐标中的记录如果和右边连接上了，那么就出现在结果集中，如果连接不上，左边的内容照抄，右边的字段用null
填充。
5、右外连接
把内连接的inner换成right,左表是right前面的表，
6、自连接查询
自己的结果集和自己的另外一个结果集进行连接查询，诀窍是对于表取别名，两个别名不要相同。
SELECT e1.empno,e1.ename,e2.ename AS 上级名称,e2.empno 上级编号
FROM emp e1
INNER JOIN emp e2
ON e1.mgr=e2.empno
3、子查询
1、子查询也叫嵌套查询，在一个查询中嵌套了其他的查询，这就是子查询。
2、一般是在from或者是where 条件中放了另外一个查询。另外一个查询叫子查询，本查询叫主查询。执行的时候
先执行子查询，再执行主查询。因为主查询要依赖子查询的结果。
SELECT e.deptno,e.ename
FROM emp e
WHERE e.deptno=
(SELECT deptno
FROM dept
WHERE dname =‘SALES’)
5、分页查询
员工的工资按照从低到高排序，查询排序结果的前3条记录。
SELECT * FROM emp
ORDER BY sal ASC
LIMIT 0,3;
员工的工资按照从低到高排序，查询排序结果的第4条到第6条记录。
SELECT * FROM emp
ORDER BY sal ASC
LIMIT 3,3;
员工的工资按照从低到高排序，查询排序结果的第7条到第9条记录。
SELECT * FROM emp
ORDER BY sal ASC
LIMIT 6,3;
把员工的工资按照从低到高排序,每页显示3条，查询第1页。每页显示的条数pageSize=3.第几页叫
pageNo=1;
SELECT * FROM emp
ORDER BY sal ASC
LIMIT (pageNo-1)*pageSize,pageSize;