Sql语言

一， Sql的分类

1，DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等； CREATE、 ALTER、DROP
2，DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数，据）； INSERT、 UPDATE、 DELETE
3，DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
4，DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。
SELECT

• 注意：sql语句以;结尾

二，DDL:操作数据库、表、列等

使用的关键字：CREATE、 ALTER、 DROP

1，操作数据库

1，创建

create database 数库库名;//创建数据库
Create database mysql02 character set gbk;//创建数据库并输入类型为gbk
Create database mysql03 character set gbk COLLATE gbk_chinese_ci;

2，查询
1，查看当前数据库服务器中的所有数据库

show databases;

2，查看前面创建的数据库的定义信息，，

Show  create  database  mysq01;	

3，删除前面创建的数据库

Drop database数库库名;

3，修改
1， 查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database mysql02 character set utf8;

4，删除

drop database mydb3;

5，其他

1，查看当前使用的数据库

select database();

2，切换数据库

use mydb2;

2，操作数据表

1，创建表

create table 表名(
				字段1 字段类型,
				字段2 字段类型,
				...
				字段n 字段类型
			);

2，常用数据类型
（1）int：整型
（2） double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
（3） char：固定长度字符串类型； char(10) 'abc ’
（4） varchar：可变长度字符串类型；varchar(10) ‘abc’
（5） text：字符串类型;
（6） blob：字节类型；
（7） date：日期类型，格式为：yyyy-MM-dd；
（8）time：时间类型，格式为：hh:mm:ss
（9） timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
（10）datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
3，创建一个员工表

显示当前数据库中的所有表

SHOW TABLES;

查看表的字段信息

DESC 表名;

在上面员工表的基本上增加一列。

ALTER TABLE 表名  ADD  字段名  字段类型;

修改列，使其长度为60。

ALTER TABLE 表名 MODIFY字段名varchar(60);

删除列,一次只能删一列。

ALTER TABLE 表名 DROP字段名;

表名修改。

RENAME TABLE 表名 TO 新表名;

查看表格的创建细节

SHOW CREATE TABLE 表;

修改表的字符集为gbk

ALTER TABLE 表名 CHARACTER SET gbk;

列名修改

ALTER TABLE 表名 CHANGE 列名 新列名 数据类型;

4，删除表

DROP TABLE 表名;

三，DML操作

查询表中的所有数据

SELECT * FROM 表名;

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
INSERT 、UPDATE、 DELETE

小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。‘tom’ ‘2015-09-04’
空值：null

1，插入操作 INSERT

语法：
INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);

注意：
列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。
练习 ：

create table emp(
id int,
name varchar(100),
gender varchar(10),
birthday date,
salary float(10,2),
entry_date date,
resume text
);

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,'wangwu','male','1995-5-10',10000,'2015-5-5','good boy');

批量插入：

  INSERT INTO emp VALUES
    (4,'zs','m','2015-09-01',10000,'2015-09-01',NULL),
    (5,'li','m','2015-09-01',10000,'2015-09-01',NULL),
    (6,'ww','m','2015-09-01',10000,'2015-09-01',NULL);

2，修改操作 UPDATE

语法：

UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。 WHERE 列名=值

练习：
（1）将所有员工薪水修改为5000元。

UPDATE emp SET salary=5000

（2）将姓名为’zs’的员工薪水修改为3000元。

UPDATE emp SET salary=3000 WHERE name=’ zhangsan’;

（3）将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。

 UPDATE emp SET salary=4000,gender='female' WHERE name='lisi';

（4）将wu的薪水在原有基础上增加1000元。

 UPDATE emp SET salary=salary+1000 WHERE gender='male';	

3 ，删除操作 DELETE

语法 ：

DELETE FROM 表名 【WHERE 列名=值】

练习 ：
（1）删除表中名称为’zs’的记录。

DELETE FROM emp WHERE name=‘zs’;

（2）删除表中所有记录。

DELETE FROM emp;

（3）使用truncate删除表中记录。

TRUNCATE TABLE emp;

注：
DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

四， DQL操作

DQL数据查询语言 （重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。

查询关键字：SELECT

语法：

 SELECT selection_list                   //要查询的列名称
  FROM table_list                        //要查询的表名称
  WHERE condition                        //行条件
  GROUP BY grouping_columns              //对结果分组
  HAVING condition                       //分组后的行条件
  ORDER BY sorting_columns              //对结果分组
  LIMIT offset_start, row_count         //结果限定

练习

1　基础查询
1.1　查询所有列

SELECT * FROM stu;

1.2　查询指定列

SELECT sid, sname, age FROM stu;

2　条件查询
2.1　条件查询介绍
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：


	=、!=、<>、<、<=、>、>=；
	BETWEEN…AND； 
	IN(set)；
	IS NULL； IS NOT NULL
	AND；
	OR；
	NOT；

2.2　查询性别为女，并且年龄小于50的记录

SELECT * FROM stu 
WHERE gender='female' AND age<50;

2.3　查询学号为S_1001，或者姓名为liSi的记录

SELECT * FROM stu 
WHERE sid ='S_1001' OR sname='liSi';

2.4　查询学号为S_1001，S_1002，S_1003的记录

SELECT * FROM stu 
WHERE sid IN ('S_1001','S_1002','S_1003');

2.5　查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM tab_student 
WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

2.6　查询年龄为null的记录

SELECT * FROM stu
WHERE age IS NULL;

2.7　查询年龄在20到40之间的学生记录

SELECT * 
FROM stu
WHERE age>=20 AND age<=40;

或者

SELECT * 
FROM stu 
WHERE age BETWEEN 20 AND 40;

2.8　查询性别非男的学生记录

SELECT * 
FROM stu
WHERE gender!='male';

或者

SELECT * 
FROM stu
WHERE gender<>'male';

或者

SELECT * 
FROM stu
WHERE NOT gender='male';

2.9　查询姓名不为null的学生记录

SELECT * 
FROM stu
WHERE sname IS NOT NULL;

或者

SELECT * 
FROM stu
WHERE NOT sname IS NULL;

3　模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
通配符:
_ 任意一个字符
%：任意0~n个字符
例如

'%张%'  //姓名中有“张”字的
 '张_'   //姓张且名字长度为两个字的

3.1　查询姓名由5个字母构成的学生记录

SELECT * 
FROM stu
WHERE sname LIKE '_____';

//模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。

3.2　查询姓名由5个字母构成，并且第5个字母为“i”的学生记录

SELECT * 
FROM stu
WHERE sname LIKE '____i';

3.3　查询姓名以“z”开头的学生记录

SELECT * 
FROM stu
WHERE sname LIKE 'z%';
//其中“%”匹配0~n个任何字母。

3.4　查询姓名中第2个字母为“i”的学生记录

SELECT * 
FROM stu
WHERE sname LIKE '_i%';

3.5　查询姓名中包含“a”字母的学生记录

SELECT * 
FROM stu
WHERE sname LIKE '%a%';

4 字段控制查询

4.1　去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

4.2　查看雇员的月薪与佣金之和
　　因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

4.3　给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0)  total FROM emp;

也可以给emp添加别名

SELECT SUM(e.sal+IFNULL(e.coom,0)) total FROM emp e;

SELECT e.* FROM emp e;
SELECT emp.empno,emp.job FROM emp;

这句话是有问题的：

SELECT emp.empno,a.job FROM emp a;    //取过别名后不能再用原表名。

5　排序 order by 列名 asc(默认) desc
5.1　查询所有学生记录，按年龄升序排序

SELECT *
FROM stu
ORDER BY sage ASC;

或者

SELECT *
FROM stu
ORDER BY sage;

5.2　查询所有学生记录，按年龄升序排序（空会排到前面）

SELECT *
FROM stu
ORDER BY age DESC;

5.3　查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp
ORDER BY sal DESC,empno ASC;

6　聚合函数 sum avg max min count
聚合函数是用来做纵向运算的函数：
 COUNT()：统计指定列不为NULL的记录行数；
 MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
 MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
 SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
 AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6.1　COUNT
当需要纵向统计时可以使用COUNT()。
（1） 查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

（2） 查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

（3）查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp
WHERE sal > 2500;

（4）统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

（5） 查询有佣金的人数，有领导的人数：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

6.2　SUM和AVG
当需要纵向求和时使用sum()函数。
（1）查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

（2）查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

（3） 查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

（4） 统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

6.3　MAX和MIN
（1）查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

7　分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部门来分组。
注：凡和聚合函数同时出现的列名，一定要写在group by 之后

7.1　分组查询
（1） 查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno

（2）查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

（3）查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*)
FROM emp
WHERE sal>1500
GROUP BY deptno;

7.2　HAVING子句
（1） 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
　　

注：
having与where的区别:

1，having是在分组后对数据进行过滤.不能单独使用。
where是在分组前对数据进行过滤

2，having后面可以使用聚合函数(统计函数)
where后面不可以使用聚合函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

**8　LIMIT **
LIMIT用来限定查询结果的起始行，以及总行数。

8.1　查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;
  //  注意，起始行从0开始，即第一行开始

8.2　查询10行记录，起始行从3开始

SELECT * FROM emp LIMIT 3, 10;

8.3　分页查询
如果一页记录为10条，希望查看第3页记录应该怎么查呢？
 第一页记录起始行为0，一共查询10行；
 第二页记录起始行为10，一共查询10行；
 第三页记录起始行为20，一共查询10行；

Int curPage     //当前页
Int pageSize    //每页共显示多少条数据
pageCont      //总页数
select * from emp limit （(curpage-1)*pagesize,pagesize）；

//例如，查询第三页的数据，则Int curPage=3

8.3　查询代码的书写顺序和执行顺序
查询语句书写顺序：select – from- where- group by- having- order by-limit
查询语句执行顺序：from - where -group by - having - select - order by-limit

五、数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：
实体完整性（参照完整性）
域完整性:
引用完整性:

1、实体完整性
实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto_increment)

1.1主键约束（primary key）
注：每个表中要有一个主键。
特点：数据唯一，且不能为null
例：
第一种添加方式：

CREATE TABLE student(
id int primary key,
name varchar(50)
);

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

CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);
CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid，stuid)
);

第三种添加方式：

CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student  ADD  PRIMARY  KEY (id
);

1.2唯一约束(unique)：
特点：数据不能重复。

   CREATE TABLE student(
    Id int primary key,
    Name varchar(50) unique
    );

1.3自动增长列(auto_increment)
sqlserver数据库 (identity) oracle数据库( sequence)

给主键添加自动增长的数值，列只能是整数类型

    
 create table( id int(10) auto_increment primary key) auto_increment=1

2、域完整性
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)
1.1 数据类型:（数值类型、日期类型、字符串类型）
1.2 非空约束：not null

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10)
);

INSERT INTO student values(1,’tom’,null);

1.3 默认值约束 default

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10) default ‘男’
);

insert into student1 values(1,'tom','女');
insert into student1 values(2,'jerry',default);

3、引用完整性（参照完整性）
外键约束：FOREIGN KEY
例：

CREATE TABLE student(
sid int pirmary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);

create table score(
	id int,
	score int,
	sid int , //外键列的数据类型一定要与主键的类型一致
	CONSTRAINT fk_score_sid foreign key (sid) references student(id)
);

第二种添加外键方式。

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

4、　表与表之间的关系
（1）一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：
 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。
（2） 一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！
（3）多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

六、多表查询（重要）

多表查询有如下几种：
 合并结果集；UNION 、 UNION ALL
 连接查询
 内连接 [INNER] JOIN ON
 外连接 OUTER JOIN ON
 左外连接 LEFT [OUTER] JOIN
 右外连接 RIGHT [OUTER] JOIN
 全外连接（MySQL不支持）FULL JOIN
 自然连接 NATURAL JOIN
 子查询
1　合并结果集
（1） 作用：合并结果集就是把两个select语句的查询结果合并到一起！
（2）合并结果集有两种方式：
 UNION：去除重复记录，例如：

SELECT * FROM t1 UNION SELECT * FROM t2；

 UNION ALL：不去除重复记录，例如：

SELECT * FROM t1 UNION ALL SELECT * FROM t2。

（3） 要求：被合并的两个结果：列数、列类型必须相同。
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表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。
（1）使用主外键关系做为条件来去除无用信息

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

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

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

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

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

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

SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：
其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

2.2　外连接（左连接、右连接）
外连接的特点：查询出的结果存在不满足条件的可能。
左连接：

SELECT * FROM emp e 
LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

2.3　右连接
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e 
RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
3　自然连接
大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：
 两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;

4　子查询（非常重要）
一个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同一个部门的员工。

 子查询作为条件
 子查询形式为单行单列

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')


SELECT * FROM emp WHERE job=(select job from emp where enmae='MARTIN')and sal=(select 
 sal from emp where enmae='MARTIN')

5、有2个以上直接下属的员工信息

SELECT * FROM emp WHERE empno IN(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

 子查询作为条件
 子查询形式为单行多列

5、查询员工编号为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

 子查询作为表
 子查询形式为多行多列
6、自连接:自己连接自己，起别名
求7369员工编号、姓名、经理编号和经理姓名

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

练习：
求各个部门薪水最高的员工所有信息
select e.* from emp e,
–部门最高工资

(select max(sal) maxsal,deptno from emp
group by deptno) a
where e.deptno = a.deptno
and e.sal =a.maxsal

七、视图
– 是由来自数据库中的一个或多个表或多个表的字段所组成的一个虚拟的表。

CREATE OR REPLACE VIEW v_name
AS
SELECT * FROM emp e;

八、索引（index）
用来快速定位查找对应记录的
数据库表中的字节段没有索引则查询时按照FTS（full table Scan）查
以高水位线进行全表扫描

索引倚靠rowid来实现快速定位一条记录
1.数据库默认创建索引
表的主键，唯一键会自动创建索引
自定义索引字段

	create index index_ename on emp(ename);

适合建立索引的情况
1.频繁查询的字段
2.经常作为查询条件的字段
3.数据集比较大，查询的结果小
4.连接查询的连接字段

不适合建立索引的情况；
1.使用不频繁的字段
2.经常更新的字段
3.数据集比较大,查询结果大

索引失效
1.索引字段使用函数,导致索引失效
2.索引字段进行运算
3.使用like关键字查询

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

九、MySQL中的函数
时间日期相关函数
ADDTIME (date2 ,time_interval ) 将time_interval加到date2
CURRENT_DATE ( ) 当前日期
CURRENT_TIME ( ) 当前时间
CURRENT_TIMESTAMP ( ) 当前时间戳
DATE (datetime ) 返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) 在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type ) 在date2上减去一个时间
DATEDIFF (date1 ,date2 ) 两个日期差
NOW ( ) 当前时间
YEAR|Month|Day(datetime ) 年月日
字符串相关函数
CHARSET(str) 返回字串字符集
CONCAT (string2 [,… ]) 连接字串
INSTR (string ,substring ) 返回substring在string中出现的位置,没有返回0
UCASE (string2 ) 转换成大写
LCASE (string2 ) 转换成小写
LEFT (string2 ,length ) 从string2中的左边起取length个字符
LENGTH (string ) string长度
REPLACE (str ,search_str ,replace_str ) 在str中用replace_str替换search_str
STRCMP (string1 ,string2 ) 逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) 从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 ) trim 去除前端空格或后端空格
数学相关函数
ABS (number2 ) 绝对值
BIN (decimal_number ) 十进制转二进制
CEILING (number2 ) 向上取整
CONV(number2,from_base,to_base) 进制转换
FLOOR (number2 ) 向下取整
FORMAT (number,decimal_places ) 保留小数位数
HEX (DecimalNumber ) 转十六进制
LEAST (number , number2 [,…]) 求最小值
MOD (numerator ,denominator ) 求余
RAND([seed]) RAND([seed])

十、MySQL数据库的备份与恢复
1　生成SQL脚本 导出数据
在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。
mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

现在可以在C盘下找到mydb1.sql文件了！
mysqldump -uroot -p123 mysql04> d:mysql04.sql
注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

2　执行SQL脚本 恢复数据
前提：必须先创建数据库名

执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！
执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！
SOURCE C:\mydb1.sql

注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

还可以通过下面的方式来执行脚本文件：
mysql -uroot -p123 mydb1<c:\mydb1.sql
mysql –u用户名 –p密码 数据库<要执行脚本文件路径

mysql -uroot -p123 stumanage<F:\stumanage.sql
这种方式无需登录mysql！

注意：在CMD下 命令不能加;