数据库笔记
typora-root-url: ..\..\..\..\Workspaces\git\md-img\mysql typora-copy-images-to: ..\..\..\..\Workspaces\git\md-img\mysql
mysql笔记
mysql(一)
课程目标：
1) 数据库模型的理解
2) 数据库的分类
3) mysql数据库服务器的安装
4) 使用PD画E-R图
数据库的分类
关系型数据库（SQL）: oracle、mysql、sql-server、DB2
非关系型数据库（NOSQL）：redis、mongdb
数据库的名称
数据库(database)：是按照数据结构来组织、存储和管理数据的仓库。
数据库管理软件(DBMS): 是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。
数据表(table)：由行和列组成的。
行(row)：记录或一条实体数据
列(column):字段或属性
实体(entity)：模拟现实世界中的实物(名词)，表中存放了多个实体数据。
实体关系模型（Entity-Relationship Model）
指的是实体与实体之间的关系，简称为E-R。
关系型数据库
关系型数据库就是由二维表及其之间的联系组成的一个数据组织.
E-R图
E-R图元素
矩形框:表示实体,在框中记入实体名 菱形框:表示联系,在框中记入联系名
E-R关系类型
一对一：
一对多:
多对一：
多对多：
mysql数据
世界是最流行的开源数据库，支持多引擎。
什么是存储引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
mysql存储引擎
Myisam: 不支持事务、也不支持外键，优势是访问速度快。
Innodb:提供了具有提交、回滚和崩溃恢复能力的事务安全。
Memory:使用存在于内存中的内容来创建表。
Merge: 是一组MyISAM表的组合。
Archive:非常适合存储大量的独立的，作为历史记录的数据。
mysql安装
-
服务器安装：
服务器安装时默认的服务名称（mysql80）请改为:mysql
默认的用户名：root
-
客户端安装
mysql常用命令
配置环境变量：mysql安装目录的bin目录，如下：
C:\mysql\mysql-8.0.16\bin;
-
启动或停止服务
启动：net start mysql
停止：net stop mysql
-
使用cmd连接服务器
命令：mysql -h 主机ip -u 用户名 -p 密码
连接本地主机：mysql -h localhost -u root -p 123456
-
显示所有数据库：
-
切换数据库:
use 数据库名;
-
显示所有表：
show tables;
-
显示表结构：
describe user;
Navicat图形化软件连接mysql
-
创建连接
-
新建查询
mysql(二)
课程目标
1) 数据类型
2) 建库建表
3) 建约束
问题：
1) 用navicat创建的脚本怎么导出？
2) 用navicat无法连接本地数据库?
3) 切换到压缩版安装？
-
解压文件（不要使用中文目录）
-
my.ini配置文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\mysql\mysql-8.0.16
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
datadir=C:\\web\\sqldata
允许最大连接数
max_connections=100
服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
设置时区
default-time-zone = '+8:00'
-
安装mysql数据库服务
-
以管理员身份打开cmd
-
cd 进入到mysql/bin目录
-
执行mysqld --install命令安装
如果需要卸载：执行mysqld --remove卸载安装（非必需）
-
继续执行mysqld --initialize --user=root --console命令
要把这个随机的密码保存下来，用于连接登录
-
执行net start mysql启动服务（执行net stop mysql关闭服务）
-
作业讲解：
PowerDesigner模型分类：
1、 企业架构模型（Enterprise Architecture Model，EAM）
2、 需求模型（Requirements Model，RQM）
3、 业务处理模型（Business Process Model，BPM）
4、 信息流模型（Information Liquidity Model，ILM）
5、 概念数据模型（Conceptual Data Model，CDM）
6、 逻辑数据模型（Logical Data Model，LDM）
7、 物理数据模型（Physical Data Model，PDM）
8、 面向对象模型（Object-Oriented Model，OOM）
9、 XML模型（XML Model，XSM）
10、 自由模型（Free Model，FEM）
关系型数据库的三种模型：
概念数据模型(CDM)：把现实世界中的对象建成数据模型（分析阶段） E-R
逻辑数据模型(LDM)：实体与实体之间的关系（对CDM具体实现，主键，数据类型）
物理数据模型(PDM)：数据库表的实现（生成数据库sql脚本）
mysql数据类型
-
数值类型
整数类型 字节 用途 tinyint 1字节 小整数值 smallint 2字节 大整数值 mediumint 3字节 大整数值 int或integer 4字节 大整数值 bigint 8字节 极大整数值 float 4字节 单精度 浮点数值 double 8字节 双精度 浮点数值 decimal decimal(M,D) ,如果M>D，为M+2否则为D+2 -
日期类型
类型 字节 格式 用途 date 3 YYYY-MM-DD 日期值 time 3 HH:MM:SS 时间值 year 1 YYYY 年份值 datetime 8 YYYY-MM-DD HH:MM:SS 日期和时间值 timestamp 4 YYYYMMDD HHMMSS 时间戳 -
字符串类型
类型 大小 用途 char 0-255 定长字符串 varchar 0-65535 变长字符串 tinyblob 0-255字节 不超过 255 个字符的二进制字符串 tinytext 0-255 短文本字符串 blob 0-65535 二进制形式的长文本数据 text 0-65535 长文本数据 mediumblob 0-16777 215 二进制形式的中等长度文本数据 mediumtext 0-16777 215 中等长度文本数据 longblob 0-4294967295 二进制形式的极大文本数据 longtext 0-4294967295 极大文本数据 -
复合类型
-
Enum枚举类型： 限制取值范围，只能取其中的一个值或使用null值
-
Set集合类型: 限制取值范围，可以取其中的多个值
-
-
object类型
json类型：{key:values} 8.0之后的版本可以使用
{"name":"张三",age:18,address:{"zip":418000,"city":"湖南长沙"}}
mysql约束
| 约束名 | 功能 |
|---|---|
| 非空约束（not null） | 指定某列不为空 |
| 主键约束（primary key） | 指定某列的数据不能重复、唯一 |
| 唯一约束（uniqu） | 指定某列和几列组合的数据不能重复，允许一个null值 |
| 外键约束（foreign key） | 指定该列记录属于主表中的一条记录，参照另一条数据 |
| 检查约束（check） | 指定一个表达式，用于检验指定数据 |
| 自动增长（auto_increment） | 整数类型字段，从1开始按照1递增,一般作为主键字段 |
| 默认值（default） | 设置字段一个默认值 |
三种写约束方式
-
直接写在表中的字段后面
-
写在所有字段的后面
-
创建表后再添加约束
# 切换数据库
use 18c1ja;
# 创建表
# 方式1(推荐):
create table student
(
# comment 注释 给字段加注释
sno tinyint primary key auto_increment comment '学生编号',
sname varchar(50) not null
);
drop table student;
# 方式2：
create table student
(
sno tinyint,
sname varchar(50) not null ,
CONSTRAINT PK_STUDENT_SNO PRIMARY key (sno)
);
# 方式3：
create table student
(
sno tinyint,
sname varchar(50) not null
);
alter table student
add CONSTRAINT PK_STUDENT_SNO PRIMARY KEY(sno);
mysql(三)
课程目标：
-
json类型的讲解
-
复习SQL语言的分类
-
mysql中的函数
json类型的学习
-
什么JSON?
JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式。它基于 ECMAScript (欧洲计算机协会制定的js规范)的一个子集，
-
JSON语法
// json对象可以直接取值
json对象：{key,value}
// json字符串需要转换为json对象才能取值
json字符串：'{key,value}'常用方法：
JSON.parse() ： JSON字符串转换为JS对象
var obj = JSON.parse('{"a": "Hello", "b": "World"}');JSON.stringify() ：JS对象转换为JSON字符串
var json = JSON.stringify({a: 'Hello', b: 'World'}); -
mysql中的JSON用法
-
创建json类型数据表
drop table if exists t2;
create table t2
(
tid int,
object json # json类型
) -
添加json数据
insert into t2 values(3,'{"name":"张三","age":18}'); insert into t2 values(4,'{"name":"如花","age":20,"address":{"zip":418000,"city":"长沙"}}'); select * from t2; -
json操作
-
检索json字段-> 或 ->>（推荐此方式）
# -> 获取json中的字符串值,结果是带双引号的字符串 select object -> '$.name' name from t2; # ->> 获取json中的字符串值，结果没有双引号 select tid, object ->> '$.name' name, object->>'$.age' age from t2;
-
查找方法
# JSON_PRETTY():以优雅的格式显示JSON值 select json_pretty(object) from t2; # JSON_CONTAINS(): 如果找到了数据，则返回1，否则返回0 select json_contains(object ->> '$.age','18') from t2 where tid = 3;
-
修改方法
# JSON_SET():替换现有值并添加不存在的值
update t2 set object = json_set(object,'$.score',"90") where tid =3;
# JSON_INSERT():插入值，但不替换现有值
update t2 set object = json_insert(object,'$.birthday',"1990-10-10") where tid = 4;
# JSON_REPLACE():仅替换现有值
update t2 set object = json_replace(object,'$.address.city',"上海") where tid = 4; -
删除方法
# JSON_REMOVE():删除指定值
update t2 set object = json_remove(object,'$.score') where tid = 3;
-
其它方法
# JSON_KEYS()：获取JSON文档中的所有键
select json_keys(object) from t2;
# JSON_LENGTH()：给出JSON文档中的元素数
select json_length(object) from t2;
-
-
复习SQL
什么是sql
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
sql分类
-
DDL(数据定义语言)：create、alter 、drop、truncate
-
DML(数据操作语言)：insert、update、delete、select
-
DCL(数据控制语言)：grant、revoke
-
TCL(事务控制语言)：commit、rollback、savepoint
建表
# 学生表
drop table if exists student;
create table student
(
sno tinyint primary key auto_increment comment '学生编号',
sname varchar(20) not null unique comment '学生姓名',
sex char(2) check(sex in('男','女')) comment '学生性别',
age int check(age >= 16 and age <=30) comment '学生年龄',
address varchar(200) default '地址不详' comment '学生地址'
);
delete from student;
insert into student(sname,sex,age,address) values
('张三','男',18,default),('李四','男',20,'长沙'),
('如花','女',16,'北京'),('王五','男',25,'上海');
commit;
select * from student;
# 课程表
drop table if exists course;
create table course
(
cid int primary key auto_increment comment '课程编号',
cname varchar(20) not null comment '课程名称'
);
insert into course(cname) values('数学'),('语文'),('英语');
commit;
select * from course;
# 成绩表
# `(反单引号) 一般用于表名或字段名，插入数据时为字符串使用''单引号
drop table if exists score;
create table `score`
(
`sid` tinyint primary key auto_increment,
`sno` tinyint references student(sno),
`cid` int references course(cid),
`score` int not null check(score > 0 ) default 0
);
# 添加数据
insert into score(sno,cid,score) values(1,1,70),(1,2,85),(2,1,40),(2,2,90);
commit;
select * from score;
# 查看所有约束
select * from information_schema.table_constraints where table_name = 'student';
# 日期默认值不能使用函数
# 方式1：使用timestamp
drop table if exists t1;
create table t1
(
tid int,
# # mysql 5.0 版本不支持使用函数作为默认值，可以用同义词
createTime timestamp default current_timestamp
)
# 方式2：使用函数sysdate()或now()
drop table if exists t1;
create table t1
(
tid int,
createTime datetime default now()
)
insert into t1 values(10,default);
select * from t1;
数据库运算符
-
关系运算符
=、<>(!=)、<、>、<=、>=、!>、!< -
逻辑运算符
not、and、or -
特殊运算符
% 通配符代表任意多个字符
_ 通配符代表任意一个字符
escape：用来转义特定字符
# 转义后_不作为通符使用了
select * from student where sname like '%张__' escape '_';
between 定义一个取值范围区间,使用and分开
like 字符串匹配
in 一个字段的值是否在一个定义的值之中
exists 某个字段是否有值
is null 字段是否为 null
is not null 段是否不为 null
mysql函数
官网：https://dev.mysql.com/doc/refman/8.0/en/functions.html
-
字符串函数
名称 描述 ASCII()返回最左边字符的数值 BIN()返回包含数字的二进制表示形式的字符串 BIT_LENGTH()返回参数的长度（以位为单位） CHAR()返回每个传递的整数的字符 CHAR_LENGTH()返回参数中的字符数 CHARACTER_LENGTH()CHAR_LENGTH（）的同义词 CONCAT()返回串联的字符串 CONCAT_WS()返回用分隔符连接 ELT()返回索引编号的字符串 EXPORT_SET()返回一个字符串，这样对于值位中设置的每个位，您将获得一个打开的字符串，对于每个未设置的位，您将获得一个关闭的字符串 FIELD()后续参数中第一个参数的索引（位置） FIND_IN_SET()第二个参数中第一个参数的索引（位置） FORMAT()返回格式化为指定的小数位数的数字 FROM_BASE64()解码base64编码的字符串并返回结果 HEX()十进制或字符串值的十六进制表示 INSERT()在指定位置插入子字符串，最多可指定字符数 INSTR()返回第一次出现的子串的索引 LCASE()LOWER（）的同义词 LEFT()返回指定的最左边的字符数 LENGTH()返回字符串的长度（以字节为单位） LIKE简单模式匹配 LOAD_FILE()加载命名文件 LOCATE()返回第一次出现的子串的位置 LOWER()以小写形式返回参数 LPAD()返回字符串参数，用指定的字符串左填充 LTRIM()删除前导空格 MAKE_SET()返回一组用逗号分隔的字符串，这些字符串在位中具有相应的位 MATCH执行全文搜索 MID()返回从指定位置开始的子字符串 NOT LIKE否定简单模式匹配 NOT REGEXP否REGEXP OCT()返回包含数字的八进制表示形式的字符串 OCTET_LENGTH()LENGTH（）的同义词 ORD()返回参数最左边字符的字符代码 POSITION()LOCATE（）的同义词 QUOTE()转义要在SQL语句中使用的参数 REGEXP字符串是否匹配正则表达式 REGEXP_INSTR()子串匹配正则表达式的起始索引 REGEXP_LIKE()字符串是否匹配正则表达式 REGEXP_REPLACE()替换匹配正则表达式的子字符串 REGEXP_SUBSTR()返回匹配正则表达式的子字符串 REPEAT()重复字符串指定次数 REPLACE()替换出现的指定字符串 REVERSE()反转字符串中的字符 RIGHT()返回指定的最右边字符 RLIKE字符串是否匹配正则表达式 RPAD()将字符串追加指定次数 RTRIM()删除尾随空格 SOUNDEX()返回soundex字符串 SOUNDS LIKE比较声音 SPACE()返回指定数量的字符串 STRCMP()比较两个字符串 SUBSTR()返回指定的子字符串 SUBSTRING()返回指定的子字符串 SUBSTRING_INDEX()在指定的定界符出现次数之前从字符串返回子字符串 TO_BASE64()返回转换为以64为底的字符串的参数 TRIM()删除前导和尾随空格 UCASE()UPPER（）的同义词 UNHEX()返回包含数字的十六进制表示形式的字符串 UPPER()转换为大写 WEIGHT_STRING()返回字符串的权重字符串 -
数值函数
名称 描述 ABS()返回绝对值 ACOS()返回反余弦 ASIN()返回反正弦 ATAN()返回反正切 ATAN2()，ATAN()返回两个参数的反正切 CEIL()返回不小于参数的最小整数值 CEILING()返回不小于参数的最小整数值 CONV()在不同的基数之间转换数字 COS()返回余弦 COT()返回余切 CRC32()计算循环冗余校验值 DEGREES()将弧度转换为度 DIV整数除法 EXP()提升力量 FLOOR()返回不大于参数的最大整数值 LN()返回参数的自然对数 LOG()返回第一个参数的自然对数 LOG10()返回参数的以10为底的对数 LOG2()返回参数的以2为底的对数 MOD()退还剩余 PI()返回pi的值 POW()将参数提高到指定的幂 POWER()将参数提高到指定的幂 RADIANS()返回参数转换为弧度 RAND()返回一个随机浮点值 ROUND()围绕论点 SIGN()返回参数的符号 SIN()返回参数的正弦 SQRT()返回参数的平方根 TAN()返回参数的正切值 TRUNCATE()截断为指定的小数位数 -
日期和时间函数
名称 描述 ADDDATE()将时间值（间隔）添加到日期值 ADDTIME()加时间 CONVERT_TZ()从一个时区转换到另一个时区 CURDATE()返回当前日期 CURRENT_DATE()，CURRENT_DATECURDATE（）的同义词 CURRENT_TIME()，CURRENT_TIMECURTIME（）的同义词 CURRENT_TIMESTAMP()，CURRENT_TIMESTAMPNOW（）的同义词 CURTIME()返回当前时间 DATE()提取日期或日期时间表达式的日期部分 DATE_ADD()将时间值（间隔）添加到日期值 DATE_FORMAT()指定格式日期 DATE_SUB()从日期中减去时间值（间隔） DATEDIFF()减去两个日期 DAY()DAYOFMONTH（）的同义词 DAYNAME()返回工作日的名称 DAYOFMONTH()返回月份中的一天（0-31） DAYOFWEEK()返回参数的工作日索引 DAYOFYEAR()返回一年中的某天（1-366） EXTRACT()提取部分日期 FROM_DAYS()将天数转换为日期 FROM_UNIXTIME()将Unix时间戳记格式化为日期 GET_FORMAT()返回日期格式字符串 HOUR()提取时间 LAST_DAY返回参数的月份的最后一天 LOCALTIME()，LOCALTIMENOW（）的同义词 LOCALTIMESTAMP，LOCALTIMESTAMP()NOW（）的同义词 MAKEDATE()根据年份和年份创建日期 MAKETIME()从小时，分钟，秒创建时间 MICROSECOND()从参数返回微秒 MINUTE()返回参数的分钟 MONTH()返回经过日期的月份 MONTHNAME()返回月份名称 NOW()返回当前日期和时间 PERIOD_ADD()在一年的月份中添加一个期间 PERIOD_DIFF()返回期间之间的月数 QUARTER()从日期参数返回季度 SEC_TO_TIME()将秒转换为“ hh：mm：ss”格式 SECOND()返回第二个（0-59） STR_TO_DATE()将字符串转换为日期 SUBDATE()用三个参数调用时DATE_SUB（）的同义词 SUBTIME()减去时间 SYSDATE()返回函数执行的时间 TIME()提取传递的表达式的时间部分 TIME_FORMAT()格式化为时间 TIME_TO_SEC()返回参数转换为秒 TIMEDIFF()减去时间 TIMESTAMP()仅使用一个参数，此函数将返回日期或日期时间表达式。有两个参数，参数的总和 TIMESTAMPADD()向日期时间表达式添加间隔 TIMESTAMPDIFF()从日期时间表达式中减去一个间隔 TO_DAYS()返回日期参数转换为天 TO_SECONDS()返回从Year 0开始转换为秒的date或datetime参数 UNIX_TIMESTAMP()返回Unix时间戳 UTC_DATE()返回当前UTC日期 UTC_TIME()返回当前UTC时间 UTC_TIMESTAMP()返回当前UTC日期和时间 WEEK()返回星期数 WEEKDAY()返回工作日索引 WEEKOFYEAR()返回日期的日历周（1-53） YEAR()返回年份 YEARWEEK()返回年和周 -
流程控制函数
名称 描述 CASE案例运算符 IF()如果/其他构造 IFNULL()空if / else构造 NULLIF()如果expr1 = expr2，则返回NULL -
转换函数
名称 描述 BINARY将字符串转换为二进制字符串 CAST()将值强制转换为特定类型 CONVERT()将值强制转换为特定类型 -
其它函数
名称 描述 DATABASE() 返回当前数据库名 VERSION() 返回当前数据库版本< USER() 返回当前登录用户名 INET_ATON(IP) 返回 IP 地址的数字表示 INET_NTOA(num) 返回数字代表的 IP 地址 PASSWORD(str) 返回字符串 str 的加密版本 MD5(str) 返回字符串 str 的 MD5 值 ExtractValue() 使用XPath表示法从XML字符串中提取值 UpdateXML() 返回替换的XML片段 -
聚合函数
名称 描述 AVG()返回参数的平均值 BIT_AND()按位返回AND BIT_OR()按位返回OR BIT_XOR()返回按位异或 COUNT()返回计数返回的行数 COUNT(DISTINCT)返回多个不同值的计数 GROUP_CONCAT()返回串联的字符串 JSON_ARRAYAGG()将结果集作为单个JSON数组返回 JSON_OBJECTAGG()将结果集作为单个JSON对象返回 MAX()返回最大值 MIN()返回最小值 STD()返回人口标准差 STDDEV()返回人口标准差 STDDEV_POP()返回人口标准差 STDDEV_SAMP()返回样品标准偏差 SUM()返回总和 VAR_POP()返回总体标准方差 VAR_SAMP()返回样本方差 VARIANCE()返回总体标准方差 -
窗口函数
名称 描述 CUME_DIST()累积分布值 DENSE_RANK()当前行在其分区内的排名，没有空格 FIRST_VALUE()窗框第一行的自变量值 LAG()分区中当前行滞后的参数值 LAST_VALUE()窗口框架最后一行的参数值 LEAD()来自分区内当前行的行的参数值 NTH_VALUE()窗框第N行的自变量值 NTILE()分区中当前行的存储桶号。 PERCENT_RANK()百分比排名值 RANK()当前行在其分区内的排名，带有空格 ROW_NUMBER()分区中当前行的数量
mysql(四)
课程目标：
DML：inert、update、delete
DQL：select
基础查询 统计查询 排序查询 常用函数 分组函数 分组查询 连接查询 子查询 分页查询 联合查询
drop、deletet和truncate的区别?
-
语言类型：
drop和truncate属性DDL语言
delete属性dml语言
-
操作内容：
drop删除整张表（所有的数据和表结构都不存，也无法恢复）
truncate和delete都可以清空表数据
truncate只能删除表
delete可以清空表，也可以带wheret条件删除
-
事务区别：
truncate删除的数据不能使用事务恢复，操作不会记录到日志中
delete删除的数据可以使用事务
-
外键关系：
delete有主外键关系时，先删从表数据，再删除主表数据
truncate有主外键关系时，只能删除从表数据
-
执行效率
drop>truncae>delete
查询分类
-
单表查询
语法：
select [distinct|top] *,列名,函数, 变量,子查询
from 表名
where 分组前条件
group by 分组查询
having 分组后条件
order by 排序
# 2020年S10《LOL》全球总决赛,选出1个mvp,13赛区
select top 1
from 全球
where 年龄>=18
group by 赛区分组
having 每个赛区的前3名队伍
order by 人气 desc -
多表查询
-
连接查询(内外交叉，左右完整)
内连接：inner join
外连接:
左外：left join
右外：right join
全外：full join
交叉连接(笛卡尔积) : select * from 表1,表2
-
子查询
子查询作为作条件
子查询作为字段
子查询作为表
-
联合查询
交集: union、unionall
并集: Intersect
差集: minus
-
统计查询
-
聚合函数: 聚合函数对一组值执行计算，并返回单个值，也被称为组函数。一般与group by一起使用。
-
分类
avg(): 平均值
max():最大值
min():最小值
sum():求和
count():求行的总数，不包括null值
count(*):求表的总数，包括null值
查询之排序（order by）
-
排序规则：升序(默认): asc 降序：desc
-
语法：
select sex,max(age) 最大年龄
from student
group by sex
order by 最大年龄 desc;
分组查询(gruop by)
-
分组查询与统计函数一起使用
-
分组前的条件where
-
分组后的条件having
连接查询
# 交叉连接(迪卡尔的值)
select * from student s ,score c; # 16
# 连接查询相当于inner join
select * from student s ,score c where s.sno = c.sno;
行列转换 CASE WHEN THEN END
-
语法
# 语法1：
case 列名
when '值' then
end
# 语法2：
case
when 列名 = '值' then
end
-
查询案例
# 查询学生的性别，如果是男显示1,如果是女显示0
select sname 姓名, sex ,
case sex
when '男' then '1'
when '女' then '0'
end 性别
from student;
转换前
select sname,cname,score from student s inner join score a
on s.sno= a.sno
inner join course c
on c.cid = a.cid;
转换后
# 行列转换
select s.sno 学号, s.sname 姓名,
sum(case cname when '语文' then score else 0 end ) 语文 ,
sum(case cname when '数学' then score else 0 end ) 数学
from student s
inner join score a on s.sno= a.sno
inner join course c on c.cid = a.cid
group by s.sno,s.sname;
mysql中的事务
# 开启事务
start transaction;
insert into t3 values(1,'张三');
insert into t3 values(2,'李四');
# 提交事务后，事务结束
# commit;
# 设置事务保存点
savepoint p1;
insert into t3 values(4,'如花');
insert into t3 values(5,'王五');
# 回滚事务后，事务结束
# rollback;
# 回滚到p1保存点
rollback to p1;
select * from t3;
# 查看当前会话的数据库隔离级别
select @@transaction_isolation;
# 设置事务隔离级别
# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
视图
create or replace view v1
(学号,姓名,课程,成绩)
as
select a.sno,sname,cname,score
from student a ,score b,course c
where a.sno = b.sno and c.cid = b.cid;
select * from v1;
索引
-
普通索引(index)
create index 索引名称 on 表名(列名); -
主键索引(primary index)
# 创建表时设置主键会自动创建主键索引
create table 表名(
....
字段 类型 primary key
) -
唯一索引(unique index)
# 方式1：创建表时设置唯一键会自动创建唯一索引
create table 表名(
....
字段 类型 unique
)
# 方式2：create unique index 索引名称 on 表名(列名) -
函数索引(function)，8.0新特性
create index 索引名称 on 表名(函数(列名));
create index idx_stu on student(upper(sname)); -
组合索引
create index 索引名称 on 表名(列名1,列名2); -
全文索引(full text)，用于长篇文章
# 创建表时使用fulltext()
create table 表名 (
....
fulltext(列名)
)
mysql(五)
课程目标：
1) 创建用户
2) 权限管理
3) 存储过程
4) 游标
用户管理
-
以root用户登录，只有管理员才有创建用户的权限。
mysql -u root -p -
查看系统中所有用户
# 切换数据库
use mysql;
# 查询user表
select user from user; -
创建新用户(8.0先创建用户再授权)
# 创建用户时未指定主机，默认%
create user test identified by '123456';
# 创建用户时指定主机
create user 'scott'@'localhsot' identified by '123456'; -
授权
# 指定详细权限(创建用户时未指定主机，默认%)
grant create on *.* to 'scott'@'%';
# 给所有权限
grant all privileges on *.* to 'scott'@'%'; -
回收权限
revoke all on *.* from 'scott'@'%'; -
删除用户
# 删除默认主机用户
drop user 'scott';
# 删除指定主机名的用户
drop user 'test'@'localhost';
mysql编程
-
变量
变量分类： 系统变量和自定义变量
# 查看系统变量
show variables;
# 查看具体变量值
select @@admin_port;
select @@version;
select @@autocommit;
mysql不能单独执行pl/sql块，必需使用过程和函数
# 自定义变量语法
decalre 变量名 数据类型;
decalre 变量名 数据类型 default 值;** 变量赋值**
# 方式一：
set @i = 0;
select @i;
# 方式二：
select * from student;
select @i := age from student where sno = 1;
# 查看自定义变量
select @i;
# 一次可以接收多个值,结果为最后个值
select @i := age from student;
select @i;
-
数据类型
mysql编程支持的数据类型：
nt,integer 32位整数. bigint 64位整数 float 32位浮点数 double 64位浮点数 decimal(precision,scale) numeric(precision,scale) 定点数 numeric通常用来保存重要的十进制数,例如货币数字
date 日期类型 datetime 日期和时间,时间精确到秒
char(length) 固定长度字符 varchar(length) 可变字符串 blob,text 最大64K长度,blob用来保存2进制数据
longblob,longtext blob和text的加长版本,存储能力达4GB
-
创建存储过程
语法：
create procedure proc_name()
begin
...
end;
过程的三个参数类型
in:输入参数
out:输出参数
in out:输入输出参数
注意与oracle中的写法不一致：
# oracle写法: 参数名 参数类型 数据类型
v_name in varchar2(20)
# mysql写法：参数类型 参数名 数据类型
in v_name varchar(20)
创建经典存储过程
# 经典过程
create procedure hello()
begin
select 'Hello World!';
end;
# 调用过程
call hello();
# 删除过程
drop procedure hello;** 无参存储过程**
delimiter $$
create procedure show_stu()
begin
select * from student;
end$$
call show_stu;有参存储过程
# 带输入参数
create procedure proc_stu
(in v_id tinyint)
begin
select * from student where sno = v_id;
end;
# 调用过程
call proc_stu(2);
# 带输出参数
delimiter $$
create procedure proc_stu2
(out v_count tinyint)
begin
select count(*) into v_count from student;
end$$
# 调用过程
call proc_stu2(@sno);
select @sno; -
流程控制语句
-
if 条件语句
create procedure proc_if()
begin
declare i int default 1;
if i>0 then
select '正数';
else
select '负数';
end if;
end;
call proc_if(); -
case end 开关语句
create procedure proc_case(in v_sno tinyint)
begin
# 声明变量接收值
declare v_sex varchar(20);
# into赋值
select sex into v_sex from student where sno = v_sno;
case v_sex
when '男' then select 1;
when '女' then select 0;
end case;
end;
call proc_case(3); -
while 循环
delimiter $$
create procedure proc_cal()
begin
declare i int default 1;#循环变量
declare total int default 0; #结果
while i<=100 do
set total=total+i;
set i=i+1;
end while;
select total;
end$$
call proc_cal(); -
loop 循环
delimiter $$
create procedure proc_cal2()
begin
declare i int default 1;
declare total int default 0;
# 标记：xxx
xxx:loop
set total=total+i;
set i =i+1;
if i>100 then
leave xxx;
end if;
end loop;
select total;
end$$
call proc_cal2();
-
-
游标
当结果集中有多个数据，需要提取其中的每行数据，使用游标。
开发步骤：
-
声明游标：declare cur_name cursor for select ...
-
打开游标：open cur_name;
-
提取数据：fetch cur_name into 变量;
-
关闭游标：close cur_name;
create procedure proc1()
begin
# 接收游标值
declare v_name varchar(20) character set utf8;
# 游标结束时的标志
declare done int default 0;
# 1.声明游标
declare mycur cursor for select sname from student;
# 指定游标循环结束时的返回值
declare continue handler for not found set done=1;
# 2. 打开游标
open mycur;
myloop:loop
# 3.提取游标数据给到变量v_name
fetch mycur into v_name;
# 退出条件
if done=1 then
leave myloop;
end if;
select v_name;
end loop;
# 4.关闭游标
close mycur;
end;
call proc1(); -
-
浙公网安备 33010602011771号