mysql笔记

一、操作数据库

mysql关键字不区分大小写

1、创建数据库

CREATE DATABASE IF NOT EXISTS `data`

2、删除数据库

DROP DATABASE IF EXISTS `data`

3、使用数据库

3.1创建数据表

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; 

3.2查看数据库

SHOW CREATE DATABASE 库名

3.3查看数据表

SHOW CREATE TABLE 表名

3.4 显示表结构

DESC 表名

4、修改数据库表

-- 修改表 alter table 旧表名 rename as 新表明
ALTER TABLE school RENAME AS student
-- 添加表字段
ALTER TABLE student ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- alter table 表名 modify 字段名 列属性[]
ALTER TABLE student MODIFY age VARCHAR(11) -- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性[]
ALTER TABLE student CHANGE age 	age1 INT(1) -- 字段重命名

-- 删除表的字段
ALTER TABLE student DROP age1

二、mysql数据库管理

1、外键（了解即可）

1.1在创建表的时候给主键增加约束

-- 创建grade表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建student表
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级id',
	 PRIMARY KEY(`id`),
    -- 关联外键
	 KEY `FK_gradeid` (`gradeid`),
	 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
    
)ENGINE=INNODB DEFAULT CHARSET=utf8

2.2在创建表后添加外键依赖关系

ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- alter table 被关联的表 add constraint 约束名 foreign key(被关联的表中作为外键的列) references 关联的表（关联的字段）

以上的操作都是物理外键，即数据库级别的外键，我们不建议使用！

2、DML语言（全部要记住）

insert

-- 插入语句（添加）
-- insert into 表名 (字段1，字段2) values ('值1','值2')
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES('1','大一')
-- 插入多行数据
INSERT INTO `grade` (`gradename`) VALUES ('大二'),('大三')

update

-- update 带指定条件
UPDATE `grade` SET `gradename`='大三' WHERE `gradeid` = 3;
-- 不带指定条件下会修改所有选项
UPDATE `grade` SET gradename = '大一'
-- 修改多个属性，逗号隔开
UPDATE `grade` SET `gradename`='大三'，`gradeid`= 4 WHERE `gradeid` = 3;

delete

delete删除问题，重启数据库，现象：

innodb 自增列会从1开始（存在内存中，断电即失）

myisam 继续从上一个自增量开始（存在文件中，不会丢失）

-- 删除数据
DELETE FROM `student` WHERE id = 1;
-- truncate 清空表
TRUNCATE `student`
-- truncate和delete的区别
-- 相同点：都能删除数据，都不会删除表结构
-- 不同点：truncate 会重新设置自增序列，计数器会归零，不会影响事务

三、DQL查询数据

（data query language:数据查询语言）

3.1指定查询字段

-- 查询所有
select * FROM student
-- 查询指定字段
select `studentno`,`studentname` from student
-- 起别名，给结果起一个名字 as(可以给字段器别民，也可以给表器别名)
select `studentno` as 学号,`studentname` as 学生姓名 from student as s
-- 函数 concat（a,b）  给查询结果拼接上字符串
select concat('姓名:',studentname) as 新名字 from student

3.2 去重 distinct

-- 发现重复数据，去重
select * from result -- 查询到全部的考试信息
select studentno from result -- 查询有哪些同学参加了考试
select distinct studetn from result -- 发现重复数据，去重

3.3 数据库的列（表达式）

select version() -- 查询系统版本
select 100*3-1 as 计算结果 -- 用来计算
select @@auto_increment_increment -- 查询自增的步长

-- 学员考试成绩 + 1分查看
select studentno，syudentresult +1 as '提分后' from result

3.4 where的运用

-- 查询考试成绩在95~100之间
select studentno,studentresult from result where studentresult>=95 and studentresult <=100
-- and &&
select studentno，studentresult from result where studentresult>=95 && studentresult <=100
-- 模糊查询（区间）
select studentno，studentresult from result where studentresult between 95 and 100
-- 除了1000号学生以外的同学的成绩
select studentno,studentresult from result where studentno != 1000
-- != not
select studentno,studentresult from result where not studentno = 1000

3.5 模糊查询

-- 查询姓刘的同学
-- 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 '%刘%' -- 名字前后都有字的
-- in 只能查询具体的值
select studentno,studentname from student where studentno in (1000,1001,1002);

3.6连表查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RtFT3b7R-1597151445539)(C:%5CUsers%5Cclsld%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5Cimage-20200805160720406.png)]

-- ============== 连表查询 join ============
-- 查询参加了考试的同学(学号，姓名，科目编号,分数)
/*
1.分析需求，分析查询的字段来自哪两个表
2.确定使用哪种连接查询？七种。确定交叉点（这两个表中哪些地方是相等的）
学生表 studentno = 成绩表 studentno
*/
SELECT s.studentno,studentname,`subject`,studentresult
FROM student s INNER JOIN result r 
WHERE s.studentno = r.studentno

-- right join
-- 即在原有的result表的基础上查找student表中有无相同的studentno，如果有将student表中拥有相同studentno的studentname提取到result表中，但是result中有没有和student表中匹配的studentno的话，那么联合查询出来的结果会有studentname字段的值为null
SELECT s.studentno,studentname,`subjectno`,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno

-- left join
-- 即在原有student表的基础上查找result表中有没有一一对应的studentno，如果有则将result中的studentresult和subjectno提取到student表中，如果student表中存在一些没有一一对应的话，那么联合查询出来的结果会有studentresult和subjectno字段的值为null
SELECT s.studentno,studentname,`subjectno`,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT studentno,stufdentname,studentresult FROM student s
LEFT JOIN result r ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- 查询了参加考试的同学的信息（学号，学生姓名，科目，分数）
-- student，result，subject三表

SELECT r.studentno，studentname，subjectname，studentresult FROM
result r LEFT JOIN student s ON s.studentno = r.studentno
LEFT JOIN `subject` sub ON sub.subjectno = r.subjectno

3.7、分页和排序

-- 分页limit和排序order by
-- 升序：asc 降序：desc
-- order by 通过那个字段排序
SELECT r.studentno，studentname，subjectname，studentresult FROM
result r LEFT JOIN student s ON s.studentno = r.studentno
LEFT JOIN `subject` sub ON sub.subjectno = r.subjectno
WHERE subjectname = '数据结构-1'
ORDER BY studentresult DESC -- 通过成绩降序排列

-- 分页，每页只显示五条数据
-- limit 0,5 表示1~5 
-- limit 1,5 表示2~6

SELECT r.studentno，studentname，subjectname，studentresult FROM
result r LEFT JOIN student s ON s.studentno = r.studentno
LEFT JOIN `subject` sub ON sub.subjectno = r.subjectno
WHERE subjectname = '数据结构-1'
ORDER BY studentresult DESC -- 通过成绩降序排列
LIMIT 0,5

3.8子查询

本质：在where语句中嵌套一个子查询语句

即 ：where（select from）

-- 查询数据库结构一的所有考试结果（学号，科目编号，成绩）降序查询

-- 方式一：使用连接查询
SELECT studentno,studentname,studentresult
FROM result r
INNER JOIN `subject` sub 
ON r.studentno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询
-- 拆开分析，查询所有数据库结构-1的学生学号
SELECT studentno 
FROM `subject` 
WHERE subjectname = '数据库结构-1'
-- 合并
SELECT studentno,studentname,studentresult
FROM result 
WHERE studentno = (SELECT studentno 
                   FROM `subject` 
                   WHERE subjectname = '数据库结构-1')
order by studentresult desc
                

四、事务

4.1什么是事务

要么都成功，要么都失败

————————

1、SQL折行 A给B转账 A 1000 ->200 B 200

2、SQL折行 B收到A的钱 A 800 ->B 400

————————

将一组SQL放在一个批次中去折行~

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中

隔离性（isolation）

事务的隔离性是多个用户并发请求数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事物之间要相互隔离

-- ============== 事务 ====================
-- MySQL是默认开启事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启（默认是开启的）
/*
步骤：
1.关闭自动提交
2.开启一个事务
3.成功就提交  失败就回滚
4.开启自动提交
*/
-- ========================================
-- 手动处理事务
set autocommit = 0
-- 事务开始
start transaction -- 标记一个事务的开始，
-- 从这个之后的SQL都在同一个事务内
insert 
insert
-- 提交：持久化（成功）
commit
-- 回滚：回到原来的样子（失败）
rollback
-- 事务结束
set autocommit = 1

savepoint 保存点名 -- 设置一个事务的保存点
rlease savepoint 保存点名 -- 撤销保存点

USE shop
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8`shop`

INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),('B',20000.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; -- 恢复默认值

五、索引

mysql官方对索引的定义为：索引是帮助mysql高效获取数据的数据结构

提取句子主干，就可以得到索引的本质：索引是数据结构

5.1索引的分类

主建索引（REIMARY KEY）

• 唯一的标识，主键不能重复，只能有一个列作为主键

唯一索引（UNIQUE KEY）

• 避免重负出现的列出现，唯一的索引可以重复，多个列都可以标识位

常规索引（KEY/INDEX）

• 默认的，index,key关键字

全文索引（FULLTEXT）

• 在特定的数据库引擎才有，mylsam

• 快速定位数据

-- 索引的使用
-- 1.在创建完表的时候给字段增加索引
-- 2.在创建完毕后，增加索引
-- 显示所有索引的信息
show index from student
-- 增加一个全文索引（索引名）列名
alter table school.student add fulltext index studentname (studentname);
-- explain 分析SQL折行的状况
explain select * from student
explain select * from student where match(studentname) against('刘');

5.2测试索引