mysql之测试工程师必会基础知识

• 基础部分

　　　　1、创建表table指令

create table 表名(
-- 字段的定义
id int unsigned primary key auto_increment comment 'id主键',
name varchar(50) not null default "" comment "姓名",
)engine=INNODB default charset=utf8 comment "表备注";   --engine=INNODB常见引擎

　　　　2、常见的字段类型

整形：int（-2^31即-2147483648到2^31-1 2147483647）、tinyint(2^-1微整形,0-255的整型数据)，bigint(从-2^63即-9223372036854775808到2^63-1即9223372036854775807)的整型数据
浮点数：float(最大位数，小数位数) double(最大位数，小数位数)，decimal(最大位数，小数位数)用于钱; 最大位数包含小数位数，整数位数=最大位数-小数位数
字符串:char(M),varchar(M)可变长度，最大长度为M
文本类型：text,longtext
日期类型：datetime:0000-9999年，timetamp 最大2038年

　　　　3、常见的字段属性

primary key： 主键

auto_increment： 自动增长
default ：默认值
null 和not null 是否为空
comment ：备注
unique key：设置唯一键

　　　　4、记录操作：
插入：

insert into 表名(字段列表) VALUES(值列表),(值例表),(值例表) --如果值列表与表里字段完全匹配时，字段列表可不写；自增ID不用写

删除：

delete from 表名 where 条件表达式;

修改：

update 表名 set 字段1=值,字段2=值 where 条件表达式;

添加字段：

alter table 表名 add column age tinyint default 0 after 已有列后;

查询：

select 字段列表 from 表名 where 条件表达式;

多表查询：根据连接条件将多个表合成一个新的数据源（笛卡尔积），再对新数据源进行过滤

select 字段列表
from 表1
join 表2 on 连接条件 WHERE 条件诗句;
例如：
SELECT column_name(s)
FROM table1
INNER JOIN table2 
ON table1.column_name=table2.column_name

-- 重点select查询的完整语法，共8部分，每部分要么不出现，若出现必须按顺序来

SELECT [选项 distinct|all]
字段表达式
from 子句
where 子句
GROUP BY 子句
having 子句
ORDER BY 子句
LIMIT 子句;　　　

　　　　5、字段表达式：
别名：

select name as alias_name,cliassid from stu;

查询常量

select CURDATE(),6 from DUAL;

统计函数：
sum(字段)求和,
min(字段)最小,
max(字段)最大,
avg(字段)平均、
count(字段)求记录条数

　　　　6、where
6.1、 between ... and ... 后者 not between ... and ... 适用于数字和日期的范围判断
案例：

SELECT * from stu where age between 20 and 30;

6.2、is null 和 is not null 判断字段值是否为空

SELECT * from stu where name is not null;

6.3、条件之间的逻辑运算
与：and
或：or
非：not

SELECT * from stu where sex='f' or sex='F';
SELECT * from stu where age != 1;
SELECT * from stu where age <> 1;

　　　　7、GROUP BY 对列分组统计，只能查询 统计函数的结果 及 分组的字段
统计函数：count(),sum(),min(),max(),avg(),group_concat(字段) ：将组内字段拼接成一个字符串

SELECT
count(id) AS 班级人数,
avg(age),
classid
FROM stu
WHERE classid IS NOT NULL
GROUP BY classid;

　　　　8、having 子句：对分组后的结果进行再过滤

SELECT
count(id) AS 班级人数,
avg(age) as avg,
classid
FROM stu
WHERE classid IS NOT NULL
GROUP BY classid
having avg >20;

　　　　9、order by :用于对查询数据进行排序 asc 默认升序；desc 降序

SELECT * FROM stu WHERE classid IS NOT NULL ORDER BY classid DESC,age ASC;

　　　　10、limit 语句：用于限制查询结果的条数，为减轻服务器的查询压力，提高用于体验度
语法： limit start,length

--假如每面2条
第一页 ：select * from stu limit 0,2;
第二页 : select * from stu limit 2,2;
第N页：select * from stu limit （页码-1）*2,2;

　　　　11、union 联合查询：把多个select 查询结果集合到一起。
语法：

select 语句1
union
select 语句2
UNION
select 语句3;

备注：
a、联合查询 多个select查询的列数量要一致
b、联合查询 以第一个查询为表头
c、联合查询 和 数据类型没有任何关系，不同的字段类型都能联合

联合查询的高级了解
union [all | distinct] 不去重和去重,默认去重

select * from stu1 where age>20
union
select * from stu2 where age>20;

　　　　12、子查询（重点）
12.1、where 子查询，子查询语句出现在where子句中
例如：查询学生信息，要求学生年龄大于全班平均年龄

select * from stu where age > (select avg(age) as avg from stu);

12.2、from子查询，对子查询取一个别名，相当于虚拟一个表
例如：查询班级id大于1的学生，再对该结果 按照年龄降序排列，通过from子查询的形式实现

SELECT * from (SELECT * from stu where classid > 1) as t order by age desc;

12.3、列子查询：子结果的结果为一列多行，当返回多行时，使用 in 和 not in

SELECT * from class where classid in (select DISTINCT classid from stu where classid is not null);

12.4、行子查询：子结果的结果为一行多列，()为行构建符

SELECT * from stu where (age,classid) = (select max(age),max(classid) from stu);

• 高级部分

　　　　1、索引 ：表上创建和各种键叫索引,相当于书的目录，极大提高查找效率
1.1、主索引（主键）：primary key  

1.2、唯一索引（唯一键）unique key 

1.3、普通索引（普通键）key

primary key与unique key的区别:
a、作为Primary Key的域/域组不能为null，而Unique Key可以
b、在一个表中只能有一个Primary Key，而多个Unique Key可以同时存在

查看一条语句的效率： explain select 查询语句：

分析结果里的：possible_keys这条语句可能涉及到该表的键（索引）；key这条语句实际用到的索引；rows该sql实际遍历的次数，越小越好；

　　　　2、外键约束：指有关联的两个数据表之间的跨表条件约束
主表，从表
外加约束的注意事项：
2.1、表引擎 必须为innodb
2.2、要求关联字段的类型必须一致
2.3、主表（parent table）的字段被从表(child table)所引用

外加约束的作用：
a、约束主表数据不能随意删除和修改
b、约束从表的数据源于主表

外键约束的注意事项：
a、工作中可以没有外键，通过程序代码控制数据的完整性
b、外键的创建，会增大mysql服务器的压力

增加外键语法：
alter table 从表 add foreign key('从表字段') references 主表('主表字段');
创建失败原因：
a、表引擎不为innodb
b、数据类型不一致
c、存在错误数据

　　　　3、事务：用于保证 多条sql语句执行，要么全部成功 要么全部失败，从而保证数据的完整性
3.1、事务流程：
开启事务 start transaction 或者 begin
sql1;
sql2;
...
提交事务 commit （都成功时）/回滚事务 rollback（有失败时）

3.2、使用事务前提：表引擎必须为innodb

3.3、什么情况下使用事务
原则：对数据安全性要求非常高的情况下都建议使用事务，例如 钱相关

3.4、事务的使用举例
张飞给刘备转100元；

begin;
update stu set money=money-100 where name = '张飞';
update stu set money=money+100 where name = '刘备';
commit;

3.5 事务特点 原子性A,一致性C，隔离性I，持久性D
a、原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，或都不发生
b、一致性：事务前后，数据完整性
c、隔离性：多个并发事务之间数据要相互隔离
d、持久性：一个事务一旦提交，它对数据中的数据改变是永久的

　　　　4、实体间的关系:记录与记录间的关系
实体：实实在在的事物，即对象（记录）g--表（类）；
实体关系的类型？
一对一:如何创建一对一对应关系的表，使用相同的主键作为他们的关联字段
一对多：班级表与学生表之间的关系；如何创建一对多关系表：在多的一方添加一个字段保存一的一方的主键id
多对多：