MySQL第二次笔记

MySQL:开源免费的数据库，小型的数据库，已经被Oracle收购了，MySQL6.x版本也开始收费
Oracle：收费的大型数据库，Oracle公司的产品，Oracle收购sun公司
DB2：IBM公司的数据库产品，收费的，常用在银行系统中
SQLserver：MicroSoft公司收费的中型数据库，C#，.net等语句使用
SyBase：已淡出历史舞台，提供理论一个非常专业数据建模工具PowerDesigner
SQLite：嵌入式的小型数据库，应用在移动端

什么是数据库管理系统

数据管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件，用于建立，使用和维护数据库，对数据进行统一管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理系统访问数据库。

理解数据库

我们现在所说的数据库泛指“关系型数据库管理系统(RDBMS-Relational database management system)”，即"数据库服务器".
主键自增长：
当主键设置为自增长后，在没有给出主键值时，主键的值会自动生成，而且时最大主键值+1，这样就不会出现主键重复的可能

• 主键设置自增长（主键必须是整型才能自增长）
  create table stu(
  id int primary key auto_increment;
  );
• 设置主键自增长的初始值
  create table stu(
  id int primary key auto_increment;
  )auto_increment=1001;

DCL 数据控制语言

• 创建用户：
  create user 用户名@地址 identified by ‘密码’;
  %:可以从任意远程主机登陆，可以使用通配符%.
  localhost:本地登陆用户
  create user abc@’%’ identified by ‘123’;
  create user abc@localhost identified by ‘123’;
• 给用户授权：
  grant 权限1,…,权限n on 数据库.to 用户名@ip
  grant create,drop,insert,selert,delete,update,alter on abc. to user@localhost;
  grant all on abc.*to user@localhost;
• 撤销授权：
  revoke 权限1,…权限n on 数据库.*from 用户名@ip
• 查看用户权限
  show grants for 用户名@ip;
• 删除用户
  drop user 用户名@ip;
• 修改用户密码
  update user set password=password (‘密码’) where user=‘用户名’ flush privileges;
• 查看用户信息
  select user,host from mysql.user;

DQL 数据查询语言

语法：
select 字段
from 要查询的表名
where 行条件
group by 对结果分组
having 分组后的行条件，一般区分是聚合函数还是普通函数条件，聚合函数放这
order by 对结果排序
limit 分页

基础查询

• 查询所有列内容：select * from 表名；
• 查询指定列：select 字段名 from 表名；

条件查询

条件查询就是查询时候加限定条件，用在where语句后面

• =,!=,<>（不等于）,<,<=,>,>=;
• between…and;相当于>=and <=
• in();查询in内包含的所有记录的数据
• is null；是空的 is not null；不是空的
• and 并且，用来连接多个条件
• or 或者
• not 取反

模糊查询

模糊查询必须使用like关键字

• 其中"_"匹配任意一个字符
• 其中"%"匹配0~n个任意字符

字段控制查询

去除重复记录：select distinct from 表名；
替换空字符：select * ifnull(有null的字段列,替换的值) from 表名;
给列表添加别名，方便使用：select 字段 as 别名 from 表名;这里的as可以省略
拼接字段;select concat(字段，‘拼接’,字段2) from 表名；

排序

升序:select * from 表名 order by 想要排序的字段名 asc(默认排序方式是asc（升序）可以不写，降序是desc)
先升序某个字段，再将结构以另外一个字段降序：select * from 表名 order by 某个字段 asc ,另外一个字段 desc;

聚合函数

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

分组查询

• 如查询不同数据的和时，我们要进行分组查询
• 分组查询和聚合函数搭配使用就是分组统计
• 分组查询使用group by进行查询信息分组
  格式：select 字段,字段2 from 表名 group by 分组字段 having 分组条件;
  注意：group by 必须要指定需要分组的字段，而且必须时出现在select之后除了聚合函数之外所有列

limit(MySQL方言)

limit用来限定查询结果的起始行start，以及总行size
注意：起始行从0开始，即第一行开始
查询10条记录，起始行从3开始
select * from 表名 limit 3,10;

区分表与表之间关系

• 一对多关系
  主外键关系，找到唯一的数据作为主键，其他数据关联作为外键
• 多对多关系
  多个个学生可以学习多个课程
  建立一个分数表，分别放学生和课程的外键
• 一对一关系
  两个表都有对方的主键，作为外键

多表查询

导入案例内容：

-- 创建数据库
create database employee character set utf8;
use employee;
-- 表一：部门表(部门编号，部门名称，部门电话)
create table dept(
	did int primary key auto_increment,	-- 部门编号
	dname varchar(20),			-- 部门名称
	dtel  varchar(20)			-- 部门电话
);
-- 表二：员工表（员工编号，员工姓名，员工性别，入职时间，员工工资，部门编号）
create table emp(
	eid int primary key auto_increment,	                -- 员工编号
	ename varchar(20),					-- 员工姓名
	esex  CHAR(2),		                                -- 员工性别
	estartime datetime,					-- 入职时间
	epay float,						-- 员工工资
	did int,							-- 部门编号
    foreign key (did) references dept(did)  -- 外建
)auto_increment=1;
-- 表三：员工工作履历表（履历编号，开始时间，结束时间，就业地，职务，员工编号）
create table history(
	hid int primary key auto_increment,		-- 履历编号
	hstarttime datetime,				-- 开始时间
	hendtime datetime,				-- 结束时间
	hcity varchar(20),				-- 就业地
	hwork varchar(20),				-- 职务
	eid int,                   			-- 员工编号
	foreign key (eid) references emp(eid)  -- 外建
);


-- 写入数据
insert into dept values(did,'董事会','111111');
insert into dept values(did,'财务部','222222');
insert into dept values(did,'市场部','333333');
insert into dept values(did,'研发部','444444');
insert into dept values(did,'行政部','555555');
insert into dept values(did,'质检部','666666');


insert into emp values(eid,'tom','男','2005-09-13',9000.00,1);
insert into emp values(eid,'jack','男','2005-10-22',8500.00,1);
insert into emp values(eid,'kelly','女','2006-02-19',5000.00,2);
insert into emp values(eid,'red','女','2007-10-31',4800.00,2);
insert into emp values(eid,'blue','男','2006-05-10',6000.00,3);
insert into emp values(eid,'green','女','2006-06-19',5500.00,3);
insert into emp values(eid,'peter','女','2006-08-21',5800.00,3);
insert into emp values(eid,'cat','女','2008-11-09',6300.00,3);
insert into emp values(eid,'dog','男','2009-09-02',7800.00,4);
insert into emp values(eid,'lion','男','2010-12-09',7200.00,4);
insert into emp values(eid,'suny','男','2011-04-06',7500.00,4);
insert into emp values(eid,'tiger','男','2009-12-27',6400.00,5);
insert into emp values(eid,'bell','男','2010-11-25',6200.00,5);
insert into emp values(eid,'bull','女','2013-12-15',5500.00,6);
insert into emp values(eid,'eecc','女','2011-1-18',5800.00,6);
insert into emp(ename,estartime,epay,did) values('lucy','2017-5-1',7600.00,1);
insert into emp values(eid,'郭德纲','','2016-7-1',3500.00,3);
insert into emp values(eid,'关晓彤','','2011-7-1',9000.00,1);


insert into history values(null,'2000-10-01','2001-02-02','襄阳','行政总监',1);
insert into history values(null,'2001-02-08','2002-06-06','深圳','总经理',1);
insert into history values(null,'2003-02-11','2004-02-25','青岛','会计',3);
insert into history values(null,'2004-03-01','2006-02-01','威海','会计师',3);
insert into history values(null,'2005-09-23','2006-09-24','达州','出纳',4);
insert into history values(null,'2006-10-08','2007-10-31','成都','出纳',4);
insert into history values(null,'2002-10-22','2004-04-18','宜昌','市场专员',5);
insert into history values(null,'2004-05-08','2006-04-30','北京','区域经理',5);
insert into history values(null,'2005-11-01','2006-10-28','武汉','市场专员',6);
insert into history values(null,'2006-11-01','2006-06-19','青岛','市场专员',6);
insert into history values(null,'2004-04-12','2005-04-21','徐州','市场专员',7);
insert into history values(null,'2005-05-08','2006-08-20','武汉','市场专员',7);
insert into history values(null,'2004-10-09','2006-10-10','南京','市场专员',8);
insert into history values(null,'2006-10-21','2008-10-31','北京','市场专员',8);
insert into history values(null,'2006-05-08','2007-05-08','太原','程序员',9);
insert into history values(null,'2007-06-01','2009-09-01','衡阳','架构师',9);
insert into history values(null,'2008-11-28','2009-11-30','北京','程序员',10);
insert into history values(null,'2009-12-01','2010-12-08','南京','程序员',10);
insert into history values(null,'2008-02-01','2010-02-02','青岛','程序员',11);
insert into history values(null,'2010-03-01','2011-04-03','武汉','数据库管理员',11);
insert into history values(null,'2004-09-11','2007-09-10','深圳','行政助理',12);
insert into history values(null,'2007-09-29','2009-12-25','北京','行政专员',12);
insert into history values(null,'2008-11-20','2009-11-22','广州','行政助理',13);
insert into history values(null,'2009-12-01','2010-11-20','北京','行政助理',13);

合并结果集

• 合并结果集就是把两个select语句的查询结果合并在一起
• 合并的select语句的列数量，类型，顺序必须完全一样
• 合并结果集有两种方式：
  union:去除重复记录
  union all：不去除重复记录

select 列1，列2 from 表a
union
select 列1，列2 from 表b；

连接查询

多个表都有主外键连接，进行多个表数据查询

• 内连接
  方言版（笛卡尔积）–自然连接
  连接两个表会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合
  的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情
  况。
  多余内容不是我们想要的，需要去除重复，条件过滤，通过主外键来去除
  语法：

Select 列名
From 表名,表名
where 主表的主键值=子表的外键值

案例：

Select * from emp, dept; -- 产生大量冗余无效的数据
SELECT * FROM emp e,dept d WHERE e.did=d.did
-- 用where关键字在笛卡尔积基础上去除重复的数据
/*1.查询每个部门的部门名称及平均工资,并且平均工资大于6000*/
select d.dname,AVG(e.epay) from dept d,emp e where
d.did=e.did group by d.dname having
AVG(e.epay)>6000;
/*2、查询所有性别为男的员工的姓名和工作经历*/
select e.ename,h.* from emp e,history h 
where e.esex='男' and h.eid = e.eid;
/*3.查询工资比其所在部门平均工资高的员工信息*/
-- 步骤：
-- 1、查询部门的平均工资
select d.did,d.dname,AVG(e.epay) from dept d,emp e
where d.did=e.did group by d.dname,d.did;
-- 2、查询工资比其所在部门平均工资高的员工信息
select * from emp e1,
(select d.did,d.dname,AVG(e.epay) aepay -- 虚拟表 e2
from dept d,emp e 
where d.did=（e.did group by d.dname,d.did) e2
where e1.did = e2.did and e1.epay > e2.aepay;

• 标准版（内连接）
  关键字inner join…on
  内连接的特点：查询结果必须满足条件

/*查询所有员工及其所在部门名称*/
select e.ename,d.dname
from emp e inner join dept d on e.did=d.did;

• 外连接
  关键字：left/right join
  外连接特点：可以在原有基础上查询没能建立主外键关系的值，不满足条件的值
  左外连接和右外连接是区别是以哪个表作为主键，哪个表作为外键，能查到的值以主键表的值为主
• 全连接
  左右两个表的值都让显示，如果没有匹配的值，显示null

/*检索当前所有员工的信息和工作经历。
*/
select * from emp e LEFT JOIN history h on
e.eid=h.eid;
UNION
select * from emp e RIGHT JOIN history h on
e.eid=h.eid;

• 连接查询心得：
  连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通
  常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需
  要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系
  去除。
  两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主
  外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔
  积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有
  一个主外键条件，三张表连接至少有两个主外键条件。

子查询

• 当一个查询语句嵌套在另外一个查询语句中称之为子查询
• 子查询的结果为外查询的条件
• 所有子查询都可以使用连接查询实现，反之，所有连接查询不能通过子查询实现，子查询只是相对灵活而已
• 执行顺序：先执行最内层的子查询，让后一层一层往外执行
• 子查询出现的位置：where后，作为条件的一部分，from后，作为被查询的一个表
• 当子查询出现where后面作为条件时，还可使用一些关键字如：any和some表示满足设定条件的最小值均可返回结果，all是当>|>=时，满足条件的最大值返回结果，当<|<=时，满足条件的最小值返回结果

/*1、查询所有研发部的员工信息*/
-- 步骤:
-- 1、查询研发部的did
select did from dept where dname='研发部'; -- 4
-- 2、根据步骤1查询研发部的员工信息
select * from emp where did = 4;
-- 合并，用子查询
select * from emp where did =
(select did from dept where dname='研发部');
/*2、查询研发部和市场部的员工信息。
*/
select * from emp where did in
(select did from dept where dname in ('研发部','市场部'));
/*3、查询不在财务部的员工信息*/
select * from emp where did !=
(select did from dept where dname='财务部');
/*4、工资高于red的员工信息*/
select * from emp where epay >
(select epay from emp where ename ='red');
/*5、找出高于平均工资的所有人*/
select * from emp where epay >
(select AVG(epay) from emp);
/*6、找出高于财务部门所有员工工资的人*/
/*7、查询部门和工资与tom完全相同的员工信息*/
select * from emp where (did,epay) in
(select did,epay from emp where ename ='tom');

case…when 语句

• 用于计算条件列表返回多个可能结果表达式之一
• 第一种简单case函数，将某个表达式与一组简单表达式进行比较确定结果
• 语法：

select 字段,
CASE 字段
	WHEN 条件1 THEN 值1
	WHEN 条件2 THEN 值2
	ELSE 其他值 END 别名
from 表名;

• 第二种case搜索函数，搜索函数计算一组布尔表达式以确定结果，两个格式都可以支持可选的else函数
• 语法：

select 字段,
CASE WHEN 字段=条件1 THEN 值1
	WHEN 字段=条件2 THEN 值2
	ELSE 其他值 END 别名
from 表名;

注意：case函数只返回第一个符号条件的值，剩下的case部分会被自动忽略
案例：

/*1.员工表中，如果性别是男，显示：盖世英雄方世玉
如果性别是女，显示：貌美如花林黛玉*/
/*2.行列转换*/
create table stu (
	stuname varchar(20),
-- 学生姓名
	cname varchar(20),
-- 科目
	score float -- 成绩
);
insert into stu values
('张三','DBB',56),
('张三','C++',92),
('李四','JAVA',78),
('李四','DBB',34),
('王五','JAVA',89),
('王五','DBB',67),
('赵六','C++',85);
/*实现行列转换*/
select stuname,
	SUM(case when cname ='DBB' then score else 0 end) 'DBB',
	SUM(case when cname ='C++' then score else 0 end) 'C++',
	SUM(case when cname ='JAVA' then score else 0 end) 'JAVA'
from stu group by stuname;