Mysql数据库
一、什么是Mysql
Mysql是一个关系型数据库管理系统,属于Oracle旗下。
二、数据库三大范式
1.原子性 保证每个字段都是不可拆分的最小单元
2.在第一范式的基础上,确保其他字段都与主键有关
3.在确保第二范式的基础上,如果多表之间有关联,确只需要存从表的外键
在设计数据库的时候。要尽量遵守三大范式。但是在实际开发过程中,需要考虑性能等方面的原因,只能进行妥协。
三、Mysql数据类型
四、sql语句
4.1数据定义语言(DDL) create、alter、drop
-- 数据定义语言(DDL)
-- 一、数据库相关
-- 1.创建数据库
CREATE DATABASE mysqltest;
-- 2.创建数据库设置字符集
CREATE DATABASE mysqltest1 CHARACTER SET UTF8;
-- 3.查看所有数据库
SHOW DATABASES;
-- 4.删除数据库
DROP DATABASE mysqltest1;
-- 5.切换数据库
USE mysqltest;
-- 表相关
-- 1.创建表
CREATE TABLE student(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
English INT,
Chinese INT,
Math INT
);
-- 2.查看数据库中所有表
SHOW TABLES;
-- 3.查看表结构
DESC student;
-- 4.删除表
DROP TABLE student;
-- 5.修改表
-- 5.1添加列
ALTER TABLE student ADD sex INT NOT NULL;
-- 5.2修改列的类型、长度、约束
ALTER TABLE student MODIFY sex DOUBLE(3,2);
-- 5.3修改列名
ALTER TABLE student CHANGE sex money INT NOT NULL;
-- 5.4修改表名
RENAME TABLE student TO stu;
-- 5.5修改表的字符集编码
ALTER TABLE stu CHARACTER SET GBK;
-- 5.6删除列
ALTER TABLE stu DROP money;
4.2数据操纵语言(DML) insert、update、delete
-- 数据操纵语言(DML)
-- 1.数据插入 INSERT
INSERT INTO stu(id,NAME,English,Chinese,Math) VALUE(1,'张三',90,90,90);
INSERT INTO stu(id,NAME,English,Chinese) VALUE(2,'李四',90,90);
INSERT INTO stu VALUE(3,'王五',90,90,90);
-- 2.修改数据 UPDATE
UPDATE stu SET English = 99;
UPDATE stu SET English = 80 WHERE id = 3;
-- 3.删除数据 DELETE
DELETE FROM stu WHERE id = 2;
DELETE FROM stu;
-- 4.删除数据 TRUNCATE
TRUNCATE TABLE stu;
DELETE、TRUNCATE区别:
DELETE 删除不会清空AUTO_INCREMENT(自增)的值
TRUNCATE 删除会清空AUTO_INCREMENT(自增)的值
二者都只删除表中的数据
4.3数据查询语言(DQL) select
-- 数据查询语言(DQL)
-- 1.查询全表
SELECT * FROM stu;
-- 2.查询部分字段
SELECT `name`,English FROM stu;
-- 3.去重
SELECT DISTINCT name FROM stu;
-- 4.查询字段起别名
SELECT `name` AS 姓名 FROM stu;
-- 5.模糊查询
SELECT * FROM stu WHERE name LIKE '赵%';
SELECT * FROM stu WHERE name LIKE '赵_';
-- 6.AND、OR
SELECT * FROM stu WHERE English>85 AND Chinese>80;
SELECT * FROM stu WHERE English>85 OR Chinese>80;
-- 7.IN、not IN
SELECT * FROM stu WHERE id IN(2,3);
SELECT * FROM stu WHERE id not IN(2,3);
-- 8.BETWEEN AND
SELECT * FROM stu WHERE English BETWEEN 85 AND 90;
-- 9.is null、is not null
SELECT * FROM stu WHERE name is null;
SELECT * FROM stu WHERE name is not null;
-- 10.排序 ORDER BY
SELECT * FROM stu ORDER BY English;
SELECT * FROM stu ORDER BY English ASC;
SELECT * FROM stu ORDER BY English DESC;
-- 11.聚合函数
SELECT SUM(English) FROM stu;
SELECT COUNT(English) FROM stu;
SELECT MAX(English) FROM stu;
SELECT MIN(English) FROM stu;
SELECT AVG(English) FROM stu;
4.4数据控制语言(DCL) grant、revoke
-- 数据控制语言(DCL)
-- 1.创建用户 CREATE USER 用户名@ip IDENTIFIED BY 密码
-- 2.给用户授权 GRANK 权限1,权限2,...,权限n ON 数据库名.* TO 用户名@IP
-- 3.撤销权限 REVOKE 权限1,权限2,...,权限n ON 数据库名.* FROM 用户名@IP
-- 4.查看用户的权限 SHOW GRANTS FOR 用户名@IP
-- 5.删除用户 DROP USER 用户名@IP
-- 6.登录 mysql -u 用户名-p 密码
-- 7.退出登录 exit
4.5分组查询和分页查询
CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL
);
INSERT INTO emp VALUES
(1002,'白展堂','clerk',1001,'1983-05-09',7000.00,200.00,10),
(1003,'李大嘴','clerk',1002,'1980-07-08',8000.00,100.00,10),
(1004,'吕秀才','clerk',1002,'1985-11-12',4000.00,null,10),
(1005,'郭芙蓉','clerk',1002,'1985-03-04',4000.00,null,10),
(2001,'胡一菲','leader',null,'1994-03-04',15000.00,NULL,20),
(2002,'陈美嘉','manger',2001,'1993-05-24',10000.00,300.00,20),
(2003,'吕子乔','clerk',2002,'1995-05-19',7300.00,100.00,20),
(2004,'张伟','clerk',2002,'1994-10-12',8000.00,500.00,20),
(2005,'曾小贤','clerk',2002,'1993-05-10',9000.00,700.00,20),
(3001,'刘梅','leader',null,'1968-08-08',13000.00,NULL,30),
(3002,'夏冬梅','manger',3001,'1968-09-21',10000.00,600.00,30),
(3003,'夏雪','clerk',3002,'1989-09-21',8000.00,300.00,30),
(3004,'张一山','clerk',3002,'1991-06-16',88000.00,200.00,30);
-- -- 分组查询 GROUP BY
-- 1.查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 2.查询每个职位的最高工资和最低工资
SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job;
-- 3.查询每个部门每种职位的最高工资
SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job;
-- 4.查询每个部门的最高薪水,只有最高薪水大于15000的记录才被输出显示
SELECT deptno,MAX(sal) AS max_sal FROM emp GROUP BY deptno HAVING max_sal > 15000;
-- 5.查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 分页 LIMIT
SELECT * FROM emp LIMIT 0,5;
-- (页数-1)*条数,条数
-- 每页5条 第二页
SELECT * FROM emp LIMIT 5,5;
-- 每页3条,第4页
SELECT * FROM emp LIMIT 9,3;
where、havaing区别:
where是用来过滤记录的,havaing是用来过滤分组的
同时出现的话先过滤where再过滤havaing
havaing是在分组之后对分组进行过滤
havaing必须跟在group by之后
五、数据库引擎
myisam | innoDB | |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁支持 | 表级锁 | 行级锁 |
select | 效率高 | |
update、insert、delete | 效率高 | |
全文索引 | 支持 | 不支持 |
六、索引
索引是数据库管理系统中的一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引相当于目录。
6.1 索引的类型分类:
主键索引:数据列不允许重复,不允许为null,一张表只能有一个主键
唯一索引:数据列不允许重复,允许为null,一张表允许有多个
普通索引:没有唯一性的限制,允许为null
全文索引:
6.2 索引的基本原理
1.把创建了索引的列中的内容进行排序
2.将排序结果生成倒排表
3.在倒排表内容上拼上数据地址
4.在查询的时候,先拿到倒排表内容,在取到数据地址,从而拿到具体数据
6.3创建索引的原则
索引虽然可以提高查询的效率,但是过多的索引也会给数据库带来一定的压力
1.频繁作为查询条件的字段创建索引
2.更新频繁的字段不适合创建索引
3.不能有效区分数据的列不适合做索引(例如:性别)
4.查询中很少涉及的列,重复数据比较多的列不要建立索引
5.定义为text、image、bit的数据类型的列不要建立索引
七、事务
7.1 事务的四大特性
原子性:要么全部成功,要么全部失败
一致性:执行事务前后,数据保持一致
持久性:事务一旦提交,对数据的改变是持久不变的
隔离性:各个并发事务之间是相互独立的
7.2 Mysql事务的隔离级别
READ_UNCOMMITTED(读取未提交) 允许读取未提交的数据
READ_COMMITTED(读取已提交) 允许读取已提交的数据
REPEATABLE_READ(可重复读 默认) 对同一字段的多次读取结果是一致的
SERIALIZABLE(序列化) 所有事务依次执行
7.3 隔离失败可能会导致的问题
脏读:两个事务同时读取同一个数据,一个事务读取了另一个事务未提交成功的数据
幻读:一个事务在两次查询中数据量不一致。
例:事务A读取了数据,事务B此时插入了新的数据,事务A再次读取结果和之前结果不同
不可重复读:一个事务在两次查询中数据不一致
例:事务A新增了一条数据,事务B查看;事务A修改了这个数据,事务B再次查看结果不同
八、连表查询
创建表:
CREATE TABLE `category` (
`categoryid` int NOT NULL COMMENT 'id',
`pid` int NOT NULL COMMENT '父id 没有父则为1',
`categoryname` varchar(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `grade` (
`gradeid` int NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` varchar(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `result` (
`studentno` int NOT NULL COMMENT '学号',
`subjectno` int NOT NULL COMMENT '课程编号',
`examdate` datetime NOT NULL COMMENT '考试日期',
`studentresult` int NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`studentno` int NOT NULL COMMENT '学号',
`loginpwd` varchar(20) DEFAULT NULL,
`studentname` varchar(20) DEFAULT NULL COMMENT '学生姓名',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` int 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;
CREATE TABLE `subject` (
`subjectno` int NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` varchar(50) DEFAULT NULL COMMENT '课程名称',
`classhour` int DEFAULT NULL COMMENT '学时',
`gradeid` int DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
测试:
-- ===============连表查询===================
-- 查询参加考试的学生的 学号、姓名、学科编号、分数
SELECT
s1.studentno AS 学号,
s1.studentname AS 姓名,
r1.subjectno AS 学科编号,
r1.studentresult AS 分数
FROM
student s1
RIGHT JOIN result r1 ON s1.studentno = r1.studentno
-- 查询参加考试的学生的 学号、姓名、学科名称、分数
SELECT
t.studentno AS 学号,
t.studentname AS 姓名,
s.subjectname AS 学科,
t.studentresult AS 分数
FROM
`subject` s
RIGHT JOIN (
SELECT
s1.studentno,
s1.studentname,
r1.subjectno,
r1.studentresult
FROM
student s1
RIGHT JOIN result r1 ON s1.studentno = r1.studentno
) t ON s.subjectno = t.subjectno
-- =============自连接=================
SELECT
c1.categoryname AS 父,
c2.categoryname AS 子
FROM
category c1,
category c2
WHERE
c1.categoryid = c2.pid
-- 查询学员 学号 姓名 年级名称
SELECT studentno,studentname,gradename FROM student s LEFT JOIN grade g ON s.gradeid = g.gradeid
-- 查询 科目名称 年级名称
SELECT subjectname,gradename FROM `subject` s LEFT JOIN grade g ON s.gradeid = g.gradeid
-- =============分页limit 排序order by====================
SELECT * FROM `subject` LIMIT 0,5
SELECT * FROM result ORDER BY studentresult ASC
SELECT * FROM result ORDER BY studentresult DESC
-- 从总数据的下标为1的开始,五条数据
SELECT * FROM result ORDER BY studentresult DESC LIMIT 1,5
-- 查询 java第一学年 成绩排名前十并且分数大于70的学生 学号 姓名 课程名称 分数
SELECT
stu.studentno,
stu.studentname,
sub.subjectname,
res.studentresult
FROM
student stu
LEFT JOIN result res ON stu.studentno = res.studentno
LEFT JOIN `subject` sub ON res.subjectno = sub.subjectno
WHERE
res.studentresult > 70
AND sub.subjectname = '高等数学-1'
ORDER BY
studentresult DESC
LIMIT 0,
10
-- =============子查询==================
-- 查询分数不小于80 学生学号 姓名
SELECT DISTINCT
stu.studentno,
stu.studentname
FROM
student stu
INNER JOIN result res ON stu.studentno = res.studentno
WHERE
res.studentresult >= 80
九、sql优化
1、查询语句尽量少使用select *
2、尽量减少子查询,使用关联查询
3、or的查询尽量使用union或者union all 代替
4、尽量避免在where语句中使用操作符,否则引擎放弃使用索引进行全表扫描
5、尽量避免在where语句中对null值判断,否则引擎放弃使用索引进行全表扫描
十、存储过程
studentresult DESC
LIMIT 0,
10
– =子查询======
– 查询分数不小于80 学生学号 姓名
SELECT DISTINCT
stu.studentno,
stu.studentname
FROM
student stu
INNER JOIN result res ON stu.studentno = res.studentno
WHERE
res.studentresult >= 80
## 九、sql优化
1、查询语句尽量少使用select *
2、尽量减少子查询,使用关联查询
3、or的查询尽量使用union或者union all 代替
4、尽量避免在where语句中使用操作符,否则引擎放弃使用索引进行全表扫描
5、尽量避免在where语句中对null值判断,否则引擎放弃使用索引进行全表扫描
## 十、存储过程
一组为了完成特定功能的sql集,存储在数据库中,经过一次编译后再次查询就不需要编译,用户通过指定存储过程的名字来执行。