电商平台网站开发,东莞网站关键词排名,网站排名优化技巧,个人微信网站怎么做#x1f600;#x1f600;#x1f600;创作不易#xff0c;各位看官点赞收藏. 文章目录 MySQL 学习笔记1、DQL 查询语句1.1、基本查询1.2、函数查询1.2.1、单行函数1.2.2、聚合函数 1.3、复杂查询1.3.1、连接查询1.3.2、子查询 1.4、SQL 语句 执行顺序 2、DDL 定义语句2.1、…创作不易各位看官点赞收藏. 文章目录 MySQL 学习笔记1、DQL 查询语句1.1、基本查询1.2、函数查询1.2.1、单行函数1.2.2、聚合函数 1.3、复杂查询1.3.1、连接查询1.3.2、子查询 1.4、SQL 语句 执行顺序 2、DDL 定义语句2.1、MySQL 数据类型2.2、DDL 常见操作 3、DML 操作语句4、数据约束constraint5、视图 View6、存储过程与函数7、MySQL 变量7.1、系统变量7.2、用户自定义变量 8、触发器9、MySQL8.0 新特性9.1、窗口函数9.2、公共表表达式 MySQL 学习笔记 MySQL 数据库按照一定的格式来储存数据的一些文件的组合就是储存数据的仓库。是专门用来管理数据库中的数据的可以对数据库中的数据进行增删改查。常见数据库管理系统MySQL、Oracle、MS等。 MySQL数据库的官网https://www.mysql.com/
SQL 语句分类
DQL数据查询语言凡是带有select关键字的都是查询语句。DML数据操作语言凡是对数据库的增、删、改。insert、delete、update。DDL数据定义语言主要操作表的结构凡是带有create、drop、alter。TCL事务控制语言包括事务提交commit、事务回滚rollback。DCL数据控制语言包括授权和撤销权限。 基本常用命令行 # windows启动、停止命令(可能需要管理员权限)
net stop/start MySQL服务名称
# 命令登陆到MySQL
mysql -u[登陆用户名] -p[登陆密码]
# 退出MySQL
exit
# 展示数据库列表(要分号结尾)
show databases;
# 使用某个数据库
use [数据库名称]
# 创建数据库
create database [数据库名]
# 查看数据库下的表列表
show tables;
# 导入某个SQL资源路径使用绝对路径路劲不能存在中文
source 资源的路径;
# 查看表结构
desc [表名];
# 查看数据库版本号
select version();SQL 语句注释 # 注释内容。-- 注释内容–后面有一个空格。/* 注释内容*/多行注释。
1、DQL 查询语句 查询语句负责数据库数据查询。关键字 select查询语句只会查询数据永远不会修改表中的内容。 # 最基本查询语句(查询常数)并没有指定表它就会指定伪表DUAL
SELECT 1,2,3
SELECT 1,2,3 FROM DUAL# 查询表中字段
# other在表中没有存在可以给它指定一个默认值
# as表示给查询出来的字段起别名
# FROM指定表名这个as可以省略直接使用空格隔开
SELECT id,name,sex,password,test as other FROM t_user# * 会自动解析成表中所有字段这样写会多一个步骤不推荐
SELECT *,test as other FROM t_user注意对于字符串、时间表达式、字段别名等值需要使用单引号或者双引号包裹起来。
1.1、基本查询 条件查询查询符合条件的数据不符合条件的就不查询出来通过 where 关键字添加条件。 # 比较运算符# 1. 等于
select 字段 from 表 where 字段 条件值;
# 2.安全等于特殊等于可以用于比较NULL值
select 字段 from 表 where 字段 条件值;
# 3.不等于 或!
select 字段 from 表 where 字段 条件值;
select 字段 from 表 where 字段 ! 条件值;
# 4.小于 , 小于等于 , 大于 , 大于等于
select 字段 from 表 where 字段 | | | 条件值;# 关键字运算符# 1.字段值为 null不能使用 字段 null
select 字段 from 表 where 字段 is null;
# 2.字段值不为 null
select 字段 from 表 where 字段 is not null;
# 3.在.....之间between ... and ...左小又大相当于大于等于和小于等于一起使用
select 字段 from 表 where 字段 betwee 条件值1 and 条件值2;
# 4.in包含相当于使用多个or数据值是否在in这个集合中多个匹配数据使用逗号隔开
select 字段 from 表 where 字段 in (值1,值2,值3);
# 5.not in不包含查询数据值不在in这个集合中的数据
select 字段 from 表 where 字段 not in (值1,值2,值3);# 逻辑运算符# 1.并且 and (多个条件同时满足)或者 or || (多个条件有一个满足)and的优先级比or的优先级高
select 字段 from 表 where 字段1 条件值1 and 字段2 条件值2;
select 字段 from 表 where 字段1 条件值1 or 字段2 条件值2;
# 2.非 not !
select 字段 from 表 where not 字段1 条件值1;
# 3.异或 XOR两边条件结果不一致返回 1
select 字段 from 表 where 字段1 条件值1 XOR 字段2 条件2;distinct 去重使用 distinct 关键字去除查询的重复数据。 # 按照指定字段进行去重后面有多个字段按照多个字段进行去重
select distinct 字段1 from 表;注意distinct 只能使用在 select 的后面字段的前面。如果 distinct 后面有多个字段则是多个字段的联合然后去除重复的数据。
# 不能写成这样
select 字段1, distinct 字段2 from 表名;算数运算符查询字段、过滤条件时可以使用基本数学表达式进行操作。在 SQL 中和 NULL 进行运算结果都是 NULL。 # 最基本的数学四则运算、-、*、/、%在 SQL 语句中 符号没有连接作用只有加法作用
SELECT (sex 10) as sex, (money * 10) as money, (id/2) as id FROM t_user排序查询在查询数据时可以按照某个字段查询后的数据进行排序不指定排序默认查询出来就是在数据库添加的顺序。 # 默认是升序排序 asc降序排序在后面添加desc
select 字段 from 表 order by 字段 asc/desc;# 可以按照多个字段进行排序当某个字段相等时这时可以按照另一个字段进行排序前面的字段起主导作用。
select 字段 from 表 order by 字段1 asc, 字段2 desc;# 选择表中对应位置字段进行排序。
# 按照表中排第二的字段进行排序但是在实际开发中不建议使用该方法可能表中的字段位置可能发生变化
select 字段 from 表 order by 2;模糊查询使用 like 去匹配模糊查询结果。 %匹配多个任意字符例如%三表示以三结尾的数据。_匹配任意一个字符例如_三表示任意一个字符开头三结尾的数据数据只有两个字符。
# 左模糊查询
select 字段 from 表 where 字段 like %张;
# 右模糊查询
select 字段 from 表 where 字段 like 张%;# 正则表达式条件查询使用 REGEXP或者RLIKE关键字
select 字段 from 表 where 字段 REGEXP | RLIKE 正则表达式;注意在查询时遇到特殊字符例如 _ 和 % 需要使用转义字符来转义select 字段名 from 表名 where 字段名 like %\_%; limit 分页用于限制查询出来数据的条数一般用于分页查询。limit 后面需要指定数据开始的下标和查询的长度它的下标是从0 开始的。 # 查询下标开始后面指定长度的数据条数
select 字段 from 表名 limit 下标,长度;分页查询
实现分页查询一般是从前端传入一个页码和每一页的数据条数然后执行相应的sql语句。limit 下标公式下标 (页码 - 1) * 每页条数每页条数一般就是作为长度。
1.2、函数查询 MySQL 提供了许多内置函数涵盖了各种数据类型和操作。这些函数可以用于处理字符串、数字、日期、时间等各种数据。 单行函数一条数据输入对应一条数据输出。聚合函数输入一组数据最终输出一行数据多组数据有多条结果相当于数据的统计。
1.2.1、单行函数 数值函数用于计算的一些函数。 函数名说明ABS(x)求 x 的绝对值SIGN(x)返回 x 的符号正数返回1负数返回-10返回0PI()圆周率CEIL(x)向上取整FLOOR(x)向下取整LEAST(x1,x2,…)求列表中最小值GREATEST(x1,x2…)求列表中最大值MOD(x,y)求x%yRAND()产生0-1之间的伪随机数多次调用可能相同ROUND(x)、ROUND(x,y)对x四舍五入y指定保留小数位数TRUNCATE(x,y)截取x为y位小数SQRT(x)求x的开方x为负值时返回NULLPOW(x,y)求x的y次方EXP(x)求e的x次方BIN(x)返回x的二进制HEX(x)返回x的十六进制OCT(x)返回x的八进制CONV(x,y,z)返回用y进制表示的x的z进制数 字符串函数操作字符串的一些内置函数。 函数名说明CHAR_LENGTH(x)返回x的字符长度LENGTH(x)返回x字符串占用的字节数例如一个汉字占用3个字节CONCAT(x1,x2,x3…)连接所有字符串CONCAT_WS(s,x1,x2,x3…)以 s 为分隔符连接字符串INSERT(str1, index, len, str2)将 str1 字符串从index下标开始长度len的字符串替换成str2REPLACE(str1, a, b)将 str1 中的所有 a 替换换成 bLOWER(x)将 x 全部转换成小写UPPER(x)将 x 全部转换成大写LEFT(x, len)取出 x 左边 len 个字符RIGHT(x, len)取出 x 右边 len 个字符TRIM(x)去掉 x 前后空格SUBSTR(str, index, len)截取 str 字符串从 index 开始截取 len 长度REVERSE(s)反转字符串 获取日期函数 函数名说明CURDATE()获取当前日期年月日CURTIME()获取当前时间时分秒NOW()、SYSDATE()获取当前时间年月日时分秒UNIX_TIMESTAMP()获取当前时间的时间戳UNIX_TIMESTAMP( date )获取指定时间的时间戳FROM_UNIXTIME( v )获取指定时间戳对应的时间EXTRACT(type FROM date)返回日期 type 指定部分可以是年月日等等TIME_TO_SEC(date)时间转换秒数SEC_TO_TIME( sec )秒数转换成时间DATE_ADD(date, INTERVAL value type)在 date 日期在 type 指定的部分加上 valuetype可以是年月日时分秒DATE_SUB(date, INTERVAL value type)在 date 日期在 type 指定的部分减上 valuetype可以是年月日时分秒 日期格式化与解析 MySQL 中日期格式默认格式%Y-%m-%d%Y表示年%m表示月%d表示天%H表示时%i表示分%s表示秒
# 字符串转换成date日期str_to_date
insert into 表名(日期字段)
values (str_to_date(01-11-1999,%d-%m-%Y));# 日期按照指定格式转换成字符串date_format
select date_format(字段,日期格式) from 表;流程控制函数 加密解密函数需要匹配加密后的内容直接将用户输入内容进行加密然后再进行匹配。 MySQL 信息函数 1.2.2、聚合函数 聚合函数是作用于一组数据并且对一组数据返回一个值聚合函数在使用时需要进行分组然后才能使用如果没有分组默认整张表就是一组。 # 常见聚合函数# 1.求平均值针对数字avg
select avg(字段) as 别名 from 表;
# 2.求和针对数字sum
select sum(字段) as 别名 from 表;
# 3.最大值/最小值max/min
select max(字段)/min(字段) as 别名 from 表;# 4.指定字段在查询结果中的个数count
select count(字段) as 别名 from 表;count() 函数
count(字段)统计这个字段不为 NULL 的数据条数。count(*)、count(1)统计查询结果的所有条数。
注意事项
聚合函数自动忽略 null 值得数据记录不需要对 null 的数据处理。聚合函数不能使用在 where 语句中。因为聚合函数使用前必须先进行分组但是 where 语句得执行顺序比分组 group by 先执行。所有得聚合函数不能嵌套使用。 分组查询将数据按照一定字段进行分组然后对每组数据进行处理。 分组函数后的 select 只能查询到参与分组的字段、聚合函数所取到的值。如果存在其它字段在其它数据库中会出错。分组函数可以对多个字段进行分组查询多个字段使用英文逗号隔开。
WITH ROLLUP它会将分组后的结果在当成一个分组然后进行操作相当于是多一条当前分组后的操作数据但是不能和 ORDER BY 一起使用因为最后一条数据是分组后数据的操作数据再进行分组就会矛盾。
HAVING 关键字 用于对分完组的数据进行筛选与 group by 联合使用不能单独使用也不能代替 where。能用 where 就用wherewhere 不行的再使用 having。
select 字段 from 表 where 条件 group by 字段 having 条件;1.3、复杂查询
1.3.1、连接查询 连接查询两张表或多张表联合起来查询数据称为连接查询。 内连接两张表之间没有主次之分没有匹配就不显示。 等值连接表与表连接的条件时某个字段的值相等。非等值连接表与表连接的条件时某个字段的值不相等。自连接一张表自己与自己关联 查询。 外连接 左外连接左边表为主表显示左表所有数据右表没有匹配的查询字段显示为 NULL。右外连接右边表为主表显示中的所有数据没有匹配的也会显示。 全连接两张表都为主表两张表的所有数据都会展示出来不匹配的字段显示为 NULL。 笛卡尔积现象当两张或多张表进行连接查询时没有任何的条件限制最终的查询结果条数是两张表条数的乘积这个现象称为笛卡尔积现象。例如select 字段 from 表1,表2; — 查询的数据就是表1数据的条数乘以表2数据的条数。 如何避免笛卡尔积 在进行多表联查的时候对查询的条件进行加限制。
# 虽然避免了笛卡尔积现象但是表之间的比配次数还是没有减少就是表1和表2数据记录数的乘积将每张表的关联关系作为条件
# 不加连接条件默认是 CROSS JOIN 表之间全部匹配就会出现笛卡尔积现象
select 字段 from 表1,表2 where 表1.字段 表2.字段;注意表的连接次数越多查询的效率越低尽量避免表之间的连接。 内连接 # inner 可省略默认是内连接
select 字段 from 表1
inner join 表2
on 连接的表达式 where 条件# 连接的表达式是一个值相等关系就是等值连接如果是一个不相等关系就是一个非等值连接。外连接 # outer可省略
select 字段 from 表1
left/right/full outer join 表2
on 条件表达式 where 条件 # 左外连接和右外连接是可以相互转换的只是表的位置不同更具需求来写sql语句。union 合并查询将两条 SQL 查询出来的结果合并成一个集合并且去掉重复数据。 select 。。。
union [all] # 加上all就不去掉重复数据
select 。。。# union 对应的每个sql语句的查询字段要相同自然连接会将两张表中所有 相同字段进行等值连接。 # 自动将两张表中相同字段进行等值连接但是灵活性不强
select 。。。
from 。。。
NATURAL JOIN 。。。注意 一条sql语句中内连接和外联接可以同时存在。 外连接查询的数据数一定比内连接查询的数据数多。
1.3.2、子查询 子查询指 select 语句中嵌套 select 语句被嵌套的 select 语句称为子查询子查询查询结果会被外查询使用的。 where 中子查询 在 where 的后面出现 select 子查询一般子查询的结果作为 where 的条件判断依据。
# 只有当子查询出来的结果条数大于3时主查询才会满足条件
select distinct name
from user u1
where (select count(*) from user u2 where u1.nameu2.name) 3from 中子查询 把 from 中的子查询的结果当成一张临时的表提供给主查询进行查询。
SELECT字段
FROM( SELECT 字段 FROM 表名 ) AS 临时表的别名
WHERE条件表达式注意临时表必须起别名不然无法在 where 语句中使用表。临时表中的字段也是要取别名的。
case 流程中子查询使用子查询在流程控制中作为条件。
SELECTid,name,( CASE salay_level WHEN SELECT MIN( salay ) FROM...THEN A ELSE B END CASE ) AS salay_level
FROM...
WHERE...子查询函数通过 any、all、some 函数前面使用运算符 (、等 )去匹配子查询结果。 any 匹配子查询的任意结果。all需要匹配子查询的所有结果。some与 any 用法一样匹配子查询的某一个结果。
SELECT ...
FROM ...
WHERE ... 运算符 any/all/some( 子查询)相关子查询子查询执行依赖于外部查询在子查询中表用到外部表并作为条件进行关联因此没执行一步外部查询子查询都要执行一次。 SELECT ...
FROM t1
WHERE (SELECT ...FROM t2# 子查询查询条件依赖于外部传入WHERE t2... t1...
)EXISTS 和 NOT EXISTS子查询中的是否存在记录数据。 EXISTS如果在子查询中存在了满足的一条记录就返回 true 就不在继续查询如果不存在继续查询。NOT EXISTS相反如果不存在就返回 true。
SELECT ...
FROM ...
WHERE EXISTS/NOt EXISTS (子查询结果)1.4、SQL 语句 执行顺序 在 SQL 语句中有自己的执行顺序理解执行顺序才能写出正确的 SQL 语句。 # 标准 SQL 执行语句
SELECT
DISTINCT # 去重
.... # 查询字段
FROM .... # 查询表
JOIN .... ON ... # 连接表
WHERE ... # 不含有聚合函数的过滤条件
GROUP BY ... # 分组
HAVING ... # 含有聚合函数的过滤条件
ORDER BY ... # 排序
LIMIT ... # 分页执行顺序
首先执行 FROM先知道从哪个表中获取数据。然后连接查询表通过 ON 连接条件过滤掉一部分数据。再执行 WHERE过滤掉表中不符合条件的数据。再执行 GROUP BY给过滤好的数据进行分组没有指定分组默认是所有的数据为一组。再执行 HAVINGHAVING 必须和 GROUP BY 同时使用是将分组的数据进行再次过滤。再执行 SELECT 选取出满足条件的所有数据。DISTINCT去掉查询到的重复数据。再执行 ORDER BY将查询的数据按照指定字段进行排序。最后执行分页 LIMIT。
2、DDL 定义语句 DDL数据定义语言包含表的创建、修改、删除等等操作。 2.1、MySQL 数据类型 常用数据类型 VARCHAR可变长度的字符串根据实际的数据来动态分配空间节省空间最长255。char定长的字符串不管实际长度多少都会分配固定的长度来储存数据可能会导致空间的浪费最长255。int数字中的整型等同于Java中的 int。bigint数字中的长整型等同于Java中的 long。float单精度的浮点型数据可以指定数据长度整数位超过长度报错小数超过长度进行四舍五入。double双精度的浮点型数据。DECIMAL定点数据类型精度更高。BIT位类型用于存储 0、1不能超过这个值可以指定二进制存储的长度。date短日期类型日期默认格式%Y-%m-%d。datetime长日期类型格式%Y-%m-%d %h:%i:%s占用8个字节。TIMESTAMP表示带有时区的时间格式%Y-%m-%d %h:%i:%s占用4个字节但是存储范围小一些。longtext大文本存储类型可以存储 4GB 的文本数据。enum枚举类型字段只能存储列举出来的一个枚举值。SET集合类型集合元素最多 64 个可以使用集合中一个或多个元素可多选但是不能重复选择。longblob二进制大对象专门用来储存图片、声音、视频等往该类型的字段插入数据的时候通常使用IO流来完成最大存储 4GB。json能够存储 json 字符串对字符串进行自动验证。
注意当数据为固定长度的时候使用char这样的速度较快不用动态分配空间。当数据为可变长度的时候使用varchar这样可以避免空间的浪费。
2.2、DDL 常见操作 数据库命令 # 创建数据库
# 方式一默认字符集是 mysql 服务器中database的字符集
CREATE DATABASE 数据库名;
# 方式二创建数据库指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
# 方式三如果数据库不存在就创建存在就不会创建上面两种方式如果数据库存在再创建就会报错
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集;# 操作数据库
# 查看当前数据库有哪些
SHOW DATABASES;
# 切换数据库
USE 数据库名;
# 查看数据库下的表不指定数据库则是当前数据库下的表
SHOW TABLES [FROM 数据库名]# 修改数据库
# 修改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集# 删除数据库
DROP DATABASE IF EXISTS 数据库名;表操作命令 # 创建表
# 方式一最后一个字段的数据类型的后面不能有逗号否则报错。
CREATE TABLE 表(字段1 数据类型 [约束] [默认值] [CHARACTER SET 字符集],字段2 数据类型,字段3 数据类型
)ENGINEInnoDB DEFAULT CHARSETutf8; # 指定字符集和存储引擎
# 方式二将表中的数据查询出来数据和表结构都会复制到新表中
# 用于复制表时可能出现表默认值不能赋值
CREATE TABLE 新表 AS SELECT 复制的字段 FROM 旧表# 修改表
# 重命名表名
RENAME TABLE 旧表名 TO 新表名;
ALTER TABLE RENAME 旧表名 TO 新表名;# 修改表字段
# 增加字段可以指定新字段添加的位置
ALTER TABLE 表名 ADD 字段名 类型 [FIRST|BEFORE|AFTER 字段名] ;
# 修改字段一般不会修改字段类型
ALTER TABLE 表名 MODIFY 字段 [长度] [默认值];
# 重命名字段
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 [类型|长度|默认值];
# 删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;# 删除表
DROP TABLE IF EXISTS 表名;
truncate table 表名;
# 清空表
TRUNCATE TABLE 表名;
DELETE FROM 表名;DELETE删除表中的数据如果不添加条件限制则是删除表中所有数据这种删除会把删除日志记录支持事务回滚操作。TRUNCATE用于清空表中的所有数据会保留表的结构、属性、索引等truncate 会重置自增列的值。执行后立即生效数据无法回滚删除效率高。DROP删除表的数据和结构将表所占用的空间全释放掉无法进行事务回滚操作。
3、DML 操作语句 INSERT用于向 MySQL 数据库中添加数据。 # 新增单挑数据
INSERT INTO 表 (字段1,字段2,字段3) VALUES (值1,值2,值3);# 批量添加数据
INSERT INTO 表 (字段)
VALUES(一条数据),(两条数据),(三条数据);# 将查询结果作为数据添加到表中添加数据字段与查询字段需要保持一致不然会报错
INSERT INTO 表 SELECT 字段 FROM 查询表;注意
字段的顺序可以和表中的字段顺序不一样但是值的顺序要和前面指定的字段顺序对应。如果缺少一些表中得字段那些缺少得字段则会为默认值如果没有设置默认值则值为 NULL。如果所有得字段都不写默认是插入全部得字段后面得值要全部填上。 UPDATE通过 SQL 语句去修改表中指定的数据。 # 注意在修改时没有条件来限制的的话它会修改表中的全部数据。
UPDATE 表 SET 字段1值1,字段2值2 WHERE 条件表达式;DELETE通过 SQL 语句删除表中的数据。 # 注意如果没有条件表达式则会删除表中的所有数据可以用于清空表中的数据。
DELETE FROM 表 WHERE 条件表达式;# 连接删除
# 可以删除两个表中条件满足的数据
DELETE m,u
FROM 表1 JOIN 表2 ON 连接条件
WHERE ... 注意
只是删除表中的数据表的结构没有删除例如索引、约束等可以重置自增列的值。支持事务回滚删除后可以后悔但是效率低下。 MySQL8.0 新特性计算列在创建表时可以指定字段为计算列当表中其他列数据发生变化可以重新计算出新值。 # 创建计算列字段c为计算列会根据a、b字段的变化而变化
CREATE TABLE IF NOT EXISTS 表名(a int,b int,c int GENERATED ALWAYS AS (a b) VIRTUAL
);4、数据约束constraint 数据约束创建表时可以给字段添加一些约束在插入数据时必须满足这些约束来保证数据的完整性。 非空约束NOT NULL插入数据字段值不能为空如果不设置非空约束默认数据库为空是 NULL。唯一性约束UNIQUE字段的值不能重复但是可以为 NULL多个 NULL可以重复可以设置多个字段组合唯一性约束。添加唯一性约束会自动创建唯一索引删除唯一性约束只能通过删除唯一性索引去删除。主键约束PRIMARY KEY数据不能为空并且所有数据必须唯一任何一张表都必须要有一个主键相当于数 非空约束 唯一性约束可以设置多个字段为复位主键约束。自增约束当字段是整型时添加自增约束插入数据时不设置该字段值默认会递增方式填充。 一个表只有一个自增长列。自增长列必须是主键列唯一并且不能为空。当插入数据的自增字段值为 0 或者 NULL实际上会自动添加成最大自增值。 CHECK 约束对数据进行校验约束MySQL5.7 之前不支持 CHECK 约束但是 MySQL8.0支持约束。DEFAULT 约束在插入数据时没有显示赋值设置了默认值就用默认值填充。
# 创建表时添加对应的约束
CREATE TABLE IF NOT EXISTS 表(# 列级约束字段1 数据类型 NOT NULL, # 非空约束字段2 数据类型 UNIQUE, # 一个字段的唯一性约束字段3 数据类型 PRIMARY KEY, # 主键约束字段4 数据类型 AUTO_INCREMENT, # 自增约束字段5 数据类型 CHECK(校验条件boolean值), # CHECK 约束字段6 数据类型 DEFAULT 默认值 # 默认值约束# 表级约束 UNIQUE(字段1,字段2), # 多个字段的联合性唯一PRIMARY KEY(字段1,字段3) # 多个字段的联合主键约束
);# 对于已存在表修改字段约束
ALTER TABLE 表名 MODIFY 字段 类型 约束;
# 删除表索引
ALTER TABLE 表名 DROP INDEX 索引名
# 删除表主键约束
ALTER TABLE 表名 DROP PRIMARY KEY外键约束FOREIGN KEY两个表之间子表引用父表中指定字段的数据只能使用这些数据不能使用其它的数据。 create table if not exists t_class(c_no int(11) primary key, // 被引用的字段c_name varchar(255)
);create table if not exists t_student(stu_no int(11) primary key,stu_name varchar(255),c_no int, // 被设置外键的字段foreign key (c_no) references t_class(c_no) // 这是设置外键的语法
);# 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名 两个表t_class(父表)是班级表t_student(子表)是学生表子表中创建一个外键 c_no引用父表中的 c_no 字段。在子表中这个字段值只能是父表中对应字段的值。
注意
创建表时要先创建父表然后创建子表。删除表时要先删除子表然后删除父表。删除数据时要删除子表中的数据然后才能删除父表中的数据。插入数据时要插入父表中的数据然后才能在子表中插入数据。外键的值可以为空但父表中字段值必须是唯一的。
5、视图 View 视图一个或多个数据表里的数据的逻辑显示视图并不存储数据。 视图是一种虚拟表不存储数据占用空间小。视图的创建、删除只会影响本身不会影响基表 (创建视图的表)对视图中数据的增加、删除、修改会对基表也做修改基表的数据发生变化视图中的数据也会变化。视图可以看成存储起来的 SELECT 语句对于复杂的 SELECT 语句可以用视图进行代替来减少重复 SQL 的编写。创建视图后可以将视图看做表进行操作简便开发中编写重复 SQL。
# 创建视图SELECT 语句就是查询对应的表数据创建的视图字段名和 SELECT 语句查询出来的字段名一样
CREATE VIEW 视图名 AS SELECT ...# 创建视图也可以自定义视图的字段名称名称与 SELECT 查询的字段需要一一对应
CREATE VIEW 视图名(字段名...) AS SELECT ...# 基于视图创建视图SELECT 查询视图中的数据
CREATE VIEW 视图名称 AS SELECT ...# 修改视图
CREATE OR REPLACE VIEW 视图名称 AS SELECT ...
ALTER VIEW 视图名称 AS SELECT ...# 删除视图删除视图不会对基表有影响但是删除视图中的数据基表中对应数据也会删除
DROP VIEW IF EXISTS 视图名称;注意
如果视图数据来之一张表可以对数据进行更新但是如果数据来之不同表或者 SELECT 语句中出现了 GROUP BY\ HAVING \UNION \UNION ALL、聚合函数等或者视图来之一张不可更新的视图那么视图的数据不能进行更新操作。如果视图是根据另一个主视图创建主视图删除了会导致从视图查询失败。
6、存储过程与函数 存储过程一组经过预编译的 SQL 语句的封装存储过程存储在 MySQL 服务器上需要执行的时候客户端发出调用命令服务端就将存储过程执行一次。 简化操作提高 SQL 语句的重用性。减少网络的传输提高性能。
# 存储过程的基本语法
/*
1.括号中无参数
2.IN 类型参数有输入值
3.OUT 类型参数有返回值
4.INOUT 类型参数既是输入值也是返回值
*/
# 若参数不写 IN或者OUT默认是IN类型参数
# DELIMITER $表示是结束标识符
DELIMITER $
CREATE PROCEDURE 存储过程名称 (IN|OUT|INOUT 参数名称 参数类型)
BEGIN存储过程体;
END $
DELIMITER ;# 调用存储过程
CALL 存储过程名称(参数);# OUT 类型的存储过程
DELIMITER $
CREATE PROCEDURE test1 (OUT param 参数类型)
BEGIN# 将查询出来结果写入返回值中SELECT 字段 INTO param ...
END $
DELIMITER ;# 调用存储函数
CALL test1(param);存储函数用户自定义函数使用函数可以对数据进行各种操作定义完成后可以像调用 MySQL 内置函数一样调用。 # 存储函数的所有参数都是 IN 类型参数
CREATE FUNCTION 函数名(参数名 参数类型 ...)
RETURN 返回值类型
BEGIN# 函数体中一定有return返回值return 函数体....
END# 调用函数
SELECT 函数名();查看、修改、删除存储过程和函数 # 查看
SHOW CREATE [PROCEDURE | FUNCTION] 存储过程或函数名# 修改
ALTER [PROCEDURE | FUNCTION] 存储过程或函数名 具体存储过程或函数# 删除
DROP [PROCEDURE | FUNCTION] IF EXISTS 存储过程或函数名7、MySQL 变量 变量可以使用变量来存储查询计算的结果在 MySQL 中分为系统变量和用户自定义变量。 7.1、系统变量
全局系统变量需要添加 global 关键字全局变量针对的是所有会话都共有的变量重启 MySQL 服务会导致全局系统变量失效。会话系统变量需要添加 session 关键字会话系统变量针对的是某一次客户端连接会话会话与会话之间相互不干扰。
# 查看所有系统变量
SHOW [GLOBAL|SESSION] VARIABLES;
SHOW VARIABLES; # 默认查询会话系统变量# 查看部分系统变量
SHOW [GLOBAL|SESSION] VARIABLES LIKE 模糊匹配变量名;# 查看指定系统MySQL 中使用 表示系统变量例如global 表示全局系统变量
SELECT global.变量名;
SELECT session.变量名;
SELECT 变量名; # 默认先查询会话系统变量再查询全局系统变量操作系统变量系统变量一般是初始化 MySQL 服务时从配置文件读取或者系统初始化加载的重启 MySQL 服务对于后期设置的系统变量会失效。 # 设置系统变量值
SET [global|session].变量名 变量值;SET GLOBAL 变量名 变量值;
SET SESSION 变量名 变量值;7.2、用户自定义变量
会话用户变量作用于当前会话连接断开连接变量失效用户变量以 符号开头。局部变量只在 BEGIN 和 END 语句中有效局部变量只能在存储过程和函数中使用。
# 定义会话用户变量
# 方式一
SET 变量名 值;
SET 变量名 : 值;
# 方式二
SELECT 变量名 : 表达式 FROM ...
SELECT 表达式 INTO 变量名 FROM ...# 例如
SET a 1;
SET b 2;
SET c a b;
SELECT count : COUNT(*) FROM 表
SELECT COUNT(*) INTO count FROM 表# 定义局部变量
BEGIN# 定义局部变量DECLARE 变量名 类型 default 默认值;# 赋值和用户变量赋值类似SET 变量名 值;
END8、触发器 触发器由某个时间触发某个操作例如发生插入、更新、删除等操作时触发器就会自动触发执行定义的相关操作。 # 创建触发器
DELIMITER $
CREATE TRIGGER 触发器名称
# 设置触发时机以及触发事件在之前还是之后
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表 # 设置触发器作用的表
FOR EACH ROW
BEGIN 触发执行的语句块;
END $
DELIMITER ;
# 创建好触发器后发生对应的事件就会自动执行定义的 SQL 语句# 触发器在执行时可以获取到触发事件的数据
DELIMITER $
CREATE TRIGGER test
BEFORE INSERT ON table
FOR EACH ROW
BEGIN SELECT ... FROM .... NEW # 表示添加时对应的数据。
END $
DELIMITER ;# 查看触发器
SHOW TRIGGERS 触发器名称;
# 删除触发器
DROP TRIGGER 触发器名称;9、MySQL8.0 新特性
9.1、窗口函数 序号函数ROW_NUMBER类似于分组函数它不会将一组的数据放在一起而是一组的数据都是占用一行并且携带序号可以根据某个字段进行排序每一组都有自己组的序号。 # PARTITION BY: 按照字段进行分组
SELECT ROW_NUMBER() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表;RANK 函数对序号进行并列排序并且会跳过重复的序号相同成绩并列下一个成绩跳一个序号。例如1、1、3、4 SELECT RANK() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表; DENSE_RANK() 函数对序号进行并列排序不会跳过重复的序号例如1、1、2 SELECT DENSE_RANK() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表; 前后函数LAG(expre, n)返回当前行的前 n 行的 expre 的值。 # 按照某个字段进行分组返回组内这条记录前n行的column1的值
SELECT 字段..., LAAG(column1, n) OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS t
FROM ...LEAD(expr, n)函数返回当前行的后 n 行对应的字段数据。 FIRST_VALUE(exp)返回组内中第一个 exp 的值。 # 按照某个字段进行分组返回组内这条记录前n行的column1的值
SELECT 字段..., FIRST_VALUE(exp) OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS t
FROM ...LAST_VALUE(exp)返回组内中最后一个 exp 的值。NTH_VALUE(exp, n)返回组内中第 n 个exp 对应的值。
9.2、公共表表达式 CTE一个可以复用的子查询查询出来的结果看成一个临时表分为普通公共表表达式和递归公共表表达式。 # 普通公共表表达式
WITH 表达式名称 AS 子查询# 使用公共表表达式把它看成一个临时表
SELECT 字段
FROM ... JOIN 表达式名称 ON 关联条件# 递归公共表表达式
WITH RECURSIVE 表达式名称 AS 子查询SE_RANK() 函数对序号进行并列排序不会跳过重复的序号例如1、1、2
SELECT DENSE_RANK() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表;
