一、基础语句、属性
1.命令行连接
mysql -uroot -p123456 -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
--------------------------
-- 所有语句使用;结尾
show databases;-- 查看所有数据库
mysql> use school -- 切换数据库 use 数据库名
Database changed
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有表的信息
create database westos; -- 创建一个数据库westos
exit; -- 退出连接
-- 单行
/* 多行注释 */
2.操作数据库(了解即可)
[]表示可选
-
创建数据库
create database [if not exists] westos;
-
删除数据库
drop database [if not exists] westos;
-
使用数据库
-- tab上的漂符号` 用来括住字段名和表名,防止和关键词冲突 use `school`
-
查看数据库
show database; -- 查看所有数据库
3.数据类型
1.数值
数据类型 | 所占字节 |
---|---|
tinyint | 1 |
smallint | 2 |
mediumint | 3 |
int | 4 |
bigint | 8 |
float | 4 |
double | 8 |
decimal | a/b*4 (decimal(a,b)) |
整型特点:
① 都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号
② 如果超出了范围,会报out or range异常,插入临界值
③ 长度可以不指定,默认会有一个长度
长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型
小数特点:
①M代表整数部位+小数部位的个数,D代表小数部位
②如果超出范围,则报out or range异常,并且插入临界值
③M和D都可以省略,但对于定点数,M默认为10,D默认为0
④如果精度要求较高,则优先考虑使用定点数
2.字符型
数据类型 | 大小 | 描述 |
---|---|---|
char | 0~255 | 字符 |
varchar | 0~65535 | 常用 |
tinytxt | 2^8-1 | 微型文本 |
txt | 2^16-1 | 保存大文本 |
char、varchar、binary、varbinary、enum、set、text、blob
char:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1
varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略
3.日期型
Java包java.util.Data
-
year 年
-
date 日期 YYYY-MM-DD
-
time 时间 HH:mm:ss
-
datetime 日期+时间 YYYY-MM-DD HH:mm:ss
占8
-
timestamp 日期+时间 时间戳 1970.1.1到现在的毫秒数 占4 比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间 也更为常用
4.null
-
没有值,未知
-
注意不要用NULL值进行运算,结果为NULL
4.字段
unsigned
- 无符号整数
- 声明了该列不能声明为负数
zerofill
- 0填充
- 不足位数用0填充 int(3) 5 – 005
自增
- 自动在上一条记录的基础上+1
- 可自定义设计主键增长的起始值和步长
非空 NOT NULL
非空,该字段的值必填
其他
UNIQUE:唯一,该字段的值不可重复
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
FOREIGN KEY:外键,该字段的值引用了另外的表的字段
规范
每一个表都要存在以下字段
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
5.操作表
1.创建表
create table [if not exists] `表名`(
`字段名` 列属性 [属性] [索引] [注释],
`字段名` 列属性 [属性] [索引] [注释],
......
`字段名` 列属性 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
-- 例子
CREATE TABLE `course` (
`course_id` VARCHAR(10) NOT NULL COMMENT '课程号',
`course_name` VARCHAR(20) NOT NULL COMMENT '姓名',
`course_teacher_id` VARCHAR(20) NOT NULL COMMENT '任课教师号',
`credit_hour` INT(3) NOT NULL COMMENT '学时',
`credit_point` INT(2) NOT NULL COMMENT '学分',
`school_time` DATETIME NOT NULL COMMENT '上课时间',
`class_place` VARCHAR(50) NOT NULL COMMENT '上课地点',
`exam_time` DATETIME NOT NULL COMMENT '考试时间',
PRIMARY KEY (`course_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
show create database `school` -- 查看创建数据库的语句
show create table `student` -- 查看student数据表的定义语句
desc student -- 显示表的结构
2.修改表
修改
-- 修改表名:alter table 旧表名 rename as 新表名
alter table `teacher` rename as `teacher1`
-- 增加表的字段:alter table 表名 add 字段名 列属性
alter table `teacher1` add age int(11)
-- 修改表的字段(重命名 修改约束)
-- alter table 表名 modify 字段名 列属性[]
alter table `teacher1` modify age varchar(11) -- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性[]
alter table `teacher1` change age age1 int(1) -- 字段重命名
-- 删除表的字段:alter table 表名 drop 字段名
alter table `teacher1` drop age1
3.删除表
删除
-- 删除表 (如果存在)
drop table if exists `teacher1`
4.外键约束
-
什么是外键?
外键是相对于主键说的,是建立表之间的联系的必须的前提,比如有两张表,student(学生)表和grade(年级)表,student 中 id 是主键,而 gradeid 是依赖于 student 中的 id ,那么 grade 中的 id 就是 student 的外键;我们可以通过外键使两张表进行关联。 -
外键的作用是什么?
使两张表形成关联,外键只能引用外表中的列的值,可以使得两张表关联,保证数据的一致性和实现一些级联操作。
总结:
1)为了一张表记录的数据不要太过冗余;
2)保持数据的一致性、完整性。
增加约束(物理外键,实际开发中一般不用)
创建一张年级表
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;
-- 学生表的 id 字段要去引用年级表的 gradeid 字段
-- 定义外键key
-- 给最高外键添加约束(执行引用) references 引用
-- >> 方式一:创建表时增加约束(麻烦,复杂)
CREATE TABLE `student` (
`id` VARCHAR(20) NOT NULL COMMENT '学号',
`name` VARCHAR(20) NOT NULL COMMENT '姓名',
`gradeud` 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 `student` (
`id` VARCHAR(20) NOT NULL COMMENT '学号',
`name` VARCHAR(20) NOT NULL COMMENT '姓名',
`gradeud` INT(10) NOT NULL COMMENT '年级号',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建表时没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-- alter table 表 add constraint 约束名 foreign key (`作为外键的列`) references 哪个表(`哪个字段`)
-- >> 删除有外键关系的表的时候,必须先删除引用别人的表(从表),再删除被引用的表(主表)
-- >> 建立物理外键后,删除grade表时需删除student表
以上操作都是物理外键,数据库级别的外键,不建议使用
避免数据库过多造成困扰,了解即可
一般也不推荐使用外键约束,阿里巴巴的Java规范下是:强制不得使用外键,一切外键在应用层解决,原因就是在DELETE或者UPDATE时都要考虑外键约束,导致开发变得痛苦很多!
5.主键
数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.
表中的一个或多个字段,它的值用于唯一地标识表中的某一条记录
语法
PRIMARY KEY (`字段名`)
拓展
MySQL—为什么需要主键?主键为什么最好是单调递增的?
mysql 添加主键和外键【深度解析】
关于业务主键和逻辑主键
6.数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用,现在使用最多
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持(1.2.x版本开始支持全文检索) |
表空间的大小 | 较小 | 较大(约为2倍) |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
MySQL引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的 ibdata1 文件
- MYISAM 对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不是指的话,会是mysql默认的字符集编码,不支持中文
MySQL的默认编码是Latin1,不支持中文
- 也可以在my.ini中配置默认的编码(不推荐)
character-set-server=utf8
二、DML
DML:数据操纵语言(Data Manipulation Language),它可以实现对数据库的基本操作。
数据库意义:数据存储,数据管理
- insert
- update
- delete
1.添加/insert
语法:INSERT INTO 表名([字段名1,字段2,字段3,...]) VALUES ('值1','值2'...),('值3',....)
…
-- 给上面的student表插入数据
INSERT INTO `student`(`name`) VALUES ('张三')
-- 如果不写表的字段,他就会一一匹配,这条语句就会报错!
INSERT INTO `studnet` VALUES('李四')
-- 插入多个字段
INSERT INTO `student`(`id`,`name`) VALUES ('1001','张三')
INSERT INTO `student`(`id`,`name`) VALUES ('1001','李四'),('1001','王五')
- 注意:
1)字段和字段之间使用英文逗号隔开;
2)字段是可以省略的,但是后面的值必须要要一一对应,不能缺少数据;
3)可以同时插入多条数据,VALUES 后面的值,需要使用逗号隔开。
2.修改/update
语法:UPDATE 表名 SET colnum_name = value,[colnum_name = value,....] WHERE [条件]
-- 修改student表name字段
UPDATE `student` SET `name`='小明' WHERE id = 1;
-- 不指定条件的情况下,会改动所有表
UPDATE `student` SET `name`='小红';
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='小强',`email`='123456@qq.com' WHERE id = 1;
- 注意:
1)WHERE 条件子句 :id 等于某个值,或大于某个值,表示在某个区间内修改;
2)colnum_name 是数据库的列,尽量带上`` ;
3)条件,筛选的条件,如果没有指定,则会修改所有的列;
4)value,是一个具体的值,也可以是一个变量;
5)多个设置的属性之间,使用英文逗号隔开。
通过多个条件定位数据
UPDATE `student` SET `name`='小强' WHERE `name`='张三' AND `id`=1
3.删除/delete
语法:DELETE FROM 表名 [WHERE 条件]
-- 删除数据 (这样写,会全部删除)
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id = 1;
完全清空一个数据库表(TRUNCATE)
--清空 student 表
TRUNCATE `student`;
DELETE 和 TRUNCATE:
**相同点:**都能删除数据,都不会删除表结构
不同点: DELETE ,不会影响自增;TRUNCATE ,重新设置自增列,计数器会归零 。
三、DQL
DQL:数据查询语言(Data Query Language,所有的查询操作都用它,数据库中最核心的语言,最重要的语句,使用频率最高的语句。
1.指定查询字段
-- 查询全部的学生 SELECT 字段 FROM 表
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
有的时候,列名字不是那么的见名知意,
所以我们赋值一个新的名字,语法:字段名 AS 别名 表名 AS 别名
SELECT完整语法
SELECT [ALL | DISTINCT]
{* | 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}];
-- 指定查询的记录从哪条至哪条
2.去重 (DISTINCT)
去除SELECT 查询出来的结果中重复的数据,重复的数据只显示一条
SELECT DISTINCT `StudentNo` FROM result
3.数据库的列(表达式)
-- 查询系统版本 (函数)
SELECT VERSION()
-- 用来计算 (表达式)
SELECT 100*3-1 AS 计算的结果为
-- 查询自增的步长 (变量)
SELECT @@auto_increment_increment
-- 给student表的考试成绩都 + 1分
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
4.WHERE条件子句
用来检索数据中符合条件的值
MySQL官网的函数和运算符说明:点我
常用的:
符号 | 描述 | 语法 |
---|---|---|
<> 或 != | 不等于 | id <> 3、id != 3 |
BETWEEN … and … | 在某个范围内 | BETWEEN A and B |
AND、&& | 逻辑与 | a and b 、 a&&b |
OR | 逻辑或 | a or b |
Not ! | 逻辑非 | not a !a |
IS NULL | 如果操作符为 NUll, 结果为真 | a is null |
IS NOT NULL | 如果操作符不为 null,结果为真 | a is not null |
BETWEEN | 若a 在 b 和c 之间,则结果为真 | a between b and c |
LIKE | SQL 匹配,如果a匹配b,则结果为真 | a like b |
In | a在a1,或者a2…. 其中的某一个值中,结果为真 | a in (a1,a2,a3….) |
应用: 模糊查询
-- 查询考试成绩在 95~100 分之间
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
-- 模糊查询(区间)
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 查询学号不为1000的学生成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;
SELECT studentNo,`StudentResult` FROM result
WHERE NOT studentNo = 1000
-- 查询姓张的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName 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 '%坤%'
-- 查询 1001,1002,1003号学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询地址为北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('北京');
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address='' OR address IS NULL
-- 查询有出生日期的同学,不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查询没有登记出生日期的同学,为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL
- 注意:
WHERE IN()查询时,IN()里面是具体的值,如果地址是北京市海淀区,当你输入WHERE Address IN ('北京')
,结果是查询不到的,只能采用WHERE Address LIKE '%北京%'
。
5.联表查询(JOIN)
LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法
- 思路:
1)分析需求,分析查询的字段来自哪些表?并进行连接查询;
2)确定使用上图的哪种连接查询?
3)确定交叉点(这两个表中哪个数据是相同的;
4)判断的条件
-- INNER JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON 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
- 注意:
JOIN | 描述 |
---|---|
INNER JOIN | 如果表中有至少一个匹配,则返回行 |
LEFT JOIN | 即使右表中没有匹配,也从左表返回所有的行 |
RIGHT JOIN | 即使左表中没有匹配,也从右表返回所有的行 |
- 总结:
联表查询思路:
1)首先确定要查询哪些数据:SELECT ...
2)从哪几个表中查FROM 表 XXX JOIN 连接的表 on 交叉条件
3)假设存在一种多张表查询,先查询两张表,然后再慢慢增加。
6.自连接
自己的表和自己的表连接,将一张表拆为两张一样的表即可
假设一张表:
categoryid | pid | categoryName |
---|---|---|
2 | 1 | 水果 |
3 | 1 | 蔬菜 |
4 | 3 | 生菜 |
5 | 1 | 肉类 |
6 | 3 | 青菜 |
7 | 5 | 猪肉 |
8 | 2 | 苹果 |
拆分成两张表:
categoryid | categoryName |
---|---|
2 | 水果 |
5 | 肉类 |
3 | 蔬菜 |
pid | categoryid | categoryName |
---|---|---|
4 | 3 | 生菜 |
6 | 3 | 青菜 |
7 | 5 | 猪肉 |
8 | 2 | 苹果 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
蔬菜 | 青菜,生菜 |
水果 | 苹果 |
肉类 | 猪肉 |
-- 通过别名将一张表看作两个一摸一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
7.排序与分页
- 排序(ORDER BY)
-- 升序 ASC , 降序DESC
ORDER BY StudentResult ASC
ORDER BY StudentResult DESC
- 分页(LIMIT)
缓解数据库压力,给人的体验更好
语法: LIMIT (查询起始下标,pageSize)
第一页 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,pageSize
pageSize:页面大小
(n-1)* pageSize:起始值
n :当前页
数据总数/页面大小 = 总页数
8.子查询
在where语句中嵌套一个子查询语句,查询高数考试学生的成绩、学号,成绩降序排序
-- 方式一: 使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '高数'
ORDER BY StudentResult DESC
-- 方式二: 使用子查询(由里及外) -- 查询所有数据库结构-1 的学生学号
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result` WHERE SubjectNo = (
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '高数'
) ORDER BY StudentResult DESC
9.过滤分组
-- 通过什么字段来分组
GROUP BY xxx
-- 过滤分组后的信息,需要满足的条件,次要条件
HAVING xxx
四、函数
1.常用函数
- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 0~1 之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1,正数返回 1
- 字符串函数
SELECT CHAR_LENGTH('啊哈哈哈哈哈') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('你好世界',1,2,'Hello') -- 从某个位置开始替换某个长度 你好世界-->Hello世界
SELECT LOWER('Hello') -- 小写字母
SELECT UPPER('Hello') -- 大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现的子串的索引
SELECT REPLACE('你好世界','你好','保护') -- 替换出现的指定字符串,将'你好'替换成'保护'
SELECT SUBSTR('今天天气真的很好',4,3) -- 返回指定的子字符串 (截取的位置,截取的长度)
SELECT REVERSE('你爱我') -- 反转
-- 查询姓张的同学,将姓改为李
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() -- 版本号
2.聚合函数
COUNT():计数
COUNT(字段):会忽略所有的 null 值
COUNT(*):不会忽略 null 值, 本质是计算行数
COUNT(1):不会忽略 null 值, 本质是计算行数
SUM():求和
AVG():平均值
MAX():最大值
MIN():最小值
-- 统计student`BornDate`字段个数
SELECT COUNT(`BornDate`) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM result;
-- 计算student成绩的总和、平均分、最高分、最低分
SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result
3.MD-5加密
主要增强算法复杂性和不可逆性
-- 创建一个test表
CREATE TABLE `test`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据,即明文和密码
INSERT INTO test VALUES(1,'zhangsan','123456'),(2,'lisi','123456'), (3,'wangwu','123456')
-- 加密
-- 加密第一个人的密码
UPDATE test SET pwd=MD5(pwd) WHERE id = 1
-- 加密全部的密码
UPDATE test SET pwd=MD5(pwd)
-- 插入数据时加密
INSERT INTO test VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
SELECT * FROM test WHERE `name`='xiaoming' AND pwd=MD5('123456')
-- 加密后:
五、事务
-
概述:
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如,在人员管理系统中,你删除一个人员,你既需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等,此时这些数据库操作语句就构成一个事务!
-
条件:
事务是必须满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
事务原则:SCID 原则 原子性,一致性,隔离性,持久性
原子性(Atomic)
- 要么都成功,要么都失败
- 整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性(Consist)
- 事物前后的数据完整性要保证一致
- 一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。
隔离性(Isolated)
- 每个操作每个事务之间相互隔离
- 隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。
虚读(幻读):
是指一个事务内读取到了别的事务插入的数据,导致前后读取不一致
持久性(Durable)
- 事务一旦提交则不可逆,被持久化数据库中
- 在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
基本语法
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
模拟事务
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
六、索引
在一个表中,主键索引只能有一个,唯一索引可以有多个
作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
分类
- 主键索引 (Primary Key)
- 唯一索引 (Unique)
- 常规索引 (Index)
- 全文索引 (FullText)
1.主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
2.唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
- 主键索引只能有一个
- 唯一索引可能有多个
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
3.常规索引
作用 : 快速定位特定数据
注意 :
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
4.全文索引
百度搜索:全文索引
作用 : 快速定位特定数据
注意 :
- 只能用于MyISAM类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
/*
#方法一:创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二:CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三:ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引:DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*
开始之前,先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
5.拓展:测试索引
建表app_user:
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`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 '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
批量插入数据:100w
DROP FUNCTION IF EXISTS mock_data;
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), '24736743@qq.com', CONCAT('18', 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'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
测试普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
6.索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
7.索引的数据结构
-- 我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
七、权限管理和备份
1.权限
基本命令
/* 用户和权限管理 */ ------------------
用户信息表:mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限,或拥有INSERT权限。
- 只能创建用户,不能赋予权限。
- 用户名,注意引号:如 'user_name'@'192.168.1.1'
- 密码也需引号,纯数字密码也要加引号
- 要在纯文本中指定密码,需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值,需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
权限解释
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器(从主服务器中读取二进制日志事件)
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句,mysqladmin debug命令;允许您连接(一次),即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
2.备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件和相关配置文件
mysqldump客户端
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
-- 导入
1. 在登录mysql的情况下:-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
3.规范化数据库设计
为什么需要数据库设计
当数据库比较复杂时我们需要设计数据库
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤
- 收集信息
-
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
- 标识实体[Entity]
-
- 标识数据库要管理的关键对象或实体,实体一般是名词
- 标识每个实体需要存储的详细信息[Attribute]
- 标识实体之间的关系[Relationship]
4.三大范式
问题 : 为什么需要数据规范化?
不合规范的表设计会导致的问题:
- 信息重复
- 更新异常
- 插入异常
-
- 无法正确表示信息
- 删除异常
-
- 丢失有效信息
三大范式
第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。
第二范式要求每个表只描述一件事情
第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
八、JDBC
JDBC(概述、Java需要的数据库驱动包下载以及在IDEA中的导入、第一个JDBC程序、提取工具类、常用对象、statement对象详述)
JDBC(SQL注入问题、PreparedStatement对象、IDEA连接数据库、Java代码实现事务、数据库连接池)