测试需要掌握的SQL知识
2021-11-02 17:24:03 1 举报AI智能生成
MySQL需要掌握的SQL知识;
作者其他创作
大纲/内容
数据库概念
数据库（database）：存储数据的仓库
数据库管理系统
（Database Management System=DBMS）
（Database Management System=DBMS）
数据库定义功能
数据操作功能
数据控制功能
数据查询功能
数据库的运行和管理
数据库建立和维护功能
常用数据库管理系统：MySQL、DB2，Oracle，SQLserver，Acess
数据库的类型
关系型数据库
由行和列组成的一个二维表
关系概念模型
实体：表示现实事物。如：学生，课程
属性：实体的特性。比如：学生的姓名，课程的编号
关系：
一对一
一对多
多对多
概念模型表示
ER图
矩形：实体
椭圆形：属性
菱形：关系
使用无向线连接
支持结构化语言
（Structure Query Language）SQL
（Structure Query Language）SQL
DDL（数据定义语言）
create：创建数据库对象
alter：修改数据库对象
drop ：删除数据库对象
rename ：重命名
truncate ：删除表数据保留表结构
DML（数据库操纵语言）
insert：向表中插入数据
update：更新表中的数据
delete：删除表中的数据
DQL（数据查询语言）
select ： 查询数据
DCL（数据控制语言）
grant：授予权限
grant <权限操作> on <表名> to <用户>
权限操作：select，update，insert，delete，all privilege（全部权限）
revoke：移除权限
revoke <权限操作> on <数据库对象> from <数据库或者表名称>
非关系型数据库
存储数据：以对象的形式存储数据。如：键值形式，文档形式，图形等
常用：redis，MongoDB，Memcached 等
数据库安装/卸载(了解)
基本命令
查看命令show
查看所有数据库：show databases;
查看数据库下的所有表：show databases;
查看字符集：show character set;
切换数据库命令
use 数据库名称；
查看表结构
desc 表名称；
创建数据库
create database <数据库名称>
character set 字符集名称；
character set 字符集名称；
charset=utf8
创建表
create table <表名称>（
字段名称1 数据类型 [约束名称]，
字段名称2 数据类型 [约束名称]
）ENGINE=INNODB CHARSET=utf8;
字段名称1 数据类型 [约束名称]，
字段名称2 数据类型 [约束名称]
）ENGINE=INNODB CHARSET=utf8;
数据类型（常用）
数值
int：整型
tinyint：小整型
float：单精度浮点型
字符类型
char(n)：定长字符串
varchar(n)：变长字符串
tinytext(n)：短文本字符串
n：代表设置的字符串长度
日期类型
date：格式YYYY-MM-DD
time：格式HH:MM:SS
datetime：YYYY-MM-DD HH:MM:SS
插入数据
insert into 表名称(字段1，字段2) values(值1，值2）
插入多条数据：insert into 表名称(字段1，字段2) values(值1，值2),(值3，值4)
更改数据
update 表名称 set 字段名=新的值 where 条件
删除
删除数据库：drop database 数据库名称；
删除表：drop table 表名称；
删除某一条数据
delete from 表名称 where 条件;
清空表
truncate 表名称；
delete from 表名称；
delete ，truncate，drop区别
truncate：清空表数据，保留表结构并且重置主键序号
delete：删除表中的数据，不重置主键索引值，可以删除某一条数据
drop：删除表结构及表数据，可以删除其他数据库对象
更改表结构（alter table）
增加字段：alter table 表名称 add 字段名 数据类型；
修改字段或数据类型：alter table 表名称 change 旧字段名 新字段名 数据类型；
修改数据类型：alter table 表名称 modify 字段名 数据类型；
删除字段：alter table 表名称 drop 字段名；
重命名：alter table 表名称 rename as 新的表名称；
完整性约束
主键(primary key)
标识字段的唯一性，具备非空和唯一约束
特性
表中只能有一个主键
可以将多个字段设置为主键
创建约束
create table 表名称（
字段名 数据类型 primary key,
字段名2 数据类型，
字段名3 数据类型
）
字段名 数据类型 primary key,
字段名2 数据类型，
字段名3 数据类型
）
create table 表名称（
字段名称1 数据类型，
字段名称2 数据类型，
字段名称3 数据类型，
字段名称4 数据类型，
primary key(字段名称1，字段名称2)
）
字段名称1 数据类型，
字段名称2 数据类型，
字段名称3 数据类型，
字段名称4 数据类型，
primary key(字段名称1，字段名称2)
）
表创建完成后，添加主键
alter table 表名称 add constraint primary key(字段名)
删除主键约束
alter table 表名称 drop primary key
非空（not null）
将一个字段设置为非空约束后，插入的数据不能为null
语法
create table 表名（
字段名 数据类型 not null,
字段名 数据类型
）
字段名 数据类型 not null,
字段名 数据类型
）
外键（foreign key）
当一个表的字段，引用了其他表的主键时，我们称之为这个字段是该表的外键
被引用的表称为主表，引用字段所在的表称之为从表
语法
创建表时，添加外键约束
create table 表名（
字段名 数据类型,
字段名 数据类型，
foreign key(字段名) references 主表（主键字段名）
）
create table 表名（
字段名 数据类型,
字段名 数据类型，
foreign key(字段名) references 主表（主键字段名）
）
表已经创建好后，添加外键
alter table 从表名 add constraint [约束名] foreign key(从表字段) references 主表（主键字段名）
alter table 从表名 add constraint [约束名] foreign key(从表字段) references 主表（主键字段名）
唯一（unique）
表示该字段中不允许出现重复的数据
语法
create table 表名（
字段名 数据类型 unique,
字段名 数据类型
）
字段名 数据类型 unique,
字段名 数据类型
）
默认值（default）
表示当插入数据时，该字段没有插入任何内容，此时使用默认值
语法
create table 表名（
字段名 数据类型 default ‘默认值’,
字段名 数据类型
）
字段名 数据类型 default ‘默认值’,
字段名 数据类型
）
自增长（auto_increment）
一般与主键一起使用
起到让序号自动增加的作用
检查（check）
mysql中不支持该约束
单表查询
查询所有：select * from 表名称
按条件查询
select * from 表名称 where 条件
条件表达式
比较运算符：> < >= <= <> !=
逻辑运算符：and , or , not
在……之间
between ……and ……
包含两边的数值
例如：
--查询工资大于1000，并且年龄小于20
select * from 表名称 where 工资 > 1000 and 年龄<20
select * from 表名称 where 工资 > 1000 and 年龄<20
-- 查询工资在2000到3000之间的员工信息
select * from 表名称 where 工资 between 2000 and 3000
select * from 表名称 where 工资 between 2000 and 3000
起别名（as）
语法：select 字段名 as 别名，字段名2 as 别名2 from 表名称
as 可以省略，可以给字段起别名，也可以给表起别名
as 的作用：让字段的标题更有实际意义，明了。
模糊查询（like）
语法：select * from 表 where 字段名 like ‘关键字%’
%：代表匹配多个字符个数
_：代表匹配一个字符
例如
-- 查询以姓“王”的所有学生信息
select * from 表名称 where 字段名 like '王%'
select * from 表名称 where 字段名 like '王%'
-- 查询以“成”值结尾的但是两个字的学生信息
select * from 表名称 where 字段名 like '_成'
select * from 表名称 where 字段名 like '_成'
分页（limit）
语法：select * from 表名称 limit m，n;
从m+1行开始，查询n条数据
从m+1行开始，查询n条数据
m：可以省略，代表从第一行开始
例如
-- 查询表中的第一行数据
select * from 表名称 limit 1
select * from 表名称 limit 1
-- 查询表中，从第5行开始，查询3条数据
select * from 表名称 limit 4,3;
select * from 表名称 limit 4,3;
分组（group by）
一般与聚合函数一起使用
语法：select 聚合函数 from 表名称 group by 字段名 having 条件
having 不能单独使用，必须与group by 一起使用
聚合函数
统计个数：count()
求和：sum()
求平均：avg()
求最大：max()
求最小：min()
例如
-- 统计每个班级人数是多少？
select class，count(*) 人数 from 表名称 group by class
select class，count(*) 人数 from 表名称 group by class
-- 统计每个班级人数超过25人的记录
select class, count(*) 人数 from 表名称 group by class having 人数 >25
select class, count(*) 人数 from 表名称 group by class having 人数 >25
排序（order by）
语法：select * from 表名称 order by 字段名 desc
desc ： 倒序，从大到小
asc : 正序，从小到大
例如
-- 按成绩从高到低排序
select * from 表名称 order by 成绩 desc
select * from 表名称 order by 成绩 desc
其他内置函数
日期函数
now()
current_time()
current_date()
month()
year()
day()
week()
计算时间差值
timestampdiff(unit，starttime，endtime)
unit(单位)：Year,month,day，hour，minute，week,second 等
数值函数
ceil(num)：向上取整
floor(num) : 向下取整
round(num) ： 四舍五入
format(para,n) ： 保留n位小数
字符函数
length(para)：求字符串长度
concat()：连接字符串
多表查询
连接查询
关联查询
语法：select * from 表A ，表B where 表A.id = 表B.id
表A的id 与 表B 的 id 表示的是同一个现实事物的属性
返回结果：既在表A中，又在表B中的数据
内连接(inner join)
语法：select * from 表A inner join 表B on 表A.id=表B.id
表A的id 与 表B 的 id 表示的是同一个现实事物的属性
返回结果：既在表A中，又在表B中的数据
外连接
左外连接（left join）
语法：select * from 表A left join 表B on 表A.id=表B.id
返回左表（表A）中的所有数据，对应右表中不存在的数据以null的形式返回
右外连接（right join）
语法：select * from 表A right join 表B on 表A.id=表B.id
返回右表（表B）中的所有数据，对应左表中不存在的数据以null的形式返回
子查询
单行子查询
使用运算符：> < >= <= != <>
子查询语句中，返回一行记录结果
语法：select * from 表名称 where 字段名 > (select 字段名 from 表名称)
多行子查询
in , not in
子查询中返回多行的查询结果
语法：select * from 表名称 where 字段名称 in (select 字段名称 from 表名称)
exists
语法：select * from 表名 where exists（select 字段名 from 表名）
exists 后面语句查询有结果则返回true；查询无结果则返回false
any | some | all
any | some
语法：select * from 表名 where 字段名 运算符 any (select 字段名 from 表)
含义：字段名 满足 any子查询里任意一条件即可 （or）
all
select * from 表名 where 字段名 运算符 all (select 字段名 from 表)
含义： 字段名 满意 all 子查询中所有的条件，（and）
纵向连接（union）
要求：表1的字段个数要与表2的字段个数一致，字段含义以第一个SQL为准
union 与 union all
union 会去除重复数据
union all 显示所有数据，包括重复的数据
语法：select 字段1，字段2，字段3 from 表1 union select 字段1，字段2，字段3 form 表2
0 条评论
下一页
