mysql基础
1 mysql安装
本演示使用的是mysql 5.7.19，下载网址：mysql。如下图：
mysql 5.7.19依赖组件：Visual Studio 2013 Redistributable。需要先安装这个组件再安装mysql，组件下载地址：网址。如下图：
mysql安装参考网址：win10安装mysql。
在配置mysql时，注意将mysql的服务名改为mysql，如下图（后面启动或者停止服务会用到这个名字）：
2 mysql服务的启动与关闭
- 注意
cmd需要以管理员的身份运行
# 关闭mysql服务:
net stop mysql
# 启动mysql服务：
net start mysql
3 数据库的连接
前提：要保证mysql服务已经被启动，可以在任务管理器中查看，如下图：
3.1 命令行连接
- 使用
cmd连接数据库
# 连接数据库：123456是密码
mysql -u root -p123456
# 退出连接
exit
3.2 使用SQLyog连接
- SQLyog 是一个快速而简洁的图形化管理
mysql数据库的工具。
- 如下图，是连接后的界面：
4 数据库的基本操作
-- 修改密码：
set password for root@localhost = password('123456');
-- 删除数据库
drop database if exists `school`;
-- 创建数据库：
create database `school` character set utf8 collate utf8_general_ci;
create database if not exists `school`;
-- 查看所有的数据库：
show databases;
-- 设置当前操作的数据库：
use school;
-- 显示数据库(默认使用钢材设置的数据库)中的表：
show tables;
-- 查看school数据库中表的字段信息：
describe student; -- 没有创建student表，会报错
-- 查看用户信息
-- mysql5.7.19中password字段被删除了，替换为authentication_string, 可以用过下句话改为password
-- alter table mysql.user change authentication_string password varchar(100);
select user, host, password from mysql.user;
-- 刷新权限：
flush privileges;
-- 查看系统版本：
select version();
-- SQL的单行注释：
--
-- SQL的多行注释：
/* */
5 mysql基础知识
- 数据库语言分类
DDL : 数据定义语言 (Data Definition Language)
DML : 数据操作语言 (Data Manipulation Language)
DQL : 数据查询语言 (Data Query Language)
DCL : 数据控制语言 (Data Control Language)
-
mysql数据库不区分大小写。 -
数据库帮助命令：
[]表示可选内容，{}表示必选内容，如下图：
- 数据库中的表字段类型：
数值：
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节(常用)
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 金融计算的时候，使用这个
字符串：
char 固定大小的字符串 0~255
varchar 可变字符串 0~65535(常用)
tinytext 微型文本 0~255
text 文本串 0~65535(保存大文本)
时间日期：
date 日期格式 YYYY-MM-DD
time 时间格式 HH:mm:ss
datetime 时间格式格式 YYYY-MM-DD HH:mm:ss(常用)
timestamp 时间戳 1970.1.1到现在的毫秒数(常用)
year 年份表示
null：没有值；注意，不要使用null进行运算，即使运算了结果也为null
- 数据库中的表字段属性：
Unsigned:
无符号的整数
声明了该属性的列的取值不能为负数
zerofill:
0填充
不足的位数，使用0填充，int(3), 5 --> 005
自增：
自动在上一条记录的基础上+1(默认)
通常用来设计唯一的主键，必须是整数类型
可以自定义设计主键的初始值和步长
非空：null / not null
假设设置为 not null, 如果不给它赋值，就会报错
假设设置为 null, 不填写值，默认就是null
默认：
设置默认的值
如果不指定该列的值，则会填充默认的值
- 每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在的意义！（这里是学习，可以暂时不用）
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
mysql引擎对比
INNODB 默认使用
安全性高，事务的处理，多表多用户操作
MYISAM 早些年使用的
节约空间，速度较快
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大，约为2倍
所有的数据库文件都存在data目录下：C:\ProgramData\MySQL\MySQL Server 5.7\Data
这个文件夹里面的每一个文件夹就对应一个数据库，本质山MySQL还是文件的存储
MySQL引擎在物理文件上的区别：
InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
MYISAM对应文件：
*.frm 表结构的定义文件
*.MYD 数据文件(data)
*.MYI 索引文件(index)
mysql字符集
设置数据库表的字符集编码：charset=utf8
不设置的话会使用MySQL的默认编码(不支持中文)
MySQL的默认编码是Latin1，不支持中文
如果想要支持中文，有两种方式：
(1) 创建表的时候加上 : charset=utf8
(2) 修改MySQL的配置文件(my.ini) : character-set-server=utf8
mysql数据库备份
为什么需要备份？
(1) 保证重要数据不丢失
(2) 数据转移
MySQL数据库备份的方式：
(1) 直接拷贝物理文件
(2) 在SQLyog这类可视化数据中手动导出
在想要导出的表或者库中，右键，选择备份或导出
(3) 使用命令导出 mysqldump 命令行使用
格式：mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘
例子：mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
格式：mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘
例子：mysqldump -hlocalhost -uroot -p123456 school student result > D:/b.sql
格式：mysqldump -h主机 -u用户名 -p密码 数据库 > 物理磁盘
例子：mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql
根据sql文件导入数据：
第一种方式：
(1) 登陆情况下，切换到指定数据库：
use school
(2) 格式：source 备份文件
例子：source d
第二种方式：
mysql -u用户名 -p密码 库名 < 备份文件
- 数据库三范式
数据库三大范式(规范数据库设计)：
(1) 第一范式(1NF)
原子性：保证每一列不可再分
(2) 第二范式(2NF)
前提：满足第一范式
每张表只描述一件事情
(3) 第三范式(3NF)
前提：满足第一范式和第二范式
确保数据表中每一列数据都与主键直接相关，而不能间接相关
规范性 与 性能 的问题：
阿里规约：关联查询的表不得超过三张表
(1) 考虑商业化的需求和目标，（成本，用户体验），数据库性能更加重要
(2) 早考虑性能的同时，需要适当考虑一下 规范性
(2) 故意给某些表增加一些冗余的字段。（从多表查询变为单表查询）
(2) 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
6 数据库定义语言DDL
-
数据定义语言：
Data Definition Language，简称DDL。 -
数据定义语言
DDL用来创建数据库中的各种对象 ----- 表、视图、索引、同义词、聚簇等如：create table/view/index/syn/cluster
/*
DDL : 数据定义语言
create\drop\alter
create table:
create table [if not exists] `表名` (
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释];
*/
-- 1.目标：创建一个 school 数据库
-- 2.创建学生表（列，字段），使用 SQL 创建
-- 学号int, 登陆密码varchar(20), 姓名, 性别varchar(2), 出生日期datetime, 家庭住址, 邮箱
-- 1. 创建数据库
create database if not exists `school`;
use `school`;
-- 2.
/*
注意点：
(1) 使用英文括号，表的名字 和 字段 尽量使用 `` 括起来
(2) auto_increment 自增
(3) 字符串使用单引号括起来
(4) 所有的语句后面加英文逗号，最后一个不用加
(5) 主键，一般一个表只有一个
*/
create table if not exists `student` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8;
-- 删除某数据库中的表
drop table if exists `student`;
-- =================================================================
-- 查看创建数据库的语句
show create database `school`;
-- 查看创建表的语句
show create table `student`;
-- 显示表的结构
desc student;
-- =================================================================
-- 修改表名
alter table student rename as students;
alter table students rename as student;
-- 增加表的字段
alter table student add age int(11);
-- 修改表的字段(重命名，修改约束)
alter table student modify age varchar(11); -- 修改约束
alter table student change age age1 int(3); -- 字段重命名
-- 删除表的字段
alter table student drop age1;
7 数据库操作语言DML
-
数据操作语言：
Data Manipulation Language，简称DML。 -
数据操纵语言
DML主要有三种形式：（1）插入：inset；（2）更新：update；（3）删除：delete。
/*
DML : 数据操作语言
insert / delete / update
insert : insert into 表名[(字段1, 字段2, ...)] values ('值1', '值2', ...), ('值1', '值2', ...);
(1) 字段和字段之间使用英文逗号隔开
(2) 字段是可以省略的，但是后面的值必须要一一对应，不能少
(3) 可以同时插入多条数据，values后面的值，需要使用英文逗号隔开
update : update 表名 set column_name=value[,column_name=value,...] [where 条件];
(1) column_name是数据库的列名，尽量带上``
(2) 筛选的条件如果没有加上，则会修改所有的列
(3) value，可以是一个具体的值，也可以是一个变量
(4) 多个需要修改的属性之间，用英文逗号隔开
*/
-- 准备
drop table if exists `grade`;
drop table if exists `student`;
create table if not exists `grade` (
`gradeId` int(10) not null auto_increment comment '学生的年级',
`gradeName` varchar(50) not null comment '年级名称',
primary key (`gradeId`)
)engine=innodb default charset=utf8;
create table if not exists `student` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8;
-- ================================insert=================================
insert into `grade`(`gradeName`) values('大一');
-- 由于主键自增，我们可以省略主键(如果不写表的字段，会从前到后一一匹配)
-- 一般写插入语句，我们一定要数据和字段一一对应!
insert into `grade` values('2', '大二');
-- 插入多条数据
insert into `grade` values('3', '大三'), ('4', '大四');
insert into `student`(`name`, `pwd`, `sex`, `birthday`)
values ('张三', 'abcdef', '男', '2000-01-01 17:05:20'),
('李四', '123456', '男', '1999-01-01'),
('王五', '123321', '男', '2001-01-01');
-- ================================update=================================
-- 修改学员的名字
update `student` set `name`='wxx' where `id`=1;
-- 修改所有人的名字
-- update `student` set `name`='someone';
-- 修改多个属性
update `student` set name='kuang', email='24736743@qq.com' where id=1;
update `student` set `birthday`=current_date where id=1;
-- ================================delete=================================
-- 删除数据(避免这样写，会全部删除)
-- delete from `student`;
-- 删除指定数据
delete from `student` where id=1;
-- 完全清空一个数据库表，表的结构和索引约束不会变
truncate `student`;
/*
delete vs. truncate
相同点：都能删除数据，清空一张表
不同点：
truncate :
(1) 重新设置自增列，计数器归零
(2) 不会影响事务
delete :
(1) 不会重新设置自增列，计数器不归零
但要注意，如果使用 InnoDB 断电后自增会重新从1开始(存在内存中，断电即失)；
如果使用 MYISAM 断电后自增会继续上一个自增量开始(存在文件中，不会丢失)
*/
8 数据库查询语言DQL
-
数据查询语言：
Data Query Language，简称DQL。 -
数据查询语言
DQL基本结构是由select子句，from子句，where子句等组成的查询块：select <字段名表> from <表或视图名> where <查询条件>。 -
准备工作：
/*
DQL连续需要的准备工作
*/
-- ================================创建数据库=================================
create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
-- ================================创建学生表，并添加数据=================================
drop table if exists `student`;
create table `student`(
`StudentNo` int(4) not null comment '学号',
`LoginPwd` varchar(20) default null,
`StudentName` varchar(20) default null comment '学生姓名',
`Sex` tinyint(1) default null comment '性别，0或1',
`GradeId` int(11) default null comment '年级编号',
`Phone` varchar(50) not null comment '联系电话，允许为空',
`Address` varchar(255) not null comment '地址，允许为空',
`BornDate` datetime default null comment '出生时间',
`Email` varchar (50) not null comment '邮箱账号允许为空',
`IdentityCard` varchar(18) default null comment '身份证号',
primary key (`StudentNo`),
unique key `IdentityCard`(`IdentityCard`),
key `email` (`Email`)
)engine=myisam default charset=utf8;
-- 插入学生数据 其余自行添加 这里只添加了2行
insert into `student` (`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-11-21','text111@qq.com','123456199001011233'),
(1002,'123456','刘洋',1,3,'13800002222','辽宁大连','1995-1-9','text111@qq.com','123456199001011234'),
(1003,'123456','刘洋洋',0,3,'13800002222','浙江杭州','1998-9-1','text111@qq.com','123456199001011239'),
(1004,'123456','赵杰',1,3,'13800002222','广东深圳','1990-10-10','text111@qq.com','123456199001011264'),
(1005,'123456','刘丽霞',0,2,'13800002222','广东佛山','1994-11-11','text111@qq.com','123456199001016833'),
(1006,'123456','仓央嘉措',0,2,'13800002222','安徽合肥','1994-5-1','text111@qq.com','123456199405016892');
-- ================================创建年级表，并添加数据=================================
drop table if exists `grade`;
create table `grade`(
`GradeId` int(11) not null auto_increment comment '年级编号',
`GradeName` varchar(50) not null comment '年级名称',
primary key (`GradeId`)
) engine=innodb auto_increment = 6 default charset = utf8;
-- 插入年级数据
insert into `grade` (`GradeId`,`GradeName`)
values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- ================================创建科目表，并添加数据=================================
-- 创建科目表
drop table if exists `subject`;
create table `subject`(
`SubjectNo`int(11) not null auto_increment comment '课程编号',
`SubjectName` varchar(50) default null comment '课程名称',
`ClassHour` int(4) default null comment '学时',
`GradeId` int(4) default null comment '年级编号',
primary key (`SubjectNo`)
)engine = innodb auto_increment = 19 default charset = utf8;
insert into `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeId`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
-- ================================创建成绩表，并添加数据=================================
-- 创建成绩表
drop table if exists `result`;
create table `result`(
`StudentNo` int(4) not null comment '学号',
`SubjectNo` int(4) not null comment '课程编号',
`ExamDate` datetime not null comment '考试日期',
`StudentResult` int (4) not null comment '考试成绩',
key `SubjectNo` (`SubjectNo`)
)engine = innodb default charset = utf8;
-- 插入成绩数据 这里仅插入了一组，其余自行添加
insert into `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58),
(1001,4,'2013-11-11 16:00:00',85),
(1002,3,'2013-11-12 16:00:00',72),
(1003,2,'2013-11-11 09:00:00',64),
(1004,5,'2013-11-13 16:00:00',96),
(1005,1,'2013-11-14 16:00:00',60);
- 例子：
/*
DQL : 数据查询语言 select
(1) 所有的查询都要用到它
(2) 简单的查询，复杂的查询，它都能做
(3) 数据库中最核心的语言
(4) 使用频率最高的语句
select 表达式 from 表
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量......
select [all | distinct]
{* | table.* | field1 [as alias1], field2 [as alias2], ...}
from table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[where ...] -- 指定结果满足的调价
[group by ...] -- 指定结果按照哪几个字段来分组
[having ...] -- 过滤分组的记录必须满足的次要条件
[order by ...] -- 指定查询记录按照一个或者多个条件排序
[limit {[offset,] row_count}] -- 指定查询的记录从哪条至哪条
*/
-- 准备，执行util包下的school.sql文件即可
-- =================================================================
use `school`;
-- 查询全部的学生
select * from `student`;
-- 查询指定字段
select `StudentNo`, `StudentName` from `student`;
-- 别名：给结果起一个别名 as，也可以给表起一个别名
select `StudentNo` as '学号', `StudentName` as '学生姓名' from `student` as s;
-- 函数，例如 concat
select concat('姓名 : ', `StudentName`) as '新名字' from `student`;
-- 去重，查询哪些同学参加了考试
select distinct `StudentNo` from `result`;
-- ================================一些特殊查询=================================
-- 查看系统版本(函数)
select version();
-- 用于计算(表达式)
select 100*3+2 as 'count';
-- 查询自增的步长(变量)
select @@auto_increment_increment;
-- 查看当前的隔离级别(变量)
SELECT @@tx_isolation; -- REPEATABLE-READ
-- ================================查询结果后进行操作=================================
-- 将所有学员成绩 +1分，查看
select `StudentNo`, `StudentResult`+1 as '提分后' from `result`;
-- ================================where子句：逻辑运算符=================================
-- 查询考试成绩在 85~100 之间的。and 也可以用 && 代替, 或者使用between...and...
select `StudentNo`, `StudentResult` from `result`
where `StudentResult`>=85 and `StudentResult`<=100;
select `StudentNo`, `StudentResult` from `result`
where `StudentResult`>=85 && `StudentResult`<=100;
select `StudentNo`, `StudentResult` from `result`
where `StudentResult` between 85 and 100;
-- 查询除了1000号学生之外的学生的成绩
select `StudentNo`, `StudentResult` from `result`
where `StudentNo`!=1000;
select `StudentNo`, `StudentResult` from `result`
where not `StudentNo`=1000;
-- ================================where子句：模糊查询=================================
/*
where子句(返回boolean值)：模糊查询
运算符 语法 描述
is null a is null 如果a为null, 结果为真
is not null a is not null 如果a不为null, 结果为真
between a between b and c 如果a在b和c之间，则结果为真
like a like b SQL匹配，如果a匹配b, 则结果为真
in a in (a1, a2,...) 假设a在(a1, a2,...)之中，结果为真
*/
-- ----------------------like--------------------------
-- like 结合，%(代表0到任意个字符) _(代表一个字符)
-- 查询姓刘的同学
select `StudentNo`, `StudentName` from `student`
where `StudentName` like '刘%';
-- 查询姓刘的同学，名字是二个字的
select `StudentNo`, `StudentName` from `student`
where `StudentName` like '刘_';
-- 查询姓刘的同学，名字是三个字的
select `StudentNo`, `StudentName` from `student`
where `StudentName` like '刘__';
-- 查询名字中有嘉字的同学
select `StudentNo`, `StudentName` from `student`
where `StudentName` like '%嘉%';
-- ----------------------in--------------------------
-- 查询 1001,1002,1003号学员
select `StudentNo`, `StudentName` from `student`
where `StudentNo` in (1001, 1002, 1003);
-- 查询在北京朝阳和安徽合肥的学生
select `StudentNo`, `StudentName` from `student`
where `Address` in ('北京朝阳', '安徽合肥');
-- ----------------------null / is null--------------------------
-- 查询地址为空的学生 null ''
select `StudentNo`, `StudentName` from `student`
where `Address`='' or `Address` is null;
-- 查询有出生日期的同学 不为空
select `StudentNo`, `StudentName` from `student`
where `BornDate` is not null;
-- ================================联表查询join=================================
-- 查询参加了考试的同学(学号，姓名，科目编号，分数) 两个表
/*
思路：
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定不同表的的交叉点(两个表中哪个数据是相同的)
针对本问题：student 表中的 StudentNo = result 表中的 StudentNo
join on:
join 连接的表 on 判断的条件
连接查询
where:
等值查询
*/
-- inner join
select s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
from student as s
inner join result as r
where s.StudentNo = r.StudentNo;
-- right join
select s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
from student s
right join result r
on s.StudentNo = r.StudentNo;
-- left join
select s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
from student s
left join result r
on s.StudentNo = r.StudentNo;
-- 查询缺考的同学
select s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
from student s
left join result r
on s.StudentNo = r.StudentNo
where `StudentResult` is null;
-- 查询学员所属的年级(学号，姓名，年级)
select `StudentNo`, `StudentName`, `GradeName`
from `student` s
inner join `grade` g
on s.GradeId = g.GradeId;
-- 查询科目所属的年级(科目名称，年级名称)
select `SubjectName`, `GradeName`
from `subject` sub
inner join `grade` g
on sub.`GradeId`=g.`GradeId`;
-- 查询参加了考试的同学(学号，姓名，科目名，分数) 三个表
-- 核心思想：两个表查询后的结果可以看成一个表
select s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
inner join subject as sub
on r.`SubjectNo`=sub.`SubjectNo`;
-- 查询参加了 高等数学-1 考试的同学(学号，姓名，科目名，分数) 三个表
select s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
inner join subject as sub
on r.`SubjectNo`=sub.`SubjectNo`
where `SubjectName`='高等数学-1';
-- 查询 高等数学-1 课程成绩排名前10的学生，并且分数要大于80的学生信息(学号，姓名，课程名称，分数)
select s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
from `student` s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
inner join `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
where `SubjectName`='高等数学-1'
order by `StudentResult` desc
limit 0,10;
-- ================================自连接=================================
-- 准备
drop table if exists `category`;
create table `category` (
`categoryId` int(10) unsigned not null auto_increment comment '主题id',
`parentId` int(10) not null comment '父id',
`categoryName` varchar(50) not null comment '主题名字',
primary key (`categoryId`)
) engine=innodb auto_increment=9 default charset=utf8;
insert into `category` (`categoryId`, `parentId`, `categoryName`)
values ('2', '1', '信息技术'),
('3', '1', '软件开发'),
('4', '3', '数据库'),
('5', '1', '美术设计'),
('6', '3', 'web开发'),
('7', '5', 'ps技术'),
('8', '2', '办公信息');
/*
核心：一张表拆为两张一样的表即可、
父表：
categoryId categoryName
2 信息技术
3 软件开发
5 美术设计
子类：
parentId categoryId categoryName
2 8 办公信息
3 4 数据库
3 6 web开发
5 7 美术设计
操作：查询父类对应的子类关系
-父类- -子类-
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术
*/
select a.`categoryName` as '父栏目', b.`categoryName` as '子栏目'
from `category` as a, `category` as b
where a.`categoryId`=b.`parentId`;
-- ================================排序order by=================================
-- 将查询的结果进行排序
-- 升序 asc; 降序 desc
-- order by 待排序的字段 排序策略
select s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
from student as s
inner join result as r
where s.StudentNo = r.StudentNo
order by `StudentResult` desc;
-- ================================分页limit=================================
/*
为什么需要分页？
(1) 缓解数据库压力
(2) 给人的体验更好
*/
-- 语法：limit 起始值, 页面的大小
select s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
from student as s
inner join result as r
where s.StudentNo = r.StudentNo
order by `StudentResult` desc
limit 0,5;
/*
第一页 limit 0,5 (1-1)*5
第二页 limit 5,5 (1-1)*5
第三页 limit 10,5 (1-1)*5
第n页 limit (n-1)*5,5 (n-1)*pageSize, pageSize
pageSize : 页面大小
(n-1)*pageSize : 起始值
n : 当前页数
总页数 = 数据总数 / 页面大小 + 1
*/
-- ================================子查询=================================
-- 1.查询 高等数学-1 的所有考试结果(学号，科目编号，成绩)，降序排列
-- 方式一：使用连接查询
select `StudentNo`, r.`SubjectNo`, `StudentResult`
from `result` r
inner join `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
where `SubjectName`='高等数学-1'
order by `StudentResult` desc;
-- 方式二：使用子查询(查询过程：由里及外)
select `StudentNo`, `SubjectNo`, `StudentResult`
from `result` where `SubjectNo`=(
select `SubjectNo` from `subject` where `SubjectName`='高等数学-1'
);
-- 2.查询课程为 高等数学-1 且分数不小于 80 的同学的学号和姓名
-- 方式一：使用连接查询
select s.`StudentNo`, `StudentName`
from `student` s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
inner join `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
where `SubjectName`='高等数学-1' and `StudentResult`>=80;
-- 方式二：使用子查询(查询过程：由里及外)
select `StudentNo`, `StudentName` from `student` where `StudentNo` in (
select `StudentNo` from `result` where `StudentResult`>=80 and `SubjectNo`=(
select `SubjectNo` from `subject` where `SubjectName`='高等数学-1'
)
);
-- 方式三：使用连接和子查询(查询过程：由里及外)
select s.`StudentNo`, `StudentName`
from `student` s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
where `StudentResult`>=80 and `SubjectNo`=(
select `SubjectNo` from `subject` where `SubjectName`='高等数学-1'
);
-- ================================MySQL函数：常用函数(也不是那么常用)=================================
/*
官网：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
MySQL字符串的下标都是从1开始的
*/
-- 数学运算
select abs(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); -- 向下取整
select rand(); -- 返回一个在0~1之间的随机数[0~1)
select sign(10.1); -- 判断一个数的符号，0:0，负数返回-1，正数返回1
-- 字符串函数
select char_length('即使再小的帆也能远航'); -- 字符串长度
select concat('我', '爱', '你'); -- 拼接字符串
select insert('我爱编程', 1, 2, '超级热爱'); -- 从某个位置开始用 '超级热爱' 替换 '我爱编程' 指定长度
select lower('I love you!'); -- 转为小写字母
select upper('I love you!'); -- 转为大写字母
select instr('I love you!', 'ov'); -- 返回第一次出现的字符串的索引
select replace('坚持就能成功!', '坚持', '努力'); -- 体现出现的指定字符串
select substr('坚持就能成功!', 1, 4); -- 返回指定的子字符串(源字符串, 截取的位置, 截取的长度)
select reverse('I love you!'); -- 翻转字符串
-- 查询姓 刘 的同学，更改姓为 柳
select replace(`StudentName`, '刘', '柳') from `student`
where `StudentName` like '刘%';
-- 时间日期函数
select current_date(); -- 获取当前日期, 精确到日
select curdate(); -- 获取当前日期, 精确到日
select now(); -- 获取当前日期, 精确到秒
select localtime(); -- 获取本地时间, 精确到秒
select sysdate(); -- 获取系统时间, 精确到秒
select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());
-- 系统
select system_user(); -- 获取当前系统用户
select user(); -- 获取当前系统用户
select version(); -- 获取数据库版本
-- ================================MySQL函数：聚合函数=================================
-- 下面三种写法都能统计表中的数据，有什么区别？
select count(`StudentName`) from `student`; -- count(字段)，如果某个字段为null，则不会被记入
select count(*) from `student`; -- count(*), 如果某个字段为null，会被记入；本质：计算行数
select count(1) from `student`; -- count(1), 如果某个字段为null，会被记入；本质：计算行数
select sum(`StudentResult`) as '总和' from `result`;
select avg(`StudentResult`) as '平均分' from `result`;
select max(`StudentResult`) as '最高分' from `result`;
select min(`StudentResult`) as '最低分' from `result`;
-- 查询平均分大于 60 的课程的平均分，最高分，最低分
select `SubjectName`, avg(`StudentResult`) as '平均分', max(`StudentResult`), min(`StudentResult`)
from result r
inner join `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
group by r.SubjectNo -- 分类的字段
having `平均分`>=60;
-- ================================扩展：MD5加密=================================
drop table if exists `testmd5`;
create table `testmd5` (
`id` int(4) not null comment '主键',
`name` varchar(20) not null comment '名字',
`pwd` varchar(50) not null comment '密码',
primary key (`id`)
)engine=innodb default charset=utf8;
-- 插入明文密码
insert into `testmd5` values (1, 'zs', '123456');
-- 插入加密后的密码
insert into `testmd5` values (2, 'ls', MD5('123456'));
-- 查询加密后的用户信息
select * from `testmd5` where `id`=2 and `pwd`=MD5('123456');
9 数据控制语言DCL
-
数据控制语言：
Data Control Language，简称DCL。 -
数据控制语言
DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等，如grant，rollback等。
/*
用户管理
用户表：mysql.user
本质：对 mysql.user 进行增删改查
*/
-- 创建用户
-- create user 用户名 identified by '密码'
create user wxx identified by '123456';
-- 修改密码(修改当前用户密码)
set password = password('123456');
-- 修改密码(修改指定用户密码)
set password for wxx = password('123456');
-- 重命名
-- rename user 原来名字 to 新名字
rename user wxx to wxx2;
rename user wxx2 to wxx;
-- 用户授权 all privileges 全部的权限
-- all privileges 除了grant的权限，其他的权限都能赋予
grant all privileges on *.* to wxx;
grant all privileges on *.* to wxx with grant option; -- 授予全部权限，包括grant权限
-- 查询指定用户的权限
show grants for wxx;
show grants for root@localhost;
-- 撤销权限
-- revoke 哪些权限 on 在哪个库那个表撤销 from 用户
revoke all privileges on *.* from wxx;
-- 删除用户
drop user wxx;
10 表的外键
- 外键：表
A的主键在表B中被使用了，那么A的这个主键称为B的外键。删除表B前必须先删除A。
/*
外键
删除有外键关系的表的时候，必须要先删除引用别人的表(从表，例如下面的student),再删除被引用的表(主表，例如下面的grade)
以下的操作都是物理外键，数据库级别的外键，不建议使用(避免数据库过多造成困扰)，以下是推荐方式：
最佳实践：
数据库就是单纯的表，只用来存储数据，只有行(数据)和列(字段)
我们想使用多张表的数据，在代码层面去实现(比如Java层面)
*/
-- 删除某数据库中的表
drop table if exists `student`;
drop table if exists `grade`;
create table if not exists `grade` (
`gradeId` int(10) not null comment '学生的年级',
`gradeName` varchar(50) not null comment '年级名称',
primary key (`gradeId`)
)engine=innodb default charset=utf8;
-- ================================第一种创建外键的方式=================================
-- 学生表的 gradeId 字段 要求引用年级表的 gradeId
-- 定义外键key
-- 给这个外键添加约束(执行引用) references 引用
create table if not exists `student` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
`gradeId` int(10) not null comment '学生的年级',
primary key(`id`),
key `FK_gradeId` (`gradeId`),
constraint `FK_gradeId` foreign key (`gradeId`) references `grade`(`gradeId`)
)engine=innodb default charset=utf8;
-- ================================第二种创建外键的方式=================================
-- 创建表之后再添加约束
-- 创建表的时候没有外键关系
create table if not exists `student` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
`gradeId` int(10) not null comment '学生的年级',
primary key(`id`)
)engine=innodb default charset=utf8;
-- 创建表之后，给student表增加外键
alter table `student`
add constraint `FK_gradeId` foreign key (`gradeId`) references `grade`(`gradeId`);
11 数据库索引
- 索引：建立索引的目的是为了加快查询速度。
/*
索引：索引是帮助MySQL高效获取数据的数据结构。
索引的分类：
(1) 主键索引(PRIMARY KEY)
唯一的标识，主键不可重复，只能有一个列作为主键
(2) 唯一索引(UNIQUE KEY)
避免重复的列出现，唯一索引可重复，多个列都可以标识为唯一索引
(3) 常规索引(KEY / INDEX)
默认的
(4) 全文索引(FullText)
在特定的数据库引擎下才有，MYISAM，最新的INNODB也有
快速定位数据
索引的使用：
(1) 在创建表的时候给字段增加索引
(2) 创建完毕后，增加索引
对所有讲解的极好的博客：
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
索引在小数据量的时候用处不大，但是在大数据的时候，区别十分明显
索引原则：
(1) 索引不是越多越好
(2) 不要对经常变动的数据加索引
(3) 小数据量的表不需要添加索引
(4) 索引一般加在常用的查询字段上
索引的数据结构：
(1) Hash类型的索引
(2) Btree : Innodb的默认数据结构
*/
-- ================================关于所有的一些命令=================================
-- 切换数据库
use `school`;
-- 显示所有的索引信息
show index from `student`;
-- 给字段增加索引(给 student 表中的字段 StudentName 字段增加全文索引)
alter table `student` add fulltext index `StudentNameIndex`(`StudentName`);
-- explain 分析sql执行的状况
explain select * from `student`; -- 非全文索引
explain select * from `student` where match(`StudentName`) against('刘');
-- ================================索引测试=================================
-- 删除 school 中的表 app_user
drop table if exists `app_user`;
-- 创建表
create table `app_user` (
`id` bigint(20) unsigned not null auto_increment comment '主键',
`name` varchar(50) default ''comment '用户昵称',
`email` varchar(50) not null comment '用户邮箱',
`phone` varchar(20) default '' comment '手机号',
`gender` tinyint(4) unsigned default '0' comment '性别（0：男；1：女）',
`password` varchar(100) not null comment '密码',
`age` tinyint(4) default '0' comment '年龄',
primary key (`id`)
)engine = innodb default charset = utf8 comment 'app用户表';
-- 插入100万条数据
-- 定义函数
delimiter $$ -- 写函数之前必须要写，标志
create function mock_data()
returns int
begin
declare num int default 1000000;
declare i int default 0;
while i < num do
insert into `app_user`(`name`, `email`, `phone`, `gender`, `password`, `age`)
values (
concat('用户', i),
'1137247975@qq.com',
concat('13', floor(rand()*(999999999 - 100000000)+100000000)),
floor(rand()*2),
uuid(),
floor(rand()*100)
);
set i = i + 1;
end while;
return i;
end;
-- 执行函数
select mock_data();
-- 查询某个用户
select * from `app_user` where `name`='用户9999'; -- 未创建索引：1s多；创建索引后：30ms左右
-- 分析sql
explain select * from `app_user` where `name`='用户9999';
-- 创建索引 (命名：id_表名_字段名)
-- create index 索引名 on 表(字段)
create index `id_app_user_name` on `app_user`(`name`);
-- 删除 app_user 表
drop table if exists `app_user`;
12 数据库事务
- 事务是为了保证一个操作序列要么全部执行，要么全部不执行
/*
事务：要么都成功，要么都失败
结合 _0000_study\_db\_jdbc\senior\transaction\description.txt 一起查看
事务的原则：ACID
原子，一致，隔离，持久
原子性：要么都成功，要么都失败
一致性：事务前后的数据完整性要保证一致
持久性：事务一旦提交则不可逆，被持久化到数据库中
隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不会被其他事务的操作数据所干扰，多个并发事物之间要相互隔离
隔离所导致的一些问题：
脏读：指一个事务读取了另外一个事务未提交的数据
不可重复读：在一个事务内读取某一行数据，多次读取结果不同。(这个不一定是错误，只是某些场合不对)
虚读(幻读)：是指在一个事务内读取到别的事务插入的数据，导致前后读取不一致
数据库提供的4种事务隔离级别：read uncommited、read commited、repeatable read、serializable
- Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: repeatable read。
- Mysql 是默认开启事务自动提交的。
*/
-- ================================事务命令=================================
# -- 查询事务级别
# select @@tx_isolation;
#
# -- 设置事务级别
# set global transaction isolation level read committed; -- 设置全局事务
# set session transaction isolation level repeatable read ; -- 设置session事务
#
# -- 关闭和开启事务
# set autocommit = 0; -- 关闭事务
# set autocommit = 1; -- 开启事务
#
# -- 开启事务
# start transaction; -- 开启事务
#
# -- 保存点
# savepoint myPoint; -- 设置一个事务的保存点
# rollback to savepoint myPoint; -- 回滚到保存点
# release savepoint myPoint; -- 撤销保存点
#
#
# -- 提交事务：持久化
# commit;
# -- 回滚
# rollback;
-- ================================事务：转账例子=================================
-- 删除数据库
drop database if exists `shop`;
-- 创建数据库
create database if not exists `shop` character set utf8 collate utf8_general_ci;
-- 使用数据库
use `shop`;
-- 删除表
drop table if exists `account`;
-- 在选中的数据库中创建表
create table `account` (
`id` int(3) not null auto_increment comment '主键',
`name` varchar(30) not null comment '姓名',
`money` decimal(9, 2) not null comment '账户余额',
primary key (`id`)
)engine=innodb default charset=utf8;
-- 向表中插入数据
insert into `account`(`name`, `money`)
values ('A', 5000.00),
('B', 10000.00);
-- 模拟事务，转账
set autocommit = 0; -- 关闭自动提交
start transaction; -- 开启一个事务
-- 转账
update `account` set `money`=`money`-500 where `name`='A'; -- A减500
update `account` set `money`=`money`+500 where `name`='B'; -- B加500
-- 成功则提交事务
commit;
-- 错误则回滚事务
rollback;
-- 开启事务自动提交(即恢复默认值)
set autocommit = 1;
1728
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
