一、操作数据库
文章目录
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('刘');