文章目录
目录
2.用select查询数字常量、表达式(还可以是字段，变量，函数，或多个组合)
前言
在当今这个数据产生速度和有用数据价值越来越重要的时代，数据的存储和提取就显得十要重要，因此数据库对每一个与数据打交道的打工人也变得越来越重要。
数据库的基本概念
1.DB：数据库，存储数据的容器
2.DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
3.SQL：结构化查询语言，用于和数据库进行通信的语言，不是某个数据软件特有的，而是几乎所有的主流数据库软件通用的语言
数据库存储数据的特点和结构
1.数据存放在表中，然后表再放到库里面，上图就是一个简单的表
2. 一个库里面可以有多张表，每一张表具有唯一的表名用来表示标识自己
3.表中的每一行可以看成一个有各种属性的对象
4.表中有一个或者多个列，列又称为字段，每一列可以看作一个属性，每一个对象(即每一行)具备这些属性，但是其属性值可能不同或者缺失
一、DQL语言(数据查询语言)
1.基础查询
1.数据查询语法
select
查询字段
from
表名
注意：查询结果均为虚拟表
2.用select查询数字常量、表达式(还可以是字段，变量，函数，或多个组合)
#查询函数
select 函数名(实参列表);
3.给列表字段取别名-----便于阅读
select
查询字段
from
表名
as
别名;
#注意:as可以省略(用空格代替)
如果as后面的别名是good friends这种带有空哥分隔的应该加引号为‘good friends’4.mysql中‘+’号仅用作运算符
select '123' + 10 as 结果; ----结果为133，因为‘123’可以成功转换为数值
select 'jack' + 10 结果; ----结果为10，因为'jack'不能转换成数值，故其视作0
总结如下：
select 数值+数值 ----直接运算
select 字符+数值 ----先试图将字符类型强制转换为对应数值，成功则进行数值运算，失败则直接将字符转化为0，再进行数值运算
select null + 任何 -----结果都是null5.查询去重
要对查询结果去重，只需要在select后面加上关键字distinct即可，系统会以该字段作为标准对表进行删除
select distinct
字段
from
表名;
eg:
select distinct department_id from employees;注意：select distinct一次只能取一个字段，如果取多个字段，那么可能以每一个字段作为标准去重后的行数量不同，无法对齐，sql会给出报错，下面是错误示例：
#错误示例1：
select distinct a, b, c from data;
#错误示例2:
select distinct a, distinct b from data6.查询结果的拼接----concat()函数
CONCAT(str1,str2,...)
eg:
select
concat(first_name, ',', last_name, ',', salary)
As
结果
from
employees;例子如下：
7.显示表中的全部字段
select * from employees;8.ifnull()函数
IFNULL(expr1,expr2)
exper1为操作的字段， 用exper2的值取替换exper1中的null，结果返回一个进行操作后的列表9.isnull()函数
ISNULL(expr)
expr参数为要进行操作的字段
如果是null则替换为1，不是则替换为0
结果返回替换后的字段2.条件查询
1.条件查询基础语法
select
字段
from
表名
where
查询条件
内部的实现过程是先根据查询表名在数据库中查询出对应的表（from），然后根据筛选条件去除不满足条件的行（where），最后再进行字段的查询（select）。
2.简单条件运算符
查询条件中运用>, <, =, <=, >=, <>和安全等于:<=>（可用于判断null）
示例如下：
select
first_name, last_name, age
from
data
where
age>=183.逻辑运算符
&& --- and
|| --- or
! --- not
示例如下：
select
first_name, last_name, age
from
data
where
age>=18 && ages <=354.模糊查询
（1）like: 一般搭配通配符使用， 用于判断字符型或者数值型，常用通配符如下：
%:匹配任何字符或者数值，都是不能匹配null
_:匹配单个字符或者数值
示例如下：
select
first_name, age
from
data
where
first_name like '_a%' or age like '2_'
#筛选名字第二个字符是‘a’的或者年龄是20及以上的
在用到通配符时，可能需要使用转义字符，使其后面的一个字符不具有特殊作用，关于转义字符的使用有下列二种办法
#第一种是直接使用sql的转义字符‘\’：
select
first_name
from
data
where
first_name like '_\_%'. #选取第名字的第二个字符是‘_’的名字，比如k_ing,\是转义字符
#使用适宜字符作为转义字符，但是要使用关键字escape
select
first_name
from
data
where
first_name like '_$_%' escape '$' ##选取第名字的第二个字符是‘_’的名字，比如k_ing,$是转义字符
注意：在sql中是不区分大小写的
（2）between and (包括首尾)，直接看示例如下：
select
first_name, age
from
data
where
age between 18 and 35 #筛选年龄是18到35之间的（3）in : 某种意义上等价于‘=’，用于判断是否在所设列表中
select
first_name, age
from
data
where
age in (19, 20);
#选取年龄是19或者20的，等价于：
select
first_name, age
from
data
where
age = 19 or age = 20;注意：in等价于‘=’ 故不可以配合通配符使用，也不能使用like，非法
（4）is null / is not null (注意不要写成not is null) ----专用于判断null值，is不可与其他数值或者字符搭配，非法
3.排序查询
1.排序查询基本语法
select
字段
from
表名
[where 筛选条件]
order by
排序列表 [asc(升序-默认)｜desc(降序)]示例如下：
select
first_name, age
from
data
order by
age ； #通过年龄升序排序，不指定asc、desc则默认为asc升序2.排序查询与函数搭配
这里提前介绍一个length()函数，它返回传入字符的字节数(在utf-8编码下，一个英文字符占一个字节，一个中文字符占三个字节)，常用于按字符的字节数排序查询
示例如下：
select
first_name, age
from
data
order by
length(first_name) desc; #按姓名的字节数降序查询3.同时传入多个查询列表的排序查询
在示例中进行讲解：
select
*
from
data
order by
age asc, height desc;
# 先按照年龄进行升序排序，然后对于年龄相同的按照身高降序排序4.排序查询 order by的位置
order by子句一般放在查询语句的最后面， limit子句除外
order by字句后面可以支持单个字段、多个字段、表达式、函数、别名
4.mysql基础函数的介绍
mysql的基础函数分为二大类，分别是单行函数和分组函数，单行函数指主要用于数据处理的函数，而分组函数主要用于对数据进行统计和聚合功能。
函数使用的基本语法如下：
select 函数名(实参列表) [from 表];
1.单行函数
常用单行函数又可以分为：字符函数，数学函数，日期函数，其他函数，控制函数，分别对应着自己作用的领域。
1.字符函数
常用字符函数如下：length(), concat(), substr(), instr(), trim(), upper(), lower(), lpad(), rpad(), replace()。
（1）length()函数
select LENGTH(str);
length()接受一个字符参数，返回其对应的字节数（2）concat()函数
select CONCAT(str1,str2,...)
concat()函数可接受多个字符，对其进行拼接（3）substr()函数
传入一个字符参数和索引，在索引处进行切割，返回切割后片段。
注意：sql的索引是从1开始的
还可以传入第二个参数，表示从第一个索引参数开始，切割长度为多少的片段，上述例子表示从索引1开始切割长度为3的字符片段。
（4）instr()函数
传入二个字符参数，返回第二个字符在第一个字符中从左到右首次出现的索引。
如果没有找到则返回0：
（5）trim()函数
从第二个字符参数左右二边开始依次去除字符参数1，直到不是的时候停止
（6）upper()和lower()函数
对传入字符参数进行大写、小写操作
（7）lpad()和rpad()函数
lpad()是左填充，rpad()是右填充，这里以rpad()为示例
传入三个参数，分别是原字符，指定长度，填充字符，用填充字符在原字符右边进行填充至指定字符长度，如果指定长度小于原字符长度则进行截断操作。
（8）replace()函数
传入三个字符参数：a,b,c;在a中查找全部的b，并用c去进行替换。
2.数学函数
sql中常用数学函数如下：round(), ceil(), floor(), truncate(), mod()
（1）round() -----------四舍五入
传入二个数值参数，第一个是操作数，第二个是保留几位小数，不传入则默认为不保留小数。
（2）ceil()和floor()-----------向上取整和向下取整
ceil()传入一个数值参数，返回一个大于等于它的最小整数
floor()传入一个数值参数，返回一个小于等于它的最大整数
（3）truncate()--------数值的截断
传入二个数值参数，上图所示表示保留一位小数进行截断
（4）mod()-------取余/取模
（5）rand----取0到1直接的随机数，不包括0和1
3.日期函数
常用日期函数如下：now(), curdata(), curtime(), year(), month(), day(), hour(), minute(), second(), str_to_data, data_format.
（1）now()-------返回当前系统日期及时间
（2）curdata(), curtime()------返回当前系统日期、返回当前系统时间
（3）year(), month(), day(), hour(), minute(), second(),返回日期型数据的年，月，日，分，秒
（4）str_to_date()------将日期格式的字符转换成指定格式的日期
格式符如下表：
（5）date_format------将日期转换成指定格式的字符
（6）datediff ------传入二个容器参数，返回它们的天数差，大家可以看看自己已经活了多少天，下面是我的：
（7）monthname----以英文形式返回月
4.其它函数
常用其它函数主要包括：version(), user(), database()
（1）version-----查看当前mysql版本
（2）database-----查看当前打开的数据库
（3）user()-----查看当前用户
（4）password()-----传入一个字符参数，返回该字符的密码形式
（5）md5()----传入一个字符参数，返回该字符的md5加密形式
5.控制函数
常用控制函数为if和case
（1）if函数，类似于其它编程语言中的三元运算符，其基本语法如下：
select if(判断条件， 条件为正的返回值， 条件为否的返回值)；示例如下：
（2）case函数一般有二种用法，第一种用法的功能类似于C语言中的switch语句，其基本语法如下：
case 要判断的变量、字段或者表达式 功能类似c语言里面的switch,一般用于处理等值判断
when 常量1 then 要显示的值1(或语句1；)
when 常量2 then 要显示的值2(或语句2；)
......
else 要显示的值n(或语句n;)
end
上述例子中运用case函数根据部门编号来决定员工的新工资。
case函数的第二种用法的作用类似于C语言中的多重if-else语句，其基本语法如下：
case 作用类似c语言里面的多重if
when 判断表达式1 then 要显示的值1(或语句1；)
when 判断表达式2 then 要显示的值2(或语句2；)
·····
else 要显示的值n(或语句n;)
end
上述例子运用case函数，根据员工的工资来确定其级别。
2.分组函数
分组函数主要作用是数据的统计与聚合，常用的分组函数主要包括以下几个：sum, avg, max, min, count。
1.分组函数的基础用法
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX((salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#上述分别是从employees表中获取工资的和、平均值、最小值、最大值、数量2.各分组函数支持的参数类型
（1）sum,avg的参数只在传入数值数据时有意义，传入字符、日期类型数据作为参数不会报错但是没有意义,而且sum和avg都会忽略null
（2）max和min支持字符、日期类型（有自己的排序规则）作为参数可以正常使用，是有意义的,max和min忽略null
（3）count支持任何类型的参数，且count计数计的是非空（非null）的数量，它支持任何参数
3.分组函数常用的技巧及其注意
（1）各分组函数可以和distinct搭配实现去重的运算
select sum(distinct salary) as out_put from employees
select avg(distinct salary) as out_put from employees
........（2）用count函数来对行数进行统计
SELECT COUNT(*) FROM employees; #统计满足不是全部为null的行的数量
SELECT COUNT(1) FROM employees; #统计全部的行数，count()内的参数可以是任何常量，该示例其实现原理就是给表新增了一列全为1的字段，然后统计该字段的1的数量（3）和分组函数一同查询的字段有限制，其查询字段下的值没有任何意义
这是因为分组函数最终返回的一定是一个值，由于最终返回的表应该是对齐的规则表格，故一起查询的字段返回的值是没有任何意义的，其示例如下：
SELECT AVG(salary), employee_id FROM employees;
#字段employee_id字段下返回的值是没有任何意义的5.分组查询
在学习分组查询之前，有必要了解一下已学sql语法的执行顺序：
from, where, groud by, having, select, distinct, order by
即from先找表，然后where作分组前筛选，除去不满足条件的行，然后groud by按条件作分组，having对分组后各个部分作筛选，将各组中不满足条件的行除去，然后select对各组进行字段查询及去重，最后拼接成新表再进行排序
1.分组查询基本语法
select 分组函数， 分组后的字段(要求出现在groud by的后面)
from 表
【where 分组前筛选条件】
groud by 作为分组的字段
【having 分组后筛选条件】
【order by】
分组查询一定是和分组函数配合使用的，这样才能使最终得到的结果表是对齐的
示例如下：
2.添加分组前条件查询----where
这一部分的理解要综合上面的sql语法执行顺序
示例如下：
3.添加分组后条件查询----having
注意区分分组前条件和分组后条件，这一部分的理解要综合上面的sql语法执行顺序
示例1如下：
示例2如下：
4.按表达式或函数分组（不常用）
5.按多个字段进行分组
6.添加排序
6.sql92语法下的连接查询
当查询中涉及到多个表的字段，需要使用多表连接，且一般通过二表的共同字段来将二表连接起来,这里先基于sql92语法来介绍一下，sql92语法只支持内连接
1.表格拼接的基本原理
在了解连接查询之前，应该了解一下sql进行表连接的基本原理
在对来自二张表的数据进行同时查询时，按照sql语句的执行顺序，首先执行from语句，from发现是多张表时会先对表格进行拼接，拼接是按照笛卡尔集发生，即第一个表的每一列和第二个表的每一列进行一个拼接，上述表格拼接后如下:
12*4 = 48行，这就是经过from实现表格拼接以后得到的表格，然后进行后续语句的执行，可以把我们的条件查询，分组查询，排序查询都运用上去
2.等值连接
对值连接基本语法如下：
select 查询列表
from 表1 别名， 表2 别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】
像上述案例中，运用了给表名取别名的技巧，这样避免了sql混乱、歧义的报错，但是要注意，给表名取别民是在from这一步实行的，from是sql语句中首先执行的语句，故后面都应该去使用表名的别民
3.非等值连接
非等值连接条件即where筛选条件是不是等值判断，可以是不等式判断或取值范围判断，基本语法如下：
select 查询列表
from 表1 别名， 表2 别名
where 非等值条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】
上述例子，为根据员工的工资，确定对应的工资级别，为根据取值范围来确定连接，故使用非等值连接。
4.自连接
自连接即自己与自己连接，即同一张表的连接，自连接就把给表名取别民的作用大大体现了出来
自连接的基本语法如下：
select 查询列表
from 表 别名1， 表 别名2
where 连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】
上述例子，为查询每一个用户的管理者，employees表部分数据如下：
知道管理者id又要返回该表查询对应employee_id，故需要进行自连接。
7.sql99语法下的连接查询
1.sql99语法下的连接查询
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
连接类型：
内连接：inner
外连接：
左外：left【outer】
右外：right 【outer】
全外：full 【outer】----mysql暂不支持
交叉连接：cross执行顺序：from - join on - where - group by - having - select - order by
sql92语法与sql99语法的对比：
在功能方面sql92语法下的连接查询仅支持内连接，但是sql99语法支持内连接、外连接，交叉连接。
在程序可读性下，sql92语法将连接条件与分组前筛选条件共同放与where语句之下，可读性不高，而sql99语法实现了连接条件和分组前筛选的分离，连接条件放于on语句之下，分组前筛选放于where之下。
2.内连接---inner join
sql99的内连接和sql92内连接基本相同，知识作的连接条件与分组前筛选的分离。sql99的内连接同样包括：等值连接， 非等值连接， 自连接，此下为几个例子：
3.外连接
（1）外连接的基本原理
外连接会选取一张表作为主表，另一张表则作为从表，根据关联列进行匹配，匹配成功则保留于新表，不成功怎会在保留主表信息的情况下用null去进行填充
（2）左、右外连接 ---left outer join、right outer join
所谓左、右连接，就是以左边的表作为主表，或以右边的表作为主表，这就让我们可以在保留主表完整信息的条件下去进行表连接，下面是一个基于girl数据库示例：
（3）全外连接---cross outer join
mysql暂时还不支持全外连接
4.交叉连接
所谓交叉连接，其实就是返回一个笛卡尔积连接的表，其关键字是cross join
5.内连接，外连接，交叉连接的应用图示
8.子查询
嵌套在其他语句内部的select语句称为子查询或内查询，如下：
#查询工资最低的员工的姓名
select last_name
from employees
where salary = (
select min(salary)
from employees
)1.子查询的分类与常用位置
（1）按结果集的行列分类：
标量子查询(单行子查询)：结果集为一行一列
列子查询：结果集为多行一列
表子查询：结果集为多行多列
（2）各类子查询常用位置
select后面：
仅支持标量子查询
from后面：
常跟表子查询
where或having后面：
标量子查询
列子查询
行子查询
exist后面：
任意子查询均可
2.标量子查询
标量子查询主要是查出某一个值，然后用于主查询中
#谁的工资比Abel高
SELECT
*
FROM
employees
WHERE
salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);3.多行子查询
多行子查询查询常在where后面搭配in，或者any使用
#返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN ( #in可以换成 = any 效果相同 not in 的话可以替换为 <> all 效果相同
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700)
);4.表子查询
表子查询的结果是一张多行多列的表，常用于from后面，可能还会与其他表进行一些表连接
#查询各部门中工资比本部门平均工资高的员工的员工号， 姓名和工资，应用了from后面的子查询和内连接
SELECT e.employee_id, e.last_name, e.salary, e.department_id
FROM employees e INNER JOIN (
SELECT department_id, AVG(salary) ag
FROM employees
GROUP BY department_id
) ag_dep ON e.department_id = ag_dep.department_id
WHERE e.salary > ag_dep.ag;5.exist语句
exist语句的基本语法和作用如下：
#四、exists后面（相关子查询）#它用于判断查询结果有没有值，有返回1，没有返回0 先执行主查询，再执行子查询
/*
语法：
exist(完整的查询语句)
结果：
0或者1
*/
9.多表查询
主要用于分页，基础语法如下：
/*
select 查询列表
from 表
limit 要显示条目的起始索引（起始索引从0开始）, 要显示的条目个数
*/#查询11条到第25条的员工信息
SELECT *
FROM employees
LIMIT 10, 15;10.联合查询
用于要查询的结果来于多个表，且这多个表没有直接的连接关系，但查询信息一致时,将多条查询语句的结果合并成一个结果
基本语法如下：
/*
查询语句1
union
查询语句2
union
。。。。
*/注意：
(1).要求多条查询语句的查询烈数是一致的
(2).要求多条查询语句的查询的每一列的类型和顺序最好一致
(3).union关键字默认去重，如果使用union all可以包含重复项
二、DML语言(数据操作语言)
在库中的一个表上进行操作,基本操作单位是行：
插入：insert
修改：update
删除: delete
1.经典插入语句
1.基本语法：
/*
insert into 表名(列名......) #可以省略表名后面的列名，则默认为按表中顺序的全部列名
values(值1.....)
*/#在beauty表中插入新的一列数据
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '1898888888', NULL, 2);2.注意事项：
(1).插入的值的类型要与列的类型一致或兼容
(2).输入的列名必须与输入的值相等
(3).不可以为null的列必须插入值。可以为null的，可以直接输入null，也可以在表名中省去该表名，会自动用null填充
#直接在可以为null的列输入null
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '1898888888', NULL, 2);
#直接在列名中省去可以为null的列名
INSERT INTO beauty(id, NAME, sex, borndate, phone, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '1898888888', 2);(4).列的输入顺序可以随意，但是注意值的输入要与之对应
INSERT INTO beauty(NAME, id, sex, borndate, phone, photo, boyfriend_id)
VALUES('唐艺昕', 13, '女', '1990-4-23', '1898888888', NULL, 2);(5).可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
(6).经典插入语句支持一次插入多行，只需要输入一次values即可
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '1898888888', NULL, 2)
,(14, '金星', '女', '1990-4-23', '1898888888',NULL，9);
(7).经典插入语句支持子查询，即用一个select查询结果作为新行插入
insert into beauty(id, NAME, phone)
select 26, '好人', '110'3.第二种插入语句
基本语法如下：
/*
insert into 表名
set 列名=值， 列名=值，。。。。
*/该语法在可读性上更强，但是其不支持多行插入，和子查询
2.修改语句
1.修改单表的基本语法
/*
update 表名
set 列 = 新值, 列 = 新值....
where 筛选条件；
*/#将beauty表中姓唐的列的电话修改为‘110’
UPDATE beauty
SET phone = '110'
WHERE `name` LIKE '唐%';2.修改多表的基本语法（其实就是多了一个表连接）
/*
update 表1 别民
inner|left|right| join 表2 别名
on 连接条件
set 列=值，列=值.....
where 筛选条件
*/#修改张无忌的女朋友的手机号为114
UPDATE boys bo
JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.phone = '114'3.删除语句
sql中表数据的删除是以行作为基本单位去删除的
1.方式1----delete
(1)单表的删除
#单表删除的基本语法
/*
delete from 表名 where 筛选条件 【limit 条目数】
*/#删除手机号以9结尾的女生信息
DELETE FROM beauty WHERE phone LIKE '%9';(2)多表的删除（其实就是多了一个表连接）
/*
delete 表1的别民，表2的别民------根据要在那个表进行删除进行输入
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件；
*/#删除张无忌的女朋友的信息
DELETE b #表示在beauty中去删除
FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE bo.boyName = '张无忌'2.方式2----truncate
sql删库跑路必备！！！
#基本语法
#truncate table 表名; ----不能跟筛选条件，主要用于整张表的删除#删除beauty表
truncate table beauty3.二种删除方式的对比
(1)delete可以加where条件,truncate不可以
(2)假如要删除的表有自增列，如果使用delete删除后，再插入数据，自增长列的值从断点开始，但是如果是truncate删除，自增长列从值1开始
(3)truncate删除没有返回值，delete删除有返回值
(4)truncate删除不能回滚，如果在事务中使用了truncate，回滚无法恢复被删除的数据，delete删除可以回滚
三、DDL(数据定义语言)
1.库的管理
1.库的创建
create database 【if not exists】库名 【character set 字符集名】2.库的修改(使用较少，安全风险大)
(1).更改库的名字
rename database 待修改的库名 to 新库名(2).更改库的字符集
alter database 待修改的库名 character set 新的字符集；3.库的删除
drop database 【if exists】待删除的库名2.表的管理
1.表的创建
create table 【if not exists】 表名(
列名 列的类型【长度、约束】,
.....
)2.表的修改
(1).新增列----add
新增列默认为插入到最后一列，如果要指定插入顺序则应该使用关键字first、after 列名
alter tables 表名 add column 新增的列名【列类型 约束】【first｜ after 字段名】(2).删除列----drop
alter table 表名 drop column 列名(3).修改列的名字----change
alter table 表名 change column 原列名 新列名【列类型 约束】(4).修改列的类型、约束条件----modify
alter table 表名 modify column 列名【新属性 约束】(5).修改表名---rename
alter table 原表名 rename 【to】新表名3.表的删除与查看
#删除表
drop table 【if exist】 表名
#查看表
desc 表名;4.复制表
复制表可以跨库，用库名.表 即可
(1).复制表的结构
create table 待创建的表名 like 旧表(2).复制表的部分结构外加部分数据
create table 待创建的表名
select 查询列表 from 旧表【where筛选】3.sql中常见数据类型
1.常见整型
tinyint--- 1字节, smallint--- 2字节, mediumint--- 3字节, int --- 4字节, bigint --- 8字节
特点:
(1).如果不设置无符号还是有符号， 默认为有符号，如果想设置无符号，需要添加画unsigned关键字
(2).如果插入的数值超过了整型的范围，会报out of range异常，并且插入临界值
(3).如果不设置长度，会有默认长度，长度代表了显示的最大宽度。可以搭配zerofill使用，此时默认为无符号，会用0对多余部分进行填充
2.常见小数型
浮点型：float(M, D), double(M, D)
定点型：dec(M, D), decimal(M, D)
特点：
(1).M:整数部分加小数部分总的个量
D:小数部分个数
如果超出范围则插入临界值
(2).M和D都可以省略
如果是decimal，则M默认为10，D默认为0，即此时无法插入小数
(3).定点型的精度较高，如果要求插入数值的精度较高则优先考虑使用
3.常见字符类型
较短文本：char, varchar
较长文本：text, blob(较大的二进制)
特点：
char(M) :M表示最大的字符数， 可以省略， 默认是1，用于固定长度的字符
varchar(M): M表示最大的字符数， 不可以省略， 用于可变长度的字符
4.常见日期类型
data: 只保存日期
time: 只保存时间
year: 只保存年
datatime: 保存日期和时间，8字节, 不受时区的影响
timestamp(时间戳): 保存日期加时间，4字节，受时区的影响
4.常见约束条件与约束的添加
1.常见约束条件
primary key #主键约束，表明该列不得有重复，且不能为空，且一个表中只能有一个该类型的约束列
unique #唯一约束，表明该列不得有重复，null也不能重复
default #默认约束，表明该列有默认值进行填充（填充值应该与该列数据类型相符）
not null #非空约束，表明该列不可以有空缺值
foreign key #外键约束，表明该列的值必须出现在对应主表的关联列中
check #检查约束，mysql暂时还不支持2.给表添加约束
(有一个问题，默认和非空不能设置约束名吗，使用列级约束时，主键，外键，唯一键的默认约束名又是什么)
(1).列级约束的添加：支持默认，非空，主键，唯一约束的添加，不支持外键约束的添加
create table if not exists a_tab(
id int primary key, #该字段不得有重复且不能为空
stuname varchar(20) unique, #该字段不能重复
age int default 18, #该字段对空值自动填充18
seat int not null unique, #该字段不能为空，且不得重复
height float(5, 2)
);(2).表级约束的添加：不支持非空，默认约束，其它约束都支持
create table b_tab(
id int,
stuname varchar(20),
age int,
seat int,
height int,
constraint pk primary key(id) #将id列设为主键，约束名为pk
constraint uq unique(stuname, seat) #将stuname, seat列设置不可重复约束，约束名为uq
constraint out_key forigen(id) references major(id) #对id列添加外键约束，其依附于major表的id列
)3.在修改表时添加约束
#添加列级约束
#alter table 表名 modify 待修改的字段名 字段类型设置 新的约束
alter table a_tab modify id int primary key #修改id列为主建
#添加表级约束
#alter table 表名 add 【constraint 约束名】约束类型(字段名) 【外键的引用】
alter table a_tab add constraint out_key forigen(id) references major(id);4.删除约束
(1).对于非空约束、默认约束的删除
#删除约束，不加约束即可
alter table 待修改的表的表名 modify column 字段类型;(2).对唯一约束的删除
alter table 表名 drop index 唯一约束名;(3).对主键约束的删除
alter table 表名 drop primary key;(4).对外键约束的删除
alter table 表名 drop foreign key 外键约束名;5.认识标识列(自增长列)
标识列又称自增长列，该列可以不用手动插入值，系统会提供默认的序列值。如下：
create table if not exists tab_1(
id int primary key auto_increment,
name varchar(20)
);
insert into tab_1(name)
values('小明'),
('小张');
结果如下：
1.标识列的特点
(1).标识列必须配合一个key(主键，外键，唯一约束)使用
(2).标识列最一个表中至多一行
(3).标识列的字段类型必须是数值型
(4).标识列可以通过 set auto_increment_increment = 新步长,来设置增长的步长，另外可以通过手动插入值来设置起始值
2.标识列的设置
(1).创建表的时候添加：直接在列级约束主键或唯一键后继续添加关键字auto_increment，外键添加标识列不常见
(2).修改表的时候添加标识列:同样也是在列级约束key类型后面添加关键字auto_increment
3.标识列的删除
alter table 表名 modify columns 字段名 字段类型 字段约束；
#不添加关键字auto_increment即可4.TCL语言
1.事务的创建及其特性
事务:事务是由一个或多个sql语句组成的单元，在这个单元中，每一个sql语句是相互依赖的，共同作为一个不可分割的整体，要么全部执行，要么全部不执行。如果全部语句执行成功，则事务会顺利执行，如果事务中有至少一个语句没有执行成功，则会回滚，即回到最初没有执行事务中一条语句的初始状态
1.事务的特性(ACID):
原子性：一个事务不可再分割，要么都执行，要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务执行不受其他事务的干扰
持久性：一个事务一旦提交，则永久地改变数据库的数据
2.事务的创建：
#第一步：事务的创建
#开启事务
set autocommit = 0; #设置自动提交功能为禁用
start transaction; #可省略，因为当关闭自动提交功能后，默认开启事务编写
#第二步：编写事务中的sql语句(注意只能是增删改查，即insert into, delete, update, select)
#事务语句
#第三步：结束事务
commit; #表示执行事务，对磁盘信息进行更改
rollback; #表示回滚事务，撤回上述操作，回到最初起点3.利用savepoint关键字设置保存点
set autocommit = 0;
start transaction;
delete from account where id = 1;
savepoint a; #设置保存点a
delete from account where id = 2;
rollback to a; #回滚至保存点a
#该代码最后只执行了保存点a以上的语句，没有执行保存点以下的语句2.事务的隔离级别
在一个项目中，一个数据库不会只有你一个用户使用，而是可能有多个用户在使用，这也意味着可能有多个事务同时运行，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，将会导致各种并发问题，这些问题如下：
1.常见的并发问题
（1）脏读：对于二个事务T1，T2，T1读取了已经被T2更新但是还没有提交的字段。之后，如果T2回滚，则T1读取的内容就是临时且无效的
（2）不可重复读：对于二个事务T1，T2，T1读取一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段，值已经发生了改变
（3）幻读：对于二个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再对该表进行一些修改，会发现多修改了T2新增多哪些行
2.数据库提供的四种事务隔离级别
#查看当前隔离级别
select @@tx_isolation
#设置当前mysql连接的隔离级别
set transaction isolation level 隔离级别
#设置数据库系统的全局的隔离级别
set transaction isolation level 隔离级别（1）READ UNCOMMITTED(读取未提交数据)：允许事务读取未被其他事务提交的变更，即脏读，不可重复读，幻读这些并发问题都会出现。
（2）READ COMMIT(读取已提交数据)：只允许事务读取已经被其他事务提交的变更，可以避免脏读，但是不可避免不可重复读，幻读
（3）REPETABLE READ(可重复读)：mysql的默认隔离级别，在该事务持续期间，禁止其他事务对该字段数据进行修改，由此来避免不可重复读和脏读，但是无法避免幻读
（4）SERIALIZABLE(串行化)：在事务持续期间，禁止其他事务对表进行数据的新增、删除、修改，由此可以避免所有并发问题，但是效率过于低下
5.其他
1.视图的创建及其更新
视图是一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。其相对于对一个子查询进行了打包，使其使用起来更加方便，且可以重复使用。
1.视图的创建
create view 视图名
as
查询语句例如：
#创建一个视图叫myv1，它把我下面的查询语句结果作为内容
create view myv1
as
select last_name, salary
from
employees;2.视图的修改
create or replace view myv1 #如果该表不存在则创建，存在则重新覆盖其内容
as
查询语句alter view myv1 #该语句只支持对已存在的表进行全面修改
as
查询语句3.视图的删除
drop view 视图名（可以是多个）4.视图的数据更新
一般实际中很少对视图数据进行更新，因为视图的数据更新会导致原始表的更新。且有以下条件的视图都不可以进行更改：
（1）包含以下关键字的sql语句：分组函数、distinct、group by、having、union、union all
（2）常量视图
（3）select 中包含子查询
（4）select中包含表的连接
（5）from一个不能更新的表
（6）where子句的子查询应用了from子句中的表
如果要对满足条件的视图进行更改，则针对表数据修改的语法对视图同样有效:
增：inser into 语句
删：delete from语句
改：update语句
2.认识变量
1.系统变量
系统变量是由系统提供的，不是用户定义的，属于服务器层面，系统变量又可以分为：
全局变量：服务器每次启动将为所有的全局变量赋初值，针对所有的会话（连接）有效，但不能跨重启
会话变量：仅仅针对于当前会话（连接）有效
2.系统变量的查看与设置
1.查看所有的系统变量
show global|session variables;2.查看满足条件的部分系统变量
show global|session variables like "%char%"3.查看某个指定的系统变量的值
select @@global | session .系统变量名4.为某个指定的系统变量赋值
set 【@@global|session .】系统变量名 = 值;注意：如果是全局级别，则需要加gloabl，但如果是会话级别则加session，如果不写，则默认是session
3.自定义变量
自定义变量是由用户定义的，自定义变量又可以分为：
用户变量：针对当前会话（连接）有效，同于会话变量的作用域，可以应用在任何地方，也就是begin end里面或者外面，主要分三个步骤：声明---赋值---使用（查看、比较、运算）
局部变量：仅仅在定义它的begin end中有效，且必须为begin end里面第一句话，主要分三个步骤：声明---赋值---使用（查看、比较、运算）
4.用户变量的使用过程
#用户变量在整个会话框有用
#声明变量并初始化，在sql中自定义变量是动态类型的,navicat中创建用户变量必须初始化
set @用户变量名 := 值. #此处也可以用'=',但是在为了区分 赋值 与 比较，常用:=
#赋值
set @用户变量名 := 值;
select @用户变量名 := 值;
select 字段 into @变量名 from 表;
#使用
set @x = 1;
set @y = 2;
select @x + @y result; #结果为3
5.局部变量的使用过程
#局部变量仅仅在begin end 中有用，且必须放在其中最前面
#声明，不同于用户变量，局部变量不需要初始化，且是静态的，对他进行赋值可能会发生一些强制类型转换
declare 变量名 类型;
declare 变量名 类型 default 默认值;
#赋值
set 局部变量名 := 值;
select @局部变量名 := 值;
select 字段 into 局部变量名 from 表;
#使用
常用于存储过程中3.存储过程
存储过程是一组预先编译好的SQL语句的集合，可以理解成成批处理语句，它的使用可以提高代码的重用性，简化操作，减少编译次数并且减少了和数据库服务器的连接次数，提高了效率。
1.创建存储过程的语法
create procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL语句，且每一条sql语句需要以分号结尾）
end
注意：
参数列表包含三部分：
参数模式 存储过程名 参数类型
in stuname varchar(20)
参数模式:
in: 该参数可以作为输入，也就是输入值
out: 该参数可以作为输出，也就是该参数作为返回值
inout: 该参数既可以作为输入，又可以作为输出，也就是该参数既需要输入值，又可以返回值
一个存储过程可以传入多个输入参数和返回参数
一般sql的默认结束符是；，如果你是在计算机终端进行sql操作，在存储过程体中你可能会有多个sql语句，也就有多个；，为了防止计算机以为你结束该sql语句并且执行，一般会使用delimiter关键字修改默认结束符。eg：delimiter $ 修改默认结束符为$。这些仅在终端命令可用，在navicat很智能，都直接；结尾即可
示例如下
#创建一个存储过程，用于向admin表中插入固定值
create procedure myp1()
begin
insert into admin(id, name, passWord) values(1, '小张', '0000');
end;
#传入一个姓名，返回其对应的id号
create procedure myp2(in Name archer(20), out Id int)
begin
select id into Id
from admin
where name = Name;
end;
#一个存储过程可以传入多输入参数和多个输出参数2.调用存储过程
call 存储过程名(实参列表);示例如下：
#调用myp1
call myp1();
#调用myp2
call myp2('Lisa', @Id) #系统会自动创建用户变量@Id，将返回值存储于其中3.查看和删除存储过程
#查看
show create procedure; 存储过程名#查看存储过程
#查看存储过程myp1
show create procedure myp1;
#删除
drop procedure 存储过程名; #一次只能删除一个存储过程
#删除存储过程myp1
drop procedure myp1;4.函数
和存储过程类似，函数也是一组预先编译好的SQL语句集合，可以理解成批处理语句。
函数与存储过程的区别：
存储过程：可以有0个或多个返回，且返回不可直接打印，且调用时返回值需要用变量来接受保存，适合做批量插入，批量更新
函数：必须有且仅有一个返回值，且调用时返回值可直接打印，适合做处理数据后返回一个结果
1.函数的创建语法
create function 函数名(参数名 参数类型) returns 返回值类型 #注意是returns，不要掉了s
begin
函数体
return 返回变量;
end示例如下：
#返回公司的员工个数
create function myf1() returns int
begin
declare x int default 0;
select count(*) into x
from employees;
return x;
end
#根据员工名，返回它的工资
create function myf2(empName varchar(20)) returns double
begin
set @result := 0;
select salary into @result
from employees
where name = empName;
return @result;
end2.函数的调用
select 函数名(实参列表)；5.流程控制结构
1.sql中的流程控制结构
（1）顺序结构:程序从上往下执行，程序的默认结构
（2）分支结构:程序从二条或更多条路径中选择一条去执行
（3）循环结构:程序在满足一定条件的基础上，重复执行一段代码
2.分支结构
（1）if函数
#if函数
#用于实现简单的双分支
if(表达式1,表达式2,表达式3)
#如果表达式1成立，则该函数返回表达式2的值，否则返回表达式3的值
#可使用在任何地方（即begin end外面和里面）
eg:
select if(2 > 1, 1, 2) as result; #最终会打印出1（2）if结构
#if结构
#只能用于begin end 中
#实现多重分支
if 条件1 then 语句1;
elseif 条件2 then 语句2； #注意是ifelse 不要写成if else
...
else 语句n;
end if;
eg:
#创建一个事务myp1,接受成绩，返回对应的等级
create procedure myp1(in x int)
begin
if x >= 80 then select 'A' result;
elseif x >= 70 then select 'B' result;
else select 'C' result;
end if;
end;（3）case结构----作为表达式，可用于任何地方
#当case结构作为表达式
#case作为一个整体，仅仅end后面带一个;
#可以运用在begin end中或者外面
#情况1:等值判断
case 表达式
when 值1 then 值1_
when 值2 then 值2_
...
else 值n_
end;
#情况2:范围判断
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end;
case结构作为表达式，常用与查询，根据已知表的字段，映射一个新字段:
#注意中间case部分整体作为一个语句，不要加；
select a.*,
case stuname
when '小张' then '中路'
when '小白' then '打野'
else '上路'
end as '擅长位置'
from a_tab a;查询结果如下：
select stuname, age,
case
when age <= 15 then '中路'
when age > 15 and age <= 20 then '打野'
else '下路'
end as '擅长位置'
from a_tab as a;查询结果如下：
（4）case结构----作为独立的语句，只能用于begin end 中
#当case结构作为独立的语句
#每一个语句结束后面都要带;
#只能运用在begin end中
#情况1:等值判断
case 表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end case;
#情况2:范围判断
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end case;示例如下：
drop function if exists myf1;
create function myf1(score int) returns char
begin
case
when score > 90 then return 'A';
when score between 80 and 90 then return 'B';
when score between 60 and 79 then return 'C';
else return 'D';
end case;
end;
select myf1(79) as 结果; #结果返回C3.循环结构
（1）while
【标签:】while 循环条件 do
循环体
end while 【标签】;（2）repeat
【标签:】repeat
循环体;
until 结束循环条件 #注意，此处不许要加;
end repeat 【标签】;（3）loop
【标签:】loop
循环体;
end loop 【标签】;（4）循环控制
leave:相对于break
iterate:相对于continue
注意:想要在循环体中使用leave、iterate必须给循环设置标签
示例如下：
drop table if exists a_tab;
create table a_tab(
id int primary key auto_increment,
name varchar(20) not null,
age int default 18
);
drop procedure if exists myp1;
create procedure myp1(in insertCount int)
begin
declare i int default 0;
a:while i <= insertCount do
set i = i + 1;
if i % 2 = 0 then iterate a;
end if;
insert into a_tab(name, age) values(concat('同学', i), 18);
end while a;
end;
call myp1(20);结果如下：
1068
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
