MySQL 基础+高级篇- 数据库 -sql -尚硅谷（基础篇）

MySQL初级

第一章.为什么要学习数据库

 实现数据持久化

使用完整的管理系统统一管理，易于查询

 

第二章.数据库相关概念

DBMS：Database Management System。数据库时通过DBMS创建和操作的容器

DB：Database，存储数据的仓库。保存了一系列有组织有格式规范的数据

SQL：Structure Query Language。专门与数据库管理系统通信的语言

SQL优点：

 

第三章.数据库存储数据的特点

1.数据放到表里，表放到库里

2.一个数据库里可以有多张表，每个表有自己的名字，名字唯一

3.表具有特性，定义了数据在表中如何存储。表类似类

4.表由一个或多个列组成，叫做字段。列类似属性

5.表中数据按行存储。行类似对象

 

第四章.初识MySQL

MySQL产品介绍

DBMS：分两类，基于共享文件系统的和基于C/S架构的

MySQL基于客户端服务端架构

 

MySQL产品安装

安装好后要配置

 

MySQL产品卸载

控制面板里卸载，安装路径里删除，ProgramData里删除，删除注册表（预防配置影响新安装的软件），删除Application Data里的MySQL

 

配置文件：my.ini

 

MySQL服务的启动和和停止

是个daemon，各个系统里找启动/停止服务的办法

windows：net stop 服务名。star启动

 

MySQL服务的登录和登出

mysql -h 地址 -P 端口号 -u 用户 -p 密码。选型和参数之间可以没有空格

exit退出

 

配置环境变量：

把安装路径bin加入Path环境变量

 

MySQL的常见命令和语法规范

show databases（;或者\g）：显示数据库

use 数据库名；：使用进入数据库

show tables；：显示当前数据库里的表名

show tables from 数据库：显示某个数据库里的表名

select database（）；：显示当前位于哪个数据库

desc 表名；：显示表的schema

 

查看MySQL版本：

select version（）；

mysql --version

mysql -V

 

MySQL语法规范

1.不区分大小写，关键字大写，表名，列名小写

2.每条命令分号结尾

3.每条命令根据需要缩进换行。建议关键字一行

4.注释是#和--空格 来当行注释，/* */多行

 

图形界面客户端：

SQLyog和Navicat

 

第五章.DQL语言的学习

myemployees库的四张表

1.departments：

2.employees：

3.jobs：

4.locations：

 

基础查询

select 查询列表 from 表名

查询列表：表中字段，常量，表达式，函数

查询结果是虚拟表格

 

1.查询表格中的单个字段

select last_name from employees；

2.查询表格中的多个字段

select last_name，salary，email from employees；

3.查询表格中的所有字段

select * from employees；

 

细节补充：

use myemployees；

``着重号代表不转义，关键字也可以在里边使用

 

查询常量：

select 100；结果的字段名是100，即常量

 

查询表达式：

select 100*98；结果的列字段名是“100*98”

 

查询函数：

select database（）；结果列的字段名是database（）

 

结果列起别名：

方式一：

select 100*98 as result from employees；

好处：1.结果清晰2.别名区分重名字段，比如自连接

 

方式二：

select last_name 姓 from employees；省略as

 

如果别名有空格，用着重号包起来

 

去重：

select distinct departmeng_id from employees；

 

+号的作用：

列合并用contat函数而不是+号

+号只能做数值运算符，不能用在非数值字符上，否则字符被转换成0；只有+的操作数有一个是null，结果就是null

 

concat作用：

字符连接用concat

select concat（last_name，first_name） as 姓名 from employees；

 

ifnull：

如果是第一个参数是null，返回第二个参数。可以用来在做concat的时候修饰可能是null值的列

 

条件查询

select 查询列表 from 表名 where 【筛选条件】；

筛选条件分类：

1.按条件表达式筛选：条件运算符，>  <  =  !=  <> >= <=

select * from from employees where salary > 12000;

2.按逻辑表达式筛选：逻辑运算符，&& || ！ and or not,连接条件表达式

select last_name, salary from employees where salary > 1000 && salary <= 20000;

3.模糊查询：like， between and，in，is null

like:不是完全匹配，包含部分字符

select * from employees where last_name like '%a%'；通配符用%

一般和通配符搭配使用。

%：任意多个字符，可以是0个

_：任意单个字符

\为转义

'_$_' escape '$'表明转义字符为💲，也可以是任意字符

 

between and：在某个范围内，闭区间，提高语句简洁度。两边的值不满足交换律

select * from employees where saly between 10000 and 150000

 

in：值在一个散列集合里。提高语句简洁度。集合里的值类型要统一或者兼容。不支持通配符

select * from employees where job_id in ('IT_PROT', 'AD_VP')

 

is （not）null：判断null值。等号和不等号不能判断null值，这个不能判断列值是否是普通类型数值

select last_name, commission_pct from employees where commission_pct is null;

 

安全等于：<=>,可以判断null值，普通类型的值也可以

 

排序查询

order by 排序列表 acc | desc，排序列表可以用逗号分隔

select * from employees order by salary desc

 

常见函数

调用：select 函数名（） from 表

分类：

单行函数：每一行处理，一对一映射

分组函数：多行统计，多对一

 

单行函数分类：

字符函数：

length：字符串字节长度

concat：拼接字符串

upper：换成大写

lower：换成小写

substring，substr：截取字符串，索引从1开始

instr：子串在字符串里的索引

trim：去前后空格，trim("a" from "aaaaBBBBBaaaaBBBBaaa")则结果为 BBBBBaaaaBBBB

lpad：左边填充字符，最后字符长度为第二个参数指定，可能裁剪

rpad：右填充

replace：替换字符，全部替换

 

数学函数：

round：四舍五入；两个参数取小数点位数

ceil：向上取整

floor：向下取整

truncate：截断，取小数点后位数

mod：取余数，和%一样

 

日期函数：

now：当前时间日期

curdate：当前日期

curtime：当前时间

获取指定的部分，比如年月日：year，month，monthname

str_to_date：将字符串转为日期

date_format:将日期转为字符

datediff：算日期差距

 

其他函数：

verson：数据库管理软件版本

database：当前数据库

user：当前用户

 

流程控制函数：

if：参数类似三元运算符

case：

使用1:

case 表达式

when 常量1 then

when 常量2 then

else

end

使用2:多重if

case

when 条件1 then

when 条件2 then

else

end

 

分组函数

sum

avg

max

min

count

参数类型：sum，avg处理数值型数据，max，min，count处理任何类型；忽略null值

 

和distinct搭配：sum（distinct 列名），去重后相加，主要给count用

 

count函数：可用四个参数

参数为*：任何列，只要有非空的，就被统计进去

count（1）：每一行加个值为1的列

*一般效率最高

 

和分组函数一同查询的字段有限制：

要求是groupby的字段。否则普通字段的话返回的是一组值里的一个，没意义

 

分组查询

 1.介绍

select column（不能是随便的一列）, group_func(column)

from table

where condition

group by (分组条件一般是column)

将相同column值的行聚合在一起，可以应用聚合函数，多对一，column相当于一个key带着一个数组

 

2.筛选

分组前筛选：针对每一行筛选，where条件指定

分组后筛选：针对聚合结果筛选，having条件指定

筛选总结：筛选数据源不同，原始表或者分组后结果集。分组函数做条件放having字句里，为了性能尽量用分组前筛选

 

按函数分组：group by后接函数/表达式，这是对每一行算一下添加一个新列（单行函数）

 

按多个字段分组：多个字段可以组成联合主键唯一标识一个特性，可以一起select出更清楚。即是多个列都一样的行才能分成一个组内

 

分组查询顺序：order by后加聚合条件的列名

 

总结：group by支持按照单个字段分组，也支持按照多个字段分组，也支持表达式/函数分组

 

连接查询

多表查询

1.笛卡尔积

from a,b：默认就是笛卡尔乘积，没有加任何连接条件。两个表每一行一一匹配

通过where子句，设置连接条件，筛选里边的行

 

2.连接查询分类

按年代分类：

• sql92标准
• sql99标准

 

按功能分类：

内连接

• 　　等值连接
• 　　非等值连接
• 　　自连接

外连接

• 　　左外连接
• 　　右外连接
• 　　全外连接

交叉连接

 

3.等值连接

where指定两个表的列表达式对比条件，列名有重复的要加表前缀

from子句中用as为表起别名（或者不加as）。起过表别名后，不能再使用原表名

结果是两个表的交集，相应列在两个表里都要有

有筛选的查询：用AND附加在where条件后

 

多表查询就是几个表，找相应列符合条件，条件可以是多个表达式逻辑组合；换句话说就是几个表挑列

n表连接，至少n-1个连接条件

 

4.非等值连接

where子句里用非等于号，因为两个表里的数据不能用等于判断，可能需要是范围值

 

5.自连接

相当于等值连接，但只有一个表，一个表分饰两个角色

 

sql99语法

1.介绍

select 列名

from 表1

【连接类型】join 表2

on 连接条件

 

内连接：inner

左外：left 【outer】

右外：right 【outer】

全外：full 【outer】

 

交叉连接：cross

 

2.等值连接

与之前类似，叫了on， join结构更清楚

 

3.非等值连接

与之前类似，叫了on， join结构更清楚

 

4.自连接

与之前类似，叫了on， join结构更清楚

 

5.左右外连接

查询a表内容不在b表里的行，即一个表中有另一个表中没有的记录

一种主从结构，左外连接以左边的表为主表，右外连接以右边的表为主表

结果集为内连接结果+主表中有从表中没有的

判断在不在一般用主键

 

6.全外连接

MySQL不支持，用联合查询代替

是把交集部分查出来+左表有右表没有+右表有左表没有

 

7.交叉连接

 笛卡尔乘积

select 列名

from 表1

cross join 表2

 

总结：join可以有七种，分别查交集，表1+交集，表2+交集，表1，表2，全部，全部-交集

 

子查询

1.介绍

出现在其他语句中的select语句，叫做子查询或者内查询

外部查询语句是主查询或者内查询

 

分类：

按结果集的行数不同

标量子查询：一行一列

列子查询：一列多行

行子查询：一行多列

表子查询：多行多列

 

2.where

子查询结果当作条件参数

特点：

1.子查询放在小括号内

2.子查询放在条件的右侧

3.标量子查询配合单行操作符使用，>,<等；列子查询配合多行操作符使用，in 、not in、any、some、all

4.子查询优先于主查询先执行，主查询的条件用到了子查询结果

 

非法使用子查询：数目不对的话，会报错；空的子查询结果集结果是空

 

1.标量

子查询结果是一个标量，一行一列

 

2.列

子查询结果是一个列多行

 

3.行

一行多列或者多行多列

where条件里向量化，where （列1，列2） = （子查询（每个列可以是一个函数，得到一行））

 

3.select

仅仅支持标量子查询

子查询结果当作结果集中的一列

可以把当前行相应的列值，送到子查询里当参数，返回的是一个标量，匹配主查询里一行，当作结果集

一般可以用表连接替代

 

4.from

支持表子查询

子查询结果当作中间表

子查询的结果集当成一个表来连接。要用as起个别名方便后面引用

 

5.exists

相关子查询，仅仅支持表子查询

子查询结果当作条件结果

exists（子查询）

一个子查询里有没有值，有返回1，没有返回0

用在where里，先执行主查询，再执行子查询，过滤，判断是否在在行。where的本质是返回的值为1，该行保留

 

分页查询

limit子句

limt offset， size

offset从0开始

offset公式是（page-1）* size

 

union联合查询

并集

将多条查询语句结果，合并为一个结果集（行数相加）

把or这个大的条件，拆成多个，结果集去重

可以实现全连接，但是没办法两个表横着拼接，只能竖着拼

查询多个表，多个表没有关系，但是查询信息一致

要求：

1.两个表列数一致

2.列的顺序要一致，不然歧义

3.结果集去重，UNION ALL不去重

 

第六章.DML语言的学习

插入语句

insert

表明 列名 新值

第一种插入：

insert into 表名（列名1，……） values（值1，……）

插入的值要与列类型兼容

可以为空的列，不写就是插入null；或者写列名，值用null填充；列的顺序可以调换

列和值数量要一致

列名可以省略，默认是所有列，顺序不能变

 

第二种插入：

insert into 表名 set 列名=值，列名=值

 

对比总结：

方式1支持插入多行

方式1支持子查询，把子查询（比如其他表）的结果插入，一个select语句放的都是常量返回的也是一行

 

修改语句

update

 

修改单表：

update 表

set 列=新值，列=新值

where

 

修改多表：

级联更新

update 表1

连接表2

set 列=值（针对连接后表的列）

where

 

删除语句

delete

 

第一种删除：

delete from 表名 where

 

第二章删除：

truncate table 表名：删除这个表里的数据

 

truncate删除后，自增长列重新开始，delete不会

truncate没有返回值；delete返回影响几行

truncate不能回滚，delete可以回滚

 

第七章.DLL语言的学习

创建/修改/删除/查询库和表

create

alter

drop

show

库和表的管理

库：

create database 数据库名

create database if not exists 数据库名，数据库存在也不会报错

更改库的字符集：alter database  库名 character set  【】

drop database ，删库

 

表：

创建：create table 表名（列名 类型 （长度） 约束，）

 

修改：修改列名，类型，约束，添加列，删除列，修改表名

修改列名：alter table 表名 change column 旧列名 新列名 类型

修改类型：alter table 表名 modify column 列名 类型

添加列：alter table 表名 add column 新列名 类型

删除列：alter table 表名 drop column 列名

重命名表：alter table 表名 rename to 新表名 

 

删除：

drop table 表

 

复制：

1.仅仅复制表结构

create table 表名 like 现有表

 

2.复制内容

create table 表名 

select * from 表，用子查询返回一个临时表

 

只复制部分列名：子查询where都是假

 

常见数据类型介绍

•  数值型
• 　　整型
• 　　小数
• 　　　　定点数
• 　　　　浮点数
• 字符型
• 　　较短的文本
• 　　　　char
• 　　　　varchar
• 　　较长的文本
• 　　　　text
• 　　　　blob
• 日期型

 

整型：

分类：

Tinyint：1

Smallint：2

Mediumint：3

Int，integer：4

BigInt：8

 

特点：

设置无符号，有符号：

int类型后边跟unsigned，即为无符号，否则默认为有符号

超出了范围，会有警告报错，数值为0或者插不进去

Int （7）设置显示结果宽度，zerofill可以用0填充显示位并且int默认是无符号

 

小数：

浮点：

float：4

double：8

定点：

dec(m,d):m+2

decimal(m,d):m+2

特点：m代表整数部位+小数部位，d代表小数部位。如果超过范围，插入临界值

默认decimal是10，0；float和double没有限制

定点型精度较高，如货币运算

所选择的类型越简单越好

 

字符型：

char（m）：最长m个字符，空间固定m个字符，m默认为1

varchar（m）：最长m个字符，空间根据实际字符数来取，m不可以省略

bit（m）：

binary

varbinary

enum：最多两个字节，必须是列表中的一个值。enum（“a”， “b”）。不区分大小写

set：和枚举类似，可以一次插入列表中的多个。 values（‘a，b，c’）

 

日期型：

date：4

datetime：8

timestamp：4，范围比datetime小，和实际时区相关，时间会跟着时区变

time：3

year：1

 

常见约束

限制数据，保证数据可靠准确性

六大约束：

not null：非空约束

default：默认约束，有默认值

primary key：主键约束，具有唯一性非空

unique：唯一约束，唯一性可为空

check：检查约束，满足一定条件才可以，MySQL不支持

foreign key（references）：外键约束。从表添加该约束

 

show index from 表名：显示索引。主键，外键，唯一键自动生成索引

 

创建表和修改表时添加约束

 

约束分类：

列级

表级

 

外键约束对列级无效

非空和默认约束对表级约束无效

 

添加列级约束：

约束放在类型后面

 

添加表级约束：

列最后加约束，constraint 约束名 约束

 

主键和唯一区别：

唯一只可以插一个null

两个列可以组合成主键（定义的时候一起放括号里），唯一键，联合主键

 

外键特点：

在从表里设置外键关系

主表从表里要类型兼容，名称无所谓

外键在主表里要是key（主键或者唯一键）

插入数据先插入主表，删除数据时先删除从表

不同约束可以用空格间隔约束同一个列

 

修改表时添加约束：

修改表时（modify），直接在列后边加约束

表级约束：用Add加约束

 

修改表时删除约束：

modify的约束用null替代或者不写

表级约束：drop 约束类型 约束名

 

主键起约束名字也没有效果

 

外键级联删除：

最后加on delete cascade

 

外键级联置空：

on delete set null

 

标识列： 

限制某个字段，又叫自增长列

id Int Primary Key auto_increment

 

变量auto_increment_increment auto_increment_offset定义步长与起始值，MySQL里不能设置起始值

起始值可以用第一条数据插入值来设定

 

标识列要和key搭配

一个表中只能有至多一个标识列

标识列只能是数值型

 

修改表时也可以设置标识列

 

第八章.TCL语言的学习

事物和事物处理

介绍：

事物：一组sql语句成为一个执行单元，要不全部执行，要不都不执行。一部失败则回滚

show engines显示存储引擎

acid：

1.原子性：事物不可分割

2.一致性：事物导致数据从一个一致性状态转换为另一个一致性状态

3.隔离性：事物并发不能互相干扰，需要隔离级别来控制

4.持久性：事物一旦提交，对数据改变是永久的

 

事物创建：

1.隐式事物：自动开启，关闭，比如delete/update/create，变量autocommit可以看

2.显示事物：先关闭自动提交，具有明显开始结束标志

set autocommit = 0;

start transaction;

 

commit or rollback,autommit自动开启

 

事物并发：

事物访问相同数据，竞争访问，在事物过程中会有以下问题：

1.脏读：t1读取了t2更新但是回滚的数据

2.不可重复读：t1读了一次，t2更新了一次，t1再读不一样

3.幻读：t1读，t2插入，t1再读,数据数量变了

 

隔离级别是隔离程度，隔离级别越高，并发性越差数据一致性越好

 

MySQL提供四种隔离级别：

1.read uncommitted：脏读，不可重复读，幻读都有

2.read commited：提交后再读。不可重复读，幻读都有。

3.repeatable read：事物里已有数据不能更改。幻读

4.serializable：没有问题

 

MySQL默认隔离级别是repeatable read

 

Oracle只有两个，read commited和serializable

 

查看隔离级别：

变量@@tx_isolation

设置隔离级别：set session transaction isolation level read uncommitted

设置全局隔离级别：set global transaction isolation level read uncommitted

 

回滚点：

savepoint 节点名；

rollback to 节点名

 

delete和truncate在事物中的区别：

truncate不能回滚

 

第九章.视图的讲解

介绍：

虚拟表

临时性，通过表动态生成，查询过一次的缓存。视图是sql语句，而不是查询结果

适合多次使用，sql比较复杂的情况

视图一般不带where之类的语句

重用sql，简化调用的sql，保护安全

 

创建：

create view 视图名

as

查询语句；

 

修改：

create or replace view 视图名

as

查询语句；

 

alter view 视图名

as

查询语句；

 

删除：

drop view 视图名1，视图名2

 

查看：

desc view1

show create view view1（显示创建视图过程）

 

视图里实现插入：

insert into view1 values（）

更改和删除亦可，但是受到限制

具有以下元素的视图不能被更新

• 分组函数，distinct，group by，having，union，union all
• 常量视图
• select包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

不能有中间表

 

视图和表的区别

create view

create table

表占用物理空间

 

第十章.变量 

分类：

1.系统变量

变量由系统提供的

 

查看所有变量

show global | session variables

 

查看满足条件的系统变量

show global | session variables like ‘%变量名%’

 

查看指定变量的值

select @@global | session.系统变量名

 

为某个系统变量赋值

set global | session 系统变量名 = 值

set @@global | session.系统变量名 = 值

 

什么也不写默认会话级别

　　1.全局变量

重启后全局变量修改无效，要改配置文件才行

　　2.会话变量

2.自定义变量

使用步骤：

声明

赋值

使用

　　1.用户变量

会话期有效

set @用户变量名=值

set @用户变量名：=值

select @用户变量名：=值

 

select 字段名 into 变量名 from 表

　　2.局部变量

作用域在begin end块中，且声明为第一句

声明：

declare 变量名 类型 default 默认值

 

赋值：

set 用户变量名=值

set 用户变量名：=值

select @用户变量名：=值

 

select 字段名 into 变量名 from 表

 

使用：

select 局部变量名

 

第十一章.存储过程和函数

提供代码重用性

简化操作

 

存储过程

一组预先编译好的SQL语句集合，类似批处理语句

可以有预编译，多条语句只需连接一次数据库

 

语法：

创建：

create procedure 存储过程名（参数列表）

begin

方法体

end

参数列表包含三部分：

参数模式 参数名 参数类型

 

参数模式：in out inout

 

存储过程只有一句话，begin end可以省略

delimiter 结束标志，重新设置存储过程结尾，默认是分号，但是在客户端不好用

 

使用：

call 存储过程名（实参列表）

 

空参列表：

不带参数，直接调用

 

in模式：

参数不带返回值

 

out模式：

保存返回值

 

inout模式：

参数既有输入，也保存返回值

 

存储过程删除：

drop procedure 存储过程名字

一次只能删除一个

 

存储过程查看：

show create procedure 存储过程名

 

函数

和存储过程区别：

函数有且只能有一个返回（适合处理数据）；存储过程可以有0个或多个 （适合增删改）

 

创建函数：

create function 函数名（参数列表） returns 类型

begin

　　函数体

end

注意事项：

参数列表：参数名，参数类型

函数体：要有return语句

函数体只有一句话，省略begin end

 

调用函数：select 函数名（参数列表）

 

删除函数：

drop function 函数名

 

查看函数：

show create function 函数名

 

第十二章.流程控制结构

介绍

顺序结构

分级结构

循环结构

 

case：

类似switch，等值判断

case 值

when 值1 then；

when 值2 then；

else ；

end case；

 

类似if，区间判断

case

when 条件1 then；

when 条件2 then；

else；

end case；

 

既可以作为表达式，也可以做语句

 

if：

if 条件1 then ；

elseif 条件2 then；

else ；

end if；

 

循环：

分类：

while

loop

repeat

 

控制：

iterate 标签名

leave 标签名

 

标签： while 循环条件 do

循环体

end while 标签；

 

标签： loop

循环体

end loop 标签；

模拟死循环

 

标签：repeat

循环体

until 结束循环条件

end repeat 标签；

至少执行一次

 

第十三章 触发器

为某张表绑定好代码，当表中内容改变时（增删改），触发代码执行

触发器：事件类型，触发时间，触发对象

触发类型：增删改

触发时间：前后

触发对象：行记录

 

MySQL高级结构没有大括号，都是用字符符号代替

 

创建

create trigger 触发器名 触发时间 事件类型 on 表名 for each row

begin

 

end；

 

查看

show triggers 【like ‘pattern’】

 

show create trigger 触发器名字

 

\G:结果颠倒90度

 

触发器保存到information_schema.triggers表中

 

使用

某种情况发生，自动触发

 

删除 

drop tirgger 触发器名字

 

记录

每个操作要执行，系统将操作的数据新旧状态记录下来

访问使用old.字段名和new.字段名

 

第十四章 游标

 

第十五章 事件

定时器，时间到了自动执行