day05_MySQL学习笔记_02

五、数据的完整性

　　　　作用：保证用户输入的数据保存到数据库中是正确的。

　　　　确保数据的完整性 = 在创建表时给表中添加约束

　　　　完整性的分类：

　　　　> 实体完整性

　　　　> 域完整性

　　　　> 引用完整性

　　1、实体完整性(行级约束)

　　　　实体：即表中的一行(一条记录)代表一个实体(entity)。

　　　　实体完整性的作用：标识每一行数据不重复。

　　　　如何保证数据的完整性呢？答：创建表时给表添加约束。

　　　　约束类型： 

　　　　　　主键约束（primary key）  

　　　　　　唯一约束(unique)     

　　　　　　自动增长列约束(auto_increment)

　　　　1.1 主键约束(primary key)

　　　　注：每个表中至少要有一个主键。 主键用于唯一地标识表中的每一条记录，可以定义一列或多列为主键。 

　　　　特点：主键的数据唯一，且不能为null。

 

　　示例：

　　　　CREATE DATABASE day05;

　　　　USE day05;

 

　　　　第一种添加方式：一个一个的添加

　　　　CREATE TABLE student(

　　　　　　id int primary key,

　　　　　　name varchar(50)

　　　　);

 

　　　　第二种添加方式：此种方式优势在于，可以创建联合主键。

　　　　CREATE TABLE student(

　　　　　　id int,

　　　　　　name varchar(50),

　　　　　　primary key(id)

　　　　);

　　　　创建联合主键

　　　　CREATE TABLE student(

　　　　　　id int,

　　　　　　name varchar(50),

　　　　　　primary key(id,name)

　　　　);

 

　　　　第三种添加方式：

　　　　CREATE TABLE student(

　　　　　　id int,

　　　　　　name varchar(50)

　　　　);

　　　　ALTER TABLE student ADD CONSTRAINT pk_id PRIMARY KEY(id); 或者   

　　　　ALTER TABLE student ADD PRIMARY KEY(id);

　　　　CONSTRAINT(constraint:约束)

　　　　1.2 唯一约束(unique)

　　　　　　　　特点：唯一的数据不能重复，但可以为null。

　　　　　　　　但是理论上不能有多个null。因为多个null不也是数据相同了吗！

　　　　　　　　注意：MySQL在唯一约束下可以允许有多个null值，Oracle、SQL Server 在唯一约束下不可以有多个null值。

　　　　　　示例：

　　　　　　CREATE TABLE student(

　　　　　　　　id int primary key,

　　　　　　　　name varchar(50) unique

　　　　　　);

　　　　1.3 自动增长列约束(auto_increment)

   　　　　给主键添加自动增长的数值，列只能是整数类型，但是如果删除数据之前增长的序号，后面再添加数据的时候序号不会重新开始，而是会接着被删除的那一列的序号开始。

　　　　　　SQL Server数据库软件(identity:身份、特性)

　　　　　　Oracle数据库软件(sequence:序列)

　　　　　　MySQL数据库软件(auto_increment:自动递增)

 　　  示例：

　　　　CREATE TABLE student(

　　　　　　id int primary key auto_increment,

　　　　　　name varchar(50)

　　　　);

　　　　INSERT INTO student(name) values('tom'); -- 不用写id了，id会自增

　　　　INSERT INTO student(name) values('bruce'); -- 不用写id了，id会自增

　　2、域完整性(列级约束)

　　　　域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较。

　　　　域代表当前单元格。

　　　　域完整性约束：    

　　　　　　数据类型约束       

　　　　　　非空约束(not null)     

　　　　　　默认值约束(default)

　　　　　　check约束(MysSQL不支持check检查约束，例如：check(sex='男' or sex='女'); Oracle中支持)

　　　　2.1 数据类型约束(数值类型、日期类型、字符串类型)

　　　　2.2 非空约束(not null)

　　　　CREATE TABLE student(

　　　　　　id int pirmary key,

　　　　　　name varchar(50) not null,

　　　　　　sex varchar(10)

　　　　);

　　　　INSERT INTO student values(1,'null',男);

　　　　2.3 默认值约束(default)

　　　　CREATE TABLE student(

　　　　　　id int pirmary key,

　　　　　　name varchar(50) not null,

　　　　　　sex varchar(10) default '男'

　　　　);

　　　　-- 批量插入(MySQL中的方式，别的数据库软件不好使哦)

　　　　INSERT INTO student1 values(1,'jerry','女');

　　　　INSERT INTO student1 values(2,'tom',default); -- sex插入的是默认值

　　　　INSERT INTO student1 values(3,'bruce',null); -- sex插入的是null值

　　3、引用完整性(参照完整性)

　　　　要有外键必须先有主键，外键关联/引用/参照主键，主键和外键的数据类型必须一致。

　　　　外键约束：foreign key

 

　　　　例：

　　　　　　-- 学生表(主表)

　　　　　　CREATE TABLE student(

       　　　　　　stuid VARCHAR(10) primary key,

       　　　　　　stuname VARCHAR(50) not null

　　　　　　);

 

　　　　　　第一种添加外键约束的方式：在创建表格的时候同时添加外键约束。

　　　　　　-- 分数表(次表/子表)

　　　　　　CREATE TABLE score(

       　　　　　　stuid VARCHAR(10), --外键列的数据类型一定要与主键列的数据类型一致

       　　　　　　score INT,

       　　　　　　courseid INT,

       　　　　　　CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES student(stuid)

　　　　　　);

　　　　　　CONSTRAINT(constraint:约束)     FOREIGN KEY(foreign key:外键)     REFERENCES(references:引用/参照/关联)

 

　　　　　　第二种添加外键约束的方式：在表格创建时没有添加外键约束，之后通过修改表格添加外键约束。

　　　　　　-- 分数表(次表/子表)

　　　　　　CREATE TABLE score(

       　　　　　　stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致

       　　　　　　score INT,

       　　　　　　courseid INT,

　　　　　　);

　　　　　　ALTER TABLE score ADD CONSTRAINT fk_student_score_stuid FOREIGN KEY(stuid) REFERENCES stu(stuid);

 

　　　　　　select * from student;

　　　　　　insert into student values('1001','张三丰');

　　　　　　insert into student values('1002','张无忌');

　　　　　　insert into student values('1003','王尼玛');

　　　　　　insert into student values('1004','王老五');

 

　　　　　　select * from score;

　　　　　　insert into score values('1001','98',1);

　　　　　　insert into score values('1002','95',1);

　　　　　　insert into score values('1002','67',2);

　　　　　　insert into score values('1003','83',2);

　　　　　　insert into score values('1003','57',3);

   　　　　　　

 

　　　　SQL中 PK、UK、DF、CK、FK 的意思：

　　　　--主键约束 PK

　　　　　　　　在表外修改：alter table xxx add constraint PK_字段 primary key(字段);

　　　　　　　　在表中修改：constraint PK_字段 primary key(字段),

　　　　　　　　在表中修改：字段 字段类型 primary key,

　　　　--唯一约束 UK

　　　　　　　　在表外修改：alter table xxx add constraint UK_字段 unique key(字段);

　　　　　　　　在表中修改：constraint UK_字段 unique key(字段),

　　　　　　　　在表中修改：字段 字段类型 unique,

　　　　--默认约束 DF

　　　　　　　　在表外修改：alter table xxx add constraint DF_字段 default('默认值') for 字段;

　　　　　　　　在表中修改：constraint DF_字段 default('默认值') for 字段,

　　　　　　　　在表中修改：字段 字段类型 default('默认值'),

　　　　--检查约束 CK

　　　　　　　　在表外修改：alter table xxx add constraint CK_字段 check(约束。如：len(字段)>1);

　　　　　　　　在表中修改：constraint CK_字段 check(约束。如：len(字段)>1),

　　　　　　　　在表中修改：字段 字段类型 check(约束。如：len(字段)>1),

　　　　--外键约束 FK

　　　　　　　　在表外修改：alter table xxx add constraint FK_主表_子表_主表主键字段 foreignkey(子表外键字段) references 主表(主表主键字段);

　　　　　　　　在表中修改：constraint FK_主表_子表_主表主键字段 foreign key(子表外键字段) references 主表(主表主键字段),

　　　　　　　　在表中修改：字段 字段类型 foreign key(子表外键字段) references 主表(主表主键字段),

　　4、表与表之间的关系

　　　　一对一：

　　　　　　例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。

　　　　　　人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。

　　　　　　设计从表可以有两种方案：

　　　　　　　　方式1：在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；即：字段 字段类型 unique,

　　　　　　　　方式2：给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

　　　　　　示例：用方式2

　　　　　　create table QQ(

              　　qqid int primary key,

             　　 password varchar(50)

　　　　　　);

　　　　　　create table QQDetail(

             　　 qqid int primary key,

              　　name varchar(50),

              　　address varchar(200)

　　　　　　);

　　　　　　alter table QQDetail add constraint fk_QQ_QQDetail foreign key(qqid) references QQ(qqid); 或者

　　　　　　alter table QQ add constraint fk_QQ_QQDetail foreign key(qqid) references QQDetail(qqid);

　　　　　　注意：虽然是一对一，但是维护关系不一样，那么主从表关系也不一样。(也就是说仍然有主从表的关系)

　　　　　　

　　　　一对多(多对一)：

　　　　　　最为常见的就是一对多！一对多和多对一，这是从哪个角度去看或者说以谁为参照物。

　　　　　　-- 学生表(主表)

　　　　　　CREATE TABLE student(

       　　　　　　stuid VARCHAR(10) primary key,

      　　　　 　　stuname VARCHAR(50) not null

　　　　　　);

　　　　　　第一种添加外键约束的方式：在创建表格的时候同时添加外键约束。

　　　　　　-- 分数表(次表/子表)

　　　　　　CREATE TABLE score(

       　　　　　　stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致

      　　　　 　　score INT,

      　　　　 　　courseid INT,

       　　　　　　CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES student(stuid)

　　　　　　);

　　　　　　CONSTRAINT(constraint:约束)     FOREIGN KEY(foreign key:外键)     REFERENCES(references:引用/参照/关联)

　　　　　　第二种添加外键约束的方式：在表格创建时没有添加外键约束，之后通过修改表格添加外键约束。

　　　　　　-- 分数表(次表/子表)

　　　　　　CREATE TABLE score(

       　　　　　　stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致

       　　　　　　score INT,

       　　　　　　courseid INT,

　　　　　　);

　　　　　　ALTER TABLE score ADD CONSTRAINT fk_student_score_stuid FOREIGN KEY(stuid) REFERENCES stu(stuid);

　　　　　　

　　　　　　

　　　　多对多：

　　　　　　例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。

　　　　　　例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

　　　　　　create table teacher(

              　　tid int primary key,

              　　tname varchar(20)

　　　　　　);

　　　　　　create table stu(

              　　sid int primary key,

              　　sname varchar(50)

　　　　　　);

 　　　　　 --中间表

　　　　　　create table tea_stu_rel(

              　　tid int,

              　　sid int

　　　　　　);

　　　　　　alter table tea_stu_rel add constraint fk_teacher_rel foreign key(tid) references teacher(tid);

　　　　　　alter table tea_stu_rel add constraint fk_stu_rel foreign key(sid) references stu(sid);

　　　　　　

　　　　　　

六、多表查询（重要）

　　多表查询有如下几种：

1. 1. 1. 合并结果集查询(UNION、UNION ALL)
      2. 连接查询
             内连接查询 [INNER] JOIN ON
             外连接查询 OUTER JOIN ON
                 左外连接查询 LEFT [OUTER] JOIN
                 右外连接查询 RIGHT [OUTER] JOIN
                 全外连接查询(MySQL不支持) FULL JOIN
             自然连接查询 NATURAL JOIN
         
      3. 子查询
      4. 自连接查询

　　1、合并结果集查询(UNION、UNION ALL)

　　　　作用：合并结果集就是把两个select语句的查询结果合并到一起。

　　　　合并结果集有两种方式：

• • • UNION：去除重复记录，      例如：SELECT * FROM t1 UNION SELECT * FROM t2;
    • UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2;

　　　　要求：被合并的两个结果：列数、列类型必须相同。

　　　　　　

　　　　　　-- 联合查询

　　　　　　CREATE TABLE A(

　　　　　　　　　　NAME VARCHAR(10),

　　　　　　　　　　score INT

　　　　　　);

　　　　　　CREATE TABLE B(

　　　　　　　　　　NAME VARCHAR(10),

　　　　　　　　　　score INT

　　　　　　);

　　　　　　-- 批量插入

　　　　　　INSERT INTO A VALUES('a',10),('b',20),('c',30); 

　　　　　　INSERT INTO B VALUES('a',10),('b',20),('d',40); 

　　　　　　--合并结果集查询

　　　　　　SELECT * FROM A 

　　　　　　UNION 

　　　　　　SELECT * FROM B;

　　　　　　SELECT * FROM A 
　　　　　　UNION ALL
　　　　　　SELECT * FROM B;

　　2、连接查询(非常重要)

　　　　　　连接查询就是求出多个表的乘积，

　　　　　　　　例如t1连接t2，那么查询出的结果就是t1*t2。

　　　　　　

　　　　连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

　　　　那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢？

　　　　当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

　　　　你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。

　　　　也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。

　　　　

　　　　使用主外键关系作为条件来去除无用信息

　　　　　　SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;

　　　　

　　　　上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

　　　　　　SELECT emp.ename, emp.sal, emp.com, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno;

　　　　

　　　　还可以为表指定别名，然后在引用列时使用别名即可。

　　　　　　SELECT e.ename, e.sal, e.com, d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;

　　　　2.1　内连接查询 [INNER] JOIN ON

　　　　上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言的内连接查询。

　　　　　　SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno; -- INNER 可以省略。MySQL 默认连接方式为内连接。

　　　　　　SELECT s.stuid, s.stuname, s.score FROM student s, score c WHERE s.stuid = c.stuid AND score>70; -- 99查询法(老的查询方法)

　　　　　　SELECT s.stuid, s.stuname, s.score FROM student s INNER JOIN score c ON s.stuid = c.stuid WHERE score>70; -- 新的标准查询法

　　　　　　INNER可以省略。

　　　　特别注意：ON相当于WHERE，一般多用于主外键条件关联。

　　　　         不是主外键条件也可以啊！说白了，ON就是筛选的条件。

　　　　　　内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录。

　　　　　　

　　　　　　其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

　　　　2.2　外连接查询(左外连接查询、右外连接查询) [OUTER] JOIN ON

　　　　外连接的特点：查询出的结果存在不满足条件的可能。

 

　　　　左外连接查询：

　　　　　　SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno; -- OUTER可以省略。以表emp为主。

　　　　左外连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

　　　　这么说你可能不太明白，我们还是用上面的例子来说明。

　　　　其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。

　　　　但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

　　　　

　　　　右外连接查询

　　　　　　SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno; -- OUTER可以省略。以表dept为主。

　　　　右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

　　　　例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

　　　　

　　　　连接查询心得：

　　　　　　连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。

　　　　　　通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。

　　　　　　这个条件大多数情况下都是使用主外键关系去除(一般使用内连接查询)。

　　　　　　两张表的连接查询一般会有一个主外键关系(没有关系的话，你查个鬼啊！)，三张表的连接查询就一般会有两个主外键关系，

　　　　　　所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。

　　　　　　如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

　　　　特别注意：我自己测试过，两张表的主外键关系可以alter修改表的主次表关系，使这两张表有了主外键关系；

　　　　　　　　　两种表也可以不有主外键关系，只要他们对应的字段和字段类型相同就行。

　　　　多张表查询

　　　　法一：内连接查询(99查询法)：

　　　　　　SELECT * FROM student s, score c, course cc WHERE s.stuid = c.stuid AND c.courseid = cc.coureseid;

　　　　　　SELECT s.stuid, s.stuname, c.score, cc.cname FROM student s, score c, course cc WHERE s.stuid = c.stuid AND c.courseid = cc.coureseid;

　　　　法二：新的内连接查询

　　　　　　SELECT * FROM student s JOIN score c ON s.stui = c.stuid JOIN cource cc ON c.courseid = cc.coureseid;

　　　　小结：

　　　　　　合并查询：union、unionall

　　　　　　连接查询：

　　　　　　　　内连接查询：[inner] join in

　　　　　　　　左外连接查询：left [outer] join in

　　　　　　　　右外连接查询：right [outer] join in

　　　　　　多个表查询：n表连接查询，至少有n-1个关系条件。

　　　　2.3、自然连接查询(NATURAL JOIN)

　　　　大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。

　　　　而自然连接无需你去给出主外键等式，它会自动找到这一等式：两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

　　　　当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

　　　　　　SELECT * FROM emp NATURAL JOIN dept; -- 没写条件，默认内连接查询。

　　　　　　SELECT * FROM emp NATURAL LEFT JOIN dept; -- 默认左外连接

　　　　　　SELECT * FROM emp NATURAL RIGHT JOIN dept;  -- 默认右外连接

　　3、子查询(非常重要)

  　　一个select语句中包含另一个完整的select语句。

  　　子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

　　　　　　子查询出现的位置：

• • • where后，作为主句的条件来用。
    • from后，作表。

　　　　　　当子查询出现在where后作为条件时，还可以使用如下关键字：

• • • any
    • all

　　　　　　子查询结果集的形式：

• • • 单行单列（用于条件）
    • 单行多列（用于条件）
    • 多行单列（用于条件）
    • 多行多列（用于表）

　　　　练习1：查询工资高于JONES的员工。

　　　　　　分析：

　　　　　　　　查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

　　　　　　第一步：查询JONES的工资

　　　　　　　　SELECT sal FROM emp WHERE ename='JONES';

　　　　　　第二步：查询高于甘宁工资的员工

　　　　　　　　SELECT * FROM emp WHERE sal>(第一步);

　　　　　　结果：

　　　　　　　　SELECT * FROM emp WHERE sal>(

　　　　　　　　SELECT sal FROM emp WHERE ename='JONES');

　　　　练习2：查询与SCOTT在同一个部门的员工。

　　　　　　　　SELECT *FROMemp WHERE depton=(

　　　　　　　　SELECT depton FROM emp WHERE ename ='SCOTT');

　　　　　　　　SELECT * FROM emp WHERE depton=(20); -- 两句等价

• • • • 子查询结果集作为条件
      • 子查询结果集形式为单行单列

　　　　练习3：查询工资高于30号部门所有人的员工信息。

　　　　　　分析：

　　　　　　法一：

　　　　　　　　SELECT * FROM emp WHERE sal>(

　　　　　　　　SELECT MAX(sal) FROM emp WHERE deptno=30);

　　　　　　法二：查询条件：查询工资高于30号部门所有人的工资，其中查询30号部门所有人工资是子查询。高于所有需要使用all关键字。

　　　　　　第一步：查询30号部门所有人的工资(多行单列)

　　　　　　　　SELECT sal FROM emp WHERE deptno=30;

　　　　　　第二步：查询高于部门编号为30号的部门所有人的工资的员工信息

　　　　　　　　SELECT * FROM emp WHERE sal > ALL(第一步);

　　　　　　结果：

　　　　　　　　SELECT * FROM emp WHERE sal > ALL(

　　　　　　　　SELECT sal FROM emp WHERE deptno=30)

• • • • 子查询结果集作为条件
      • 子查询结果集形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

　　　　练习4：查询工作和工资与MARTIN（马丁）完全相同的员工信息。

　　　　　　分析：

　　　　　　　　查询条件：工作和工资与MARTIN完全相同，这是子查询。

　　　　　　第一步：查询出MARTIN的工作和工资(单行多列)

　　　　　　　　SELECT job,sal FROM emp WHERE ename='MARTIN';

　　　　　　第二步：查询出与MARTIN工作和工资相同的人

　　　　　　　　SELECT * FROM emp WHERE (job,sal) IN(第一步);

　　　　　　结果：

　　　　　　　　SELECT * FROM emp WHERE (job,sal) IN(

　　　　　　　　SELECT job,sal FROM emp WHERE ename='MARTIN');

　　　　练习5：查询有2个以上直接下属的员工信息。(即如果mgr中的数据有出现两次以上一样的，说明该编号对应的人有两个以上的直接下属)

　　　　　　　　SELECT * FROM emp WHERE empno IN(

　　　　　　　　SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

• • • • 子查询结果集作为条件
      • 子查询结果集形式为单行多列

　　　　练习6：查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

　　　　　　分析：（多表查询，无需子查询）

　　　　　　　　查询列：员工名称、员工工资、部门名称、部门地址

　　　　　　　　查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）

　　　　　　　　条件：员工编号为7788

　　　　　　第一步：去除多表，只查一张表，这里去除部门表，只查员工表

　　　　　　　　SELECT ename, sal FROM emp e WHERE empno=7788;

　　　　　　第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积

　　　　　　　　SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno AND empno =7788; -- 不用子查询

　　　　　　 -- 用子查询（很鸡肋，意义不大）

　　　　　　第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。

　　　　　　第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

　　　　　　　　SELECT dname,loc,deptno FROM dept;

　　　　　　第四步：替换第二步中的dept

　　　　　　　　SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname, loc, deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788;

　　　　　　　　SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT * FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788;

• • • • 子查询作为表
      • 子查询形式为多行多列

　　　　　　小结：表dept是一张表的表名，表示一张表，
     　　　　　　 dept AS d(dept d),给表dept起了个新名字d，则d也表示一张表，
      　　　　　　dept 等价于 select * from dept。

　　4、自连接查询

     　　　　自己连接自己，起别名(即把自己看成两张表，为了区分，所以每个自己要起一个名字)

　　　　　 求7369员工的经理的编号和姓名

　　　　　　　　SELECT ename,empno FROM emp WHERE empno = (

　　　　　　　　SELECT mgr FROMemp WHERE empno =7369);

　　　　　　求7369员工编号和姓名以及该员工的经理编号和姓名

　　　　　　　　SELECT e1.empno, e1.ename, e2.empno, e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno =7369;

　　　　　　练习：求各个部门薪水最高的员工所有信息

　　　　　　普通版本(会有问题)

　　　　　　　　SELECT * FROM emp WHERE sal IN(

　　　　　　　　SELECTMAX(sal) FROM emp GROUP BY deptno);

　　　　　　改进版本

　　　　　　　　SELECT e1.* FROM emp e1,

　　　　　　　　(SELECT MAX(sal) maxsal, deptno d FROM emp GROUP BY deptno) e2  --部门最高工资和部分号一起组成的表

　　　　　　　　WHERE e1.deptno = e2.d AND e1.sal = e2.maxsal;

七、MySQL中的函数

　　　　-- 时间、日期相关函数

　　　　　　SELECT ADDTIME('19:52:00','01:01:01'); -- 20:53:01

　　　　　　SELECT CURRENT_DATE(); -- 2017-03-09
　　　　　　SELECT CURRENT_TIME(); -- 20:05:44
　　　　　　SELECT CURRENT_TIMESTAMP(); -- 2017-03-09 20:05:44
　　　　　　SELECT TIME(NOW()); -- 20:07:16
　　　　　　SELECT YEAR(NOW()); -- 2017
　　　　　　SELECT NOW(); -- 2017-03-09 20:08:10

　　　　　　SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); -- 2017-04-10 20:09:45
　　　　　　SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH); -- 2017-04-09 20:09:45
　　　　　　SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR); -- 2016-04-09 20:09:45

　　　　-- 字符串相关函数

　　　　　　SELECT CHARSET('tom'); -- utf8  查看字符集
　　　　　　SELECT CONCAT('aaa','bbb'); -- aaabbb  字符串拼接

　　　　　　SELECT CONCAT(ename,job) FORM emp;

　　　　　　SELECT INSTR('admin','d');  -- 2  相当于indexof
　　　　　　SELECT INSTR('admin','f'); -- 0

　　　　　　SELECT LEFT('admin',2); -- ad  从字符串左边起截取2个字符
　　　　　　SELECT RIGHT('admin',2); -- in  从字符串右边起截取2个字符

　　　　　　SELECT REPLACE('black','ack','ue'); -- blue  替换

　　　　　　SELECT STRCMP('a','b');  -- -1  (a-b=-1)  strcmp逐字符比较，返回ASCII的差值，但是值只有+1、-1、0
　　　　　　SELECT STRCMP('ab','ab'); -- -1  

　　　　　　SELECT CONCAT('#',TRIM(' abc '),'#'),CONCAT('#',LTRIM(' abc '),'#'),CONCAT('#',RTRIM(' abc '),'#'); -- #abc#  #abc #  # abc#

　　　　-- 数学相关函数

　　　　　　SELECT ABS(-5); -- 5

　　　　　　SELECT BIN(10); -- 1010 

　　　　　　SELECT FLOOR(12.34); -- 12  向下取整

　　　　　　SELECT FORMAT(100.999,2); -- 101.00  格式化，保留小数位数

　　　　　　SELECT RAND(); -- [0,1) 产生随机数，每次都不一样，没有种子

　　　　　　SELECT RAND(10); -- 产生随机数，每次都一样，有种子

八、MySQL数据库的备份与恢复

　　1、生成SQL脚本 导出数据

　　　　在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本.

　　　　但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句，所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

　　　　　　mysqldump –u 用户名 –p密码 数据库名>生成的脚本文件路径    （注意：-p密码不需要加空格）

　　　　　　

　　　　现在可以在c盘（d盘或者e盘）下找到mydb1.sql文件了！

　　　　注意：mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

　　2、执行SQL脚本 恢复数据

　　　　前提：必须先创建数据库名

 

　　　　执行SQL脚本需要先登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！

　　　　执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL脚本来操作数据库！

　　　　大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

　　　　　　source C:mydb1.sql

　　　　　　

　　注意：在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！

　　例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

 

　　还可以通过下面的方式来执行脚本文件：

　　　　mysql -u root -p123 mydb1<e:mydb1.sql

　　　　mysql –u 用户名 –p密码 数据库<要执行脚本文件路径

　　　　

　　这种方式无需登录mysql！

　　注意：在CMD下，命令不能加分号;=