SQL基础练习：http://sample.jimstone.com.cn/xsql/
- 数据库分类
- 其他 Other
- DDL 数据定义
- DQL 数据查询
- DML 数据操纵
- DTL 事务控制
- DCL 数据控制
- 查看、使用、查询数据库
- 创建、删除、修改数据库
- 查看、创建、复制、删除、修改表
- 添加、删除、修改字段
- change 和 modify 的区别
- 6.1 主键（Primary Key，PK）
- 6.2 外键（Foreign Key，FK）
- 6.3 唯一性（Unique）
- 6.4 默认值（Default）
- 6.5 非空（Not Null）
- 6.6 主键自增长（auto_increment）
- 联合查询 union
- 分组 group by
- 过滤 having
- 排序 order by
- 分页查询 limit
- 求和 sum()
- 平均数 avg()
- 最大值 max()
- 最小值 min()
- 统计 count()
- 去重 distinct()
- 系统日期 curdate()
- 系统日期时间 sysdate()
- 10.1 数据类型
- 数值型、字符型、日期时间型、混合型
- 10.2 查询数据
- 一般查询 : 单表查询 , 多表查询
- 内连接 inner join
- 外连接 : 左连接 left join , 右连接 right join
- 子查询 : 嵌套 , all , any
- 10.3 插入数据
- 10.4 更新数据
- 10.5 删除数据
- 连接本地数据库
- 导入导出本地数据
- 连接远程数据库
- 导出远程数据
1. Mysql 基础
- 数据库分类：
（1）关系型数据库：Mysql、Oracle、SQLServer、DB2
（2）非关系型数据库：MongoDB、redis
- DBA：数据库管理员
- DBS：数据库系统（包含）
（1）DB：数据库
（2）DBMS：数据库管理系统
- sql 语句以 `;` 结尾
- as：一般为别名，可省略
- 除数值外，其他数据类型需加单引号或双引号
- 查询时，`%` 代表模糊查询，`_` 代表占位
2. SQL 语言
SQL 语言分类：
DDL 数据定义、DQL 数据查询、DML 数据操纵、DTL 事务控制、DCL 数据控制
- DDL 数据定义（Data Definition Language）
create、alter、drop、truncate
create：创建数据库、表（字段、约束）
alter：修改数据库、表（字段、约束）
drop：删除数据库、表（字段、约束）
truncate：清空表数据
- DQL 数据查询（Data Query Language）
select
select：查询表数据
- DML 数据操纵（Data Manipulation Language）
insert、update、delete
insert：插入表数据
update：修改表数据
delete：删除表数据
- DTL 事务控制
commit、rollback
commit：提交事务
rollback：回滚事务
- DCL 数据控制（Data Control Language）
grant、revoke
grant：授权
revoke：撤销授权
3. 数据库 Databease
# 查看数据库
show databases;
# 使用数据库
use 库名;
# 查询数据库
show database like '%库名%';
# 创建数据库
create database 库名 default character set 默认字符集 collate 排序规则;
# default character set utf8 collate utf8_general_ci
# default character set gb2312 collate gb2312_chinese_ci
# 删除数据库
drop database 库名;
# 修改数据库（字符集）
alter database 库名 character set 新字符集 collate 排序规则;
4. 表 Table
# 查看所有表
show tables;
# 查看表结构
desc 表名;
show create table 表名;
# 查看表键值
show keys from 表名;
# 创建表
create table 表名 (字段 类型(长度) 约束,字段 类型(长度) 约束,...);
create table 表名 (字段 类型(长度),...,约束(字段,字段...),约束(字段,字段...));
# 复制表
# 复制表结构
create table 表名1 like 表名2;
# 复制表结构和数据
create table 表名1 as select * from 表名2;
# 删除表
drop table 表名;
# 修改表
# 修改表名
alter table 表名 rename to 新表名;
rename table 表名 to 新表名;
# 修改表字符集
alter table 表名 convert to character set 新字符集;
5. 字段 Column（列名）
# 增加表字段
alter table 表名 add 新字段 类型(长度) 约束;
# 在某字段后插入新字段
alter table 表名 add 新字段 类型(长度) after 字段;
# 删除表字段
alter table 表名 drop 字段;
# 修改表字段
alter table 表名 change 字段 新字段 类型(长度) 约束;
# 修改表字段类型
alter table 表名 modify 字段 新类型(长度);
change 和 modify 的区别
change 和 modify 都可以修改表的定义
change 需要写两次字段名，可以修改字段名称
modify 只需写一次字段名，不可以修改字段名称
6. 约束 Constraint
约束 Constraint :
主键（Primary Key）、外键（Foreign Key）、唯一性（Unique）、默认值（Default）、非空（Not Null）、主键自增长（auto_increment）
6.1 主键（Primary Key，PK）
# 创建表时添加主键
create table 表名(字段 类型(长度) primary key);
# primary key 可搭配：unique、unsigned、zerofill、not null、auto_increment
# 添加主键
alter table 表名 add primary key(字段);
# 删除主键
alter table 表名 drop primary key;
一个表只能有一个主键，分为单个主键和复合主键
复合主键的所有字段相同，才为重复
6.2 外键（Foreign Key，FK）
主表与从表
主表：被引用数据
从表：引用其他表数据
插入：先主表再从表
删除：先从表再主表
前置条件：
1、主表与从表西段类型一致
2、主表与从表的字符集相同
3、主表被调用的列有索引
4、存储引擎为 InnoDB
# 创建表时添加外键
create table 从表 (...,foreign key(字段) references 主表 (引用字段));
# 添加外键，定义约束名
alter table 从表 add constraint 约束名 foreign key(字段) references 主表(字段);
# 添加外键，不定义约束名，约束名自动生成
alter table 从表 add foreign key(字段) references 主表(引用字段)
# 删除外键
# 1、获取约束名 constraint
show create table 表名;
# 2、删除约束
alter table 表名 drop foreign key 约束名;
# 3、删除索引
drop index 约束名 on 表名;
删除外键
1、获取约束名 constraint
2、删除约束
3、删除索引
如图，CONSTRAINT 后面为约束名，即 COUNTR_REG_FK
6.3 唯一性（Unique）
# 添加唯一性
alter table 表名 add unique(字段);
# 删除唯一性
alter table 表名 drop index 字段;
6.4 默认值（Default）
# 添加默认值
alter table 表名 alter 字段 set default 值;
# 删除默认值
alter table 表名 alter 字段 drop default;
6.5 非空（Not Null）
# 使用 change 和 modify 都可以
# 添加非空
alter table 表名 modify 字段 类型(长度) not null;
# 删除非空
alter table 表名 modify 字段 类型(长度) null;
6.6 主键自增长（auto_increment）
# 只有主键 PK 可使用
# 使用 change 和 modify 都可以
# 添加主键自增长
alter table 表名 change 字段 新字段 类型(长度) auto_increment;
# 删除主键自增长
alter table 表名 change 字段 新字段 类型(长度) null;
7. 操作符 Operator
操作符 Operator :
算数运算符、比较运算符、逻辑运算符、位运算符
7.1 算数运算符
+ , - , * , / , %
7.2 比较运算符
> , < , >= , <= , = ,<=>
!= , <> : 不等于
is null / is not null : 是否为空
like / not like : 相似 , % 模糊搜索 , _下划线占位符(1 位)
# '_2%' : 第二位为2的模糊搜索
in / not in : 取值集合
between and : 取值范围
7.3 逻辑运算符
or : 或
and : 与
not : 非
xor : 异或
# 查询优先级 : () > not > and > or
7.4 位运算符
& , | , ^ , ~ , >> , <<
8. 关键字 Keyword
关键字 Keyword :
union（联合查询）、group by（分组）、having（过滤）、order by（排序）、limit（分页查询）
1、联合查询 : union
合并重复数据 : SQL1 union SQL2
不合并重复数据 : SQL1 union all SQL2
2、分组 : group by , 条件 : 不同xx
3、过滤 : having 条件(含函数)
分组 + 过滤 , 过滤在排序前
与 where 区别 : where 不用于分组后 , 条件不含函数
4、排序 : order by asc / desc (升降序)
5、分页查询 : limit , 常用于 : 排序 + 分页查询
limit 数字 : 显示前几行 , 相当于 limit 0,数字
limit 3,1 : 取第三行后的1行数据
6、执行顺序 :
select - from - where - group by - having - order by - limit
9. 常用函数 function
常用函数 :
sum()、avg()、max()、min()、count()、distinct()、curdate()、sysdate()
1、求和 : sum()
2、平均值 : avg()
3、最大值 : max()
4、最小值 : min()
5、统计 : count()
6、去重 : distinct() , 与 count() 配合使用，统计字段类型
7、系统日期 : curdate() , 返回系统时间 select curdate();
8、系统日期时间 : sysdate() , 返回系统日期时间 select sysdate();
10. 数据 data
10.1 数据类型
数据类型 :
数值型、字符型、日期时间型、混合型
- 数值型
整型 : int , integer , tinyint , smallint , mediumint , bigint
无符号 - 0 与正整数 : unsigned
零填充 : zerofill
搭配使用 : int(10) unsigned zerofill
浮点型 : float , double
(数值总长度 , 小数长度) , 如 float(7,2)
- 字符型
定长 : char , 定长10字节，不足补空格
变长 : varchar (推荐) , 最长10字节，以输入长度为准
文本 : tinytext , text , mediumtext , longtext
二进制 : tinyblob , blob , mediumblob(图片) , longblob(音频视频)
0-M字节二进制 : binary(M) , varbinary(M)
- 日期时间型
year : YYYY
date : YYYY-MM-DD
time : HH:MM:SS
datetime : YYYY-MM-DD HH:MM:SS
timestamp(时间戳) : YYYYMMDD HHMMSS
输入 NULL , 则转为当前系统日期时间
- 混合型
enum(枚举类型) : 单选
set(集合类型) : 多选
10.2 查询数据
查询数据 :
1、一般查询 : 单表查询 , 多表查询
2、内连接 inner join - on
3、外连接 : 左连接 left join - on , 右连接 right join - on
4、子查询 : 嵌套 , all , any
- 一般查询（单表查询、多表查询）
# 查询语句执行顺序
select - from - where - group by - having - order by - limit
# 单表查询
# 查询所有数据
select * from 表名;
# 查询某字段
select 字段 from 表名;
select 字段 from 表名 where 条件;
# 复杂查询 : where + group by 分组 + having + order by 排序 + limit 分页查询
select 字段 from 表名 where 条件 group by 字段 having 条件 order by asc/desc limit 数字;
多表查询
步骤：
1、了解查询的数据和条件分布在哪些表中
2、多表间是否有联系条件 : 直接联系（相同字段） , 间接联系（共同引用字段）
3、结合其他条件
# 多表查询语句
select 别名.字段 - from 表1 as 别名1 , 表2 as 别名2 ,... - where 联系条件 and 其他条件
# 联系条件 : 别名1.字段 = 别名2.字段
- 内连接查询（inner join）
内连接 : inner join 表 - on 条件 , 返回符合条件的数据
inner 可省略
每个 inner join 后只能添加一张表 , 多个表使用多个 inner join
# 内连接查询语句
select 别名.字段 - from 表1 as 别名1 - inner join 表名2 as 别名2 - on 联系条件 and 其他条件 - ...
- 外连接查询（左连接 left join、右连接 right join）
外连接包括 : 左连接、右连接
外连接与内连接区别
外连接 : 返回符合条件的数据 , 和不符合条件的数据为 null
使用条件 : 某条件含不同情况
# 左连接 left join
# 以第一个表为左表 , 左表所有值都列出，返回符合条件的右表数据 ， 不符合条件则值为 null
select 别名.字段 - from 表1 as 别名1 - left join 表名2 as 别名2 - on 联系条件 and 其他条件 - ...
# 右连接 right join
# 以最后一个右表为主 , 右表所有值都列出，返回符合条件的左表数据 ， 不符合条件则值为 null
select 别名.字段 - from 表1 as 别名1 - right join 表名2 as 别名2 - on 联系条件 and 其他条件 - ...
- 子查询（嵌套，all，any）
# 外部查询与子查询之间有相同字段或为子集
select - from (子查询) as 别名 - where - 条件
# 多种写法
select - from (select - from - where) as 别名 - where
select - from - group by - having - (select - from - where)
select - from - where - in (select - from - where)
#
# all 用法
# 都不等于
select - from - where - <>all(子查询)
# 大于最大（大于所有）
select - from - where - >all(子查询)
# 小于最小（小于所有）
select - from - where - <all(子查询)
# any 用法
# 任意一个相等
select - from - where - =any(子查询)
# 大于最小（大于任意）
select - from - where - >any(子查询)
# 小于最大（小于任意）
select - from - where - <any(子查询)
10.3 插入数据
# 插入数据
insert into 表名 (字段) values (值);
# 数据与字段个数一致，省去字段
insert into 表名 values (值);
insert into student (sid,sname,gender) values ('001','张三','男');
insert into student values ('001','张三','男');
# 插入其他表数据
insert into 表1 select 字段 from 表2;
insert into student select sid,sname,gender from stu;
10.4 更新数据
# 更新数据
update 表名 set 修改字段 where 条件;
update student set gender='女' where sid='001';
10.5 删除数据
# 删除数据
delete from 表名 where 条件;
delete from student where sname='张三';
# 清空表数据
# 可恢复
delete from 表名;
# 不可恢复，删除速度快
truncate 表名;
11. 用户管理及授权 User
11.1 用户类型
管理员 : root 具有最高权限
非管理员 : 由 root 创建和分配权限
11.2 主机IP类型
主机类型 : % , localhost , IP地址
% : 任何机器可访问
localhost : 只能在本地访问
IP地址 : 在指定 IP 的机器上可访问
11.3 权限类型（9种）
权限类型 (9) :
create . drop , alter , insert , select , update , delete , all privileges , usage
create : 创建库、表、视图、存储过程
drop : 删除库、表、字段、约束、视图、存储过程
alter : 修改库、表、字段、约束
insert : 插入数据
select : 查询数据
update : 修改数据
delete : 删除数据
all privileges : 所有权限
usage : 没有权限，只能连接数据库
11.4 创建用户及授权
创建用户及授权
1、创建用户
2、用户授权
3、刷新授权表
# 1、创建用户
# password() 加密函数
insert into mysql.user (host,user,password) values ('主机IP','用户名',password('密码'));
# 2、用户授权
# grant - on - to - indentified by
# *.* : 所有库，每个库的所有表
# 主机IP : % , localhost , IP地址
grant 权限 on 库名.表名 to '用户名'@'主机IP' identified by '密码';
# 3、刷新授权表
flush privileges;
11.5 查看用户权限
# 查看所有用户
select user,host from mysql.user;
# 查看用户权限
# 主机IP : % , localhost , IP地址
show grants for '用户名'@'主机IP';
# 查看当前用户权限
show grants;
11.6 回收用户权限
回收用户权限
1、查看用户权限
2、回收用户权限
3、刷新权限表
# 1、查看用户权限
show grants for '用户名'@'主机IP';
# 2、回收用户权限
# revoke - on - from '
revoke 权限 on 库名.表名 from '用户名'@'主机IP';
# 3、刷新权限表
flush privileges;
11.7 删除用户
# 删除用户
drop user '用户名'@'主机IP';
# 删除任意主机(%)的远程用户
drop user '用户名';
11.8 修改密码
# 修改密码
update mysql.user set password=password('新密码') where user='用户名' and host='主机IP';
# 刷新授权表
flush privileges;
12. 事务 Transaction
12.1 前提
数据库引用了 Innodb (mysql 默认)、NDB、SEQUENCE 引擎
12.2 定义
一个事务对应一个完整的业务
事务是一个不可分割的最小工作单元
事务由单独单元的一个或多个 SQL 语句组成
12.3 作用
事务作用
1、维护数据库的完整性 , 保障多条 SQL 语句 , 同时处理成功 , 或同时处理失败
2、如果某条 SQL 语句执行失败 , 整个事务将会回滚 , 取消操作 , 如银行转账
3、事务管理 DML 数据操纵语句 : insert、update、delete
12.4 特性
事务特性：
原子性、一致性、隔离性、持久性
1、原子性 :
事务是一个不可分割的最小工作单元 (全部成功、全部失败)
2、一致性 :
事务前后数据的完整性必须保持一致 (受 DML 语句影响、或不受影响)
3、隔离性 :
防止多个事务并发执行时 , 交叉执行导致数据不一致
级别由低到高，并发性能由高到低 :
> 未提交 : 一个事务可以读到另一个事务未提交的结果
- 脏读 :
- 1、事务a修改数据 , 但未提交
- 2、事务b读到事务a未提交的更新结果
- 3、事务a提交失败 , 事务b读到的是脏数据
> 读提交 : 一个事务提交后 ,其更新结果才能被其他事务看到，解决脏读问题
- 不可重复读 : 在同一个事务中 , 对于同一份数据读取到的结果不一致
事务b在事务a提交前读到的数据 , 与事务a提交后读到的数据可能不同
> 可重复读 :
在一个事务中 , 对同一份数据的读取结果是相同的，无论其他事务是否对该数据进行操作 ,
以及这个事务是否提交 , 解决脏读、不可重复读问题 , 不能解决幻读问题
- 幻读 : 在同一事务中 , 同一个查询多次返回的结果不一致 , 如新增记录
> 串行化 :
事务串行化执行 , 解决并发所有问题
隔离级别最高 , 牺牲系统并发性
4、持久性 :
一旦事务提交 , 则其所做的修改就会永久保存到数据库中 (即使系统崩溃)
12.5 术语
事务术语 :
开始、回滚、设置保存点、回滚到保存点、提交事务
# 开始事务
begin;
或 start transaction;
# 回滚事务
rollback;
# 设置保存点
savepoint 保存点名;
# 回滚到保存点
rollback to 保存点名;
# 提交事务\
commit;
12.6 步骤
事务步骤
1、查看事务提交模式
# 默认为 1 自动提交 , 0 手动提交
select @@autocommit;
2、设置事务提交模式
# 关闭自动提交
set autocommit=0;
3、开始事务
begin;
或 start transaction;
4、设置保存点 a
savepoint a;
5、DML 语句 : insert , update , delete
6、回滚 : rollback;
或回滚到保存点 a : rollback to a;
7、提交事务
commit;
# 提交事务后 , 保存点消失 , 回滚失效
13. 远程连接 Remote Connect
- 连接本地数据库
mysql -u 用户名 -p
# 输入密码
- 导入导出本地数据
# cmd 切换到在 mysql 安装目录下 ： cd /mysql/bin
1、导入本地数据
mysqldump -u 用户名 -p 库 < 文件路径
# 输入密码
mysqldump -u root -p test < D:\test\sql
2、导出本地数据
mysqldump -u 用户名 -p 库 > 文件路径
# 输入密码
mysqldump -u root -p test > D:\test\sql
- 连接远程数据库
-- 服务器 mysql
1、在服务器创建指定 IP 地址的新用户
insert into mysql.user(host,user,password) values ('ip地址','用户名',password('密码'));
2、新用户授权
grant 权限 on 库.表 to '用户名'@'IP地址' identified by '密码';
权限类型 (9) : create . drop , alter , insert , select , update , delete , all privileges , usage
3、刷新授权表
flush privileges;
-- 客户端（IP地址）
连接远程数据库
mysql -h ip -u 用户名 -p
# 输入密码
mysql -h 172.30.67.98 -u abc -p
- 导出远程数据
mysqldump -h ip -u 用户名 -p 库名 > 文件路径
mysqldump -h 172.30.67.98 -u abc -p test > D:\test.sql
扫一扫
3511
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
