MySQL学习笔记——基础与进阶篇

 

 

目录

  一、###MySQL登录和退出

 

  二、###MySQL常用命令

 

  三、###MySQL语法规范

 

  四、###基础查询

 

  五、###条件查询

 

  六、###排序查询

 

  七、###常见函数的学习

 

  八、###分组查询

 

  九、###连接查询

 

  十、###子查询

 

  十一、###分页查询

 

  十二、###联合查询

 

  十三、###DML语言

 

  十四、###DDL语言

 

  十五、###常见的数据类型

 

  十六、###常见的约束

 

  十七、###标识列

 

  十八、###TCL

 

  十九、###视图

 

  二十、###变量

 

  二十一、###存储过程和函数

 

 

###1.MySQL登录和退出
登录:
mysql (-h主机名 -p端口号)-u用户名 -p密码
退出:
exit或者ctrl+c



###2.MySQL基础命令
1.查看所有数据库
show databases;
2.打开库
use 库名;
3.查看其他库中的表
show tables from 库名;
4.创建表
creat table 表名(

列名 列类型,
列名 列类型,
。。。
);
5.查看表结构
desc 表名;



###3.MySQL语法规范

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




###4.基础查询

#语法:
select 查询的东西
from 表名;

#特点:
1.查询的东西可以是:字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格。

#1.查询表中的单个字段
SELECT NAME FROM city;

#2.查询表中的多个字段
SELECT NAME,id FROM city;

#3.查询表中所有的字段
SELECT * FROM city;

#4.查询常量值
SELECT 100;
SELECT 'beijing';

#5.查询表达式
SELECT 100*98;

#6.查询函数
SELECT version(); //得到的是查询函数的返回值

#7.为字段起别名
①便于理解
②如果查询的字段有重名情况,使用别名可以区分开

#方式一:使用As
SELECT 100*98 As 结果;
SELECT 'beijing' AS 首都; 

#方式二:使用空格
SELECT 'beijing' 首都; 

#案例:如果别名中存在关键字或特殊符号,加双引号
(单引号也可,但建议双引号)

#8.去重
SELECT DISTINCT 'beijing' from city;

#9.+的作用
运算符: 
例如:select 100+10;//两个操作数都为数值型,则作加法运算
select '123'+90;//一个操作数是字符,另个一是数值,会
尝试将字符型转换为数值型 结果:213;
SELECT DISTINCT 'beijing' + 10;//若不能转换,则将字符型
视作0 结果:10;
SELECT DISTINCT 'beijing' + null;//若有一方是null,则
结果为null




###5.条件查询

#语法:
select 
查询列表
from
表名
where
筛选条件;
#分类
一、按条件表达式筛选
条件运算符> < = != <= >= 建议不等于用<>
案例:筛选id>50的城市
SELECT 
*
FROM 
city
WHERE
id>50


二、 按逻辑运算符筛选
逻辑运算符:
与:$$ 或:|| 非:!
建议使用: and or not
案例:查询id 50到100的城市:
SELECT 
*
FROM 
city
WHERE
id>50 AND id<100


三、模糊查询
like
between and
in
is null | is not null

#1.like
特点:
①一般和通配符搭配使用
通配符:
% 任意多个字符
_ 任意单个字符
如果查询的中含有通配符,应转译 例如 \_


案例:查找ch开头的国家
SELECT 
*
FROM 
country

WHERE 
NAME LIKE 'ch%'



#2.between and

特点:
①包含临界值
②调换顺序不报错,但是错误

案例:查找id在100到120的城市
SELECT 
*
FROM 
city

WHERE 
id BETWEEN 100 AND 120


#3.in
特点:
①in列表的值必须一致或兼容
②不能使用通配符

案例:查询国家代码是CHN和NLD的城市名和ID
SELECT 
id,
NAME
FROM 
city
WHERE 
countrycode IN ('CHN','NLD')


#4.is null
id = null 不成立 
id isnull





###6.排序查询
语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点:
①asc是升序 desc是降序,如果不写,默认升序
②支持单个字段,也支持多个字段、表达式、函数、别名
③order by子句一般放在查询语句的最后,limit子句除外

案例1:查询城市名字和id,按id降序
SELECT 
id AS 序号,NAME AS 名字
FROM 
city
ORDER BY 
id DESC
案例2:按照城市名的字节长度排序【按函数排序】
SELECT 
*,LENGTH(NAME)
FROM 
city
ORDER BY LENGTH(NAME) DESC

案例3:按别名排序

案例4:先按照名字长度排序,再按id排序【按多个字段排序】
SELECT 
*,LENGTH(NAME)
FROM 
city
ORDER BY LENGTH(NAME) DESC,id ASC



###7.常见函数的学习

概念:类似java的方法

调用:select 函数名(参数列表) 【from 表】

分类:
1.单行函数
如 concat、length、ifnull等
2.分组函数
功能:做统计使用,又称作统计函数、聚合函数、组函数


# 单行函数

#一.字符函数

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

#2.concat 拼接字符串

#3.upper、lower 变大写,变小写

#4.substr 、substring 截取字符串
注意:索引从1开始
#一个参数:截取从指定索引到结尾
SELECT 
SUBSTR('李莫愁爱上了陆湛远',7)
输出陆湛远
#两个参数:截取从指定索引指定长度
SELECT 
SUBSTR('李莫愁爱上了陆湛远',1,3)
输出李莫愁

#5.instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠')
输出7

#6.trim
SELECT TRIM(' 周福利 ')
输出:周福利

SELECT TRIM('a' FROM 'aaaaa周aaaa福aaaaa利aaaaa')
输出:周aaaa福aaaaa利

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('周福里',10,'*')
输出:*******周福里
#8.rpad 用指定的字符实现右填充指定长度

#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若张无忌爱上了周芷若周芷若','周芷若','赵敏')
输出:赵敏赵敏赵敏张无忌爱上了赵敏赵敏


#二、数学函数

#1.round 四舍五入
先转化为绝对值,再四舍五入
SELECT ROUND(-1.51)
SELECT ROUND(-1.515,2)小数点后保存两位

#2.ceil 向上取整
返回>=该参数的最小整数

#3.floor 向下取整
返回<=该参数的最大整数

#4.truncate 截断
SELECT TRUNCATE(1.6999,1)
输出:1.6

#5.mod 取余
mod(a,b) : a-a/b*b;


#三、日期函数

#1.now 返回当前系统日期+时间

#2.curdate 返回当前系统日期,不包含时间

#3.curtime 返回当前时间,不含日期

#4.可以获取指定部分,年、月、日、小时、分钟、秒
select year(now()) 年;
select year('1998-1-1') 年;
输出1998

select month(now());
9
select monthname(now());
December

#5.str_to_date:将字符转换为指定类型的日期
select str_to_date('4-3 1992','%c-%d %Y')
输出:1998-03-02
%Y 四位年份的年
%y 2位年份的年
%m 月份(01,02,...,11,12)
%c 月份(1,2,3.。。。,11,12)
%d 日(01,02.。。。)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟(00,01,02,....,59)
%s 秒(00,01,02,....,59)

#6.date_format将时间类型转化为字符串
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
输出:2019年12月06日

#7.datediff( , )
查找两个日期相差的时间


#四、其他函数
version()查看版本
database()查看当前数据库
user()查看当前用户


#五、流程控制函数
#1.if函数: if else的效果
SELECT IF(10>5,'大','小')
输出:大

#2.case函数的使用一: switch case的效果
语法:
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
....
else 要显示的值n或语句n;
end

案例:
SELECT ID 序号, NAME 名字,
CASE id
WHEN 1 THEN 'test'
ELSE NAME
END AS 新姓名
FROM city

#3.case 函数的使用二: 类似于多重if
语法:
case 
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
.。。
else 要显示的值n或语句n
end

案例:
SELECT ID 序号, NAME 名字,
CASE 
WHEN id<=10 THEN 'A'
WHEN id>10 AND id<=20 THEN 'B'
ELSE 'C'
END AS 新姓名
FROM city


#二、分组函数
/*
功能:用作统计使用,又称聚合函数或统计函数或组函数

分类:sum求和、avg平均值、max最大值、min最小值、count计算个数
*/

#1、简单的使用
SELECT SUM(id) FROM city
SELECT COUNT(id) FROM city
.....

#2.参数支持哪些类型
sum和avg只支持数值类型 不支持字符型 不支持日期类型

max和min值支持字符型、数字型、日期型

count任何类型都支持 

#3.是否忽略null值
以上分组函数都忽略null值

#4.可以和distinct搭配

#5.count函数的详细介绍
SELECT COUNT(1) FROM city//显示所有字段
SELECT COUNT(*) FROM city

#6. 和分组函数一同查询的字段有限制
和分组函数一同查询的字段要求是group by后的字段

 

 

 

###8.分组查询
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组列表
【order by 子句】

特点:
①分组查询中的筛选条件可以分为分组前查询和分组后查询
②分组查询支持单个字段分组,也支持多个字段分组(中间用逗号隔开,顺序可变)
③也可以添加排序(放在最后)

案例1:查询每个国家的最大城市id
SELECT MAX(id) 最大id,`CountryCode`
FROM city
GROUP BY countrycode
ORDER BY 最大id

案例2:查询每个国家的城市数
SELECT COUNT(*),`CountryCode`
FROM city
GROUP BY countrycode

案例3:添加筛选条件
SELECT COUNT(*),`CountryCode`
FROM city
WHERE `CountryCode` ='chn'
GROUP BY countrycode

#添加复杂的筛选条件
案例:查询每个城市数大于50的国家
SELECT COUNT(*) 城市数,`CountryCode`
FROM city
GROUP BY countrycode
HAVING 城市数>50
由于where只能跟在from后面。所以用了新的having




###9.连接查询:
含义:又称多表查询,查询的字段来自不同表时使用

分类:
按年代分类:
sql192标准 仅仅支持内连接:
sql199标准【推荐】支持内+外(左外+右外)+交叉连接

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

#一、sql92标准
#1.等值连接
#1.案例:查询城市名对应的国家名
SELECT city.`Name`,country.`Name`
FROM city,country
WHERE city.`CountryCode`=country.`Code`

#2.为表起别名
#与给字段起别名一至
#缩短表名长度,提高效率
#起完别名后则查询字段不能使用原来的表名限定

#3.两个表的顺序可以调换

#4.可以加筛选
#案例:
SELECT a.`Name`,b.`Name`
FROM city a,country b

WHERE a.`CountryCode`=b.`Code`
AND a.`ID`>10

#5.可以分组

#6.可以排序

#7.可以实现三表连接

#2.非等值连接

#3.自连接
使用别名进行区分

#二、sql99语法
/*
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表1 别名
on 连接条件
【where 筛选条件】
【group by】
【order】
*/

#连接类型关键字:
内连接:inner
外连接:
左外 left 【outer】
右外 right 【outer】
全外 full 【outer】
交叉连接:cross

#(一)内连接
select 查询列表
from 表1 别名
innet join 表2 别名
on 连接条件

#案例:
SELECT DISTINCT `CountryCode`,b.`Name`
FROM `city` a
INNER JOIN `country` b
ON a.`CountryCode`=b.`Code`


#(二)外连接
/*
应用场景:用于查询一个表中有,另一个表中没有的记录

特点:
1.外连接查询结果为主表中的所有记录
如果从表中没有和他匹配的值,则显示null
如果又和他匹配的值,则显示匹配的值
外连接查询的结果等于=内连接结果加主表中没有的记录
2.左外连接,left join 左边是主表
右外连接,right join 右边是主表
3.左外和右外互换表的位置也可以实现相同的效果

*/

#案例:
select b.name,bo.*
frome beauty b
left outer join boys bo
on b.id = bo.id

select b.name ,bo.*
frome boys bo
right outer join beauty b
on b.id = bo.id

#全外(msql不支持!)
select b.*,bo.*
from beauty b
full outer join boys b
on b.id = bo.id


#交叉连接
案例:
select b.*,bo.*
from beauty b
cross join boys bo
on b.id = bo.id 
产生笛卡尔积


#sql92和sql99
功能:sql99功能较多
可读性:sql99可读性更高



###10.子查询
/*
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,成为主查询或外查询


分类:
按子查询出现的位置:
select后面
仅仅标量子查询
from后面
支持表子查询
where或having后面?
标量子查询(单行)
列子查询(多行)

行子查询
exists后面(相关子查询)

表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)


*/

#(一)where或having后面的子查询
1.标量子查询(单行子查询)
2.列子查询(多行子查询)

3.行子查询(多行多列)

#一、标量子查询

#特点:
①子查询都要放在小括号内
②子查询一般放在条件的右侧
③标量子查询一般搭配着单行操作符使用
< > >= <= 
列子查询一般搭配多行操作符使用
in、any/some、all
④子查询的执行要优先于主查询

#非法使用标量子查询


#案例:查询谁的工资比Abel高?
#①查询Abel的工资
select salary
from employee
where last_name = 'Able'

#②查询员工的信息,满足salary>①的结果
select *
from employee 
where salary > (
select salary
from employee
where last_name = 'Able'



#(二)列子查询(多行子查询)
关键字: IN any/some all
#案例:
select *
from employee
where asalry<all(




#(三)行子查询(结果集一行多列或多行多列)
不常用


#二、select后面
/*
#里面仅支持标量子查询
*/


#三、将子查询放在from后面


#四、exists后面(相关子查询)

/*
语法:
exists(完整的查询语句)
结果:
0或1
*/



###11.分页查询
/*

应用场景:
要显示的数据一页显示不全,需要分页提交sql请求

语法:

limit offset,size;

offset:要显示条目的起始索引(索引从0开始)
size:要显示的条目个数

*/

#案例:
查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;

#特点:
①limit语句放在查询语句的最后
②公式
显示的页数是page,每页条目数是size
select 查询列表
from 表
limit (page -1)*size,size;


#应用场景:
要查询的结果来自于多个表,多个表没有直接关系,但查询的信息相同


#注意事项:
①要求多条查询语句的查询列数是一致的
②查询的每列类型和顺序是一致的
③union关键字默认是去重的,如果使用union all会保留所有项



###12.联合查询
/*
union 将多条查询语句的结果合并成一个结果

语法:
查询语句1
union
查询语句2
union
...
*/

#案例1:查询部门编号>90或邮箱中包含a的员工信息
做法1:
select *
from employees
where email like '%a%' or department_id>90

做法2:
select *
from employees
where email like '%a%'
union
select *
from employees 
where department_id >90

#案例2:查询中国用户男士的信息和外国用户男士的信息



###13.DML语言
/*
数据操作语言:
插入:insert
修改:update
删除:delete
*/ 

#一、插入语言:

#方式一:
/*
#语法:
insert into 表名(列名,....) values(值1,...) 

#注意事项:
①插入值的类型要与列的类型一致
②不可以为null的列必须插入值,可以为null的列,写null或直接不写列
③列的顺序可以颠倒
④列和值的个数必须一致
⑤可以省略列名,默认是所有列,而且列的顺序和表中的顺序一致

*/

#方式二:
/*
语法:
insert into 表名
set 列名=值,列名=值,....

*/

#两种方式pk 
①方式1支持多行输入,方式二不支持
②方式1支持子查询,方式二不支持



#二、修改语句

/*

1.修改单表中的记录:
语法:
update 表名
set 列= 新值,...
where 筛选条件;
案例:
update beauty 
set sex = 'a'
where name = '苏德间'


2.修改多表中的记录
语法:
update 表1 别名,
inner join 表2 别名
on 连接条件
set 列= 值...
where 筛选条件

#案例1:修改张无忌的女朋友的手机号为112
UPDATE beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
SET b.`phone` = '112'
WHERE bo.`boyName` = '张无忌'

#案例2:修改没有男朋友的boyfriendid为5
UPDATE beauty b 
LEFT OUTER JOIN boys bo 
ON b.`boyfriend_id` = bo.`id` 
SET b.`boyfriend_id` = 5 
WHERE bo.`id` IS NULL; 
*/


#三、删除语句

/*

#方式一:delete
语法:
delete from 表名 where 筛选条件

多表删除
#案例一:删除手机号0结尾的
delete from beauty where phone like '%0'



#方式二:truncate
语法:truncate table 表名


#两种方式pk
①delete可以添加where条件,另一个不能
②truncate删除效率高一丢丢
③如果使用delete删除所有后,再插入数据,自增长列从断点开始
truncate删除后自增长列从1开始
④truncate删除没有返回值,delete删除有返回值
⑤truncate删除不能回滚,delete可以回滚


*/




###14.DDL语言
/*
数据定义语言

库和表的管理

#一、库的管理
创建、修改、删除
#二、表的管理
创建、修改、删除

创建:create
修改:alter
删除:drop


*/

#一、库的管理

#1.库的创建

#语法:
create datebase 库名;

#案例:
CREATE DATABASE books

#2.库的修改(基本不修改,会导致数据丢失,不安全)

#更改库的字符集
alter database books character set gbk

#3.库的删除

drop database if exists books

#二、表的管理
/*
alter table 表名 add|drop|modify|change column 

*/

#1.表的创建
create table 表名(
列名 列的类型【(长度) 约束】,
....
)

#2.表的修改
#①修改列名
alter table book change column publishdate pubDate datetime

#②修改列的类型或约束
alter table book modify column pubdate timestamp

#③添加新列
alter table author add column annual double

#④删除列
alter table author drop column annual

#⑤修改表名
alter table author rename to book_author


#3.表的删除
drop table book_author;

#通用的写法
drop database if exists 旧库名;
create database 新库名;

drop table if exists 旧表名;
create table 新表名();

#4.表的复制
#1.仅仅赋值表的结构
CREATE TABLE copy LIKE book_author`copy`

#2.复制表的结构+数据(或部分数据)
CREATE TABLE copy2 
SELECT * FROM book_author
【where】

只复制某些字段
CREATE TABLE copy3
SELECT id,au_name
FROM book_author

 

 


###15.常见的数据类型

/*
数值型:
整形:
小数:
定点数
浮点数

字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)

日期型:

*/


#一、整形
/*
分类:
tinyint、smallint mediumint int/integer、bigint

特点:
①如果不设置无符号,则默认有符号
②超出范围默认插入临界值,且会报异常
③如果不设置长度,会有默认长度
长度代表了显示的最大宽度,如果不够会用0填充,需搭配zerofill使用


*/
#1.如何设置有符号和无符号
unsigned 约束关键字


#二、小数
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)

特点:
①M:整数部位+小数部位
D:小数部位
如果超过范围,则插入临界值
②M和D可以省略
如果是decimal,则M默认是10,D默认是0
如果是float和double则会根据插入数值的精度确认精度
③定点型的精确度较高,如果对插入数值的精度要求较高可以使用,如货币

#原则:
所选择的类型越简单越好,能保存数值的类型越小越好


#三、字符型
/*
较短的文本:
char
varchar

较长的文本:
text
blob

*/
特点:

#char(M)
#varchar(M)
char和varchar区别:carchar可变,char不可变
varchar省空间 但是char效率较高
例如性别,可以用char
char的m可以省略,默认1 ,varchar不能省略



#enum类型: 枚举类型
案例:
create table season(
s1 ENUM('春','夏','秋','冬')
)
insert into season values ('春')

#set类型:
create table tab_set(
s1 set('a','b','c','d')
)
insert into tab_set values ('a')
insert into tab_set values ('a,b')

#binary和varbinary用于保存较短的二进制

#四、日期型
分类:
date 只保存日期
time 只保存时间
year 只保存年

datetime 日期+时间 受时区英雄
timestamp 日期+时间 不受时区影响




###16.常见的约束
/*

含义:一种限制,用于限制表中的数据为了保证表中数据的准确性和可靠性

分类: 六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认的值
比如性别
PRIMATY KEY:主键,用于保证该字段的值具备唯一性并且非空
比如学号、员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如:座位号
CHECK:检查约束【mysql不支持】
比如:年龄、性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值
比如:学生表的专业编号,员工表的部门编号


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


约束的添加分类:
列级约束
六大约束语法上都支持,但外键约束没有效果

表级约束
除了非空、默认,其他的都支持



*/

#一、创建表时添加约束
#1.添加列级约束
语法:
直接在字段名和类型名后面追加约束

CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuname VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender = '女'),#检查(无效)
seat INT UNIQUE , #唯一
age INT DEFAULT 18 #默认

)

#2.添加表级约束
语法:
在各个字段的最下面
【CONSTRAINT 约束名】 约束类型(字段名)
取名可以省略(中括号内的)

CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20), 
gender CHAR(1) ,
seat INT ,
age INT ,
magorid INT,

CONSTRAINT pk PRIMARY KEY(ID),
CONSTRAINT ck UNIQUE(seat),
CONSTRAINT fk_s_m FOREIGN KEY(magorid) REFERENCES magor(`m_id`)
)


#通用写法:
外键约束用表级,其他都用列级约束


#主键和唯一的对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ X 至多有一个 √(组合主键,不推荐)
唯一 √ √ 可以有多个 √(不推荐)

#外键:
1.要求在从表设置外键的关系
2.从表的外键列的类型和主表的关联列的类型一致
3.主表的关联列必须是一个key(一般是主键或唯一键)
4.插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表


#二、修改表时添加约束
/*
1.添加列级约束:
alter table 表名 modify column 字段名 字段类型 新约束

2.添加表级约束:
alter table 表名 add 【constraint 约束名】 约束类型(字段名)


*/

#1.添加非空、默认约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOTNULL

#2.添加主键、唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id)

#3.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_s_m FOREIGN KEY(majorid) REFRENCES major(id)

#三、修改表时删除约束
基本与添加一致
#删除唯一
alter table s drop index 约束名
#删除外键
alter table s drop foreign key 约束名




###17.标识列
/*

又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值


特点:
①标识列不一定和主键搭配,但一定要和key搭配
②一个表中最多有一个标识列
③标识列的类型只能是数值型,一般是int
④标识列可以通过
set auto_increment_increment= 3 
设置步长
可以通过设置第一个值的来决定开始数值


*/

#一、创建表时设置标识列
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)

)

#二、修改表时设置标识列
alter table s modify colunm id primary key auto_increment


###18.TCL
/*

Transaction Control Language 事务控制语言

sql支持insert,update,delete

事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行

案例: 实时转账
张三非:1000
郭襄: 1000
update 表 set 张三非的余额= 500 whrer name = ‘张三非’
update 表 set 郭襄的余额 = 1500 where name = ‘郭襄’


#事务所具备的属性/特点:(ACID)
1.原子性(atomiciy)
事务是一个不可分割的工作单位,要么都发生,要么都不发生
2.一致性(consistency)
事务必须使数据库从一个一致性状态变换到另一个一致性的状态
3.隔离性(isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务的操作及
使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰
4.持久性(durability)
指一个事务一旦被提交,他对数据库中数据的改变是永久性的



#事务的创建

#隐式事务:事务没有明显的开启和结束的标记
比如:insert update delete

#显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能禁用
set autocommit = 0;

步骤1:开启事务
set autocommit = 0;
start transaction; 
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3:结束事务
commit;提交事务
rollback;回滚事务


#并发事务
1.事务的并发问题是如何发生的?
多个事务同时操作同一个数据库的相同数据时
2.并发的问题有那些?
脏读:一个事务读取了其他事务还没提交的数据,读到的是其他事务‘更新的事务’
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务没有提交的事务,读到了其他事务‘插入的事务’
3.如何解决并发问题?
设置隔离级别

#事务的隔离级别:
              脏读    幻读   不可重复度
read uncommitted(读未提交): √      √       √
read committed(读已提交):   x      √      √
repeatable read(可重复读):   x     x      √
serializable(串行化):     x      x       x
mysql中默认第三个隔离级别 repeatable read
orical中默认第二个隔离级别 read commited

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


savepoint 节点名; 设置保存点
配合rollback to 节点名; 使用。

*/








###19.视图
/*

含义:虚拟表,他的数据来自表,通过执行时动态生成

意义:①重用sql语句
②简化复杂的sql语句
③保护数据,提高安全性

#视图与表
创建语法及关键字 是否实际占用物理空间 使用
视图 create table 占用较小只是保存了sql逻辑 一般只是用查询
表 create view 是,保存了实际数据 增删改查

*/
#案例:查询J开头的员工的姓名和部门名
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`
WHERE a.`first_name` LIKE 'J%'

建立视图:
CREATE VIEW v1
AS
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`

从视图中查询:
SELECT * FROM v1
WHERE first_name LIKE 'J%'


#一、创建视图

#语法:
create view 视图名
as
查询语句;




#二、视图的修改

#方式一:
create or replace view 视图名
as
查询语句;


#方式二:
alter view 视图名
as
查询语句;

#三、删除视图
#语法:
drop view 视图名,视图名,....;
DROP VIEW v1,v2

#四、查看视图
desc 视图
show create view 视图


#五、视图的更新
#1.插入
insert into 视图 values()
与表的插入类似

#2.修改
与表的更新类似

#3.修改
DELETE FROM v1 
WHERE last_name = '展飞'

#具备以下特点的视图是不允许更新的
1.包含 group by,distinct,分组函数,having,union ,union all
2.常量视图
3.select中包含子查询
4.join
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表


###20.变量
/*

系统变量:
全局变量
会话变量

自定义变量
用户变量
局部变量

*/

#一、系统变量
#说明:变量由系统提供,不是用户定义,属于服务器层面

#使用的语法:
1.查看所有的系统变量
show global|【session】 variables;
2.查看满足条件的部分系统变量
show global|【session】 variables like '%char%'
3.查看指定的某个系统变量的值
select @@ gloable|【session】.系统变量名; //跨连接有效
4.为某个系统变量复制
set gloabal|【session】 系统变量名 = 值;
注意:如果是全局级别,需要加global
如果是会话级别,则需要加session 
如果不写,默认session

#1、全局变量
#作用域:服务器每次启动将为所有的全局变量赋初始值,修改针对于所有的连接都有效,
但是不能跨重启(重启会初始化)

#2、会话变量
#作用域:仅仅针对当前会话(连接)有效


#二、自定义变量
/*

说明:变量由用户定义的,不是系统的

#使用步骤:
声明
赋值
使用(查看、比较、运算)


*/

#1.用户变量
#作用域:针对有当前会话(连接)有效的,同于会话变量的作用域


#应用:可以放在begin end任何地方
#①声明并初始化
赋值的操作符:= 或 :=
set @用户变量名 = 值;
set @用户变量名:=值;
select @用户变量名 :=值;

#②赋值(更新用户变量的值)
方式一:
set @用户变量名 = 值;
set @用户变量名:=值;
select @用户变量名 :=值;

方式二:通过select 字段 into @变量名

select 字段 into @变量名
from 表;

#③使用(查看用户变量的值)
select @用户变量


#2.局部变量
#作用域:仅仅再定义在他的begin end 中有效

#应用:应用在begin end中的第一句话

#①声明
declear 变量名 类型;
declear 变量名 类型 default 值;

#②赋值
方式一:
set 局部变量名 = 值;
set 局部变量名:=值;
select @局部变量名 :=值;

方式二:通过select 字段 into 变量名

select 字段 into 变量名
from 表;

#③使用
select 局部变量名;


#3.对比用户变量和局部变量
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何位置 必须加@符号,不用限定类型
局部变量 begin end中 只能在begin end中,且为第一句 一般不用加@符号,需要限定类型






###21.存储过程和函数
/*

#类似于java中的方法

#好处:
1.提高代码的重用性
2.简化操作




*/

#存储过程

#含义:一组预先编译好的sql语句的集合。

#好处:
1.提高代码的重用性
2.简化操作
3.减少了编译次数和数据库服务器的连接次数,提高效率

#一、创建语法
#语法:
create procedure 储存过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
注意:
1.参数列表包含三部分
参数模式 参数名 参数类型
举例 IN stuname VARCHAR(20)

参数模式:
IN:该参数可以作为输入,也就是说该参数需掉用方法传入值
OUT:该参数可以作为输出,也就是该参数可以作为返回值
INOUT:该参数既可以作为输入,又可以作为输出,即该参数既可以传入值,也可以返回值
2.如果存储过程体仅仅只有一句话,begin end 可以省略
存储过程体中的每条sql语句的结尾必须加分号
存储过程的结尾可以使用delimiter重新设置
语法: delimiter 结束标记
案例: delimiter $

#二、调用语法
#语法:
call 存储过程名(实参列表);


#1.空参列表
#案例:插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
INSERT INTO admin (username,PASSWORD)
VALUES('j2','0000'),('j3','0000'),('j4','0000'),('j5','0000'),('j1','0000');
END $
调用:
CALL myp1()$

#2.创建带in模式参数的存储过程
#案例1:创建存储过程实现 根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END $

call myp2('关晓彤')$
#案例2:创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3 (IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN 
DECLARE result INT DEFAULT 0;

SELECT COUNT(*) INTO result
FROM admin a
WHERE a.username = username
AND a.password = PASSWORD;

SELECT IF(result>0,'成功','失败');

END $

#3、创建带out模式的存储过程

#案例1:根据女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT bName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO bName
FROM boys bo
INNER JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;

END$

调用:
DELIMITER $
CALL myp5('周芷若',@bName)$
SELECT @bName$

#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最终a和b都翻倍并返回


#二、删除存储过程
语法:drop procedure 存储过程


#三、查看存储过程的信息
show create procedure myp2;


#函数
#好处:
1.提高代码的重用性
2.简化操作
3.减少了编译次数和数据库服务器的连接次数,提高效率

#区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有一个返回,适合做处理数据后返回一个结果


#一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end



/*
注意:
1.参数列表 包含两部分:
参数名 参数类型

2.函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不会报错,但是不建议

3.函数体只有一句话,可以省略begin end

4.使用delimiter语句作为设置结束标记



*/

#二、调用语法
select 函数名(参数列表)


#1.无参有返回
#案例1:返回公司的员工个数
CREATE FUNCTION myf1()RETURNS INT 
BEGIN 
DECLARE c INT DEFAULT 0; 
SELECT COUNT(*) INTO c 
FROM employees; RETURN c; 
END$


SELECT myf1()

#2.有参有返回
#案例:根据员工名,返回他的工资


#三、查看函数
show create function 函数名

#四、删除函数
drop function 函数名


###流程控制结构
/*
顺序结构:程序从上往下依次执行
分支结构:程序可以从两条或多条路径中选择一条去执行
循环结构:在满足一定条件的基础上,重复执行一段代码


*/

#一、分支结构
#1.if函数
功能:实现简单的双分支

语法:select if(表达式1,表达式2,表达式3)

执行顺序:
如果表达式一成立,则if函数返回表达式2的值,否则返回表达式3的值

应用:任何地方

#2.case结构

情况1:类似于java中的switch语句,一般用于实现等值判断

语法:
case 变量|表达式|字段
when 要判断的值1 then 返回的值1或语句1;
when 要判断的值2 then 返回的值2或语句2;
...
else 要返回的值n或语句n;
end case;

情况2:类似于java中的多重if语句,一般用于实现区间判断

语法:
case 
when 要判断的条件1 then 返回的值1或语句1;
when 要判断的条件2 then 返回的值2或语句2 ;
...
else 要返回的值n或语句n;
end case;

特点:
①既可以作为表达式,嵌套在其他语句中使用,begin end中或begin end后面
可以作为独立的语句去使用,只能放在begin end中

②如果when中的值满足或条件成立,则执行对应的then后面的语句,并结束case
如果都不满足,则执行else中的语句或值

③else可以省略,如果else省略并且所有when都不满足时,则返回null


#案例:创建存储过程,根据传入成绩,来显示等级,如果传入90-100 A 80-90B 60-80 C 其他D
CREATE PROCEDURE 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$


#3.if结构
功能:实现多重分支

语法:
if 条件一 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;

应用:
在begin end中

#案例:创建存储过程,根据传入成绩,来显示等级,如果传入90-100 A 80-90B 60-80 C 其他D


#二、循环结构
分类:
while、loop、repeat

循环控制:
iterate类似于 continue
leave 类似于break


#1.while
语法:
【标签:】while 循环条件 do 
循环体;
end while【标签】;

#2.loop
语法:
【标签:】loop
循环体;
end loop 【标签】;

可以用来模拟简单的死循环

#3.repeat
语法:
【标签:】repeat
循环体;
until 循环结束的条件
end repeat 【标签】;


#没有添加循环空语句
#案例:批量插入,根据次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE ('rose','6666');
SET i = i +1;
END WHILE;
END$
CALL pro_while1(100)$


#添加一个leave语句
#案例:和上一个相同,如果次数》20则停止
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE (CONCAT('rose',i),'6666');
IF i>=20 THEN LEAVE a;
END IF; 
SET i = i +1;
END WHILE a;
END$
CALL pro_while1(100)$

posted @ 2020-01-06 23:52  不是正经鱼  阅读(...)  评论(...编辑  收藏