文章目录
1. 数据相关操作
1.1 登录
-- u和p后面没有空格
mysql -u用户名 -p密码
mysql -hIP地址 -u用户名 -p密码
mysql --host=IP地址 --user=用户名 --password=密码
1.2 退出
quit 或 exit
1.3 数据库备份和还原
- 备份格式:DOS下,未登录时。(这是一个可执行文件exe,在bin文件夹)
mysqldump -u用户名 -p密码 数据库 > 文件的路径
- 还原格式:mysql中的命令,需要登录后才可以操作
use 数据库;
source 导入文件的路径;
2. SQL语句
2.1 DDL数据定义语言
2.1.1 操作数据库
- 创建数据库
-- 创建数据库
create database 数据库名;
-- 判断数据库是否已经存在,不存在则创建数据库
create database if not exists 数据库名;
-- 创建数据库并指定字符集
create database 数据库名 character set 字符集;
- 查看数据库
-- 查看所有数据库
show databases;
-- 查看某个数据库的创建语句
show create database 数据库名;
- 修改数据库
-- 修改数据库默认的字符集
alter database 数据库名 default character set 字符集;
- 删除数据库
drop database 数据库名;
-- 判断数据库是否存在,存在再删除
drop database if exists 数据库名;
- 使用数据库
-- 查看正在使用的数据库
select database(); #使用mysql中的全局函数
-- 使用/切换数据库
use 数据库名;
2.1.2 操作表结构
- 创建表
create table 表名 (
列名1 数据类型1,
列名2 数据类型2,
....
列名n 数据类型n
);
-- 最后一列,不需要加逗号
- 查看表
-- 查看某个数据库中的所有表
show tables;
-- 表格格式返回
desc 表名;
-- 以sql格式返回
show create table 表名;
- 快速创建一个表结构相同的表
create table 新表名 like 旧表名;
- 删除表
-- 直接删除表
drop table 表名;
-- 判断表是否存在,如果存在则删除表
drop table if exists 表名;
- 修改表结构
-- 添加新字段
alter table 表名 add 字段 字段类型;
-- 修改字段类型MODIFY
alter table 表名 modify 字段 新的类型;
-- 修改字段名CHANGE
alter table 表名 change 旧字段名 新字段名 类型;
-- 删除字段DROP
alter table 表名 drop 字段名;
-- 修改表名
rename table 表名 to 新表名;
alter table 表名 rename to 新的表名;
-- 修改字符集character set
alter table 表名 character set 字符集;
2.2 DML数据操纵语言
- 插入
-- 插入全部字段
insert into 表名 (列名1,列名2,列名3...) values (值1,值2,值3...);
-- 插入全部字段另一写法
insert into 表名 values (值1,值2,值3...)
-- 字符和日期型数据应包含在单引号中
-- 不指定列或使用null,表示插入空值
- 蠕虫复制:将一张已经存在的表中的数据复制到另一张表中
-- 将表2中的所有列复制到表1中
insert into 表名1 select * from 表名2;
-- 只复制部分列
insert into 表名1(列1,列2) select 列1,列2 from 表2;
- 更新表记录
update 表名 set 列名=值 [where 条件表达式]
-- 不带条件修改数据(修改所有行)
update 表名 set 字段名=值;
-- 带条件修改数据
update 表名 set 字段名=值 where 字段名=值;
- 删除表记录
delete from 表名 [where 条件表达式]
-- 不带条件删除数据[会删除表中所有记录]
delete from 表名;
-- 带条件删除数据
delete from 表名 where 字段名=值;
-- 使用truncate删除表中所有记录
truncate table 表名;
-- truncate和delete区别:
truncate相当于删除表的结构再创建一张表
2.3 DQL数据查询语言
select 列名 from 表名 [where 条件表达式]
2.3.1 简单查询
- 查询所有数据
select * from 表名;
- 查询指定列
select 字段名1,字段名2... from 表名; -- 多个列之间以逗号分隔
2.3.2 使用别名查询
- 显示的时候使用别名,但并不修改表结构
-- as可以省略
select 字段名1 as 别名,字段名2 as 别名... from 表名; -- 对列指定别名
select 字段名1 as 别名,字段名2 as 别名... from 表名 as 表别名; -- 列和表同时指定别名
2.3.3 清楚重复值
- 查询指定列时,结果不出现重复数据
select distinct 字段名 from 表名;
2.3.4 查询结果参与运算
-- 某列数据和固定值运算
select 列名1+固定值 from 表名;
-- 某列数据和其他列数据参与运算
select 列名1+列名2 from 表名;
-- 注:参与运算的必须是数值类型
2.3.5 条件查询
- 流程:取出表中的每条数据,满足条件的记录就返回,不满足条件的记录不返回
select 字段名 from 表名 where 条件;
- 运算符
比较运算符 | 说明 |
---|---|
<、>、<=、>=、=、<> | <> 在SQL中表示不等于,在mysql中也可以使用!= |
between…and | 表示在一个范围之内,包括边界值 |
in(集合) | 集合表示多个值,用逗号分隔 |
like ‘刘%’ | 模糊查询 |
is null | 查询某一列为null值,不能写=null |
逻辑运算符 | 说明 |
---|---|
and 或 && | 与 |
or 或 || | 或 |
not 或 ! | 非 |
- 关键字in
-- in里面的每个数据都会作为一次条件,满足条件就会显示
select 字段名 from 表名 where 字段 in(数据1,数据2...);
- 范围查询
between 值1 and 值2 -- 表示从值1到值2范围,包括值1和值2
- 关键字like:表示模糊查询
select * from 表名 where 字段名 like '通配符字符串';
通配符 | 说明 |
---|---|
% | 匹配任意多个字符串 |
- | 匹配一个字符 |
2.3.6 排序order by
- 通过order by子句,可将查询出的结果进行排序(排序只一种显示方式,不会影响数据库中数据的顺序)
- 升序[默认值]:asc;降序:desc
select 字段名 from 表名 where 字段=值 order by 字段名 [asc|desc];
- 单列排序:只按某一个字段进行排序
- 组合排序:同时对多个字段进行排序
select 字段名 from 表名 where 字段=值 order by 字段名1[asc|desc],字段名2[asc|desc];
2.3.7 聚合函数
-
之前的查询都是横向查询,即根据条件一行一行的进行判断;而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值
-
聚合函数会忽略空值null
-
SQL中的聚合函数
聚合函数 作用 max(列名) 求这一列的最大值 min(列名) 求这一列的最小值 avg(列名) 求这一列的平均值 count(列名) 统计这一列有多少条记录 sum(列名) 对这一列求总和 -
语法:
select 聚合函数(列名) from 表名;
- 对于null的记录不会统计,建议统计个数不要使用可能为null的列。若需要将null也统计进去:
-- 如果列名不为空,返回这列的值;如果为null,返回默认值
ifnull(列名,默认值)
-- 如果记录为null,给个默认值(这样统计数据不会遗漏)
select count(ifnull(id,0)) from 表名;
2.3.8 分组查询group by
- 使用group by语句对查询信息进行分组,将结果中相同内容作为一组,并返回每组的第一条数据
select 字段1,字段2... from 表名 group by 分组列 [having 过滤条件];
-
分组的目的是为了统计,一般分组会和聚合函数一起使用
-
分组查询若要添加条件使用having,而不使用where。
- having和where的区别
作用 where子句 在分组之前过滤数据;where后面不可以使用聚合函数 having子句 在分组之后过滤数据;having后面可以使用聚合函数
2.3.9 分页查询limit
- 限制查询记录的条数
select *|字段列表 [as 别名] from 表名 [where子句][group by子句][order by子句][limit子句];
-
语法格式:
-- offset:起始行数;从0开始计数,若省略则默认是0 -- length:返回的行数 limit offset,length;
2.4 DCL数据控制语言
2.4.1 创建用户
- 建立一个用户,默认自动授予其usage权限[usage:连接(登录)权限]
create user '用户名'@'主机名' identified by '密码';
-
关键字说明
关键字 说明 ‘用户名’ 将创建的用户名 ‘主机名’ 指定该用户在哪个主机上可以登录。本地用户:localhost;可从任意远程主机登录:使用通配符 % ‘密码’ 该用户的登录密码;密码可以为空,若为空则该用户可以不需要密码登录服务器
2.4.2 给用户授权
- 用户名、主机名要与创建时所写的相同,且加单引号
grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';
-
关键字说明
关键字 说明 权限 用户的权限,例:create、update、select等。所有的权限则使用all 数据库名.表名 对于哪些数据库的哪些表。所有数据库和表的相应操作权限则可用*表示
2.4.3 取消授权
revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';
-- 撤销所有权限
revoke all on 数据库名.表名 from '用户名'@'主机名';
2.4.5 查看权限
show grants for '用户名'@'主机名';
2.4.6 删除用户
drop user '用户名'@'主机名';
2.4.7 修改管理员密码
- 需要在未登录MySQL的情况下操作,新密码不需要加引号
mysqladmin -uroot -p password 新密码
2.4.8 修改普通用户密码
- 需要在登陆 MySQL 的情况下操作,新密码要加单引号
set password for '用户名'@'主机名' = password('新密码');
3. 数据库表的约束
3.1 约束的作用
- 对表中数据进行限制,保证数据的正确性、有效性和完整性。(约束在创建表的时候添加比较合适)
3.2 约束种类
约束名 | 约束关键字 |
---|---|
主键 | primary key |
唯一 | unique |
非空 | not null |
外键 | foreign key |
检查约束 | check [注:mysql不支持] |
3.3 主键约束
-
作用:用来唯一标识数据库中的每一条记录
-
哪个字段应作为表的主键?
- 通常不用业务字段作为主键,单独给每张表设计一个id字段作为主键;
- 主键是给数据库和程序使用的,不是给最终的客户使用的。故:主键有无含义没关系,只有不重复、非空就行。
-
主键的特点
- 非空(not null)、唯一
-
创建主键
-- 在建表的时候给字段添加主键
字段名 字段类型 primary key
-- 在已有表中添加主键
alter table 表名 add primary key(字段名);
- 删除主键
alter table 表名 drop primary key;
- 主键自增
- 若我们自己添加主键,很有可能重复。故一般在插入新记录时,让数据库自动生成主键字段的值
auto_increment -- 自动增长(字段类型必须是整数类型)
- 修改自增长的默认起始值
- 默认auto_increment的开始值是1
- 修改起始值:
-- 创建表时指定起始值
create table 表名(
列名 int primary key auto_increment
)auto_increment=起始值;
-- 创建好以后修改起始值
alter table 表名 auto_increment=起始值;
- delete和truncate对自增长的影响
- delete:删除所有记录之后,对自增长没有影响;
- truncate:删除以后,自增长又重新开始;
3.4 唯一约束
- 唯一约束:表中某一列不能出现重复的值
- 基本格式
字段名 字段类型 unique
3.5 非空约束
- 非空约束:某一列不能为null
- 基本格式
字段名 字段类型 not null
- 默认值
字段名 字段类型 default 默认值
- 若一个字段设置了非空与唯一约束,该字段与主键的区别是什么?
- 一张表只能有一个主键
- 不能出现多个主键;
- 主键可以是单列也可以是多列
- 自增长只能用在主键上
- 一张表只能有一个主键
3.6 外键约束
-
什么是外键约束
- 外键:从表中,与主表主键对应的那一列
- 主表:一方,用来约束别人的表
- 从表:多方,被别人约束的表
-
创建约束
-- 建表时增加外键
[constraint][外键约束名称] foreign key(外键字段名) references 主表名(主键字段名)
-- 已有表增加外键
alter table 从表 add [constraint][外键约束名称] foreign key(外键字段名) references 主表(主键字段名)
- 删除外键
alter table 从表 drop foreign key 外键名称;
-
外键的级联
- 级联操作:在修改和删除主表的主键时,同时更新或删除副表的外键值
- 语法:更新或删除主表中的主键,从表中的外键列也自动同步更新
级联操作语法 描述 on update cascade 级联更新,只能是创建表的时候创建级联关系 on delete cascade 级联删除
alter table 从表 add [constraint][外键约束名称] foreign key(外键字段名) references 主表(主键字段名) on update cascade on delete cascade
3.7 小结
约束名 | 关键字 | 说明 |
---|---|---|
主键 | primary key | 唯一、非空 |
默认 | default | 若一列没有值,则使用默认值 |
非空 | not null | 该列必须有值 |
唯一 | unique | 该列不能有重复值 |
外键 | foreign key | 主表中主键列,在从表中外键列 |
4. 表与表之间的关系
4.1 一对多
- 建表原则:在从表创建一个字段,字段作为外键指向主表的主键
4.2 多对多
- 建表原则:创建一张中间表,中间表至少包含两个字段。这两个字段分别作为外键指向各自一方的主键
4.3 一对一
- 建表原则
一对一建表原则 | 说明 |
---|---|
外键唯一 | 主表的主键和从表的外键(唯一),形成主外键关系;外键唯一unique |
外键是主键 | 主表的主键和从表的主键,形成主外键关系 |
4.4 小结
表与表的关系 | 关系的维护 |
---|---|
一对多 | 通过主外键约束 |
多对多 | 通过中间表,中间表与两个表是多对一 |
一对一 | 特殊的一对多,多方加唯一约束;从表的主键又是外键 |
5. 表连接查询
5.1 内连接
5.1.1 隐式内连接
- 条件使用where指定
select 字段名 from 表1,表2 where 条件
5.1.2 显示内连接
- 使用 inner join … on 语句,可省略 inner
select 字段名 from 表1 [inner] join 表2 on 条件 [where 过滤条件] -- where添加过滤条件
5.2 外连接
5.2.1 左外连接
-
可以理解为:在内连接的基础上保证左表的数据全部显示
-
使用 left outer join … on ,可省略 outer
select 字段名 from 左表 left [outer] join 右表 on 条件
5.2.2 右外连接
- 可以理解为:在内连接的基础上保证右表的数据全部显示
6.子查询
6.1 概念
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套,内部的查询称为子查询
- 子查询要使用括号
6.2 结果的三种情况
- 单行单列
- 多行单列
- 多行多列
6.3 结果是一个值
-
结果是单行单列
-
子查询结果在where后面作为条件
-
父查询使用比较运算符
select 字段名 from 表 where 字段=(子查询);
-
-
结果是多行单列
-
子查询结果类似一个数组
-
父查询使用运算符 in
select 字段名 from 表 where 字段 in (子查询);
-
6.4 结果是多行多列
- 子查询结果在from后面作为一张表再次查询
select 字段名 from (子查询) 表别名 where 条件;
6.5 小结
- 子查询结果是单列,则在 where 后面作为条件
- 子查询结果是多列,则在 from 后面作为表进行二次查询
7. 事务
7.1 手动提交
功能 | SQL语句 |
---|---|
开启事务 | start transaction; |
提交事务 | commit; |
回滚事务 | rollback; |
7.2 自动提交
-
MySQL 默认开始自动提交事务
-
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕自动提交事务
-
取消自动提交
-- 查看MySQL是否开启自动提交事务 select @@autocommit; -- @@表示全局变量;1表示开启,0表示关闭 -- 取消自动提交事务 set @@autocommit = 0;
7.3 回滚点
-
设置回滚点可以让我们在失败的时候回到回滚点,而不是回到事务开启的时候
-
操作语句
回滚点操作语句 语句 设置回滚点 savepoint 名字 回到回滚点 rollback to 名字
7.4 事务的隔离级别
-
事务的四大特性
-
原子性、一致性、隔离性、持久性
-
可能引发的并发访问问题
并发访问的问题 含义 脏读 一个事务读取到了另一个事务中尚未提交的数据 不可重复读 一个事务中两次读取的数据内容不一致;要求的是一个事务中多次读取时数据是一致的,这是事务update时引发的问题 幻读 一个事务中两次读取的数据的数量不一致;要求在一个事务多次读取的数据的数量是一致的,这是insert或delete时引发的问题 -
四种隔离级别
- 上面的级别最低,下面的级别最高
- 隔离级别越高,性能越差,安全性越高
级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别 1 读未提交 read uncommitted 是 是 是 2 读已提交 read committed 否 是 是 Oracle和SQL Server 3 可重复读 repeatable read 否 否 是 MySQL 4 串行化 serializable 否 否 否 -
相关命令
-- 查询全局事务隔离级别
select @@tx_isolation;
-- 设置隔离级别[需要退出 MySQL 再重新登录才能看到隔离级别的变化]
set global transaction isolation level 级别字符串;
8. DOS命令窗口操作数据乱码问题
- 因为系统cmd窗口的编码是GBK,而mysql的是utf-8,故有时会出现乱码问题;
- 查看MySQL内部设置的编码
show variables like 'character%'; -- 查看包含character开头的全局变量
-
解决方案:修改client、connection、results的编码为GBK,保证和DOS命令行编码保持一致
- 单独设置
单独设置 说明 set character_set_client=gbk; 修改客户端的字符集为GBK set character_set_connection=gbk; 修改连接的字符集为GBK set character_set_results=gbk; 修改查询的结果字符集为GBK -
同时设置三项
set names gbk;
| 否 | |
-
相关命令
-- 查询全局事务隔离级别
select @@tx_isolation;
-- 设置隔离级别[需要退出 MySQL 再重新登录才能看到隔离级别的变化]
set global transaction isolation level 级别字符串;
8. DOS命令窗口操作数据乱码问题
- 因为系统cmd窗口的编码是GBK,而mysql的是utf-8,故有时会出现乱码问题;
- 查看MySQL内部设置的编码
show variables like 'character%'; -- 查看包含character开头的全局变量
-
解决方案:修改client、connection、results的编码为GBK,保证和DOS命令行编码保持一致
- 单独设置
单独设置 说明 set character_set_client=gbk; 修改客户端的字符集为GBK set character_set_connection=gbk; 修改连接的字符集为GBK set character_set_results=gbk; 修改查询的结果字符集为GBK -
同时设置三项
set names gbk;
-
退出DOS命令行就失效了,需要每次都配置