库表管理
#库的管理
/*
创建：create
修改：alter
删除：drop
*/
#一、库的管理
#1、库的创建
CREATE DATABASE IF NOT EXISTS books;
#2、库的修改
ALTER DATABASE books CHARACTER SET gbk;
#3、库的删除
DROP DATABASE IF EXISTS books;
#表的管理
#1、表的创建
/*
create table 表名(
列名 列的类型【(长度) 约束】
列名 列的类型【(长度) 约束】
。。。 。。。
列名 列的类型【(长度) 约束】
)
*/
CREATE TABLE book(
id INT,#书编
bName VARCHAR(20),#书名
price DOUBLE,#价格
author `id`INT,#作者编号
publishDate DATETIME#出版日期
);
DESC book;
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
#2、表的修改
#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#③添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE;
#④删除列
ALTER TABLE book DROP COLUMN annual;
#⑤修改表名
ALTER TABLE book RENAME TO book_author;
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国');
#3.表的复制
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
#3.只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
视图
#表
USE aoce
CREATE TABLE student(
stuname VARCHAR(20),
stuage INT
);
INSERT INTO student VALUES ('aoce',23);
DROP TABLE student;
DROP DATABASE aoce;
UPDATE student SET stuname = 'Main' WHERE stuname = 'a';
DELETE aoce FROM student aoce WHERE aoce.`stuname`='aoce';
SELECT * FROM student;
#视图
#创建
CREATE VIEW v1
AS
SELECT *
FROM student
WHERE student.`stuname` = 'Main';
#使用
SELECT * FROM v1;
DROP VIEW v1;
数学函数
#二、数学函数
#1. round 四舍五入
SELECT ROUND(1.65);
SELECT ROUND(1.567,2);
#2. ceil 向上取整
SELECT CEIL(1.52);
#3. floor 向下取整
SELECT FLOOR(1.52);
#4. truncate 截断
SELECT TRUNCATE(1.69,1);
#5. mod 取余
SELECT MOD(-10,-3); #a-a/b*b
#三、日期函数
#1. now 返回当前习通日期+时间
SELECT NOW();
#curdate 返回当前习通日期
SELECT CURDATE();
#curtime 返回当前时间
SELECT CURTIME();
#可以获取指定的部分：年、月、日、时、分、秒
SELECT YEAR(NOW());
SELECT MONTHNAME(NOW()) 月;
#2. str_to_date:将日期格式的字符转换成指定格式的日期
#STR_TO_DATE('9-13-1999','%m-%d-%Y')
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');
#3. date_format：将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');
#四、其他函数
SELECT VERSION();
SELECT DATABASE(); #查看当前的库
SELECT USER();
#五、流程控制函数
#1. if函数：if else 的效果
SELECT
IF(10 > 5, '大', '小') ;
#2. case函数的使用一:
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
... ...
else 要显示的值n或语句n;
end
*/
#案例1：查询员工工资，要求
/*
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍
其他部门，显示工资为1倍
*/
SELECT
salary,
department_id,
CASE
department_id
WHEN 30
THEN salary * 1.1
WHEN 40
THEN salary * 1.2
WHEN 50
THEN salary * 1.3
ELSE salary
END AS 新工资
FROM
employees ;
#case函数的使用二:
/*
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/
#案例：查询员工工资
/*
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则，显示D级别
*/
SELECT
salary,
CASE
WHEN salary > 20000
THEN 'A'
WHEN salary > 15000
THEN 'B'
WHEN salary > 10000
THEN 'C'
ELSE 'D'
END
FROM
employees ;
分组函数
#二、分组函数
/*
功能：用作统计使用，有称为聚合函数或统计函数
分类：
sum 求和、svg 平均值、max 最大值、min 最小值、count 计算个数
特点：
1.sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和一个关键字搭配使用
4.count函数的
一般使用COUNT(*)统计行数
5.和分组函数一同查询的字段要求是group by后的字段
*/
#1、简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#2、参数支持哪些类型
#不支持字符型
SELECT SUM(last_name,AVG(last_name) FROM employees;
#支持字符型
SELECT MAX(last_name),MIN(last_name) FROM employees;
#几乎支持所有（支持不为null的）
SELECT COUNT(last_name)FROM employees;
#4、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#5. count函数的详细介绍
SELECT COUNT(salary) FROM employees;#返回含有多少不同类型
SELECT COUNT(*) FROM employees; #统计行数
SELECT COUNT(1) FROM employees; #统计行数，写2都可以
/*效率：
NYISAM存储引擎下，COUNT(*)的效率最高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)效率高一些
*/
#6、和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;
练习
#创建表
CREATE DATABASE stident;
#添加列
CREATE TABLE students(
sName CHAR(10),
sAge INT
);
#添加行
INSERT INTO students VALUES
('Aoce',23),
('Main',22),
('Aine',20);
ALTER TABLE students ADD COLUMN id INT;
ALTER TABLE students MODIFY COLUMN id INT AUTO_INCREMENT;
SET auto_increment_increment = 1;
SELECT * FROM students;
USE students
CREATE VIEW s1
AS
SELECT * FROM students;
DROP VIEW s1;
1. 基础查询
#进阶1：基础查询
/*
语法：
select 查询列表
from 表名
类似与：System.out.println(打印东西）
特点：
1. 查询列表可以是：表中的字段、常量值、表达式、函数
2. 查询的结果是一个虚拟的表格
*/
USE employees;
@1. 查询表中的单个字段
SELECT
last_name
FROM
employees ;
@2. 查询表中的多个字段
SELECT
`last_name`,
`salary`,
`email`
FROM
employees ;
@3. 查询表中的所有字段
SELECT
*
FROM
employees ;
#4.查询常量值
SELECT 100;
SELECT 'john';
#5.查询表达式
SELECT 100*98;
#6.查询函数
SELECT VERSION();
#7.起别名
/*
1.便于理解
2.如果要查询的字段有重名的情况，可以使用别名区分开来
*/
#方式一：使用AS
SELECT 100*98 AS 结果;
SELECT
`last_name` AS 姓,
`first_name` AS 名
FROM
employees ;
#方式二：使用空格
SELECT `last_name` 姓,`first_name` 名 FROM employees;
#案例：查询salary，显示结果为 out put
SELECT salary AS `out put` FROM employees;
#8.去重
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
#9.+号的作用
/*
mysql中的加号只有一个功能：运算符
select 100+90;两个操作数都为数值型，则做加法运算
select '123'+90;其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90;如果转换失败，则将字符数值转换成0
select null+0;只要其中一方为null，结果肯定为null
*/
#案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS 结果;
SELECT IFNULL(NULL,0); #判断是否为null,为null返回0
SELECT
CONCAT(concatlast_name,first_name) AS 姓名
FROM
employees ;
2. 条件查询
#进阶2：条件查询
/*
语法：
select 查询列表 第三步
from 表名 第一步
where 筛选条件; 第二步
分类：
一、按条件表达式筛选
条件运算符：> < = !=(<>) >= <=
二、按逻辑表达式筛选
逻辑运算符：&& || ! and or not
三、模糊查询
like
between and
in
is null
*/
#一、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary > 12000;
#案例2：查询部门编号不等于90的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
departent_id <> 90;
#二、按逻辑表达式筛选
#案例1：查询工资在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000 AND salary <= 20000;
#案例2：查询部门编号不是在90到110之间，或则员工工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
department_id < 90 OR department_id > 110 OR salary > 15000;
#三、模糊查询
/*
like
特点：
1.一般和通配符搭配使用
通配符：
% 任意多个字符，包含0个字符
_ 任意单个字符
between and
in
is null is not null
*/
#1.like
#案例1：查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#案例2：查询员工名中第三个字符为e,第五个字符为a的员工和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__r_a%';
#案例3：查询员工名中第二个字符为下划线的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%'; #转义
last_name LIKE '_$_%' ESCAPE '$'; #自定义转义字符
#2.between and
/*
特点：
1.包含临界值
2.提高语言的简洁度
3.两个临界值不能颠倒
*/
#案例1：查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
#employee_id >= 100 and employee_id <= 120;
employee_id BETWEEN 100 AND 120;
#3.in
/*
含义：
1.判断某字段是否属于in列表中的某一项
2.in列表中的类型必须一致或兼容
*/
#案例：查询员工的工种编号是： IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
#job_id = 'IT_PROG' OR job_id = 'AD_VP' or job_id = 'AD_PRES';
job_id IN ('IT_PROG','AD_VP','AD_PRES');
#4.is null
/*
= <> 不能判断null值，需要用is null 或 is not null 判断
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
#commission_pct = null; err
commission_pct IS NULL;
#安全等于：<=>
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
#commission_pct = null; err
commission_pct <=> NULL;
#案例2：查询奖金为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;
#2.查询员工号为176的员工的姓名和部门号和年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL (commission_pct)) AS 年薪
FROM
employees;
3. 排序查询
#进阶3：排序查询
/*
引入：
select * from employees;
语法：
select 查询列表
from 表
【where 表】
order by 排序列表 asc | desc
特点：
1. asc代表的是升序，desc代表的是降序
如果不写，默认是升序
2. order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
3. order by 子句一般是放在查询语句的最后面，limit子句除外
*/
#案例1：查询员工信息，要求工资从高到低
SELECT *
from
employees
order by
salary desc;
#案例2：查询部门编号>=90的员工信息，按入职事件的先后进行排序
select
*
from
employees
where
department_id >= 90
order by
hiredate asc;
#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
select
*,
salary*12*(1+ifnull(commission_pot)) 年薪
from
employees
order by
salary*12*(1+ifnull(commission_pot)) desc;
#案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
select
*,
salary*12*(1+ifnull(commission_pot)) 年薪
from
employees
order by
年薪 desc;
#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
select
length(last_name) 字节长度,
last_name,
salary
from
employees
order by
length(last_name) desc;
#案例6：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
select
*
from
employees
order by
salary asc,
employees_id desc;
4. 常见函数
#进阶4：常见函数
/*
功能：类似Java的方法
调用：
select 函数名(实参列表) 【from 表】;
特点：
1. 函数名
2. 函数的功能
分类：
1. 单行函数
如：concat、length、ifnull等
2. 分组函数
功能：做统计使用，有称为统计函数、聚合函数、组函数
*/
#一、字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰');#这里占3个字节
SHOW VARIABLES LIKE '%char%';#字符集
#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
#3.upper、lower
SELECT UPPER('john');#变大写
#4.substr、substring
SELECT SUBSTR('李莫愁爱上了陆展远',7) output;
SELECT SUBSTR('李莫愁爱上了陆展远',1,3) output;
#案例1：姓名中的首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR('aocE',1,1)),LOWER(SUBSTR('aocE',2))) output;
#5.instr、返回指定字符出现的第一出现的位置，没有返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS output;
#6.trim、去掉前后指定的重复字符
SELECT LENGTH(TRIM(' 张翠山 ')) AS output;
SELECT LENGTH(TRIM('a' FROM 'aaaa张aa翠山aaaa')) AS output;
#7.lpad、用指定的字符从左填充到指定长度，不足则从右端截断
SELECT LPAD('殷素素',10,'*') AS output;
SELECT LPAD('殷素素',2,'*') AS output;
#8.rpad、用指定的字符从右填充到指定长度，不足则从右端截断
SELECT RPAD('殷素素',10,'*') AS output;
SELECT RPAD('殷素素',2,'*') AS output;
#9.replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS output;
5. 分组查询
#进阶5：分组查询
/*
语法：
select 分组函数，列（要求出现在group by的后面）
form 表【table】
[where condition]
[group by group_by_expression]
注意：
查询列表必须特殊，要求是分组函数和group by 后出现的字段
特点：
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果表 group by子句的后面 having
1.分组函数做条件肯定是放在having子句中
2.能用分组前筛选的尽量分组前筛选
2、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用得较少）
3、也可以添加排序（放在所有分组之后）
*/
#引入：查询每个部门的平均工资
SELECT AVG(salary) FROM employees;
#简单的分组查询
#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE emal LIKE '%a%'
GROUP BY department_id;
#添加复杂的筛选条件
#案例1：查询哪个部门的员工个数>2
#1查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#2根据1的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#按表达式分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#1.查询每个长度的员工个数
#2.添加筛选条件
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#按多个字段分组
#案例1：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id;
HAVING svg(salary)>10000
ORDER BY AVG(salary) DESC;
6. 连接查询
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql192标准：仅仅支持内连接
sql199标准【推荐】：支持内连接+外连接（左外、右外）+交叉连接
按功能分类：
内连接：
等值连接
非等职连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
*/
SELECT NAME,boyName FROM boys,beauty;
SELECT NAME,bouName FROM boys,beauty
WHERE beauty.boufriend_id = boys.id;
#一、sql192标准
#1、等值连接
/*
1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.一般需要为表起别名
4.可以搭配所有子句使用，比如排序
*/
#案例1：查询女神名和对应的男神名
SELECT
NAME,
bouName
FROM
boys,
beauty
WHERE beauty.boufriend_id = boys.id ;
#案例2、查询员工名和对应的部门名
#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM department_name d,location l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';
#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM department d,location l
WHERE d.location_id = l.department_id
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT department_name,manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_name = e.manager_id
AND commission_pct IS NOT NULL
GROUP BY department_name,manager_id;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#7、可以实现三表连接
#案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
#2、非等值连接
浙公网安备 33010602011771号