MySQL数据库详细总结

已于 2022-10-04 21:36:00 修改
阅读量1.5k 收藏 11
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2022-01-01 17:34:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45895026/article/details/112463704
版权

文章目录

一、常识简介

为什么学习数据库

  1. 持久化数据
  2. 方便检索
  3. 存储大量数据
  4. 共享、 安全
  5. 通过组合分析，获取新的数据

数据库的相关概念

DB: database数据库，存储一些列有组织、有格式数据的容器
DBMS:Database Management System 数据库管理系统，使用DBMS管理和维护DB
SQL：structureQueryLanguage结构化查询语言，程序员用于和DBMS通信的语言
SQL简介
SQL 是用于访问和处理数据库的标准的计算机语言。

什么是SQL?

  • SQL 指结构化查询语言
  • SQL 使我们有能力访问数据库
  • SQL 是一种 ANSI (美国国家标准化组织) 的标准计算机语言

SQL能干啥？

  • SQL 面向数据库执行查询
  • SQL 可从数据库取回数据
  • SQL 可在数据库中插入新的记录
  • SQL 可更新数据库中的数据
  • SQL 可从数据库删除记录
  • SQL 可创建新数据库
  • SQL 可在数据库中创建新表
  • SQL 可在数据库中创建存储过程
  • SQL 可在数据库中创建视图
  • SQL 可以设置表、存储过程和视图的权限

数据库存储数据的特点

  1. 数据先放在表中，表再放在库中
  2. 一个库可以有多张表，每张表都有自己的唯一标识名
  3. 一张表的设计，类似于JAVA中”类“的设计；表中的字段的设计，类似于属性的设计；表中的记录，类似于对象；表中所有的记录类似于对象的集合

orm:object relation mapping 对象关系映射

MySQL的常见命令和语法规范

①常见命令：

show database 			显示当前连接下所有数据库
show tables       		显示当前库中所有表
show tables from 库名   显示指定库中所有表
show columns from 表名 	显示指定表中所有列
use 库名                 打开/使用指定库

②语法规范：
不区分大小写
每条命令结尾建议用分号
注释：
#单行注释
--单行注释
/*多行注释*/

备份和恢复数据库

备份数据库（在DOS执行）
	mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
如果只备份表
	mysqldump -u 用户名 -p  数据库 表1 表2 表n  > 文件名.sql


恢复数据库（进入SQLyog再执行）
Source 文件名.sql
 可以通过复制进入sqlyog执行 也可以DOS命令执行

常用数据类型

在这里插入图片描述

表的创建删除修改

CREATE TABLE emp(
	id INT(11),
	`name` VARCHAR(32),
	`sex` CHAR(1),
	`birthday` DATE,
	`entry_date` DATETIME,
	`job` VARCHAR(32),
	`salary` DOUBLE,
	`resume` TEXT);
# 修改表 altertab.sql
# 增加varchar类型的image列在resume后面
ALTER TABLE emp 
	ADD image VARCHAR(32) NOT NULL DEFAULT '' 
	AFTER RESUME
DESC emp

# 修改job列 长度变为60
ALTER TABLE emp
	MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
# 删除sex列
ALTER TABLE emp
	DROP sex
# 表名改为employee
RENAME TABLE emp TO employee
DESC employee

# 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8
#列明 name修改成user_name

ALTER TABLE employee CHANGE NAME user_name VARCHAR(32) NOT NULL DEFAULT ''

CRUD语句

CREATE TABLE `goods` (
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE);
	
-- 添加数据
 INSERT INTO `goods` (id, goods_name, price)
	VALUES(10, '华为手机', 2000);
	
INSERT INTO `goods` 
	VALUES(20, '苹果手机', 3000);
	
SELECT * FROM goods; 

UPDATE employee SET salary = 5000;
SELECT * FROM employee

INSERT INTO employee
	VALUES(200, '小妖怪', '2000-11-11', '2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山','d:\\a.jpg');	
	
INSERT INTO employee
	VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 3000, '给大王捶背', 'd:\\a.jpg');
UPDATE employee SET RESUME = '给大王捶背的' WHERE user_name = '老妖怪' ;
	

UPDATE employee 
	SET salary = 5000
	WHERE user_name = '小妖怪'
	
DELETE FROM employee 
WHERE user_name = '老妖怪';

-- 删除表中 所有记录

DELETE FROM employee;

二、数据查询语言 DQL

DQL: 全称 Data QueryLanguage ,数据查询语言

基础查询

语法

SELECT 列名称 FROM 表名称

注释：SQL 语句对大小写不敏感。SELECT 等效于 select。

执行顺序

①from子句
②select子句

select first_name from employees;

特点

1、查询的结果集，是一个虚拟表
2、select 查询列表 类似于 Java中System.out.println(打印内容）;
      select后面跟的查询列表，可以有多个部分组成，中间用逗号隔开。
      例如： select 字段1, 字段2, 字段3, 表达式 from 表；

	System.out.println()的打印内容只能有一个

4、查询列表可以是：字段、表达式、常量、函数等

一、查询常量

select 100;

二、查询表达式

select 100 % 3;

三、查询单个字段

select last_name from `employees`;//单词比较长时在左边双击单词即可

四、查询多个字段

select `last_name`, `email`, `employee` from employee;

五、查询所有字段

星号（*）是选取所有列的快捷方式。

select * from `employee`;//按照表中当时自己定义的相关顺序
//要想更改顺序只能用“四”中的方法

规范写法:
当一条select语句比较长时，使用规范写法。使用快捷键F12：对齐格式 。 一般一句话完事，这样做为了美观。

SELECT 
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct` 
FROM
  employees ;

六、查询函数（调用函数、获取返回值）

select database();//查询当前使用的数据库
select version();//查询当前数据库服务器的版本
selcet user();//查询root用户
//如果有参数括号内可以填入实参列表

七、起别名

#方式一：使用as关键字

SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';

select last_name AS "姓 名" FROM employees;//若姓 名不加双引号会报错

#方式二：使用空格
SELECT USER()   用户名;
SELECT USER()  "用户名";
SELECT USER()  '用户名';

select last_name  "姓 名" FROM employees;

八、+ 的作用
需求：查询first_name和last_name拼接成的全名，最终起别名为：姓 名

mysql中的+的作用：
1、加法运算 ①两个操作数都是数值型 100 + 2.8
②其中一个操作数为字符型
将字符型数据强制转换成数值型，如果无法转换，则直接当作0处理

‘哈哈哈哈哈’ + 100 ====> 100;
③其中一个操作数为null

null + null ====> null
null + 100 ====> null

#方案1：使用 +   不成立，不能用这种方法
SELECT first_name + last_name AS "姓 名" FROM employees;

#方案2：使用concat拼接函数
SELECT CONCAT(first_name, last_name) AS  "姓 名" FROM employees;

九、distinct的使用

在表中，可能会包含重复值。这并不成问题，不过， 有时您也许希望仅仅列出不同（distinct）的值。
关键词 DISTINCT 用于返回唯一不同的值。

#需求 查询员工涉及到的部门编号有哪些

select distinct department_id from employees;

十、查询表的结构

DESC employees;
或
SHOW COLUMNS FROM employees;

ifnull(表达式1， 表达式2)

表达式1：可能为null的字段或表达式
表达式2：如果表达式1为空，则最终结果显示的值为表达式2

功能：如果表达式1为null, 则显示表达式2，否则显示表达式1.

--------------------------------------华丽分割线------------------------------------

条件查询

WHERE 子句
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

语法

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

执行顺序

①from子句
②where子句
③select子句

select last_name, first_name from employees where salary > 20000;
#先查询employees的大框架， 再查询薪水大于20000的那一行，再查找具体的列。

注意：SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号, 如上。

一、按关系表达式筛选
在这里插入图片描述

补充：也可以用!=, 但不建议 。

案例1：查询部门编号不是100的员工信息

select *
from employees
where departments_id <> 100;

案例2：查询工资 < 15000的姓名、工资

select last_name 
from employees
where salary < 15000;

二、按逻辑表达式筛选

逻辑运算符： and、 or、 not
补充：也可以使用 && || ！ 但不建议。

案例1：查询部门编号不是50 - 100之间员工姓名、部门编号、邮箱

# 写法一：
SELECT last_name, department_id, email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100)
# 写法二：
SELECT last_name, department_id, email
FROM employees
WHERE department_id <50 OR department_id > 100

案例2：查询奖金率>0.03或者员工编号在60 - 110 之间的员工信息

SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id >= 60 AND employee_id <= 110);

模糊查询

like 、 in 、 between and 、 is null

1、LIKE / NOT LIKE 操作符
功能 : 一般和通配符搭配使用，对字符型数据进行部分匹配查询，即用于在 WHERE 子句中搜索列中的指定模式。。
常见的通配符:

  • _ 仅替代一个字符
  • % 替代0个、1个或多个字符
  • [charlist] 表示字符列中的任何单一字符
  • [^charlist]或者[!charlist] 表示不在字符列中的任何单一字符

案例1：查询姓名中包含字符a的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%a%';

案例2：查询姓名中包含最后一个字符为e的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%e';

案例3：查询姓名中第一个字符为e的员工信息

SELECT *
FROM employees
WHERE last_name LIKE 'e%';

案例4：查询姓名中包含第三个字符为x的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '__x%';

案例5：查询姓名中包含第二个字符为x的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '_\_%';

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';//也可以是$以外的任何字符

案例6：查询姓名中包含字母a和e的员工姓名

 SELECT last_name
 FROM employees
 WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

2、in / not in 操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

功能：查询某字段的值是否属于指定的列表之内

#in / not in 

a in(常量值1, 常量值2, 常量值3, ...)
a not in(常量值1, 常量值2, 常量值3, ...)

案例1：查询部门编号是30/50/90的员工名、部门编号

SELECT last_name, department_id
FROM employees
WHERE department_id IN (30, 50, 90);
-- 相比于用逻辑运算符or来写更简单
-- or
SELECT last_name, department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

案例2：查询工种编号不是SH_cCLERK或IT_PROG的员工信息

SELECT  *
FROM employees
WHERE job_id NOT IN('SH_CLERK', 'IT_PROG');

3、between and / not between and 操作符

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

功能:判断某个字段的值是否介于（x, y)之间

案例1：查询部门编号是30 - 90之间的部门编号员、员工名


SELECT department_id , last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

案例2：查询年薪不是100000 - 200000之间的部员工名、工资、年薪

SELECT last_name, salary, salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE salary * 12 * (1 + IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

4、is null / is not null

 SELECT *
 FROM employees
 WHERE commission_pct IS NULL;
 
 SELECT *
 FROM employees
 WHERE commission_pct IS NOT NULL;

补充：
= 只能判断普通的内容
IS 只能判断NULL值
<==> 安全等于，既能判断普通内容，又能判断NULL值

排序查询

语法：

select 查询列表
from 表名
【where 筛选条件】
order by  排序字段1 排序方式1 , 排序字段2 排序方式2;

执行顺序

①from子句
②where 子句
③select子句
④order by 子句

特点:

  1. 排序列表可以是单个字段、多个字段、表达式、函数、列表、以及以上的组合。
  2. 升序排序， 通过关键字ASC, 一般默认为升序。
  3. 降序排序，通过关键字DESC。

一、按单个字段排序

案例1将员工编号>120的员工信息进行工资的升序

SELECT 
  * 
FROM
  employees 
WHERE employee_id > 120 
ORDER BY salary ASC ;

案例2将员工编号>120的员工信息进行工资的降序

SELECT 
  * 
FROM
  employees 
WHERE employee_id > 120 
ORDER BY salary DESC ;

二、按表达式排序
案例1：对有奖金的员工，按年薪降序

SELECT *, salary * 12 * (1 + IFNULL(commission_pct,0))
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY  salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;

三、按别名排序
案例1：对有奖金的员工，按年薪降序

SELECT *, salary * 12 * (1 + IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY  年薪 DESC;

补充：where句子里不能用别名，因为执行顺序的原因，先执行where再执行select.

四、按函数的结果排序
案例1：按姓名的字数长度进行升序排序

SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY LENGTH(last_name) ASC;

五、按多个字段排序
案例1：查询员工的姓名、工资、部门编号、先按工资升序、再按部门编号降序

SELECT last_name, salary, department_id
FROM employees
ORDER BY salary ASC, department_id DESC;

六、按列数排序
案例：按照第二列进行升序

SELECT * FROM employees ORDER BY 2;//升序
//SELECT * FROM employees ORDER BY 2 DESC;降序

函数

字符函数
函数用法
concat（） 拼接函数select concat(‘hello’,first_name,last_name) 备注 from employees;
length（） 获取字节长度select length(‘hello, 中国’); 长度为12，一个汉字3字节
char_length（）获取字符个数select char_length(‘hello,中国’); 8字符
substring() 截取子串substr(str, 起始索引(min = 1)， 截取的字符长度) or substr(str,起始索引）；
instr()获取字符第一次出现的索引SELECT INSTR(‘三打白骨精=’,‘白骨精’); 返回3
trim（） 去掉前后指定的字符，默认去掉前后空格SELECT TRIM(‘x’ FROM ‘xxxxxx虚 xxx 竹xxxxxxxx’) AS a;
lpad()/rpad() 左填充/右填充SELECT LPAD(‘木婉清’,10,‘a’);拿a在字符串左边填充至10个字符 SELECT RPAD(‘木婉清’,10,‘a’); 右填充
upper()/lower()变大写/变小写

案例：查询员工表的姓名，要求格式，姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名"OUTPUT"

--分步
SELECT UPPER(SUBSTR(first_name, 1, 1)) ,first_name FROM employees;
SELECT LOWER(SUBSTR(first_name, 2)), first_name FROM employees;
SELECT UPPER(last_name), last_name FROM employees;
--综上
SELECT CONCAT(UPPER(SUBSTR(first_name, 1, 1)),LOWER(SUBSTR(first_name, 2)),'_', UPPER(last_name)) "OUTPUT"
FROM employees;
数学函数
函数用法
strcmp()比较两个字符大小select strcmp(‘abc’, ‘aaa’);前面大返回1，后面大返回-1
left()/right() 截取子串SELECT LEFT(‘鸠摩智’,1); SELECT RIGHT(‘鸠摩智’, 1);
函数用法
ABS ()绝对值SELECT ABS(-2.4);
CEIL () 向上取整 返回>=该参数的最小整数SELECT CEIL(-1.09);
FLOOR() 向下取整，返回<=该参数的最大整数SELECT FLOOR(-1.09);
ROUND() 四舍五入SELECT ROUND(1.8712345);
truncate() 截断/保留几位SELECT TRUNCATE(1.8712345,1);
MOD() 取余SELECT MOD(-10,3);==>SELECT -10%3;
日期函数
函数用法
NOW() 当前日期及时间SELECT NOW();
curdate()当前日期SELECT CURDATE();
curtime()当前时间select CURTIME()
datediff()两个日期之差SELECT DATEDIFF(‘1998-7-16’,‘2019-7-13’);
DATE_FORMAT()将日期按照指定格式转换SELECT DATE_FORMAT(hiredate,‘%Y年%M月%d日 %H小时%i分钟%s秒’)入职日期 FROM employees;
STR_TO_DATE ()按指定格式解析字符串为日期类型SELECT * FROM employeesWHERE hiredate<STR_TO_DATE(‘3/15 1998’,‘%m/%d %Y’);
流程控制函数
函数用法
if()函数SELECT IF(100>9,‘好’,‘坏’);

需求1：如果有奖金，则显示最终奖金，如果没有，则显示0

SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金,commission_pct
FROM employees;

case函数
①情况1 ：类似于switch语句，可以实现等值判断

CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END

案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变

显示 部门编号，新工资，旧工资

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END  newSalary
FROM employees;

②情况2：类似于多重IF语句，实现区间判断

CASE 
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...

ELSE 结果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
 AS  a
 FROM employees;
加密和系统函数
-- USER() 查询用户
SELECT USER() FROM DUAL; -- 可以查看登录到mysql的有哪些用户，以及登陆的IP

-- database() 数据库名称

SELECT DATABASE() FROM DUAL;

-- MD5(str) 为字符串算出一个MD5 32 的字符串 常用（用户密码）加密

SELECT  LENGTH(MD5('kdz')) FROM DUAL;

 -- PASSWORD(str) -- 加密函数 MySQL 数据库的用户密码就是PASSWORD函数
 
 
 SELECT PASSWORD('hsdsdp') FROM DUAL;
分组函数（聚合函数）
# 分组函数
/*分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
	sum(字段名）：求和
	avg(字段名）：求平均数
	max(字段名）：求最大值
	min(字段名）：求最小值
	count(字段名）：计算非空字段值的个数
		
*/

#案例1：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数

SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary), COUNT(salary) 
FROM employees;

#案例2: 添加筛选条件

#查询emp表中记录数:
    SELECT COUNT(employee_id) FROM employees;
    
#查询emp表中有佣金的人数:
SELECT COUNT(salary) FROM employees;

#查询emp表中月薪大于2500的人数:
SELECT COUNT(salary) 
FROM employees 
WHERE salary > 2500;

#查询有领导的人数:
SELECT COUNT(manager_id) FROM employees;
#count的补充介绍

#1、统计结果集的行数，推荐使用count(*)

SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;

SELECT COUNT(1) FROM employees;#添加一个全是1的常量列
SELECT COUNT(1) FROM employees WHERE department_id = 30;

#2、搭配distinct实现去重的统计
#需求：查询有员工的部门个数
SELECT COUNT(DISTINCT(department_id))  FROM employees;


#思考：每个部门的总工资、平均工资？
SELECT SUM(salary) FROM employees WHERE department_id = 30;
SELECT SUM(salary) FROM employees WHERE department_id = 50;
#这样写很麻烦 需要很多条sql语句, 所以这里使用分组查询更为高效

分组查询

分组查询 ：
当需要分组查询时需要使用GROUP BY 子句， 例如查询每个部门的工资和，这说明要使用 部分 来分组（部门编号一样的放到一个组中，再每组统计一次工资）

#分组查询
/*
语法：
 select 查询列表
 from 表明
 where 筛选条件
 group by 分组列表
 having 分组后筛选
 order by 排序列表;
 
 执行顺序：
 ①from子句
 ②where子句
 ③group by 子句
 ④having子句
 ⑤select子句
 ⑥order by 子句
 
 特点：
 ①查询列表往往是 分组函数和被分组的字段 
 ②分组查询中的筛选分为两类
			筛选的基表（筛选谁）   使用关键词  位置
分组前筛选		原始表                 where       group by 前
分组后筛选		分组后的结果集         having      group by 后
#总结： group by 语句前面执行的筛选是分组前筛选 用where ，group by 后执行为分组后筛选 用having
where - group by - having
问题： 分组函数作条件（count(*) > 谁谁谁） 用having连接  只可能放在having后面！
*/

#1）简单的分组
#案例1:查询每个工种的员工平均工资

SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id
#案例2：查询每个领导的手下人数
SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

#2）可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的(先查询) 每个部门的最高工资（再分组）
SELECT MAX(salary) 最高工资, department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资, manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#3) 可以实现分组后的筛选（先分组，再查询）
#案例1：查询哪个部门的员工个数 > 5
#分析1： 查询每个部门的员工个数
SELECT COUNT(*) 员工个数,department_id
FROM employees
WHERE 
GROUP BY department_id

#分析2:在刚才的结果基础上，筛选哪个部门的员工个数>5
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5#(having 才支持后面加分组函数做条件，where不支持，而且having才支持分组后的筛选)

# 案例2：每个工种有奖金员工工资 > 12000的工种编号和最高工资

SELECT job_id, MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

# 案例3：领导编号 > 102的 每个领导手下的最低工资大于5000的最低工资
#分析1：查询每个领导手下员工的最低工资
SELECT MIN(salary) 最低工资, manager_id
FROM employees
GROUP BY manager_id
#分析2：
SELECT MIN(salary) 最低工资, manager_id
FROM employees
WHERE manger_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000; 

#4) 可以实现排序
#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
SELECT 
  MAX(salary) 最高工资,
  job_id 
FROM
  employees 
WHERE commission_pct IS NULL 
GROUP BY job_id 
HAVING MAX(salary) > 6000 
ORDER BY MAX(salary) ASC ;

#5) 按多个字段进行分组
#案例：查询每个工种每个部门的最低工资，并按照最低工资降序
#提示：工种和部门都一样，才是一组
SELECT MIN(salary) 最低工资, job_id, department_id
FROM employees
GROUP BY job_id, department_id

连接查询（多表查询）

SQL92语法

#连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

*/
#----------------------SQL92语法---------------------------
#一、内连接

#一）等值连接
/*


语法：
	select 查询列表
	from 表名1 别名1， 表名2 别名2 ，...（到这里就是一个笛卡尔乘积）
	where 等值连接的连接条件
特点：
	1、为了解决多表中的字段名重名问题，往往为表起别名，提高语义性
	2、表的顺序无要求
	3、多表等值连接的结果为多表的交集部分
	4、n表连接， 至少需要n - 1 个连接条件
	5、可以搭配前面介绍的所有子句使用， 比如排序、分组、筛选
	
*/
#①简单的量两表连接
USE myemployees;
#案例:查询员工名和部门名
SELECT last_name,`department_name`#select 在from之后， 可以用别名但不能使用原名
FROM employees e, departments d#(笛卡尔乘积)
WHERE e.`department_id`=d.`department_id`;

#②添加筛选条件
#案例1:查询部门编号>100的部门名和所在的城市名
SELECT `department_name`, `city`
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND d.`department_id` > 100;

#案例2：查询有奖金的员工名、部门名
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#案例3:查询城市名中第二个字符为o的部门名和城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE'_o%';
#③添加分组+筛选
#案例1:查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY l.`city`;

#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name, d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY deparment_name, d.manager_id;

#④添加分组+筛选+排序

#案例1:查询每个工种的员工个数和工种名，并按个数降序

SELECT job_title, COUNT(*) 员工个数
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;



#⑤三表连接
#案例:查询员工名、部门名、城市名
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`
AND city LIKE 's%';

#二）非等值连接
#案例1：查询员工的工资和工资级别
#先创建一个工资表
CREATE TABLE job_ grades
(grade_level VARCHAR(3) ,
 lowest_sal INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ( 'A', 1000, 2999);

INSERT INTO job_ grades
VALUES ('B', 3000, 5999);

INSERT INTO job_ grades
VALUES('C', 6000, 9999);

INSERT INTO job_ grades
VALUES('D', 10000, 14999);

INSERT INTO job_ grades
VALUES('E', 15000,24999);

INSERT INTO job_ grades
VALUES('F', 25000, 40000);

#查询
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest _sal
AND g.grade_level='A';

SQL99语法

#----------SQL99语法-----------
#一、内连接
/*
     语法：
	select 查询列表
	from表明
	【INNER】JOIN 表明2 别名
	ON 连接条件
	where 筛选条件
	group by 分组列表
	having 分组后筛选
	Order by 排序列表;

SQL92和SQL99的区别：
SQL99, 使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高了阅读性
	
*/

#一）等值连接
①简单链接
案例：查询员工名和部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d 
ON e.department_id = d.department_id;

②添加筛选条件
案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name, city
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id > 100;
③添加分组 + 筛选
#案例1：查询每个城市的部门个数
 SELECT COUNT(*), l.city
 FROM departments d
 JOIN locations l
 ON d.location_id = l.location_id
 GROUP BY l.city

④ 添加分组 + 筛选 + 排序
#案例1： 查询部门中员工个数 > 10的部门名， 并按员工个数降序
SELECT COUNT(*) 员工人数, d.department_name
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING 员工人数>10    #(having 后面也支持别名)
ORDER BY 员工人数 DESC;

#二）非等值连接
#案例: 查询部门编号在10 - 90之间的员工的工资级别，，并按级别进行分组

SELECT * FROM sal_grade;
SELECT last_name, salary, grade
FROM employees e
JOIN sal_grade g
ON e,salary BETWEEN g.min_salary AND g.max_salary
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;
#三）自连接

#案例：查询员工名和对应的领导名
SELECT e.last_name, m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`

#二、外连接（左外连接、全外连接、右外连接）
select 查询列表
from 表1 别名
left|right|full[outer] join 表2 别名
on 连接条件
where 筛选条件

子查询

/*
说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
外部的查询称为子查询或者外查询

分类：
按子查询出现的位置进行分类：
1、select后面
    要求：子查询的结果为单行单列
2、from后面
     要求：子查询的结果可以为多行多列
3、where或having 后面
     要求：子查询的结果必须为单列
		单行子查询
		多行子查询
4、exists后面
    要求:子查询结果必须为单列（相关子查询）
    

嵌套子查询(非相关子查询)：子查询的查询条件不依赖于父查询(子查询可以独立执行)，包括，
返回单个值的子查询
返回一个值列表(集合)的子查询
相关子查询：子查询的查询条件依赖于父查询   (子查询不能独立执行) 。


*/
#一）单行子查询
#案例：查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name	= 'Zlotkey'
	);
#查询工资比公司平均工资高的员工的员工号，姓名和工资.
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);
#二）多行子查询
in
any/some
all
--此处省略案例练习， 视频中有


#放在其他位置的子查询
放在where和having后面用的最多

分页查询

/*
应用场景：当页面上的数据，一页显示不全，则需要分页显示。
分页查询的sql命令请求数据库服务器->服务器相应查询到的多条数据->前台页面

语法：
select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序列表
limit 起始条目索引，显示的条目数
 
 执行顺序：
 1》 from子句
 2》 join子句
 3》 on、where、group by、 having 子句
 4》 select子句
 5》 order by 子句
 6》 limit 子句 
 
 特点：
 ①起始条目索引如果不写，默认是0
 ②limit后面支持两个参数
 参数一：显示的起始条目索引
 参数二：条目数
 
 公式：
 假如要显示的页数是page, 每页显示的条目数为size
 
 select * 
 from emploees
 limit  (page - 1) * 10, size
 page    size = 10
 1                  limit 0, 10
 2                  limit 10, 10
 3                  limit 20, 10
 4                  limit 30, 10
*/

#案例1：查询员工信息表的前五条
SELECT * FROM employees LIMIT 0,5;
#案例2：查询有奖金的，且工资较高的第11名到第20名
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10, 10;

联合查询

/*
说明: 当查询结果来自多张表，但多张表之间没有关联，这个时候往往使用联合查询，也成为union查询

语法：
select 查询列表 from 表1 where 筛选条件
        union
select 查询列表 from 表2 where 筛选条件

特点：
1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、union 实现去重查询
   union all实现全部查询 包含重复项
*/


#案例:  查询所有国家的年龄>20岁的用户信息
SELECT * FROM chinese  WHERE age > 20 UNION
SELECT * FROM usa WHERE uage > 20;
#案例：查询所有国家的用户姓名和年龄
SELECT uname, uage FROM usa
UNION
SELECT  `name` ,age FROM chinese

#案例3:union自动去重/union all 可以支持重复项

三、数据定义语言 DDL

Data Define Language数据定义语言，用于对数据库和表的管理和操作

#----------------库的管理-------------
#一、创建数据库
CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

#二、删除数据库
DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

#----------------表的管理-------------

#一、创建表
语法：
CREATE TABLE IF NOT EXISTS 表名(
	字段名 字段类型  【字段约束】,
	字段名 字段类型  【字段约束】,
	字段名 字段类型  【字段约束】,
	字段名 字段类型  【字段约束】,
	字段名 字段类型  【字段约束】
);


案例：没有添加约束的案例
CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT ,
	stuname VARCHAR(20),
	stugender CHAR(1)
	email VARCHAR(20),
	borndate DATETIME
);
#查看表的结构
DESC stuinfo;


#一)数据类型：
1、整型
TINYINT small INT INT BIGINT
2、浮点型
FLOAT(m,n)
DOUBLE(m, n)
DECIMAL(m, n)
m和n可选
3、字符型
CHAR(n):n可选， 不选默认为1
VARCHAR(n):n必选
TEXT n表示最多字符个数

4、日期型
DATE TIME DATETIME TIMESTAMP

5、二进制型
BLOB 存储图片数据

#二）常见约束
说明: 用于限制表中字段的数据，从而进一步保证数据表的数据是一致的、准确的、可靠的！

NOT NULL 非空：用于限制该字段为必填项
DEFAULT 默认：用于限制如果该字段没有显示插入值，则直接显示默认值
PRIMARY KEY 主键：用于限制该字段的值不能重复，设置为主键列的字段默认不能为空（not null）
	一个表只能有一个主键，当然可以是组合(复合)主键
		PRIMARY KEY(id, name) 两个都相同才算重复 实际开发中每个表往往都会设计一个主键
UNIQUE 唯一：用于限制该字段值不能重复 与主键的区别是如果没有指定 not null, 则unique字段可以有多个null

CHECK 检查
FOREIGN KEY 外键： 用于定义主表和从表之间的关系，外键约束要定义在从表上, 
主表则必须具有主键约束或者是unique约束，当定义外键约束后，
要求外键列数据必须在主表的主键列存在或是为null.

案例：添加约束
CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY,#添加了主键约束
	stuname VARCHAR(20) UNIQUE NOT null, #添加了唯一约束
	stugender CHAR(1) DEFAULT '男', #添加了默认约束
	email VARCHAR(20)  NOT NULL ,
	age INT CHECK(age BETWEEN 0 AND 100), #添加了检查约束, mysql不支持
	major INT,
	CONSTRAINT fk_strinfo_major FOREIGN KEY majorid referrences major(id)
);

#二、修改表
语法： ALTER TABLE 表明 ADD|modify（修改列的类型）|change（修改列名）column |DROP  COLUMN 字段名 字段类型 【字段约束】；

#1.修改表名
ALTER TABLE stuinfo RENAME TO students;

#2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL NOT NULL;

DESC students
#3.修改字段类型
ALTER TABLE students CHANGE COLUMN bornadate birthday TIMESTAMP NULL;

#4.修改字段类型
ALTER TABLE students MODIFY colunm birthday TIMESTAMP;

#5.删除字段
ALTER TABLE students DROP COLUMN birthday;
DESC students;

#三、删除表 √
DROP TABLE IF EXISTS students;

#四、复制表 √

#仅仅复制表的结构
CREATE TABLE newtable2 LIKE major;

#复制表的结构 + 数据
CREATE TABLE newtable3 SELECT * FROM girls.'beauty'

#复制employees表中的last_name,department_id, salary 字段到新表 emp表
CREATE TABLE emp 
SELECT last_name, department_ment_id, salary FROM employees.'employees'
WHERE 1=2;

四、数据操纵语言 DML

DML(Data Manipulation Language) 数据操纵语言： insert update delete
对表中的数据的增删改

插入数据

语法：
插入单行：insert into 表名(字段名1， 字段名2， 字段名3，..., ) values (值1， 值2 ，....)
插入多行：insert into 表名(字段名1， 字段名2， 字段名3，..., ) values (值1， 值2 ，....),(值1，值2，...),(值1，值2， ...);

特点：
1、字段和值列表一一对应
包含类型、约束等必须匹配
2、数值型的值，不用单引号
非数值型的值、必须使用单引号
3、字段顺序无要求

自增长列

--
/*
1、自增长列要求必须设置在一个键上，比如主键或唯一键
2、自增长列要求数据类型为数值型
3、一个表至多有一个自增长列
*/
1、可以通过可视化界面操作
2、create table gradeninfo(
	gradeID int primary key auto_increment,
	gradeName VARCHAR(20) 
);

select * from gradeinfo;
insert into gradeinfo values(NULL, '1年级'),(null, '2年级'),(null, '3年级')
或者写为
insert into gradeinfo(gradename)values( '1年级'),( '2年级'),('3年级');

删除数据

/*
数据的删除：
	方式一：delete语句
		语法：delete from 表名 where 筛选条件；也就是删除的直接就是一行
	方式2：truncate语句
		语法：truncate table 表名；
*/

#案例1：删除姓李所有信息
delete from stuinfo where stuname like '李%';

#案例2: 删除表中的所有数据
truncate table stuinfo;

#【面试题】delete 和 truncate的区别
1、delete可以添加where条件 可以删除部分数据 truncate不能添加where条件， 一次性清除所有数据
2、truncate的效率较高
3、如果删除带自增长列的表，使用delete删除后，重新插入数据，记录从断点处开始
使用truncate删除后，重新插入数据，记录从1开始
4、delete删除数据，会返回受影响的行数
truncate删除数据，不返回受影响的行数
5、delete删除数据，可以支持事务回滚
truncate删除数据，不支持事务回滚

★
常用的是delete  
等你删库跑路的时候，truncate就方便多了......

五、事务的演示 DCL

/*
	概念：由一条或多条sql语句组成，要么都成功，要么都失败
	
分类：
隐式事务：没有明显的开启和结束标记
	比如DML语句的insert、update、de;ete语句本身就是一条事务
	
显示事务：具有明显的开启和结束标记
	一般由多条sql语句组成，必须具有明显的开启和结束标记
步骤：
去校隐式事务自动开启的功能
1、开启事务
2、编写事务需要的sql语句（1条或者多条）
	insert into stuinfo values(...,...,...,);
3、结束事务

*/

show variables like '%auto%'
#演示事务的使用步骤
#1、取消事务自动开启
set autocommit = 0
#2、开启事务
start transanction;
#3、编写事务sql语句
 update stuinfo set balance = balance - 5000 where stuid = 1;
#将灭绝的钱 + 5000
update stuinfo set balance = balance + 5000 where stuid = 2;

#4、结束事务
#提交
commit;

#回滚
rollback;

以上学习内容均参考：
尚硅谷mysql基础篇（P243 - P307）

Charming Boy
关注
  • 1
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql语句(二)
weixin_30379973的博客
07-27 258
--MySQL 连接的使用 　　JOIN 按照功能大致分为如下三类： 　　INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。 　　LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。 　　RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。--创建一个新表CREATE TABL...
mysql数据库 总结
黑码哥的技术博客
01-02 568
​简介：介绍什么是mysql以及整套课程的大纲知识点​简介：mysql的简介1、什么是mysql？mysql是一个开源的关系型数据库管理系统，现在是oracle公司旗下的一款产品，由C和C++语言编写，可移植性高。支持在多种操作系统上安装，最常见有AIX，linux，window。mysql因为开源免费，所以受到了目前互联网行业的欢迎。
mysql总结
wwwwwww31311的博客
03-24 519
1 数据库使用的意义 ①内存速度快，但是容量小且无法永久性存储 ② 使用数据库可以实现永久性存储，结构清晰，可读性好，标准的sql语句，更方便的进行增删改查等操作 2 sql structure query language 结构化查询语言，是关系数据库的通用的增删改查语言，mysql是一个基于sql的轻量级开源关系型数据库软件 3 数据库的三大范式 ①第一范式，每一列都不可再分 ②第二范式，在第一范式的基础上，每一列都没有重复出现的数据 ③第三范式， ...
MySQL数据库总结
Zeker62的博客
10-30 337
文章目录DDL操作数据库、表操作数据库查看数据库查询创建数据库mysql的字符集创建数据库修改数据库删除数据库使用数据库操作表查询表（use、show）数据类型创建表（create）复制表（like）删除数据表（drop table）修改表（alter）DML 增删改表中数据添加数据（insert）删除数据（delete、truncate）修改数据（update）DQL查询表中的记录基础查询（select、DISTINCT、IFNULL()函数）条件查询（> < = ! & |、bet
MYSQL数据库总结
qq_45929569的博客
01-02 616
MYSQL数据库总结
MySQL数据库入门总结
08-12
MySQL 数据库入门总结 MySQL 数据库入门总结是关系型数据库管理系统的核心内容，本节主要总结了 MySQL 数据库的入门知识点，涵盖了数据库设计、数据定义语句、数据查询语句、数据操作语句、数据控制语句等方面的...
Eclipse连接Mysql数据库操作总结
09-01
在本文中，我们将深入探讨如何使用Eclipse IDE连接到MySQL数据库。Eclipse是一款广泛使用的Java开发工具，而MySQL则是一种流行的开源关系型数据库管理系统。将两者结合，开发者可以便捷地进行数据库相关的开发工作。...
mysql数据库学习总结
11-25
mysql整理资料上传，包含了DDL(数据库定义语言)、DML(数据库操纵语言)的增删改查，还有DCL(数据库控制语句)以及事务隔离级别、数据库主从的原理和配置，以及一些sql调优的信息等等
MySQL数据库面试题总结
最新发布
03-08
涉及MySQL数据库相关面试知识, 三大范式,建表,建约束,增删改查,触发器,存储过程,游标,索引,视图,集群,事务,优化等,
MYSQL数据库简单概念总结
01-31
MYSQL数据库简单概念总结,里面涵盖sql的数据处理、数据描述、数据模型、逻辑设计中的简单概念总结，测试时复习紧急概况的
Mysql数据库知识点归纳汇总
weixin_42751488的博客
04-13 7593
MYSQL数据库基础知识汇总
MySQL8.0连接问题总结
一位自然人的博客
09-25 8664
MySQL8.0连接问题总结1.驱动包版本2.驱动类3.连接属性 1.驱动包版本 对于8.0版本的MySQL数据库，驱动包版本也要跟上，一般使用mysql-connector-java-8.0.11，否则会报如下错误： Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC C...
MYSQL 数据库详解
只要学不死，就往死里学！！！
01-13 3941
史上最全的MySQL
MySQL数据库 实验报告（一）
gxy3202365628的博客
03-31 4万+
MySQL数据库 实验报告（一） 1、实验目的 （1）掌握系统数据类型的特点和功能 （2）掌握创建、修改表结构的方法 （3）掌握数据添加的方法 2、实验预习与准备 （1）MYSQL中创建数据库的命令 （2）表结构的创建、修改和删除 （3）MYSQL导入导出表数据的方式 （4）添加数据的SQL命令 3、实验内容及步骤 （1）利用SQL命令创建名为“jwgl”+“自己学号”的数据库 （2）使用命令行方式利用SQL语句在第一步创建的数据库中按下列要求创建数据表 a)创建如下表所示的student表（学生表） 列名
mysql实验三报告总结_数据库实验工作报告总结多篇
weixin_39945915的博客
02-02 682
数据库实验总结【一】试验内容1、 数据表的建立基本表《简单的》带有主键带有外码约束的(外码来自其他表或者本表)2、 数据表的修改添加删除列修改列属性类型添加删除约束(约束名)元组的添加，修改，删除删除数据表试验过程1、create table student(sno char(9) primary key , /*sno是主码 列级完整性约束条件*/sname char(20) unique, /...
MySQL 5.7.22 自带的四个数据库 介绍
Steven's blog
05-28 1万+
1.终端登录mysql数据库，显示全部数据库（或者直接用客户端工具展示）,如下： show databases;
MySQL/Oracle数据库优化总结（非常全面）
热门推荐
Java仗剑走天涯
08-21 6万+
MySQL/Oracle数据库优化总结（非常全面）
Mysql数据库基础知识总结，结构分明，内容详细
唯有努力，方可自信！
06-20 1556
Mysql数据库知识详细总结，可学习，可复习
MySQL技术总结第一篇
lishinho的博客
07-11 3771
（答辩完成，接下来一段时间会抓紧学习，月底回国。） 拿两三篇总结MySQL的知识点，补上之前的坑。这里第一篇主要讲粗略讲一下MySQL体系结构和存储引擎，通过对MySQL大体的认识，连带补充一些关于InnoDB的特性。 一，MySQL体系结构 1.1 什么是数据库，MySQL和SQL 首先讲数据库， 数据库（DB）是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。通...
Mysql数据库主从心得整理1
08-08
Mysql数据库主从复制是一种主数据库与从数据库之间的数据同步方式，通过将主数据库的数据同步到从数据库，实现数据备份和读写分离的效果。主从复制的过程主要包括Master和Slave之间的IO线程和SQL线程进行数据同步，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值