数据库进阶及查询联系

一、       编码

1、  查看MySQL数据库编码：SHOW VARIABLES LIKE ‘char%’

2、  编码解释：

1.     Character_set_client:MySQL将客户端传送的数据当成指定编码
2.     Character_set_results：MySQL发给客户端的指定类型。
3.      通过 set character_set_client/result=gbk方式修改编码方式，同时修改后只在当前窗口有效，再my.ini中修改可以保证一劳永逸。

3、  数据库备份与恢复

1.     备份：指将数据库转换成SQL脚本

1.       Mysqldump –u用户名 –p密码 数据库名>目录\xxx.sql

1.     恢复

1.       Mysqldump –u用户名 –p密码 数据库<脚本文件路径 该操作需要在数据库外执行
2.      Source 备份文件   改操作直接在数据库内部执行
3.     恢复的时候不能恢复数据库，只能恢复数据库内容，所以要保证数据库是存在的，如果不存在可以创建一个null的数据库

4、  约束：约束时添加在列上的，用来约束列

1.     主键约束（非null、唯一、被引用（外键就是用来引用主键））

1.       当表的某一列被指定为主键，该列就不能为null，同时不能有重复的出现。
2.      创建表

CREATE TABLE stu(

Sid CHAR(5) PRIMARY KEY,指定sid为主键，即为sid列添加主键约束

Sname VARCH(20)

)

或者

CREATE TABLE stu(

Sid CHAR(5)，

Sname VARCH(20)，

PRIMARY KEY(sid)

)

                            ALTER TABLE stu ADD PRIMARY KEY(sid)修改表时指定主键

                            ALTER TABLE stu DROP PRIMARY KEY;删除主键,不用指定主键名字

                     ALTER TABLE tableName

                     ADD 添加列

                     MODIFY 修改列名和列类型

                     CHANGE 修改列

                     DROP 删除

                     RENAME TO 重新命名

1.     主键自增长，主键通常会专门指定一个列，选择一个没有意义的作为主键，一般使用uuid作为主键。

CREATE TABLE stu(

Sid INT PRIMARY KEY AUTO_INCREMENT，必须时整数，可以指定也可以不指定，不指定的时候自增

Sname VARCH(20)

)

 

1.     非null约束，要求添加的列不能为null，在列后添加NOT NULL.
2.      唯一约束，表明该列不能设置唯一值，NOT NULL UNIQUE

 

二、       概念模型、对象模型和关系模型

1、  概念模型

1.     根据系统中状况抽象出实体，一般概念模型对应着Java中的列

2、  对象模型：java中的domian类，可以双向关联，引用的是一个对象而不是一个主键

3、  关系模型：数据库中的表，一对一、一对多、多对多。只能多方引用一方，只能是主键，而不能是一整行记录就。

三、       外键约束：CONSTRAINT fk_emp_dept FOREIGN KEY(主表列名) REFERENCES 子表（子表主键）

1、  外键必须是另一个表中的主键值。

2、  外键可以重复。

3、  外键可以为null。

4、  一张表中可以有多个外键。

四、       一对一关系：需要让其中一张表的主键既是主键又是外键。创建方法类似一对多外键方式。会自动检擦

 

五、       多对多关系两张表都是主表，新增一个关系表，关联表中uuid不是次序的

 

六、       多表查询，分为合并结果集、链接查询和子查询

1、  合并结果集，要求两表的表结构必须一致，列名显示第一个查询表的列名,UNICN去除重复行，UNICN ALL不去除重复行。

1.     SELECT * FROM ab UNICN [ALL] SELECT * FROM cd；要求ab和cd表的结构相同
2.     SELECT id FROM ab UNICN [ALL] SELECT id FROM cd ；此时不要求ab和cd表结构相同，只需要查询出来的列的结构相同即可合并。

2、  链接查询一次查询多张表，同时分为内连接、外连接和自然连接，内连接又可以分为左连接、有链接和全连接。 

1.     内连接查出所有表中都满足条件的记录

1.       方言：SELECT * FROM table1 别名1，table2 别名2 WHERE 别名1.xx=别名2.xx
2.      标准：SELECT * FROM table1 别名1 INNER JOIN table2 别名2 ON 别名1.xx=别名2.xx
3.     自然：SELECT * FROM table1 别名1 NATURE JOIN table2 别名2

1.     外连接

1.       左：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx   左表记录全部查出+右表满足条件的记录
2.      左自然：SELECT * FROM table1 别名1 NATURE LEFT OUTER JOIN table2 别名2 ON 别名1.xx=别名2.xx
3.     右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx   右表记录全部查出+左表满足条件的记录
4.     右自然：SELECT * FROM table1 别名1 NATURE RIGHT OUTER JOIN table2 别名2 ON 别名1.xx=别名2.xx
5.      全连接：可以使用UNION来完成连接。

3、  子查询子查询一般出现在from后作为表和where后面作为条件

1.     SELECT * FROM emp WHERE sal> [ALL\ANY](SELECT sal FROM emp WHERE job=’’)

七、       联系题

1、  查出至少有一个员工的部门，显示部门编号、部门名称、部门位置和部门人数

SELECT d.*,z.cnt

FROM dept d, (SELECT DEPTNO, COUNT(*) cnt FROM EMP GROUP BY deptno) z

WHERE d.deptno=z.deptno

2、  列出薪资不关于高的所有工资

 

3、  列出所有员工的姓名及其直接上级的姓名

SELECT e.ename 员工姓名, IFNULL(“e2.ename”,”boss”) 上级领导

FROM emp e1 LEFT OUTER JOIN emp e2

WHERE e1.mgr=e2.empno

4、  列出受雇日期遭遇直接上级的所有员工的编号姓名和部门名称

SELECT e.empno, e.ename, d.dname

FROM emp e1, emp e2, dept d

WHERE e1.hiredate< e2.hiredate AND e1.mgr=e2.empno AND e1.deptno=d.deptno

5、  列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门

SELECT *

FROM emp e RIGHT OUTER JOIN dept d

ON e.deptno=d.deptno

6、  列出所有文员的姓名和其部门名称，部门的人数

SELECT

FROM

WHERE

7、  列出最低薪资大于150000的各种工作及其从事此工作的员工人数

SELECT job COUNT(*)

FROM emp e

GROUP BY job

              HEAVING MIN(sal)>15000

8、  列出在销售部工作的员工姓名、假定不知道销售部门的编号

SELECT e.ename

FROM emp e, dept d

WHERE d.dname=”销售部” AND d.deptno=e.deptno

9、  列出薪资高于平均薪资的所有员工信息，所在部门名称和上级领导、工资等级

 

10、       列出与庞统从事相同工作的所有员工及部门名称

11、       列出薪资高于部门30工作的所有有员工的信息的员工姓名和薪资、部门名称

12、       列出每个部门的员工数量和平均工资

13、       查出年份、利润、年度增长比。