MySQL基础使用
-- 所有命令;分号结尾
show databases; --查看所有数据库
use central_database -- 切换数据库
show tables --查看数据库中所有表
describe mange_device --查看表的所有信息
create database central_database -- 创建数据库
show create database central_database -- 查看数据库创建的语句
show createtable mange_device -- 查看表创建的语句
desc central_database -- 查看表结构
exit -- 退出连接
数据库的列类型
数值
tinyin 1字节
smallint 2字节
mediumint 3字节
int 4字节
big 8字节
float 4字节
double 8字节
decimal 金融使用
字符串
char 0-255
varchar 0-65535
tinytext 2^8 - 1
text 2^16 - 1 保存大文本
时间日期
date yyyy--mm-dd
time hh:mm:ss
datetime yyyy--mm-dd hh:mm:ss
timestamp 时间戳 1970.1.1到现在的毫秒数
year
null
没有值
不要使用null 进行运算
数据库的字段属性
Unsigned
无符号证书
声明了该列不能声明为负数
zerofill
0填充
自增
通常理解为自增
设置主键 必须整数类型
可以自定义设置主键自增的起始值和步长
非空
如果不赋值 就报错
Null 如果不填写 默认就是null
默认
设置默认的值
/*
id
version
is_delete
gmt_create
gmt_update
*/
建表
createtable if not exists `student` (
`id` int(4) notnull auto_increment comment 'id',
`name` varchar(30) notnull default '匿名' comment '姓名',
`password` varchar(20) notnull default '123456' comment '密码',
`sex` varchar(20) notnull 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
createtable if not exists 表明 (
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
primary key(`字段名`)
)[表类型][字符集设置][注释]
数据表类型
INNODB 默认使用
支持事务
行锁
支持外键
不支持全文索引
表空间较大 ,约为MYISAM的2倍
MYISAM
不支持事务
表锁
不支持外键
全文索引
较小 节约空间
常规使用操作
MYISAM 节约空间 速度较快
INNODB 安全性高 事务处理 多表多用户操作
物理空间位置
所有的数据库文件都存在data目录下,一个文件夹对应一个数据库
本质是文件存储
MySQL 引擎在物理文件上的区别
InnoDB 在数据库表中只有一个*.frm文件 还有上级目录下的ibdata1文件
MYISAM对应文件
*.frm - 表结构定义文件
*.MYD 数据文件(data)
*.MYI 索引文件(index)
设置数据库字符集编码
charset=utf8
不设置的话 会是mysql默认的字符编码(不支持中文)
在my.ini 中设置默认的编码(不推荐)
character-set-server=utf8
修改删除表操作
-- alter table 旧表名 rename as 表名
altertable teacher rename as tecacher1 -- 修改表名
-- alter table 表名 add 字段 类型
altertable teacher1 add age int(11) -- 表新增字段
-- alter table 表名 modify 已有字段 要更改的类型
altertable teacher1 modify age varchar(11) -- 修改约束
-- alter table 表名 change 旧字段名 重命名字段 要更改的类型
altertable teacher1 change age age1 int(11) -- 修改字段名
-- alter table 表名 drop 已有字段
altertable teacher1 drop age1 -- 删除字段
-- drop table if exists 表名
droptable if exists teacher1 -- 删表
创建和删除尽量加上判断 以免报错
注意点
`` 字段名 使用这个包裹
注释 -- 或者 /**/
sql 大小写不敏感 建议小写
符号用英文
Mysql 数据管理
外键(了解即可)
a表的某字段 要去引用b表的字段
定义外键key 外键在从表
添加约束 references 引用
alter table `student`
ADD CONSTRAINT FK_gradeid FOREIGN KEY( `gradeid` ) REFERENCES `grade` ( `gradeid`);
物理外键 不建议使用(阿里规范)
最佳实践
数据库就是淡出的表 存数据
程序实现外键
DML(数据库操作语言)
数据库意义:数据存储,数据管理
insert
update
delete
添加
-- insert into 表名(字段1,字段2,...) value('v1','v2',...)
insert into test(`name`,`age`) value('zhangsan','12')
-- 插入多个
-- insert into 表名(字段1,字段2,...) value('v1','v2',...),...
insert into
`test`(`name`,`age`)
value
('zhangsan','12'),('lisi','15')
注意 如果省略字段 value要与表一一对应
修改
-- update `表名` set `列字段`='值' where 条件
update `student` set `name`='wangwu'where id=1
-- update `表名` set `列字段1`='值1',`列字段2`='值2',... where 条件
update `student` set `name`='wangwu',`age`='10',`birthday`=current_time where id=1
where
-- 运算符
=
<> 或 !=
between2and3 [2,3]
AND -- 多条件
OR -- 或条件
删除
-- delete from 表名 where 条件
deletefrom `student` where id=1
truncate
-- truncate table 表名
truncate table student -- 删除表
相同点:都能删除数据 不删除表结构
不同:
truncate 重新设置自增列 计数器归零
truncate 不会影响事务
delete删除问题,重启数据库 ,现象
innodb 自增列会重1开始 ( 存在内存中 断电即使)
Myisam 继续从上一个自增量开始 (存在文件中,不会丢失)
DDL(数据库定义语言)
select
数据库最核心的语言
高频使用
查询所有字段
-- select 字段 from 表名
select * from student -- 查全部
select `name` ,`age` from student -- 查指定字段
select `name` as 名字 ,`age` as 年龄 from student as s -- 别名
-- 函数拼接 concat(a,b)
select concat('姓名:',name) as 新名字 from student as s -- 函数拼接
有的时候 列名字不是那么见名知意 我们起别名 字段名 as 别名 表名 as 别名
去重 distinct
-- select distinct 字段 from 表
select distinct `stuNo` from `result`
数据库的列(表达式)
select version() -- 查看当前版本(函数)
select100*3-1as 计算结果 --计算(表达式)
select @@auto_increment_increment -- 查看自增的步长(变量)
-- stuNo字段+1
select `stuNo`+1as'提分后'from `result`
数据库中的表达式: 文本值,列,null,函数,计算表达式,系统变量...
-- select 表达式 from 表
where 条件子句
作用:检索符合条件的
逻辑运算符
and &&
or ||
not !
尽量使用英文字母
select
`stuNo`,`stuRes`
from
`result`
where
stuRes <= 100AND
stuRes > 95
----------------------------------------
select
`stuNo`,`stuRes`
from
`result`
where
stuRes between95and100
----------------------------------------
select
`stuNo`,`stuRes`
from
`result`
where
stuRes != 100
---------------------------------------
select
`stuNo`,`stuRes`
from
`result`
where
not stuRes = 100
模糊查询: 比较运算符
is null
is not null
between
like
in
select
`stuNo`,`stuName`
from
`student`
where
stuName like'刘%'
---------------名字后面只有一个字的----------------
select
`stuNo`,`stuName`
from
`student`
where
stuName like'刘_'
---------------名字后面只有两个字的----------------
select
`stuNo`,`stuName`
from
`student`
where
stuName like'刘__'
-------------------------------
select
`stuNo`,`stuName`
from
`student`
where
stuName like'%佳%'
-------------------------------
select
`stuNo`,`stuName`
from
`student`
where
stuNo in(1001,1002,1003);
-------------------------------
select
`stuNo`,`stuName`
from
`student`
where
Address=""or Address isnull;
-------------------------------
select
`stuNo`,`stuName`
from
`student`
where
`birthday` isnotnull;
连表查询
join对比
-- join on(判断条件) 连接查询
-- where 等值查询
/*
*如果表中至少有一个匹配 就返回行
*/
select
s.studentNo,studentName,studentResult
from student as s
inner join result as r
where
s.studentNo = r.studentNo
---------------------------------------------
/*
*会从左表中返回所有的值 即使右表中没有匹配
*/
select
s.studentNo,studentName,studentResult
from student s
right join result r
on
s.studentNo = r.studentNo
---------------------------------------------
/*
*会从右表中返回所有的值 即使左表中没有匹配
*/
select
s.studentNo,studentName,studentResult
from student s
left join result r
on
s.studentNo = r.studentNo
---------------------------------------
-- 查询缺考的 studentResult 为null
select
s.studentNo,studentName,studentResult
from student s
left join result r
on
s.studentNo = r.studentNo
where
studentResult isnull
----------------------------------------
-- 3表查询
select
s.studentNo,studentName,subjectName,studentResult
from student s
right join result r
on s.studentNo = r.studentNo
inner join subject sub
on r.subjectNo = sub.subjectNo
-- 我要查询哪些数据 select...
--从那几个表中查 FROM 表 xx Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
From a left join b
From a right join b
自连接
自己的表和自己的表连接 核心:一张表拆为两张一样的表即可
场景:树形结构 分类表 pid 顶级分类 二级分类...
-- 查询父子信息 把一张表拆成两张表
select sp.nameas"父" ,ss.nameas"子"
from sort sp,sort ss
where sp.id = ss.pid
分页和排序
-- desc降序 asc升序
-- limit 起始值 页面大小
select
s.studentNo,studentName,subjectName,studentResult
from student s
right join result r
on s.studentNo = r.studentNo
inner join subject sub
on r.subjectNo = sub.subjectNo
--------------------- 排序语句
orderby studentResult desc
--------------------- 分页语句
limit0,5
子查询
where(动态条件)
select
studentNo,subjectNo,studentResult
from result r
inner join subject s
on r.subjectNo = s.subjectNo
where subjectName = "数据库"
----------------------------------
select
studentNo,subjectNo,studentResult
from result
where subjectNo = (
select
studentNo
from subject
where subjectName = "数据库"
)
分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT SubjectName, AVG(StudentResult) As 平均分MAX(StudentResult) AS 最高分,MIN(StudentResult) As 最低分
from result r
INNER JOIN `subject` sub
ON r.subjectNo = sub.`subjectNo`
GROUPBy r.subjectNo-- 通过什么字段来分组
HAVING 平均分>80
常用函数
select ABS(-8) -----绝对值 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("hahaha") -- 字符串长度
select concat("我",'爱','你们') -- 字符串拼接
selectinsert("我爱编程123123",1,2 ,"不爱") --查询 替换
select lower('HelloWord') --小写字母
select upper('HelloWord') --大写字母
select instr("hello" ,'l') -- 返回第一次出现的子串的索引
select replace("hello",'e','o') -- 返回hollo 替换
select substr("hello123123",2,5) -- 返回 ello1 从第二个开始截取5个
select reverse('123') -- 返回321 反转
select current_date() -- 当前时间
select currdate() -- 当前时间
select now() -- 当前时间
select localtime() -- 当前时间
select sysdate() -- 系统时间
select system_user() -- root
select user() -- root
select version() -- 版本
聚合函数(常用)
count()
sum()
avg()
max()
min()
-- 都能够统计数据库表中有多少记录
-- count(字段) 会忽略null值
selectcount(studentname) from student -- 指定列
-- count(*) 不会忽略null值 计算行数
selectcount(*) from student
-- count(1) 不会忽略null值 计算行数
selectcount(1) from student
select sum(studentNum) as 总和 from student -- 总数
select avg(studentNum) as 平均分 from student -- 平均分
select max(studentNum) as 最高分 from student -- 最高分
select min(studentNum) as 最低分 from student -- 最低分
MD5加密
MD5 不可逆
MD5破解网站原理 背后有一个字典
update test set pwd=md5(pwd)
insertinto `test`(name,pwd) values('zhangsan',md5('123456'))
select小结
select完整的语法
select [all | distinct]
{* | table.* | [table.field1[as aliasl][,table.field2[as alias2]][....]]}
FROM table_name [astable alias]
[left | right | inner join table_name2] -- 联合查询
[ON ...]-- 等值判断
[WHERE ...] -- 指定结果需满足的条件
[GROUPBY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过速分组的记录必须满足的次要条件
[ORDERBY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
-- 注意:[]]括号代表可选的,{}括号代表必选的
数据库事务
事务原则:ACID 原子性 一致性 隔离性 持久性 (脏读 幻读 不可重复读)
-- mysql默认开启事务
set autocommit = 0-- 关闭
set autocommit = 1-- 开启
---测试事务---
set autocommit = 0
-- 事务开启
start transaction
update account set money=money-500 where name = a
update account set money=money+500 where name = b
-- 事务提交
commit
-- 回滚
rollback
--事务结束
set autocommit = 1 -- 恢复默认值
-- 了解
savepoint 保存点 -- 设置一个事务保存点
rollback to savepoint 保存点 -- 回滚到保存点
release savepoint 保存点 -- 撤销保存点
MySQL隔离级别--未提交读,提交读,可重复读,序列化
MySQL的事务隔离级别的含义,并用示例说明各个级别会出现的问题:脏读、不可重复读、幻读。
MySQL有四种隔离级别:未提交读,提交读,可重复读,序列化。
事务的隔离级别是Java后端面试题中经常会问到的问题。
索引
帮助mysql高效获取数据的 数据结构
在一个表中 主键索引只能有一个 唯一索引可以有多个
索引分类
主键索引 (primary key)
唯一标识 主键不可重复 只能有一个列作为主键
唯一索引 (unique key)
避免重复的列出现 唯一索引可以重复,多个列都可作为唯一索引
常规索引 (key/index)
默认的, index,key关键字来设置
全文索引 (fulltext)
在特定的数据库引擎下才有
快速定位数据
基础用法
-- 索引使用
-- 在创建表的时候 给字段加索引
-- 创建完毕后 增加索引
-- 显示所有的索引信息
SHOW INDEX FROM 表名
-- 增加一个索引 alert table 数据库名.表名 add 索引类型 INDEX `索引名`(`需要在索引的字段`)
alert table test.student add fulltext INDEX `studentName`(`studentName`)
-- explan 分析sql执行情况
explan select * from student
explan select * from student where match(studentName) against('刘')
测试
// 生成100w条数据
delimiter $$ -- 写函数之前必须写!
create function mock_data()
RETURNS INT
begin
declare num int default 1000000;
declare i int default 0
where i<num DO
-- 插入语句
INISERT INTO app_user(name,email,phone,pwd,age)
values(concat("用户",i),
'123123@qq.com',
concat("18",floor(rand()*((999999999-100000000)+100000000))),
UUID(),
floor(rand()*100) );
set i = i+1;
end while;
return i;
end;
select * from app_user where name = "用户9999"--用时 1s左右
explain select * from app_user where name = "用户9999"
-- id_表名_字段名
-- create index 索引名 on 表名(`字段`)
create index id_app_user_name on app_user(`name`)
select * from app_user where name = "用户9999"--用时 0.01s左右
explain select * from app_user where name = "用户9999"
索引在小数据量的时候 感觉不大 但在大数据的时候 区别十分明显
索引原则
索引不是越多越好
不要对经常变动的数据加索引
小数据量的表 不需要加索引
一般价值常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree innodb 的默认使用
权限管理和备份
sql命令
-- 创建用户
-- creat user 用户名 identified by '密码'
creat user zhang identified by'123456'
-- 修改当前用户密码
-- set password = password("新密码")
set password = password("111111")
-- 修改指定用户密码
-- set password for zhang = password('新密码')
set password for zhang = password('123456')
-- 用户重命名
-- rename user 原名字 to 新名字
rename user zhang to ZHLng
-- 授予全部权限 除了给别人授权
GRANT ALL privileges on *.* to ZHLng
-- 查询权限
show grant for ZHLng
show grant for root@127.0.0.1
-- 撤销权限
revoke all privileges on *.* from ZHLng
-- 删除用户
drop user ZHLng
mysql备份
命令行
-- mysqldump -h主机 -u用户 -p密码 数据库 表 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
-- mysqldump -h主机 -u用户 -p密码 数据库 表1 表2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql
-- mysqldump -h主机 -u用户 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
-- 登录状态下导入
-- source 文件位置
use school -- 切换数据库
source D:/a.sql-- 执行导入
-- 未登录
mysql -u用户名 -p密码 库名< 备份文件
定期备份
数据库设计
糟糕的数据库设计:
数据冗余 浪费空间
数据库插入和删除麻烦 异常(屏蔽物理外键)
程序性能差
良好的数据库设计
节省内存空间
保证数据完整性
方便我们开发系统
软件开发中 关于设计库的设计
分析需求: 分析业务和需要处理的数据库的需求
概要设计: 设计关系图E-R图
数据库设计规范
三大范式
第一范式
原子性:不可再分
第二范式
每张表只描述一件事情
第三范式
确保每张表的每一列数据都和主键直接相关,而不能间接相关
规范性 和 性能问题
关联查询的表不得超过三张
考虑商业的需求和目标,数据库的性能更加重要
在规范性能的问题的时候,适当的考虑一下规范性
故意给某些表增加冗余的字段(减少连表查询)
故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
JDBC
数据库驱动
步骤总结:
加载驱动
连接数据库
获得执行sql的对象
获得返回值
释放连接
url
// 协议://localhost:3306/数据库名?参数?参数...
// jdbc:mysql://localhost:3306/数据库名?参数?参数...
// jdbc:oracle:thin:@localhost:1521:sid
SQL注入
select * from username where name="zhangsan"or1=1and password=123456or1=1