赞
踩
1、SQL基本语句
MySQL -u root -p root --连接数据库 UPDATE mysql.`user`SET authentication_string=PASSWORD('123456') WHERE USER='root' AND HOST = 'localhost';--修改用户密码 flush privileges; --刷新权限 -------------------------- 所有的语句都是用;结尾 show datebases; -- 查看所有的数据库 mysql> use school -- 切换数据库 use 数据库名 Database changed show tables; -- 查看数据库中所有的表 mysql> describe student; -- 显示数据库中所有表的信息 create database "名字"; -- 创建一个数据库 exit; -- 退出连接 -- 单行注释 /*多行注释*/
DDL 定义
DML 操作
DQL 查询
DCL 控制
操作数据库->操作数据库中的表->操作数据库中表的数据
== MYSQL关键字不区分大小写 ==
1、创建数据库
CREATE DATABASE IF [NOT NOT EXISTS] "表名"
2、删除数据库
DROP DATABASE IF EXISTS "表名"
3、使用数据库
如果你的表名或者字段名是一个特殊字符,就需要带`````
USE `school`
4、查看数据库
SHOW DATABASES --查看所有数据库
数值
字符串
时间日期
java.util.Date
null
Unsigned:
zerofill
auto_increment
NULL not null
默认:
拓展
/*每一个表,都必须存在以下五个字段!,在做项目中,表示一个记录存在意义
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
-- 目标 :创建一个school数据库 -- 创建学生表(列,字段)使用SQL 创建 -- 学号int 登陆密码varchar(20)姓名,性别varchar(2),出生如期(datatime),家庭住址,email -- 注意点,使用英文(),表的名称和字段尽量使用``括起来 -- auto_increment 自增 -- 字符串使用:单引号括起来! -- 所有的语句后面加,(英文的),最后一个不用加 -- PRIMARY KEY :主键,一般一个表只有一个唯一的主键! CREATE table if not exists `student`( `id` int(4) not null 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
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school -- 查看船舰数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 (表锁) | 支持(行锁) |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM的两倍 |
常规使用操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,本质还是文件的存储!
MySQL 引擎在物理文件上的区别
设置数据库表的字符节编码
CHARSET=Utf8
不设置的话,会是mysql默认的字符集编码(不支持中文)
可以在my.ini中配置默认的编码
character-set-server=utf8
修改
-- 修改表 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段 (重命名,修改约束!)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE student1 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE student1 DROP age1
删除
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS student1
所有的创建和删除操作尽量加上判断,以免报错
注意点
方式一、在创建表的时候,增加约束(麻烦,比较复杂)
CREATE TABLE `grade`( `gradid` INT(10) NOT NULL auto_increment COMMENT '年纪id', `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称', PRIMARY KEY(`gradid`) )ENGINE=INNODB DEFAULT CHARSET=utf8 -- 学生表的gradeid字段要去引用年纪表的gradeid -- 定义外键key -- 给这个外键添加约束(执行引用)references 引用 CREATE TABLE `student1` ( `id` int(4) NOT NULL COMMENT '学号', `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别', `gradid` INT(10) NOT NULL COMMENT '学生的年纪', `birthday` datetime DEFAULT NULL COMMENT '出生日期', `address` varchar(100) DEFAULT NULL COMMENT '家庭住址', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`), KEY `FK_gradid` (gradid), CONSTRAINT `FK_gradid` FOREIGN KEY (`gradid`) REFERENCES `grade`(`gradid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
删除有外键关系的表的时候,必须要先删除引用别人的表(从表),在删除引用的表(主表)
方式二:创建表成功后,添加约束
CREATE TABLE `grade`( `gradide` INT(10) NOT NULL auto_increment COMMENT '年纪id', `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称', PRIMARY KEY(`gradid`) )ENGINE=INNODB DEFAULT CHARSET=utf8 -- 学生表的gradeid字段要去引用年纪表的gradeid -- 定义外键key -- 给这个外键添加约束(执行引用)references 引用 CREATE TABLE `student1` ( `id` int(4) NOT NULL COMMENT '学号', `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别', `gradeid` INT(10) NOT NULL 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 -- 创建表的时候没有外键关系 -- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(哪个字段), ALTER TABLE `student1` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上的外键都是物理外键,数据库级别的外键,不建议使用(避免数据库过多造成困扰,这里了解即可)
*最佳实践
数据库意义:数据存储,数据管理
DML语言:数据操作语言
insert
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaa','男')
-- insert into 表名(字段1,字段2,字段3)]values('值1','值2','值3')
-- 由于主键自增我们可以胜利(如果不写表的字段,他就会一一匹配)
-- 一般插入语句,我们一定要数据和字段一一对应!
语法:insert into 表名(字段1,字段2,字段3)]values('值1','值2','值3')
注意事项:
update 修改谁(条件)set原来的值 = 新值
-- 修改学员名字
UPDATE `student` SET `name`='周俊杰' WHERE id = 1;
-- 不指定条件的情况下,会改动所有表!
UPDATE `student` SET `name`='金志鑫'
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='周俊杰',`email`='798523509@qq.com' WHERE id = 1;
-- 语法:UPDATE 表名 SET colnum_name = value,[colnum_name = value....] where "条件"
条件:where子句,运算符 id 等于某个值,大于某个值,在某个区间内修改
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | lg:5=6 | false |
<> 或 != | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
between A and B | 在某个区间,闭合区间 | between 5 and 6 | |
and | 和 && | 5>1 and 1>2 | false |
or | 或 | | | 5>1 and 1>2 | true |
-- 通过多个条件定位一个数据
UPDATE `student` SET `name`='周俊杰' WHERE id = 1 AND sex = '男'
注意:
colnum_name 是数据库的列,尽量带上``
条件,筛选的条件,如果没有指定,则会修改所有的列
多个设置的属性之间,使用英文逗号隔开
value,是一个具体的值,也可以是一个变量
UPDATE `student` SET `birthday`=CURRENT_DATE WHERE id = 1;
delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student`WHERE ` id` = 1;
TRUNCATE 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
-- 清空 student 表
TRUNCATE `student`
delete 和 TRUNCATE 区别
-- 测试delete 和 TRUNCATE 区别
CREATE TABLE `test`(
`id` INT(4) PRIMARY KEY,
`coll` VARCHAR(20) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`)VALUES('1'),('2'),('3');
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零
了解即可:delete删除的问题
,重启数据库
(Date Query LANGUAGE)
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}]; -- 指定查询的记录从哪条至哪条
注意:[]括号代表可选,{}括号代表必选
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给结果起一个名字, AS 可以给字段起别名,也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student
-- 函数 CONCAT(a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
语法:SELECT 字段 FROM 表
有的时候,列名字不是那么的见名知意,我们可以起别名 :AS 字段名 as 别名
去重 distinct
作用:去除SELECT 查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下又哪些同学参加了考试
SELECT * FROM result -- 查询全部的考试成绩
SELECT 'StudentNo' FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentNo` FROM result-- 发现重复数据,去重
数据库的列(表达式)
select VERSION()-- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 学员考试成绩 +1 分
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量
select 表达式
from 表
作用:检索表中符合条件
的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
not ! | not a ! a | 逻辑非 |
SELECT StudentNo,`StudentResult` FROM result
WHERE studentresult<=99
SELECT StudentNo,`StudentResult` FROM result
WHERE studentresult BETWEEN 94 AND 99
模糊查询 :比较运算符
运算符 语法 描述 IS NULL a is null 如果操作符为NULL,结果为真 IS NOT NULL a is not null 如果操作符为 not null,结果为真 BETWEEN a between b and c 若a在b和c之间,结果为真 Like a like b SQL匹配,如果a匹配到b,结果为真 in a in(a1,a2,…) 假设a在a1,或者a2…其中的某一个,结果为真
-- ===========================模糊查询=========================== -- 查询姓金的同学 -- 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 '金__' -- 查询名字中间有志的同学 %志% SELECT `StudentNo`,`StudentName` FROM `student` WHERE StudentName LIKE '%志%' -- =========in(具体的一个或者多个值)========= -- 查询 1001,1002,1003号学员 SELECT `studentno`,`studentname` FROM `student` WHERE StudentNo IN (1001,1002,1003); -- 查询在山西大同的学生 SELECT `studentno`,`studentname` FROM `student` WHERE `address` IN ('山西大同'); -- ============ null not null============== -- 查询地址为空的学生 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
JOIN 对比
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WWKNrGYI-1647350508411)(C:\Users\周俊杰\AppData\Roaming\Typora\typora-user-images\image-20211117202501357.png)]
-- ====================联表查询 join=========================== -- 查询参加了考试的同学(学号,姓名,科目编号,分数) SELECT *FROM student SELECT *FROM result /*思路 1.分析需求,分析查询的字段来自哪些表 2.确定使用哪种连接查询? 3.确定交叉点(这两个表中哪个数据是相同的) 4.判断的条件:学生表中的 studentNo = 成绩表 studentNo */ -- 我要查询哪些数据 -- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件 -- 假设存在一种多张表查询,慢慢来,先查询两表,在慢慢增加 -- FROM a LEFT join b 以左表为准 -- FROM a RIGHT JOIN b 以右表为准 SELECT s.studentno,studentname,subjectno,studentresult FROM student AS s INNER JOIN result AS r WHERE 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 -- 思考(查询了参加考试的同学信息:学号。学生姓名,科目名称,分数)(三表查询) SELECT s.studentno,studentname,subjectname,studentresult FROM student AS s RIGHT JOIN result AS r ON r.studentno = s.studentno INNER JOIN `subject` AS sub ON r.subjectno = sub.subjectno -- JOIN (连接的表)ON (判断的条件)连接查询 -- WHERE 等值查询
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配, 就会返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
自连接(了解)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
CREATE TABLE `category`( `categoryid` INT(10) UNSIGNED NOT NULL auto_increment COMMENT '主题id', `pid` INT(10) NOT NULL COMMENT '父id', `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryid`) )ENGINE=INNODB auto_increment=9 DEFAULT CHARSET=utf8 INSERT INTO `category`(`categoryid`,`pid`,`categoryName`) VALUES(2,1,'信息技术'), (3,1,'软件开发'), (4,3,'数据库'), (5,1,'美术设计'), (6,3,'web开发'), (7,5,'ps技术'), (8,2,'办公信息'); -- 查询父子信息:把一张表看成两张一样的表 SELECT a.`categoryName` AS '父',b.`categoryName` AS '子' FROM `category` AS a,`category` AS b WHERE a.`categoryid` = b.`pid`
排序
-- =======================分页 limit 和排序 order by 排序============================
-- 升序 :ASC,降序 DESC
-- ORDER BY 通过哪个字段排序,怎么排?
-- 查询的结果根据 成绩降序 排序
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
ORDER BY studentresult ASC
-- =================分页====================== -- 为什么要分页? -- 缓解数据库压力,给人的体验更好 -- 分页,每页只显示五条数据 -- 语法:起始值,页面的大小 CREATE TABLE `category` ( `categoryid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主题id', `pid` int(10) NOT NULL COMMENT '父id', `categoryName` varchar(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryid`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 SELECT * FROM category ORDER BY pid ASC LIMIT 0,3 -- 若页只要求五条数据 -- 第一页 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:页面大小】 -- 【(n-1)*pageSize:起始值】 -- 【n:当前页】 -- 【数据总数/页面大小 = 总页数】
语法:limit(查询起始下标,pageSize)
where (这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- =================== WHERE ====================== -- 1、查询数据库结果-1的所有考试结果(需噩耗,科目编号,成绩),降序排列 -- 方式一:使用连接查询 SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM `result` r INNER JOIN `subject` sub on r.StudentNo = sub.StudentNo WHERE subjectname = '数据库结果-1' ORDER BY StudentResult DESC -- 方式二:使用子查询() SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM `result` WHERE StudentNo = (SELECT SubjectNo FROM `subject` WHERE SubjectName = '数据库结构-1') ORDER BY StudentResult DESC -- 查询所有数据库结构-1的学生学号 -- SELECT SubjectNo FROM `subject` WHERE SubjectName = '数据库结构-1' -- 分数不小于80分的学生的学号和姓名 SELECT s.`StudentNo`,`StudentName` FROM student s INNER JOIN result r ON r.StudentNo = s.StudentNo WHERE `StudentResult` >= 80 -- 在这个基础上增加一个科目,高等属性-2 -- 查询 高等数学-2的编号 SELECT s.`StudentNo`,`StudentName` FROM student s INNODB JOIN result r ON r.StudentNo = s.StudentNo WHERE `StudentResult` >=80 AND `SubjectNo` = ( SELECT SubjectNo FROM `subject` WHERE `subjectname` = `高等属性-2` ) -- SELECT SubjectNo FROM `subject` WHERE `subjectname` = `高等属性-2` -- 再改造 SELECT `StudentNo`,`StudentName` FROM student WHERE IN( SELECT StudentNo FROM result WHERE StudentResult > 80 AND SubjectNo = ( SELECT SubjectNo FROM `subject` WHERE `subjectname` = '高等数学-2' ) )
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT subjectname,AVG(StudentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subjecr` sub
ON r.`SubjectNo` = sub.`SubjectNo`
-- WHERE AVG(StudentResult) > 80 会报错
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING AVG(StudentResult) > 80
-- ==================常用函数======================= -- 数学运算 SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 10 向上取整 SELECT FLOOR(9.4) -- 9 向下取整 SELECT RAND() -- 返回一个0 ~ 1之间的随机数 SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1,正数返回1 -- 字符串函数 SELECT CHAR_LENGTH('我是中国人') -- 字符串长度 SELECT CONCAT('我','哎','你') -- 拼接字符串 SELECT INSERT('我哎敲代码helloworld',1,2,'超级热爱') -- 插入,从某个位置开始替换某个长度 SELECT LOWER('WDSJcJEw') -- 转换小写字母 SELECT UPPER('esAWWDs') -- 转换大写字母 SELECT INSTR('adc','c') -- 返回第一次子串出现的位置 SELECT REPLACE('坚持成功','成功','努力') -- 替换指定的子串 SELECT SUBSTR('我爱你是个笑话',2,4) -- 返回指定的子字符串(源字符串,截取的位置,截取的长度) 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 SYSTEM_USER() -- 系统用户 SELECT USER() -- 系统用户 SELECT VERSION() -- 版本
COUNT() | 计数 |
---|---|
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… | … |
-- ====================== 聚合函数 =============================
-- 都能够统计 表中的数据(想查询一个表中又多少个数据,就使用这个count())
SELECT COUNT(StudentNo) FROM student; -- COUNT(指定列),会忽略所有的 null 值
SELECT COUNT(*) FROM student; -- count(*) ,不会忽略所有的 null 值 本质 计算行数
SELECT COUNT(1) FROM student; -- count(1),不会忽略所有的 null 值 本质 计算行数
SELECT SUM(`StudentResult`) FROM result -- 总和
SELECT AVG(`StudentResult`) FROM result -- 平均分
什么是MD5:主要增强算法复杂度和不可逆性
MD5不可逆,MD5破解网站背后有个字典
-- =================== 测试MD5 加密======================== CREATE TABLE `testmd5`( `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 testmd5 VALUES(1,'张三','123456'),(2,'lisi','233445'),(3,'wangwu','123445') -- 加密 UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1 -- 插入的时候加密 INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('1234565')) -- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值 SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd = MD5('123445')
什么是事务
要么都成功,要么都失败
核心:将一组SQL放在一个批次中去执行
事务原则:ACID原则-原子性,一致性,持久性 ,隔离性(脏读,幻读…)
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保证一致
持久性(Durability)
事务一旦提交则不可逆,被持久化到数据库中
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干涉
隔离所导致的问题:
脏读:
指一个事务读取了另外一个事务未提交的数据
不可重复读:
再一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读):
是指再一个事务读取到了别的事务插入的数据,导致前后读取不一样
执行事务
-- ===================== 事务 ======================= -- mysql 是默认开启事务自动提交的 SET autocommit = 0 -- 关闭 SET autocommit = 1 -- 开启(默认) -- 手动处理事务 -- 事务开启 START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内 INSERT xx INSERT xx -- 提交: 持久化(成功!) COMMIT -- 回滚: 回到原来的样子 (失败!) ROLLBACK -- 事务结束 SET autocommit = 1 -- 开启自动提交 -- 了解 SAVEPOINT 保存点名 -- 设置一个事务的保存点 ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点 RELEASE -- 撤销指定的保存点
模拟场景
-- 转账 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci 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 INSERT INTO account(`name`,`money`) VALUE ('A',2000.00),('B',10000.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' -- A加500 COMMIT; -- 提交事务 ROLLBACK; -- 回滚 SET autocommit = 1; -- 恢复默认值
MySQLG官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的笨猪:索引是数据结构。
在一个表中,主键索引只能有一个,唯一索引可以可以有多个
基础语法:
-- 增加一个全文索引
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘')
CREATE TABLE `app_user`( `id` BIGINT(20) UNSIGNED NOT NULL auto_increment, `name` VARCHAR(50) DEFAULT '' COMMENT '' 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 UPDATE CURRENT_TIMESTAMP,PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='用户表' -- id _ 表名 —— 字段名 -- CREATE INDEX 索引名 ON 表(字段) -- CREATE INDEX id_app_user_name ON app_user(`name`);
索引在小数据量的时候,用户不大,但在大数据的时候,区别十分明显
索引的数据结构
Hash类型的索引
Btree:InnoDB的默认数据结构
SQL命令操作
用户操作:mysql.user
本质:对这张表进行增删改查
-- id _ 表名 —— 字段名 -- CREATE INDEX 索引名 ON 表(字段) -- CREATE INDEX id_app_user_name ON app_user(`name`); -- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码' CREATE USER demo IDENTIFIED BY '123467' -- 修改密码(修改当前用户密码) SET PASSWORD = PASSWORD('123456') -- 修改密码(修改指定用户密码) SET PASSWORD FOR demo = PASSWORD('123456') -- 重命名 RENAME USER 原来名字 TO 新名字 RENAME USER demo TO demo2 -- 用户授权 ALL PRIVILEGES 全部的权限, 库.表 -- ALL PRIVILEGES 除了给别人授权,其他的都很干 GRANT ALL PRIVILEGES *.* -- 查看权限 SHOW GRANTS FOR root@localhost -- 查看指定用户的权限 -- REVOKE : 撤销权限 (REVOKE 哪些权限,在哪个库撤销,给谁撤销) -- 删除用户
为什么要备份:
MySQL数据库备份的方式
# mysqldump -u 用户名 -h 主机 -p 密码 端口号(-P3306) 数据库 表名 > 物理磁盘位置/文件名
mysqldump -uroot -hlocalhost -proot -P3306 school app_user >D:/a.sql
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
# 导入:
# 登录mysql后,切换到指定的数据库 source +文件物理磁盘/文件名
source d:/a.sql
当数据库比较复杂的时候,我们需要设计数据库
糟糕的数据库设计:
良好的数据库设计:
软件开发中,关于数据库的设计
设计数据库的步骤(个人博客)
三大范式
第一范式(1NF):列不可再分
每一列的属性都是不可再分的属性值,确保每一列的原子性。
两列的属性相近或相似一样,尽量合并属性一样的列,确保不产生冗余数据。
第二范式(2NF):属性完全依赖于主键
第三范式(3NF):属性不依赖于其他非主属性,属性之间依赖于主键。
(规范数据库的设计)
规范性和性能的问题
关联查询的表不得超过三张表
jdbc程序:
加载驱动
链接驱动
连接成功,返回一个数据库对象
执行SQL的对象
执行SQL,获得返回的结果集
String sql = ‘‘SELECT *FROM users’’;
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,其中封装了我们全部查询的结果
while(resultSet.next()){
System.out.println(’‘id = ‘’+resultSet.getObject(’‘id’’));
}
释放连接
JDBC工具类包装
第一步:创建一个db.properties文件将其放在src文件夹下
第二步:将dirver url username password放入其中
第三步:创建一个jdbcUtils的java类
第四步:将连接驱动,加载驱动,释放资源封装其中
第四步:测试数据
//db.properties driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/hotel reservation system?useUnicode=true&characterEncoding=utf8&useSSL=true username = root password = root //jdbcUtils类 package zhou.test; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.驱动只需要加载一次 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } //释放连接资源 public static void release(Connection cnn, Statement st, ResultSet rs){ if(rs != null){ try{ rs.close(); } catch (SQLException e) { e.printStackTrace(); } }if (st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } }if (cnn != null){ try { cnn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } //测试插入 package zhou.test; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestInsert { public static void main(String[] args){ Connection cnn = null; Statement st = null; ResultSet rs = null; try{ cnn = JdbcUtils.getConnection();//获取数据连接 st = cnn.createStatement();//获得SQL的执行对象 String sql = "INSERT INTO hotel(`HotelName`,`city`,`address`,`HotelNum`)" + "VALUES('酒店42','城市42','地址42','18345436731')"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(cnn,st,rs); } } }
PreparedStatement 可以防止SQL注入。效率更搞
SQL注入:
sql存在漏洞,会导致被攻击
//db.properties driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/hotel reservation system?useUnicode=true&characterEncoding=utf8&useSSL=true username = root password = root //jdbcUtils类 package zhou.lesson.jdbc; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.驱动只需要加载一次 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } //释放连接资源 public static void release(Connection cnn, Statement st, ResultSet rs){ if(rs != null){ try{ rs.close(); } catch (SQLException e) { e.printStackTrace(); } }if (st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } }if (cnn != null){ try { cnn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } // 测试 package zhou.lesson.jdbc; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class Demo { public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; try{ conn = JdbcUtils.getConnection(); String sql = "INSERT INTO hotel(`HotelName`,`city`,`address`,`HotelNum`) values(?,?,?,?)"; st = conn.prepareStatement(sql);//预编译SQL,先写sql,但不执行 st.setObject(1,"酒店55"); st.setObject(2,"城市55"); st.setObject(3,"地址55"); st.setObject(4,"2847394934"); int i = st.executeUpdate(); if (i > 0){ System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,null); } } }
1、开启事务
2、一组业务执行完毕,提交事务
package zhou.lesson07; import zhou.lesson.jdbc.JdbcUtils; import javax.xml.transform.Result; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestTransaction1 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); //关闭数据库的自动提交,自动会开启事务 conn.setAutoCommit(false);//开启事务 String sql1 = "update account1 set money = money - 100 where name = 'A'"; st = conn.prepareStatement(sql1); st.executeUpdate(); //conn.setAutoCommit(true); String sql2 = "update account1 set money = money + 100 where name = 'B'"; st = conn.prepareStatement(sql2); st.executeUpdate(); //conn.setAutoCommit(true); //业务完毕,提交事务 conn.commit(); System.out.println("操作成功"); } catch (SQLException e) { try { conn.rollback();//如果失败则回滚事务 } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
DELIMITER $$
CREATE FUNCTION mock_data1()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 40;
DECLARE i INT DEFAULT 0;
while i < num Do
INSERT hotel(`HotelName`,`city`,`address`,`HotelNum`) VALUES (CONCAT('酒店',i),CONCAT('城市',i),CONCAT('地址',i),CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))));
SET i = i+1;
END while;
RETURN i;
END;
SELECT mock_data1()
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。