基本命令
所有的命令都需要以分号结尾
mysql -uroot -proot --进入数据库 ; -p后面写密码,不带空格
exit --退出mysql
--:单行注释
/*
多行注释
*/
show databases; --查看所有的数据库
use 数据库名; --切换数据库
show tables --查看数据库内所有的表
describe class; --查看class表内的具体信息
create database 数据库名;--创建一个数据库
systemctl restart mysqld --重启mysql
操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] wkx;
2、删除数据库
DROP DATABASE [IF EXISTS] qcby;
3、使用数据库
-- tab 键的上面,如果你的表名或者字段名是一个特殊字符,就需要带``
USE `school`;
列类型
字段属性
创建一个表
create table if not exists student (
id int(4) not null auto_increment comment '学号',
name varchar(30) not null default '匿名' comment '姓名',
psw varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '男' comment '性别',
birthday datetime default null comment '出生日期',
`email` VARCHAR(50) default null comment '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
show create database ssm --查看已经创建数据库的语句
show create table student --查看已经创建的表的语句
desc student --显示表结构
类型,引擎
innodb :默认使用
myisam:早些年使用
myisam | innodb | |
事务支持 | 不支持 | 支持 |
数据行锁定(一行为临界资源) | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大 |
文件存储位置: 所有的数据库都存储在data目录下,本质还是文件
修改表
都是以alter table 表名起头
修改表名
alter table 旧表名 rename as 新表名
增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
alter table 表名 add age int(3)
修改表的字段(重命名,修改约束!)
ALTER TABLE teacherl MODIFY age VARCHAR (11) --修改约束
ALTER TABLE teacher1 CHANGE age agel INT (1) --字段重名名
change和modify的区别:modify只可以修改约束,change可以重命名和修改约束
删除表的字段
alter table 表名 drop 字段名 : ALTER TABLE teacher1 DROP age1
删除表:
drop table if exists 表名;
所有的创建和删除尽量加上判断,以免报错
mysql的数据管理
外键
ALTER TABLE 表 ADD CONSTRAINT
约束名 FOREIGN KEY(作为外键的列)REFERENCES 那个表(哪个字段)
ALTER TABLE student ADD CONSTRAINTFK `fk_gradeid` FOREIGN KEY ( "gradeid*) REFERENCES `grade` ( `gradeid` );
添加
insert into表名(宇段名1,宇段2,字段3)values('值1','值2','值3')
插入多个字段
INSERT INTO `grade` (gradename) VALUES('大二'),( '大一') VALUES 后面的值需要使用逗号隔开即可(),()
修改
UPDATE 表名 set colnum_name=value, colnum_name = value,.... where 条件
UPDATE student SET birthday = CURRENT_TIME WHERE name = '张三' AND sex='女' 一些特殊的条件
between and: where id between 2 and 5 (闭合区间)
删除
- 删除指定数据
DELETE FROM student WHERE id = 1;
-清空 student 表
TRUNCATE table student
delete 的 TRUNCATE 区别
相同点:都能删除数据,都不会删除表结构
不同:
- TRUNCATE 重新设置自增列 计数器会归零
- TRUNCATE 不会影响事务
DELETE删除的问题 重启数据库:
- InnoDB 自增列会从1开始(存在内存当中 断电即失)
- MyISAM 继续从上一个自增量开始(存在磁盘中 不会丢失)
查询
各类关键字的顺序是固定的,比如limit一定需要写在order by的下面
别名,给结果起一个名字 AS
SELECT StudentNo AS 学号,StudentName AS 学生姓名 FROM student as 学生表
拼接字符串:函数:Concat(a,b) :,b是要查询的字段,a是想要拼接的
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
去重查询 distinct
SELECT DISTINCT StudentNo FROM result
where条件
搜索的条件返回一个布尔值
and查询 >=
SELECT studentNo,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100
模糊查询
使用like , %可占任意个字符 , _代表一个字符
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE '刘%' / '刘_' :刘后面只有一个字符
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE '%嘉%'
使用IN : 必须是具体的一个或多个值 , 不可以与百分号一起用
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (1001,1002, 1003);
使用 is null 和is not null ---查询某一个字段为空或者不为空
联表查询
join(表) on (条件)
首先分析查询的字段来自哪两个表,并写出要查的信息
按照需求确定使用哪种连接,(看问题中想要哪个表的全部信息)
确定交叉点:连接表的条件
如果有多张表,先查询两张表,然后再慢慢加
操作 | 描述 |
inner join | 两表都存在匹配,返回行 |
left join | 返回左表全部数据 |
right join | 返回右表全部数据 |
分页和排序
这里需要注意order by 和limit的前后顺序
- 最简单的排序:order by 字段 asc/desc (升序和降序)
- 分页:limit a , b a:跳过几条数据, b:显示几条数据
- 比如limit 5,7 跳过5条数据,即从第六条数据开始显示,一共展示7条数据
- 查询第n页的公式: limit (n-1)*pageSize , pageSize pageSize:页面大小
子查询
where里嵌套一个查询语句 : where (select * from ...) 由里及外 建议使用join联表
SELECT StudentNo,SubiectNo,StudentResult
FROM result
where SublectNo = (
SELECT SubiectNo FROM subiect
WHERE SubjectName ='数据库结构-1'
)
--对比联表查询
SELECT StudentNo,r.SubjectNo,studentResult
FROM result r
INNER JOIN
subject sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName ='数据库结构-1'
究极嵌套子查询
SELECT StudentNo, StudentName FROM student WHERE StudentNo IN(
SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo =(
SELECT SubiectNo FROM subject WHERE SubjectName = '高等数学-21'
)
)
分组过滤
查询不同课程的平均分,最高分,最低分,平均分大于80
核心:根据不同的课程分组
SELECT SubjectName, AVG (StudentResult) As 平均分,MAX(StudentResult) As 最高分,MIN (StudentResult) As 最低分
from result r
inner join subject sub
on r.subjectno = sub.subjectno
group by r.subjectno --通过课程号来分组
having 平均分>=80 --having过滤分组之后的约束,这里不可以用where
mysql函数
数学运算
ABS() 绝对值函数
ceiling() 向上取整
floor() 向下取整
rand() 返回一个0-1之间的随机数
字符串函数
char_length() 计算字符串的长度
concat("a","b","c") 拼接字符串
lower() 把所有字母转小写
upper() 把所有字母转大写
insert("wangkax","a") 从1开始,返回a第一次出现的索引
replace("wwkkww","kk","ww") 会将kk替换为ww
substr("123456",3,3) 从1开始, 从第三个开始截取,截取3个字符 === 结果是:345
reverse() 翻转字符串
时间和日期函数
current_date() / curdate() 获取当前日期 1990-01-01
now() 获取当前时间 1990-01-01 12:01:01
localtime() 本地时间
sysdate() 系统时间
获取当前的年月日时分秒
-
- SELECT YEAR(now())
- SELECT MONTH(now())
- SELECT DAY (now())
- SELECT HOUR(now())
- SELECT MINUTE(now())
- SELECT SECOND (now())
系统
select user() 查询当前mysql登录用户
select version() 查询当前mysql的版本
聚合函数
count:查询表中有多少个记录
SELECT COUNT (BornDate) from student --count(字段) 会忽略所有的null值
SELECT COUNT (*) from student --不会忽略null值,本质计算多少行
SELECT COUNT (1) from student --不会忽略null值,本质计算多少行
事务
要不都成功,要不都失败
事务原则:ACID 原则 : 原子性,一致性,隔离性,持久性
- 原子性:要么都成功,要么都失败
- 一致性: 事务前后的数据完整性要保证一致,比如A给B转账,转账的总数需固定
- 隔离性: 多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作干扰.
- 持久性: 事务一旦提交不可逆,没有提交就恢复原状
隔离所导致的一些问题
- 脏读:指一个事务读取了另外一个事务未提交的数据。
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
- 虚读(幻读)是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
mysql是默认开启事务的, SET autocommit = 0 :关闭 =1 开启
当我们想要手动开启事务时:
- set autocommit = 0; 先关闭自动提交
- start transaction --标记一个事务的开始,从这个之后的 sql 都在同一个事务内
然后执行sql语句
- 成功就commit(提交) 并完成持久化
- 失败就rollback(回滚) 回到原来的样子
- 最后set autocommit =1 开启自动提交
索引
索引可以更快速的获取数据 将查询的时间复杂度由o(n)降到o(logn)
给某个字段加了索引,where这个字段时就会提升速度
索引的分类:
- 主键索引 (PRIMARY KEY)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引 (KEY/INDEX)
- 默认的, index key 关键字来设置
- 全文索引 (FullText)
- 在特定的数据库引擎下才有,MySAM
- 快速定位数据
索引不是越多越好,
不要对经常变动的数据加索引
小数据量不加索引
索引使用在经常使用查询的数据.
权限管理和备份
权限管理
默认用户是root
我们可以创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER wangkaixuan IDENTIFIED BY 1123456
修改密码 (修改当前用户密码)
SET PASSWORD PASSWORD('123456')
修改密码(修改指定用户密码)
SET PASSWORI FOR wangkaixuan = PASSWORD ('111111")
重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER wkx ro wkx2
用户授权 ALL ERIVILEGES 全部的权限 on 库.表 to 用户
这里的全部权限不包括grant(给别人授权) 所以即使授权所有权限,依然低于root
GRANT ALL PRIVILEGES ON *.* TO wkx
查看指定用户的权限
show grants FOR wkx
撤销权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM wkx
删除用户
DROP USER wkx
备份
备份的方式
- 直接拷贝物理文件
- 在可视化程序导出
- 命令:mysqldump
mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot qcby student >/Users/wangkx/Desktop/a.sql
导入数据
- 登录mysql : mysql -uroot -proot
- 切换到指定数据库 use qcby (如果导入数据库省略这步)
- source 备份文件 source Desktop/a.sql
三大范式
- 第一范式:原子性,保证每一列都不可再分
- 第二范式:满足第一范式,每张表只描述一件事情
- 第三范式:满足一二范式,需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范性 和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下 规范性!
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)