mysql全套

1. 什么是数据库
存储数据的仓库

2. 什么数据:
大家所知道的都是数据.比如:你同学的名字,年龄,性别等等

3. 数据库概念
1.数据库服务器
2.数据库管理系统
重点
3.库
4.表
5.记录
6.数据
参考画图

4.环境安装:
1.傻瓜式安装下载地址
https://dev.mysql.com/downloads/installer/
2. ZIP 包下载地址
https://dev.mysql.com/downloads/mysql/

3.完全卸载mysql:http://www.cnblogs.com/wangfengming/articles/7879074.html
一.系统数据库

information_schema ：虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
mysql：核心数据库，里面包含用户、权限、关键字等信息。不可以删除
performance_schema：mysql 5.5版本后添加的新库，主要收集系统性能参数，记录处理查询请求时发生的各种事件、锁等现象
sys : mysql5.7版本新增加的库,通过这个库可以快速的了解系统的元数据信息,可以方便DBA发现数据库的很多信息，解决性能瓶颈都提供了巨大帮助

5.库操作
1.创建
CREATE DATABASE db charset utf8; 创建一个名字为 db的数据库,并指定当前库的编码集为utf8
2.删除
DROP DATABASE db; 删除库

3.选择数据库(进入)
use db;
4.查看库
show databases ; -- 查看当前用户下所有的库名
select DATABASE(); -- 查看当前使用的
show CREATE DATABASE db1; -- 查看创建库的信息

5.用户权限：用户管理 identified 确定密码
-- CREATE USER 'zxc'@'%' identified by '123' #创建所有人都可以连接用户 %代表所有
-- CREATE USER 'zxc'@'192.168.13.79' identified by '123' 创建特定用户连接 create user '用户名'@'IP地址' identified by '密码';
-- DROP user 'zxc'@'%' #删除用户 drop user '用户名'@'IP地址';
-- rename user 'zxc'@'192.168.13.79'TO 'zzxxcc'@'192.168.13.79';#修改用户 rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

6.授权管理 grant 赋予，给予
SHOW GRANTS FOR 'zzxxcc'@'192.168.13.79'; -- 查看权限
-- grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
-- 举例授权 db数据库下的所有表的 查询.更新.修改权限
GRANT select,update,delete on db.* to 'zzxxcc'@'192.168.13.79';
-- 授权 所有库的所有权限-->*.*(除grant权限外) privileges特权
grant all privileges on *.* to 'zzxxcc'@'192.168.13.79';
flush privileges; #刷新用户权限必须刷新才能看到效果
show grants for'zzxxcc'@'192.168.13.79'; #再次查询权限就发生变化了
-- revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
revoke DELETE on db.* FROM 'zzxxcc'@'192.168.13.79';
flush PRIVILEGES;
show grants for'zzxxcc'@'192.168.13.79';
7.修改密码
-- 方式一: mysqladmin 命令 #在cmd中使用
-- mysqladmin -u 'zzxxcc' -p123 PASSWORD 123456;

-- 方式二: 直接设置用户密码　
set password for 'zzxxcc'@'192.168.13.79'=PASSWORD('123456');
flush privileges; -- 刷新权限

-- 方式三:修改mysql库下的user表 5.7和5.6的区别是password变成了authentication_string
-- 5.6 版本
update mysql.user set password = password('新密码') where user= '用户名'
flush privileges; -- 刷新权限
-- 5.7版本修改密码方式:
update mysql.user set authentication_string=password('新密码') where user= '用户名'
flush privileges; -- 刷新权限
8.忘记密码.
-- 1.首先打开cmd窗口，关闭mysql服务 -->net stop mysql
-- 2.跳过权限检查，启动mysql mysql -->skip-grant-tables
-- 3.重新打开一个新的cmd窗口,启动客户端(已跳过权限检查,可以直接登录) --> mysql
-- 4.直接进来,修改密码 -->update mysql.user set authentication_string=password('123456') where user='root';
-- 5. 刷新权限 flush privileges;
8.中文乱码问题 使用5.7会乱码
-- 查询字符编码 variables 变量
-- show variables like 'char%' #5.7以下都是默认utf-8

6.表操作 SQL语句
1.创建表
create table info1(
id int not null auto_increment PRIMARY key,
name VARCHAR(50) not null,
sex CHAR(2) NULL
2.查看表
select * from info1; -- 查看表数据
DESC info1; -- 查看表结构
show create table info1; -- 查看表的创建信息

3.删除表
drop table info;
4.修改表
-- ALTER table info1 add age int not null ; -- 添加表字段
-- alter table info1 drop age; -- 删除表字段
-- alter table info1 modify name char(100) null ; -- 修改表字段属性
-- alter table info1 CHANGE names name varchar(50) not null; -- 修改表名称
补充信息
alter table info1 add PRIMARY key(ID,names); -- 添加主键

alter table info1 drop PRIMARY key ;-- 删除主键

alter table info1 alter sex set default '男'; -- 设置默认值

alter table info1 alter sex drop default; -- 删除默认值

5.修改表名称
rename table info1 to info; -- 修改表名


6.复制表
1.CREATE table info3 select * from info where id =1;
ps:主键自增/索引/触发器/外键 不会 被复制

2.复制表结构
CREATE table info5 SELECT * FROM info where 1!=1

CREATE table info4 LIKE info;
ps: 数据/触发器/外键 不会被复制　

7.数据类型

8.数据操作
1.增
-- INSERT into info values(1,'韩涉','女');

-- INSERT into info(id,NAMES) values(2,'韩涉');

-- INSERT into info(id,NAMES) values(3,'韩涉'),(4,'韩涉'),(5,'韩涉');

-- INSERT into info(names,sex) select nameS,sex from info ;

2.删
-- DELETE from info where id = '11'; -- 删除指定数据

-- delete from info; -- 删除整张表中所有的数据

TRUNCATE info; -- 清空整张表
3.改：字段要写全
update info set sex = '人妖', names = '韩小强' where id=12;

4.查
4.1 简单查询
select * from person; -- 查询所有

select name,SEX from person; -- 按指定字段查询

select name,SEX as'性别' from person; -- as 表示为字段起别名

select salary+200 from person; -- 可以进行数据列运算初始值不能为空，不会真的改变表数据，只是生成了虚拟数据

select DISTINCT age,name FROM person; -- 去重复查询

4.2 条件查询
1.运算符
select * FROM person WHERE age >20;
select * FROM person WHERE age <=20;

select * FROM person WHERE age <>20;
select * FROM person WHERE age !=20;

2.null 关键字
select * FROM person where dept_id is null;

select * FROM person where dept_id is not null;

select * FROM person where name ='';

3.逻辑运算符 and or

select * from person where age = 28 and salary =53000;

select * from person where age = 23 or salary =2000;

select * from person where not(age = 28 and salary =53000);

` 4.3 区间查询

select * from person where age BETWEEN 18 and 20;

ps: between...and 前后包含所指定的值
等价于 select * from person where salary >= 4000 and salary <= 8000;

4.4集合查询

select * from person where id = 1 or id = 3 or id = 5;

select * from person where id not in(1,3,5);

4.5 模糊查询

select * from person where name like '%e%'; -- 包含指定参数
select * from person where name like '%e'; -- 以什么结尾
select * from person where name like 'e%'; -- 以什么开头

select * from person where name like '__e%'; -- _表示单个字符站位符

select * from person where name like '__';

4.6 排序查询
select * from person where age >30 ORDER BY salary desc; -- ASC正序 DESC倒序

select * from person ORDER BY CONVERT(name USING gbk);-- 中文排序

-- 表操作

-- --1.创建表
-- CREATE table info (id int not null#约束不为空
-- auto_increment primary key,
-- name varchar(50) null,
-- sex char(5) not null
-- )engine = innodb default charset utf8; #编码方式
-- engine =innodb :表示指定当前表的存储引擎
-- auto_increment 自动递增，自增长只能添加在主键或者唯一索引字段上
-- primary key #主键
-- default charset utf8 :设置表的默认编码集
-- 除了敲代码建立表，还可以直接通过填写空表来建立
-- 表-->新建表-->依次根据需求填写字段名，类型，长度，小数点等-->添加栏位

-- 2.查询表
#查询表数据
-- select name ,sex from info;
-- select * from info
-- 查询表结构
-- desc info

#修改表结构
-- 添加表字段
-- alter table info add age int not null after name
#after表示在name字段后添加字段 age 在什么之前使用 before
-- 修改表字段
-- alter table info modify name varchar(100) not null;
-- desc info; #更改表name字段类型长度为100 不为空
-- alter table info change name names varchar(80) null;
-- desc info; #更改表name字段类型长度为80 并且改名字为names 默认空
-- 区别：change 可以改变字段名字和属性
-- modify只能改变字段的属性

-- 删除表字段
-- alter table info drop sex #删除字段名

-- 更新表字段
-- rename table info to myinfo

-- 主键更新操作
-- #添加主键 :
-- alter table myinfo add primary key(id)
-- 移除主键
-- alter table myinfo drop primary key #后面不用再加括号写去除哪个字段主键了，因为主键唯一

-- 外键更新操作（暂时没讲）
#添加外键:

-- 修改默认值
-- alter table myinfo alter names set default 80;
-- 删除默认值 :
-- alter table myinfo alter names drop default

-- 删除表
-- drop table myinfo

-- 清空表 速度快,如果有字段有自增数组,则清空表后,自增的值从0开始
-- truncate table myinfo #truncate 截短

-- 复制表
#复制表中的结构和数据:主键自增/索引/触发器/外键 不会 被复制
-- create table myinfo2 select * from myinfo;
#复制表结构:数据/触发器/外键 不会被复制
-- create table myinfo3 like myinfo

-- 数据类型： MySQL支持多种类型
-- 大致可以分为三类：数值、日期/时间和字符串(字符)类型

-- 二进制类型:
-- bit[(M)]
-- 二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

-- 整数类型: 作用：存储年龄，等级，id，各种号码等
-- tinyint[(m)] [unsigned] [zerofill] ：# 小整数，数据类型用于保存一些范围的整数数值范围：
-- int[(m)][unsigned][zerofill] #整数，数据类型用于保存一些范围的整数数值范围：
-- bigint[(m)][unsigned][zerofill] #大整数，数据类型用于保存一些范围的整数数值范围：
-- 注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，所以我们使用默认的就可以了
-- 有符号和无符号的最大数字需要的显示宽度均为10，
-- 而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的

-- 小数型:作用：存储薪资、身高、体重、体质参数等 但是数值越大，越不准确
--
-- decimal[(m[,d])] [unsigned] [zerofill]#准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
-- 特别的：对于精确数值计算时需要用此类型
-- decaimal能够存储精确值的原因在于其内部按照字符串存储。
-- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] #单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
-- DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] #双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

-- 字符型:
-- char (m)
-- varchar(m)
-- text
-- 比较char (m)
-- char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
--
-- PS: 即使数据小于m长度，也会占用m长度,但是在查询时，查出的结果会自动删除尾部的空格
--
-- 特点:定长，简单粗暴，浪费空间，存取速度快
--
-- varchar(m)
-- varchar 数据类型用于变长的字符串，可以包含最多达65535个字符(理论上可以,但是实际上在超出21845长度后,mysql会自动帮您转换数据类型为文本类型)。
-- 其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
--
-- PS: varchar类型存储数据的真实内容，不会用空格填充，例如:如果'ab ',尾部的空格也会被存起来
-- 强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
-- 如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
-- 如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
-- 特点:变长，精准，节省空间，存取速度慢
--
-- sql优化：创建表时，定长的类型往前放，变长的往后放
-- 比如性别 比如地址或描述信息
-- PS：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
-- 因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
-- -- 日期/时间类型:作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
-- DATE YYYY-MM-DD（1000-01-01/9999-12-31）
-- TIME HH:MM:SS（'-838:59:59'/'838:59:59'）
-- YEAR YYYY（1901/2155）
-- DATETIME YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
-- TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

-- 六.数据库的表操作(核心)
-- 对数据库文件内容的操作可以总结为:增/删/改/查 四个操作
-- 1.新增操作 2种方法
-- insert into myinfo(id,names,age) VALUES(2,'zxc',38);
-- insert into myinfo(id,names,age) select id,names,age from myinfo2;
-- 2.更新操作
-- update myinfo set `names`='乐乐',age=22 WHERE myinfo.id=2
-- 3.删除操作
-- delete from myinfo -- 整表数据删除
-- DELETE from myinfo WHERE names='ly' 删除符合 where后条件的数据
-- 4.查询操作
-- 1.单表查询（现在公司主要推崇形式）
-- 创建表
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` tinyint(4) DEFAULT '0',
`sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
`salary` decimal(10,2) NOT NULL DEFAULT '250.00',
`hire_date` date NOT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 创建数据
#创建部门
CREATE TABLE IF NOT EXISTS dept (
did int not null auto_increment PRIMARY KEY,
dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;

#添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

#查询所有字段信息
-- select * from person
#查询指定字段信息
-- SELECT name from person
#别名查询，使用的as关键字，as可以省略的
-- SELECT name as '姓名' from person #as 可以省略
#直接对列进行运算,查询出所有人工资,并每人增加100块.
-- SELECT name,salary+100 from person #查询字段之间用逗号隔开
#剔除重复查询
-- SELECT distinct age from person

-- 二 条件查询
-- 条件查询:使用 WHERE 关键字 对简单查询的结果集 进行过滤
-- 　　1. 比较运算符: > < >= <= = <>(!=)
-- select * from person where age = 23;
-- select * from person where age <> 23;
-- 　 2. null 关键字: is null , not null
-- select * from person where age is null;
-- select * from person where age is not null;
-- 　 3.逻辑运算符: 与 and 或 or (多个条件时,需要使用逻辑运算符进行连接)
-- select * from person where age = 23 and salary =29000;
-- select * from person where age = 23 or salary =29000;

-- 三 区间查询
# 使用 between...and 进行区间查询 前后包含所指定的值
-- select * from person where salary between 4000 and 8000;
-- 等价于 select * from person where salary >= 4000 and salary <= 8000;

-- 四 集合查询
-- 关键字: in, not null
#使用 in 集合(多个字段)查询
-- select * from person where age in(23,32,18);
-- 等价于: select * from person where age =23 or age = 32 or age =18;

#使用 in 集合 排除指定值查询
-- select * from person where age not in(23,32,18);

-- 五 模糊查询
-- 关键字 like , not like
-- _ : 只能是单个字符
#模糊查询 like %:任意多个字符, _:单个字符
-- select * from person where name like '月%'; #查询姓名以"月"字开头的
-- select * from person where name like '%周'; #查询姓名以"周"字结尾的
-- select * from person where name like '%苍%';#查询姓名中含有"苍"字的
#查询 name 名称 是四个字符的人
-- select * from person where name like '____';
#查询 name 名称 的第二个字符是 '老'的人
-- select * from person where name like '_老%';
#排除名字带 '周'的学生
-- select * from person where name not like '周%';

-- 六 排序查询
-- 关键字: ORDER BY 字段1 DESC, 字段2 ASC
-- 默认是升序,排序order by 要写在select语句末尾
-- select name from person order by age desc #按照年龄倒序排列
-- select name from person ORDER BY age,id desc #按照年龄倒序，年龄相同按照id倒序排列
#按中文排序
-- select * from person order by name
#强制中文排序
-- SELECT * from person order by convert(name using gbk)

-- 七 聚合函数
-- 聚合函数: 对 '列' 进行操作,返回的结果是一个单一的值,
-- 除了 COUNT 以外，都会忽略空值
-- COUNT：统计指定列不为NULL的记录行数；
-- SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
-- MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
-- MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
-- AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
-- #格式:select 聚合函数(字段) from 表名;
-- select count(name) from person
-- select max(age),min(age),avg(age) from person;

-- 八 分组查询
-- 分组的含义: 将一些具有相同特征的数据进行归类.比如:性别,部门,岗位等等
-- 怎么区分什么时候需要分组呢?
-- 　　套路: 遇到 "每" 字,一般需要进行分组操作.
-- 　　例如: 1. 公司每个部门有多少人.
-- 　　　　 2. 公司中有 多少男员工 和 多少女员工.
#分组查询格式:group by 和having 组合使用添加条件
#查询每个部门的平均薪资
-- select avg(salary),count(name),dept_id from person group by dept_id
#查询每个部门的平均薪资 并且看看这个部门的员工都有谁?
-- select avg(salary),dept_id,group_concat(name) from person GROUP BY dept_id
#查询平均薪资大于5000的部门, 并且看看这个部门的员工都有谁?
select avg(salary),group_concat(name),dept_id from person GROUP BY dept_id having avg(salary)>5000;
-- where 与 having区别:
#执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

-- 九 分页查询 limit (起始条数),(查询多少条数);
-- 好处:限制查询数据条数,提高查询效率
#查询前5条数据
-- select * from person limit 5
#查询第5条到第10条数据
-- select * from person limit 5,5
#查询第10条到第15条数据
-- select * from person limit 10,5

-- 十 SQL 语句关键字的执行顺序
-- 执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->limit　
-- select name, max(salary) -->from person --> where name is not null -->
-- group by name -->having max(salary) > 5000 -->order by max(salary)-->limit 0,5

-- 多表查询

一.多表联合查询where 联合
-- 多表查询语法
-- 如果不加条件直接进行查询,则会出现以下效果,
-- 这种结果我们称之为 笛卡尔乘积。如下
-- select * from person,dept
-- 笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积.
select name,dname from person,dept where person.id = dept.did;

-- 二 多表连接查询(重点) INNER|LEFT|RIGHT JOIN on 连接
-- SELECT 字段列表
-- FROM 表1 INNER|LEFT|RIGHT JOIN 表2
-- ON 表1.字段 = 表2.字段;
-- 1 内连接查询 (只显示符合条件的数据)
-- #查询人员和部门所有信息
-- select * from person inner join dept on person.id=dept.did
-- 效果等同select * from person,dept where person.id = dept.did;
-- 可见内连接查询与多表联合查询的效果是一样的.
-- 2 左外连接查询 (左边表中的数据优先全部显示)
-- 效果:人员表中的数据全部都显示,而 部门表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充.
#查询人员和部门所有信息
-- select * from person left join dept on person.id=dept.did
-- 3 右外连接查询 (右边表中的数据优先全部显示)
-- 效果:正好与[左外连接相反]
-- SELECT * FROM person RIGHT JOIN dept on person.id=dept.did
-- 4 全连接查询(显示左右表中全部数据)
-- 注意: mysql并不支持全连接 full JOIN 关键字
-- 　　注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能
-- #查询人员和部门的所有数据
-- SELECT * from person LEFT JOIN dept on person.id=dept.did
-- UNION
-- SELECT * FROM person RIGHT JOIN dept on person.id=dept.did

-- 三 复杂条件多表查询
-- 1. 查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.
-- (要求:分别使用多表联合查询和内连接查询)
-- SELECT * from person p1 ,dept d1 where p1.dept_id=d1.did and dname='教学部' and age>20 and salary<40000 ORDER BY salary desc;
-- SELECT * from person as p1 INNER JOIN dept as d1 on p1.dept_id=d1.did and dname='教学部' and age>20 and salary<40000 ORDER BY salary desc;
-- 2.查询每个部门中最高工资和最低工资是多少,显示部门名称
-- SELECT max(salary),min(salary),dname from person p1 ,dept d1 where p1.dept_id=d1.did GROUP BY p1.dept_id

-- 四 子语句查询　
-- 子查询(嵌套查询): 查多次, 多个select
-- 注意: 第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用.
-- 1.作为表名使用
-- select * from (select * from person) as 表名;
#最里层括号(sql语句)具有优先执行权,as 后面的表名称不能加引号('')
-- 2.求最大工资那个人的姓名和薪水
-- 错误写法：原因是不能自动对应最大薪水的人名
-- SELECT max(salary),薪水.name from(SELECT name,salary from person) as 薪水;
-- 正确方法
-- 首先求出最大薪资
-- SELECT max(salary) from person; #得到是100000
-- 然后求出最大薪资名字
-- SELECT name,salary from person where salary=1000000;
-- 最后合并
-- SELECT name,salary from person where salary=(SELECT max(salary) from person)

-- 4.练习
-- 　　1.查询平均年龄在20岁以上的部门名
-- SELECT avg(age),dept_id from person GROUP BY person.dept_id HAVING avg(age)>20; #先查部门平均年龄大于20 的部门id
-- SELECT dname from dept where dept.did in(1,3,5) #根据部门id再在部门表中查出具体的部门名称
-- 合并：
-- SELECT dname from dept where dept.did in(SELECT dept_id from person GROUP BY person.dept_id HAVING avg(age)>20)
-- 　　2.查询教学部 下的员工信息
-- SELECT did from dept where dname='教学部';
-- select * from person where person.dept_id=1
-- --合并
-- select * from person where person.dept_id=(SELECT did from dept where dname='教学部')
-- 　　3.查询大于所有人平均工资的人员的姓名与年龄
-- SELECT name , age from person where salary>(SELECT avg(salary) from person)

-- 5.关键字
-- any ==or 其中任意值
-- select ...from ... where a > any(...);
-- select ...from ... where a > result1 or a > result2 or a > result3;

-- all ==and 所有
-- select ...from ... where a > all(...);
-- select ...from ... where a > result1 and a > result2 and a>result3;

-- some==any == or
-- select ...from ... where a > some(...);
-- select ...from ... where a > result1 or a > result2 or a > result3;

-- exists 存在
-- not EXISTS 不存在
-- 可以与其他的查询条件一起使用 用and or 链接
-- SELECT ... FROM table WHERE EXISTS (subquery)
-- 该语法可以理解为：主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否得以执行。
-- SELECT * FROM person WHERE EXISTS(SELECT * FROM dept WHERE did=5);
#内层查询SELECT * FROM dept WHERE did=5 返回true所以外层查询执行
-- NOT EXISTS刚好与之相反

-- 五 其他查询
-- 1.临时表查询
-- 需求: 查询高于本部门平均工资的人员
-- SELECT dept_id ,avg(salary) as '平均工资'from person GROUP BY person.dept_id
-- sELECT salary,p1.dept_id,name from person as p1,
-- (SELECT dept_id ,avg(salary) as '平均工资'from person GROUP BY person.dept_id) as p2
-- where p1.dept_id=p2.dept_id and p1.salary>p2.平均工资
-- 在当前语句中,我们可以把上一次的查询结果当前做一张表来使用.
-- 因为p2表不是真是存在的,所以:我们称之为 临时表　

-- 2. 判断查询　IF关键字 语法: IF(条件表达式,"结果为true",'结果为false')
-- 需求1 :根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。
-- 显示效果:姓名,年龄,性别,工资,级别
-- SELECT p1.* ,
-- if (p1.salary>10000,'高端人群' ,'低端人群') as '级别'
-- from person as p1
-- 需求2: 根据工资高低,统计每个部门人员收入情况,划分为
-- 富人,小资,平民,吊丝 四个级别, 要求统计四个级别分别有多少人
-- SELECT dname as '部门',
-- sum(case when person.salary>50000 then 1 else 0 end) as '富人',
-- sum(case when person.salary between 20000 and 50000 then 1 else 0 end ) as '小资',
-- sum(case when person.salary between 5000 and 20000 then 1 else 0 end) as '平民',
-- sum(case when person.salary<5000 then 1 else 0 end ) as '屌丝'
-- FROM person,dept where person.dept_id = dept.did GROUP BY person.dept_id
#函数sum()调用，前面不能有空格

 

-- 六 SQL逻辑查询语句执行顺序(重点***)
伪代码
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
顺序：
1.执行FROM语句
2.执行ON过滤
3.添加外部行 OUTER JOIN
4.执行WHERE过滤
5.执行GROUP BY分组
6.执行HAVING过滤
7.SELECT列表
8.执行DISTINCT子句
9.执行ORDER BY子句
10.执行LIMIT子句

-- 七 外键约束：对两个表的关系进行一些约束froegin key
-- foreign key 定义:就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。
-- 创建表时,同时创建外键约束
CREATE table if not EXISTS ddd(
did int not null auto_increment PRIMARY key,
dname varchar(50) not null comment '部门名称'#comment注释
)engine=innodb default CHARSET utf8;
CREATE table if not EXISTS ppp(
id int not null auto_increment PRIMARY key,
name varchar(100) not null,
age tinyint(4) null DEFAULT 0,
dept_id int not null,
constraint fk_did foreign key (dept_id) references ddd(did)#设置外键约束fk_did 约束dept_id references 引用 #constraint约束
)engine=INNODB DEFAULT charset utf8

-- 5.2 已经创建表后,追加外键约束
#添加外键约束 外键名字不能重复
-- alter table person add constraint fk_d FOREIGN key(dept_id) references dept(did)
#删除外键约束
-- alter table person drop foreign key fk_d
-- 定义外键的条件：
-- （1）外键对应的字段数据类型保持一致,且被关联的字段(即references指定的另外一个表的字段)，必须保证唯一
-- （2）所有tables的存储引擎必须是InnoDB类型.
-- （3）外键的约束4种类型: 1.RESTRICT(常用) 2. NO ACTION 3.CASCADE 4.SET NULL
-- （4）建议:1.如果需要外键约束,最好创建表同时创建外键约束.
-- 注：插入数据时，先插入主表中的数据，再插入从表中的数据。（主表是关联表）
-- 删除数据时，先删除从表中的数据，再删除主表中的数据。（从表是需要设置外键的表）

-- 八 其他约束类型
-- 1.非空约束
-- 关键字: NOT NULL ,表示 不可空. 用来约束表中的字段列
-- 2.主键约束 主键这一行的数据不能重复且不能为空。
-- 特殊的主键——复合主键 primary key(id,name)
-- 3.唯一约束 关键字: UNIQUE
-- 一张表中指定的一列的值必须不能有重复值
-- unique id_name(id,name)
-- 添加唯一约束：alter table t4 add unique id_name(id,name);
-- 删除唯一约束：alter table t4 drop index id_name;
-- 效果： 当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败.　
-- 4.默认值约束 关键字: DEFAULT
-- 效果：INSERT语句执行时.，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充

 

-- 九.表与表之间的关系
-- 一对多 一对一 多对多

十.数据库三范式
1．第一范式(确保每列保持原子性)
2．第二范式(确保表中的每列都和主键相关)
3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

练习题
-- 1.创建留言数据库: liuyandb;
-- 2.在liuyandb数据库中创建留言表liuyan
-- drop table if EXISTS liuyan;
-- CREATE table liuyan(
-- id int not null auto_increment PRIMARY key,
-- author varchar(32) null,
-- addtime datetime not null DEFAULT NOW(),
-- content text not null,
-- isdelete char(1) not null DEFAULT 0
-- )ENGINE=INNODB auto_increment=6 DEFAULT charset=utf8;
-- ALTER table liuyan add title varchar(32) not null; #添加属性

-- 　3.在留言表最后添加一列状态（status char(1) 默认值为0
-- alter table liuyan add status char(1) DEFAULT 0

-- 4.修改留言表author的默认值为’youku’,设为非空
-- alter table liuyan modify author varchar(32) null DEFAULT 'youku'
-- 5.删除liuyan表中的isdelete字段
-- alter table liuyan drop isdelete

-- 6.为留言表添加>5条测试数据
-- INSERT into liuyan(title,author,content) VALUES('夜空','zzy','zxc浪');
-- INSERT into liuyan(title,author,content) VALUES('大白天','zxc','睡懒觉');
-- INSERT into liuyan(title,author,content) VALUES('蓝天','ly','跑步');
-- INSERT into liuyan(title,author,content) VALUES('白云','闫佳琪','睡懒觉');
-- INSERT into liuyan(title,author,content) VALUES('黑土地','高雅','睡懒觉');
-- INSERT into liuyan(title,author,content) VALUES('黄种人','浩南','睡懒觉');
-- 7. 要求将id值大于3的信息中author字段值改为admin
-- UPDATE liuyan set author='admin' where id>3
-- 8. 删除id号为4的数据。
-- DELETE from liuyan where id=4
-- 附加题：
-- 1.为留言表添加>15条测试数据，要求分三个用户添加
-- CREATE user 'zxc'@'192.168.79' identified by '123'
-- CREATE user 'zzy'@'192.168.78' identified by '123'
-- 2.查询所有留言信息
-- SELECT * from liuyan
-- 3.查询某一用户的留言信息。
-- SELECT * from liuyan where id=3
-- 4.查询所有数据，按时间降序排序
SELECT * from liuyan WHERE addtime desc
-- 获取id在2到6之间的留言信息，并按时间降序排序