MySQL学习笔记
一.MySQL数据类型
1.类型属性
UNSIGNED属性
USIGNED属性就是将数字类型无符号化,这与c语言中的unsigned含义相同.INT类型的范围是-2147483648 ~ 2147483647,而INT UNSIGNED的范围是0 ~ 4294967295.这看起来UNSIGNED是比单纯的INT类型更好一点.但是如果在对数据进行运算操作的时候恰好结果为负数,那么麻烦就来了.例如:
mysql> select * from t\G;
*************************** 1. row ***************************
a: 1
b: 2
1 row in set (0.00 sec)
mysql> select a - b from t;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
这种情况是因为MySQL中对UNSIGNED数的操作,其返回值都是UNSIGNED的.因为UNSIGNED中没有负数,所以就会报错.如果在其他系统上可能会返回4294967295.
总之尽量不要使用UNSIGNED.
ZEROFILL属性
ZEROFILL属性,看起来像是一个用来格式化显示的属性.请看下面例子.
mysql> show create table t\G;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
可以看到int(10),这是什么鬼??整型不就是4个字节么?10是代表什么意思.如果没有ZEROFILL属性括号内的数字就毫无意义.如果显示的数据是1,添加了ZEROFILL属性之后,假设括号内的数据设为4,那么就会显示成0001.其实他的作用就是当数据宽度小于设定值,那么就在前面添0.
2.日期和时间类型
| 类型 | 所占空间 |
|---|---|
| DATETIME | 8字节 |
| DATE | 3字节 |
| TIMESTAMP | 4字节 |
| YEAR | 1字节 |
| TIME | 3字节 |
DATETIME与DATE
DATETIME它既显示日期也显示时间,表示范围为: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE只显示日期,其表示范围为: 1000-01-01 ~ 9999-12-31.
TIMESTAMP
TIMESTAMP和DATETIME显示结果是一样的都是固定的: YYYY-MM-DD HH:MM:SS 的形式.而TIMESTAMP显示的范围为: 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC 其实际存储的内容为1970-01-01 00:00:00 到当前时间的毫秒数.
除了显示范围不同外还有以下不同之处:
- 在建表时,列为TIMESTAMP类型时可以设置默认值,而DATETIME不行.
- 在更新表时,可以设置TIMESTAMP类型的列自动更新时间为当前时间.
YEAR和TIME
YEAR 可以指定显示宽度,如YEAR(4)或YEAR(2).YEAR(4)其显示年份为: 1901~2155, YEAR(2)其显示范围为: 19702070.在YEAR(2)的设置下0069代表2000~2069.
TIME 显示范围为:-838:59:59 ~ 838:59:59.What??? 为什么会有负数!因为TIME类型不仅可以用来表示时间,还可以保存时间间隔.So,他可以为负数.
3.数字类型
整型
MySQL支持SQL标准支持的整数类型:INT、SMALLINT.此外还有TINYINT、MEDIUMINT和BIGINT.前面说的ZEROFILL属性可以格式化显示整型,这里还要注意,一旦启用ZEROFILL属性MySQL会为列自动添加UNSIGNED属性.
浮点型(非精确类型)
MySQL支持两种浮点类型:单精度FLOAT和双精度DOUBLE PRECISION.
高精度类型
DECIMAL和NUMERIC被视为相同类型,用于保存必须为确切精度的值.必须制定精度和标度.精度表示保存值的主要位数,标度表示小数点后面可以保存的位数.
3.字符类型
CHAR 和 VARCHAR是最常见的两种字符串类型.一般的,CHAR(N)用来保存固定长度的字符串,VARCHAR(N)用来保存变长字符类型.其中N表示字符长度,而非字节长度.其他类型用不上就不再记录了!
创建表和操作表
创建一个表
使用CREATE TABLE来创建一个数据表
CREATE TABLE[IF NOT EXISTS] (表名)
(
列名1 类型 其他限定属性(NOTNULL,AUTO_INCREMENT等),
···
列名n 类型 其他限定属性(NOTNULL,AUTO_INCREMENT等),
PRIMARY KEY (列名1,···,列名n)
)ENGINE=InnoDB
按照上面格式创建一个表.首先指定要创建的表名,使用IF NOT EXISTS来防止创建已存在的新表而产生错误.然后接要创建的列名,及其一些属性.使用PRIMARY KEY()来设置主键.ENGINE指定使用的存储引擎.
下面介绍几个重要的属性:
- NULL,NOTNULL表示指明该列能否为null.
- AUTO_INCREMENT表示该列的列值每当行增加时列值就自增.并且每个表只有一个这样的列.
- DEFAULT (值) 表示使用默认值,当插入行是没有给出值,那么就使用默认值.
- ENGINE指明使用的存储引擎.
常用的存储引擎:
InnoDB是一个可靠的事务处理引擎
MEMORY在功能上等同于MyISAM但是由于数据存储在内存中,速度很快.
MyISAM是一个性能极高的引擎它支持全文本搜索,不支持事务处理.
更新表
可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。
ALTER TABLE table_name action
更改列的属性
ALTER TABLE (表名)
CHANGE COLUMN (列名 类型 其他限定属性(NOTNULL,AUTO_INCREMENT等));
添加新列
ALTER TABLE (表名)
ADD COLUMN (列名 类型 其他限定属性(NOTNULL,AUTO_INCREMENT等))
AFTER description
AFTER description 设置该列不可删除.
删除列
ALTER TABLE (表名)
DROP COLUMN 列名
更改表名
ALTER TABLE (表名)
RENAME TO (新表名)
一.MySQL数据查询
检索数据
1.只命令行下的一些语句:
1.连接MySQL.若连接本地MySQL,则不需要-h参数.exit(回车)退出.
mysql -h主机地址 -u用户名 －p用户密码
2.修改用户密码.若用户没有密码,则不用-p参数则可以为用户添加密码.
mysqladmin -u用户名 -p旧密码 password 新密码
3.显示所有的数据库.
show databases;
4.选择一个数据库.
use 数据库名;
5.显示已选数据库中的表.
show tables;
2.检索单个列
Tips:在编写SQL语句时一般对所有的SQL关键词要大写,对所有的列名和表名小写.即使SQL语句不区分大小写,但是这是一种约定俗成的规范,要严格遵守.
利用SELECT语句从表名中检索出指定的列.
SELECT (列名) FROM (表名);
SELECT语句是按列检索的,意思是只能检索出满足条件的列信息.
3.检索多个列
SELECT (列名1),(列名2),...,(列名n) FROM (表名);
Tips:注意,不同列名之间用逗号隔开.
4.检索所有列
SELECT * FROM (表名);
Tips:通过*通配符来表示返回所有的列.如果确定要检索的列,则没必要过多的查询无用的列,这样会降低检索和应用程序的性能.
5.检索结果去重
如果检索的列信息可能存在重复,例如很多行数据对应的某一列的值可能有很多相同的值,则可以用DISTINCT来去除.
SELECT DISTINCT (列名) FROM (表名);
6.限制结果
如果不需要检索所有信息,只需取一部分信息,则可以用LIMIT来限制检索的范围.
SELECT (列名) FROM (表名) LIMIT 5,5;
LIMIT后面接的是要检索的行范围,即:例中之检索从第5行开始后的5行数据.如果范围大过表的范围,则只能表中有多少数据显示多少数据.
7.使用完全限定的表名
通过表名来引用列名,在多表查询时,那么可以通过这种方式来显示不同列与数据表的对应关系.
SELECT (表名).(列名) FROM (表名);
排序检索数据
1.排序数据
之前如果单纯地用SELECT来检索数据,结果是没有顺序的.在很多时候我们希望让结果有顺序的列出来,那么我们可以使用ORDER BY子句.
子句,SQL语句是由子句构成,有些子句是必须的,而有些子句是可选的.一个子句通常由一个关键字和所提供的数据组组成.
使用ORDER BY子句对检索结果进行排序.
SELECT (列名1) FROM (表名) ORDER BY (列名2);
该子句意思是按列名2以字母排序的顺序来对检索的结果进行排序输出.
列名2可与列名一相同.
2.按多个列排序
有时我们需要不仅仅是按一个列进行排序.所以可以用ORDER BY子句进行按多个列排序.
只需要将指定的列名用逗号隔开即可.
SELECT (列名) FROM (表名) ORDER BY (列名1),...,(列名n);
按多个列排序,其实就是按指定列的顺序依次进行多次按列排序.
3.指定排序方向
在用ORDER BY子句进行排序是默认采用字母顺序A-Z,或数字从小到大(升序).但是可以通过DESC关键字改变排序顺序为Z-A,或从大到小(降序).在排序是不区分大小写.
SELECT (列名) FROM (表名) ORDER BY (列名1)DESC,...,(列名n);
注意DESC只作用于位于其前面的列.如上面例子DESC只作用于列名1,即列名1按降序排列,其他的按升序排列.
过滤数据
现在建立一个学生——课程数据库方便之后讲解和操作.
- 学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
- 课程表:Course(Cno,Cname,Cpno,Ccredit)
- 学生选课表:SC(Sno,Cno,Grade)
Student
| 学号(Sno) | 姓名(Sname) | 性别(Ssex) | 年龄(Sage) | 所在系(Sdept) |
|---|---|---|---|---|
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 张立 | 男 | 19 | IS |
| Course |
| 课程号(Cno) | 课程名(Cname) | 先行课(Cpno) | 学分(Ccridit) |
|---|---|---|---|
| 1 | 数据库 | 5 | 4 |
| 2 | 数学 | 2 | |
| 3 | 信息系统 | 1 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | 2 | |
| 7 | C语言 | 6 | 4 |
| SC |
| 学号(Sno) | 课程名(Cno) | 成绩(Grade) |
|---|---|---|
| 201215121 | 1 | 92 |
| 201215121 | 2 | 85 |
| 201215121 | 3 | 88 |
| 201215122 | 2 | 90 |
| 201215122 | 3 | 80 |
1.WHERE子句进行数据过滤
数据库表中一般包含大量的数据,我们很少会检索所有的行.通常只会根据特定操作或报告的需要提取表格数据的子集.我们可以通过WHERE子句指定搜索条件也叫过滤条件.
如何理解SELECT WHERE语句对表遍历的过程:
先WHERE语句遍历所有行找到所有符合条件的行,SELECT遍历从WHERE语句找出的所有行中的所有列选出指定的列.
例如查找年龄为19岁的学号和姓名
SELECT Sno, Sname FROM Student WHERE Sage=19;
- WHERE找出的行:
| 学号(Sno) | 姓名(Sname) | 性别(Ssex) | 年龄(Sage) | 所在系(Sdept) |
|---|---|---|---|---|
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215125 | 张立 | 男 | 19 | IS |
- SELECT选出的列:
| 学号(Sno) | 姓名(Sname) |
|---|---|
| 201215122 | 刘晨 |
| 201215125 | 张立 |
| 上表即为最终检索到的结果. |
WHERE子句操作
WHERE子句操作符
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN A AND B | 指定介于两个值A,B之间 |
| 这里注意一种特殊的情况:NULL空值.可以使用IS来判断是否为空值.添加NOT来判断是否不是空值.特别注意空字符‘’和null,他们是有所不同的. |
2.组合WHERE子句进行数据过滤
- AND操作符:使用AND操作符将两个子句A,B组合,表示同时满足A,B子句才算满足条件.
- OR操作符:使用AND操作符将两个子句A,B组合,表示满足A或B子句就算满足条件.
- IN操作符:用来指定条件范围,范围中的条件都可以进行匹配.
- NOT操作符:用来否定位于其后的条件.
3.用通配符符进行过滤
前面使用的检索方式都是采用确定条件的方式来进行数据的过滤.然而这种方式并不是在任何时候都好用.例如我们要如何检索出姓何的学生信息.这个时候之前的确定条件的检索就肯定行不通了,那么我们可以使用通配符的方式进行一种特定搜索模式的检索.
通配符:用来匹配值的一部分的特殊字符.
搜索模式:由字面值,通配符或者两者组合起来的搜索条件.
在搜索条件子句中使用通配符,必须使用LIKE操作符.他表示其后跟的搜索模式利用通配符而不是直接相等匹配.
| 通配符 | 用法 |
|---|---|
| 百分号(%) | 表示匹配任何字符串出现任意次数(零个,一个或多个,注意NULL不能匹配) |
| 下划线(_) | 只匹配单个字符(一个) |
| 下面来举个例子: |
搜索以数据开头的课程名,结果会找到:数据库,数据结构,数据处理三个课程.
SELECT Cname FROM Course WHERE Cname LIKE '数据%';
搜索以数开头两个字的课程名,结果会找到:数学.
SELECT Cname FROM Course WHERE Cname LIKE '数_';
注意事项
通配符虽然实用,但是代价也大,就是会降低查询效率.
- 不要过度使用通配符.能用其他方式代替就用其他方式.
- 在确实需要使用通配符时,除非绝对有必要,否则不要把他们用在搜索模式的开始处.
- 仔细注意通配符的位置.
4.用正则表达式进行搜索
之前我们可以通过匹配,比较和通配符来实现数据的过滤,但是随着过滤和搜索的条件越来越复杂,WHERE子句也会变得越来越复杂,那么这个时候正则表达式就显得十分有用了.
正则表达使的基本用法
SELECT Cname FROM Course WHERE Cname REGEXP '数据结构'
这条语句的结果会查找到‘数据结构’.其实正则表达式与LIKE子句语法上差别不大,只是将LIKE换成REGEXP.
在sql语句中使用正则表达式的基本语法请参考正则表达式的一些文档,这里便不再赘述.下面来说明一些与一般正则表达式不同的地方
匹配字符类
| 类 | 说明 |
|---|---|
| [:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
| [:alpha:] | 任意字符同([a-zA-Z]) |
| [:blank:] | 空格和制表同([\t]) |
| [:cntrl:] | ASCII控制字符 |
| [:digit:] | 任意数字 |
| [:graph:] | 与[:print:]相同,但不包括空格 |
| [:lower:] | 任意小写字母 |
| [:print:] | 任意可打印字符 |
| [:punct:] | 既不在[:album:]又不在[:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符 |
| [:upper:] | 任意大些字符 |
| [:xdigit:] | 任意十六进制数字 |
定位符
| 元字符 | 说明 |
|---|---|
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
LIKE和REGEXP的不同之处
尽管他们用法相似但是毕竟是不一样的,对于LIKE而言它是匹配整个串,而REGEXP则是匹配子串.
数据处理
创建计算字段
当存储在表中的数据不是应用程序所需要的,而是这些数据经过转换、计算、或格式化的数据.那么我们就需要计算字段来发挥作用了.虽然这些操作也可以在应用程序上完成,但是相比之在数据库服务器上要更快.
字段基本上与列的意思相同,不过数据库的列一般称为列,而术语字段通常用在计算字段的连接上.
拼接字段
拼接 将值联结到一起构成单个值
SELECT Concat(Trim(Cno), ' ', Cname) AS Cno_name FROM Course
该语句则将课程编号与课程名一起显示在一列中.
在拼接中可以使用RTrim()(去掉串右边空格),LTrim(去掉串右边空格),Trim()(去掉左右两边空格)
AS后面是设置拼接后的列的别名.如果不设置别名则该列的名字为Concat(Trim(Cno), ' ', Cname),设置别名之后则使用别名.
执行算数计算
我们在检索数据时,可以对数值数据可以进行计算.
SELECT Sname 2018 - Sage AS Birth FROM Student
这条语句就可以计算出学生的出生日期.
使用数据处理函数
为了方便地转换和处理数据,SQL支持使用函数来处理数据,例如Trim()就是一个函数.下面简单介绍一些常用函数.
文本处理函数
| 函数 | 说明 |
|---|---|
| Left() | 返回串左边的字符 |
| Length() | 返回串长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转换为小写 |
| Right() | 返回串右边的字符 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为大写 |
时间和日期处理函数
| 函数 | 说明 |
|---|---|
| CurTime() | 返回当前日期 |
| CurDate() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期差 |
| Date_Format() | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期返回对应的星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回一个数的绝对值 |
| Cos() | 返回一个角度的余弦值 |
| Exp() | 返回一个数的指数值 |
| Mod() | 取余操作 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sin() | 返回一个角度的正弦 |
| Sqrt() | 返回一个数的平方根 |
| Tan() | 返回一个角度的正切 |
汇总数据
聚集函数
有很多时候我们并不想要表中的数据,而是需要对数据库行列进行简单的统计操作,产生一个汇总的数据出来.例如求和,平均值等.进行这样简单 处理的函数称为聚集函数.
MySQL给出了一些这样的函数.
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
SELECT AVG(Sage) AS avg_age FROM Student
SELECT COUNT(Sno) AS Stu_number FROM Student
SELECT MAX(Sage) AS max_age FROM Student
SELECT MIN(Sage) AS min_age FROM Student
SELECT SUM(Sage) AS sum_age FROM Student
上面例子表示:查找学生平均年龄、查找Sno的行数即学生总数、查找学生中的最大年龄、查找学生中的最小年龄、查找所有学生年龄总和.
SELECT AVG(Sage) AS avg_age,
COUNT(Sno) AS Stu_number,
MAX(Sage) AS max_age,
MIN(Sage) AS min_age,
SUM(Sage) AS sum_age
FROM Student
**Tips:COUNT(*)与普通的COUNT(某列),当在表中没有null值时,返回的值是一样的,当存在null值时,COUNT(*)不会忽略null值,即null值也算是一行,而普通的COUNT(某列)会忽略.
按上面例子也可以组合聚集成一个信息表.
| avg_age | Stu_number | max_age | min_age | sum_age |
|---|---|---|---|---|
| 19 | 4 | 20 | 18 | 76 |
分组数据
数据分组顾名思义就是将具有相同属性的数据分成一组.以便能对分组进行聚集计算.
创建分组
例如我们将学生中的男生人数和女生人数分别查找出来
SELECT COUNT(*) AS male_number FROM Student WHERE Ssex = ‘男’
SELECT COUNT(*) AS male_number FROM Student WHERE Ssex = ‘女’
如果用上面的方法未免太繁琐了,MySQL提供了GROUP BY子句来轻松的进行分组.
SELECT Ssex, COUNT(*) AS sex_num FROM Student GROUP BY Ssex
则可以方便且清楚地显示结果
| Ssex | sex_num |
|---|---|
| 男 | 2 |
| 女 | 2 |
- GROUP BY 会自动按选定列的不同列值进行分组.
- GROUP BY 可以包含任意数目的列
- GROUP BY 中列出的每一个列都必须是检索列货有效的表达式,不能是聚集函数.
- 除聚集函数外,SELECT语句中的每个列都必须在GRUOP BY子句中给出
- GROUP BY子句必须在WHERE子句之后,ORDER BY子句之前
过滤分组
如果单纯的使用GROUP BY分组的话,可能会有很多我们不需要的分组.还好MySQL提供了HAVING子句对分组进行过滤.
现在我们要查询学分相同且大于等于2的课程数,那么我们就可以使用HAVING.
SELECT Ccredit, COUNT(*) AS credit_num FROM Course GROUP BY Ccredit HAVING COUNT(*)>=2
子查询
子查询顾名思义就是在一个查询中嵌套一个查询.
现在我们要查询女生的学号和成绩.
SELECT Sno,Grade FROM CS WHERE Sno IN(SELECT Sno
FROM Student
WHERE Ssex='女');
一般来说子查询用于一个查询需要多个表的情况,一般结合IN使用.
联结表
SQL最强大的功能之一就是能在数据检索查询的执行中联结表.为什么要使用联结?
在数据库存储中,分解数据为多个表能有效的存储,更方便的处理,并且具有更大的可伸缩性.但是数据存在多个表中,那怎么用SELECT语句检索哪?所以我们就需要使用联结表.
创建联结
现在我们要检索CS表中成绩对应的课程名和学生名.
SELECT Student.Sno, Sname, Cname ,Grade FROM CS, Student, Course WHERE CS.Sno = Student.Sno AND Course.Cno = CS.Cno;
其结果为:
| Sno | Sname | Cname | Grade |
|---|---|---|---|
| 201215121 | 李勇 | 数据库 | 92 |
| 201215121 | 李勇 | 数学 | 85 |
| 201215121 | 李勇 | 信息系统 | 88 |
| 201215122 | 刘晨 | 数学 | 90 |
| 201215122 | 刘晨 | 信息系统 | 80 |
| 这样我们可以把三个表联结起来. |
在相同列名情况下必须使用完全限定列名
详细讨论联结
- 先生成所有表的笛卡尔积,即:CS * Student * Course.也就是将CS,Student,Course三个表的所有行的排列组合情况,而列则是SELECT选取出来的列,则有140种情况.又相当于执行SELECT * FROM CS, Student, Course语句.
Sno Cno Grade Sno Sname Ssex Sage Sdept Cno Cname Cpno Ccredit
201215121 1 92 201215121 李勇 男 20 CS 1 数据库 5 4
201215121 1 92 201215122 刘晨 女 19 CS 1 数据库 5 4
201215121 1 92 201215123 王敏 女 18 MA 1 数据库 5 4
201215121 1 92 201215125 张立 男 19 IS 1 数据库 5 4
201215121 2 85 201215121 李勇 男 20 CS 1 数据库 5 4
201215121 2 85 201215122 刘晨 女 19 CS 1 数据库 5 4
201215121 2 85 201215123 王敏 女 18 MA 1 数据库 5 4
201215121 2 85 201215125 张立 男 19 IS 1 数据库 5 4
201215121 3 88 201215121 李勇 男 20 CS 1 数据库 5 4
201215121 3 88 201215122 刘晨 女 19 CS 1 数据库 5 4
201215121 3 88 201215123 王敏 女 18 MA 1 数据库 5 4
201215121 3 88 201215125 张立 男 19 IS 1 数据库 5 4
201215122 2 90 201215121 李勇 男 20 CS 1 数据库 5 4
201215122 2 90 201215122 刘晨 女 19 CS 1 数据库 5 4
201215122 2 90 201215123 王敏 女 18 MA 1 数据库 5 4
201215122 2 90 201215125 张立 男 19 IS 1 数据库 5 4
201215122 3 80 201215121 李勇 男 20 CS 1 数据库 5 4
201215122 3 80 201215122 刘晨 女 19 CS 1 数据库 5 4
201215122 3 80 201215123 王敏 女 18 MA 1 数据库 5 4
201215122 3 80 201215125 张立 男 19 IS 1 数据库 5 4
201215121 1 92 201215121 李勇 男 20 CS 2 数学 NULL 2
201215121 1 92 201215122 刘晨 女 19 CS 2 数学 NULL 2
201215121 1 92 201215123 王敏 女 18 MA 2 数学 NULL 2
201215121 1 92 201215125 张立 男 19 IS 2 数学 NULL 2
201215121 2 85 201215121 李勇 男 20 CS 2 数学 NULL 2
201215121 2 85 201215122 刘晨 女 19 CS 2 数学 NULL 2
201215121 2 85 201215123 王敏 女 18 MA 2 数学 NULL 2
201215121 2 85 201215125 张立 男 19 IS 2 数学 NULL 2
201215121 3 88 201215121 李勇 男 20 CS 2 数学 NULL 2
201215121 3 88 201215122 刘晨 女 19 CS 2 数学 NULL 2
201215121 3 88 201215123 王敏 女 18 MA 2 数学 NULL 2
201215121 3 88 201215125 张立 男 19 IS 2 数学 NULL 2
201215122 2 90 201215121 李勇 男 20 CS 2 数学 NULL 2
201215122 2 90 201215122 刘晨 女 19 CS 2 数学 NULL 2
201215122 2 90 201215123 王敏 女 18 MA 2 数学 NULL 2
201215122 2 90 201215125 张立 男 19 IS 2 数学 NULL 2
201215122 3 80 201215121 李勇 男 20 CS 2 数学 NULL 2
201215122 3 80 201215122 刘晨 女 19 CS 2 数学 NULL 2
201215122 3 80 201215123 王敏 女 18 MA 2 数学 NULL 2
201215122 3 80 201215125 张立 男 19 IS 2 数学 NULL 2
201215121 1 92 201215121 李勇 男 20 CS 3 信息系统 1 4
201215121 1 92 201215122 刘晨 女 19 CS 3 信息系统 1 4
201215121 1 92 201215123 王敏 女 18 MA 3 信息系统 1 4
201215121 1 92 201215125 张立 男 19 IS 3 信息系统 1 4
201215121 2 85 201215121 李勇 男 20 CS 3 信息系统 1 4
201215121 2 85 201215122 刘晨 女 19 CS 3 信息系统 1 4
201215121 2 85 201215123 王敏 女 18 MA 3 信息系统 1 4
201215121 2 85 201215125 张立 男 19 IS 3 信息系统 1 4
201215121 3 88 201215121 李勇 男 20 CS 3 信息系统 1 4
201215121 3 88 201215122 刘晨 女 19 CS 3 信息系统 1 4
201215121 3 88 201215123 王敏 女 18 MA 3 信息系统 1 4
201215121 3 88 201215125 张立 男 19 IS 3 信息系统 1 4
201215122 2 90 201215121 李勇 男 20 CS 3 信息系统 1 4
201215122 2 90 201215122 刘晨 女 19 CS 3 信息系统 1 4
201215122 2 90 201215123 王敏 女 18 MA 3 信息系统 1 4
201215122 2 90 201215125 张立 男 19 IS 3 信息系统 1 4
201215122 3 80 201215121 李勇 男 20 CS 3 信息系统 1 4
201215122 3 80 201215122 刘晨 女 19 CS 3 信息系统 1 4
201215122 3 80 201215123 王敏 女 18 MA 3 信息系统 1 4
201215122 3 80 201215125 张立 男 19 IS 3 信息系统 1 4
201215121 1 92 201215121 李勇 男 20 CS 4 操作系统 6 3
201215121 1 92 201215122 刘晨 女 19 CS 4 操作系统 6 3
201215121 1 92 201215123 王敏 女 18 MA 4 操作系统 6 3
201215121 1 92 201215125 张立 男 19 IS 4 操作系统 6 3
201215121 2 85 201215121 李勇 男 20 CS 4 操作系统 6 3
201215121 2 85 201215122 刘晨 女 19 CS 4 操作系统 6 3
201215121 2 85 201215123 王敏 女 18 MA 4 操作系统 6 3
201215121 2 85 201215125 张立 男 19 IS 4 操作系统 6 3
201215121 3 88 201215121 李勇 男 20 CS 4 操作系统 6 3
201215121 3 88 201215122 刘晨 女 19 CS 4 操作系统 6 3
201215121 3 88 201215123 王敏 女 18 MA 4 操作系统 6 3
201215121 3 88 201215125 张立 男 19 IS 4 操作系统 6 3
201215122 2 90 201215121 李勇 男 20 CS 4 操作系统 6 3
201215122 2 90 201215122 刘晨 女 19 CS 4 操作系统 6 3
201215122 2 90 201215123 王敏 女 18 MA 4 操作系统 6 3
201215122 2 90 201215125 张立 男 19 IS 4 操作系统 6 3
201215122 3 80 201215121 李勇 男 20 CS 4 操作系统 6 3
201215122 3 80 201215122 刘晨 女 19 CS 4 操作系统 6 3
201215122 3 80 201215123 王敏 女 18 MA 4 操作系统 6 3
201215122 3 80 201215125 张立 男 19 IS 4 操作系统 6 3
201215121 1 92 201215121 李勇 男 20 CS 5 数据结构 7 4
201215121 1 92 201215122 刘晨 女 19 CS 5 数据结构 7 4
201215121 1 92 201215123 王敏 女 18 MA 5 数据结构 7 4
201215121 1 92 201215125 张立 男 19 IS 5 数据结构 7 4
201215121 2 85 201215121 李勇 男 20 CS 5 数据结构 7 4
201215121 2 85 201215122 刘晨 女 19 CS 5 数据结构 7 4
201215121 2 85 201215123 王敏 女 18 MA 5 数据结构 7 4
201215121 2 85 201215125 张立 男 19 IS 5 数据结构 7 4
201215121 3 88 201215121 李勇 男 20 CS 5 数据结构 7 4
201215121 3 88 201215122 刘晨 女 19 CS 5 数据结构 7 4
201215121 3 88 201215123 王敏 女 18 MA 5 数据结构 7 4
201215121 3 88 201215125 张立 男 19 IS 5 数据结构 7 4
201215122 2 90 201215121 李勇 男 20 CS 5 数据结构 7 4
201215122 2 90 201215122 刘晨 女 19 CS 5 数据结构 7 4
201215122 2 90 201215123 王敏 女 18 MA 5 数据结构 7 4
201215122 2 90 201215125 张立 男 19 IS 5 数据结构 7 4
201215122 3 80 201215121 李勇 男 20 CS 5 数据结构 7 4
201215122 3 80 201215122 刘晨 女 19 CS 5 数据结构 7 4
201215122 3 80 201215123 王敏 女 18 MA 5 数据结构 7 4
201215122 3 80 201215125 张立 男 19 IS 5 数据结构 7 4
201215121 1 92 201215121 李勇 男 20 CS 6 数据处理 NULL 2
201215121 1 92 201215122 刘晨 女 19 CS 6 数据处理 NULL 2
201215121 1 92 201215123 王敏 女 18 MA 6 数据处理 NULL 2
201215121 1 92 201215125 张立 男 19 IS 6 数据处理 NULL 2
201215121 2 85 201215121 李勇 男 20 CS 6 数据处理 NULL 2
201215121 2 85 201215122 刘晨 女 19 CS 6 数据处理 NULL 2
201215121 2 85 201215123 王敏 女 18 MA 6 数据处理 NULL 2
201215121 2 85 201215125 张立 男 19 IS 6 数据处理 NULL 2
201215121 3 88 201215121 李勇 男 20 CS 6 数据处理 NULL 2
201215121 3 88 201215122 刘晨 女 19 CS 6 数据处理 NULL 2
201215121 3 88 201215123 王敏 女 18 MA 6 数据处理 NULL 2
201215121 3 88 201215125 张立 男 19 IS 6 数据处理 NULL 2
201215122 2 90 201215121 李勇 男 20 CS 6 数据处理 NULL 2
201215122 2 90 201215122 刘晨 女 19 CS 6 数据处理 NULL 2
201215122 2 90 201215123 王敏 女 18 MA 6 数据处理 NULL 2
201215122 2 90 201215125 张立 男 19 IS 6 数据处理 NULL 2
201215122 3 80 201215121 李勇 男 20 CS 6 数据处理 NULL 2
201215122 3 80 201215122 刘晨 女 19 CS 6 数据处理 NULL 2
201215122 3 80 201215123 王敏 女 18 MA 6 数据处理 NULL 2
201215122 3 80 201215125 张立 男 19 IS 6 数据处理 NULL 2
201215121 1 92 201215121 李勇 男 20 CS 7 C语言 6 4
201215121 1 92 201215122 刘晨 女 19 CS 7 C语言 6 4
201215121 1 92 201215123 王敏 女 18 MA 7 C语言 6 4
201215121 1 92 201215125 张立 男 19 IS 7 C语言 6 4
201215121 2 85 201215121 李勇 男 20 CS 7 C语言 6 4
201215121 2 85 201215122 刘晨 女 19 CS 7 C语言 6 4
201215121 2 85 201215123 王敏 女 18 MA 7 C语言 6 4
201215121 2 85 201215125 张立 男 19 IS 7 C语言 6 4
201215121 3 88 201215121 李勇 男 20 CS 7 C语言 6 4
201215121 3 88 201215122 刘晨 女 19 CS 7 C语言 6 4
201215121 3 88 201215123 王敏 女 18 MA 7 C语言 6 4
201215121 3 88 201215125 张立 男 19 IS 7 C语言 6 4
201215122 2 90 201215121 李勇 男 20 CS 7 C语言 6 4
201215122 2 90 201215122 刘晨 女 19 CS 7 C语言 6 4
201215122 2 90 201215123 王敏 女 18 MA 7 C语言 6 4
201215122 2 90 201215125 张立 男 19 IS 7 C语言 6 4
201215122 3 80 201215121 李勇 男 20 CS 7 C语言 6 4
201215122 3 80 201215122 刘晨 女 19 CS 7 C语言 6 4
201215122 3 80 201215123 王敏 女 18 MA 7 C语言 6 4
201215122 3 80 201215125 张立 男 19 IS 7 C语言 6 4
2.再根据WHERE的条件再对上表进行检索.检索出满足CS.Sno = Student.Sno AND Course.Cno = CS.Cno的行.得到下表:
Sno Cno Grade Sno Sname Ssex Sage Sdept Cno Cname Cpno Ccredit
201215121 1 92 201215121 李勇 男 20 CS 1 数据库 5 4
201215121 2 85 201215121 李勇 男 20 CS 2 数学 NULL 2
201215121 3 88 201215121 李勇 男 20 CS 3 信息系统 1 4
201215122 2 90 201215122 刘晨 女 19 CS 2 数学 NULL 2
201215122 3 80 201215122 刘晨 女 19 CS 3 信息系统 1 4
3.再根据SELECT语句选取对应的列便可得到上面的查询结果.
像上面这种通过不同表之间的相等性测试构成的联结称为等值联结,也叫内部联结.
对于内部联结可以用稍微不同的语法来明确指定联结的类型.
SELECT Student.Sno, Sname, Cname ,Grade FROM CS INNER JOIN Student INNER JOIN Course ON CS.Sno = Student.Sno AND Course.Cno = CS.Cno;
联结条件用特定的ON子句,其实与WHERE没有什么区别.
高级联结
自联结
假设现在要查询和李勇同专业的其他同学的学号和姓名.
- 使用子查询
SELECT Sno, Sname FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = '李勇')
- 使用自联结
SELECT S1.Sno, S1.Sname FROM Student AS S1, Student AS S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = '李勇'
这里使用了别名来区分相同的表.按照联结的执行流程很容易得出结果.
于是得到结果如下:
| Sno | Sname |
|---|---|
| 201215121 | 李勇 |
| 201215122 | 刘晨 |
自然联结
无论何时我们对表进行联结,应该至少有一个列出现在不止一个表中.标准的联结应该将所有表的所有列全部列出来,即使有重复的列.而自然联结排除了重复出现的列,使每个列只出现一次.自然联结系统是不会自己实现,需要自己SELECT出所需要的列.如果没有指定列,而是使用通配符 * 那么查询出来的结果将会包含重复列.迄今为止我们使用的内部联结使用的都是自然联结,没有谁会傻到多此一举.除非有特定需求.
外部联结
在联结的时候,通常将一个表与另一个表的行相关联.但是有的时候,我们还需要表中一些不关联的行.那么就要用到外部联结.
例如:查询所有学生的课程成绩.
SELECT Student.Sno,Student.Sname,CS.Cno,CS.Grade FROM Student LEFT OUTER JOIN CS ON Student.Sno = CS.Sno;
结果如下:
| Sno | Sname | Cno | Grade |
|---|---|---|---|
| 201215121 | 李勇 | 1 | 92 |
| 201215121 | 李勇 | 2 | 85 |
| 201215121 | 李勇 | 3 | 88 |
| 201215122 | 刘晨 | 2 | 90 |
| 201215122 | 刘晨 | 3 | 80 |
| 201215123 | 王敏 | null | null |
| 201215125 | 张立 | null | null |
| Student表中的有些没有成绩的学生,也就是说与CS表不相关,通过外部联结可以将其列出来,但是不相关的列值是null. |
在外部联结中必须包含LEFT,RIGHT用来指定包含所有行的表(RIGHT指出OUTER JOIN右边的表,LEFT指出OUTER JOIN左边的表)指定的表会保留所有行.
组合查询
MySQL允许执行多个查询,并将结果作为单个查询结果集返回.这些组合查询通常称为并或者组合查询.
有两种基本情况需要组合查询:
- 在单个表查询中从不同的表返回类似结构的数据.
- 对单个表执行多个查询,按单个查询返回数据.
假设我们要查询CS专业和男学生的姓名,性别,专业.我们可以使用UNION将两条语句组合起来.
SELECT Sname, Ssex, Sdept FROM Student WHERE Sdept = "CS" UNION SELECT Sname, Ssex, Sdept FROM Student WHERE Ssex = "男"
意思就是将两条语句的查询结果并起来.就是组合查询了.
组合查询注意事项
- UNION必须有两条或两条以上的SELECT语句组成
- UNION中的每个查询语句必须包含相同的列、表达式、和聚集函数.
- UNION会自动覆盖重复的行,若想不覆盖则使用UNION ALL.
全文本搜索
全文本搜索介绍
在MySQL中MyISAM引擎支持全文本搜索而InnoDB不支持.而MyIASAM不支持事务和外键.
通配符与正则表达式匹配的性能较低，通常会匹配表的所有行，而且这些搜索极少使用索引，且返回的结果不智能化。在使用全文本搜索时,mysql不需要分别查看每个行，不需要分析和处理每个词.
为了进行全文本搜索，必须在被搜索的列建立索引，在建立索引后，使用match()与against()完成搜索，match指定被搜索的列，against指定要使用的搜索表达式。传递给match()的值必须与fulltext()定义中的相同，如果指定多个列，则必须列出他们，次序也需正确.
启用全文本搜索可以在建立表时指定，FULLTEXT(note_text)，指定对该列进行索引，如果需要也可以指定多个列。如果正在导入数据到一个新表，不应该开启fulltext索引，应该先导入所有数据，然后再修改表，定义fulltext,这样有助于更快导入数据，因为如果建表时指定，mysql会自动维护索引，在增加、更新或删除行时，索引也会随之更新，会花费时间.
全文本搜索会对结果排序，具有较高等级的行先返回，以文本匹配的良好程度排序数据。如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词的那些行高的等级值。全文搜索有的功能可以用LIKE实现，但是全文搜索提供了LIKE不能提供的功能（结果自动排序），而且由于数据是索引的，全文本所有还相当快.
SELECT (列) FROM (表) WHERE MATCH(列) AGAINST(条件)
使用查询扩展
查询扩展用来设法放宽返回的全文本搜索结果.除了返回匹配成功的行,还可以返回与搜索相关的行.
查询扩展过程:
- 先进行一个基本的全文本搜索,找出与搜索条件匹配的行.
- MySQL检查这些行并选择所有有用的词
- 再进行全文本搜索,这次不仅使用原条件还要加上所有有用的词.
SELECT (列) FROM (表) WHERE MATCH(列) AGAINST(条件 WITH QUERY EXTENSION)
布尔文本搜索
布尔文本搜索是MySQL全文本搜索的另一种形式.它可以对全文本搜索条件进行和正则表达式类似的模式化条件.
| 布尔操作符 | 说明 |
|---|---|
| + | 包含,词必须存在 |
| - | 排除,词必须不出现 |
| > | 包含,而且增加等级值 |
| < | 包含,且减少等级值 |
| () | 把词组成表达式 |
| ~ | 取消一个值的排序值 |
| * | 词尾通配符 |
| "" | 定义一个短语 |
MATCH(text) AGAINST('+rabbit +bait' IN BOOLEAN MODE)
匹配包含rabbit和bait的行
MATCH(text) AGAINST('rabbit bait' IN BOOLEAN MODE)
没有指定操作符,匹配至少包含一个词的行
MATCH(text) AGAINST('>rabbit <bait' IN BOOLEAN MODE)
匹配rabbit和bait,前者增加等级,后者减少等级
MATCH(text) AGAINST('"rabbit bait"' IN BOOLEAN MODE)
匹配短语 rabbit bait,而不是匹配rabbit和bait
MATCH(text) AGAINST('rabbit -bait' IN BOOLEAN MODE)
匹配包含rabbit,排除bait的行
二.数据插入
数据插入
使用INSERT语句插入数据,通常可以采用一下方式:
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询结果
插入完整的行
假设现在添加一个学生
INSERT INTO Student VALUES('201215124','李刚','男','19','IS')
通过INSERT语句进行数据插入,INTO指明插入的表,VALUES()中对应每一列必须填入一个数据,没有数据填null.这种做法不安全,不推荐使用.
推荐使用下面的安全方式:
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215124','李刚','男','19','IS')
就是在上面例子的基础上将表中的列名指出,对应VALUES里面的值.如果将值置为null,那么就可以实现插入行的一部分.
插入多行
实现多行插入可以使用多条INSERT语句,也可以使用如下的单条INSERT语句(将每行值用括号括起来接在VALUSES之后,每行值用逗号隔开).
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215127','李刚','男','19','null'),('201215124','李刚','男','19','IS')
插入检索出的数据
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) SELECT * FROM Student
使用上述格式便可将查询的数据插入到表中.
三.数据更新和删除
使用UPDATE和DELETE对数据表更新和删除.
数据更新
UPDATE (表) SET (列1)=(列值1),···,(列n)=(列值n) WHERE (条件)
更新指定表中满足条件的行中的一些列的值.
数据删除
DELETE FROM (表) WHERE (条件)
删除表中满足条件的行
四.视图
视图是虚拟的表.与包含数据的表不一样,视图是对数据表查询的结果的集合.
为什么要使用视图
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分,而不是整个表
- 保护数据.可以给用户授予表的特定部分的访问权限
- 更改数据格式和表示.
视图的规则和限制
- 与表一样视图必须唯一命名
- 对于可以创建视图的数目没有限制
- 为了创建视图必须有足够的访问权限
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图
- ORDER BY可以在视图中使用
- 视图不能索引,也不能有关联的触发器或默认值
- 视图可以和表一起使用
使用视图
- 视图用CREATE VIEW语句来创建
- 使用SHOW CREATE VIEW (视图名)来查看创建视图的语句
- 用DROP VIEW (视图名)来删除视图
- 更新视图时,可以先删除再创建,也可以使用CREATE OR REPLACE VIEW (视图名).
CREATE VIEW male_student AS SELECT * FROM Student WHERE Ssex = '男'
创建一个视图,其中AS并不是之前用作起别名,而是起连接作用.
DROP VIEW male_student
删除视图
CREATE OR REPLACE VIEW male_student AS SELECT * FROM Student WHERE Ssex = '女'
更新视图
五.存储过程
存储过程
在实际应用中,我们不可能只是简单的使用单条SQL语句对一个或几个表进行查询.很多情况下会对很多个表按照一定顺序执行很多条SQL语句,那么我们可以使用存储过程来实现.存储过程是为了以后的使用而保存的一条或多条SQL语句的集合.可以将其视为批文件.
为什么要使用存储过程
- 通过把处理语句封装在容易使用的单元中,简化复杂操作.
- 由于不要求反复建立一系列处理步骤,这保证了数据的完整性.如果所有开发人员和应用程序都使用同一存储过程,则使用的代码都是相同的.这一点的延伸就是防止错误,需要执行的步骤越多,出错的可能性就越大,防止错误保证了数据的一致性.
- 提高性能.存储过程要比SQL语句执行快
- 简化对变动的管理.
- 存在一些只能在用在单个请求中的MySQL特性和元素,存储过程可以使用他们编写功能更强更灵活的代码.
使用存储过程
现在创建一个查询所有男生的存储过程
# 创建存储过程(类似于定义函数)
CREATE PROCEDURE findFemale()
BEGIN
SELECT * FROM Student WHERE Ssex = '男';
end;
# 调用存储过程(函数调用)
CALL findFemale();
# 删除储存过程
DROP PROCEDURE findFemale;
MySQL变量
mysql的变量分为两种：系统变量和用户变量。但是在实际使用中，还会遇到诸如局部变量、会话变量等概念。根据个人感觉，mysql变量大体可以分为四种类型：
局部变量
局部变量一般用在sql语句块中，比如存储过程的BEGIN/END。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。局部变量一般用DECLARE来声明，可以使用DEFAULT来说明默认值。
例如在存储过程中定义局部变量：
DROP PROCEDURE IF EXISTS add;
CREATE PROCEDURE add
(
IN a INT,
IN b INT
)
BEGIN
DECLARE c INT DEFAULT 0;
SET c = a + b;
SELECT c AS c;
END;
# c就是局部变量
用户变量
用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。
用户变量使用如下(这里我们无须使用DECLARE关键字进行定义，可以直接这样使用): select @变量名.
对用户变量赋值有两种方式，一种是直接用"="号，另一种是用":="号。其区别在于使用SET命令对用户变量进行赋值时，两种方式都可以使用；当使用SELECT语句对用户变量进行赋值时，只能使用":="方式，因为在SELECT语句中，"="号被看作是比较操作符。
DROP PROCEDURE IF EXISTS math;
CREATE PROCEDURE math
(
IN a INT,
IN b INT
)
BEGIN
SET @var1 = 1;
SET @var2 = 2;
SELECT @sum:=(a + b) AS sum, @dif:=(a - b) AS dif;
END;
# 以@开头的为用户变量
使用参数
上面的存储过程只是简单的显示SELECT语句的结果.存储过程并不显示结果,而是把结果返回给你指定的变量.
假设我们要查询男生和女生分别有多少人.
# 创建存储过程
CREATE PROCEDURE find(OUT female INT, OUT male INT)
BEGIN
SELECT COUNT(*) INTO female FROM Student WHERE Ssex = '男';
SELECT COUNT(*) INTO male FROM Student WHERE Ssex = '女';
end;
# 调用存储过程
CALL find(@female, @male);
SELECT @female, @male;
/*
1.创建存储过程就像定义一个函数，用IN，OUT，INOUT来指明形参变量是传入，传出还是传入传出，形参变量名后面指明变量类型。
2.在存储过程体里，通过INTO将SELECT语句执行结果传给OUT类型的变量，于是可以将结果返回出去。
3.在存储过程调用的时候，传入实参（@开头的变量）。
4.可以使用DECLARE语句定义变量.DECLARE+变量名+数据类型;
*/
/*
1.IN参数修饰的变量是传入存储过程的变量供存储过程使用，OUT参数修饰的变量是存储过程的返回值，以及INOUT参数修饰的变量可以传入和传出。
2.INTO 是将SELECT查询结果指向传给哪个变量。
3.调用存储过程时传入的实参变量必须用@开头。
*/
六.游标
我们知道MySQL检索操作返回的是一组称为结果集的行.每组结果都匹配一个SQL语句.我们就不能很方便地得到结果集的第一行,下一行之类的结果.那么我们就需要一种可以滚动查看结果集的机制.所以我们就用游标来实现.(游标只能用于存储过程)
使用游标
使用游标的步骤
- 在能够使用游标前,必须声明它.这个过程实际没用检索数据,它只是定义要使用的SELECT语句.
- 一旦声明后必须打开游标以供使用.这个过程用前面声明的SELECT语句把数据实际检索出来.
- 对于填有数据的游标,根据需要取出各行.
- 在结束游标使用时,必须关闭游标.
创建游标
DROP PROCEDURE IF EXISTS find;
# 输入查询第几行数据，返回第几行数据
CREATE PROCEDURE find(OUT o INT, IN i INT)
BEGIN
DECLARE temp INT DEFAULT 0;
# 创建游标
DECLARE find_stu CURSOR FOR
SELECT Sage FROM Student WHERE Ssex = '男';
# 打开游标
OPEN find_stu;
# 循环
REPEAT
FETCH find_stu INTO o;
SET temp = temp + 1;
UNTIL temp = i END REPEAT;
/*
# 循环
WHILE i > 0 DO
FETCH find_stu INTO o;
set i = i - 1;
END WHILE;
*/
# 关闭游标
CLOSE find_stu;
END;
CALL find(@o,1);
SELECT @o;
七.触发器
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。
创建触发器
在创建触发器时要给出以下信息:
- 唯一的触发器名
- 触发器关联的表
- 触发器响应的活动(DELETE,INSERT,UPDATE)
- 触发何时执行
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE
其中，触发器名参数指要创建的触发器的名字,BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后,FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END
其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开.
tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;
mysql> DELIMITER ||
mysql> CREATE TRIGGER demo BEFORE DELETE
-> ON users FOR EACH ROW
-> BEGIN
-> INSERT INTO logs VALUES(NOW());
-> INSERT INTO logs VALUES(NOW());
-> END
-> ||
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER ;
上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作就会执行BEGIN和END中的语句，接着使用||结束
最后使用DELIMITER ; 将结束符号还原
触发器类型:
load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。
下面我们对Student表创建一个日志表,来验证触发器.
CREATE TABLE logs (
'Id' int(11) NOT NULL AUTO_INCREMENT,
'log' varchar(255) DEFAULT NULL COMMENT '日志说明',
PRIMARY KEY ('Id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';
CREATE TRIGGER logs AFTER INSERT ON Student
FOR EACH ROW
BEGIN
INSERT logs(Id, log) VALUES(null,NEW.Sname);
end;
#当Student有数据插入时,会触发触发器将插入添加到Student表中的Sname到logs表中.
八.事务处理
事务处理可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行.当一个成批处理的操作中途出现错误,如果不进行处理,那么有可能产生不完整的数据,而且你还不知道.所以就需要事务处理来控制其完整执行.
事务处理术语:
- 事务指一组SQL语句
- 回退指撤销指定SQL语句过程
- 提交将未存储的SQL语句结果写入数据库表
- 保留点指事务处理中设置的临时占位符,你可以对他发布回退.
事务要满足ACID特性:
- 原子性(A)：事务是最小单位，不可再分
- 一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
- 隔离性(I)：事务A和事务B之间具有隔离性
- 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)
假设有两名学生的平时成绩被老师加错了,现在将其改正.
START TRANSACTION;
UPDATE CS SET GRADE = GRADE + 10 WHERE Sno = '201215121' AND Cno = 2;
SAVEPOINT up;
UPDATE CS SET GRADE = GRADE - 10 WHERE Sno = '201215122' AND Cno = 2;
COMMIT;
ROLLBACK TO up;
ROLLBACK;
SELECT * FROM CS WHERE Cno = 2;
事务保证了数据库的完整性.如果两条UPDATE语句有失败的那么事务会回滚.事务成功就直接提交.还可以设置保留点,可以将事务回退到指定的保留点.
九.安全管理
管理用户
查看MySQL用户
USE mysql;
SELECT user FROM user;
创建用户账号
CREATE USER 'username'@'host' IDENTIFIED BY 'PASSWORD';
username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%(如果没有指定主机,默认是%)
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
删除用户
DROP USER hank;
更改用户名
REMANE USER (oldname) TO (newname);
访问权限
查看用户权限
SHOW GRANTS FOR username;
GRANT USAGE ON *.* TO
username@%
*.* 表示没有权限.
设置权限
GRANT SELECT ON test.* TO username;
GRANT SELECT ON
test.* TOusername@%
赋予username用户对test数据库下所有表查询权限.
取消权限
REVOKE SELECT ON test.* FROM username;
GRANT和REVOKE可在几个层次上控制访问权限:
- 整个服务器,使用GRANT ALL和REVOKE ALL
- 整个数据库,使用 ON 数据库名.*
- 特定表,使用 ON 数据库名.表名
更改用户密码
SET PASSWORD FOR username = Password(新密码);
结语
本人拙见,望师斧正!不吝赐教,感激不尽!
