基础操作
对用户操作
登陆mysqlmysql -uroot -p密码
修改密码 update mysql.user set authentication_string=password('12346') where user='root' and Host = 'localhost';
刷新权限 flush privileges;
exit; 退出链接
常用数据类型
数值
-
tinyinnt 十分小的数据 1字节
-
smallint 较小的数据 2字节
-
int 4字节
-
bigint 大的数据 8 字节
-
float 4字节
-
double 8字节
-
decimal 字符串形式的浮点数 精度更高。
日期类
- date YYYY-MM-DD
- time HH:mm:ss
- datetime YYYY-MM-DD HH:mm:ss最常用
- timestamp 时间戳 1970.1.1到现在的毫秒数
- year 年份表示
字符串
- char
- varchar
- tinytext 微型文本 2^8 - 1
- text 文本串 2^16-1
数据库的字段属性
Unsigned
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
- 0填充的
- 不足的位数用0来填充 例如:int(3) 5 -> 005
自增
- 通常理解为自增,按顺序+1
- 设置唯一主键
- 可以自定义设计主键的起始值和自增的步长
非空 NULL not null
- 假设设置为 not null ,不给他赋值就报错
- NUll 不填写 默认就是 null
默认
- 设置默认的值
- sex 默认值为男,不指定该列的值,就是默认为男
拓展
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zaT8Gn5K-1596546740931)(http://img.zzzyuan.cn/image-20200801081503913.png)]
数据库的引擎 innoDB(默认使用) 和 MYISAM
MYISAM | INNODB | |
---|---|---|
事务支持 | no | yes |
外键约束 | no | yes |
数据行锁定 | no | yes |
全文索引 | yes | no |
表空间大小 | 较小 | 较大,约为2倍 |
- 常规使用操作:
- MYISAM 节约空间,速度快
- INNODB 安全性高,事务的处理,
在物理空间存在的位置
所有的数据库文件都存在data目录下,本质是文件存储。
MYSQL引擎在物理文件的区别
- innoDB 在数据库上只有一个 *.fem 文件,以及上级目录下的ibadta1文件
- MYISQM 对应文件
- *.frm 表结构的定义文件
- *.MYD 数文件 (data)
- .MYI 索引文件(index)
设置数据库的字符集编码
CHARSET=utf8
- 不设置的话是mysql的默认的字符集编码Latin1(不支持中文)
- 可以在my.ini 中设置默认编码
- 创表的时候可以设置编码格式
约束条件
-
主键
唯一确定一张表的一条记录,不重复不为空
就是同一属性只有一个值,并且不可以为空create table user( 属性名 类型 primary key )
-
联合主键 两个属性加起来不重复
create table user2( 属性名1 类型 属性名2 类型 primary key(属性名1,属性名2) )
-
自增
create table 表名(
属性名 类型名 primary key auto_increase
) -
后期添加主键
alter table 表名 add primary key(属性)
-
删除主键
alter table 表名 drop primary key (一个表中只有一个主键)
-
修改字段,添加约束
alter table 表名 modify 属性 类型 primary key
-
唯一约束
修饰不可重复
alter table 表名 add unique(属性);
create table 表名(
属性 类型
unique(属性)
);
删除唯一约束
alter table 表名 drop index 属性
修改唯一
alter table 表名 modify 属性 类型 unique
-
默认约束
不传值时默认create table 表名( 属性 类型 default 默认值 )
-
外键约束
-
物理外键
方法一、 create table 表名1( 属性1 类型 primary key ) create table 表名2( 属性2 类型 primary key key 属性名3 (属性52) constraint 属性3 foreign key(属性2)references 表名1(属性1) ) 方法二、 alter table 表二 add constraint 属性名 foreign key(属性2)references 表名1(属性1) 添加属性2时必须是属性1中已有的值 并且表1的属性不能任意删除修改如果已经被表二中属性绑定 删表时先删从表,再删主表(如上表二为从表,表一为主表)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AjOr69bi-1596546740935)(http://img.zzzyuan.cn/image-20200801091737036.png)]
-
数据库的设计范式
- 第一范式
数据库中的所有字段都是不可分割的原子值
设计的越详细不一定越好
- 第二范式
必须满足第一范式
除主键外的每一列都需要完全依赖主键
如果出现不完全依赖,那只能是联合主键情况
create table 表名(
人名 类型
人年龄 类型
产品 类型
primary key(人名,人年龄)
)
除主键以外的其他属性,只依赖主键部分字段
– 拆表
create table 信息(
人名 类型 primary key
人年龄 类型
)
create table 产品表(
产品 类型 primary key
)
- 第三范式
必须满足第二范式
除开主键列的其他列之间不能有传递依赖关系
查询总结
select * form 表名 |查询表中的所有记录
select 属性,属性 from 表名 |查询部分属性
select distinct 属性 from 表名 |查询不重复的属性
select * from 表名 where 属性 between 小 and 大 |查询一个范围内的属性
select * from 表名 属性 >小 and 属性 < 大
select * from 表名 where 属性 in(指定值,指定值) |查询某属性的指定数据
select * from 表名 where 属性 ="x" or 属性 = "y" |查询某属性x 或y的具体数据
select * from 表名 order by 属性 desc | 降序查询相关属性
select * from 表名 order by 属性 asc | 升序查询相关属性
select * from 表名 order by 属性 asc ,属性2 desc| 升序查询属性1相关属性,如果相同则属性而降序排序
select count(*) form 表名 where 属性 = "数据" |统计某一属性的某一数据量
select 属性1,属性2 from 表名 where 属性3 = (select max(属性3)from 表名)|查找数据三最大值时属性2和1的值
select 属性 limit
---avg()求平均
select avg(属性)from 表名 where 属性2 |查询属性2的属性1的平均成绩
---group by 分组
select 属性1,avg(属性2)from 表名 group by 属性1
对数据库的操作
注意点:
`` 字段名推荐用 这个包裹
sql不分大小写,推荐小写
所有的符号全英文
查询数据库 show databases
选择数据库 use xx
查询select * from xxx
select * from xxx where 具体的xxx
退出数据库 exit
创建数据库 create database ***
查询表 show tables;
创建表 create table 表名(属性名 类型(字节数));
查看创建数据库的语句 show create database school
查看student 数据表的定义语句 show create table student
查看表的结构 desc 表名
### DML语言
insert
-
插入数据 insert into 表名(属性名,属性名) values(“数据”,“数据”);
-
插入多组数据insert into 表名(属性名,属性名) values(“数据”,“数据”),(“数据”,“数据”),(“数据”,“数据”);
-
省略属性插入数据insert into 表名values(“数据”,“数据”),(“数据”,“数据”),(“数据”,“数据”);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gpy0UPCP-1596546740940)(http://img.zzzyuan.cn/image-20200801093922615.png)]
update
- 修改数据(不指定条件的情况修改所有属性) update 表名 set 属性名 = “具体的数据”;
- 修改数据(指定条件的情况修改指定条件的属性属性) update 表名 set 属性名 = “具体的数据” where 属性2 = ‘数据’;
- 修改多个属性 update 表名 set 属性名 = “具体的数据”,属性名 = “具体的数据” where 属性2 = '数据’
操作符 | 含义 | 范围 |
---|---|---|
= | 等于 | 5=6 |
<> 或者 != | 不等于 | |
> | ||
< | ||
<= | ||
>= | ||
between … and … | 在某个范围内 | […,…] |
and | 和 | x > 1and x<5 |
or | 或 | x >1 or x < 5 |
delete
- 删除 delete from 表名 where 属性 = “删除的数据”
truncate
- 清空某一张表 truncate 表名。
- 完全清空一个数据库表,表的结构和索引约束不变
delete 和 truncate 区别
-
相同:都能删除表数据,不修改表结构
-
不同:
-
delete 不会影响自增变量 。
删除后
- innoDB 重启数据库后自增变量重置(保存在内存中,断电即失)
- 而MYISAM继续从上一个自增量开始(保存在文件中,不会丢失)
-
truncate 会重置自增变量 ,不会影响事务
-
DQL查询数据
数据查询语言
- 所有的查询都用它 select
- 简单的查询,复杂的查询它都能做
- 数据库最核心的语言
- 使用频率最高
基本查询
select 字段 from 表
- select * from 表 查询所有的表中数据
- select
字段名
as 别名 from 表名 查询出来的数据显示别名,可以给字段或者表起别名 - 函数 concat(a,b)
- select concat(“名字”,
字段名
) as 别名 from 表名 查询出来的结果都是名字 + 字段名(千万记得引号和斜引号的区别)
- select concat(“名字”,
进阶查询
- select version() 查询系统版本
- **select @@auto_increment_increment **查询自增步长
数学计算
- **select 100*3-1 ** 计算结果
结果显示修改查看(+1分查看)
- select 字段 +1 from 表名
去重
- **select distinct(字段)from 表名 ** 发现重复数据,去重
where条件子句
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a && b | 与 |
or || | a or b a || b | 或 |
Not ! | 非 |
模糊查询
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,结果为真 |
is not | a is not null | 如果操作符不为null,结果为真 |
between | a between b and c | 如果 a 在 b和 c之间 ,return true |
like | a like b | sql匹配,如果a匹配b,return true |
in | a in(a1,a2,a3) | 如果a在a1,a2,a3值中,return true |
like
- SELECT 字段1,字段2 FROM 表名 WHERE 字段1 LIKE ‘赵_’ 字段1以赵开头,且后面只有一个字,_ 下划线数量代表字数。
- SELECT 字段1,字段2 FROM 表名 WHERE 字段1 LIKE ‘赵%’ 字段1以赵开头。
- SELECT 字段1,字段2 FROM 表名 WHERE 字段1 LIKE ‘%其%’ 字段1 中有其字。
in
- SELECT 字段1,字段2 FROM 表名 WHERE 字段1 in (1,2,3) 查询字段1为1或2或3
关联查询
on后面的是连接条件,代表两个表建立关系所遵循的规则
where后面的可以看作是筛选条件,是对最终结果集进行过滤所遵循的规则
inner join
查出并集
-
**select 别名1.字段1(字段1在表一表二中都有,因此要确定那个表的字段一,即用别名.字段1),字段2,字段3** **from 表一 as 别名1 ** **inner join 表二 as 别名2** **on 别名1.字段一 = 别名2.字段一**
right join
-
**select 别名1.字段1(字段1在表一表二中都有,因此要确定那个表的字段一,即用别名.字段1),字段2,字段3** **from 表一 as 别名1 ** **right join 表二 as 别名2** **on 别名1.字段一 = 别名2.字段一**
lifft join
-
**select 别名1.字段1(字段1在表一表二中都有,因此要确定那个表的字段一, 即用别名.字段1),字段2,字段3** **from 表一 as 别名1 ** **lift as 别名2** **on 别名1.字段一 = 别名2.字段一**
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
自连接
自己的表和自己的表连接,核心:一张表拆成两张一样的表即可
- 父类
categoryId | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
- 子类
categoryID | categoryName | pid |
---|---|---|
4 | 数据库 | 3 |
8 | 办公信息 | 2 |
6 | web开发 | 3 |
7 | ps技术 | 5 |
操作:父类对应的子类
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
select 父.字段 , 子.字段
from 父表 as 别名 父 , 子类 as 别名 子
where 父.字段1 = 子.字段2
排序 和分页
降序(desc)和升序(asc)
select 字段
from 表名
inner join 表名 /....
on 条件
where 条件
order by 字段 desc / asc --严格按照以上顺序
分页
-
limit 起始值 ,页面大小
-
第一页 limit 0,5 (每页显示5条数据)
-
第二页 limit 5,5
-
第三页 limit 10,5
-
第N页 limit (N-1)*5,5
-
总页数为 数据总数 + (每页数据量-1)/每页数据量
函数
常用函数
数学运算
abs(-8) --绝对值
ceiling(9,4) --向上取整
floor(9,4) -- 向下取整
rand() -- 返回一个 0-1之间的随机数
sign() -- 判断一个数的符号
字符串
char_length() --返回字符串长度
concat() --拼接字符串
insert() --插入字符串,或者替换
lower() -- 转大写
upper() -- 转小写
instr() -- 返回第一次出现子串的位置
replace() -- 替换出现的字符串
substr() -- 返回指定的字符串
reverse() -- 翻转字符串
时间和日期函数
current_date() -- 获取当前日期
current() -- 获取当前日期
now() -- 获取当前的时间
localtime() -- 获取本地时间
sysdate() -- 获取系统时间
聚合函数
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均 |
max() / min() | 最大/最小 |
分组和过滤
- where中不能有聚合函数 可以使用having
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fONKVbwD-1596546740951)(http://img.zzzyuan.cn/image-20200801170028819.png)]
数据库级别的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`)
)DEFAULT CHARSET=utf8
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wanwu','123456');
UPDATE testmd5 SET pwd =MD5(pwd) --加密全部密码
INSERT INTO testmd5 VALUES(1,'zhangsan',MD5('123456')); --边插入边加密
事务
什么是事务
事务的原则性 (原子性,一致性,持久性,隔离性)
原子性
要么都成功,要么都失败
一致性
事务前后的数据保持一致
持久性
事务一旦提交就不可逆,被持久化到数据库中
隔离性
事务的隔离是多个用户并发访问数据库时,数据库为每一个用户开启的事物,不能被其他事物操作的数据所干扰,事物之间要互相隔离。
隔离导致的一些问题
- 脏读:指一个事物读取了另一个事物未提交的数据。
- 不可重复度:在一个事物内读取表中的某一行数据,多次读取的结果不同(这个不一定是错误,只是某些场合不对)
- 虚读(幻读):是指一个事物内读取到了别的事物插入的数据,导致前后读取不一致。
事物的实现
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f45wYClf-1596546740952)(http://img.zzzyuan.cn/image-20200801174003471.png)]
索引
mysql 官方对索引的定义为:索引是帮助mysql高效获取数据的数据结构。提取句子的主干,就可以得到索引的本质:索引是数据结构。
索引的分类
- 主键索引
- 唯一标识,主键不可重复,只能有一个列作为主键。
- 唯一索引
- 避免重复的列出现,唯一索引可以有多个,多个列都可以标识为唯一索引
- 常规索引
- 默认的 index,key 关键字来设置
- 全文索引
- 在特定的数据库引擎下才有,MYISAM
- 快速定位数据
索引的使用
- 创建时指定索引
- 创建完毕后添加索引
显示所有索引
show index from 表名
增加一个全文索引
alter table 表名 add fulltext index 索引名(字段名) -- 一般两个名字相同
explain 分析sql执行的情况
explain select * from 表名 -- 非全文索引
explain select * from 表名 where match(字段名) against(特定条件 例如:字段名中有刘)
索引测试
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 ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
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),CONCAT('123456',i,'@qq.com'),CONCAT('18',FLOOR(RAND()*10000000)),
FLOOR(RAND()*2),UUID(),FLOOR(RAND()*2));
SET i = i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data(); -- 执行函数
SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询速度极慢0.7s
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询了9700多条
-- id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_name ON app_user(`name`); -- 添加索引
SELECT * FROM app_user WHERE `name` = '用户9999' -- 添加索引后查询数独为0.001s
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询了一条
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
- hash 类型的索引
- btree :innoDB 的默认数据结构
权限管理与备份
权限管理
--创建用户
create user 用户名 identified by '密码'
-- 修改密码
-- 当前用户密码
set password = '密码'
-- 修改指定用户密码
set password for 用户名= password('密码')
-- 重命名
pename user 原名 to 新名
-- 用户授权
--授予全部权限(除了给其他人权限)
grant all privileges *.* to 用户名
-- 查看权限
--查看非管理员的权限
show grants for 用户名
--查看管理员的权限
show grants for roo@localhost
-- 撤销权限
-- 撤销所有权限
revoke all privileges on *.* from 用户名
-- 删除用户
drop user 用户名
数据库备份
-
直接拷贝物理文件
-
在可视化工具中手动导出
-
使用命令行导出
- mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 地址和文件名 (加表名导出表,不加表名导出数据库)
-
使用命令行导入
- 登录账户
- source 备份文件地址
规范数据库的设计
为什么需要设计
- 糟糕的数据库设计
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】
- 良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统