MySQL 基础总结(小白篇)

##本单元目标
一、为什么要学习数据库
二、数据库的相关概念
DBMS、DB、SQL
三、数据库存储数据的特点
四、初始MySQL
MySQL产品的介绍
MySQL产品的安装 ★
MySQL服务的启动和停止 ★
MySQL服务的登录和退出 ★
MySQL的常见命令和语法规范
五、DQL语言的学习 ★
基础查询 ★
条件查询 ★
排序查询 ★
常见函数 ★
分组函数 ★
分组查询 ★
连接查询 ★
子查询 √
分页查询 ★
union联合查询 √

六、DML语言的学习 ★
插入语句
修改语句
删除语句
七、DDL语言的学习
库和表的管理 √
常见数据类型介绍 √
常见约束 √
八、TCL语言的学习
事务和事务处理
九、视图的讲解 √
十、变量
十一、存储过程和函数
十二、流程控制结构

##数据库的好处
1.持久化数据到本地
2.可以实现结构化查询,方便管理

##数据库相关概念
1、DB:数据库,保存一组有组织的数据的容器
2、DBMS:数据库管理系统,又称为数据库软件(产品),用于管理DB中的数据
3、SQL:结构化查询语言,用于和DBMS通信的语言

##数据库存储数据的特点
1、将数据放到表中,表再放到库中
2、一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
3、表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。
4、表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的”属性”
5、表中的数据是按行存储的,每一行类似于java中的“对象”。

##MySQL产品的介绍和安装
5.5 5.6 5.7版本基础功能都是一样的 最新的是8.0处于测试阶段 不提倡使用 我们要使用最稳定的
课程当中讲解了手动安装mysql服务 但是我们开发的时候直接用phpstudy了 所以我们无需自己安装mysql服务了 但是得了解phpstudy当中mysql目录结构:
bin 核心目录 不需要动 也不需要懂
data 数据存储目录 其实库和表都是以文件夹包含文件的形式存储到了data目录下 千万别删除这个目录下边的东西哦
my.ini 配置文件 平时都只需改动这一个即可 需要了解的如下:
[client]
port=3306 //端口号可以改
[mysql]
default-character-set=utf8 //[mysql] 表示客户端的一些配置 这里表示字符集utf8

[mysqld] //[mysqld] 表示服务端的配置
port=3306 //端口号 可以改
basedir=”E:/phpStudy/PHPTutorial/MySQL/“ //表示mysql的安装位置
datadir=”E:/phpStudy/PHPTutorial/MySQL/data/“ //表示数据以文件形式存储的位置
character-set-server=utf8 //数据存储字符集
default-storage-engine=MyISAM //引擎 分为MyISAM 和 INNODB 默认是前者 但是在设计表的时候不做修改 默认就是系统默认的 如果该成INNODB 那就是INNODB了

#支持 INNODB 引擎模式。修改为 default-storage-engine=INNODB 即可。

#如果 INNODB 模式如果不能启动,删除data目录下ib开头的日志文件重新启动。

sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
max_connections=512 //允许最大链接数量 这个参数实际起作用的最大值(实际最大可连接数)为16384

###MySQL服务的启动和停止
直接通過phpstudy就行

###MySQL服务的登录和退出
方式一:通过mysql自带的客户端
只限于root用户

方式二:通过windows自带的客户端 前提是我们将mysql安装目录下的bin目录添加到了环境变量当中才可以使用DOS命令行里面的mysql命令哦 看这里https://blog.csdn.net/eddy23513/article/de...
登录:
mysql 【-h主机名 -P端口号 】-u用户名 -p密码
mysql -u root -p 回车 然后输入密码即可使用 或者 mysql -u root -proot (注意 这里-p和root之间没有空格哦!前边-u和root之间可以有空格也可以没有空格)

退出:
exit或ctrl+C

###小技巧总结:
dos命令下 cls表示清屏
show databases \g //\g就相当于分号 装逼神奇
select * from user \G // \G表示规范显示 在命令行下就不会出现乱七八糟的给你显示的情况啦 不信你试试看啊
dos命令行下 查询出来的数据如果有中文会显示乱码 那么如何解决呢?只需要执行 set names gbk; 通过set names gbk来设置服务器接收的传出数据时候的字符集为gbk 那么这样客户端因为默认的就是gbk就可以在插入的时候以及查询的时候都能够正常显示汉字了

###MySQL的常见命令

1.查看当前所有的数据库
show databases;
或者
show databases \g //\g就相当于分号 装逼神奇
\g的作用和MySQL中的分号”;”是一样;
\G的作用是讲查找到的内容结构旋转90度,变成纵向结构;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
select database(); 查看自己当前在哪个数据库下
5.创建表
create table 表名(

列名 列类型,
列名 列类型,
。。。
);
6.查看表结构
desc 表名;
show full columns from ci_picture; 查看表的所有信息 包含字段的注释信息
SHOW CREATE TABLE student 查看表的注释和创建 其实就是创建表的代码 create table …….

7.查看服务器的版本
方式一:登录到mysql服务端
select version();
方式二:没有登录到mysql服务端
mysql –version

mysql –V

###MySQL的语法规范
1.不区分大小写,但建议关键字大写,表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要,可以进行缩进 或换行
4.注释
单行注释:#注释文字
单行注释:– 注释文字
多行注释:/* 注释文字 */

sqlyog注册码: Name:huxiaobai key:dd987f34-f358-4894-bd0f-21f3f04be9c1
数据库管理工具使用:
1.查询语句可以保存 sqlyog和Navicat for mysql都可以保存哦 另外 查询语句字体可以鼠标滚动放大缩小
2.sqlyog 查询语句 可以按F9自动执行 但是 Navicat for mysql不可以
3.sqlyog自动规范sql代码 但是Navicat for mysql需要点击美化按钮
4.sqlyog当中按下F12自动美化代码
5.sqlyog当中在写select查询语句的时候 鼠标双击左侧表当中的栏位即可自动给你写上字段 高级得很哦

###SQL的语言分类
DQL(Data Query Language):数据查询语言
select
DML(Data Manipulate Language):数据操作语言
insert 、update、delete
DDL(Data Define Languge):数据定义语言
create、drop、alter
TCL(Transaction Control Language):事务控制语言
commit、rollback

###SQL的常见命令

show databases; 查看所有的数据库
use 库名; 打开指定 的库
show tables ; 显示库中的所有表
show tables from 库名;显示指定库中的所有表
create table 表名(
字段名 字段类型,
字段名 字段类型
); 创建表

desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据

##DQL语言的学习

###进阶1:基础查询
语法:
SELECT 要查询的东西
【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);
特点:
①通过select查询完的结果 ,是一个虚拟的表格,不是真实存在
②要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

注意: select name from aa 这里的这个name在sqlyog或者navicate fro mysql当中会被认为是关键词 所以我们需要加上着重号 select name from aa 这就是着重号的作用了!

###查询的几种方式:
A:字段查询:不讲了
B:常量查询:
select 100; 结果 100
select ‘wahaha’; 结果 wahaha
C:表达式查询:
select 10099; 结果 9900 再来个案例:select manager_iddepartment_id as al from employees where employee_id = 101;
D:查询函数:
select version(); 结果 数据库版本号 常见函数见下边的讲解吧

可以为我们查询的字段起上别名
好处:①便于理解 ②如果要查询的字段有重名的情况 使用别名可以区分开来
实现方式:①select 100 as num;//我们通过as来起别名
②select first_name xing,last_name ming from aa;//中间空格隔开也可以实现起别名的作用
注意 如果别名当中存在特殊字符 比如关键字 那么我们需要将别名用双引号引起来 或者单引号也行 但是mysql手册当中建议使用双引号哦!

去重查询:
比如要查询员工表当中涉及到的所有的部门编号
select DISTINCT department_id from employees;//记住DISTINCT关键词 去重的 后边跟上要去重的字段即可!
注意:
如果使用去重 DISTINCT distinct必须放在要查询字段的开头 那么整条sql语句只能查询一个字段! distinct必须要放在查询字段的开头 所以distinct用来查询不重复记录的条数 如果查询不重复的条数可以使用group by
更多distinct的注意点点击:https://blog.csdn.net/weixin_39166924/arti...

mysql当中加号的问题:
仅仅一个功能:运算符
select 10+99; 两个操作数都是数值类型 则做加法运算
select ‘123’+99; 只要其中一方为字符型 试图将字符型数值转换成数值型进行运算
select ‘john’+99; 如果转换失败则将字符型数值转换成0 这里也就等于0+99=99
select null+99; 只要其中一方为null,则结果肯定为null;

mysql当中拼接字段形成一个字段返回结果:
select concat(‘a’,’b’,’c’); //返回结果为abc
比如我们需要将用户的姓和名拼接在一起怎么搞呢?
select concat(last_name,first_name) as username from employees;//可以将姓和名拼接在一起 并以别名的形式展示出来 强大哦 如果是字段的话不要加引号否则会变成字符串

mysql当中判断是否为空的函数:
IFNULL(字段名称,默认值) 第二个参数表示如果字段为null 那么我们就用后边的默认值来代替
select ifnull(commission_pct,0) from employees where employee_id = 101;//如果commission_pct为null 那么结果就为0;
我们再来个比较复杂点的sql语句:(需求:显示出表employees的全部列,各个列之间用逗号链接 列头显示成out_put)
//因为commissioin_ptc有可能为空 如果用concat链接的话遇到空那么整个都是null 所以得需要使用ifnull()函数做一下判断 如果为null给个默认值
select concat(first_name,’,’,’last_name’,’,’,ifnull(commission_pct,0)) as out_put from employees;

mysql当中的isnull():
功能:判断某字段或者某函数或者某表达式是否为null 如果是返回1 如果否返回0;
select isnull(‘字段名称’) from 表名称;

###进阶2:条件查询
条件查询:根据条件过滤原始表的数据,查询到想要的数据
语法:
select
要查询的字段|表达式|常量值|函数
from

where
条件 ;

注意:语句执行的顺序是 from 先确定是哪个表 然后where 根据条件进行筛选 最后才是select 列出要的哪些字段

分类:
一、条件表达式
示例:salary>10000
条件运算符:

< >= <= = != <>
注意: 不等于建议使用 <>

二、逻辑表达式
示例:salary>10000 && salary<20000

逻辑运算符:

and(&&):两个条件如果同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true
我们主要讲解一下not()的使用:
需求分析:查询部门编号不在90到110之间的 或者工资高于15000的员工信息
select * from employees where not(department_id>=90 and department_id<=110) or salary > 15000;//利用not()取反;
或者这么写
select * from employees where department_id <90 or department_id >110 or salary > 15000;
注意:推荐使用and or not

三、模糊查询
like: 一般和通配符搭配使用 | not like
%:表示任意多个字符 包含0个字符
:下划线表示单个字符 比如查询员工名中第三个字符是e,第五个字符是a的员工名和工资 select last_name,salary from employees where last_name like ‘__e_a%’;
再比如查员工名中第二个字符为下划线的记录:select last_name from employees where last_name like ‘_\
%’;//将第二个下划线转义一下就行的;
也可以这么写select last_name from employees where last_name like ‘&%’ ESCAPE ‘&’;//我们定义&符号为转义字符 通过ESCAPE来进行声明;
注意:like的使用不仅仅局限于字符型 还可以判断int数值型!

betwwen and | not between and select * from employees where employee_id not between 90 and 120; 或者 select * from employees where not(employee_id between 90 and 120);
使用between and的好处:
①提高代码的简洁度
②包含临界值
③两个临界值不能颠倒
案例:
select * from employees where employee_id between 90 and 120;
注意:其实是代替了 >= and <=这种形式

in | not in()
含义:用户去判断某字段的值是否属于in列表中的某一项
特点:
①使用in比使用多个or提高了语句简洁度
②in列表的值类型必须保持一致或者兼容
注意:其实代替了 or or or这种形式

is null | is not null
有时候我们的字段当中的默认值是NULL 如何查询字段值为NULL的记录呢?
如果我们使用 select * from employees where commission_pct = NULL 是不对的 因为 mysql当中的 = 等于号 是无法判断NULl的! 所以我们就得需要使用 is null来判断
正确的写法如下:
select * from employess where commission_pct IS NULL ;

假设查询某个字段非NULL 正确的写法:select * from employees where commission_pct IS NOT NULL;
注意:
①=或者<>不能用户判断null值
②is null 或者 is not null 可以判断NULl;
③ is null仅仅能判断null值 就是说判断一个字段的值为null;

<=> 安全等于
安全等于可以判断NULL值 又可以判断普通的数值
正确的写法:
select * from employees where commission_pct <=> NULL;

###进阶3:排序查询

语法:
select
要查询的东西
from

where
条件

order by 排序的字段|表达式|函数|别名 【asc|desc】
注意:
asc 升序
desc 降序
order by 子句中可以支持单个字段 多个字段 表达式 函数 别名
order by 子句一般是放在查询语句的最后边 limit子句除外
order by 默认是升序排列

关于排序的案例:
a:按照普通字段来进行排序(假设按照员工id进行升序排序):
select * from employees order by employee_id asc;
b:按照数据表当中不存在的字段进行排序 比如 按照员工的年薪进行排序 数据库当中salary表示的是月工资 不存在年薪 我们就得自己算
select employee_id,salary12(1+ifnull(commission_pct,0)) as nianxin from employees order by salary12(1+ifnull(commission_pct,0)) desc;
或者使用别名:
(按照别名进行排序) select employee_id,salary12(1+ifnull(commission_pct,0)) as nianxin from employees order by nianxin desc;
c:按照函数进行排序(比如按照姓名的长度显示员工的姓名和工资)
select length(last_name) name ,last_name,salary from employees order by name desc;//length()函数获取字段字符长度 我们按照这个进行排序
d:多层排序 (比如先按照工资升序 再按照员工编号降序)
select * from employees order by salary asc,employee_id desc;//表示先按照salary工资进行升序排序,如果遇到工资一样的 那么再按照employee_id进行降序排序;是先按照什么排序 遇到相同结果再按照什么排序的原则!

###进阶4:常见函数
调用:
select 函数名(实参列表) 【from表】
上边讲到过的函数 concat() ifnull() isnull() length()
请往下看:
一、单行函数
1、字符函数
concat拼接 select concat(‘last_name’,’_’,’first_name’) from employees;//将字段拼接成一个字符串形式 可以使用别名
substr截取子串 substr(‘李莫愁爱上了陆展元’,6);//结果为陆展元 表示截取从第六个字符开始往后的全部字符;
sql当中索引都是从1开始的
substr(‘李莫愁爱上了陆展元’,1,3);//结果为李莫愁 表示从索引1开始 往后截取3个字符;

upper转换成大写 select upper(‘huxiaobai’);//全部大写
lower转换成小写 select concat(upper(last_name),’_’,lower(first_name)) from employees;//看到没 函数之间可以相互嵌套使用
trim去前后指定的空格和字符 select trim(‘ 张翠山 ‘);//张翠山 作用去掉两边的空格
select trim (‘a’ from ‘a张翠山a’);//张翠山 作用 表示去掉两边的a字符;
ltrim去左边空格
rtrim去右边空格
replace替换 select replace(“周芷若爱上了张无忌周芷若”,’周芷若’,’赵敏’);//表示用赵敏替换掉周芷若
lpad左填充 select lpad(‘殷素素’,12,’a’);//表示总长度为12个字符 在左侧填充a 直到整个字符长度为12位置 也就是aaaaaaaaa殷素素;
rpad右填充
instr返回子串第一次出现的索引 select instr(‘杨不悔爱殷六侠上了殷六侠’,’殷六侠’) as out_put;//找到殷六侠第一次出现的索引值
length 获取字节个数 select length(“中国lihai”);//结果为11 因为一个汉字为3个字节 英文一个字母为1个字节 这是在utf8的情况下

2、数学函数
round 四舍五入 select round(‘1.567’,2);//四舍五入 保留两位小数 1.57
select round(‘1.46’);//默认是1 不保留小数点
rand 随机数
floor向下取整 select floor(‘1.58’);//1 返回小于等于改参数的最大整数;
ceil向上取整 select ceil(‘1,21’);//2 向上取整 返回>=该参数的最小整数
mod取余 select (10,3);//1 取余数
truncate截断 select truncate(‘1.89’,1);//1.8 表示保留一位小数 不四舍五入也不取整 直接截断
3、日期函数
now当前系统日期+时间 select now();//2019-12-21 09:09:23
curdate当前系统日期 select curdate();//2019-12-21
curtime当前系统时间 select curtime();//12:09:09
YEAR 获取指定时间的年份 select year(‘2019-1-1’);//2019
MONTH 获取指定时间的月份
MONTHNAME 英文版月份 select monthname(‘2019-9-1’);//September 英文9月
DAY 获取指定时间里面的天
hour 获取指定时间里面的小时数
minute 获取指定时间里面的分钟数
second 获取指定时间里面的秒数
str_to_date 将字符转换成日期 select * from employees where hiredate = str_to_date(‘4-3 1991’,’%c-%d %Y’);//客户端传递过来的格式可能是4-3 1992这样的 但是我们库里面是1992-4-3形式的 所以通过str_to_date()进行转换即可
select str_to_date(‘4-3-1992’,’%c-%d-%Y’);//1992-4-3
date_format将日期转换成字符 select last_name,DATE_FORMAT(hiredate,’%m月/%d日 %Y年’) from employees;//假设字段hiredate = 2019-1-1 那么转变之后就是 1月1日 2019年
from_unixtime() 将时间戳直接转变成时间格式 select from_unixtime(1157219870);//2016-08-22 12:11:10
// mysql查询当天的记录数:
$sql=”select * from message Where DATE_FORMAT(FROM_UNIXTIME(chattime),’%Y-%m-%d’) = DATE_FORMAT(NOW(),’%Y-%m-%d’) order by id desc”;
UNIX_TIMESTAMP() 日期转变成unix时间戳 Select UNIX_TIMESTAMP(‘2006-11-04 12:23:00’);//1162614180
DATEDIFF() 计算两个时间相差的天数 select datediff(‘2019-10-1’,’2019-9-29’);//大的时间在前 小的时间在后
以上为常用时间函数 具体可了解:https://www.cnblogs.com/geaozhang/p/674045... 比较全面一些哦

4、流程控制函数
if 处理双分支
案例:
select if(10>5,’是的’,’不是’); //if()里面第一个是表达式 一定是返回true或者false 如果是true则返回第二个参数的值 如果是false则返回第三个参数的值 很类似php里面的三元运算;
再来个案例
select last_name,if(commossion_pct is null,’有奖金’,’没奖金’) as jj from employees;

case语句 处理多分支

情况1:处理等值判断

格式:
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
when 常量3 then 要显示的值3或者语句3

else 要显示的值n或者语句n;
end;
案例:
查询员工的工资 要求 部门号=30 显示工资的1.1倍
部门号=40 显示工资的1.2倍
部门号=50 显示工资的1.3倍
其他部门 显示工资为原工资
sql如下:
select salary yugongzi,department_id,
case department_id
when 30 then salary1.1
when 40 then salary
1.2
when 50 then salary*1.3
else
salary
end
as xingongzi
from employees;

情况2:处理条件判断

格式:
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者条件2
when 条件3 then 要显示的值3或者条件3
else 要显示的值n或者语句n
end;

sql如下:
select salary,
case
when salary>20000 then ‘A’
when salary>15000 then ‘B’
when salary>10000 then ‘C’
else ‘D’
end as 工资级别
from employees;

5、其他函数
version版本
database当前库
user当前连接用户

二、分组函数

sum 求和 select sum(salary) from employees; //在计算的时候会忽略掉字段里面的null值
max 最大值 select max(salary) from employees; //在计算的时候会忽略掉字段里面的null值
min 最小值 select min(salary) from employees; //在计算的时候会忽略掉字段里面的null值
avg 平均值 select avg(salary) from employees; //在计算的时候会忽略掉字段里面的null值
count 计数 select count(salary) from employees; //在计算的时候会忽略掉字段里面的null值

还可以组合使用
select sum(salary) 和,round(avg(salary),2) 平均,max(salary) 最高 from employees;

特点:
1、以上五个分组函数都忽略null值,除了count()
2、sum和avg一般用于处理数值型(只处理数值类型即可)
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果
案例:
select count(distinct salary) as sa,count(salary) as ssaa from employees;//表示对salary去重之后再计算有多少条记录
其他的sum max min avg都可以搭配distinct来使用
4、count的参数可以支持:
字段、
、常量值,一般放1
案例: select count(*) from employees;
select count(1) from employees;//啥意思 其实就是另外多加了一列 值都为1 然后去统计1的个数 当然也可以写count(2) count(3) 都是可以的
select count(‘字段名称’) from employees;// 如果遇到值为null的就会被忽略掉该条记录 所以不建议使用

效率问题:
在MYISAM引擎的前提下 count()效率是最高的 因为内部已经计算好了总条数 直接返回 不需要再计算
在INNODB引擎下 count(
) 和count(1)效率差不多 但是绝对比count(‘字段名称’)的效率要高很多 因为count(字段名称)还得需要去计算 比较费时

建议使用 count(*)

5.和分组函数一起查询的字段要求是group by 后的字段;

##进阶5:分组查询
语法:
select 查询的字段,分组函数
from 表
group by 分组的字段

//案例1
比如要查询每个部门的平均工资
select max(salary),job_id from employees group by job_id;//上边我们讲过和分组函数一起查询的字段要求是group by 后的字段 比如这里的job_id 是按照它分组的 那么我们就可以让job_id和分组函数一起被查询出来;
//案例2
查询有奖金的每个领导首先员工的最高工资
select max(salary),manager_id from employees where commission_pct is not null group by manager_id;//按照manager_id分组 所以可以和分组函数一起放在select的后边被查询出来

having的使用:
需求:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
①先查询每个工种有奖金的员工的最高工资
select max(salary),job_id from employees where commission_pct is not null group by job_id;
②根据①的结果继续筛选 查出最高工资>12000的记录出来
select max(salary),job_id from employees where commission_pct is not null group by job_id having max(salary) > 120000;
注意:
我们在写sql语句的时候一定要学会分析 先把主体查找出来 然后再看条件是写在where里面还是having里面 如果原始表当中有的就写到where后边 如果原始表当中没有 在分组之后的数据里面的就通过having继续筛选出来!

按照表达式或者函数分组:
需求:按员工姓名的长度分组 查询每一组的员工个数 筛选员工个数>5的有哪些
select count(),length(last_name) len_name from employees group by length(last_name) having count() > 5;//这里我们通过length() 按照员工姓名长度进行的分组 也就是按照函数进行的分组

按照多个字段分组
需求:查询每个部门每个工种的平均工资 关于group by 多个字段分组 详情参考:https://blog.csdn.net/qq_27361945/article/...
select avg(salary),department_id,job_id from employees group by job_id,department_id;//每个部门的每个工种 那么部门一定是有重复的出现的

特点:
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
针对的表 位置 关键字
分组前筛选: 原始表 group by的前面 where
分组后筛选: 分组后的结果集 group by的后面 having

4、可以按多个字段分组,字段之间用逗号隔开,多个字段之间没有顺序要求
5、可以支持排序
6、having后可以支持别名
7.分组函数做条件 那一定是放在having的后边 属于分组后筛选
8.能用分组前筛选的 就优先考虑使用分组前筛选;性能会提高很多这样;
9.可以按照表达式或者函数进行分组

##进阶6:多表连接查询

按照年代分类:
sql92标准:仅仅支持内连接
sql99标准[推荐]:支持内连接+外连接(左连接+右连接)+交叉连接

按照功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接

一、传统模式下的连接 :等值连接

sql92标准 (仅仅支持内连接)
1.等值连接(条件用等号来连接的)

案例1:查询员工名称和对应的部门名称

select last_name,department_name from employees,departments where employees.department_id = departments.department_id;
//结果是106行 但是数据表当中是107行 为什么少了一行呢?是因为有一个员工的部门编号是空的 所以就少了一条 其实这里讲的等值连接就相当于sql99当中的内连接 意思就是必须两边同时存在我才给你显示 不然 连接的两张边有一方为空或者不存在那么整条记录就都不显示了!inner join一样一样滴
//另外如果查询的字段两边都存在 那么最好加上表名称进行限定 如果都是一方所独有的 加不加的都行!
//另外最好给表名称起个别名 可读性更高 然后字段前边都加上表别名最好了哦!

案例2:查询有奖金的每个部门的部门名称和部门的领导编号和该部门的最低工资

分析:要查什么 部门名称 领导编号 以及 最低工资 department_name,d.manager_id,min(salary);
筛选条件是什么 有奖金的 commission_pct is not null
关联关系是什么 d.department_id = e.department_id
每个部门的 那么就要进行分组 部门名称和领导编号同时进行分组
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 department_name,d.manager_id;

总结:
1.等值连接的结果 = 多个表的交集
2.n表连接,至少需要n-1个连接条件
3.多个表不分主次,没有顺序要求
4.一般为表起别名,提高阅读性和性能
5.可以搭配前面介绍的所有子句使用 比如排序分组筛选都可以使用

2.非等值连接

案例:查询员工的工资和工资级别

select salary,grade_level from employees e,job_grades g where salary between g.lowest_sal and g.highest_sal;
//这就是非等值连接 好他妈操蛋!

3.自连接 (employee_id 员工id last_name 员工名 manager_id 上级领导id 其中manager_id 其实就是 employee_id ) 字段都是在一张表里面

案例:查询员工名和上级的名称

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语法:通过join关键字实现连接

含义:1999年推出的sql语法
支持:
等值连接、非等值连接 (内连接)
外连接
交叉连接

具体如下:
内连接:inner
等值连接
非等值连接
自连接
外连接:
左外连接 left 【outer】
右外连接 right 【outer】
全外连接 full 【outer】 mysql不支持
交叉连接 cross

语法:

select 字段,…
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处:语句上,连接条件和筛选条件实现了分离,简洁明了!

内连接 精讲
1.等值连接 等值连接的结果 = 多个表的交集

案例1:查询员工名 部门名

select last_name,department_name from employees e inner join departments d on e.department_id = d.department_id;//查出来的和sql92语法一样的结果都是106条因为有一条没有部门编号 两张表当中都存在的时候才会显示出该条记录

2.非等值连接

案例1:查询员工的工资级别

select salary,grade_level from employees e join job_grades g on e.salary between g.lowest_sql and g.highest_sal;

3.自连接 (employee_id 员工id last_name 员工名 manager_id 上级领导id 其中manager_id 其实就是 employee_id ) 字段都是在一张表里面

案例:查询员工的名字和上级领导的名字

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.manager_id=m.employee_id;

sql92

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

外连接:
应用场景:用户查询一个表当中有 另外一个表没有的记录可以使用
特点:
1.外连接的查询结果为主表当中所有的记录
如果从表当中有和它匹配的 则显示匹配的记录值
如果从表当中没有和它匹配的 则显示null
2.左外连接 left join 左边的是主表
右外连接 right join 右边的是主表
3.左外和右外交换两个表的顺序可以实现同样的效果
注意 你要查询的信息主要来自哪个表 那么哪个表就是主表

案例:

select b.name from beauty b left join boys on b.boyfriend_id = bo.id where bo.id is null;//left outer join 的左边为主表 beauty为主表

全外连接:
mysql不支持

交叉连接:
其实就是笛卡尔乘积
select b.,bo. from beauty b cross join boys bo;//比如b表当中4条 bo表当中11条 那么查询出来的结果就是11*4=44条 说白了就是笛卡尔乘积

##进阶7:子查询
出现在其他语句中的select语句 称为子查询或者内查询
外部的查询语句 称为主查询或外查询

分类:
按照子查询出现的位置划分:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面(重点)
标量子查询(单行) √
列子查询(多行) √
行子查询
exists后面(相关子查询)
表子查询
按照结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)

开始讲解:
一.where或者having后面
1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多行多列)

特点:
①子查询都是放在小括号里面的
②子查询一般放在条件的右侧
③标量子查询 一般搭配着单行操作符使用

< = <> <= >=其实就是操作符
列子查询 一般搭配着多行操作符使用
in any/some all
④子查询的执行优先于主查询 主查询的条件用到了子查询的结果

a.标量子查询 (子查询的结果为一行一列 说白了就是查询出一个字段的值) 符合特点当中的1,2,3标准吧!

案例1:查询谁的工资比Abel高?

select * from employees where salary > (
select salary from employees where last_name = ‘Abel’
);

案例2:返回job_id与141号员工相同 salary比143号员工多的员工所有信息 (一条sql里面可以有多个子查询 但是这里都是标量子查询)

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
);

案例3:返回公司工资最少的员工的last_name job_id salary (我们还可以在子查询当中使用分组函数)

select last_name,job_id,salary from employees where salary = (
SELECT MIN(salary) FROM employees
);

案例4:查询最低工资大于50号部门最低工资的 部门的id和其他最低工资 (having后边也可以使用标量子查询)

查询处每个部门的最低工资 然后再筛选出大于50号部门的最低工资 的 这些部门的最低工资
SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary) > (
SELECT MIN(salary) FROM employees WHERE department_id = 50
)

b.列子查询(多行子查询):

案例1:返回location_id是1400或者1700的部门中的所有员工的姓名(子查询当中返回的结果是一列多行 也就是列子查询或者称为多行子查询 那么我们就需要使用in了哦 符合特点当中讲到的第③点)

select last_name from employees where department_id in (
select distinct department_id from departments where location_id in (1400,1700)
);

案例2:返回其他工种中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id、以及salary

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < ANY(
SELECT DISTINCT salary FROM employees WHERE job_id = ‘IT_PROG’
) and job_id <> ‘IT_PROG’;

其实上边的any我们完全可以用max()来代替 你想想啊 salary小于里面的任何一个值 那不就是小于里面的最大值吗?所以可以写成:
SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < (
SELECT max(salary) FROM employees WHERE job_id = ‘IT_PROG’
) and job_id <> ‘IT_PROG’;

借此我们介绍一下ANY的用法:
ANY 关键字必须接在一个比较操作符的后面,表示与子查询返回的任何值比较为TRUE ,则返回 TRUE
比如 where salary > any(列子查询查询出来的多行的值); 啥意思啊 就是说salary大于任何一个列子查询查询出来的结果集里面的值

案例3:返回其他工种中比job_id为‘IT_PROG’工种所有工资都低的员工的 员工号 姓名 job_id 以及 salary

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < ALL(
SELECT DISTINCT salary FROM employees WHERE job_id = ‘IT_PROG’
) and job_id <> ‘IT_PROG’;

其实上边的any我们完全可以用min()来代替 你想想啊 salary小于里面的所有的值 那不就是小于里面的最小值吗?所以可以写成:
SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < (
SELECT min(salary) FROM employees WHERE job_id = ‘IT_PROG’
) and job_id <> ‘IT_PROG’;

借此我们介绍一下ALL的用法:
比如 where salary < all(列子查询查询出来的多行的值); 啥意思啊 就是说salary小于所有的列子查询查询出来的结果集里面的值 那就是小于列子查询查询结果里面的最小值呗

扩展: 在列子查询当中
in 可以被替换成 = any(列子查询结果集) in表示里面的任何一个 那么 = any()也是表示等于里面的任何一个的意思
not in 可以被替换成 <> all(列子查询结果集) not in 表示不等于里面的任何一个 那么 <> any() 也是表示不等于里面的任何一个的意思

c:行子查询 (结果是一行多列或者多行多列的结果集)

案例1:查询员工编号最小并且工资最高的员工的信息

按照我们之前学过的列子查询方法 我们可以一天sql语句当中使用两个列子查询查找出来 比如:
SELECT * FROM employees WHERE employee_id = (
SELECT MIN(employee_id) FROM employees
) AND salary = (
SELECT MAX(salary) FROM employees
)

现在我们再用行子查询的方式来查找:(规律 如果我们发现列子查询当中有两个或者更多个列子查询都是等号链接的情况 那么我们就可以使用行子查询)
SELECT * FROM employees WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary) FROM employees
);

二.select后边的子查询(仅仅支持标量子查询 即返回一行一列)

案例1:查询每个部门的员工个数

SELECT d.,(
SELECT COUNT(
) FROM employees e WHERE e.department_id = d.department_id
) 个数
FROM departments d;

三.from后边的子查询(支持表子查询 多行多列或者一行一列都行)

需要特别注意:要求将子查询的结果充当一张表 必须起别名

案例:查询每个部门的平均工资的工资等级

分析:先查询每个部门的平均工资 然后将结果充当成一张表 起个别名 然后再和工资级别表相互关联
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

案例: 查询各部门中工资比本部门平均工资高的员工的员工号 姓名和工资

– 分析 首先查询各部门的平均工资 然后将各部门的平均工资和employees表相互关联 进行筛选
SELECT employee_id,last_name,salary,e.department_id FROM employees e INNER JOIN (
SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id WHERE salary > ag_dep.ag

四:exists后面(相关子查询)
语法:
exists(完整的查询语句)
结果: 1或者0
作用:判断是否有值存在

案例1:查询有员工的部门名称 (这里的exists()其实就是在判断查询的每个员工的department_id是否和employees里面的department_id相等 相等说明存在 不相等就不展示 返回的是1或者0)

SELECT department_name FROM departments d WHERE EXISTS(
SELECT * FROM employees e WHERE d.department_id = e.department_id
);

特别提醒:
凡是exists()相关查询的地方都能用in来代替
为什么叫相关子查询 因为在exists()当中用到了主表里面的字段 相关联了 所以就叫相关子查询
exists()里面一般都是一个连接查询;

下边是用in来代替的写法:
SELECT department_name FROM departments WHERE department_id IN (
SELECT department_id FROM employees
);

案例2:查询没有女朋友的男神信息:(常规的写法可以查出来有女朋友的男神信息 有的都返回1 如果要查找没有的 那么负负得正 加上not 也就是not 0 不就是正了吗 就查找出来了没有女朋友的男神的信息了)

select b.* from boys b where not exists(
select 1 from beauty bb where bb.boyfriend_id = b.id
);

如果要使用in那么就要用not in()

经典习题练习:

查询和Zlotkey相同部门的员工名和工资

– 分析 查询处Zlotkey所在部门的id 然后再查employees表当中部门id = 查找出来的部门的id
SELECT last_name,salary FROM employees WHERE department_id = (
– 查询处Zlotkey所在部门的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
);

查询各部门中工资比本部门平均工资高的员工的员工号 姓名 和 工资

– 分析 首先查询各部门的平均工资 然后将各部门的平均工资和employees表相互关联 进行筛选
SELECT employee_id,last_name,salary,e.department_id FROM employees e INNER JOIN (
SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id WHERE salary > ag_dep.ag

查询平均工资最低的部门信息和该部门的平均工资

– 先查询每个部门的平均工资 然后查询每个部门平均工资最低的信息字段包括salary和部门id 然后结果集喝departments表进行关联查询
SELECT * FROM departments d INNER JOIN (
SELECT * FROM (SELECT AVG(salary) ag,department_id dd FROM employees GROUP BY department_id HAVING dd IS NOT NULL) temp WHERE temp.ag = (
SELECT MIN(tt.ag) FROM (SELECT AVG(salary) ag,department_id dd FROM employees GROUP BY department_id HAVING dd IS NOT NULL) tt
)
) aa on d.department_id = aa.dd

##进阶8:分页查询

应用场景:

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法:

select 字段|表达式,…
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引,】条目数;

特点:

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式:select * from 表 limit (page-1)*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

##进阶9:联合查询
场景:
要查询的结果来自于多个表 并且多个表没有直接的链接关系 但查询的信息一致时 就可以使用union联合查询

引入:
union 联合、合并 将多条查询语句的结果合并成一个结果

语法:

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
…..
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点:

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同 也就是说union前边查询的字段是id name union后边查询的字段可以是tid tname 但是尽量要保持id tid字段类型一致 name和tname字段类型一致才有意义 但是前后字段的列数必须一致
3、union代表去重,union all代表不去重 去重的意思是整条记录都重复 比如 1 huxiaobai 1 huxiaobai 有两条这样的记录 union是一样会去重的 但是union all就不会去重了哦

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

//之前的写法是这样的
select * from employees where email like ‘%a%’ or department_id >90;
//现在我们使用union来进行联合查询
select * from employess where email like ‘%a%’
union
select * from employess where department_id >90;
//其实就是将多个查询结果进行了合并

##DML语言

###插入

语法:
insert into 表名(字段名,…)
values(值1,…);

这种语法支持一次性插入多条记录
insert into beauty values(1,’a’,’aa’),(2,’b’,’bb’),(3,’c’,’cc’);

这种语法还支持子查询 ☆
insert into beauty(id,name,phone) select 26,’a’,’13222’;
//就是如此的神器 类似于从一张表里面读取某条记录直接插入到另一张表当中去 并且不用写values()了 后边直接跟行子查询

语法二:
insert into 表名
set 列名 = 值,列名 = 值,列名 = 值

这种方式不支持批量插入!
这种方式不支持子查询!

特点:

1、字段类型和值类型一致或兼容,而且一一对应
2、可以为空的字段,可以不用插入值,或用null填充
3、不可以为空的字段,必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略,但默认所有字段,并且顺序和表中的存储顺序一致

###修改

修改单表语法:

update 表名 set 字段=新值,字段=新值
【where 条件】

修改多表语法:
sql92语法:
update 表1 别名1,表2 别名2
set 字段=新值,字段=新值
where 连接条件
and 筛选条件

sql99语法:
update 表1 别名
inner|left|right join 表2 别名
on 链接条件
set 列=值……
where 筛选条件

案例:修改张无忌的女朋友的手机号为114

update boys bo
inner join beauty b on bo.id = b.boyfriend_id
set b.phone = 114
where bo.boyName = ‘’张无忌;

###删除

方式1:delete语句

单表的删除: ★
delete from 表名 【where 筛选条件】

多表的删除:
sql92语法:(要删除哪张表里面的 那么delete后边就写谁的别名 如果两个表都删那么就都写上这两个别名)
delete 别名1,别名2
from 表1 别名1,表2 别名2
where 连接条件
and 筛选条件;

sql99语法:(要删除哪张表里面的 那么delete后边就写谁的别名 如果两个表都删那么就都写上这两个别名)
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 链接条件
where 筛选条件

案例:删除张无忌女朋友的信息(要删除的是张无忌女朋友的信息 那么就写beauty的别名b)

delete b
from beauty b
inner join boys bo on b.boyfriend_id = bo.id
where bo.boyname = ‘张无忌’;

案例:删除黄晓明的信息以及他女朋友的信息(要删除的是黄晓明的信息 和 他女朋友的信息 涉及到两张表 所以写两个表的别名)

delete b,bo
from beauty b
inner join boys bo on b.boyfriend_id = bo.id
where bo.boyname = ‘黄晓明’;

方式2:truncate语句

truncate table 表名

两种方式的区别【面试题】

#1.truncate不能加where条件,而delete可以加where条件

#2.truncate的效率高一丢丢

#3.truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始

#delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始

#4.truncate删除不能回滚,delete删除可以回滚

##DDL语句

###库和表的管理

一.库的管理:
创建:create
create database if not exists book;//创建数据库 如果存在就不再创建了

删除:drop
drop database if exists book;//如果存在就删除数据库

修改:alert
数据库基本不修改 因为修改很容易出现不可预知的错误!
如果要修改数据库名称 那么直接去mysql安装目录下的data目录里面找到对应的文件夹进行修改 但是前提是要停止mysql服务 然后再修改 再重启
能改什么呢?
更改库的字符集 alter database book character set gbk;

二.表的管理:
创建:create
create table if not exists 表名(
列名 列的类型【(长度) (约束)】,
列名 列的类型【(长度) (约束)】,
列名 列的类型【(长度) (约束)】,
列名 列的类型【(长度) (约束)】
)

删除:drop
drop table books;
drop table if exists books;//存在就删除 不存在就不删除了哦 避免报错

修改:alert
①修改列名
alter table book change column bookname bkname varchar(100);// 如果之前有类型和长度的话 最好带上类型和长度
②修改列的类型或者约束
alter table book modify column bkname int(10);//修改列的类型为int类型
③添加列
alter table book add column annual double;
④删除列
alter table book drop column annual;
⑤修改表名
alter table book rename to books;

总结:

通常的建库和建表的写法:
drop database if exists wahaha;
create database wahaha;

drop table if exists book;
create table book (
id int(11),
name varchar(122),
……
);

三.复制表
仅仅复制表的结构:
create table aa like bb;//复制表结构 bb有数据 但是aa没有数据
复制表结构和数据:
create table aa select * from bb;
只复制部分数据过去 结构是一样的:
create table aa select * from bb where id >40;
仅仅复制某些字段: id name passwor字段过去了 但是数据查询的时候条件不成立 所以数据就没过去!
create table aa select id,name,password from bb where 1=2;

###常见类型

整型:
tinyint
有符号:-128 到 127
无符号:0到255
smallint
有符号:-32768 到 32767
无符号:0到65535
mediumint
有符号:-8388608 到 8388607
无符号:0到1677215
int/integer
有符号:-2147483648 到 2147483647
无符号: 0到4294967295
bigint
有符号:-9223372036854775808 到 9223372036854775808
无符号: 0到9223372036854775808*2+1

特点:
整数类型 分为 无符号和有符合 无符号不能插入负数 有符号的可以插入负数;
①如果不设置无符号或者有符号 那么默认就是有符号 如果想设置无符号 需要添加关键字unsigned关键字 比如去修改字段的约束 alter table books modify column price int(10) unsigned; unsigned其实就是一种约束! 如果设置了无符号但是插入了负数 那么插入进去的将是0;
②如果插入的数值超出了整数的范围 会报out of range异常 并且插入的是临界值
③如果不设置长度会有默认的长度
④长度代表了显示的最大宽度 如果不够会用0在左边填充 但必须搭配zerofill使用 使用了zerofill那么默认就变成了无符号的了

小数:
浮点型
float(M,D)
double(M,D)
定点型
dec(M,D)
decimal(M,D)

特点:
①M 整数部分+小数部分的和 D 小数部分
②M和D都可以省略
如果是decimal 则M默认为10 D默认为0;
如果是float和double 则会根据插入的数值的精度来决定精度

注意:在涉及到小数运算的时候为了避免损失精度 我们最后将字符类型设置为demical类型 习惯于用decimal吧 如果只是存储那么就用float 因为是单精度 mysql执行效率更高比double双精度的 以及 定点型类型decimal

字符型:
较短的文本:
对比char和varchar:
char
0-255个字符数
代表固定长度字符
比较耗费空间
比如char(10)然后插入中国两个字符但是也是给你开了10个字符的空间
效率高
长度省略 默认为1
适合存放固定位置的字符 比如身份证号或者手机号码等
varchar
0-65535个字符
代表可变长度字符
比较节省
比如varchar(10)然后插入中国虽然规定10个字符但是实际就给你开了2个字符的空间
效率低
长度不可以省略
适合存放长度不固定的字符

较长的文本:text blob(较长的二进制数据

枚举类型:(插入的值不区分大小写)
规定只能插入固定的字符串
create table if not exists(
status ENUM(‘1’,’2’,’3’),
);
比如我们在创建表的时候用的枚举 status字段就是枚举类型 那么插入的时候只能插入是1 2 3这里面的任意一个数值 别的插入就会为空NULL

set()用语保存集合 不过多解释了

日期型:
日期的值必须用单引号引起来
date 只能保存日期 没有时间 2019-01-21
datetime 保存日期和时间 2018-12-21 12:32:23 重点
timestamp 保存日期和时间 最大到2038年的某个时刻 重点
time 只能保存时间 时分秒 H:i:s
year 只能保存年 2019

特点:
datetime 8字节 范围较大1000年-9999年 不受时区影响
timestamp 4字节 范围较小1970年-2038年 受时区影响

总结:所选择的类型越简单越好 能保存数值的类型越小越好

###常见约束
约束的含义:一种限制 用于限制表中的数据 为了保证表中的数据的准确和可靠性
create table 表名(
字段名 字段类型(长度) 约束,
字段名 字段类型(长度) 约束,
字段名 字段类型(长度) 约束
……
)

一共有六大约束:
NOT NULL //该字段的值不能为空! 比如姓名 学号等关键性字段就要建立非空约束
DEFAULT //默认值 用于报章该字段的值有默认值 比如一个班全是男生 那么性别 默认值为男
UNIQUE //唯一约束 用于报章该字段的值具有唯一性 但是可以为空! 比如座位号 必须唯一但也可以为空 写就要保持唯一 不写也没事
CHECK //mysql当中不支持
PRIMARY KEY //主键约束 用于报章字段的值具有唯一性并且还得是非空 比如学号 员工编号等可设置为主键
FOREIGN KEY //外键约束 用于限制两个表的关系 用于保证该字段的值必须来自于主表的管理关联列的值 是在从表添加外键约束引用主表某列的值

添加约束的时机:
约束一定是在设计创建表的时候加上约束
修改表时候去添加约束 但是 还没有在加入数据之前修改

主键和唯一键的区别:
保证唯一性 是否允许为空 一个表中可以有几个 是否允许组合
主键 √ 不允许 至少有1个也可以没有主键 √但不推荐 id username组合为主键那么插入两个1 huxiaobai是不行的 其实两字字段链接作为主键来看待
唯一键 √ 允许(但只能有一个为空 因为null两个为空也就违背了唯一性) 可以有多个唯一键 √但不推荐 组合唯一键和组合主键类似的情况

注意:当我们要求数据表当中两个字段拼接起来必须要唯一的时候可以考虑使用组合主键或者组合唯一键 但是一般不推荐使用 因为不稳定

约束的分类:
列级约束:
六大约束语法上都支持 但是外键约束没有效果!不要写外键约束
表级约束:
除了非空、默认 其他的都支持

创建表示添加约束:
1.添加列级约束:
drop database if exists students;
create database students;
use students;
create table stuinfo(
id int(11) primary key,#主键id
stuname varchar(30) not null,#学生姓名
gender char(1),#性别
seat int(11) unique,#座位号唯一约束
age int(3) default 18 #年龄默认值为18
);

注意:主键约束 外键约束 唯一键约束会自动生成索引!
注意:可以为一个字段设置多个约束 比如 not null unique就可以联合在一起
查看表里面有哪些索引:
show INDEX from stuinfo;

#外键的特点:(外键必须是主键或者唯一键)
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要去一致或者兼容 名称无要求
3.主表的关联列必须是一个key(一般是主键或者唯一键)
4.插入数据的时候要先插入主表再插入从表
5.删除数据要先删除从表 然后再删除主表

创建表时添加约束 alter table命令去修改字段的约束 前边都讲过了哦!

注意:

#添加外键:
alter table stuinfo add foreign key(majorid) references major(id);

#标识列
又称为自增长列
含义:可以不用手动的插入值 系统提供默认的序列值
创建表时就要设置标识列
标识列特点:
标识列必须和主键搭配吗?不一定 但要求是一个key 比如主键 唯一键都行 或者之后我们自己减的其他键
一个表只能有一个标识列 也就是至多一个
标识列的类型:int dobule float都可以的 其他不行 也就是说只能是数值型
可以通过 set auto_increment_increment = 3 设置步长
可以通过手动插入之来设置起始值;

#事务控制语言
show engines;//查看数据库支持的引擎 也会显示当前数据库默认的引擎是哪一个;
只有InnoDb数据库引擎才支持事务其他的不支持哦!

###事务:
一个或者一组sql语句组成一个执行单元 这个执行单元要么全部执行 要不全部不执行 成功就成功了 不成功会回滚到原始状态

###事务的属性特点
原子性:是指事务是一个不可分割的工作单位 事务中操作要么都发生要么都不发生
一致性:事务必须使数据库从一个一致性状态变换成另外一个一致性状态
隔离性:多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰(得看设置的隔离级别 后边讲到了)
持久性:一个事务一旦提交,则数据将持久化到本地,除非其他事务对其进行修改 比如删除就是一个事务

###事务的创建
隐式事务:事务没有明显的开启和结束的标记 比如insert delete update语句都属于隐式的事务
SHOW VARIABLES LIKE ‘autocommit’;我们会发现事务是自动提交的;也就是说我们执行insert update delete那么就自动执行了
隐式事务就这样!
显示事务:事务具有明显的开启和结束的标记
前提:
第一步:先设置我们的自动提交功能为禁用
set autocommit=0;
start transaction;//可选的 只要执行了上边的set autocommit=0 那么就相当于自动开启了显示事务
第二步:编写事务中的sql语句(insert select update delete)(在没有提交之前都是驻留在了内存当中)
语句1
语句2
语句3
……
第三步:结束事务
commit;//可以提交事务
rollback;//也可以回滚事务

####php当中调用mysql事务的执行案例:
<?php
$dbhost = ‘localhost:3306’; // mysql服务器主机地址
$dbuser = ‘root’; // mysql用户名
$dbpass = ‘123456’; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘连接失败: ‘ . mysqli_error($conn));
}
// 设置编码,防止中文乱码
mysqli_query($conn, “set names utf8”);
mysqli_select_db( $conn, ‘RUNOOB’ );
mysqli_query($conn, “SET AUTOCOMMIT=0”); // 设置为不自动提交,因为MYSQL默认立即执行
mysqli_begin_transaction($conn); // 开始事务定义

if(!mysqli_query($conn, “insert into runoob_transaction_test (id) values(8)”))
{
mysqli_query($conn, “ROLLBACK”); // 判断当执行失败时回滚
}

if(!mysqli_query($conn, “insert into runoob_transaction_test (id) values(9)”))
{
mysqli_query($conn, “ROLLBACK”); // 判断执行失败时回滚
}
mysqli_commit($conn); //执行事务
mysqli_close($conn);
?>

###事务的并发性问题
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:

脏读:对于两个事务T1,T2,,T1读取了已经被T2更新但是还没有被提交的字段之后,若T2回滚,T1读取的内容就是临时并且无效的.
不可重复读:对于两个事务T1,T2,,T1读取了一个字段,然后T2更新了该字段之后,T1再次读取同一个字段值就不一样了;
幻读:对于两个事务T1,T2,,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行之后,如果T1再次读取一个表就多出来几行;

####数据库事务的隔离性
数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响避免各种并发问题;
一个事务与其他事务隔离的程度称为隔离级别,数据库规定了各种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高数据一致性就越好,但并发性越弱;

如何避免事务的并发问题?
通过设置事务的隔离级别
1、READ UNCOMMITTED(三种情况都会出现哦)
2、READ COMMITTED 可以避免脏读
3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读(效果是最好的 也是默认的)
4、SERIALIZABLE可以避免脏读、不可重复读和幻读(这种情况下在这个事务持续期间 禁止其他事务对该表执行插入更新和删除操作 所有并发问题都可以避免 但是性能十分低下 不建议使用)

设置隔离级别:
set session|global transaction isolation level 隔离级别名;
查看隔离级别:
select @@tx_isolation;

但是通常情况下我们只需要保证隔离级别是默认的REPEATABLE READ即可以了!

###事务使用到的关键字
set autocommit=0;
start transaction;
commit;
rollback;

###savepoint 保存点
现在我们重点来讲解一下断点savepoint的使用
案例:
set autocommit = 0;
start transaction;
delete from account where id = 25;
savepoint a;
delete from account where id = 29;
rollback to a;
注意:这样就删除了25这条记录 因为我们设置了保存点savepoint a 那么roleback to a 回滚到了保存点a 那么id = 29的也就被回滚了所以没删除!

###事务当中delete和truncate的区别
delete支持事务
truncate不支持事务

##视图
含义:理解成一张虚拟的表
mysql5.1版本出现的新特性 是通过表动态生成的数据
定义:一种虚拟存在的表 行和列的数据来自定义视图的查询中使用的表,并且是在使用视图是动态生成的 只保存sql逻辑 不保存查询结果;
应用场景:
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂

####视图的创建
语法:
CREATE VIEW 视图名
AS
查询语句;
比如:
create View ac as select * from account; 这样我们就创建了一张视图表将表accout表当中的所有的数据都放到了视图里面去

####视图的使用
我们在使用的时候直接 select * from ac;即可
另外 如果accout表当中的数据发生了变化 那么当我们查找视图的时候相应的数据也会随着发生变化!

####视图的修改(修改的视图的逻辑)

#方式一:
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;

#方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;

###视图的删除
DROP VIEW test_v1,test_v2,test_v3;

###视图结构的查看
DESC test_v7;
或者
SHOW CREATE VIEW test_v7;

###视图的增删改查 很少用
1、查看视图的数据 ★
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name=’Partners’
2、插入视图的数据(插入 往视图当中插入数据 那么原始表对应的记录里面也有了数据!)
INSERT INTO my_v4(last_name,department_id) VALUES(‘虚竹’,90);
3、修改视图的数据 (更新 那么原始表的数据也跟着更新了)
UPDATE my_v4 SET last_name =’梦姑’ WHERE last_name=’虚竹’;
4、删除视图的数据(删除 原始表里面的该条记录也没了)
DELETE FROM my_v4 where id = 3;

#注意:我们发现上边通过视图也可以去修改增加删除原始表里面的数据 这样绝对的不安全!怎么办呢?
首先为视图设置权限 只允许读 不能增删改
第二只要我们的视图逻辑当中包括包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
这些关键字的 那么都无法对视图进行增删改操作

#视图的好处:
1、sql语句提高重用性,效率高
2、和表实现了分离,提高了安全性

#视图和表的比较
是否实际占用物理空间 使用
视图 create view 没有(只是保存了sql逻辑) 增删改查 一般不能增删改
表 create table 占用(保存了数据) 增删改查

#mysql当中的变量
分类:
系统变量:变量是由系统提供 不是用户定义 属于服务器层面

全局变量 show global variables;
(作用域:全局变量可以跨链接起作用 比如autocommit = 0 不过多少个链接只要我们设置了那么都起作用 但是不能胯重启)
会话变量 show session variables;
(作用域:仅仅针对当前会话链接有效)
查看系统变量:
查看满足条件的部分系统变量:show global variables like ‘%char%’; show session variables like ‘%char%’
查看指定的某个系统变量的值:select @@global.系统变量的名称; select @@session.系统变量的名称
设置系统变量:
方式一:set global 系统变量 = 值 或者 set session 系统变量 = 值
方式二:set @@global.系统变量 = 值 或者 set @@session.系统变量 = 值

自定义变量 (变量是用户自定义的 不是系统提供的)
用户变量
(作用域:针对于当前会话链接有效的 应用在我们的任何地方)
①声明并初始化(必须初始化)
set @用户变量名=值 第二种写法 set @用户变量名:=值 第三种写法 select @用户变量名:=值
②赋值(更新用户变量的值)
方式一:set @用户变量名=值 第二种写法 set @用户变量名:=值 第三种写法 select @用户变量名:=值
方式二:select 字段 into 变量名 from 表;(重点 从表里面查询出来设置为用户变量) 比如:select count(*) into @count from employees;
案例:
set @name=’join’; set @name=1; 变量为弱类型
③使用
select @用户变量名;

完整案例(必须三步走 并且声明的时候必须要赋初始值):
声明
set @count = 1
赋值
select count(*) into @count from employees;
使用
select @count;

局部变量
(作用域:作用局仅仅在局部有效 定义它的begin end 块当中有效 出了这个范围就失效了)
①声明 (这个可以只声明不赋值初始化 但必须加上类型)
declare 变量名 类型;
或者
declare 变量名 default 值;(这个默认值必须要保证和前边声明的类型保持一致)
②赋值
方式一:set 局部变量名=值 第二种写法 set 局部变量名:=值 第三种写法 select @用户变量名:=值(这个写法要加上@符号)
方式二:select 字段 into 局部变量名 from 表;(重点 从表里面查询出来设置为用户变量) 比如:select count(*) into count from employees;
③使用
select 局部变量名;

案例:声明两个变量并赋初始值求和并打印

#1 用户变量
set @m=1;
set @n=2;
set @sum=@m+@n;
select @sum;

#2 用局部变量(必须放在begin end 块当中 还没学呢 草)

#===============================================

###系统变量
一、全局变量

作用域:针对于所有会话(连接)有效,但不能跨重启

查看所有全局变量
SHOW GLOBAL VARIABLES;
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE ‘%char%’;
查看指定的系统变量的值
SELECT @@global.autocommit;
为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

二、会话变量

作用域:针对于当前会话(连接)有效

查看所有会话变量
SHOW SESSION VARIABLES;
查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE ‘%char%’;
查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
为某个会话变量赋值
SET @@session.tx_isolation=’read-uncommitted’;
SET SESSION tx_isolation=’read-committed’;

###自定义变量
一、用户变量

声明并初始化:

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
赋值:

方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;

方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用:

select @变量名;

二、局部变量

声明:

declare 变量名 类型 【default 值】;
赋值:

方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;

方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用:

select 变量名

二者的区别:

作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号,不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

##存储过程(存储过程往往是去做增删改 因为查找上边我们讲过太多了 基本都能搞定了)

含义:存储过程和函数类似java当中的方法 一组经过预先编译的sql语句的集合 可以理解成批处理语句
好处:

1、提高了sql语句的重用性,减少了开发程序员的压力
2、提高了效率 如果已经编译过存储过程当中的sql逻辑 那么就不会再编译了 会直接拿过来使用 提高了效率
3、减少了传输次数 也就是说减少了和数据库服务器的链接次数 提高了效率

分类:
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个

###创建存储过程
1.语法:

create procedure 存储过程名(in|out|inout 参数名 参数类型,…)
begin
存储过程体(一组合法有效的sql语句)
end
2.如果begin end之间仅仅只有一句话 begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号:
存储过程的结尾可以使用 DELIMITER 重新设置
语法:
DELIMITER 结束标记
案例:
DELIMITER $

3.
无返回无参
in 该参数可以作为输入 也就是该参数需要调用方传入值
out 该参数可以作为输出 也就是说该参数可以作为返回值
intout 该参数既可以作为输入又可以作为输出 也就是该参数既需要传入值 又可以返回值

4.调用语法:
CALL 存储过程名称(实参列表);//形参 实参类型个数都要一致

###存储过程创建使用案例:
注意存储过程往往更多的用于增删改!当然查也可以!
A:空参列表的存储过程的创建
需求:一次性插入多条记录到表里面
创建:
CREATE PROCEDURE myp4()
BEGIN
INSERT INTO account(username,balance) values(‘wahaha1’,1000),(‘wahaha2’,2000),(‘wahaha3’,3000);
END;
调用:
CALL myp4();

B:创建带in模式参数的存储过程
需求:查询指定女生的男朋友的名称
创建:参数 in表示需要调用方传入值 username 变量名称 varchar(20)参数类型和字符长度限制
因为我们定义的变量username是在存储过程里面 所以属于局部变量 只能在存储过程函数体当中使用
如果遇到变量名称和字段名称相同的情况 那么我们需要在字段名称前边加上表名称来区分
create PROCEDURE myp2(IN username varchar(20))
BEGIN
select bb.name,b.boyName from boys b RIGHT JOIN beauty bb ON bb.boyfriend_id = b.id where bb.name = username;
END;
调用:
CALL myp2(‘柳岩’);

需求:根据用户名称和密码判断登录是成功还是失败
创建:就跟上边讲到的如果遇到变量名称和字段名称相同的情况 那么我们需要在字段名称前边加上表名称来区分
create PROCEDURE ccgc3(IN username varchar(20),IN password varchar(20))
BEGIN
DECLARE num int default 0; //我们设置局部变量
//查询结果直接赋值给局部变量
select count(*) into num from admin where admin.username=username and admin.password = password;
//判断局部变量的值 select 其实就是返回输出值
select if(num>0,’成功’,’失败’);
END;

CALL ccgc3(‘wahaha’,’1sdfs’);

C:创建带out模式的存储过程
需求:根据女神名 返回对应的男神名
创建:
create PROCEDURE wahaha2(IN girlName varchar(20),OUT boysName varchar(20))
BEGIN
//我们使用了out模式带返回值的类型 那么我们需要在查询的时候把结果into到out类型的参数上去 这样我们在调用的时候第二个参数就是获取到的返回值了
select bo.boyName INTO boysName from boys bo RIGHT JOIN beauty g ON g.boyfriend_id = bo.id where g.name = girlName;
END;
使用:
//因为我们使用了out模式的 有返回值 并且我们在存储过程当中将结果into到了返回的参数当中 那么这里我们先定义一个用户变量 用来存放返回值
set @bName:=’’;
//调用的时候第二个参数就放我们定义好的用户变量
CALL wahaha2(‘小昭’,@bName);
//直接查询我们定义的用户变量就能获取存储过程返回的值了
select @bName;

需求:根据女神名 返回对应的男神名称和魅力值(多个返回值的情况下)
创建:
create PROCEDURE wahaha6(IN girlName varchar(20),OUT boysName varchar(20),OUT userCp INT)
BEGIN
//如果是多个返回值的情况下 那么我们需要这么into
select bo.boyName,bo.userCp INTO boysName,userCp from boys bo RIGHT JOIN beauty g ON g.boyfriend_id = bo.id where g.name = girlName;
END;
使用:
//也可以不设置用户变量 而是直接写到call里面去
CALL wahaha6(‘小昭’,@bName,@bUserCp);
select @bName,@bUserCp;

D:创建带inout模式参数的存储过程
需求:传入a和b两个值 最终a和b都翻倍并返回
//创建 inout类型 那么进来必须传值 也要有返回值 不跟out类型一样需要使用into 这里直接使用set即可
create PROCEDURE wocao(INOUT a int,INOUT b int)
BEGIN
set a = 2a;
set b = 2
b;
END;
//使用 因为是inout类型 所以我们必须将有值的参数传递进去才行 所以设置用户变量赋值然后再传值进去
set @a=2;
set @b=4;
CALL wocao(@a,@b);
select @a,@b;

#删除存储过程
drop procedure 存储过程名称
每次只能删除一个存储过程

#存储过程的查看
show create procedure myp2;//查看创建存储过程的语句

###创建函数
函数的含义:其实和存储过程是一样的!

函数和存储过程的区别:
存储过程:可以有0个返回 也可以有多个返回 适合做批量插入 批量更新 操作
函数:有且仅有1个返回 适合做处理数据后返回一个结果 适合查询出结果集然后进行处理返回一个值的场景

语法:
CREATE FUNCTION 函数名(参数名 参数类型,…) RETURNS 返回类型
BEGIN
函数体
END
注意:参数列表包含两部分 参数名 参数类型
不要忘记写返回类型哦
函数体当中肯定会有return 语句!
return 值 放在函数体的最后

###调用函数
SELECT 函数名(实参列表)

###函数使用案例演示:
a.无参数返回
需求:返回公司的员工个数
//创建
create function aa() returns INT
BEGIN
//我们需要创建一个局部变量 用于保存返回值
DECLARE c INT DEFAULT 0;
//还是通过into 将结果放到局部变量里面去
select count(*) into c from boys;
//最后不要忘记return
return c;
END
//使用
select aa();

b.有参数返回
需求:找出指定女神对应的男朋友的姓名
//创建 里面涉及到类型的 都加上限制长度 比如varchar(20)
CREATE FUNCTION abc(uname VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
set @xm=’’; #这个地方我们也可以使用用户变量 当然也可以使用局部变量 都行
select b.boyName into @xm from boys b RIGHT JOIN beauty bb ON bb.boyfriend_id = b.id where bb.name = uname;
return @xm; #return的时候不要忘记带上@
END;
//使用
select abc(‘小昭’);

###查看函数主体
show create function 函数名称

###删除函数
drop function 函数名称

##流程控制结构
分为:
顺序结构
分支结构
循环结构

###分支
一、if函数
语法:if(条件,值1,值2)
特点:可以用在任何位置

二、case语句

语法:

情况一:类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句,需要加分号)
when 值2 then 结果2或语句2(如果是语句,需要加分号)

else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)

情况二:类似于多重if
case
when 条件1 then 结果1或语句1(如果是语句,需要加分号)
when 条件2 then 结果2或语句2(如果是语句,需要加分号)

else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)

特点:
可以用在任何位置

案例
create procedyre test_case(IN score INT)
begin
case
when score>=90 and score<=100 then select ‘A’;
when score>=80 and then select ‘B’;
else select ‘C’;
end case;
end;

三、if elseif语句

语法:

if 情况1 then 语句1;
elseif 情况2 then 语句2;

else 语句n;
end if;

特点:
只能用在begin end中!!!!!!!!!!!!!!!
案例:
create function test_if(score int) returns char
begin
if score>=90 and score<=100 then return ‘A’;
elseif score>=80 then return ‘B’;
elseif score>=60 then return ‘c’;
else return ‘d’;
end if;
end;

三者比较:
应用场合
if函数 简单双分支
case结构 等值判断 的多分支
if结构 区间判断 的多分支

###循环

语法:

【标签:】WHILE 循环条件 DO
循环体
END WHILE 【标签】;

案例
根据次数插入到admin表中多条记录
//创建
CREATE PROCEDURE abg(IN num INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= num DO
INSERT INTO admin (username, PASSWORD) VALUES (CONCAT(‘ali’,i), ‘666’);
SET i = i + 1;
END WHILE;
END;
//调用
CALL abg(10);

特点:

只能放在BEGIN END里面

如果要搭配leave跳转语句,需要使用标签,否则可以不用标签

leave类似于java中的break语句,跳出所在循环!!!

本作品采用《CC 协议》,转载必须注明作者和本文链接

胡军

huxiaobai_001
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!