MySQL学习记录

基础查询

/*
select 查询列表 from 表名;
特点：
	1.查询的表可以是：表中的字段，表达式值，函数
	2.查询打结果是虚拟的表格（临时）
*/

#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.为字段起别名

SELECT 100%98 AS 100对98取模的结果
SELECT last_name AS "姓",first_name AS 名 FROM employees

#8.去重(DISTINCT)

#案例：查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees

#9.+号的作用

/*
mysql中的 + 号只能作为运算符来使用

select 100+90 	   两个数都为数值型，则做加法运算
select '123'+90    一个为字符型一个为数值型，则试图将字符型转换为数值型，若转换成功，则进行运算
select  'jack'+90  若转换失败，则将字符型看作 0 参与计算
select null+0      只要一方为null，则结果一定为null	
*/

#案例：查询员工名和员工姓，将其连接成一个字段，显示其姓名
#使用 concat（str1，str2，...）

SELECT 
	CONCAT(last_name,first_name) AS 姓名
FROM
	employees

#条件查询

/*
语法：
	select
		查询列表
	from
		表名
	where
		筛选条件
		
分类：
	一.按条件表达式筛选
	条件运算符：  >  <  =  !=  <>  >=  <=
	
	二.按逻辑表达式筛选
	逻辑运算符： &&  ||  !   and  or  not
	
	三.模糊查询
		like
		between and
		in
		is null
		
*/

#一.按条件表达式筛选

#案例：查询工资 > 12000 的员工信息

SELECT
	*
FROM
	employees
WHERE
	salary>12000

#二.按逻辑表达式筛选

#案例：查询工资在10000 - 20000之间的员工名，工资及奖金
SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary>10000 && salary<20000

#三.模糊查询

#1.like

#案例1：查询员工名中包含字符a的员工信息
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%'
	
#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名
SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '__n_l%'

#2.betwend and

#案例：查询员工编号在100到200之间的员工信息
SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 200

#3.in

#案例：查询员工的编号是 IT_PROG或AD_VP或AD_PRES的员工名和员工工种编号
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN('IT_PROG','AD_VP','AD_PRES')

#4.is null

#案例：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL  # is not null 则可查询非空的值

#安全等于 <=>

#案例：查询没有奖金且工资为12000的员工名和奖金率
SELECT
	last_name,
	commission_pct,
	salary
FROM
	employees
WHERE
	commission_pct <=> NULL AND salary<=>12000

/*
is null 和 <=> 的区别
	is null:仅仅可以判断 NULL 值，可读性较高
	    <=>:既可以判断NULL值，又可以判断普通类型的值，可读性较差
*/

##排序查询

/* 语法： select 查询列表 form 表 where 筛选条件 order by 排序列表
asc/desc

特点：
1.asc:升序
2.desc：降序 不写默认是升序
3.order by 中可以支持单个字段，多个字段，表达式，函数，别名
4.order by一般放在查询语的最后面，limit句子除外 */

 #案例：查询员工信息，要求员工工资从高到低排序
 
 SELECT * FROM employees ORDER BY salary DESC

 #案例：查询员工信息，要求先按员工工资升序，再按员工编号降序（按多个字段排序
 
 SELECT
	*
 FROM
	employees
ORDER BY
	salary ASC,employee_id DESC

##常见函数、

/* 分类：
1.单行函数 如 concat，length，ifnull等
2.分组函数 功能：做统计使用，又称为统计函数，聚合函数
*/

#一：字符函数

#1.length 获取参数值的字节个数

SELECT LENGTH('Jack')
SELECT LENGTH('看看看')

#2.concat 拼接字符串

SELECT	CONCAT (last_name,'_',first_name) AS 姓名 FROM employees

#3.upper,lower 改变大小写

#案例：将姓变大写，名变小写，然后拼接

SELECT	CONCAT (UPPER(last_name),'_',LOWER(first_name)) AS 姓名 FROM employees

#4.substr

#注意：索引是从1开始
#截取从指定索引处后面的所有字符
SELECT SUBSTR('这是一段测试用例',4)

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('这是一段测试用例',1，3)

#5.instr 返回字串第一次出现的索引，如果找不到，则返回0

SELECT INSTR('这是一段测试用例','测试') AS 字串出现的位置

#6.trim 去除前后空格或指定的内容

SELECT TRIM('     测试测试          ') AS out_put

SELECT TRIM('a' FROM 'aaaaaaaaaaaaaaaaaaaa测a试aaa测aa试aaaaaaaa') AS out_put

#7.lpad，rpad 用指定的字符实现左右填充指定长度

SELECT LPAD('测试文字',8,'*')

SELECT RPAD('测试文字',8,'*')

#8.replace 替换（有多个符合的可替换文字时，全部会被替换）

SELECT REPLACE('这是一段测试测试文字','测试','被替换')

#二：数学函数

#1.round 四舍五入

SELECT ROUND(-1.55)
SELECT ROUND(1.567,2)

#2.ceil 向上取整,返回大于等于改参数的最小整数

SELECT CEIL(1.52)

#3.floor 向下取整 返回小于等于改参数的最小整数

SELECT FLOOR(1.52)

#4.truncate 截断

SELECT TRUNCATE(1.699999,1)

#5.mod 取余

#符号取决于第一个参数
SELECT MOD(10,-3)
SELECT MOD(-10,3)

#三.日期函数

#now 返回当前系统日期+时间
SELECT NOW()

#2.curtime 返回当前时间，不包含日期

SELECT CURTIME()

#3.year，month，day,hour,mimute,second 可以获取指定的部分，如年，月，日，时，分，秒

SELECT YEAR(NOW()) AS 年
SELECT MONTH(NOW()) AS 月
SELECT DAY(NOW()) AS 日
SELECT HOUR(NOW()) AS 时
SELECT MINUTE(NOW()) AS 分
SELECT SECOND(NOW()) AS 秒

#4.str_to_date 将字符通过指定格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') 

#5.date_format 将日期转化为字符

#案例：查询有奖金的员工和入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL

#四：其他函数

SELECT VERSION()
SELECT DATABASE()
SELECT USER()

#五：流程控制函数

#1. if函数

SELECT IF(10 < 5,'大','小')

#2.case 函数

/*
语法一：
	case 要判断的字段或表达式
	when 常量1 then 要显示的值1或语句1
	when 常量1 then 要显示的值2或语句2
	when 常量1 then 要显示的值3或语句3
	...
	else 要显示的值n或语句n
	end
	
	
	

语法二：
	case 
	when 条件1 then 要显示的值1或语句1
	when 条件1 then 要显示的值2或语句2
	when 条件1 then 要显示的值3或语句3
	...
	else 要显示的值n或语句n
	end
*/

#案例：查询员工的工资，要求
#部门号=30，显示工资为1.1倍
#部门号=40，显示工资为1.2倍
#部门号=50，显示工资为1.3倍
#其他部门，显示为原工资

SELECT salary AS 原始工资,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

##分组函数

#1.sum 求和
SELECT SUM(salary) FROM employees
#2.avg 求平均值
SELECT AVG(salary) FROM employees
#3.min 求最小值
SELECT MIN(salary) FROM employees
#4.max 求最大值
SELECT MAX(salary) FROM employees
#5.count 计算个数（非null个数
SELECT COUNT(salary) FROM employees

##分组查询

/*
语法：
	select 分组函数，列（要求出现在group by后面）
	from 表
	(where 筛选条件)
	group by 分组的列表
	（order by 子句）

注意：
	查询列表比较特殊，要求是分组函数和group by后出现的字段
	
特点：
	1.分组查询中的筛选条件分为两类
			数据源		位置			关键字
	分组前筛选	原始表		group by子句的前面	where
	分组后筛选	分组后的结果	group by子句的后面	having
	
	分组函数做条件，一定是放在having子句中
*/

#案例：查询每个工种的最高工资


SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY salary

#案例：查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id

#按表达式或函数分组

#案例：按员工姓名长度分组，查询每一组的员工个数，筛选员工个数 > 5的有哪些

# ① 查询姓名每个长度的员工个数

SELECT COUNT(*),LENGTH(last_name) AS len_name
FROM employees
GROUP BY LENGTH(last_name)
# ② 添加筛选条件
HAVING COUNT(*) > 5

#按多个字段分组

#案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id

/*
连接查询：
按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全外连接
	交叉连接

*/

一.ql92标准

#1.等值连接

#案例：查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id

#案例：查询有奖金的员工名，部门名

SELECT last_name,department_name,commission_pct
FROM employees,departments
WHERE employees.department_id = departments.department_id
AND employees.commission_pct IS NOT NULL

#案例：查询每个城市的部门个数

SELECT COUNT(*) AS 个数,city
FROM departments AS d,locations AS l
WHERE d.location_id = l.location_id
GROUP BY city

#非等值连接

#案例：

#自连接

#：案例：查询员工名和员工的领导编号

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id

#二.sql99

/*
语法：
	select 查询列表
	from   表1 别名 （连接类型）
	join   表2 别名 
	on     连接条件
	（where）
	（group by）
	（having）
	（order by）
	

*/

#内连接

/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

分类：
	等值连接
	非等值连接
	自连接
*/

#等值连接

#案例：查询员工名，部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id

#非等值连接

#自连接
#查询员工的名字，上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id

#外连接

/*
应用场景：用于查询一个表中有，另一个表中没有的记录

特点：
	外连接的查询结果为主表中的所有记录
	如果从表中有和他匹配的，则显示匹配值
	如果从表中没有和他匹配的，则显示null
	外连接查询结果 = 内连接查询结果 + 主表中有而从表中 没有的记录
	
	左外连接：left join左边的是主表
	右外连接：right join右边的是主表
*/

#案例：查询哪个部门没有员工
#左外连接
SELECT d.*,e.employee_id
FROM departments d 
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL

#右外连接

SELECT d.*,e.employee_id
FROM employees e 
RIGHT OUTER JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL

#子查询

/*
含义：出现在其他语句中的select语句，称为子查询或内查询
      外部的查询语句，称为主查询或外查询
 分类：
按子查询出现的位置：
	select后面：
		仅支持标量子查询
	from后面：
		支持表子查询
	where或having后面：
		标量子查询
		列子查询
		行子查询
	exists后面（相关子查询）：
		表子查询

按结果集的行列数不同：
	标量子查询（结果集只有一行一列
	列子查询（结果集只有一列多行
	行子查询（结果集只有一行多列
	表子查询（结果集一般为多行多列
	
	
*/

#一.where或having后面

/*
1.标量子查询（单行子查询
2.列子查询（多行子查询
3.行子查询（多行多列

特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧

3.标量子查询，一般搭配着单行操作符使用
 > < >= <= = <>
 
 列子查询：一般搭配多行操作符使用
 in any/some,all

*/

#1.标量子查询

#案例：谁的工资比Abel高

#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查询员工信息，满足salary > ① 的结果
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
)

#案例：查询job_id与141员工相同，salary比143号员工多的员工信息
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

#③查询符合要求的员工信息
SELECT *
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
)

#列子查询（多行子查询

#案例：查询location_id是1400或1700的部门中的所有员工姓名
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查询员工姓名，要求部门编号是①中的结果
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
)

#行子查询（结果集一行多列或者多行多列

#案例：查询员工编号最小且工资最高的员工信息

#① 查询员工编号最小的员工编号
SELECT MIN(employee_id)
FROM employees

#②查询工资最高的员工信息
SELECT MAX(salary)
FROM employees

#③查询员工编号最小且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
) AND salary = (
	SELECT MAX(salary)
	FROM employees
)

##分页查询

/*
语法：
	select 查询列表
	from 表
	limit 起始条目索引，条目数
*/

#案例：查询前五条员工的信息

SELECT * FROM employees LIMIT 0,5
#或者省略起始条目索引2
SELECT * FROM employees LIMIT 5

#案例：查询有奖金的员工信息，并将工资较高的前十名显示出来
SELECT
*
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
salary DESC
LIMIT 10

##联合查询

#将多条查询语句的结果合并成一个结果
#一般用于多个表的查询

#案例：查询部门编号 > 90或邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90


SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE  department_id > 90

##DML语言

#一.插入语句

/*
语法：
	方式一：
	insert into 表名(列名,.....) values(值，...）
	方式二：
	insert into 表名 set 列名= 值，...
	
*/

#1.插入的值要与列的类型一致
#2.可以为null的列直接填入null或者省略

#修改语句

/*
语法：

1.修改单表记录
update 表名
set 列=新值，...

2.修改多表记录



*/

#删除语句

/*
方式一：
	单表：
	delete from 表 where 条件
	多表：
	delete 表一的别名，（表二的别名）
	from 表一  AS 别名
	inner|left|right| join 表二 AS 别名 on 连接条件
	where 筛选条件
方式二：
	truncate table 表
	删除整个表

区别：
	delete删除后若再插入数据，自增长列从断点开始
	delete删除有返回值，返回受影响的行数
	truncate删除后，若再插入数据，自增长列从1开始
	truncate删除无返回值
*/

##DDL语言

#一.库的管理

#1.库的创建

/*
语法：
	create database （if not exists）库名字
*/

#2.库的修改

RENAME DATABASE 旧库名字 TO 新库名
#3.更改库的字符集
ALTER DATABASE 库名 CHARACTER SET 字符集
#4.库的删除
DROP DATABASE （if exists）库名

#二.表的管理
#1.表的创建

/*
	create table 表名（
		列名 列的类型 （ 长度 约束）
		列名 列的类型 （长度 约束）
		列名 列的类型 （长度 约束）
		....
		列名 列的类型 （长度 约束）
	）

*/

#2.表的修改

#①修改列名
ALTER TABLE 表 CHANGE COLUMN 旧列名 新列名 （列的类型）
#②修改列的类型或约束
ALTER TABLE 表 MODIFY COLUMN 列名 类型或约束
#③添加新列
ALTER TABLE 表 ADD COLUMN 新列 类型
#④删除列
ALTER TABLE 表 DROP 要删除的列
#⑤修改表名
ALTER TABLE 表 RENAME TO 新表名

#3.表的删除

DROP TABLE 表名

#4.表的复制

#1.仅复制表的结构
CREATE TABLE 复制的表 LIKE 被复制的表
#2.复制表的结构加数据
CREATE TABLE 复制的表
SELECT * FROM 被复制的表
#3.复制表并复制一部分数据
使用查询语句做一下筛查就可以了
#4.仅仅复制表的部分结构
使用查询语句做一下筛查

##常见约束

/*

CREATE TABLE 表名（
	字段名 字段类型 约束
）

分类：六大约束
	not null：非空，用于保证字段不能为空
	defalut：默认，用于保证该字段有默认值
	primary key：主键约束，用于保证字段的值具有唯一性
	unique：唯一，用于保证该字段的值具有唯一性，但是该值可以为空
	check：检查约束（mysql中不支持）
	foreign key：外键，用于限制两表之间的关系，用于保证该字段的值必须来自
	主表的关联列的值
	

添加约束的时机：
	1.创建表时
	2.修改表时

添加约束的分类：
	列级约束：
		六大约束再语法上都支持，但外键约束不起效果
	表级约束：
		除了非空，默认，其他都支持

*/



/*
变量：
	系统变量
		全局变量
		会话变量
	自定义变量
		用户变量
		局部变量


*/