MySql
服务
管理员身份打开cmd
net start mysql //启动mysql服务
net stop mysql //关闭mysql服务
登录
//用户名为root,密码为1234
mysql -uroot -p1234
mysql -uroot -p //回车再密文输入密码更安全
//远程登录
mysql -h127.0.0.1 -uroot -p
mysql --host=127.0.0.1 --user=root --password=1234 //完整写法
//退出
exit
quit
//备份
mysqldump -uroot -p1234 数据库名 > 保存路径
//还原, 登录并进入对应数据库
source 文件路径
SQL
Structured Query Language
结构化查询语言,定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为”言“,mysql中只有登录后输入的才被视为sql语句
SQL分类
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DQL: Data Query Language
- DCL: Data Control Language
SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- 可以使用空格或缩进增强语句的可读性
- MySql数据库的SQL语句不区分大小写,关键字建议大写
- 注释:单行注释 -- 注释内容 或 # 注释内容;多行注释 /* 注释内容 */
操作数据库DDL
- Create创建
- 创建数据库(已存在会报错)
- create database 数据库名称;
- 如果不存在同名数据库则创建
- create database if not exists dbName;
- 创建时指定字符集
- create database dbName character set gbk;
- 创建时既判断又指定字符集
- create database if not exists dbName character set gbk;
- 创建数据库(已存在会报错)
- Retrieve查询
- 查询所有数据库的名称
- show databases;
- 查询某个数据库的字符集(查询某个数据库的创建语句)
- show create database 数据库名称;
- 查询所有数据库的名称
- Update修改
- 修改数据库字符集(不能写成uft-8)
- alter database dbName character set utf8;
- 修改数据库字符集(不能写成uft-8)
- Delete删除
- 删除数据库(不存在会报错)
- drop database dbName;
- 如果存在则删除数据库
- drop database if exists dbName;
- 删除数据库(不存在会报错)
- 使用数据库
- 查询当前正在使用的数据库名称
- select database();
- 使用某个数据库
- use dbName;
- 查询当前正在使用的数据库名称
操作表DDL
-
Create
-
创建表(最后一列不能加逗号)
-
table tableName(
? 列名1 数据类型1,
? ...
? 列名n 数据类型n
);
-
-
常见数据类型
- int, 整型
- doublem, double(5, 2)表示一共5位,小数点后保留2位,最大值999.99
- date, 只包含年月日,yyyy-MM-dd
- datetime, 包含年月日时分秒,yyyy-MM-dd HH:mm:ss
- timestamp, 时间戳,如不赋值哐赋null,则自动存入当前系统时间
- varchar, varchar(20)表示最大20个字符
- 一般不把大文件放入数据库,只放文件的磁盘路径
-
复制表
- create table newName like tableName;
-
-
Retrieve
- 查询当前数据库中所有表的名称
- show tables;
- 查询表结构
- desc tableName;
- 查询当前数据库中所有表的名称
-
Update
- 修改表名
- alter table tableName rename to newName;
- 修改表的字符集
- alter table tableName character set 字符集名称
- 添加一列
- alter table tableName add 列名 数据类型
- 修改列名称 类型
- alter table 表名 change 列名 新列名 新数据类型
- alter table 表名 modify 列名 新数据类型
- 删除列
- alter table 表名 drop 列名
- 修改表名
-
Delete
- 删除表
- drop table tableName;
- drop table if exists tableName;
- 删除表
操作记录DML
- 添加数据
-
insert into 表名(列名1, 列名2, ... , 列名n) values(值1, 值2, ... , 值n);
- 列名和值要一一对应
- 如果表名后不定义列名则默认给所有列添加值
- 除了数字类型,其他类型需要使用引号(单双均可)引起来
- 没有值的时候可以传null
-
insert into 表名(列名1, 列名2, ... , 列名n) values(值1, 值2, ... , 值n);
- 删除数据
-
delete from 表名 [where 条件]
- 如果不加条件,则删除表中所有记录,执行次数太多建议使用truncate table t
- truncate table 表名
- 删除表中所有记录,删除表,然后创建一个一模一样的空表
-
delete from 表名 [where 条件]
- 更改数据
-
update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件];
- 如果不加条件,则修改表中所有数据
-
update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件];
约束
对表中的数据进行限定,保证数据的正确性、有效性和完整性
-
分类
- 非空约束:not null
- 唯一约束:unique (不限定null)
- 主键约束:primary key
- 非空且唯一,一张表只能有一个主键,是表中记录的唯一标识
- 外键约束:foreign key
- 让表与表之间建立关系
- 自动增长:auto_increment
- 完成值的自动增长,传null的时候生效,可以手动赋值,自动增长只看上一条记录的值加一
-
操作
-
创建表时添加约束,跟在数据类型后
create table stu(id int, name varchar(20) not null);
create table stu(id int, . . . , constraint (外键名) foreign key 外键列名 references 主表名(主表列名称)); #添加外键
-
删除约束,唯一和主键约束删除特殊
alter table stu modify name varchar(20);
alter table stu drop index name; #删除唯一约束
alter table stu drop primary key; #删除主键
alter table 表名 drop foreign key 外键名; #删除外键
-
创建表后添加约束
alter table stu modify name varchar(20) not null;
alter table stu add constraint (外键名) foreign key 外键列名 references 主表名(主表列名称)); #添加外键
-
级联操作
- 在外键约束后加 on update cascade / on delete cascade
- 删除或更新数据时会同时操作所有与其相关联的数据
-
查询记录(单表)DQL
select 字段列表 from 表名 where 条件列表 group by 分组字段 having 分组后的条件 order by 排序 limit 分布限定
列表用逗号分开,可以用*代指查询所有
-
基础查询
- 结果去重:在字段列表前加distinct,必须所有选中字段一样
-
结果计算:可计算类型的名字加四则运算符
- select name, math, english, math english from stu;
-
null判断:ifnull(字段名,替换值)
- 有null参与的计算结果为null,可以用ifnull()解决
- select name, math, english, math ifnull(english, 0) from stu;
-
起别名:字段名 as 别名,as可以用空格代替
- select name, math, english, math ifnull(english, 0) as sum from stu;
- select name, math, english, math ifnull(english, 0) sum from stu;
-
条件查询
where子句后跟条件
-
运算符
> 、<、<=、>=、=、<>(不等于符号,也可以用 != )
between...and... (也可以用and实现)
-
in(集合) (也可以用or实现)
select * from stu where age in (15, 25, 35);
is null,is not null (null值不能用基本运算符判断)
and &&,or ||,not ! (推荐使用字母符号)
-
like (模糊查询)
占位符:_ 表示单个任意字符;* 表示多个任意字符;
select * from stu where name like ‘__%‘; #查询姓名至少有两个字的人
-
排序查询
- order by 排序字段1 排序方式1, 排序字段2 排序方式2 . . .
- 先按字段1排序,如果字段1一样,则按字段2排序 . . .
- 排序方式有升序ASC和降序DESC,默认按升序
-
聚合函数
把一列数据作为一个整体进行计算,结果为单行单列,如count(math)
count、max、 min、 sum、 avg
-
自动排除null值,count时不方便可以用ifnull解决或选择非空的列或count(*)
select count( ifnull( english, 0 ) ) from stu;
-
分组查询
- group by 分组字段;
- 分组之后查询的字段要么是分组字段,要么是聚合函数,其他字段没有意义
- 可以在分组之前用where进行一些筛选工作,如果不满足条件则不参与分组
- 可以在分组之后用having进行限定,不满足条件则不会被最终查询出来
- where后不能跟聚合函数,having后可以跟聚合函数的判断
-
分页查询
- limit 开始的索引 每页查询的条数
- 公式:开始的索引 = (当前页码 - 1) * 每页显示的条数
- limit关键字是mysql的方言
多表查询
select * from a, b; 结果是ab两张表的笛卡尔积,两个集合所有组成情况
-
内连接查询
查询两张表交集部分
-
隐式内连接:select a.name from a, b where a.bid = b.id; bid和id可以加引号
一般在from后面的表后加别名
-
显式内连接:
select * from table1 inner join table2 on 条件;
select * from table1 join table2 on 条件;
- 要确定查询的表,条件,字段
-
-
外连接查询
-
左外连接(常用,可以代替右外连接)
select * from table1 left [outer] join table2 on 条件;
查询左表所有数据以及其与右表交集部分
-
右外连接
select * from table1 right [outer] join table2 on 条件;
查询右表所有数据以及其与左表交集部分
-
-
子查询
查询中嵌套查询,子查询加括号
- 子查询的结果是单行单列的
- 作为一个值参与父查询的条件判断
- 子查询的结果是多行单列的
- 作为一个集合参与父查询的条件判断
- 子查询的结果是多行多列的
- 作为一张表参与父查询的from参数,需要取别名,可以用内连接代替
- 子查询的结果是单行单列的
管理用户及授权 DCL
用户和授权信息存在自带的mysql数据库中,先use mysql切换到mysql数据库,主机名为%时表示可以在任意主机上登录
-
管理用户
添加用户:create user ‘用户名‘@’主机名‘ identified by ‘密码‘;
删除用户:drop user ‘用户名‘@’主机名‘;
-
更改用户:update user set password = password(‘新密码‘) where user = ‘用户名‘;
? set password for ‘用户名‘@’主机名‘ = password(‘新密码‘);
查询用户:select * from user;
-
忘记管理员密码
- 停止mysql服务:cmd --> net stop mysql
- 使用无验证方式启动mysql服务:mysqld --skip-grant-tables
- 打开新的cmd窗口,直接输入mysql回车,就可以登录成功
- 修改root用户密码
- 关闭两个窗口,手动停止服务
- 启动服务,用新密码登录
-
管理权限
查询权限:show grants for ‘用户名‘@’主机名‘;
-
授予权限:grant 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名‘;
? 权限包括:select, delete, update
? grant all on 星号.星号 to ‘用户名‘@’主机名‘; (通配符)
撤销权限:revoke 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名‘;
事务
- 概念:如果多个步骤被事务管理,那么这些操作要么同时成功,要么同时失败
- 操作:
- 开启事务:start transaction;
- 回滚:rollback;
- 提交:commit;
- mysql中单条增删改事务默认自动提交,手动提交需要先开户事务再提交,oracle默认手动提交
- 查看当前提交方式 select @@autocommit; #1代表自动提交,0代表手动提交
- 操作:
-
事务的四大特征
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
- 持久性:当事务提交或回滚后,数据库会持久化地保存数据
- 隔离性:多个事务之间相互独立
- 一致性: 事务的执行使数据从一个状态转换为另一个状态,但是对于整个数据的完整性保持稳定
- 事务的隔离级别
- 概念:多个事务同时操作同一批数据时会引发一些问题,设置不同的隔离级另就可以解决这些问题
- 存在问题:
- 脏读:一个事务读取到另一个事务没有提交的数据
- 不可重复读:在同一个事务中,两次读取到的数据不一样
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
- 隔离级别(等级越高效率越低)
- read uncommitted:读未提交,问题:脏读,不可重复读,幻读
- read committed:读已提交,问题:不可重复读,幻读 (oracle默认)
- repeatable read:可重复读,问题:幻读 (mysql默认)
- serializable:串行化,问题:无
- 设置隔离级别(需重新连接数据库)
- set global transaction isolation level 隔离级别
数据库设计
-
多表之间的关系
- 一对一(少见)
- 在任意一方建立指向另一方的主键,并使外键唯一,一般直接合成一张表
- 一对多
- 在多的一方建立外键,指向一的一方的主键
- 多对多
- 使用中间表,最少两个字段,作为外键分别指向两边的主键,中间表使用联合主键
- 一对一(少见)
-
范式
设计数据库时,需要遵循的一些规范
-
概念
函数依赖:如果通过A能确定B,则称B依赖于A,A可以是多个属性组成的属性组
完全函数依赖:通过属性组A中所有的属性才能确定B
部分函数依赖:cefp属性组A中部分的属性就能确定B
传递函数依赖:A确定B,B确定C,则C传递依赖于A
-
码:一张表中,一个属性或属性组被其他所有属性完全依赖,则其为该表的码
主属性:码中所有属性
非主属性:其他属性
-
第一范式1NF
每一列都是不可分割的原子项
- 问题:1. 存在严重的数据冗余;2. 添加和删除存在问题
-
第二范式2NF
1NF基础上,非码属性必须完全依赖于候选码(消除部分依赖)
- 只能解决数据冗余
-
第三范式3NF
2NF基础上,任何非主属性不依赖于其它非主属性(消除传递依赖)
-