MYSQL
1.初识MYSQL
javaEE:企业级java开发 web
前端(页面:展示,数据)
后台(连接点 :链接数据库JDBC,链接前端)
数据库(寸数据)
只会写代码,你就是码农
1.1、为什么学习数据库
1.岗位需求
2.现在的世界，大数据时代
3.被迫需求:存数据 去IOE
4.数据库是所有软件体系中最核心的存在
1.2、 什么是数据库
数据库(DB)
概念:数据仓库,软件 ,安装在操作系统(Mac、window、linux…)之上
作用: 存储数据，管理数据
1.3、 数据库分类
关系型数据库:（SQL）
- Mysql,Oracle,Sql Server, DB2, SQLlite
- 通过表与表之间，行和列之间的关系进行数据的存储
非关系型数据库 （NoSql）
-
Redis、MongDB
-
非关系型数据库,对象存储，通过对象的自身的属性来决定
DBMS (数据库管理系统):
- 数据库的管理软件,科学有效的管理数据
1.4 、Mysql
MYsql是一个关系型数据库管理系统
前世: 瑞典Mysql AB公司
今生: 属于Oracle旗下产品
1.7、 连接数据库
mysql -uroot -p --链接数据库
show databases; --查询所有的数据库
user pewee --切换数据库
show tables; --查看所有表
describe pewee; --显示数据库中所有表的信息
create database westos; --创建一个数据库
exit; --退出连接
数据库xxx语言
- DDL 定义
- DML 操作
- DQL 查询
- DCL 控制
2、操作数据库
- 创建数据库
CREATE DATABASE [if NOIT EXISTS] westos
- 删除数据库
DROP DATABASE westos
- 使用数据库
-- 如果你的表名或者字段名是一个特殊字符,就需要带 ``
USE `user`
- 查看数据库
SHOW DATABASES
2.2、数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- samllint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用的
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节(精度问题)
- decimal 字符串形式的浮点数 金融计算的时候一般使用
字符串
- char 字符串固定大小 0-255
- varchar。 可变字符串。 0-65535
- tingtext。 微型文本。 2^ 8-1
- text。 文本串。 2^16-1
时间日期
java.util.Date
- date. YYYY-MM-DD
- time HH:mm:ss
- datatime
- Timestamp. 时间戳, 1970.1.1到现在的毫秒数
- Year 年份表示
null
- 不要使用null进行运算，结果为null
2.3、数据库的字段属性
Unsigned ：
- 无符号的整数
- 声明了该列不能为负数
zerofill:
- 0填充
- 不足的位数，使用0来填充
自增:
- 通常理解为自增
非空: NOT NULL
default :
- 默认值
2.4、创建数据库表
CREATE TABLE IF NOT EXISTS student(
`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` VARCHAR(123) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` VARCHAR(50) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT charset=utf8
常用命令
SHOW CREATE TABLE student -- 查看sql语句
DESC student -- 查看表结构
2.5、数据表的类型
-- 关于数据库引擎
/*
INNODB
MYISAM
*/
| MYISAM | INNODB | |
|---|---|---|
| 事物支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高，事物的处理，多表多用户操作
在物理空间存在位置
所有的数据库文件都存在data文件下
本质还是文件的存储！
MYSQL 引擎在物理文件上的区别
- INNODB 在数据库表中只有*.frm是属于他的,以及上级目录的下的…
- MYISAM对应文件
*.frm --表结构定义文件
*.MYD --数据文件
*.MIY. – 索引文件
设置数据库的字符集编码
CHARSET=utf8
不设置的话，会是mysql默认的字符集编码（不支持中文！）
编码->Latin1,不支持中文
2.6、修改和删除
修改
-- 修改表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加字段
ALTER TABLE teacher ADD age INT(11) DEFAULT NULL COMMENT '年龄'
-- 修改表的字段
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher CHANGE age age1 int(11) -- 字段重名
-- 删除表的字段
ALTER TABLE teacher DROP age1
删除-如果表存在再删除
-- 删除表
DROP TABLE IF EXISTS teacher
注意点:
- `` 字段名用这个包裹
- – 注释
- sql 关键字大小写不敏感，建议大家写小写
- 所有符号都用英文
3、mysql的数据管理
3.1、外键(了解)
方式一
CREATE TABLE IF NOT EXISTS grade(
`grade_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gaadename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生
CREATE TABLE IF NOT EXISTS student(
`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` VARCHAR(123) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`grade_id` INT(10) NOT NULL COMMENT '家庭住址',
`address` VARCHAR(50) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`),
-- 定义外键KEY
KEY `FK_grade_id` (`grade_id`),
-- 给这个外键添加约束 REFERENCES 引用
CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (grade_id)
)ENGINE=INNODB DEFAULT charset=utf8
方式二
ALTER TABLE `student`
ADD constraint `Fk_grade_id` foreing KEY` (`grade_id`) REFERENCES `grade` (grade_id)
-- ALTER TABLE 表 ADD CONSTARAINT 约束名 FOREING KEY(作为外键的列) REFERNCES 哪个表（哪个字段）
以上操作都是物理外键,数据库级别的外键，不建议使用
最佳实践
- 数据库就是单纯的表，只是用来存数据,只有行和列
- 最好用程序去实现
3.2、DML语言(全部记住)
3.3、添加
-- 添加
insert into teacher (name,age) values （'钟晚',11）
-- 字段可以省略,后面的值要一一对应
3.4 修改
-- 修改
update student set name='tk' where name='钟晚'
3.5、删除
-- 删除
delete FROM `student` where = id=1
TRUNCATE 命令
作用: 清空表
TRUNCATE student
-
相同点：都能删除数据，都不会删除表结构
-
不同:
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
4、DQL查询数据
4.1、DQL
- 所有的查询操作都用它 Select
- 数据库中最核心的语言
- 使用频率最高
-- 简单查询
SELECT * FROM student
-- 函数 Concat
SELECT CONCAT('姓名:',studentName) AS 新名字 FROM student
去重复
-- 去重
SELECT DISTINCT * FROM result
数据库的列
SELECT VERSION() -- 查询系统版本
SELECT 100*3-1 AS 计算结果 -- 用来计算
-- 学员考试成绩+1 分查看
SELECT studentno,studentResult +1 AS 提分后 FROM result
4.2、语法
Select 完整的语法:
select语法
SELECT [ALL |DISYINCT] {* |table.* |[table.field1[as alias1][,table.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 | row_countOFFSET offset]注意:[]括号代表可选的,{}括号代表必选的
4.3、where条件字句
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or b a|b | 逻辑或 |
| NOT ! | Not a. !a | 逻辑非 |
-- 查询考试成绩在 95-100 分之间
select studentno,studentResult from result where studentResult>=95 and studentResult<100
-- 区间查询
select studentno，studentResult from result where between 95 and 100
模糊查询
| 运算符 | 语法 | 描述 |
|---|---|---|
| LIKE | a like b | |
| IN | a in {a1,a2,a3,a4} |
-- 查询姓刘的同学
SELECT * FROM student where name like '刘%'
-- 查询姓刘的同学，名字后面只有一个字的
SELECT * FROM student where name like '刘_'
-- 查询姓刘的同学，名字后面只有2个字的
SELECT * FROM student where name like '刘__'
-- 查询名字带 佳 的同学
SELECT * FROM student where name like '%佳%'
-- 查询 1001-1003学员
SELECT * FROM student where studentno in {1001,1002,1003}
4.4、连表查询
JOIN 对比
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配,就返回行 |
| left join | 会从左表中返回所有的值,即使右表中没匹配 |
| right join | 会从右边中返回所有的值,即使左边中没有匹配 |
自连接
自己的表和自己的表连接，核心：一张表拆分为两张一样的表即可
CREATE TABLE `school`.`category`( `categoryid` INT(3) NOT NULL COMMENT 'id', `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', PRIMARY KEY (`categoryid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库');
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发');
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');
-- 查询父类对应的子类关系
select
a.categoryName as 父栏目,
b.categoryName as 子栏目
FROM category as a, category as b
where a.categoryid =b.pid
4.5、分页和排序
-- 排序
ORDER BY 字段 DESC[ASC]
-- 分页
SELECT * FROM WHERE LIMIT 0,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit 0,5 (n-1)*pagesize
-- 查询 2-8 数据
SELECT * FROM WHERE LIMIT 1,7
4.6、子查询
where (这个值是计算出来的)
where(SELECT *FROM)
5、MYSQL函数
官网：
https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
5.1常用函数
/**
数学函数
*/
-- 绝对值
select ABS(-8)
-- 向上取整
select CEILING(9.4)
-- 向下取整
select FLOOR(9.4)
-- 返回一个0-1 之间的随机数
SELECT RAND()
-- 判断一个数的符号
SELECT SIGN(10)
/**
字符串函数
*/
-- 字符串长度
SELECT CHAR_LENGTH('')
-- 拼接字符串
SELECT CONCAT('w','o','r','d')
-- 从某个位置开始替换某个长度
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱')
-- 转小写
SELECT LOWER('ZhongWan')
-- 转大写
SELECT UPPER('ZhongWan')
-- 替换
SELECT REPLACE('坚持就能成功','坚持','摸鱼')
-- 返回指定的字符串 【源字符串,截取的位置,截取的长度】
SELECT SUBSTR('坚持就能成功',4,2)
-- 反转
SELECT REVERSE('ZhongWan')
/**
时间和日期函数
*/
-- 查询当前的日期
SELECT CURRENT_DATE()
-- 获取当前日期
SELECT CURDATE()
-- 获取当前时间
SELECT NOW()
-- 获取本地时间
SELECT LOCALTIME()
5.2 聚合函数
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| … | … |
-- 查询不同课程的平均分,最高分,最低分 ,平均分大于80分
-- 核心: (根据不同的课程分组)
select
s.subjectname,
AVG(r.studentresult) AS avg,
MAX(r.studentresult) AS max,
MIN(r.studentresult) AS min
FROM result AS r
INNER JOIN `subject` AS s
ON r.`subjectno` =s.`subjectno`
-- 分组
GROUP BY r.`subjectno`
-- 过滤
having avg>80
5.3、数据库级别的MD5加密(扩展)
-- sql
CREATE TABLE `testmd5` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`pwd` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd)
6、事物
6.1、什么是事务
将一组SQL放在一批次中去执行
事物原则: ACID原则
原子性：要么都成功,要么都失败
一致性：事物前后的数据完整性要保持一致
隔离性：并发访问时不会相互干扰
持久性：事物一但提交就不可逆
隔离导致一些问题
- 脏读
- 不可重复读
- 虚读(幻读)
执行事务原理
-- 关闭自动提交 -0
SET autocommit =0
-- 事务开启
START TRANSACTTON
-- 提交
COMMIT
-- 回滚
ROLLBACK
-- 开启自动提交
SET autocommit =1
-- 保存点名
SAVEPOINT
-- 回滚到保存点
ROLLBACK TO SAVEPOINT
-- 删除保存点
RELEASE SAVEPOINT
7、索引
Msyql官方对索引的定义为:索引(index) 是帮助MYSQL搞笑获取数据的数据结构。
提取句子主干，就可以得到索引的本质:索引是数据结构
7.1、索引的分类
在一个表中
- 主键索引 PRIMARY KEY
- 唯一索引 UNIQUE KEY
- 避免重复的列出现,唯一索引可以重复
- 常规索引 KEY/INDEX
- 全文索引 FulIText
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引
ALTER TABLE `表名` ADD FULLTEXT 索引名 (`列名`)
-- 分析sql执行的情况
EXPLAIN SELECT * FROM student -- 非全文索引
7.2、测试索引
-- 百万数据插入
DROP FUNCTION IF EXISTS mock_data; -- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT -- 注意returns，否则报错。
DETERMINISTIC -- 8.0版本需要多这么一行
BEGIN
DECLARE num INT DEFAULT 1000000; -- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)VALUES(CONCAT('用户', i),'965499224@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();
-- rows 992262
EXPLAIN SELECT * FROM app_user WHERE `name`= '用户9999'
--
CREATE INDEX id_app_user_name ON app_user (`name`);
-- rows 1
EXPLAIN SELECT * FROM app_user WHERE `name`= '用户9999'
7.3、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用的查询字段上
索引的数据结构
Hash类型的索引
Btree : InnoDB 默认数据结构
阅读: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
