学习笔记备份
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
登录
mysql -u root -h 127.0.0.1 -p
退出
exit
命令:(不区分大小写,关键字建议大写,以分号结尾)
SHOW DATABASES; – 查询所有数据库名称
DDL:操作数据库,表
操作数据库:CRUD
CREATE:创建;
create database 数据库名称;
create database if not exists 数据库名称;
create database 数据库名称 character set gbk;
RETRIEVE:查询;
SHOW DATABASES; – 查询所有数据库名称
show create database news; – 查看某个数据库的创建语句(查看某个数据库的字符集)
UPDATE:修改;
alter database 数据库名称 character set utf8;
DELETE:删除;
drop database 数据库名称;
drop database if exists 数据库名称;
使用数据库
select database(); – 查询当前正在使用的数据库
use database; – 使用数据库
操作表 CRUD
CREATE:创建;
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n
);
数据库类型:
int,double,date(只包含年月日),datatime(包含年月日时分秒)
timestamp(如果将来不给这个字段赋值,则默认使用当前的系统时间,来自动赋值)
varchar(),
create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
);
create table stu like student; -- 复制表
RETRIEVE:查询;
show tables;-- 查询某个数据库中所有的表名称
desc 表名;-- 查询表结构;
UPDATE:修改;
alter table 表名 rename to 新的表名; -- 修改表名;
show create table stu;
alter table stu character set gbk; -- 修改表的字符集;
alter table 表名 add 列名 数据类型; -- 添加一列;
alter table 表名 change 列名 新列名 数据类型; -- 修改列名称,类型;
alter table 表名 modify 列名 数据类型; -- 只修改列的类型
alter table 表名 drop 列名; -- 删除列;
DELETE:删除;
drop table 表名;
drop table if exists 表名;
DML:增删改表中数据
添加数据:
insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);
注意:列名和值一一对应
删除数据:
delete from 表名 where 条件;
注意: 如果不加条件,则默认删除所有记录
truncate table 表名; – 删除表再创建一个一模一样的空表
修改数据:
update 表名 set 列名1=值1,列名=值2,…where 条件;
注意:如果不加条件,则所有记录都将被修改
DQL:查询
select * from 表名;
排序查询:
order by 排序字段1 排序方式1,排序字段2,排序方式2…
升序:默认ASC 降序:DESC
聚合查询:将一列的数据作为一个整体,进行纵向的排序
count:计算个数
max:计算最大值
min:计算最小值
sum:计算和
avg:计算平均值
注意:聚合函数的计算,排除null的计算
select count(ifnull(English,0)) from stu;
分组查询:
group by 分组字段;
注意:分组之后查询的字段:分组字段,聚合函数
where和having的区别?
1,where在分组之前进行限定,如果不满足条件,则不参与分组;having在分组之后进 行限定,如果不满足结果,则不会被查询出来。
2,where之后不能跟聚合函数,having可以进行聚合函数的判断。
分页查询:
limit 开始的索引,每页查询的条数;
公式:开始的索引=(当前的页码-1)*每页显示的条数
约束(对表中的数据进行限定,保证数据的正确性,有效性,完整性)
分类: 主键约束 primary key 非空且唯一
alter table student drop primary key; – 删除主键
非空约束 not null
alter table student modify name varchar(20) not null;
唯一约束 uniqu 值不能重复
注意:mysql中国,唯一约束限定的列的值可以有多个null
alter table student drop index 列名 – 删除唯一约束
外键约束 foreign key
create table 表名(
...
外键列
constrait 外键名称 foreign key (外键列名称) reference 主表名称(主表列名称)
); -- 创建表时添加外键
alter table 表名 drop foreign key 外键名称 -- 删除外键
alter table 表名 add constraint emp_dep_fk foreign key (dep_id) references department(id) -- 添加约束
自动增长 如果某一列是数值类型的,使用auto_increment可以完成值自动增长
alter table stu modify id int auto_increment -- 添加自动增长
级联操作 添加外键时,设置级联更新,设置级联删除
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;
多表之间的关系
一对一:可以在任意一方添加外键指向另一方的主键,让外键唯一
一对多(多对一):在多的一方建立外键,指向一的主键
多对多:需要借助中间表,至少包含两个字段(两个表的主键),这两个字段作为外键分别指向两个表(联合主键)
范式
第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF的基础上消除非主属性对主码的部分函数依赖)
第三范式(3NF)
数据库的备份和还原
命令行
备份 mysqldump -u 用户名 -p 密码 数据库名称>保存的路径
还原:
登录数据库
创建数据库
使用数据库
执行文件 source 文件路径
图形化界面
基础查询
多个字段的查询
去除重复 select distinct 列名 from 表名
计算列 ifnull(列名,替代值)
起别名 as
条件查询
where
运算符
比较运算符< > <= >= = != <>
select * from stu where age!=20;等价于select * from stu where age<>20;
between and
in
select * from stu where age<>20;等价于select * from stu where age in(22,19,25);
like 模糊查询
占位符:_ 单个任意字符
% 多个任意字符
is null
and
or
not
笛卡尔积
有两个集合A,B,取这两个集合的所有组成情况
要完成多表查询,需要消除无用的数据
多表查询
内连接查询
隐式内连接 使用where连接消除无用数据
显示内连接 select 字段列表 from 表名1 [inner] join 表名2 on 条件
外连接查询
左外连接 :查询左表所有信息及交集部分
select 字段列表 from 表名1 left [outer] join 表2 on 条件
右外链接 :查询右表所有信息及交集部分
select 字段列表 from 表名1 right [outer] join 表2 on 条件
子查询:查询中嵌套子查询
select * from employee where salary =(select max(salary) from employee);
子查询的不同情况
子查询的结果是单行单列的(子查询作为条件,使用运算符去判断)
子查询的结果是多行单列的(子查询作为条件,使用运算符in来判断)
子查询的结果是多行多列的(子查询作为虚拟表)
事务
事务的基本介绍
概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
操作:1,开启事务 start transation
2,回滚 rollback
3,提交 commit
MySql数据库中事务默认自动提交,oracle数据库默认手动提交
一条DML(增删改)语句会自动提交一次
修改事务提交方式
查看事务默认提交方式 select @@autocommit; – 1代表自动提交,0代表手动提交
修改默认提交方式 set @@autocommit=0;
事务的四大特征:原子性,持久性,隔离性,一致性(事务操作前后,数据总量不变)
事务的隔离级别(了解)
多个事务之间隔离的,相互独立,如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别可以解决。
存在问题:
脏读:一个事务读取到另一个事务中没有提交的数据
不可重复读:在同一个事务中,两次读取到的数据不一样
幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
隔离级别:
read uncommitted:读未提交
产生的问题:脏读,不可重复读,幻读
read committed:读已提交(oracle)
产生的问题:不可重复读,幻读
repeatable read:可重复读(MySql)
产生的问题:幻读
serilizable:串行化
隔离级别从小到大安全性越来越高,但效率越来越低
数据库查询隔离级别:select @@tx_isolation(8.0之前版本)
select @@transaction_isolation;(8.0版本)
数据库设置隔离级别:set global transaction isolation level 级别字符串
DCL:管理用户,授权%
use mysql;
添加用户:create user ‘用户名’@‘主机名’ identified by ‘密码’
删除用户:drop user ‘用户名’@‘主机名’
修改用户密码:update user set password=’’ where user=‘用户名’
mysql中忘记密码:1,cmd 停止mysql服务net stop mysql80
2,以无验证方式启动mysql服务:mysqld --skip-grant-tables
3,打开新的cmd窗口,输入mysql,修改密码
4,关闭窗口,打开任务管理器,任务管理器关闭mysqld服务
查询用户:use mysql;
select * from user;
授权:查询权限:show grants for ‘用户名’@‘主机名’
授予权限:grant 权限列表 on 数据库.表名 to ‘用户名’@‘主机名’
授予所有权限 all 所有数据库和表 .
撤销权限:revoke 权限列表 on 数据库.表名 from ‘用户名’@'主机名