数据库Database：SQL Server、Access、MySQL、Oracle、DB2、NoSQL
1、MySQL简介
MySQL是开源的关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Oracle收购。
版本：社区版、商业版，体积小、速度快、成本低、开放源代码，中小型网站都使用MySQL数据库
2、MySQL安装
1）版本
MySQL5.5 5.6
安装过程中注意事项：
第一：修改字符集为utf8
第二：将bin目录添加到PATH变量中
第三：设置root用户密码
2)服务：MySQL
3)命令
登陆：mysql -u 用户名 -p密码 -h 服务器地址 -D 数据库名
使用第三方客户端连接MySQL数据库：SQLYog、Navicat
create database 数据库名;
show databases; 列出当前数据库服务器中所有数据库名
use 数据库; 切换到指定的数据库
show tables; 列出当前数据库中所有的表
select database(); 显示当前操作的数据库
select user(); 显示当前登陆的用户
注：mysql库是系统数据库，包含MySQL的相关系统信息，特别重要，不要删除
3、所用的表
导入数据：使用root用户登陆，切换到test数据库，
然后执行source e:/aaa/init.sql
查看表的结构：desc 表名
查看表中的数据：select * from 表名
emp 雇员表、员工表
empno 雇员编号 int
ename 雇员姓名 varchar
job 职位 varchar
mgr 雇员领导的编号 int
hiredate 雇佣日期 date
sal 工资 double
comm 奖金 int
deptno 部门编号 int
dept 部门表
deptno 部门编号 int
dname 部门名称 varchar
loc 部门地址 varchar
salgrade 工资等级表
grade 等级编号 int
losal 最低工资 int
hisal 最高工资 int
bonus 奖金表
4、SQL简介
Structured Query Language结构化查询语言
可以分为三个部分：
DML（Data Manipulation Language）数据操作语言：用于检索或修改数据
insert、delete、update、select
DDL（Data Definition Language）数据定义语句：用于定义数据的结构
create、alter、drop
DCL（Data Control Language）数据控制语句：用于定义数据库用户的权限
grant、revoke
5、查询操作
语法：
select *|列名1 as 别名1,列名2 别名2...
from 表名
where 分组前条件
group by 分组列
having 分组后条件
order by 排序列 asc|desc,排序列 asc|desc;
1）简单查询
select * from emp;
select ename,job,hiredate,sal from emp;
select ename 姓名,job 职位,hiredate 雇佣日期,sal 工资 from emp;
select ename as 姓 名,job as 职位,hiredate 雇佣日期,sal 工资 from emp;
select ename "姓 名",job 职位,hiredate 雇佣日期,sal 工资 from emp;
concat() 字符串拼接
select concat("姓名为",ename,"的雇员，编号为",empno,"，职位为",job) from emp;
+-*/四则运算
例：查询雇员姓名和年薪
select ename 雇员姓名,sal*12 年薪 from emp;
ifnull()
select ename 雇员姓名,(sal+ifnull(comm,0))*12 年薪 from emp;
distinct去除重复的列
例：查询公司所有的职位类别
select distinct job from emp;
2）限定查询
比较运算符>、<=、=、!=、<>
例：查询工资大于1500的雇员信息
select * from emp where sal>1500;
例：查询雇员编号不是7369的雇员信息
select * from emp where empno!=7369
例：找出姓名为smith的雇员信息
select * from emp where ename=smith;
注：MySQL不区分大小写，而Oracle是区分大小写
MySQL中字符串既可以使用单引号，也可以使用双引号
null、not null
例：查询可以领取奖金的雇员的姓名、入职时间、工资和奖金
select ename,hiredate,sal,comm from emp where comm is not null;
not、and、or
例：查询工资大于1500，并且可以获取奖金的雇员信息
select * from emp where sal>1500 and comm is not null;
例：查询工资小于3000，或者没有奖金的雇员信息
select * from emp where sal<1000 or comm is null;
between and
例：查询工资大于1500，并且小于3000的雇员编号、姓名、入职时间、工资及所在部门编号
select empno,ename,hiredate,sal,deptno from emp where sal>1500 and sal<3000;
select empno,ename,hiredate,sal,deptno from emp where sal between 1500 and 3000;
注：包含边界值
in、not in
例：查询雇员编号为7369、7788、7499的雇员姓名、入职时间、工资
select empno,ename,hiredate,sal from emp where empno=7369 or empno=7788 or empno=7499;
select empno,ename,hiredate,sal from emp where empno in (7369,7788,7499);
例：找出姓名为SMITH、ALLEN、KING的雇员信息
select * from emp where ename in ('SMITH','ALLEN','KING');
注：在使用字符串时只有起别名时不加引号，其他时候都要加引号
like模糊查询，通配符：%、_，%表示0到任意长度字符，_表示单个字符
例：查询姓名包含M的雇员信息(模糊查找)
select * from emp where ename like '%m%';
select * from emp where ename like 'm%';
select * from emp where ename like 'm____';
例：查询出1981年入职的雇员
select * from emp where hiredate like '1981%';
注：MySQL中日期格式：2016-12-04
3）排序
例：查询所有雇员信息，按工资由低到高排列，如果工资相同，则按入职时间降序排列
select * from emp order by sal,hiredate desc;
注：默认按升序排列
例：查询部门30的雇员信息，按年薪降序排列
select empno,ename,(sal+ifnull(comm,0))*12 income
from emp
where deptno=30
order by income desc;
6、多表查询
分类：内连接（等值连接、非等值连接、自身连接）、外连接
1）基本查询
select * from emp,dept;（笛卡尔积）
通过将多张表的关系字段进行比较，去掉笛卡尔积，多表查询时必然存在关联字段
select * from emp,dept where emp.deptno=dept.deptno;
例：查询雇员编号、姓名、工资、所在部门名称
select empno,ename,sal,dname
from emp,dept
where emp.deptno=dept.deptno;
例：查询雇员编号、姓名、工资、所在部门编号、所在部门名称
select empno,ename,sal,emp.deptno,dname
from emp,dept
where emp.deptno=dept.deptno;
为表起别名
select e.empno,e.ename,sal,e.deptno,dname
from emp as e,dept d
where e.deptno=d.deptno;
例：查询雇员姓名、雇员工资、领导姓名、领导工资（自身关联）
select e.ename 雇员姓名,e.sal 雇员工资,m.ename 领导姓名,m.sal 领导工资
from emp e,emp m
where e.mgr=m.empno;
例：查询雇员姓名、雇员工资、部门名称、领导姓名、领导工资
select e.ename,e.sal,d.dname,m.ename, m.sal
from emp e,dept d,emp m
where e.deptno=d.deptno and e.mgr=m.empno;
例：查询雇员姓名、雇员工资、部门名称、工资等级（非等值连接）
select ename,sal,dname,grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
例：查询雇员姓名、雇员工资、部门名称、雇员工资等级、领导姓名、领导工资、领导工资等级
select e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,sm.grade
from emp e,dept d,salgrade s,emp m,salgrade sm
where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno and m.sal between sm.losal and sm.hisal;
2）外连接
左外连接left outer join、右外连接right outer join
例：查询雇员姓名、雇员工资、领导姓名、领导工资（有的雇员没有领导）
select e.ename 雇员姓名,e.sal 雇员工资,m.ename 领导姓名,m.sal 领导工资
from emp e left join emp m on e.mgr=m.empno;
select e.ename 雇员姓名,e.sal 雇员工资,m.ename 领导姓名,m.sal 领导工资
from emp m right join emp e on e.mgr=m.empno;
例：查询部门编号、部门名称、所在位置以及该部门中雇员的姓名、工资、雇佣日期
select d.deptno,dname,loc,ename,sal,hiredate
from dept d left join emp e on d.deptno=e.deptno;
6、分组函数、分组统计
1）分组函数
count()
max()
min()
avg()
sum()
例：查询部门30的总人数
select count(empno) 总人数 from emp where deptno=30;
注：分组函数在统计时会自动忽略值为null的行
例：查询部门20的最高工资、最低工资、平均工资
select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资 from emp where deptno=20;
2）分组统计
例：求出每个部门的平均工资
select deptno 部门编号,avg(sal) 平均工资
from emp
group by deptno;
例：按部门分组，显示部门名称及每个部门的员工数
select d.dname,count(e.empno)
from dept d left join emp e on d.deptno=e.deptno
group by d.dname;
例：查询平均工资大于2000的部门
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
例：查询出非销售人员工作名称以及从事同一工作的雇员的月工资总和，并且要满足月工资总和大于5000，查询结果按月工资总和升序排列
select job,sum(sal) sum
from emp
where job!="SALESMAN"
group by job
having sum>5000
order by sum;
例：找出各部门平均工资的最大值
select max(tmp.avg) from (select avg(sal) avg from emp group by deptno) tmp;
7、子查询
1）概念
一个查询中嵌套着另一个查询，称为子查询
子查询必须放在()中，在执行时会先执行小括号中的子查询
子查询可以出现在任意位置
例：查询工资比7566工资高的雇员信息
select e1.*,e2.ename,e2.sal
from emp e1,emp e2
where e2.empno=7566 and e1.sal>e2.sal;
select * from emp where sal>(select sal from emp where empno=7566);
例：查询工资比部门20员工的工资高的雇员信息
select * from emp where sal>(select sal from emp where deptno=20);
注：将子查询和比较运算符联合使用时，必须保证子查询返回的值不能多于一个
例：查询雇员编号、姓名、所在部门名称
select empno,ename,dname from emp e,dept d where e.deptno=d.deptno;
select empno,ename,(select dname from dept where deptno=emp.deptno) from emp;
注：1）一般来说，多表联接查询都可以使用子查询替代，但有的子查询却不能使用多表联接迭代
2）子查询比较查询、方便，常作为增删改查的筛选条件，适合于操作单表的数据
3）多表联接更适合于查看多表的数据
2）子查询分类
单列子查询：返回单行单列，使用频率最高
多行子查询：返回多行单列
多列子查询：返回单行多列或多行多列
3）单列子查询
例：查询工资比7654高，同时又与7788从事相同工作的雇员
select * from emp where sal>(
select sal from emp where empno=7654
) and job=(
select job from emp where empno=7788
);
例：查询工资最低的雇员姓名、工作、工资
select ename,job,sal from emp where sal=(
select min(sal) from emp
);
例：查询工资高于公司平均工资的雇员
select * from emp where sal>(
select avg(sal) from emp
);
例：按部门分组，查询部门的编号、最低工资，要求最低工资大于等于部门30的最低工资
select deptno,min(sal)
from emp
group by deptno
having min(sal)>(
select min(sal) from emp where deptno=30
);
例：查询部门名称、部门员工数、部门平均工资、部门的最低收入雇员的姓名
select t.dname,t.count,t.avg,e.ename
from(
select dname,count(empno) count,avg(sal) avg,min(sal) min
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno
) t,emp e
where e.sal=t.min;
例：查询平均工资最低的工作及平均工资值
select job,avg(sal) from emp group by job
having avg(sal)=(
select min(t.avg) from (
select avg(sal) avg from emp group by job
) t
);
4）多行子查询
例：查询所在部门编号大于10的雇员信息
select * from emp where deptno>10;
IN关键字
select * from emp where deptno in (
select distinct deptno from emp where deptno>10
);
ALL关键字
>ALL：比最大值还大
<ALl：比最小值还小
例：查询工资比部门20员工的工资都高的雇员信息
select * from emp where sal>ALL(
select sal from emp where deptno=20
);
ANY关键字
>ANY：比最小值要大
<ANY：比最大值要小
=ANY：与in作用相同
5）多列子查询
例：查询工资和奖金与7788完全相同的雇员信息
select * from emp where (sal,ifnull(comm,0)) in (
select sal,ifnull(comm,0) from emp where empno=7788
);
select * from emp e,(
select sal,ifnull(comm,0) c from emp where empno=7788
) t
where e.sal=t.sal and ifnull(e.comm,0)=t.c;
8、更新操作（添加、修改、删除）
1)insert操作
语法：insert [into] 表名 (列1,列2...) values (值1,值2....);
insert into dept (deptno,dname,loc) values (50,'开发部','南京');
insert dept (deptno,dname) values (70,'清洁部');
insert into emp(empno,ename,hiredate,sal,deptno) values (9527,'唐伯虎','1990-3-15',666,50);
insert into dept values (80,'程序猿部','南京');
注：如果省略插入列的名称，则表示要按顺序插入所有的列
2)delete操作
语法：delete from 表名 where 条件
delete from dept where deptno=70;
delete from dept where dname='程序猿部';
delete from dept;
3)udpate操作
语法：update 表名 set 列1=值1,列2=值2... where 条件
update dept set loc='上海',dname='后勤部' where deptno=70;
update emp set comm=222,deptno=70,hiredate='1995-12-4' where ename='唐伯虎';
9、表和库的管理
1）数据类型
varchar(4)、char(4)、nvarchar(4)、nchar(4)
int
double
date 格式：2016-12-04或20161204
clob 存储文本大数据
blob 存储二进制大数据
2）创建表
语法：
create table 表名
(
列名1 数据类型 [特征],
列名2 数据类型 [default 默认值]
);
注：最后一列末尾不能添加逗号
create table student
(
sid int,
name varchar(20) unique,
sex varchar(6) default '男',
age int,
birthday date,
height double
);
insert into student (sid,name) values (1001,'tom');
3）删除表
语法：drop table 表名;
drop table student;
4）修改表
添加列：alter table 表名 add 列名 数据类型;
alter table student add weight double;
修改列类型：alter table 表名 modify 列名 数据类型;
alter table student modify name varchar(50);
修改列名：alter table 表名 change 原列名 新列名 数据类型;
alter table student change name sname varchar(50);
删除列：alter table 表名 drop 列名
alter table student drop weight;
修改表名：rename table 原表名 to 新表名;
rename table student to user;
5）创建库
语法：create database if not exists 数据库名;
-- 指定数据库使用的编码
create database if not exists account default charset utf8;
6）删除库
语法;drop database 数据库名;
drop database account;
10、约束constraint
约束是对表中数据的一种限制，保证数据的完整性
1）约束类型
主键约束primary key，唯一的标识，本身不能为空
唯一约束unique：不允许出现重复值
检查约束check：检查列的值是否符合条件（注：在MySQL中会忽略check约束）
非空约束not null：不允许为空
外键约束foreign key：表示两表之间的关联关系，主表(主键)、从表(外键)
注：创建表时需要先创建主表，再创建子表
2）添加约束
create table student
(
id int primary key,
name varchar(30) unique not null,
sex varchar(4) not null,
age int check (age between 1 and 100),
cid int,
constraint foreign key (cid) references class(cid)
);
create table class
(
cid int primary key auto_increment, -- 班级编号
cname varchar(20) not null
);
insert into class values (1,'wbs16061');
insert into class values (2,'wbs16062');
insert into class values (3,'wbs16063');
insert into class(cname) values ('wbs16064');
insert into class(cid) values (3);
insert into student values (1001,'tom','男',20,2);
insert into student values (1002,'jack','男',120,1);
insert into student values (1003,'alice','女',30,5);
删除表时应该先删除子表，再删除主表
drop table class;
drop table class cascade constraint;删除表时同时删除对应的外键约束（注：MySQL不支持）
auto_increment 设置主键自动增长，起始值从1开始，每次增加1
11、limit关键字
用法：select * from 表名 where 条件 limit 参数1,参数2
作用：限制查询返回的记录，参数1指定起始行，参数2指定返回的数量
注：索引从0开始，即第一行为0
例：查询前5个雇员信息
select * from emp limit 0,5;
select * from emp limit 4,8;
select * from emp limit 3; //相当于limit 0,3
用途：可以实现分页查找功能
练习
1、列出至少有4个员工的部门名称
select dname count(empno) from emp,dept where emp.deptno=dept.deptno
select dname,count(EMPNO) from EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO GROUP BY EMP.DEPTNO HAVING COUNT(EMPNO)>3;
2、列出薪金比"SMITH"多的所有员工
SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');
3、列出所有员工的姓名以及其直接上级的姓名
SELECT E.ENAME,M.ENAME FROM EMP E LEFT JOIN EMP M ON E.MGR=M.EMPNO;
4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
SELECT E.EMPNO,E.ENAME,D.DNAME FROM EMP E,EMP M,DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.MGR=M.EMPNO AND E.HIREDATE<M.HIREDATE;
5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.dname,e.ename from emp e right join dept d on e.deptno=d.deptno;
6、列出所有从事"CLERK"工作的雇员姓名及其部门名称、部门人数
select ename,dname,t.count from emp,dept,(select deptno,count(empno) count from emp group by deptno) t where emp.deptno=dept.deptno and job="clerk" and dept.deptno=t.deptno;
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job,count(empno) from emp group by job having min(sal)>1500;
8、列出在部门"sales"（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select dname,ename from emp,dept where emp.deptno=dept.deptno and dname="sales";
9、列出薪金高于公司平均薪金的雇员姓名、所在部门名称、领导姓名、雇员的工资等级
select e.ename,dname,m.ename,grade from emp e,emp m,dept,salgrade s where e.deptno=dept.deptno and e.mgr=m.empno and e.sal between s.losal and s.hisal and e.sal>(select avg(sal) from emp);
10、列出与"SMITH"从事相同工作的所有员工及部门名称
select ename,dname from emp,dept where emp.deptno=dept.deptno and job=(select job from emp where ename="smith");
11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select ename,sal from emp where sal in(select sal from emp where deptno=30);
12、列出薪金高于部门30所有员工薪金的员工姓名、薪金及部门名称
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and sal>all(select sal from emp where deptno=30);
13、列出在每个部门工作的员工数量、平均工资
select count(empno),avg(sal) from emp group by empno;
14、列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp,dept where emp.dept=dept.deptno;
15、列出所有部门的详细信息和部门人数
select dept.*,count from dept left join (select deptno,count(empno) count from emp group by deptno) t on dept.deptno=t.deptno;
16、列出每种工作的最低工资以及从事此工作的雇员姓名
select dname,min(sal),ename from dept left join emp on emp.deptno=dept.deptno group by dname;
17、列出各个部门的经理的最低薪金
SELECT DEPTNO,MIN(SAL) FROM EMP WHERE JOB="MANAGER" GROUP BY DEPTNO;
18、列出所有员工的年工资，按年薪从低到高排序
SELECT ENAME,(SAL+IFNULL(COMM,0))*12 INCOME FROM EMP ORDER BY INCOME;
19、查询雇员的领导信息，要求领导的薪水要超过3000
select e.ename,m.* from emp e left join emp m on e.mgr=m.empno and m.sal>3000;
20、求出部门名称中，带'S'字符的部门 员工的工资总和 、部门人数
select D.DNAME,t.sum,t.count from DEPT D,(select DEPTNO,sum(SAL) sum,count(EMPNO) count from EMP group by DEPTNO) t WHERE D.DEPTNO=t.DEPTNO AND D.DNAME like "%s%";