MySQL笔记_DN
MySQL_2
一、数据库简单介绍
1、 按照数据库的发展时间顺序，主要出现了以下类型数据库系统：
Ø 网状型数据库
Ø 层次型数据库
Ø 关系型数据库
Ø 面向对象数据库
二、常用基本SQL语句/语法
Ø SQL语句基础理论
SQL是操作和检索关系型数据库的标准语言，标准SQL语句可用于操作然后关系型数据库。
标准的SQL语句通常划分为以下类型：
DDL：
ddl英文全称是data definition language（数据定义语言），主要的命令有alter、create、drop、truncate，ddl主要用在定义或者改变表的结构。
DML：
dml英文全称是data manipulation language（数据操纵语言），主要的命令有select、update、delete、insert，dml主要用来操作数据库中的数据
DQL：
dql英文全称是data query language（数据查询语言），主要的命令有select，关键字：SELECT ... FROM ... WHERE
DCL：
dcl英文全称是 data control language（数据控制语言），主要的命令有grant、deny、revoke等，dcl主要用来设置或者更新数据库用户或者角色权限
TCL：
tcl英文全称是 Transaction Control language（事务控制语句），主要的作用是用来控制事务的，常用的关键字有commit、rollback、setpoint等
1. 常见数据库对象：
| 对象名称 | 对应关键字 | 描述 |
|---|---|---|
| 表 | table | 表是数据库存储的逻辑单元，以行和列的形式存在；列是字段，行就是一条数据记录 |
| 数据字典 | 就是系统表，存储数据库相关信息的表，系统表里的数据通常有数据库系统维护。系统表结构和数据，开发人员不应该手动修改，只能查询其中的数据 | |
| 视图 | view | 一个或多个数据表里的数据的逻辑显示，视图就是一张虚拟的表，并不真正存储数据 |
| 约束 | constraint | 执行数据检验规则，用于保证数据完整性的规则 |
| 索引 | index | 用于提高查询性能，相当于书的目录 |
| 函数 | function | 用于完成一个特定的计算，具有返回值和参数 |
| 存储过程 | procedure | 完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境 |
| 触发器 | trigger | 相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理 |
上面的对象都可以通过用create、alter、drop完成相关的创建、修改、删除操作。
2. 常用数据类型
| 列类型 | 说明 |
|---|---|
| tinyint/smallint/mediumint int(integer)/bigint | 1字节、2字节、3字节、4字节、8字节整数，又可分有符号和无符号两种。这些整数类型的区别仅仅表现范围不同 |
| float/double | 单精度、双精度浮点类型 |
| decimal(dec) | 精确小数类型，相当于float和double不会产生精度丢失问题 |
| date | 日期类型，不能保存时间。当Java里的Date对象保存到该类型中，时间部分丢失 |
| time | 时间类型，不能保存日期。当Java的Date对象的保存在该类型中，日期部分丢失 |
| datetime | 日期、时间类型 |
| timestamp | 时间戳类型 |
| year | 年类型，仅保存年份 |
| char | 定长字符串类型 |
| varchar | 可变长度字符串类型 |
| binary | 定长二进制字符串类型，它以二进制形式保存字符串 |
| varbinary | 可变长度的二进制字符串类型，二进制形式保存字符串 |
| tingblob/blobmediumblob/longblob | 1字节、2字节、3字节、4字节的二进制大对象，可存存储超图片、音乐等二进制数据，分别可存储：255/64K/16M/4G的大小 |
| tingtext/textmediumtext/longtext | 1字节、2字节、3字节、4字节的文本对象，可存储超长长度的字符串，分别可存储：255/64K/16M/4G的大小的文本 |
| enum(‘val1’, ‘val2’, …) | 枚举类型，该列的值只能是enum括号中出现的值的之一 |
| set(‘value1’, ‘value2’, …) | 集合类型，该列的值可以是set中的一个或多个值 |
1. 数据库操作
-- 1. 查看当前MySQL当中有多少个数据库:
SHOW DATABASES;
-- 2. 创建数据库:
-- CREATE DATABASE 数据库名
-- 创建数据库:mydb
CREATE DATABASE mydb;
-- 3. 数据库创建时指定字符集
-- CREATE DATABASE 数据库名 CHARSET=UTF8/GBK
-- 4. 创建数据库:db1(字符集用gbk) db2(字符集用utf8)
CREATE DATABASE db1 CHARSET=UTF8;
CREATE DATABASE db2 CHARSET=GBK;
-- 5. 查看数据库信息
-- SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE db2;
-- 6. 删除数据库
-- DROP DATABASE 数据库名
DROP DATABASE db1;
DROP DATABASE db2;
-- 7. 想保存数据,就要选取需要保存数据的数据库,然后才能在该数据库中建立表等操作.
-- USE 数据库名
-- 使用mydb这个数据库
USE mydb;
/* 练习: */-------------------------------------------------------------
-- 1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk
CREATE DATABASE mydb1 CHARSET=utf8;
CREATE DATABASE mydb2 CHARSET=gbk;
-- 2. 查询所有数据库检查是否创建成功
SHOW DATABASES;
-- 3. 检查两个数据库的字符集是否正确
SHOW CREATE DATABASE mydb1;
SHOW CREATE DATABASE mydb2;
-- 4. 先使用mydb2 再使用 mydb1
USE mydb2;
USE mydb1;
-- 5. 删除两个数据库
DROP DATABASE mydb1;
DROP DATABASE mydb2;
2. 表操作
/* ----------------------------------------------------------------------------- */
-- 1. 创建表
-- CREATE TABLE [库名.]表名 (
-- 列名1 类型[(长度)] [DEFAULT 默认值] [约束条件],
-- 列名2 类型...
-- )[CHARSET=utf8/gbk]
-- 创建userinfo表
CREATE TABLE userinfo(
id INT,
username VARCHAR(32),
password VARCHAR(32),
nickname VARCHAR(32),
age INT(3)
)
-- 数字的长度表示位数,VARCHAR的长度表示最多占用的字节数
/* ----------------------------------------------------------------------------- */
-- 2. 查看当前数据库创建的所有表
SHOW TABLES;
-- 3. 查看创建的某一张表的详细信息
-- SHOW CREATE TABLE 表名
SHOW CREATE TABLE userinfo;
-- 4. 查看表结构
-- DESC 表名
DESC userinfo;
-- 5. 删除表
-- DROP TABLE 表名
DROP TABLE userinfo;
-- 6. 修改表
-- 修改表名
-- RENAME TABLE 原表名 TO 新表名
RENAME TABLE userinfo TO user;
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 创建数据库mydb3 字符集gbk 并使用
CREATE DATABASE mydb3 CHARSET=gbk;
USE mydb3;
-- 2. 创建t_hero英雄表, 有名字和年龄字段 默认字符集
CREATE TABLE t_hero(
id INT,
name VARCHAR(32),
age INT(3)
);
-- 3. 修改表名为hero
RENAME TABLE t_hero TO hero;
-- 4. 查看表hero的字符集
SHOW CREATE TABLE hero;
-- 5. 查询表hero结构
DESC hero;
-- 6. 删除表hero
DROP TABLE hero;
-- 7. 删除数据库mydb3
DROP DATABASE mydb3;
/* -------------------------------------------------------------------------------- */
-- 7. 修改表结构:ALTER TABLE
-- 实际开发中,通常不建议在表中含有数据时修改表结构
-- 添加列
-- ALTER TABLE 表名 ADD 列名 类型[长度]
ALTER TABLE user ADD gender VARCHAR(10);
DESC user;
CREATE TABLE hero(
username VARCHAR(32),
age INT(3)
)
-- 在表的第一列上添加新列
-- ALTER TABLE 表名 ADD 列名 类型[长度] FIRST
ALTER TABLE hero ADD id INT FIRST;
DESC hero;
-- 在表中插入一个字段
-- ALTER TABLE 表名 ADD 列名 类型[长度] AFTER 字段名
ALTER TABLE hero ADD gender VARCHAR(10) AFTER username;
-- 删除表中现有的列
-- ALTER TABLE 表名 DROP 字段名(注:列名)
ALTER TABLE hero DROP gender;
DESC hero;
-- 修改表中现有的列
-- 注意,当表中含有数据后,字段类型尽量不要改变,长度修改尽量不要减少,否则都有可能违背表中现有
-- 数据要求导致修改失败.
-- ALTER TABLE 表名 CHANGE 原字段名 新字段名 新类型
-- 将age的类型从INT换成VARCHAR
ALTER TABLE hero CHANGE age age VARCHAR(10);
-- 将age的长度改为100
ALTER TABLE hero CHANGE age age VARCHAR(100);
-- 将age改为gender,长度改为10
ALTER TABLE hero CHANGE age gender VARCHAR(10);
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 创建数据库mydb4 字符集utf8并使用
CREATE DATABASE mydb4 CHARSET=utf8;
USE mydb4;
-- 2. 创建teacher表 有名字(name)字段
CREATE TABLE teacher(
name VARCHAR(32)
);
-- 3. 添加表字段: 最后添加age 最前面添加id(int型) , age前面添加salary工资(int型)
ALTER TABLE teacher ADD age INT(3);
ALTER TABLE teacher ADD id INT FIRST;
ALTER TABLE teacher ADD salary INT AFTER name;
-- 4. 删除age字段
ALTER TABLE teacher DROP age;
-- 5. 修改表名为t
RENAME TABLE teacher TO t;
-- 6. 删除表t
DROP TABLE t;
-- 7. 删除数据库mydb4
DROP DATABASE mydb4;
/* ----------------------------------------------------------------------------- */
-- 总结:
-- DDL语言,数据定义语言,操作数据库对象
-- CREARE,ALTER,DROP
-- 创建表:CREATE TABLE
-- 修改表:ALTER TABLE
-- 删除表:DROP TABLE
3. 数据操作
DML语言:数据操作语言,是对表中的数据进行操作的语言,包含:增,删,改操作
CREATE TABLE person(
name VARCHAR(32),
age INT(3)
);
/* ----------------------------------------------------------------------------- */
-- 1. 插入数据
-- INSERT INTO 表名 [(字段1,字段2...)] VALUES (字段1的值，字段2的值...)
INSERT INTO person(name, age) VALUES ('张三',22);
INSERT INTO person(age, name) VALUES (22,'王五');
-- 未指定的列插入的都是列的默认值。当创建表时没有为列声明特定的默认值时，列默认值为null。
INSERT INTO person(name) VALUES('李四');
-- 字段名可以忽略不写，此时为全列插入，即:VALUES需要指定每一列的值，且顺序，个数，类型必须与表中的字段一致
INSERT INTO person VALUES('传奇',22);
-- 查看person表中的所有数据
SELECT * FROM person
/* ----------------------------------------------------------------------------- */
-- 2. 修改表数据操作:UPDATE语句
-- UPDATE 表名 SET 字段名1=新值1[,字段名2=新值2,...][WHERE 过滤条件]
-- 通常修改语句要添加WHERE子句，用于添加过滤条件来定位要修改的记录。不添加WHERE子句则是全表所有记录都修改。
-- 下面的操作会将person表中每条记录的age字段值都改为55！！
UPDATE person SET age=55;
-- 将李四的年龄改成23岁
UPDATE person SET age=23 WHERE name='李四';
-- WHERE中常用的条件:=,>,>=,<,<=,<>(不等于，!=不是所有数据库都支持)
-- 将年龄大于50岁的人的年龄改为25
UPDATE person SET age=25 WHERE age>50;
-- 修改字段时，可以将计算表达式的结果进行修改
-- 将每个人的年龄涨一岁
UPDATE person SET age=age+1;
-- 将年龄为24岁的人改名为李老四，年龄为55
UPDATE person
SET name='李老四',age=55
WHERE age=24;
SELECT * FROM person;
/* ----------------------------------------------------------------------------- */
-- 3. 删除数据:DELETE语句
-- DELETE FROM 表名 [WHERE 过滤条件]
-- 注意！！！不添加WHERE条件则是全表删除！！！
-- 删除名字为李老四的记录
DELETE FROM person WHERE name='李老四';
-- 删除年龄大于25岁的人
DELETE FROM person WHERE age>25;
-- 清空表操作
DELETE FROM person
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 创建数据库day1db 字符集utf8并使用
create database day1db charset=utf8;
use day1db;
-- 2. 创建t_hero表, 有name字段 字符集utf8
create table t_hero(name varchar(20))charset=utf8;
-- 3. 修改表名为hero
rename table t_hero to hero;
-- 4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段
alter table hero add money int;
alter table hero add id int first;
alter table hero add age int after name;
-- 5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888
insert into hero values(1,'李白',50,6888);
insert into hero values(2,'赵云',30,13888);
insert into hero values(3,'刘备',25,6888);
-- 6. 查询价格为6888的英雄名
select name from hero where money=6888;
-- 7. 修改刘备年龄为52岁
update hero set age=52 where name='刘备';
-- 8. 修改年龄小于等于50岁的价格为5000
update hero set money=5000 where age<=50;
-- 9. 删除价格为5000的信息
delete from hero where money=5000;
-- 10. 删除表, 删除数据库
drop table hero;
drop database day1db;
4. 数据类型
数字类型
整数:INT(m)和BIGINT(m).m表示的是长度 例如:m=5 存数字18 实际存储:00018
浮点数:DOUBLE(m,n)。m表示数字长度(整体数字的长度，包含小数)，n表示小数位 DOUBLE(5,3) 99.999
-- INSERT INTO XXX VALUES(12.9984) 实际插入数据时当精度超过可保存范围时，会进行四舍五入
字符类型
CHAR(n):定长字符串。每条记录实际占用的字节空间是定长的，不足的部分补充空字符来满足长度要求
优点:查询速度快 缺点:浪费磁盘空间
VARCHAR(n):(变长字符串)。最多存n指定的字节数对应的字符，实际保存是用多少占多少。(推荐)
优点:节省磁盘空间 缺点:查询速度慢
TEXT(n):可变长字符串，最大65535
日期时间类型
DATE:保存年月日
TIME:保存时分秒
DATETIME:保存年月日十分秒
TIMESTAMP:时间戳，记录UTC时间，从1970-01-01 00:00:00到表示的时间之间经过的毫秒
CREATE TABLE userinfo(
id INT,
name VARCHAR(30),
birth DATETIME,
salary DOUBLE(7,2)
);
INSERT INTO userinfo VALUES(1,'张三','1992-08-02 11:22:05',5000.59);
-- DATETIME类型在不指定时分秒时，默认为00:00:00
INSERT INTO userinfo VALUES(2,'李四','1999-05-16',6500.23);
-- DATETIME中年月日不可以省略
-- INSERT INTO userinfo VALUES(3,'王五','06:15:55',1900.788);
-- 插入DOUBLE类型数字时，如果小数部分超过范围会进行四舍五入
INSERT INTO userinfo VALUES(3,'王五','1987-04-13 06:15:55',1900.788);
INSERT INTO userinfo VALUES(4,'赵六','1985-03-23',2000);
INSERT INTO userinfo VALUES(5,'钱七','1986-01-22',40000);
-- 整数超过部分会报错
INSERT INTO userinfo VALUES(6,'老八','1976-02-18',140000);
SELECT * FROM userinfo
5. 约束
-- 约束是为表中某个字段添加特定的限制条件，只有符合条件的记录才可以保存
--
-- 主键约束:该字段非空且唯一，用该字段的值唯一表示一条记录
-- 非空约束:该字段的值不允许为空
-- 外键约束:实际开发中几乎不使用外键约束
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
age INT(3),
gender CHAR(1)
);
DESC student;
-- 由于name为NOT NULL，因此名字必须指定值，不指定会报错
INSERT INTO student VALUES(NULL,NULL,55,'m');
-- id本身的主键约束要求非空且唯一，但是由于有自增，所以当不指定时使用系统生成的自增值(有自增时就不要主动赋值)
INSERT INTO student VALUES(NULL,'刘桑',55,'m');
-- 主键有自增时，可以忽略主键字段的插入
INSERT INTO student(name,age,gender) VALUES('克晶',18,'f');
-- 有非空约束的字段插入数据时不可以忽略(因为忽略的字段默认插入NULL值)
-- INSERT INTO student(age,gender) VALUES(18,'f');
-- 允许为NULL的字段可以通过显示的指定NULL作为值插入
INSERT INTO student(name,age,gender) VALUES('传奇',22,NULL);
-- 也可以不指定字段来为该字段隐式的插入NULL值
INSERT INTO student(name,age) VALUES('国斌',33);
SELECT * FROM student
CREATE TABLE teacher(
id INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
age INT(3),
gender CHAR(1)
);
INSERT INTO teacher VALUES(1,'张三',22,'F');
-- 报错，主键字段不允许插入重复的值 Duplicate entry '1' for key 'PRIMARY'
-- INSERT INTO teacher VALUES(1,'李四',23,'M');
-- 报错，主键字段不能为NULL值 Column 'id' cannot be null
INSERT INTO teacher VALUES(NULL,'李四',23,'M');
-- NULL作为条件是，要使用IS NULL 或 IS NOT NULL
-- 查看性别为null的学生信息
-- 查询不到任何数据
SELECT * FROM student WHERE gender=NULL
SELECT * FROM student WHERE gender IS NULL;
SELECT * FROM student WHERE gender IS NOT NULL
6. DQL语句
DQL语句 数据查询语句
USE empdb;
-- DQL语句 数据查询语句
-- 基本语法:
-- SELECT 字段名1[,字段名2,... 或 *] FROM 表名
-- 查看emp表中每条记录的所有字段值
SELECT * FROM emp;
-- 查看每个员工的名字，职位，入职时间
SELECT name,job,hiredate FROM emp;
/* ----------------------------------------------------------------------------- */
-- WHERE子句，用来添加过滤条件，此时可以仅将满足条件的记录查询出来
-- 比较运算符:=,>,>=,<,<=,<>
-- 查看工资大于1000的员工的名字，职位，工资
SELECT name,job,sal
FROM emp
WHERE sal>1000
-- 查看职位除'人事'之外的所有员工的名字，工资和职位?
SELECT name,sal,job
FROM emp
WHERE job <> '人事'
-- !=操作不是所有数据库都支持
SELECT name,sal,job
FROM emp
WHERE job != '人事';
-- 查看部门编号为2的员工的名字，工资，职位，部门编号(dept_id)
SELECT name,sal,job,dept_id FROM emp WHERE dept_id=2
/* ----------------------------------------------------------------------------- */
-- AND,OR来连接多个条件
-- AND:都为真是才为真
-- OR:都为假是才为假
-- 查看2号部门工资高于1000的员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE dept_id=2 AND sal>1000
-- 查看职位是人事和销售的所有员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE job='人事' OR job='销售'
-- 查看人事部的所有员工和工资高于1000的销售部员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE job='人事' OR sal>1000 AND job='销售';
-- 查看人事部和销售部工资高于1000的员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE (job='人事' OR job='销售') AND sal>1000;
-- AND优先级高于OR，因此可以通过()来提高OR的优先级
/* ----------------------------------------------------------------------------- */
-- IN(列表) 值在列表中(等于列表中的其中之一)
-- 查看职位是人事和销售的所有员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE job IN ('人事','销售');
-- 查看职位是人事和销售以外的所有员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE job<>'人事' AND job<>'销售';
--不在列表中
SELECT name,sal,job,dept_id
FROM emp
WHERE job NOT IN ('人事','销售');
-- 查看工资在2000到3000之间的员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE sal>=2000 AND sal<=3000;
-- BETWEEN min AND max 在两者之间，即:>=min AND <=max
SELECT name,sal,job
FROM emp
WHERE sal BETWEEN 2000 AND 3000;
/* ----------------------------------------------------------------------------- */
-- DISTINCT用于去除指定列重复值的行 并且DISTINCT关键字必须紧跟SELECT
-- 查看公司有多少种职位?
SELECT DISTINCT job FROM emp;
-- 多列去重时，就是去除指定这些列的值的组合有重复的行
-- 去除职位与部门编号重复的行
SELECT DISTINCT job,dept_id FROM emp
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 查询2号部门工资高于1000的员工信息
SELECT *
FROM emp
WHERE dept_id=2 AND sal>=1000;
-- 2. 查询3号部门或工资等于5000的员工信息
SELECT *
FROM emp
WHERE dept_id=3 OR sal=5000;
-- 3. 查询工资在1000到2000之间的员工姓名和工资
SELECT name,sal
FROM emp
WHERE sal BETWEEN 1000 AND 2000;
-- 4. 查询工资不等于3000和5000的员工信息
SELECT *
FROM emp
WHERE sal NOT IN(3000,5000);
-- 5. 查询1号部门有哪几种不同的工作
SELECT DISTINCT job
FROM emp
WHERE dept_id=1
模糊查询
-- 模糊查询:LIKE
-- LIKE中两个通配符:_和%
-- _(下划线):表示任意的一个字符
-- %:表示任意个字符(0-多个)
/* ----------------------------------------------------------------------------- */
-- 查看姓孙的员工信息?(名字第一个字符是孙的员工)
SELECT name,sal,job
FROM emp
WHERE name LIKE '孙%';
-- 查看名字里含"悟"的员工?
SELECT name,sal,job
FROM emp
WHERE name LIKE '%悟%';
-- 查看名字第三个字是'精';
SELECT name,sal,job
FROM emp
WHERE name LIKE '__精';
-- 查看第二个字是'骨'的员工?
SELECT name,sal,job
FROM emp
WHERE name LIKE '_骨%';
-- 总结
-- %X%:字符串中包含X
-- %X:字符串以X结尾
-- X%:字符串以X开头
-- _X%:字符串第二个字符是X
-- %X_:倒数第二个字符是X
-- X%Y:字符串以X开头Y结尾
-- X_Y:字符串只有三个字，第一个是X，第三个是Y
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 查询名字姓猪的员工姓名
SELECT name
FROM emp
WHERE name LIKE '猪%';
-- 2. 查询名字中包含僧的员工信息
SELECT *
FROM emp
WHERE name LIKE '%僧%'
-- 3. 查询名字以精结尾的员工姓名
SELECT name
FROM emp
WHERE name LIKE '%精';
-- 4. 查询工作中包含销售并且工资大于1500的员工信息
SELECT *
FROM emp
WHERE job LIKE '%销售%'
AND sal>1500;
-- 5. 查询工作中第二个字是售的员工姓名和工作
SELECT name,job
FROM emp
WHERE job LIKE '_售%';
-- 6. 查询1号和2号部门中工作以市开头的员工信息
SELECT *
FROM emp
WHERE dept_id IN (1,2)
AND job LIKE '市%';
排序
-- ORDER BY子句，根据指定的字段排序查询结果集。该子句只能放在查询语句的最后一个子句上
-- 查看公司所有员工的工资，且工资从低到高排序
SELECT name,sal
FROM emp
ORDER BY sal;
-- 按照时间排序时，越早的时间越小，越晚的时间越大
SELECT name,sal,hiredate
FROM emp
ORDER BY hiredate;
-- 按照升序排序时，可以使用关键字ASC，但是通常不需要写，因为默认就是升序(从小到大)
SELECT name,sal,hiredate
FROM emp
ORDER BY hiredate ASC;
-- 按照工资从大到小排序(降序)，降序使用关键字DESC
SELECT name,sal
FROM emp
ORDER BY sal DESC;
-- 查看每个部门的工资排名
-- ORDER BY可以按照多字段排序，排序优先级时先将结果集按照第一个字段的值排序，保证第一个字段排序顺序的前提下
-- 将该字段值相同的记录中再按照第二个字段排序，依此类推。
SELECT name,dept_id,sal
FROM emp
ORDER BY dept_id,sal DESC
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 查询有领导的员工信息,按照入职日期(hiredate) 升序排序
SELECT *
FROM emp
WHERE manager IS NOT NULL
ORDER BY hiredate;
2. 查询1号部门中名字中包含八的员工信息
SELECT *
FROM emp
WHERE dept_id=1 AND name LIKE '%八%';
-- 3. 查询2号和3号部门中工资低于1500的员工信息
SELECT *
FROM emp
WHERE dept_id IN(2,3) AND sal<1500;
SELECT *
FROM emp
WHERE (dept_id=2 OR dept_id=3) AND sal<1500;
-- 4. 查询人事和程序员中工资高于2500的员工姓名,工资和工作
SELECT name,sal,job
FROM emp
WHERE job IN('人事','程序员') AND sal>2500;
-- 5. 查询不是CEO的员工中工资高于2000的员工姓名,工资和工作,并且按照工资降序排序
SELECT name,sal,job
FROM emp
WHERE job<>'CEO' AND sal>2000
ORDER BY sal DESC
分页
/* ----------------------------------------------------------------------------- */
-- 分页查询
-- 将满足查询条件的数据分段分批的查询出来。这可以减少不必要的系统开销。
-- 分页查询在SQL92标准没有定义，这意味着不同的数据库，分页查询方式完全不一样。
-- 在ORDER BY中使用LIMIT来完成的
-- LIMIT 跳过的记录数,请求的记录数(每页显示的记录数)
-- LIMIT (页数-1)*每页显示的记录数,每页显示的记录数
-- 按照工资降序后，每页显示3条，查看第二页
SELECT name,sal
FROM emp
ORDER BY sal LIMIT 3,3;
-- 按照工资降序后，每页显示3条，查看第三页
SELECT name,sal
FROM emp
ORDER BY sal LIMIT 6,3;
-- 按照工资降序后，每页显示4条，查看第3页
SELECT name,sal
FROM emp
ORDER BY sal LIMIT 8,4;
-- 查询的字段可以使用表达式
-- 查看每个员工的年薪是多少?
SELECT name,sal,sal*12 FROM emp;
-- 查询时也可以用函数的结果作为字段
-- 孙悟空的职位是销售 name+"的职位是"+job
SELECT CONCAT(name,'的职位是',job) FROM emp;
-- 查看每个员工的工资，奖金，工资+奖金
-- 数字与NULL运算结果就为NULL
SELECT name,sal,comm,sal+comm FROM emp;
-- NVL函数 用来替换NULL值
-- NVL(arg1,arg2) 当arg1不为NULL时则函数返回arg1的值，如果arg1为NULL则返回arg2的值
SELECT name,sal,NVL(comm,0) FROM emp;
/* ----------------------------------------------------------------------------- */
-- 别名
-- 我们可以为字段定义别名，也可以给表定义别名。
-- 为字段定义别名一般多用于:
-- 1:隐藏实际表字段名名
-- 2:为计算表达式或函数的结果只作为字段时定义可读性更好的字段名
SELECT name ename,sal salary FROM emp;
SELECT name,sal*12 salary FROM emp;
-- 支持的语法:
-- 字段名 别名
SELECT name,sal*12 salary FROM emp;
-- 字段名 as 别名
SELECT name as ename,sal*12 salary FROM emp;
-- 字段名 as '别名'
SELECT name as 'ename',sal*12 salary FROM emp;
-- 字段名 as "别名"
SELECT name as "ename",sal*12 salary FROM emp;
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 查询员工表中3号部门工资高于1500的员工信息
SELECT * FROM emp WHERE dept_id=3 AND sal>1500
-- 2. 查询2号部门员工或者没有领导的员工信息
SELECT * FROM emp WHERE dept_id=2 OR manager IS NULL
-- 3. 查询有领导的员工姓名,工资按照工资降序排序
SELECT name,sal
FROM emp
WHERE manager IS NOT NULL
ORDER BY sal DESC
-- 4. 查询2号和3号部门的员工姓名和入职日期hiredate按照入职日期降序排序
SELECT name,hiredate
FROM emp
WHERE dept_id IN (2,3)
ORDER BY hiredate DESC
-- 5. 查询名字中包含僧和包含精的员工姓名
SELECT name
FROM emp
WHERE name LIKE '%僧%' OR name LIKE '%精%'
-- 6. 查询工资高于2000的工作有哪几种?
SELECT DISTINCT job
FROM emp
WHERE sal>2000
-- 7. 查询工资升序第4页的2条数据
SELECT *
FROM emp
ORDER BY sal
LIMIT 6,2
7. 聚合函数
聚合函数(也称为多函数).聚合函数是用来多条记录统计为一条记录
MIN():求最小值
MAX():求最大值
COUNT():统计记录数
AVG():求平均值
SUM():求和
-- 查看公司收入收入的员工工资是多少?
SELECT MIN(sal) FROM emp
SELECT MIN(sal) 最低工资 ,MAX(sal) 最高工资,AVG(sal) 平均工资,SUM(sal) 工资总和 FROM emp;
-- 聚合函数忽略NULL值，在AVG中比较明显可以看出这一点，以下仅对4个有奖金的人取了个并均值，并非11个人的平均值
SELECT MIN(comm),MAX(comm),AVG(comm),SUM(comm) FROM emp;
SELECT * FROM emp
/* ----------------------------------------------------------------------------- */
-- 练习:
-- 1. 查询销售的平均工资
SELECT AVG(sal)
FROM emp
WHERE job='销售'
-- 2. 查询程序员的最高工资
SELECT MAX(sal)
FROM emp
WHERE job='程序员'
-- 3. 查询名字包含精的员工数量
SELECT COUNT(*)
FROM emp
WHERE name LIKE '%精%'
-- 4. 查询和销售相关的工作一个月工资总和
SELECT SUM(sal)
FROM emp
WHERE job LIKE '%销售%'
-- 5. 查询2号部门的最高工资和最低工资起别名
SELECT MIN(sal) 最低工资,MAX(sal) 最高工资
FROM emp
WHERE dept_id=2
分组
-- GROUP BY子句，分组。
-- GROUP BY 也是为统计服务的，所以是搭配在聚合函数上使用的。
-- 查看每个部门的平均工资是多少?
SELECT sal,dept_id FROM emp ORDER BY dept_id;
-- SELECT子句中不在聚合函数中的其他字段必须出现在GROUP BY子句中！
SELECT AVG(sal),dept_id FROM emp GROUP BY dept_id;
-- 每种职位的最高工资是多少?
SELECT MAX(sal),job FROM emp GROUP BY job
/* ----------------------------------------------------------------------------- */
-- 练习
-- 1.查询每个部门的最高工资
SELECT MAX(sal),dept_id FROM emp GROUP BY dept_id
-- 2.查询每个部门工资高于2000的人数
SELECT COUNT(*)
FROM emp
WHERE sal>2000
GROUP BY dept_id;
-- 3.查询每种工作的最低工资
SELECT MIN(sal),job FROM emp GROUP BY job;
-- 4.查询1号部门和2号部门的人数
SELECT COUNT(*),dept_id
FROM emp
WHERE dept_id IN (1,2)
GROUP BY dept_id
-- 5.查询平均工资最高的部门id和平均工资
SELECT AVG(sal),dept_id
FROM emp
GROUP BY dept_id
ORDER BY AVG(sal) DESC
LIMIT 0,1;
/* ----------------------------------------------------------------------------- */
-- 可以为函数或表达式字段取别名，然后利用别名排序。
SELECT AVG(sal) avg,dept_id
FROM emp
GROUP BY dept_id
ORDER BY avg DESC
LIMIT 0,1;
-- 查看部门平均工资高于2000的那些部门的平均工资具体是多少?
-- 聚合函数不能写在WHERE子句中
SELECT AVG(sal),dept_id
FROM emp
WHERE AVG(sal)>2000
GROUP BY dept_id
-- 原因是过滤时机并不相同
-- WHERE子句是添加过滤条件，在查询表中每条记录时，用于筛选记录。(查询表的过程中用于过滤的)
-- 想利用聚合函数的结果进行过滤时，应当已经是将表中数据查询出来(此时是WHERE过滤的时机)，并且对结果集进行了统计后
-- 得到的结果集再进行过滤
-- HAVING子句。HAVING子句是跟在GOURP BY子句之后，对分组统计的出的结果集再进行过滤的。
SELECT AVG(sal),dept_id
FROM emp
GROUP BY dept_id
HAVING AVG(sal)>2000;
-- 查看部门最低工资大于1000的部门的平均工资是多少?
SELECT AVG(sal),dept_id
FROM emp
GROUP BY dept_id
HAVING MIN(sal)>1000
-- 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.
SELECT SUM(sal),dept_id
FROM emp
WHERE manager IS NOT NULL
GROUP BY dept_id
HAVING SUM(sal) > 5400
子查询
-- 子查询
-- 嵌套在其他SQL语句中的查询语句被称为叫做"子查询"
-- 子查询通常用于要基于一个查询结果再进行操作的地方
-- 查看比公司平均工资高的那些员工的名字和工资是多少?
-- 公司的平均工资
SELECT AVG(sal) FROM emp;
SELECT name,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)
-- 查询工资高于2号部门平均工资的员工信息
SELECT name,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp WHERE dept_id=2)
-- 查询比沙僧工资低的员工信息?
SELECT name,sal
FROM emp
WHERE sal<(SELECT sal FROM emp WHERE name='沙僧')
-- 查询和孙悟空同职位的员工信息?
SELECT *
FROM emp
WHERE job = (SELECT job FROM emp WHERE name='孙悟空')
-- 查询和公司最低工资员工同属一个部门的员工信息?
SELECT *
FROM emp
WHERE dept_id = (SELECT dept_id
FROM emp
WHERE sal=(SELECT MIN(sal) FROM emp))
-- 查询比2号和3号部门工资都高的员工名字和工资?
SELECT *
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE dept_id IN (2,3))
-- 查询比2号和3号部门工资任意一个员工工资高的员工信息?
SELECT *
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE dept_id IN (2,3))
/* ----------------------------------------------------------------------------- */
-- 子查询分类(按查询结果集分类):
-- 单行单列子查询(结果集只有1个值)
-- 多行单列子查询(结果集有多个值)
-- 多行多列子查询(结果集是一个表)
-- 单列子查询通常用于过滤条件中使用。
-- 单行单列可以配合>,>=,=,<,<=使用
-- 多行单列可以配合ANY,ALL,IN使用。
-- 例如:
-- >ALL(子查询):大于子查徐结果集中最大的
-- <ALL(子查询):小于子查询结果集中最小的
-- >ANY(子查询):大于子查询结果集中最小的
-- <ANY(子查询):小于子查询结果集中最大的
-- IN(子查询):等于子查询结果集中的任意一个值
-- 多行多列子查询(结果集是一个表),通常就当做一张表使用，可以跟在FROM字句中
-- 或者跟在DDL语句中基于一个查询结果集创建表。
-- 将1号部门员工信息单独定义一张表名为emp_dept1
CREATE TABLE emp_dept1
AS
SELECT * FROM emp WHERE dept_id=1;
SELECT * FROM emp_dept1
-- 如果创建表基于的子查询中某个字段是一个表达式或函数时，要给该字段取别名，那么创建出来的表该字段会以别名作为
-- 字段名。
-- 创建一张表emp_dept_sal.该表记录了每个部门的薪资情况，包含最高工资，最低工资，平均工资，工资总和，部门编号
CREATE TABLE emp_dept_sal
AS
SELECT MAX(sal) max_sal,MIN(sal) min_sal,AVG(sal) avg_sal,SUM(sal) sum_sal,dept_id
FROM emp
GROUP BY dept_id
SELECT * FROM emp_dept_sal
-- 查看每个部门的最低薪水是多少?
SELECT min_sal FROM emp_dept_sal
-- 创建一张表emp_annual_salary，记录每个员工的名字，工资，年薪和部门，年薪字段用:a_salary,工资用salary
CREATE TABLE emp_annual_salary
AS
SELECT name,sal salary,sal*12 a_salary,dept_id
FROM emp
SELECT * FROM emp_annual_salary
-- 名字里含"精"的员工年薪是多少?
SELECT name,a_salary
FROM emp_annual_salary
WHERE name LIKE '%精%'
/* ----------------------------------------------------------------------------- */
SELECT * FROM dept
-- 关联查询
-- 查询结果集中的数据来自多张表，而表与表中数据之间的对应关系就是关联关系
-- 两张表就可以产生关联关系了，关联关系分为三类:
# 1:一对一 A表中的1条记录只唯一对应B表中的1条记录
# 2:一对多 A表中的1条记录可以对应B表中的多条记录
# 3:多对多 A表与B表双向都是一对多时，就是多对多关系。
# 关联查询就是基于多张表联合查询数据而形成一个结果集的过程，在关联查询中一个至关重要的点就是关联条件
# 原则:N张表关联查询至少要有N-1个连接条件。
# 缺失连接条件会产生笛卡尔积，该查询结果集的记录数是关联表中所有记录数乘积的结果，它通常是一个无意义的结果集，要
# 尽量避免产生
-- 关联查询语法:
-- SELECT 字段
-- FROM 表A，表B[，表C，表D...]
-- WHERE 过滤条件
-- AND 连接条件
-- 注意:连接条件必须与过滤条件同时成立!!
-- 笛卡尔积:产生了44条数据，将emp表每条记录都与dept表每条记录产生一条记录。
SELECT *
FROM dept;
-- 当表中出现了同名字段时，为了查询区分字段来自于哪张表，我们可以在字段名前用"表名."来标识
SELECT emp.name,emp.sal,emp.dept_id,dept.name,dept.loc
FROM emp,dept
-- 还可以为表取别名，用"别名.字段名"也可以标明查询的是那张表上的字段
SELECT e.name,e.sal,e.dept_id,d.name,d.loc
FROM emp e,dept d
-- 实际关联查询要添加连接条件
-- 连接条件最常见的就是等值连接。
-- 查看每个员工的名字，工资，部门编号以及所在的部门名称和所在地区
SELECT e.name,e.sal,e.dept_id,d.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
/* 注:emp表上的dept_id保存的值是dept表中主键字段的值，因此emp表中dept_id与dept表id值一样的记录才会被查询
出来作为一条记录显示在结果集中。
当一张表上的某个字段保存的是另一张表中的主键字段值时，这个字段就被称为"外键"
关联关系中经常用A.主键=B.外键作为连接条件。
*/
-- 查看在天庭工作的人都有谁?
SELECT e.name,e.sal,e.job,d.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
AND d.loc='天庭'
-- 名字里含“飞”的人来自哪里?
SELECT e.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
AND e.name LIKE '%飞%'
-- 天庭的最高工资?
SELECT MAX(e.sal),d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
AND d.loc='天庭'
GROUP BY d.loc
-- 查看每个地区的平均工资?
SELECT AVG(e.sal),d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
GROUP BY d.loc
