住建部城乡建设网站,防伪码做网站的还能没导入吗,企业网站栏目设置说明,免费追剧的app下载一SQL 介绍SQL#xff08;Structured Query Language#xff09;,语义是结构化语言#xff0c; 是一门 ANSI 的标准计算机语言#xff0c;用来访问和操作数据库系统#xff1b;二 数据库介绍2.1 数据库我们已经知道了SQL是操作数据库的语言#xff0c;那么数据库是什么Structured Query Language,语义是结构化语言 是一门 ANSI 的标准计算机语言用来访问和操作数据库系统二 数据库介绍2.1 数据库我们已经知道了SQL是操作数据库的语言那么数据库是什么数据库database是 保存一个文件或者一组文件的容器这样说你可能还不懂可以比喻为数据是一个图书馆图书馆里面收藏有好多书我们可以认为数据库就是能够存储数据的容器其实数据库很常见你每天都在操作数据库比如你看手机上网微信聊天其实背后都在操作数据库只是展现操作数据库的形式不一样而已2.2 数据库管理系统数据库里面有好多的数据就像图书管一样有好多的书书是由图书管理员进行管理那么什么东西能够管理数据库呢没错就是数据库管理系统Database Manage System比如 mysql, Access、DB2、Informix、 Server、Oracle、Sybase以及其他数据库系统2.3 表表是一种结构化的文件可以用来存储特定类型的数据这么说你可能没听懂我们再举个例子表就像图书馆里面的图书架子每个书架都放了好多书再不懂也没关系你总用过excel吧excel表格里面的标签页 sheet 我们就可类比数据表表存放的就是特定类型的数据跟excel的sheet一样重点是每个表的表名是唯一不可重复2.4 列和数据类型列是表中的一个字段一个表由多个列组成每个列都由特定的数据类型只能存放指定数据类型的数据听不懂没关系我们类比excelexcel中的sheet也是由多个列组成每个列都有指定的数据类型比如文本数字日期类型那数据库的表跟excel的结果是类似的数据类型就是限定表中的每个列只能存储特定类型的数据常见的数据类型有整型数字文本字符串日期等等。2.5 行行就是表中的一条记录类比excel很容易理解2.6 主键主键就是每行的唯一标识其特性是主键不能为空不能重复不能修改主键我们可以类比身份证号每个身份证号都是唯一不可重复的2.7 行号行号指表中每个行的行号三 基本检索如果是初学者建议去网上寻找安装Mysql的文章安装以及使用navicat连接数据库以后的示例基本是使用mysql数据库管理系统需要建立一张学生表列分别是id名称年龄学生信息本示例中文章篇幅原因SQL注释略建表语句CREATE TABLE student (id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) DEFAULT NULL,age int(11) DEFAULT NULL,info varchar(255) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;
表中的数据INSERT INTO springboot.student(id, name, age, info) VALUES (1, youku1, 18, 大一新生);
INSERT INTO springboot.student(id, name, age, info) VALUES (2, youku2, 23, 毕业生);
INSERT INTO springboot.student(id, name, age, info) VALUES (3, jeff, 25, 社会人士);
INSERT INTO springboot.student(id, name, age, info) VALUES (4, smile, 17, 高三学子);3.1 select 关键字sql 都是由许多关键字keyword组成的语句关键字是数据库的保留字用户不能将其当作建表的表名字段等表中的数据检索使用select关键字作为开头进行查询数据库表的信息3.2 检索单个字段语句示例SELECT name FROM student
查询结果youku1
youku2
jeff
smile
语句分析select 是 查询 的意思 name 代表表中的字段 from 表示来自哪张表其后面的student就是表连起来的意思就是查询字段是name的数据来自表student3.3 sql语句注意点多sql语句一起执行使用封号;隔开在拼写语句时表的关键字都建议使用大写表的字段和表名使用小写为了容易阅读建议将sql分成多行由于文章篇幅原因就不分了sql语言也是使用英文字母不要开中文以免造成符号错误不容易发现sql语句默认不区分大小写3.4 检索多个字段语句示例SELECT name,age FROM student;
语句结果youku1 18
youku2 23
jeff 25
smile 17
语句分析查询字段 名字年龄来自表student多个列检索时使用逗号,隔开3.5 检索所有字段语句示例SELECT * FROM student;
语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
4 smile 17 高三学子
语句分析通配符 *表示返回表中的所有列如果不是必须建议不要使用通配符会影响数据库性能3.6 distinctdistinct 表示区分意指检索出来的行是唯一去重其放在列的最前面如果使用了关键字distinct其作用于后面的所有列由于本示例数据没有重复示例结果就不是很清晰SELECT DISTINCT name, age FROM student;
语句结果youku1 18
youku2 23
jeff 25
smile 17
3.7 限制条数access 和 sql server SELECT TOP 2 name FROM student
TOP 2 表示限制返回前2行postgresql 、SQLite和 mysqlSELECT name FROM student LIMIT 2;
limit 2 表示限制返回前2行DB2SELECT name FROM student FETCH FIRST 2 ROWS ONLY
FETCH FIRST 2 ROWS ONLY 表示只抓取前2行数据语句结果youku1
youku2
3.8偏移示例SELECT name FROM student LIMIT 1 OFFSET 1;
语句分析表示查询列名称来自学生表 限制条数1偏移值1意思就是查询学生表中的第二行数据offset表示跳跃或者偏移mysql和MariaDB简化形式:SELECT name FROM student LIMIT 1,2;
表示查询字段名称来自学生表限制2条偏移1条注意顺序语句结果youku2
jeff
四 排序检索4.1 ORDER BY 子句示例SELECT name,age FROM student ORDER BY age
语句分析检索字段名称年龄来自学生表按照列年龄排序注意默认是升序ORDER BY 子句通常在语句末尾语句结果smile 17
youku1 18
youku2 23
jeff 25
4.2 多列排序示例SELECT name,age FROM student ORDER BY age DESC, name ASC;
语句分析查询名称年龄来自学生表按照年龄降序名称升序进行排序关键字 DESC(descending) 意指降序字母默认Z-A; ASCascending意指升序字母默认A-Z多列情况下每个列后面指定使用DESC使用逗号,隔开如果不写默认升序语句结果jeff 25
youku2 23
youku1 18
smile 17
4.3 按位排序语句示例SELECT name,age FROM student ORDER BY 2 DESC, 1 ASC;按位指查询字段的位置2 对应字段age,1对应字段name结果和4.2一致五 过滤检索sql 语句中过滤条件filter condition的关键字是 WHERE其使用方式是跟在表名之后5.1 WHERE语句操作符根据不同的数据库管理系统其支持的操作符略有不同所以下文较多重复意思的操作符应查阅官方文档说明哪些操作符是支持使用的数据库管理系统5.2 单条件过滤示例SELECT * FROM student WHERE name jeff;语句分析;查询所有字段来自学生表 条件是 学生名称是 jeff注意 非表字段表名关键字使用2个单引号括起来里面存放的是我们输入的数据语句结果;3 jeff 25 社会人士
5.3 多条件过滤多条件过滤时使用 AND 或者 OR 子句AND连接表达式表示过滤条件都为真的数据OR连接表达式表示匹配过滤条件任意一个AND示例SELECT * FROM student WHERE age 18 AND age 23;语句分析查询所有字段来自学生表条件是 学生年龄大于等于18 并且 学生年龄小于 23语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
OR示例SELECT * FROM student WHERE age 18 OR age 23;语句分析检索所有字段来自学生表条件是 学生年龄大于等于18或者学生年龄小于23OR 和 AND 示例SELECT * FROM student WHERE age 18 AND (age 23 OR id 2);语句分析在使用OR 和 AND 的时候应该明确过滤条件然后使用小括号括起来由于数据库管理系统是根据顺序执行如果不使用小括号很容易造成语义错误查询所有字段来自学生表 过滤条件 年龄大于 18 并且 年龄小于23 或者 id 大于2的数据5.4 范围查询示例SELECT * FROM student WHERE age BETWEEN 18 And 23;语句分析BETWEEN 表示范围查询查询所有字段来自学生表过滤条件学生年龄在18至23之间语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
5.5 空值查询示例SELECT * FROM student WHERE age IS NULL;语句分析查询所有字段来自学生表过滤条件 学生年龄 为空数据库表不填充数据默认为空NULL当然你也可以给指定类型的列设置默认值5.6 IN 操作示例SELECT * FROM student WHERE age IN (18,23,25);语句分析查询所有字段来自学生表 过滤条件 年龄 是 18 或者 23 或者 25 IN是个范围查询匹配小括号中指定的任意值其功能跟OR类似一个IN 就相当于好多个OR语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
5.7 NOT 操作符示例SELECT * FROM student WHERE NOT age25;语句分析NOT 操作符表是否定其跟在WHERE后面功能类似;六 通配符检索6.1 通配符的介绍通配符是组成匹配模式的特殊字符串如果有用过正则表达式的同学肯定不陌生这个与其类似检索文本的通配符是用在关键词Like 后面6.2 通配符 %示例SELECT * FROM student WHERE name LIKE you% ;语句分析查询所有字段来自学生表过滤条件 名字匹配以you开头的字符串后面匹配任意个任意字符%通配符代表任意个任意字符串,0也算在内但不包括null语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
示例SELECT * FROM student WHERE name LIKE %i% ;语句分析查询所有列来自学生表过滤条件学生的名称匹配中间必须出现一次字母i字母i的前后可以匹配任意个任意字符串语句结果;4 smile 17 高三学子
6.3 通配符 _通配符 _ 代表匹配一个字符串在Access数据库中不是 _ 而是 ?示例SELECT * FROM student WHERE name LIKE youku_ ;语句分析查询所有列来自学生表过滤条件 学生名称 匹配模式 youku 后面一个为任意字符串语句结果;1 youku1 18 大一新生
2 youku2 23 毕业生
6.4 通配符 []通配符 [] 表示匹配指定一个位置一个字符其里面可以存放多个字符关系是or模式匹配时只占用一个位置Access,SQL SERVER 支持七 字段基本操作7.1 字段拼接示例SELECT concat(你好啊,name,327今天心情怎么样) FROM student WHERE id 1 ;语句分析函数 concat 就是将多个字符子串拼接成一个字符串不同的数据库管理系统其使用的方式略有差别应查阅官方文档在 mysql 中使用 concat函数在postgresql中使用 || 在 Access和 sql server中使用 ;语句结果你好啊youku1327今天心情怎么样
7.2 去除空白字符串语句示例SELECT RTRIM( 哥今天管饱 ) FROM student WHERE id 1 ;语句分析RTRIM(STR) 函数是去掉右边的字符串TRIM(STR)是去掉字符串两边的空白字符LTRIM(STR)是去掉字符串左边的空白字符语句结果; 哥今天管饱
7.3 别名语句示例SELECT name as student_name FROM student WHERE id 1 ;语句分析别名alias是可以给字段或者表起别名当多表操作出现重复字段时使用别名是个很好的选择别名可以使用AS关键字虽然其是可省略但平时我们最好加上它增强阅读性7.4 计算操作符说明*乘加-减/除语句示例SELECT 2 * 8; 语句结果16
# 八 聚集函数的使用先声明一下下面的库表只是简易的学习示例不是生产的设计不要深究此文我们的目的是学习sql的检索不是库表设计初学者最好跟着作者的文章一步一步敲一遍如果没有使用过sql的可以查阅作者SQL系列专栏顾客表CREATE TABLE customer (userId int(11) NOT NULL AUTO_INCREMENT COMMENT 顾客id,userName varchar(255) DEFAULT NULL COMMENT 顾客名称,telephone varchar(255) DEFAULT NULL COMMENT 顾客电话,PRIMARY KEY (userId)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;INSERT INTO springboot.customer(userId, userName, telephone) VALUES (1, zxzxz, 1327);
INSERT INTO springboot.customer(userId, userName, telephone) VALUES (2, youku1327, 1996);商品表CREATE TABLE product (productId int(11) NOT NULL AUTO_INCREMENT COMMENT 产品id,productName varchar(255) DEFAULT NULL COMMENT 产品名称,price varchar(255) DEFAULT NULL COMMENT 产品价格,PRIMARY KEY (productId)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8;INSERT INTO springboot.product(productId, productName, price) VALUES (1, 苹果, 5);
INSERT INTO springboot.product(productId, productName, price) VALUES (2, 梨, 4);
INSERT INTO springboot.product(productId, productName, price) VALUES (3, 香蕉, 3);订单表CREATE TABLE order (id int(11) NOT NULL AUTO_INCREMENT COMMENT id,userId int(11) DEFAULT NULL COMMENT 客户id,productId int(11) DEFAULT NULL COMMENT 产品id,orderName varchar(255) DEFAULT NULL COMMENT 订单名称,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;INSERT INTO springboot.order(id, userId, productId, orderName) VALUES (1, 1, 1, 乖乖订单);
INSERT INTO springboot.order(id, userId, productId, orderName) VALUES (2, 2, 2, 悦悦订单);
INSERT INTO springboot.order(id, userId, productId, orderName) VALUES (3, 1, 3, 香香订单);聚集函数的定义就是讲一些行的数据运行某些函数返回一个期望值下面讲述的是开发中经常使用到的聚集函数8.1 avg()avg函数也就是计算行的数量通过计算这些行的特定列值和计算出平均值(特定列值之和/行数平均值)使用时注意其会忽略列值为NULL的行语句示例SELECT AVG(price) FROM product;语句结果4
语句分析查询价格平均值来自商品表543/348.2 count()count函数用于计算行数其中count(*)计算所有行的数目count(column)会忽略column为NULL的行数语句示例SELECT count(*) FROM product;语句结果3
语句分析查询总行数来自商品表8.3 max()max函数返回特定列值的最大值忽略特定列为NULL的行语句示例SELECT max(price) FROM product;语句结果5
语句分析查询价格的最大值来自商品表8.4 min()返回特定列的最小值忽略特定列为NULL的行语句示例SELECT min(price) FROM product;语句结果3
语句分析查询价格的最小值来自商品表8.5 sum()返回特定列的和忽略特定列为NULL的行语句示例SELECT sum(price) FROM product;语句结果12
语句分析查询价格的总和来自商品表九 分组数据分组定义就是按照特定的列进行分组查询使用 GROUP BY 子句进行分组查询注意点SELEC后面的列必须出现在group by 子句后面否则报语法错误通常 group by 子句的位置是where 条件之后order by 子句之前9.1 分组求和语句示例SELECT sum(price) FROM product GROUP BY productName;语句结果4
5
3
语句分析先根据商品名称分为三组 苹果 梨 香蕉 再根据不同的分组求和因为我们表中的数据只有这三条所以就是每行的值9.2 分组过滤语句示例SELECT count(*) FROM order GROUP BY userId HAVING count(*) 1;语句结果2
语句分析查询 条数来自 订单表 根据客户id分组过滤条件 条数大于2注意 having 与 where其实差别不大通常我们讲where当作标准的过滤条件having用作分组过滤条件注意有的数据库管理系统having不支持别名作为分组过滤条件中的一部分9.3 分组排序语句示例SELECT count(*) as count FROM order GROUP BY userId ORDER BY count;语句结果1
2
语句分析 查询 行数 来自 订单表 根据 客户id分组根据 行数排序注意点是经过分组后结果看似经过排序其实并不能确保是排序后的结果所以要排序一定要使用order by子句十 子查询子查询的定义是在查询中嵌套查询注意子查询只能返回单列若企图返回多列会报语法错误语句示例SELECTuserName
FROMcustomer
WHEREuserId ( SELECT userId FROM order WHERE orderName 乖乖订单 )
语句结果zxzxz
语句分析是执行语句 【SELECT userId FROM order WHERE orderName 乖乖订单 】得到结果 userId 1 ; 然后执行语句 【 SELECT userName FROM customer WHERE userId 1】十一 联结表联结表也就是我们通常意义上的关联表查询主要功能是能在多表中使用一条sql检索出期望值但实际库表中是存在的只在查询期间存在其主要分为内联结和外连接使用的 join 关键字联结表会返回一对多一对一多对多关系联结表不建议超过三张表以上11.1 简单联结语句示例SELECTuserName,orderName
FROMcustomer,order
WHEREcustomer.userId order.userId;
语句结果zxzxz 乖乖订单
youku1327 悦悦订单
zxzxz 香香订单
语句分析 查询 用户名来自用户表查询订单名称来自订单表根据 订单表的客户id 等于 客户表的客户id做为联结条件也就是说会查询出两张表根据userId为等值条件的 userName 和 orderName 的 数据;注意点 简单联结中where子句后面 必须 要带上 两张表的联结关系否则会出现笛卡尔集比如3行数据联结另一张表3行数据会产生3*39条11.2 内联结内连接inner join 又称等值联结其查询结果跟之前的简单联结一致语句示例SELECTuserName,orderName
FROMcustomerINNER JOIN order ON ( customer.userId order.userId );
语句结果zxzxz 乖乖订单
youku1327 悦悦订单
zxzxz 香香订单
语句分析跟之前的简单联结稍微不同的是 等值条件 是放在 on 关键字后面在等值条件后面还可以进行 where 子句过滤条件查询11.3 自然联结自然联结与标准的联结不同就是只返回值唯一的列不会返回重复的列自然联结示例SELECTuserName,orderName
FROMcustomerINNER JOIN order ON ( customer.userId order.userId );
自然联结结果zxzxz 乖乖订单
youku1327 悦悦订单
zxzxz 香香订单
非自然联结示例SELECT*
FROMcustomersqLINNER JOIN order ON ( customer.userId order.userId );
非自然联结结果1 zxzxz 1327 1 1 1 乖乖订单
2 youku1327 1996 2 2 2 悦悦订单
1 zxzxz 1327 3 1 3 香香订单
重复列是 userId;11.4 外联结右外联结语句示例SELECT*
FROMorderRIGHT OUTER JOIN customer ON ( customer.userId order.userId );
右外联结是指 相对于 OUTER JOIN 右边的表那么这会查询出右边表的所有数据 和根据等值条件匹配左边表的数据如果左边表的数据不匹配那么其返回列的值是NULL充当左外联结语句示例SELECT*
FROMcustomerLEFT OUTER JOIN order ON ( customer.userId order.userId );
左外联结是指 相对于 OUTER JOIN 左边的表那么这会查询出左边表的所有数据 和根据等值条件匹配右边表的数据如果右边表的数据不匹配那么其返回列的值是NULL充当区别左外联结和右外联结其实没什么不同只是查询表顺序不一致我们通过置换 表的相对位置就可以查询出一样的结果十二 组合查询组合查询是指可以执行多条SELECT 语句其查询的结构是一致的返回查询结果通常我们称为复合操作或者并(union)语句示例SELECTuserId
FROMcustomer UNION
SELECTuserId
FROMorder
返回结果1
2
语句分析union 关联的字段或者聚合函数在两张表中必须是相同的其默认会讲结果进行去重处理如果不去重可以使用 union all语句示例SELECTuserId
FROMcustomer UNION ALL
SELECTuserId
FROMorder
执行结果1
2
1
2
1
语句分析等同于讲客户表和订单表的用户id都合并为一个并集查询出来而且不去重如果对组合语句进行排序默认是会作用于组合后的数据字段排序而不是作用于其中的一条查询语句十三 插入插入数据库记录也就是使用 INSERT 关键字能将一条语句插入数据库高级的可以组合 SELECT 关键字 实现 插入查询的结果集插入整张表建表语句CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT COMMENT 用户id,name varchar(255) DEFAULT NULL COMMENT 用户名,telephone varchar(255) DEFAULT NULL COMMENT 用户电话,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;CREATE TABLE student (id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) DEFAULT NULL,age int(11) DEFAULT NULL,info varchar(255) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8;INSERT INTO springboot.student(id, name, age, info) VALUES (1, youku1, 18, 大一新生);
INSERT INTO springboot.student(id, name, age, info) VALUES (2, youku2, 23, 毕业生);
INSERT INTO springboot.student(id, name, age, info) VALUES (3, jeff, 25, 社会人士);
INSERT INTO springboot.student(id, name, age, info) VALUES (4, smile, 17, 高三学子);13.1 插入一条完整数据语句示例INSERT INTO user ( id, name, telephone) VALUES (2,zszxz,1327);
语句结果2 zszxz 1327
语句分析插入 数据 到 user 表 字段分别是 id, name , telephone; 值 分别是 2 , zszxz , 1327; 这是插一条完整的语句虽然INTO可以忽略不写但不建议这么做原因是在数据库管理系统间会出现移植性问题还有字段也可以忽略不写但也不建议这么做这容易造成插入数据出错字段的位置和值的位置是一 一对应如果有的位置没值可以使用NULL代替13.2 插入部分数据语句示例INSERT INTO user ( id, name) VALUES (3,zszxz);
语句结果3 zszxz
语句分析插入数据到user表字段分别是 id , name ; 值分别是3zszxz; 可以看见我们没有插入telephone字段13.3 插入检索数据插入检索的数据也就是能将查询的结果插入进另一张表我们可以使用 INSERT SELECT 关键组合成一条语句实现语句示例INSERT INTO user ( id, name)
SELECT id, name FROM student WHERE id 4;
语句结果4 smile
语句分析插入数据到 user 表 字段分别是 id, name ,值是查询字段 id ,name 来自 student 表条件是 id 等于 4可以看见我们插入数据的列根查询的列名称是匹配对应的其实只要列顺序一致即可不过为了不出错建议使用名称匹配13.4 复制表复制表即检索一张表的数据全部插入另一张表有两种方法但是不同的数据库管理系统支持不同具体的看下文语句示例SELECT id , name INTO student_copy FROM student;
语句分析查询字段 id, name 插入 student_copy 表来自 student 表注意 这条语句会帮我们自动创建表 student_copy由于作者使用的是mysql做演示这条sql执行失败原因是其不支持这种方式如果是想复制整张表可以使用通配符 * 语句示例CREATE TABLE student_copy AS
SELECT * FROM student;
语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
4 smile 17 高三学子
语句分析创建表 student_copy 数据结构来源 查询 所有字段来自 student 表十四 更新更新数据库的行使用 UPDATE 关键字更新操作是个很危险的操作在每次执行前都应该检查是否丢了 where 子句14.1 更新所有行语句示例UPDATE student_copy set age Null;
语句结果1 youku1 大一新生
2 youku2 毕业生
3 jeff 社会人士
4 smile 高三学子
语句分析更新 student_copy 表 设置 字段 age 值为null可以看见表中所有的学生年龄都是Null; 如果有多个字段需要更新使用 逗号隔开14.2 更新特定的行语句示例UPDATE student_copy set age 18 WHERE id 4;
语句结果4 smile 18 高三学子
语句分析更新 student_copy 设置 学生的年龄是 18 条件是 id 等于 414.3 更新来自查询的结果集语句示例UPDATE student_copy set age student.age, name student.name
FROM student
WHERE student.id student_copy.id;
语句分析更新 student_copy 表 设置 age 是 student 表的 agename 是 student 表的 name 条件是 student 的id 等于 student_copy 表的 id; 遗憾的是 mysql 数据库管理系统又执行失败了其不支持这种方法更新如果是postgresql就支持其他数据库应查阅官方文档查看是否支持这种方式更新语句示例UPDATE student_copy INNER JOIN student on student.id student_copy.id
SET student_copy.age student.age, student_copy.name student.name;
语句结果1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
4 smile 17 高三学子
语句分析 更新 student_copy 关联 student 条件 是 student 的 id 等于 student_copy 表的id ; 设置 student_copy 表的 age 等于 student 的 age ; 设置 student_copy 表的 name 等于 student 的 name 这才是正确进入Mysql 的更新查询姿势十五 删除表删除表中的行可以使用 DELETE 关键字 可以删除特定的行或者全部使用时请先看是否丢了where子句15.1 删除整张表数据DELETE from student_copy;
语句分析删除 全部行 来自 student_copy 表15.2 删除特定的行语句示例DELETE from student WHERE id 4;
语句分析删除 行 来自 student 表条件时 id 等于 415.3 更新和删除的建议每次进行操作前检查是否丢失 where 子句每次操作前最好先使用 select 语句验证十六 SQL 分类操作16.1 SQL 分类sql 对数据库的操作分为如下三种类型如果都学会这三种SQL语言熟练对数据库操作说明你已经对数据库登堂入室如果再学会数据库高级操作说明你对数据库就有一定的使用经验如果你还学会对数据库进行优化分表分库读写分离等操作说明你使用数据库已经到专家级别DDL数据定义语言Data Define Language即定义数据的结构。比如createdropalter操作DML数据管理语言Data Manage Language即增删改查。比如insertdeleteupdateselect。这个也在之前的文章详细写过不再提及DCL数据控制语言Data Control Language对权限、事务等的控制。比如grant授权revoke取回权限commitroolback等; 事物在上篇已经说明不会在本篇提及16.2 数据库基操数据库的基本操作如下也就是我们日常使用的操作连接数据库mysql -h 地址 -P 端口 -u 用户名 -p 密码mysql -h 192.168.0.127 -P 3306 -u root -p root查看当前数据库SELECT DATABASE();显示用户活动线程SHOW PROCESSLIST;显示系统变量SHOW VARIABLES;显示当前时间用户数据库版本号SELECT now(), user(), version();创建数据库CREATE DATABASE[ IF NOT EXISTS] 数据库名 [数据库选项]数据库选项 CHARACTER SET 字符集名称 COLLATE 排序规则名称示例create database zszxz;删除数据库DROP DATABASE [ IF EXISTS] 数据库名;drop database zszxz;16.3 建表语句数据库表的日常操作如下CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 ) [ 表选项]
其中 TEMPORARY 表示临时表中括号内容都表示可选在正规的数据库版本管理开发会经常使用到字段的修饰如下 数据类型非空|空约束[NOT NULL | NULL]默认值[DEFAULT default_value]自动增长[AUTO_INCREMENT]唯一键|主键[UNIQUE [KEY] | [PRIMARY] KEY]备注[COMMENT string]表选项一般就是指定数据库引擎和字符集ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT顾客表;
示例CREATE TABLE IF NOT EXISTS customer (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键,customer_name varchar(255) DEFAULT NULL COMMENT 顾客名称,gender varchar(255) DEFAULT NULL COMMENT 性别,telephone varchar(255) DEFAULT NULL COMMENT 电话号码,register_time timestamp NULL DEFAULT NULL COMMENT 注册时间,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT顾客表;
16.4 修改表结构查看所有表SHOW TABLES
查看指定数据库的表SHOW TABLES FROM 数据库名称;
示例SHOW TABLES FROM zszxz;
删除表DROP TABLE[ IF EXISTS] 表名;
示例: drop table op;
清空表(清除数据)TRUNCATE [TABLE] 表名
复制表结构CREATE TABLE 表名 LIKE 要复制的表名;
示例: create table op like order;
复制表结构和数据CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名;
示例: CREATE TABLE op AS SELECT * FROM order;
常见的alter操作如下增加一列(追加至末尾)alter table [数据库名.]表名 add [column] 字段 数据类型;
示例alter table order add column year year ;
增加到第一列alter table [数据库名.]表名 add [column] 字段 数据类型 first;
增加一列到指定字段名后alter table [数据库名.]表名 add [column] 字段 数据类型 after 另一个字段;
修改字段名的 数据类型alter table [数据库名.]表名称 modify [column] 字段名 新的数据类型;
示例altert table order modify column gender tinyint;
修改表字段的数据类型并且移动至第一列alter table [数据库名.]表名称 modify [column] 字段名 数据类型 first;
修改表字段的数据类型并且移动至指定字段后面alter table [数据库名.]表名称 modify [column] 字段名 数据类型 after 另一个字段名;
修改表字段的名称alter table [数据库名.]表名称 change [column] 旧字段名 新的字段名 数据类型;
添加主键alter table [数据库名.]表名称 ADD PRIMARY KEY(字段名);
示例altert table order add primary key(id)
添加唯一键alter table [数据库名.]表名称 ADD UNIQUE [索引名] (字段名)
添加索引alter table [数据库名.]表名称 ADD INDEX [索引名] (字段名)
删除一列alter table [数据库名.]表名称 drop [column] 字段名;
示例altert table order drop column gender;
删除索引alter table [数据库名.]表名称 DROP INDEX 索引名
删除主键alter table [数据库名.]表名称 DROP PRIMARY KEY
删除外键alter table [数据库名.]表名称 DROP FOREIGN KEY 外键
十七 视图17.1 视图的概念视图其实就是一张虚表其本质上SQL的检索语句所以其不储存任何的数据成分我们使用视图有什么好处呢简化查询SQL直接对视图进行查询不用管视图具体生成的细节可以使用表的部分成为视图保护数据开放用户权限时可以只开放视图而不开放实体表修改数据结构可以直接对已有的表建立视图使用不同的表名字段名称我们对视图的操作只能停留在查询上如果是单表生成的视图还可以进行插入数据如果是多表关联生成的视图插入不会起作用切记任何时候进行关联的时候如果关联3张表以上就是不符合规范严重的拖累查询性能视图也是如此使用复杂的嵌套视图和多表关联也会极大的降低查询性能17.2 视图的规范视图是虚表其有表的部分特性视图名 唯一 与表名类似如果非管理员用户创建视图必须有创建权限视图本质是查询语句故视图可以嵌套可以与其它表进行联结视图不能有索引和触发器17.3 视图语句小伙伴们要记得使用视图之前要看看自己的MYSQL版本5.0以上支持创建视图 与创建表类似 create view 语句用于创建视图显示视图创建语句 使用 show create view viewName 删除视图 使用语句 drop view viewName更新视图 使用语句 create or replace view17.4 视图操作我们的准备表如下CREATE TABLE order (id int(11) NOT NULL AUTO_INCREMENT COMMENT 订单编号,order_name varchar(255) DEFAULT NULL COMMENT 订单名称,create_time datetime DEFAULT NULL COMMENT 创建时间,year year(4) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8;
准备数据如下INSERT INTO zszxz.order(id, order_name, create_time, year) VALUES (1, 知识追寻者的订单, 2020-03-04 11:01:25, 2019);
INSERT INTO zszxz.order(id, order_name, create_time, year) VALUES (2, 小天使的订单, 2020-03-04 11:01:25, 2020);
INSERT INTO zszxz.order(id, order_name, create_time, year) VALUES (3, 流浪者的订单, 2020-03-04 11:02:50, 2020);新建简单的视图示例如下使用 order 表 的 id , order_name , year 三个字段组成视图as 后面就是查询语句也可以是子查询多表关联等复杂的查询语句create view zszxz_order as SELECT id , order_name, year from order
查询视图其使用本质与查询表一样示例如下SELECT * from zszxz_order
向视图中插入数据插入的数据实际在实体表 order 表中切记INSERT INTO zszxz_order(order_name, year) VALUES (小凡汐的订单, 2020);
删除视图drop view zszxz_order
17.5 小结视图的本质上查询语句故可以对一些简单的数据统计做成视图是个不错的选择其次如果是开放权限给第三方公司使用视图查询部分实体表的数据作为开放的表也是对视图的合理应用最后也可以将简单的表联结做成视图简化开发由于视图的本质是查询语句你可以理解为其前世就是查询今生就是虚表徒有其“表”名不副实只拥有表的部分功能十八 储存过程18.1 储存过程的概念我们经常使用的SQL查询语句都是单条语句如果要使用多条语句达到一个目的就显得力不从心了储存过程就是使用多条语句完成业务的操作你可以理解为linux脚本编程类似window的批处理文件那样简单的定义储存过程就是多条SQL的集合我们使用储存过程能够简化复杂的单条SQL相比于单条复杂的SQL极大提高了性能如果表结构发生变化只需要改变储存过程使用到SQL语句的表名如果业务逻辑发生变化只需要跳转储存过程即可具有很强的灵活性建立一次储存过程即可使用不用反复建立保证开发人员使用到都是相同的储存过程保证数据可靠性总之使用储存过程简单灵活安全可靠性能好18.2 存储过程语法创建存储过程Create PROCEDURE 储存过程名称 参数列表
begin过程体
end;
参数列表IN 表示输入; 示例 IN var1 Decimal(6,2)OUT 表示输出;示例 IN var2 Decimal(6,2)INOUT 表示输入输出示例 IN var3 Decimal(6,2)
变量declare 变量名称 变量类型 [default value]
执行存储过程call 储存过程名称
删除储存过程DROP PROCEDURE 储存过程名称
赋值使用 set 和 select into 语句为变量赋值。
set var : 20
select sum(price) into total from table_name
if 语句f 条件 then表达式
[elseif 条件 then表达式]
...
[else表达式]
end if;
case语句CASE 值 WHEN 匹配值 THEN 结果
[WHEN 匹配值 THEN 结果]
......
[ELSE 结果]
END
while语句[开始标签:]while 条件 do循环体
[结尾标签]
end while ;
loop 语句[开始标签:] loop语句体
[结尾标签]
end loop;
iterate/ leave语句其是通过标签可以实现iterate 表示迭代 leave表示离开
repeat语句repeat
--循环体
until 循环条件
end repeat;
tip : 如果你是用命令行学习在写多行SQL的时候 使用 // 可以实现换行哟18.3 储存过程示例我们准备表如下一个订单明细表CREATE TABLE oder_detail (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键,detail_name varchar(255) DEFAULT NULL COMMENT 订单明细,price decimal(10,2) DEFAULT NULL COMMENT 价格,oid int(11) DEFAULT NULL COMMENT 订单id,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8 COMMENT订单明细表;
准备数据如下INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (1, 毛巾, 20.00, 1);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (2, 牙膏, 15.00, 1);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (3, 杯子, 5.00, 1);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (4, 毛巾, 15.00, 2);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (5, 杯子, 15.00, 2);无参储存过程查看订单明细的所有订单名称,示例如下跟普通的查询语句没区别create procedure slelect_detail()
beginselect detail_name from oder_detail;
end;
我们再调用储存过程call slelect_detail();
此时就会打印内容如下毛巾
牙膏
杯子
毛巾
杯子
删除储存过程drop procedure slelect_detail;
带入参储存过程示例现在我们需要查询oid为动态的所有订单明细名称由于考虑到oid为动态就是需要用户自己输入故将oid作为入参;create procedure slelect_detail(IN order_id INT)
beginselect detail_name from oder_detail where oid order_id;
end;
调用储存过程只查询oid为1的用户的订单明细名称call slelect_detail(1);
打印内容毛巾
牙膏
杯子
删除储存过程drop procedure slelect_detail;
带入参和出参的存储过程示例查询任意用户的订单明细的所有金额定义入参订单id 为 order_id 输出总金额为 totalcreate procedure slelect_toatal_money(IN order_id INT, OUT total DECIMAL(8,2))
beginselect sum(price) into total from oder_detail where oid order_id;
end;
调用储存过程示例call slelect_toatal_money(1,total);
查询 order_id 为1 总金额示例SELECT total;
输出为 40删除储存过程drop procedure slelect_toatal_money;
18.4 if 语句示例上节的储存过程都是单条SQL这次我们开始使用控制流程实现复杂的储存过程知识追寻者对输入的 order_id 自动加5 然后判断 var 是否 小于7 如果是就查询订单明细价格否则查询订单明细价格总和create procedure slelect_toatal_money(IN order_id INT)
begin-- 定义变量declare var int;-- 赋值set var order_id5;-- if 判断if var7 thenselect price from oder_detail where oid order_id;elseselect sum(price) from oder_detail where oid order_id;end if;
end;
调用call slelect_toatal_money(1);
输出price
20
15
5
调用call slelect_toatal_money(2);
输出sum(price)
30
18.5 while 语句示例对 变量 var 进行判断如果 var 7 就执行 查询价格语句并且var 进行自增create procedure slelect_toatal_money(IN order_id INT)
begin-- 定义变量declare var int;-- 赋值set var order_id5;-- whilewhile var7 doselect price from oder_detail where oid order_id;set var var 1;end while;
end;
调用示例call slelect_toatal_money(1);
输出price
20
15
5
18.6 case语句示例如下语句实现的效果与上面if语句实现效果一致create procedure slelect_toatal_money(IN order_id INT)
begin-- 定义变量declare var int;-- 赋值set var: order_id;-- case 判匹配case var when 1 then select price from oder_detail where oid order_id;when 2 thenselect sum(price) from oder_detail where oid order_id;end case;
end;
调用示例call slelect_toatal_money(2);
输出sum(price)
30
将参数改为1试试结果18.7 loop语句如果 var 小于3 就 计算 价格 var 的值create procedure slelect_toatal_money(IN order_id INT)
begin-- 定义变量declare var int;-- 赋值set var: order_id;-- loopselect_loop : loopselect pricevar from oder_detail where oid order_id;set var var 1;-- 跳出循环if var 3 thenleave select_loop;end if;end loop;
end;
调用示例call slelect_toatal_money(1);
会输出三组结果18.8 repeatrepeat 与 while 的不同之处就是 while 在 执行之前检查条件其实执行之后检查条件create procedure slelect_toatal_money(IN order_id INT)
begin-- 定义变量declare var int;-- 赋值set var order_id5;-- repeat循环repeatselect price from oder_detail where oid order_id;set var var 1;until var7end repeat;
end;
调用示例call slelect_toatal_money(1);
此时会输出2组相同结果price
20
15
5
tip: loop, while , repeat , iterate 都是循环loop,while, repeat 功能几乎相同iterate可以通过标签的形式调用 循环与 leave 语句使用方式一样十九 游标19.1 游标的概念游标的本质就是查询后的结果集当我们对查询的结果集进行前一行或者后一行类似的操作时就可以使用到游标19.2 游标的语法首先需要 定义游标declare 游标名称 cursor for 查询语句其次打开游标open 游标名称然后对查询的结果集 即游标进行 检索行至变量提供使用最后关闭游标close 游标名称19.3 使用游标准备的表CREATE TABLE oder_detail (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键,detail_name varchar(255) DEFAULT NULL COMMENT 订单明细,price decimal(10,2) DEFAULT NULL COMMENT 价格,oid int(11) DEFAULT NULL COMMENT 订单id,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8 COMMENT订单明细表;
准备的数据INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (1, 毛巾, 20.00, 1);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (2, 牙膏, 15.00, 1);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (3, 杯子, 5.00, 1);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (4, 毛巾, 15.00, 2);
INSERT INTO zszxz.oder_detail(id, detail_name, price, oid) VALUES (5, 杯子, 15.00, 2);简单的使用游标查询oid为1 的订单明细名称 的结果集作为游标打开游标后抓取每行将结果赋值给变量nameCREATE PROCEDURE printName()
BEGIN-- 订单名称declare name varchar(20);-- 创建游标declare cur cursor for select detail_name from oder_detail where oid 1;-- 打开游标open cur;fetch cur into name;select name;-- 关闭游标close cur;
END;
调用储存过程call printName;
打印结果如下只有一条数据说明上述方式只在游标中抓取到一条数据而且是表里面行号最小的行name
------
毛巾
在循环中使用游标将 查询oid为1的 结果集赋值给游标通过游标抓取每行 将 订单明细名称和价格分别赋值给变量 name 和 detail_price; 在 循环无法继续时 会出现 SQLSTATE 02000 ; 即此通过 变量 continue 时设置 done 为1 代表 true此时循环结束跳出循环drop procedure if exists printDetail;
CREATE PROCEDURE printDetail()
BEGIN-- 订单名称declare name varchar(20);-- 价格declare detail_price decimal(8,2);-- 结束标志变量默认为假declare done boolean default 0;-- 创建游标declare cur cursor for select detail_name,price from oder_detail where oid 1;-- 指定游标循环结束时的返回值declare continue HANDLER for SQLSTATE 02000 set done 1;-- 打开游标open cur;-- 循环游标数据detail_loop:loop-- 根据游标当前指向的一条数据fetch cur into name,detail_price;select name , detail_price;-- 判断游标的循环是否结束if done then-- 跳出游标循环leave detail_loop; end if;-- 结束游标循环end loop;-- 关闭游标close cur;
END;
调用储存过程-- 调用存储过程
call printDetail();
美中不足的是会多遍历最后一行如果要精细处理还是需要自定义标志位进行跳出循环二十 触发器20.1触发器的概念触发器是指当表发生改变的时候触发的动作听起来有点抽象举个栗子当你往表中插入数据的时候此时表发生了改变现在想要在每次插入数据之前检测所有的入参是否都是小写此时就可以用触发器来检测经过上面的分析知道使用一个基本的触发器至少表要发生改变还要满足一个被触发的事件表发生改变通常指 增删改其动作可以发生在增删改 之前或者之后触发事件就是我们需要写的储存过程update (after/ before)insert (after/ before)delete (after/ before)20.2 触发器的基本语法创建触发器create trigger 触发器名称 触发动作 on 表名 for each row [触发事件]删除触发器drop trigger 触发器名称查看触发器show triggers;tip : 触发器是依赖于表创建没有表就没有触发器比如视图临时表都不是真实的表它们是没有触发器一般来说每个表都有触发器的限制一般最多支持6个不同类型的触发器由于使用触发器会频繁的改变表的每行故其十分影响性能特别对一些更新频率比较快的大表如果设置触发器就非常占用系统资源一般来说触发器用在表变动较小的小表, 不使用触发器就立即删除20.3 insert 触发器示例创建触发器; 创建一个触发器 getPrice 作用于 oder_detail 表的每行每当 插入数据之后就查询这条订单明细的价格赋值给变量 price 小伙伴可能会疑惑 NEW 是何物其是一张虚表记录者被插入数据的行故我们能在NEW表中获取每次插入的数据-- insert 触发器
CREATE TRIGGER getPrice AFTER INSERT ON oder_detail FOR EACH ROW
SELECT NEW.price INTO price;
检测插入触发器; 插入一条数据使用查询语句查询变量 显示为 20-- 检测插入触发器INSERT INTO oder_detail( detail_name, price, oid) VALUES ( 脸盆, 20.00, 2);select price;
删除触发器;-- 删除触发器
drop trigger getPrice;
20.4 update 触发器示例将插入后触发器改为更新后的触发器如下 只需要改动 after insert 为 after update 即可CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT NEW.price INTO price;
将之前的插入的SQL语句进行修改价格,并查询价格,此时价格为30NEW虚表储存的是即将更新的数据UPDATE oder_detail SET price 30.00 WHERE id 6;select price;
删除触发器-- 删除触发器
drop trigger getPrice;
将 更新触发器的NEW表改为OLD表CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT OLD.price INTO price;
更新价格为40UPDATE oder_detail SET price 40.00 WHERE id 6;
此时查询 价格为30说明OLD表触发的是原始数据值select price;
tip 更新触发器主要是要搞懂OLD存放原始数据NEW存放即将更新的数据NEW表可以设置更改值二OLD表是只读20.5 delete 触发器将 更新触发器改为 delete 触发器, 之前我们省略了 begin, end 如果是多条执行语句则需要加上CREATE TRIGGER getPrice AFTER delete ON oder_detail FOR EACH ROW
begin
SELECT OLD.price INTO price;
end;
删除之前的SQL数据delete from oder_detail where id 6;
查询价格为40OLD表存放的是将要被删除的数据select price;
二十一 用户操作有关用户账号的信息储存mysql的MYSQL数据库故如果需要查看用户信息则需要进入MYSQL库21.1查看用户信息user表储存了所有的登陆账号使用mysql库查询user表中的useruse mysql;
select user from user;
打印user
----
root
mysql.session
mysql.sys
root
21.2 创建用户CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码示例创建用户 zszxz ,并指定密码为 zszxzcreate user zszxz IDENTIFIED by zszxz;
21.3 重名名用户rename user 旧的用户名 to 新的用户名;示例重命名 用户 zszxz 为 lscrename user zszxz to lsc;
21.4 删除用户drop user 用户名示例删除用户lscdrop user lsc;
21.5 更改密码SET PASSWORD FOR 用户名 PASSWORD(密码)示例为用户 zszxz 更改密码为 lscSET PASSWORD FOR zszxz PASSWORD(lsc)
二十二 权限操作22.1 查看用户权限SHOW GRANTS FOR 用户名示例查看用户zszxz 拥有的权限SHOW GRANTS FOR zszxz
打印Grant for zszxz%
----------------
GRANT USAGE ON *.* TO zszxz%
查询出一条权限但 USAGE 表示 根本没有权限22.2 授予权限GRANT 权限 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] password]常见的权限 all, create, drop, insert, update, delete,select示例 给用户zszxz 分配 zszxz 库中的所有表的查询权限grant select on zszxz.* to zszxz;
再看下 zszxz变成 2 条Grant for zszxz%
----------------
GRANT USAGE ON *.* TO zszxz%
GRANT SELECT ON zszxz.* TO zszxz%
22.3 撤销权限REVOKE 权限列表 ON 表名 FROM 用户名示例撤销 用户 zszxz 对 zszxz库里面所有表的查询操作revoke select on zszxz.* from zszxz
22.4 权限列表使用授权撤销权限时可以参考如下权限列表ALL --- 除GRANT OPTION外的所有权限
ALTER ---使用ALTER TABLE
ALTER ROUTINE ---使用ALTER PROCEDURE和DROP PROCEDURE
CREATE ---使用CREATE TABLE
CREATE ROUTINE ---使用CREATE PROCEDURE
CREATE TEMPORARY TABLES ---使用CREATE TEMPORARY TABLE
CREATE USER ---使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW ---使用CREATE VIEW
DELETE ---使用DELETE
DROP ---使用DROP TABLE
EXECUTE ---使用CALL和存储过程
FILE ---使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION ---使用GRANT和REVOKE
INDEX ---使用CREATE INDEX和DROP INDEX
INSERT ---使用INSERT
LOCK TABLES ---使用LOCK TABLES
PROCESS ---使用SHOW FULL PROCESSLIST
RELOAD ---使用FLUSH
REPLICATION CLIENT ---服务器位置的访问
REPLICATION SLAVE ---由复制从属使用
SELECT ---使用SELECT
SHOW DATABASES ---使用SHOW DATABASES
SHOW VIEW ---使用SHOW CREATE VIEW
SHUTDOWN ---使用mysqladmin shutdown用来关闭MySQL
SUPER ---使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE ---使用UPDATE
USAGE ---无访问权限
二十三 mysql 架构与锁23.1 MYSQL架构概览MYSQL 的层级大概可以分为3类第一层 为 连接层只要负责MYSQL的数据库连接安全认证的功能第二层是MYSQL的核心层面其主要功能包括MYSQL的查询缓存执行计划优化等都在第二层实现第三层是引擎层为MYSQL指定不同的引擎将达到不同的数据操作效果23.2 Query CacheMYSQL 的 Query Cache 是 基于 hash 值计算进行匹配的缓存机制通常在大数据量的情况下如果开启Query Cache 会 频繁的计算Hash 会增加性能的消耗得不偿失生产环境中建议关闭该选项可以使用 语句 show VARIABLES like %query_cache% 查看 Query Chach 是否关闭我们主要关注的是 参数query_cache_type 是否关闭 如果是OFF就是关闭状态ON是开启状态而不是 query_cache_size参数其是缓存分配大小更多关于 Query Chach 的内容可以参考如下文章https://blog.csdn.net/dongnan591172113/article/details/52084041https://www.jianshu.com/p/3ab10180fbd523.3 读锁关于锁的知识希望读者学习过高并发相关知识对所有的锁分类有个清晰的认识学习本篇关于锁的概念将不会遇到阻碍在MYSQL中根据不同的引擎主要会出现三类锁的情况即 表锁读锁 和写锁读锁很好理解在MYSQL 中 读锁也是共享锁, 即 多用户状态下同一时间对资源的读取是互不影响但不能对数据进行修改等操作一般情况下我们手动给一条或者某个范围内(一般使用在储存过程)的数据加上读锁使用语法示例如下SELECT 字段 from 表名 [where 条件] lock in share mode;
23.4 写锁写锁是排他锁也称为独占锁使用的情况一般是写入数据的情况下一个用户如果获得写锁其它用户将不能获取写锁或者读锁直到该用户执行完操作并释放锁其使用方式为在执行语句后加上for update 语句即可格式示例SELECT 字段 from 表名 [where 条件] for update;
23.5 锁粒度锁粒度是指对资源锁定范围的一个程度使用不同的锁定策略达到并发性能较优的结果通常锁粒度使用策略情况分为行锁表锁页锁的情况**表锁**即对整张表进行加锁其性能开销较小加锁的速度较快但缺点也很明显其锁粒度大并发低如果要手动加表锁语法示例 lock tables 表名释放锁 unlock tables 表名行锁即对行进行锁定能够最大支持并发量故锁粒度最小但其枷锁速度慢性能消耗大会出现死锁行锁的种类又有 记录锁主键或者唯一索引都属于记录锁间隙锁GAP记录锁和间隙锁的组合next -key lock间隙锁一般用于查询条件是范围情况下而非相等条件页锁通常情况下遇不到页锁其开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间Tip: MyISAM和 Memory 引擎 支持表锁其会自动给SELECT,UPDATE,INSERT,DELETE 自动加表锁InnoDB 支持表锁和行锁对于UPDATE, INSERT ,DELETE 语句 InnoDB 会自动给数据加排他锁SELECT语句不加锁还有锁的其它分类也会使用到比如乐观锁基于版本号实现注意点是条件必须是主键读取时将数据版本号读出更新数据时版本号加1将查询的数据进行对比如果版本号不一致就是过期数据查询示例select id,value,version from 表名 where id #{id}
更新示例update 表名
set value2,versionversion1
where id#{id} and version#{version}
悲观锁如表锁行锁读写锁都是悲观锁如果看了知识追寻者写的锁知识还有困惑可以参考如下链接https://juejin.im/post/5b82e0196fb9a019f47d182323.6 引擎简介在上面的图例中可以看见MYSQL支持多种引擎当然远不止图中显示的引擎数量我们主流使用的引擎就是 InnoDB,其次是 MyISAM特殊情况下会使用到Memory引擎的知识是一本书都无法概括的内容知识追寻者在这边给小伙伴们做个简介有个大概的了解就好InnoDB 是使用最广泛的引擎也是最重要的引擎读者有必要了解其储存性能InnoDB 是 可重复读的事物隔离级别但其实现了next key lock 防止的幻读出现其基于聚簇索引实现其组要组成结构为内存结构线程磁盘文件组MyISAM在早期版本是MYSQL的默认引擎在MYSQL5.1之后不再使用其不支持事物不支持行锁默认表锁并发量低Menory引擎故名思意其储存内容都是存放在引擎当中支持Hash和Btree索引其数据读取快但缺点也很明显服务器如果发生故障重启后就会造成数据丢失二十四 锁等待锁等待的意思非常好理解就是session 事物会话开启一个事物代表一个会话A 对 某行数据获取独占锁在这边一般就是写锁然后session B 对相同的行进行获取独占锁就发生了锁等待简单理解就是 小孩子抢玩具谁先抢到 谁 先玩没抢到的玩具的孩子只能 等待 抢到玩具孩子玩腻了再给你瞬间泪奔有木有就是这么残酷当然MYSQL 没 这么残忍 其 还是有一个保留参数 innodb_lock_wait_timeout 指定死锁 的时间如果超过 死锁等待时间就是报异常知识追寻者 做个实验session A 执行如下语句开启事物更新索引为1 的语句此时 session A 获取了 id 1 这条 语句的 写锁权限BEGIN;
update order set year 2022 where id 1;
session B 执行如下 语句 , 跟 上面的语句一样 由于 id 1 这条数据的写锁已经被session A 获取故会发生锁等待的情况BEGIN;
update order set year 2022 where id 1;
知识追寻者这边默认等待了50秒 就报了如下异常Lock wait timeout exceeded; try restarting transaction
查看 默认锁等待 语句show VARIABLES like innodb_lock_wait_timeout
二十五 死锁25.1 死锁的产生死锁 就是 两个以上的会话 在 抢占 资源过程中 产生相互等待的情况有点绕是不是其实很简单 死锁是建立在 锁等待的基础上session A 获取 id 1 的写锁 session B 获取 id 2 的写锁 此时由于索引不同故不会长生锁等待现象 当 session A 尝试 获取 id 2 的 写锁时 由于 id 2 写锁已经被 session B 获取 故产生锁等待当 session B 尝试 获取 id 1 的写锁时 由于id 1 写锁已经被 session A 获取, 此时 产生锁等待由于 session A 与 session B 同时 都在 锁 等待状态产生了等待对方释放锁故会产生死锁知识追寻者做个试验session A 执行语句 获取 id 1 的 写锁权限BEGIN;
update order set year 2022 where id 1;
session B 执行语句 获取 id 2 的 写锁权限BEGIN;
update order set year 2022 where id 2;
session A 执行语句 尝试获取 id 2 的 写锁权限进入锁等待状态update order set year 2022 where id 2;
session B 执行语句 尝试获取 id 1 的 写锁权限进入锁等待状态update order set year 2022 where id 1;
当 B 进入 锁等待后就直接报死锁异常Deadlock found when trying to get lock; try restarting transaction
25.2 查看死锁可以使用 show engine innodb status 查看死锁......
*** (1) TRANSACTION: // 事物A
TRANSACTION 253507, ACTIVE 474 sec starting index read
mysql tables in use 1, locked 1 // 已经使用一个锁
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17001, OS thread handle 139824777217792, query id 2191731 ......
root updating
update order set year 2022 where id 2//执行得语句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: // 等待锁释放获取锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table zszxz.order trx id 253507 lock_mode X locks rec but not gap waiting
.....*** (2) TRANSACTION: // 事物 B
TRANSACTION 253508, ACTIVE 425 sec starting index read
mysql tables in use 1, locked 1 // 已经使用一个锁
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17002, OS thread handle 139824778569472, query id 2191735 ......
root updating
update order set year 2022 where id 1//执行得语句
*** (2) HOLDS THE LOCK(S): //持有锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table zszxz.order trx id 253508 lock_mode X locks rec but not gap
......*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 等待锁释放获取锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table zszxz.order trx id 253508 lock_mode X locks rec but not gap waiting
......不得不说下字母代表锁得类型如下共享锁S排他锁X意向共享IS意向排他IXgap lockGK 间隙锁锁定一个范围不包括当前记录本身RECORD LOCKS 代表记录锁可以看见上面得语句 1 代表 事物A MYSQL 线程id 17001 (2) 代表事物B, MYSQL 线程id 17002 事物 A 与B 都在等待 对方释放锁 产生了死锁Tip查看表锁 : show status like table%;如何解决死锁知识追寻者这边给个思路查找到死锁线程杀死MYSQL死锁的线程(kill命令)如果事物未提交直接回滚事物25.3 如何避免死锁在死锁容易产生得表使用表锁不会产生死锁避免交叉使用相同的锁原文本文出自公众号Java知音原文链接 https://mp.weixin.qq.com/s/mwffVlNuehcsxQmtvORWCQ
