这里写目录标题
MySQL学习笔记
Linux下安装MySQL：Linux安装mysql，阿里云下安装MySQL
配套数据库创建语句见：数据库创建sql语句
安装步骤
-
确保服务器系统处于最新状态：yum -y update
-
重启服务器：或者yum makecache
-
首先检查是否已经安装，如果已经安装先删除以前版本，以免安装不成功
yum list installed | grep mysql
-
下载MySql安装包：
第一种；rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rp 直接下载安装
第二种：找到mysql yum包下载地址：https://dev.mysql.com/downloads/repo/yum/
选择对应版本得Linux —> download 右键复制下图得连接地址
weget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm，下载rmp包后经行rmp -ivh 包名 安装
-
安装完后运行：yum makecache
-
yun -y install mysql-community-server
-
安装完成后使用：systemctl start mysqld 启动mysql。然后使用grep “password” | grep /var/log/mysql.log来获取初始密码。
-
mysql -u root -p 密码
-
SET PASSWORD = PASSWORD(‘Abc123!_’);修改当前用户密码，出于保护密码太简单会有如下错误
set global validate_password_policy=LOW;设置安全等级set global validate_password_length=6;设置密码长度
-
添加一个root用户，允许任意Ip访问’%’
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘你的密码不能太简单’ WITH GRANT OPTION;
最后刷新权限flush privileges;
OK完成!
-
最后要去服务器控制台安全组开启mysql端口，不然远程连不到。
***可能会用到得指令***
检查并且显示Apache相关安装包
[root@localhost ~]# rpm -qa | grep mysql# 删除MySql
[root@localhost ~]# yum remove -y mysql mysql mysql-server mysql-libs compat-mysql51
或
[root@localhost ~]# rpm -e mysql-community-libs-5.7.20-1.el7.x86_64 --nodeps
或
[root@localhost ~]# yum -y remove mysql-community-libs-5.7.20-1.el7.x86_64# 查看MySql相关文件
[root@localhost ~]# find / -name mysql# 重启MySql服务
[root@localhost ~]# service mysqld restart# 查看MySql版本
[root@localhost ~]# yum repolist all | grep mysql# 查看当前的启动的 MySQL 版本
[root@localhost ~]# yum repolist enabled | grep mysql# 通过Yum来安装MySQL,会自动处理MySQL与其他组件的依赖关系
[root@localhost ~]# yum install mysql-community-server# 查看MySQL安装目录
[root@localhost ~]# whereis mysql# 启动MySQL服务
[root@localhost ~]# systemctl start mysqld# 查看MySQL服务状态
[root@localhost ~]# systemctl status mysqld# 关闭MySQL服务
[root@localhost ~]# systemctl stop mysqld# 测试MySQL是否安装成功
[root@localhost ~]# mysql# 查看MySql默认密码
[root@localhost ~]# grep ‘temporary password’ /var/log/mysqld.log# 查看所有数据库
mysql>show databases;# 退出登录数据库
mysql>exit;# 查看所有数据库用户
mysql>SELECT DISTINCT CONCAT(‘User: ‘’’,user,’’’@’’’,host,’’’;’) AS query FROM mysql.user
数据库卸载
- 通过软件管家或者自带得卸载
- 删除安装目录下得文件
- C盘删除Appdata下得mysql文件夹
数据库的概念
- DB：数据库（database）存储一系列有组织的数据
- DBMS：数据库管理系统（database management system），数据库通过DBMS创建操作的容器。
- SQL：结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。
数据库特点
- 将数据放在表里，数据表在放入数据库
- 一个数据库有多个表，每个表都有一个名字标识，同一数据库中表名唯一
- 表由列组成，每个列名也就是字段。
- 表中的数据是按行存储
MySQL配置文件
my.ini
- port=3306：端口
- datadir=“c：/”：数据库文件保存位置
- charater-ser-server=utf8：数据库字符编码
- default-storage-engine=INNODB：默认数据引擎
- sql-mode=“”：语法模式
- max_connections=100：数据库最大链接数
改完配置文件后数据库服务需要重启！！！
MySQL服务启动与停止
- 打开服务：找到MySQLXXX启动和关闭。
- 命令行方式：以管理员身份打开命令行：net start/stop mysqlXXX(windows下)
MySQL登录与退出
命令行完整命令
mysql [-h locahost(地址) -P 3306(端口号)] -u root -p
MySQL常见命令
基础库介绍：
information_schema：保存源数据信息
performance_schema：保存性能信息
test：空的
- show database ：查看数据库
- use 库名 ：使用数据库
- show table：查看数据库里面的数据表
- desc tablename：查看数据表结构
- select database（）：查看所在数据库
- select version（）：查看数据库版本 命令行mysql --version
- 查看数据库使用的字符集：show variables like ‘%char%’
MySQL语法规范
-
不区分大小写，但建议关键字大写，表名、列名小写
-
每条命令最好用分号结尾“；”（\g）
-
每条命令根据需要、可以进行缩进或换行，建议关键字一行
-
注释
单行注释：#注释文字 或者 – （空格）注释文字
多行注释：/*注释文字*/
SQL语言
-
DQL（Data Query Languge）：数据查询语言
select、
-
DML（Data Manipulation Languge）：数据操作语言
增删改
-
DDL（Data Define Languge）：数据定义语言
创建修改库表
-
TCL（Transaction Control Language）：事务控制语言
-
DCL（Data Control Language）：数据控制语言
DQL（数据查询语言）
练习对应练习数据库
基础查询语言
语法：select 查询列表 from 表名；
特点：
- 查询列表可以是表中字段、常量值、表达式、函数
- 查询结果是一个虚拟得表格（临时表格）
注意：
- 查询操作之前，需要使用对应的库：use 库名
- 查询的字段如果与关键字重名，需要用 ` 标明：select `name` from employees
-
查询表中的单个字段
SELECT last_name FROM employees -
查询多个字段
SELECT last_name，salary FROM employees -
查询全部
SELECT * FROM employees -
查询常量值
SELECT 100; SELECT 'john'; -
查询表达式、函数
SELECT 100+20;-- 表达式 -- SELECT count();-- 函数 -- -
去重
select department_id from employees -- 增加 distinct关键字可以去除重复的值 -- select distinct department_id from employees -
+号作用
#查询员工的姓和名组成一个字段，并显示为姓名 select last_name+first_name 姓名 from employees#失败的操作 /*mysql中 + 号作用只有一个功能：运算符 1、若加号两边都为数值，做加法运算 2、若有字符值，则试图将字符值转换为数值，若成功做加法运算，若失败字符值设为0做加法运算。 3、只要其中一方为null结果肯定为null */ -
concat连接
-- concat（）可以连接多个值。 select concat(last_name,'~',first_name) 姓名 from employees -- concat()中如果有的字段值为null，则最终显示为null，需要用到下面的ifnull()来判断 -- select concat(last_name,first_name,commission_pct) OUT_POT from employees select concat(last_name,first_name,ifnull(commission_pct,'0')) OUT_POT from employees -
ifnull判断字段是否为空
select ifnull(commission_pct,'0') as 绩点 from employees
为字段起别名
好处：便于理解，方便展示；在字段有重名的情况下可以区分
如果别名有关键字可以打双引号
-
在字段名后加 as 别名
SELECT last_name as 姓，salary as 薪水 FROM employees -
直接使用空格
SELECT last_name 姓，salary 薪水 FROM employees
条件查询
语法：
select 查询列表 from 表名 where 筛选条件
分类：
按条件表达式筛选
条件运算符：> < = !=（<>） >= <=
按逻辑表达式筛选
逻辑运算符（用于连接表达式）：&& || ! AND OR NOT
模糊查询
like
between 值 and 值
in（记录集）“in 后面是记录集”
is null
-
条件表达式查询
-- 查询工资大于12000的员工信息 select * from employees where salary > 12000 -
按逻辑表达式筛选
-- 查询工资在10000到20000之间的员工名、工资以及奖金 -- select first_name 员工名, salary 工资, commission_pct 奖金 from employees where salary > 10000 and salary <20000; -- 查询工资不在10000到20000之间的员工名、工资以及奖金 -- select first_name 员工名, salary 工资, commission_pct 奖金 from employees where salary <10000 OR salary>20000; select first_name 员工名, salary 工资, commission_pct 奖金 from employees where salary not between 10000 and 20000; -
like 模糊查询
%:任意多个字符
_:任意单个字符
\ :转义字符
自定义转义字符：定义$ 为转义字符——escape ‘$’;
无法匹配null值
-- 查询员工姓名包含a的员工信息 --
select * from employees where last_name like '%a%';
-- 查询员工姓名第二个字符为_的信息，
select * from employees where last_name like '_\_%';
select * from employees where last_name like '_$_%' escape '$';#自定义转义字符
-
in 关键字
字符类型要加单引号
-- 查询员工工种编号是IT_PROG、AD_VP、AD_PRES -- select * from employees where job_id in ('IT_PROG','AD_VP','AD_PRES'); -
is null和is not null
=或<> 不能判断null值
is 不能用于判断具体值，只能和null搭配。
-- 查询有奖金的员工和奖金率 -- select last_name, commission_pct from employees where commission_pct is not null; -- 查询没有奖金的员工和奖金率 -- select last_name, commission_pct from employees where commission_pct is null; -
安全等于 <=>（可以判断null和具体值）
-- 查询没有奖金的员工和奖金率 -- select last_name, commission_pct from employees where commission_pct <=> null; -- 查询工资等于12000的员工信息 -- select * from employees where salary <=> 12000; -
排序查询
order by 排序列 asc|desc：asc 从低到高；desc从高到低
多级排序：order by 优先排序列 asc|desc，次要排序列 asc|desc；
order by 子句一般是放在查询语句的最后面，limit子句除外
-- 按年薪从高到低显示员工的信息和年薪 【按表达式排序】-- select * ，salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by salary*12*(1+ifnull(commission_pct,0)) desc; -- 按年薪从高到低显示员工的信息和年薪 【按别名排序】-- select * ，salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc; -- 先按员工工资升序排列和再按员工编号降序排序 -- select * from employees order by salary asc,employee_id desc; -
分组查询 常和分组函数搭配使用详情见分组函数
group by 要分组的列： 分组函数，根据分组列的不同值进行分组
group by 要在where之后，order by之前
#将员工按照部门进行分组 SELECT department_id FROM employees GROUP BY department_id;
常见函数
将一组逻辑语句封装在方法体中，对外暴露方法名
格式：select 函数(实参列表 ) from 表
分类：
单行函数；
concat、length、ifnull等
分组函数 做统计使用、又称统计函数、聚合函数、组函数
单行函数
-
字符函数
-
length获取参数值的字节个数，utf8中文占两个英文占一个字节
select length(‘十分士hhhh’)#10
-
CHAR_LENGTH获取参数值的字符个数
select CHAR_LENGTH(‘十分士hhhh’)；#7
-
concat（）拼接字符串
-
upper（）转大写、lower（）转小写
-
substr、substring切割函数
#SQL中字符索引从1开始 #截取从指定索引处开始后面所有字符 SELECT SUBSTR('123456789',6);#6789 #截取从指定索引处指定字符长度的字符 SELECT SUBSTR('123456789',1,3);#123 -
instr查找子串索引
#返回第一个字串的索引，没有就返回0 SELECT INSTR('123456789','6789')#6 -
trim 去掉字符串XXX
#去掉字符串前后空格 SELECT TRIM(' jdf ');#jdf #去掉字符串前后的指定值 SELECT TRIM('a' FROM 'aaaaajdkaaaajffaaaaa');#jdkaaaajff -
lpad、rpad
#返回指定长度的字符串，长度不够用指定值左填充，长度超出则剪取指定长度。 #这里的长度指的是字符索引长度，不是lenght的子节长度。 SELECT LPAD('博傲天',8,'*');#*****博傲天 SELECT length(LPAD('博傲天',8,'*'));#14 #RPAD就是右填充 -
replace替换
#替换，讲源字符串的所有要替换的值替换为新值 SELECT REPLACE('aaabcd','a','A');#AAAbcd
-
-
数学函数
-
round 四舍五入
select round(1.16)#2 select round(1.567,2)#1.57小数点后保留2位 -
ceil 向上取整返回大于等于该参数的最小整数
select ceil(1.02);#2 select ceil(-9.99)#-9 -
floor 向下取整，返回小于等于该参数的最大整数
select floor(1.02);#1 select floor(-9.99)#-10 -
truncate截断
select truncate(1.69999,2);#1.69,保留小数点后2位 -
mod取余 与%差不多
mode(a,b): a-a/b*b select mod(10,3);#1 select mod(2,3);#2
-
-
日期函数
#1、now（）返回当前系统日期和时间 select now();#2020-04-08 15:16:18 #2、curdate 返回当前系统日期，不包含时间 select curdate();#2020-04-08 #3、curtime 返回当前时间，不包含日期 select curtime();#15:18:48 #4、获取指定的时间部分 select year(now()) 年;#2020 select month(now()) 月;#4 select monthname(now()) 月英文名;#April select day(now()) 日;#8 select MINUTE(now()) 分钟;#26 select SECOND(now()) 秒; #5、datediff（日期，日期）前面日期减去后面日期返回天数 select datediff(now(),'2020-1-24')#77 （2020/4/10）-
日期转换
-
str_to_date:字符串通过指定格式转换成日期格式
select str_to_date('1999-8-23','%Y-%m-%d');#1999-08-23 select str_to_date('8-23 1999','%m-%d %Y');#1999-08-23 -
data_format：将日期转换为字符串
select date_format(now(),'%y年%m月%d日');#20年04月08日 #查询有奖金的员工名和入职日期（格式月/日 年） select last_name , DATE_FORMAT(hiredate,'%m月%d日 %Y年') 入职时间 from employees where commission_pct is not null;
-
-
-
其他函数
SELECT VERSION();#查看 版本 select database();#查看数据库 select user();#查看用户 -
流程控制函数
#if函数：if else的效果 select if(条件，是返回值，否返回值); select if(10<5,'是','否')#是 #case函数： #####################使用一：类似于switch case，方便使用等值判断####### #case要判断的字段或表达式 #when 常量1 then 要显示的值1或语句; #when 常量2 then 要显示的值2或语句; #else 要显示的值n或语句; #end ###################################如果when返回的是值的话就不需要用； /*案例: 查询员工的工资，要求 部门号=30，显示的工资为1.1倍 部门号=40，显示的工资为1.2倍 部门号=50，显示的工资为1.3倍 其他部门，显示的工资为原工资*/ SELECT department_id 部门编号,salary 原始工资, CASE IFNULL(department_id,0) WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END as 实际工资 FROM employees order by 实际工资 asc; #####################使用二：类似于多重if，方便区间判################# #case #when 常量1 then 要显示的值1或语句; #when 常量2 then 要显示的值2或语句; #else 要显示的值n或语句; #end /*案例:查询员工的工资的情况 如果工资>20000,显示A级别 如果工资>15000,显示B级别 如果工资>10000，显示c级别 否则，显示D级别*/ SELECT department_id 部门编号,salary 原始工资, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END as 薪水等级 FROM employees order by 薪水等级 asc;感悟通过case方法二的使用，abc可以分开显示，推测，selec也是一行一行输出的
分组函数
功能：用作统计使用、又称为聚合函数或者统计函数或组函数
分类：sum（）求和、avg（）平均值、max（）最大值、min（）最小值、count（）计算个数
与单行函数区别是：只得到一个值，而单行函数是每一行都会有值
参数支持类型
sum、avg：只支持值类型
max、min：支持可比较的所有类型
count：计算不为null的个数，支持所有类型
以上分组函数都忽略null值
可以和distinct搭配使用
-
count函数详细介绍
#统计单个列非null个数 select count(salary) FROM employees; #统计表的行数 select count(*) FROM employees;#一行中只要有非null的就计数 select count(1) FROM employees;#1是新加了一列全是1，统计1的个数。等于还是统计行数。 -
使用分组函数查询要讲究一一对应，不能一对多
和分组函数一起查询的字段要求是group by 后的字段
-
分组查询
语法：
select 分组函数，列（必须是出现在group by后面）
from 表
[where 筛选条件]
group by 分组的列表或函数
[order by 子句]
特点 ：分组查询中筛选分为两类
1、分组前筛选 原始表中的条件 where 放在group by前
2、分组后筛选 分组后的结果集 having 放在group by后
3、分组函数做条件的肯定放在having子句中
4、能进行分组前筛选的就尽量使用分组前筛选。
#查询每个部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id; #查询每个工种的最高工资，然后按工资从大到小排序 SELECT MAX(salary) 最高工资, job_id 工种 FROM employees GROUP BY job_id ORDER BY 最高工资 DESC; -- 按多个字段分组，将要分组的多个字段都放在group by子句即可，他就会把多个字段当成一个组来分，类似于两个字段组成一个唯一主键 -- #查询每个部门每个工种的员工的平均工资 select avg(salary),department_id,job_id from employees group by department_id,job_id; -
having 进行复杂分组查询，位置放在group by 后面
oracle可能不支持having后面放别名
-- 复杂分组查询 -- /* 查询那个部门的员工个数大于2 分开查询，根据分组查询过后的结果集筛选 1、将员工按部门分组，使用count进行计算 2、在1的基础上进行筛选 */ select count(*),department_id from employees group by department_id having count(*)>2; #查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资。 SELECT MAX(salary) 最高工资,job_id 工种编号 FROM employees WHERE commission_pct is not NULL GROUP BY job_id HAVING 最高工资>12000; -- 练习-- #查询员工最高工资和最低工资的差距 select MAX(salary)-MIN(salary) 差距 from employees; #查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 SELECT min(salary) 最低工资,manager_id FROM employees WHERE manager_id is not NULL GROUP BY manager_id HAVING 最低工资>=6000; #查询所有部门的编号，员工数量和工资平均值(保留2位小数)，并按平均工资降序 SELECT ROUND(AVG(salary),2) 平均工资,COUNT(*) 人数,department_id 部门编号 FROM employees GROUP BY department_id ORDER BY 平均工资 desc; #选择具有各个job_ id的员工人数 SELECT count(*) 人数,job_id 工种 FROM employees WHERE job_id is not NULL GROUP BY job_id;
连接查询
笛卡尔乘积（没有添加任何字段就完全连接）
表1m行，表2n行，结果m*n行。
如何避免：要添加连接条件进行匹配。
分类：
- 按年代分类：sql92标准——仅仅支持内连接；sql99标准——支持内连接+外连接（左外、右外）+交叉连接。仅讨论在mysql中
- 按功能分：
- 内连接：
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全连接
- 交叉连接
-- 一、sql92标准 --
#【1】等职连接
/*
1、多表连接结果为所有表的交集
2、n表连接，至少需要n-1个连接条件
3、from子句的多表的顺序没有要求
4、一般需要起别名
5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#查询每个女生的男朋友名（通过id关联）
SELECT name 女生姓名,boyName 男生姓名 FROM boys,beauty WHERE beauty.id = boys.id;
#查询员工姓名、工种号、工种名
#错误示范、报错：Column 'job_id' in field list is ambiguous，job_id有歧义需要指定是哪个表中的job_id
SELECT last_name 姓名,job_id 工种号,job_title 工种名 FROM employees,jobs WHERE employees.job_id = jobs.job_id;
#正确示范
SELECT last_name 姓名,employees.job_id 工种号,job_title 工种名 FROM employees,jobs WHERE employees.job_id = jobs.job_id;
#也可以为表起别名，方便使用提高简洁度，跟字段别名类似
#如果起了别名就无法使用原来名字
SELECT last_name 姓名,e.job_id 工种号,job_title 工种名 FROM employees e,jobs j WHERE e.job_id = j.job_id;
#-----------------------可以添加筛选条件
#查询有奖金的员工名、部门名
SELECT last_name 员工名,department_name 部门名 FROM employees e, departments d WHERE e.department_id = d.department_id AND e.commission_pct IS NOT NULL;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name 部门名, d.manager_id 领导编号, MIN(salary) 最低工资 FROM employees e,departments d
WHERE e.department_id = d.department_id AND commission_pct IS NOT NULL
GROUP BY 领导编号,部门名;#group by子句必须包含select子句的字段除非你非常确认group by 子句与select结果一一对应。
#-----------------------可以添加排序，和having
#查询每个工种的工种名和员工的个数并且员工数大于3，按员工个数降序
SELECT job_title 工种名, COUNT(*) 个数
FROM employees e , jobs j
WHERE e.job_id = j.job_id
GROUP BY 工种名 HAVING 个数>3
ORDER BY 个数 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;#其余添加筛选、分组、排序与两表连接一样。
#【2】非等值连接 （不是等于号连接的查询条件）
#查询员工的工资和工资级别
SELECT salary 员工工资, grade_level 工资级别
FROM employees e,job_grades j
WHERE salary>=j.lowest_sal AND salary<j.highest_sal;
#【3】自连接 （类似于等值连接，不过是自己连接自己）
/*
1、满足条件，一个表里有可以连接的字段
2、连接条件，连接字段的顺序不同结果也不同，连接前要弄明白连接逻辑
*/
#查看员工的姓名和他的领导名
SELECT e1.employee_id 员工编号, e1.last_name 员工名, e2.employee_id 领导编号, e2.last_name 领导名
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id;#注意：要搞清楚连接条件的意义，是从e1的“员工表”里面的manager_id去对于“领导表”的employee_id，若写反结果也是反的。
sql99标准
语法：
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组筛选条件]
[order by 排序]
内连接（★）：inner
外连接：
左外（★）：left [outer]
右外（★）；right [outer]
全外：full [outer] mysql不支持 结果集类似左外加右外
交叉连接：cross
内连接
-- 一、内连接 --
/*select 查询列表
from 表1 别名
inner join 表2
on 连接条件
分类：等值、非等值、自连接
特点：
1、inner可以省略
2、筛选条件放在where子句、连接条件放在ON后面
3、inner join连接与sql92等值连接效果一样
*/
-- ￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥一、等值连接￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥--
-- 查询员工名还有部门名 --
SELECT last_name 员工名,department_name 部门名 FROM employees e INNER join departments d ON e.department_id = d.department_id;
-- 查询哪个部门的的部门名含有“i”，且部门员工个数>3的部门名和员工个数，并按个数降序(排序) --
SELECT department_name 部门名,count(*) 员工个数
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE department_name LIKE '%i%'
GROUP BY department_name HAVING 员工个数>3
ORDER BY 员工个数 DESC;
-- 【多表连接】查询员工名、部门名、工种名，并按部门名降序()--
/*最后连接的表一定要和前面的表有链接条件！*/
SELECT last_name 员工名, department_name 部门名, job_title 工种名
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j on e.job_id = j.job_id
ORDER BY department_name DESC;
-- ￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥二、非等值连接￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥--
-- 查询员工的工资和工资级别--
SELECT salary 员工工资, grade_level 工资级别
FROM employees e
INNER JOIN job_grades j ON salary BETWEEN j.lowest_sal AND j.highest_sal/*或者salary>=j.lowest_sal AND salary<j.highest_sal*/
ORDER BY salary DESC
-- ￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥三、自连接￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥--
-- 查看员工姓名中包含“o”的姓名和他的领导名 --
SELECT e1.employee_id 员工编号, e1.last_name 员工名, e2.employee_id 领导编号, e2.last_name 领导名
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e1.last_name like '%o%';
外连接
应用场景：查询一个表中有，另一个表中没有的记录
查询过程：
- 会将一个表当成主表，然后去匹配第二个表，匹配上就会显示信息，反之显示null
- 结果是等值连接查询的结果+主表中有而从表中没有的结果
分类：
- 左外连接 ：left join 左边的是主表
- 右外连接：right jion 右边的是主表
特点：
- 左外、右外交换两个表的顺序，可以实现同样的效果。
-- 使用girls库 --
/*要是采用之前的方式等值和非等值连接就无法完成下列需求，因为没得等值条件，采用非等值条件无法一一对应，筛选过后会形成笛卡尔积，总会有符合条件的重复结果
SELECT DISTINCT name 女生姓名 FROM boys,beauty WHERE beauty.id != boys.id;*/
-- 查询男朋友不在男生表的女生名 【左外】from后的表为主表--
SELECT b.name 女生名
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;-- 不为空的判断最好使用非空字段 --
--使用employee库--
-- 查看哪个部门没有员工 从题目可知主表为部门表--
SELECT department_name 部门名,d.department_id FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;-- 左外 --
SELECT department_name 部门名,d.department_id FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;-- 右外 --
交叉连接
结果集就是一个笛卡尔乘积
--交叉连接--
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
总结
- 使用等值查询，要先连接所需要信息的表，然后再使用筛选条件或分组
子查询
含义：
出现在其他语句中的select语句，称为子查询语句或内查询
外部的查询语句，称为主查询或外查询
分类：
- 按出现的位置：
- 可以放在select后面
- 仅支持标量子查询
- from后面
- 表子查询
- where或having后面★
- 标量子查询（单行）√
- 列子查询（多行）√
- 行子查询（多列）
- exists后面（相关子查询）
- 表子查询
- 按功能不同
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
- 表子查询（结果集）
where或having后面的子查询
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（一行多列）
特点：
- 子查询都会放在小括号内
- 子查询一般放在条件的的右侧
- 标量子查询，一般陪着单行操作符使用 > < <= = <>
- 列子查询，一般搭配着多行操作符使用 in、any/some、all
- 子查询执行的优先程度高于主查询
标量子查询（单单列子查询）
-- 谁的工资比Abel 高? --
--1、先查询Abel的工资 ---
SELECT salary FROM employees WHERE last_name = 'Abel';
-- 2、把第一步标量查询的结果作为条件进行比较 --
SELECT last_name 姓名
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
--返回job_ id与141号员工相同，salary比143号员工多的员工的姓名，job__id 和工资--
SELECT last_name 姓名, job_id 工作编号, salary 工资 FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
AND salary > (SELECT salary FROM employees WHERE employee_id = 143)
--查询最低工资大于50号部门最低工资的部门id和其最低工资 --
SELECT MIN(salary) 最低工资, department_id 部门编号
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50)
SELECT *
FROM (SELECT MIN(salary) 最低工资, department_id 部门编号 FROM employees GROUP BY department_id) t1
WHERE t1.最低工资 > (SELECT MIN(salary) FROM employees WHERE department_id = 50)-- 子查询在from后面是一张表，必须起别名，否则报错 --
列子查询（多行子查询）
1、子查询返回单列多行
2、要使用多行比较操作符
any和all可以在子查询select语句后面使用min或max替换
any和all要跟单行比较符配合使用
in可以换为 =any，not in 可以换为 <>all
3、记得最好使用去重，提高效率
-- 返回location_ id是 1400或1700的部门中的所有员工姓名 --
SELECT last_name
FROM employees e
WHERE department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id BETWEEN 1400 AND 1700);
SELECT last_name
FROM employees e
WHERE department_id =ANY (SELECT DISTINCT department_id FROM departments WHERE location_id BETWEEN 1400 AND 1700);
--返回其它部门中比job_ id为‘IT_PROG' 部门任一工资低的员工的:工号、姓名、job_ id以及salary--
SELECT employee_id 工号,last_name 性名,job_id 工种号,salary 工资
FROM employees
WHERE salary < ANY(SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';
SELECT employee_id 工号,last_name 性名,job_id 工种号,salary 工资
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';
行子查询（一行一列或者多行多列）
-- 查询员工编号最小并且工资最高的员工信息 --
SELECT * from employees
WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);--使用次数较小，情况必须为 = 号时才可以使用。也可以用标量子查询代替--
SELECT后面的子查询（只能标量）
-- 查询每个部门的员工个数 --
SELECT d.*,(
SELECT count(*) FROM employees e WHERE e.department_id = d.department_id
) 个数
FROM departments d;-- 这里要是理解不了为啥这个子查询里面可以使用主查询的字段，根据推理我就得这个执行顺序应该是先主查询的FROM然后是SELECT里面的子查询。--
SELECT d.department_id,COUNT(*) 个数 FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id;-- 使用外连接也可实现--
FROM后面的子查询
-- 查询每个部门的平均工资的工资等级 --
SELECT t1.*,j.grade_level 平均工资等级
FROM (SELECT department_id 部门编号, round(AVG(salary),2) 平均工资 FROM employees GROUP BY department_id) t1 ,job_grades j
WHERE t1.平均工资 BETWEEN j.lowest_sal AND j.highest_sal;-- round(,2)四舍五入保留小数点后两位 --
exists后面（相关子查询）
exists（查询子句）：子查询有值则返回true，没有则返回false
-- 查询有员工的部门名 --
SELECT department_name FROM departments d WHERE EXISTS(SELECT * FROM employees e WHERE e.department_id = d.department_id);-- 先执行主查询然后执行子查询 --
SELECT department_name FROM departments d WHERE d.department_id in (SELECT department_id FROM employees);-- 使用in代替 --
分页查询
应用场景：分页显示要显示的数据，就要分页提交sql请求
语法：
select 查询列表
from 表
【join 表2 on 条件】
【where 筛选条件】
【group by 】
【having】
【order by】
limit offset，size；
offset 要显示的条目的起始索引（从0开始）从0开始可以省略；
size 要显示的条目个数；
特点：
- 先不管分页，先把查询语句给写出来，在最后在使用limit 起始索引
- 不光语法书写在最后，执行顺序也是在最后。
- 索引公式 limit 索引 ，数目；索引=页数 - 1 x 数目
-- 查询前五条员工信息 --
SELECT * FROM employees LIMIT 0,5;
-- 有奖金的员工信息，并且工资较高的前10名显示出来 --
SELECT * FROM employees WHERE commission_pct is NOT NULL ORDER BY salary DESC LIMIT 10;--省略了0索引 --
联合查询
union 联合 合并 ：将多条查询语句的结果合并成一个结果
应用场景：查询结果列一样，但是多个表的字段不一样的情况
要查询的结果来自多个表，但两个表并没有直接的链接关系。
特点：
- 要求多条语句的查询列数是一致的
- 查询结果列名是第一条查询语句的字段名，故每一条查询语句的类型和顺序是一致的
- 联合查询自带distinct去重效果，不想去重要在UNION后加ALL；
#查询部门编号>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
修改：update
删除：delete
插入 insert
语法：
insert into 表名 （字段名1，字段名2…）
values （值1，值2…）
inset into 表名
set 列名=值，列名=值
注意：插入方式一注意事项
- 插入值得类型要与字段的类型兼容
- 不为空得必须要填值，可以为空的如果不需要填值可以在填列名得时候省略。
- 列名顺序可以调换但是要与values后面的值一一对应。
- 可以省略列名，默认所有列且顺序与表列的顺序一致。
两种方式比较：
- 方式一支持插入多行，一行的值用逗号隔开。
- 方式一支持子查 询，子查询对应的结果集对应插入到目标列
--向beauty表中插入一条信息--
INSERT INTO beauty (id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐一新','男','1999/2/24','13258485478',NULL,4);
INSERT INTO beauty (id,name,sex,borndate,phone,boyfriend_id)
VALUES(14,'唐二蛋','男','1999/1/24','13258485478',1);--省略为空的--
INSERT INTO beauty (id,name,sex,borndate,phone,boyfriend_id)
select 14,'唐二蛋','男','1999/1/24','13258485478',1;--支持子查询--
修改语句 update
修改单表的记录
语法：
update 表名
set 列=值，列=值，。。。
where 筛选条件
修改多表的记录
语法：
【sql92】
update 表1 别名，表2 别名
setset 列名 = 值，列名=值。。。
where 连接条件 and 筛选条件
【sql99】
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
setset 列名 = 值，列名=值。。。
where 筛选条件
--【修改单表】修改beauty表中唐一新的性别为女,电话更新为12345678985 --
UPDATE beauty SET sex='女',phone='12345678985' WHERE name='唐一新';
-- 【修改多表】修改张无忌的女朋友的手机号为114 --
UPDATE boys bo INNER JOIN beauty b ON b.boyfriend_id = bo.id SET phone='114' WHERE boyName='张无忌';
删除语句 delete
方式一：
语法：
delete from 表名 where 筛选条件 【单表删除】
delete 要删除表信息的表别名 from 表1 别名 inner join 表2 on 连接条件
where 筛选条件【多表删除】多表都删就把所有别名写在delete后面。
方式二：
语法：
truncate table 表名； 删除一整张表，不允许加where。
两种方式PK：
- delete可以加筛选条件，级联删除，truncat不可以
- truncat删除效率高
- 如果删除表中有自增长列，delete删除后从断点自增，truncat从1开始自增。
- delete会返回几行删除，truncat不会返回。
- truncat删除不能回滚，delete删除可以回滚。
-- 【单表删除】删除手机尾号为9的女生信息 --
DELETE FROM beauty WHERE phone like '%9';
-- 【多表删除】删除张无忌女朋友的信息 --
DELETE b FROM beauty b INNER JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.boyName='张无忌';
DDL语言
数据库定义语言，用于库和表的管理
一、数据库的管理
- 创建、修改、删除
二、表的管理
- 创建、修改、删除
创建：create
修改：alter
删除：drop
库的管理
创建语法：
- create database 库名；（默认存储）
- create database IF NOT EXISTS book；（容错，如果book不存在就创建）
更改数据库
- 一般认为库名不可修改
- 修改字符集：alter database book character set gbk；
- 库的删除：drop database IF EXISTS book；
表的管理
表的创建
语法：
create table [IF NOT EXISTS] 表名（
列名 列的类型【（长度） 列级约束】，
列名 列的类型【（长度） 列级约束】，
列名 列的类型【（长度） 列级约束】，
。。。
表级约束
）；[去重]
表的修改
修改列名、列的类型或约束、添加新列、删除列、修改表名
alter table 表名 add|drop|modify|change column 。。。。
修改列名
alter table 表名 change column 旧列名 新列名 类型；（也可以同时改类型）
列的类型或约束
alter table 表名 modify column 列名 新类型；
添加新列
alter table 表名 add column 列名 类型；
删除列
alter table 表名 drop column 列名
修改表名
alter table 表名 rename to 新表名；
表的删除
- 语法：drop table 表名；
表的复制
仅复制表的结构：
create table 新表名 like 复制源表名
复制结构和数据
create table 新表名 select * from 复制源表名 【where 筛选条件】；（select可以加筛选条件可以复制你想要的数据）
复制部分结构
create table 新表名
select 需要的列名1，需要的列名2… from 复制源表名
where 0；（利用上面的复制部分数据，找一个很不成立的筛选条件即可只复制想要的结构，不要数据）
要在设定数据库编码后在创建表，不然varchar（）中文插入可能会报错。
ALTER DATABASE book CHARACTER SET utf8mb4;
-- 创建表 --
/*创建Book表*/
CREATE TABLE Book(
id INT,#编号
bName VARCHAR(20),
price DOUBLE,
author VARCHAR(20),
publishDate datetime
);
/*#创建作者表*/
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC Book;#查看表结构
MySQL常见数据类型
数值型：
- 整型
- 小数：定点数、浮点数
字符型：
- 较短的文本：char、varchar
- 较长的文本：text、blob（较长的二进制数据）
日期型：datatime、timestamp
整型
分类：
- tinyint：1字节
- smallint：2字节
- mediumint：3字节
- int、integer：4字节
- bigint：8字节
特点：
- 区别仅仅是保存的范围不同。
- 默认为有符号，创建无符号类型时需要在后面加unsigned，如int unsigned（无符号int类型）。
- 超出类型范围则插入的是临界值，会报出out of range的错误。
- 如果不设置长度，会有默认长度，此时设置的长度仅为显示的字符长度，而数据的大小还是由类型决定，并且设定长度还必须搭配zerofill（0填充），比如int(7）zerofill，同时也变成了无符号类型。如果数据这时是123，则会显示为0000123。
小数
浮点型：float、double
- float（M，D）
- double（M，D）
定点型：DEC（DECIMAL）。最大范围与double相同
- DEC（M，D）
特点：
- M和D，M代表有效数字有几 位；D代表小数点后保留几位。超范围插入临界值，报错truncant of
- M和D都可以省略，decimal默认是（10,0），float和double会根据插入值来决定精度
- 定点型的精确度较高，如果要求插入数值的精确度 较高，如果是货币则考虑decimal。
字符型
较短的文本：
- char(M)：M是保存最长的字符数默认为1，mysql中中文和英文都是一个字符，不要与字节弄混淆。
- varchar(M)：varchar的长度可以根据数据变化M不能为空，但不能超出M指定长度。char长度不会变。但是效率比char低。
- enum(‘枚举值1’,‘枚举值2’)：只能插入列举的枚举值，不区分大小写
较长的文本：
- text
- blob
日期类型
date：只保存日期
time：只保存时间
year：只保存年
datetime：保存日期和时间
timestamp：保存日期和时间，受时区影响和数据库版本的影响较大
数据类型选择原则
所选择数值类型越简单越好、能保存数值的类型越小越好。
常见约束
含义：一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性。
分类：
- not null：非空，用于保证该字段的值不能为空。
- default：默认值，用于保障该字段有默认值。
- primary key：主键，用于保证该字段的值具有唯一性，并且非空。
- unique：唯一约束，用于保证字段具有唯一性，可为空
- check：检查约束【mysql中文不支持】
- foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值。 从表添加外键约束用于引用主表某列的值。
添加约束的时机
- 创建表时（无数据时）
- 修改表时（无数据时）
约束的添加分类
列级约束
六大约束语法都支持，但外键约束无效果。
表级约束
除了非空、默认其他的都可以写。
对比
主键和唯一的对比
- 主键不为空，唯一可为空
- 唯一约束只能有一个为空。
- 一个表中只能至多有1个主键，唯一可以有多个
- 可以多个列组成一个主键或约束。使用时不同列名用逗号隔开，如primary key(id,age)。但不推荐。
外键
- 要求在从表设置外键关系
- 从表的外键列的类型要与主表关联列的类型一致或兼容，名称无要求
- 主表的关联列必须是一个key（一般是主键或唯一）。
- 插入数据时，先插入主表，在插入从表。删除数据时，先删除从表，再删除从表。
创建表时添加约束
添加列级约束
直接在类型后面追加约束类型即可。
只支持：默认、非空、主键、唯一
create table major(
id int PRIMARY key,
majorName VARCHAR(20)
);
CREATE table stuinfo(
id int PRIMARY KEY,#主键
stuname VARCHAR(20) NOT NULL,#非空
gender char(1) CHECK(gender in ('男','女')),#检查约束，不支持
seat int UNIQUE,#唯一
age int DEFAULT 18,#默认约束
majorId int REFERENCES major(id)#外键，不支持
);
SHOW INDEX FROM stuinfo;--查看表的索引--
添加表级约束
语法：在各个字段最下面，别忘了逗号
CONSTRAINT 约束名 约束类型(约束列名)；
CONSTRAINT 约束名可以省略
CREATE TABLE studinfo(
id int ,#主键
stuname VARCHAR(20),#非空
gender char(1) ,#检查约束，不支持
seat int,#唯一
age int,#默认约束
majorId int,#外键，不支持
CONSTRAINT pk PRIMARY KEY(id),#主键，在mysql中命名但是没有效果
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT fk_studinfo_major FOREIGN KEY(majorId) references major(id)
);
通用写法
主键、非空、默认和唯一写在列级约束
外键要写在表级约束。且约束名不能重复。
修改表时添加约束
添加非空约束
alter table 表名 modify column 列名 类型 not null；
添加默认约束
alter table 表名 modify column 列名 类型 default 值；
添加主键
alter table 表名 modify column 列名 类型 primary key；【列级约束】
alter table 表名 add primary key(列名)；【表级约束】
添加唯一
alter table 表名 modify column 列名 类型 unique；【列级约束】
alter table 表名 add unique(列名)；【表级约束】
添加外键
alter table 从表名 add [constraint 外键名] foreign key(从表列名) reference 主表名(主表列名)
修改表时删除约束
添加非空约束
alter table 表名 modify column 列名 类型 ；
删除默认约束
alter table 表名 modify column 列名 类型 ；
删除主键
alter table 表名 drop primary key
删除唯一约束
alter table 表名 drop index 约束名；
删除外键
alter table 表名 drop foreign key 外键名；
标识列
修改表时设置标识列
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
语法：在类型后面添加 auto_increment
mysql中不支持设置起始值，但可以设置自增长的步长
set auto_increment _increment=3；
如果非要设置起始值，可以通过插入第一个信息的值作为起始值。
特点：
标识列必须和主键搭配吗？
Q：不一定，但一定要和一个key搭配（unique、primary key等）
一个表中可以有几个标识列？
Q：至多一个。
标识列的支持那些类型？
Q：只能时数值型。一般是int
CREATE TABLE studinfo(
id int primary key auto_increment,#主键
stuname VARCHAR(20),#非空
);
insert into studinfo (name) values('张三')
修改表时添加 / 删除标识列
--添加--
alter table 表名 modify column 列名 类型 primary key auto_increment;
--删除--
alter table 表名 modify column 列名 类型 primary key ;
TCL语言
Transaction Control Language：事务控制语言
事务：
一个或者一组sql语句组成一个执行单元，这个执行单元，要么全部执行，要么全部不执行。没有执行成功的事务就会进行回滚，会会到初始状态。
应用场景：转账时，要对双方的数据进行修改。只要有一方执行失败，就不会执行成功。
支持：只有innodb支持事务。show engines；查看数据库引擎。
事务的ACID属性：
原子性（Atomicity）
原子性是指事务是一个不可再分的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务必须是数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是持久性的，接下来其他操作和数据库故障不应该对其有任何影响
事务的创建
分类：
隐式事务：没有明显的开始和结束的标志，比如 insert、update、delete
显式事务：事务有明显的开启和结束标志
前提必须先设置自动提交功能为禁用
set autocommit=0,每次事务前都要设置。步骤：
开启事务
set autocommit=0；
start transaction；【可选】
编写事务中的sql语句（select、insert、update、delete）
语句1
语句2
…
结束事务（可以在try cache中使用）
commit；提交事务
rollback；回滚事务
savepoint 保存点
只能搭配rollback使用
delete与truncate在事务中的比较
delete可以支持回滚，而truncate不支持回滚。
--创建事务演示--
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE author SET id = 5 WHERE au_name='打分数';
INSERT INTO author VALUES(6,'三少','中国');
#结束事务
COMMIT;
#ROLLBACK;#回滚
--回滚点演示--
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE author SET id = 5 WHERE au_name='打分数';
savepoint a;
INSERT INTO author VALUES(6,'三少','中国');
#结束事务
ROLLBACK TO a;#回滚a保存点
事务的隔离
没有采取隔离措施，同时运行多个事务对同一数据处理时就会导致各种并发问题。
- 脏读：对于两个事务T1、T2，T1读取了已经被T2更新但是还没有被提交的字段。之后，若T2回滚，T1读取的内容就是临时无效的。针对跟新来说的。
- 不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
- 幻读：对于两个事务T1、T2，从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行。针对插入来说的
mysql中支持4种事务的隔离级别，默认隔离级别是：repeatable read
相关命令：
- SELECT @@tx_isolation;查看隔离级别。
- set transaction isolation level read committed；设置当前连接的隔离级别
- set global transaction isolation level read committed：设置数据库系统的全局隔离级别。一般要重启后才有效。
视图
含义：虚拟的表，和普通表一样使用。是通过动态表生存的数据，并不是保存真实数据，只是包含了逻辑。
应用场景：
- 多个地方用到同样的查询结果
- 该查询结果使用 的sql语句较复杂
视图与表的比较：
视图的创建
语法：
- create view 视图名 as 复杂的查询语句
创建好后可以在视图菜单项看见你所创建的视图。
使用视图的好处
- 重用sql语句
- 简化复杂的sql操作，不必要知道他的查询细节
- 保护数据，提高安全性。
#【创建视图】创建员工名、部门名和工种信息的视图
CREATE VIEW myv1
AS
SELECT e.last_name,d.department_name,j.* FROM employees e INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
#查询姓名中包含a字符的员工名、部门名和工种信息
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
视图的修改
修改方式
方式1：
create or replace view 视图名
as 查询语句；
方式2：
alter view 视图名
as 查询语句；
删除视图
语法：
drop view 视图名1，视图名2，。。。。可以同时删除多个
查看视图
跟查看表语法一样：
- desc 视图名
- show create view 视图名；此方法可以查看视图的创建语句
视图的更新
视图也可以使用update、insert、delete进行视图的修改，但是会影响到原始表。故不建议修改视图数据，一般会添加只读权限。
变量
分类：
系统变量：
全局变量：
作用域是服务每次启动将为所有的全局变量赋初始值，针对于所有的会话（即连接都有效），但是不能跨重启有效。
会话变量：
作用域：仅仅针对于当前会话有效。
自定义变量：用户自定义
用户变量：
针对于当前会话有效，同于会话作用域。
步骤：
申明初始化
set @用户变量名=值；或者set @用户变量名：=值或者select @用户变量名：=值
赋值
set @用户变量名=值；或者set @用户变量名：=值或者select @用户变量名：=值。【方式一】
select 字段 into @变量名 from 表；将表中查询的数值赋值给变量名。
查看
select @用户变量名。
局部变量
仅仅在局部有效，即只在begin end中有效。
应用在begin end中且只能放在第一句中。
申明
declare 变量名 类型；
declare 变量名 类型 default 值；
赋值
set 用户变量名=值；或者set 用户变量名：=值或者select @用户变量名：=值。【方式一】
select 字段 into 变量名 from 表；将表中查询的数值赋值给变量名。
比较
系统变量
变量是由系统提供的，属于服务器层面。
使用语法：
查看所有系统变量：
show GLOBAL variables;
查看当前会话变量：
show 【SESSION】 variables;
查看满足条件的部分系统变量
show GLOBAL 【SESSION】 variables like ‘%关键字%’;
查看某个具体的值
select @@GLOBAL 【SESSION】。系统变量名;
为某个系统变量赋值
set GLOBAL 【SESSION】 系统变量名=值；方式一
set @@GLOBAL 【SESSION】。系统变量名=值;方式二
自定义变量
自定义变量：用户自定义
用户变量：
针对于当前会话有效，同于会话作用域。
步骤：
申明初始化
set @用户变量名=值；或者set @用户变量名：=值或者select @用户变量名：=值
赋值
set @用户变量名=值；或者set @用户变量名：=值或者select @用户变量名：=值。【方式一】
select 字段 into @变量名 from 表；将表中查询的数值赋值给变量名。
查看
select @用户变量名。
局部变量
仅仅在局部有效，即只在begin end中有效。
应用在begin end中且只能放在第一句中。
申明
declare 变量名 类型；
declare 变量名 类型 default 值；
赋值
【方式一】
set 用户变量名=值；或者set 用户变量名：=值或者select @用户变量名：=值。【方式二】
select 字段 into 变量名 from 表；将表中查询的数值赋值给变量名。
select 字段1，字段2 into 变量1，变量2 from 表；将表中查询的数值赋值给变量名。
--用户变量演示--
set @temp='变量';
set @temp1='用户';
SELECT concat(@temp1,@temp);
存储过程
类似于面向对象语言的方法
好处：提高代码的重要性、简化操作、减少编译次数且减少了和数据库的连接次数。
含义：一组预先编译好的sql语句的集合，理解成批处理语句。
存储过程创建
语法：
create procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL语句）
end
注意事项：
参数列表包含三部分
参数模式 参数名 参数类型
in varname varchar（10）
参数模式
- in：该参数可以作为输入，该参数需要调用方法传入值
- out：gai参数可以作为输出，可以作为返回值。
- inout：该参数既可以作为输入也可以作为输出。
如果存储过程体仅仅只有一句话，begin、end可以省略。
存储过程每条sql语句的结尾必须加分号。
存储过程结尾可以使用delimiter重新设置。
语法：delimiter 结束标记 eg：delimiter $ 必须在命令行里才可以起作用
存储过程无法修改，只能删掉重新写。
--创建无参存储过程
delimiter //
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO author (au_name,nation) VALUES('卢本伟','zhong'),('及地方','是的'),('fjf','地方');
END//
delimiter ;#使用完后还原结束符号
--创建有in参存储过程
delimiter //
CREATE PROCEDURE myp2(in uname VARCHAR(20),in pwd VARCHAR(20))
BEGIN
DECLARE result int DEFAULT 0;
SELECT count(*) INTO result
FROM users
WHERE users.uname= uname AND users.pwd = pwd;
SELECT if(result<>1,'登录失败','登录成功') output;
END//
delimiter ;#使用完后还原结束符号
--创建有out参存储过程
CREATE PROCEDURE myp3(in uname VARCHAR(20),in pwd VARCHAR(20),out result VARCHAR(10))
BEGIN
DECLARE num int DEFAULT 0;
SELECT count(*) INTO num
FROM users
WHERE users.uname= uname AND users.pwd = pwd;
SELECT if(num<>1,'登录失败','登录成功') INTO result;
END
//
--创建有inout参存储过程
--使传入的参数翻倍后返回
CREATE PROCEDURE myp4(inout a int)
BEGIN
set a = a*2
END
//
存储过程调用
call 存储过程名（参数值）
--调用上面有in参存储过程
set @uname = 'lbw',@pwd='123';--创建用户自定义变量
CALL myp2(@uname,@pwd);--调用存储过程，并传入值
--调用上面有out参存储过程
set @uname = 'lbw',@pwd='123';--创建用户自定义变量
CALL myp3(@uname,@pwd,@result);
SELECT @result;--查看out参数返回值
--调用有inout参存储过程
SELECT 2 into @ma;
CALL myp4(@ma);
SELECT @ma;
删除存储过程
语法：
drop procedure 存储过程名；一次只能删掉一个。
查看存储过程的信息
语法：
show create procedure 存储过程名；
存储过程在mysql库里的proc表里保存着
函数
含义与存储过程类似
与存储过程区别：
- 存储过程可以有0个或多个返回，适合批量插入、批量跟新。
- 函数有且仅有一个返回，适合做数据处理返回一个结果。
创建函数
语法：
create function 函数名 (参数列表) returns 返回类型
begin
函数体
end
注意：
- 参数列表包含参数名、参数类型两部分。
- 函数体肯定会有return语句，如果没有也不会报错。return语句建议放在函数体最后。
- 跟存储过程一样也要使用delimiter设置结束标志。
--创建返回users表信息个数的无参函数
CREATE FUNCTION getcount() RETURNS int
BEGIN
DECLARE num int DEFAULT 0;
SELECT count(*) into num FROM users;
RETURN num;
END
//
调用函数
语法：
select 函数名(参数列表)
select getcount();
函数的查看和删除
查看函数
show create function 函数名；
函数在mysql库里的proc表 里存着
删除函数
drop function 函数名；
流程控制结构
- 分类
- 顺序结构：由上向下一次执行
- 分支结构：程序从两条或者多条路径中选择一条去执行
- 循环结构：在满足一定条件的基础之上，重复的执行一段代码。
分支结构
if函数
功能实现简单的双分支
语法：
select if(表达式1，表达式2，表达式3)；
表达式1为true返回表达式2，反之返回表达式3。
case结构
类似于swith语句，一般用于等值判断，如果返回语句则要加分号
语法：
case 变量|表达式|字段
when 要判断的值 then 返回值1 或 语句1；
when 要判断的值 then 返回值2 或 语句2；
。。。
else 要返回的值n
end case
类似于多重if,一般用于实现区间判断
语法：
case 变量|表达式|字段
when 要判断的条件1 then 返回值1
when 要判断的条件2 then 返回值2
。。。
else 要返回的值n
end case
特点：
- 可以作为表达式，嵌套再其他语句中使用，可以放在任何地方
- 可以作为独立语句使用，只能放在begin end中
- 如果省略else，并且所有的条件都不满足则返回null
--case作为单独语句使用，输入成绩返回等级--
CREATE DEFINER test_case(IN score int)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN score>=60 THEN SELECT 'C';
ELSE
SELECT 'D';
END CASE;
END//
if结构
功能：实现多重分支
语法：
if 条件1 then 语句1；
elseif 条件2 then 语句2；
。。。
else 语句n
end if；
应用必须在begin end中，存储过程和函数中都可以。
--if语句使用，输入成绩返回等级--
CREATE DEFINER=`root`@`%` PROCEDURE `getdeager`(in score int)
BEGIN
if score>=90 AND score<=100 THEN SELECT 'A';
ELSEIF score>=80 THEN SELECT 'B';
ELSEIF score>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END if;
END
循环结构
分类：
- while、loop、repeat
循环控制：
- iterate类似于 continue
- leave类似于break
while
语法：
[标签:] while 循环条件 do
循环体；
end while [标签]；
loop（不用循环控制的话就是死循环）
语法：
[标签:] loop
循环体；
end loop [标签]；
repeat（类似于do while）
语法：
[标签:] repeat
循环体；
until 结束循环的条件
end repeat [标签]；
练习&测试
/*1*/
select concat(last_name,first_name) 姓名, department_id 部门编号, salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees order by 年薪 desc, 姓名 asc;
/*2*/
SELECT concat(last_name,first_name) 姓名, salary 工资 FROM employees WHERE salary not BETWEEN 8000 and 17000 ORDER BY salary DESC;
/*3*/
SELECT concat(last_name,first_name) 姓名, email 邮箱 FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(邮箱) desc,department_id asc;
-- day1 --
#1
select concat(CURDATE(),'+',CURTIME());
#2
SELECT employee_id 员工号,CONCAT(last_name,' ',first_name) 姓名,salary 工资,salary*1.2 newsalary FROM employees;
#3
SELECT last_name 姓名,LOWER(SUBSTR(last_name,1,1)) 姓名首字母,LENGTH(last_name) 姓名长度 FROM employees ORDER BY 姓名首字母 asc;
#4
select CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3,' Dream Salary') FROM employees;
#5
SELECT job_id 工作编号,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE
'D'
END 工作等级
FROM employees ORDER BY 工作等级 asc;
-- day2 --
#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id 国家编号, count(*) 部门数
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY 国家编号 HAVING 部门数>2;
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT j.job_id 工种,department_name 部门名,j.job_title 工种名, MIN(salary) 最低工资
FROM employees e,departments d, jobs j
WHERE e.department_id = d.department_id AND e.job_id = j.job_id
GROUP BY 工种,部门名;
#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#employees Emp #manager Mgr#
#kochhar 101 king 100
SELECT CONCAT(e1.last_name,' ',e1.employee_id) employees,e2.last_name Mname,e2.employee_id "#manager Mgr#"
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id;
-- day3 --
#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.name 女生名,bo.* FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE b.id > 3;
#二、查询哪个城市没有部门
SELECT l.city 城市 FROM locations l
LEFT JOIN departments d ON l.location_id = d.location_id
WHERE d.location_id IS NULL;
#三、查询部门名为SAL或IT的员工信息.
SELECT e.* FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name IN ('sal','IT');#可以看出查询条件不区分大小写
#1、查询和zlotkey相同部门的员工姓名和工资1
SELECT e.last_name 姓名,salary 工资
FROM employees e
WHERE e.department_id = (SELECT department_id FROM employees WHERE last_name = 'zlotkey');
#2、查询工资比公司平均工资高的员工的员工号，姓名和工资.
SELECT employee_id 工号,last_name 姓名,salary 工资
FROM employees
where salary > (SELECT AVG(salary) FROM employees);
#3、查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
SELECT e.employee_id 员工号,e.last_name 姓名,e.salary 工资,t1.平均工资
FROM (SELECT AVG(salary) 平均工资,department_id FROM employees GROUP BY department_id) t1
INNER JOIN employees e ON t1.department_id = e.department_id
WHERE e.salary > t1.平均工资
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id , last_name
FROM employees
WHERE department_id in (SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');
#5.查询在部门的location_ id为1700的部门工作的员工的员工号
SELECT employee_id 工号
FROM employees
WHERE department_id in (SELECT department_id FROM departments WHERE location_id = 1700);
#6.查询管理者是King的员工姓名和工资
#UPDATE employees SET last_name='King' WHERE employee_id=156;
SELECT last_name 姓名, salary 工资
FROM employees
WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name ='K_ING')
#7.查询工资最高的员工的姓名，要求first_ name和last_ name显示为一列，列名为姓.名
SELECT CONCAT(first_name,' ',last_name) 姓·名
FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees);
-------------------------------------------------------------每天进步一点点
第一次更新于 2020.4.13
第二次更新于 2020.4.22
第三次更新于2020.4.28
已完结！
扫一扫
257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
