测试面试中MySQL 测试面试常用sql

CREATE TABLE 香水表
（
Id_P INT,
品牌 VARCHAR(30) NOT NULL UNIQUE,
原产地 VARCHAR(30)
);

DROP TABLE 香水表；

CREATE TABLE 香水表
(
Id_Perfume INT PRIMARY KEY,
品牌 VARCHAR(30) NOT NULL UNIQUE,
系列 VARCHAR(30) NOT NULL UNIQUE,
原产地 VARCHAR(50),
适合人群 VARCHAR(9) NULL
）；

INSERT INTO dbo.香水表
（
Id_Perfume,
品牌，
系列，
原产地，
适合人群
）
VALUES
(
1,
'Dior',
'真我',
'法国',
'女'
);

ALIAS=AS 别名
列名称指定别名
SELECT 列名1 AS 别名1,列名2 AS 别名2 ⋯⋯FROM 表名；********把表中的列名1、列名2⋯⋯在查询集中指定为别名1、别名2；
SELECT 列名1 别名1，列名2 别名2，⋯⋯FROM表名；

表名称指定别名
SELECT 列名1、列名2、列名3⋯⋯FROM 表名 AS 表别名；
SELECT 列名1、列名2、列名3⋯⋯FROM 表名 表别名；

WHERE语句
SELECT * FROM 表名 WHERE 列名 运算符（=/</>/<=/>=/BETWEEN/LIKE)‘限定值’；
SELECT 列名1、列名2⋯⋯FROM 表名 WHERE 列名 运算符（=/</>/<=/>=/BETWEEN/LIKE)‘限定值’；

ALTER TABLE 修改表
ALTER TABLE 表名 ADD 列名 数据类型；********给表添加列(不能跟NOT NULL)
ALTER TABLE 表名 ALTER COLUMN 列名 数据类型 NOT NULL/NULL；********改变表中某列的属性(所在列有NULL值则不能加NOT NULL)
ALTER TABLE 表名 DROP COLUMN 列名；*********删除表中某列
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束（列名1、列名2⋯⋯）；
简写=ALTER TABLE 表名 ADD 约束名（列名1、列名2⋯⋯）；
ALTER TABLE 表名 DROP CONSTRAINT [UNIQUE];*********删除名为UNIQUE的约束

EXEC SP_RENAME '表名.原列名','新列名','column';*********更改表中的列名

UPDATE 表名 SET 列名1=某值 ; **********对表中的列名1进行全部赋值
UPDATE 表名 SET 列名1=某值1 WHERE 列名2=某值2；*********对列名2 下的某值2 对应的 列名1 赋 某值1
UPDATE 表名 SET 列名1=某值1，列名2=某值2，列名3=某值3 WHERE 列名4=某值4；********更改某一行多个列的值

BETWEEN
SELECT 列名1，列名2，列名3 FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2；******** 显示 值1和 值2 范围之内的
SELECT 列名1，列名2，列名3 FROM 表名 WHERE 列名 NOT BETWEEN 值1 AND 值2；*********显示 值1 和 值2 范围之外的

（NOT)LIKE
SELECT 列名1，列名2，列名3 FROM 表名 WHERE 列名4 LIKE 'n%'; *********查询出以n字头显示的内容
SELECT 列名1，列名2，列名3 FROM 表名 WHERE 列名4 LIKE '%n%';********查询出中间带n字显示的内容
SELECT 列名1，列名2，列名3 FROM 表名 WHERE 列名4 LIKE '%n';*********查询出以n字结尾显示的内容
SELECT 列名1，列名2，列名3 FROM 表名 WHERE 列名4 NOT LIKE '%n';*********查询出不以n字结尾显示的内容

AND/OR
SELECT * FROM 表名 WHERE 列名1=某值1 AND 列名2=某值2；**********查询表中“列名1=某值1” 和“列名2=某值2“ 同时成立的项
SELECT * FROM 表名 WHERE 列名1=某值1 OR 列名2=某值2；**********查询表中“列名1=某值1”或“列名2=某值2”只要有一个成立的项

IN
SELECT * FROM 表名 WHERE 列名1 IN ('值1'，'值2'，'值3'）；*********查询列名1中同时符合值1，值2，值3的所有项

DISTINCT 只支持单列
SELECT DISTINCT 列名1 FROM 表名；********用于返回列名1中唯一的（没有冗余）的项

ORDER BY(对指定的列的结果集进行排序_默认升序_对多个列时优先级从左至右）ASC升序
SELECT 列名1，列名2，列名2 FROM 表名 ORDER BY 列名n ASC;**********根据“列n”对表进行升序排列
SELECT 列名1，列名2，列名2 FROM 表名 ORDER BY 列名n DESC;**********根据“列n”对表进行降序排列
SELECT 列名1，列名2，列名2 FROM 表名 ORDER BY 列名1 ASC,列名2 ASC，列名3 DESC;**********根据“列1”对表进行升序排列后对表2在1的基础上进行升序排列并在1，2条件上对列3降序排序

TOP （用于规定要返回的记录和数目）
SELECT TOP 数值n（PERCENT) * FROM 表名 ORDER BY 列n;********根据列n的排列返回“数值n”个项

DELETE （用于删除某行、多行、所有行）
DELETE FROM 表名 WHERE 列名=值；
DELETE FROM 表名 ；********删除表中所有数据

TRUNCATE TABLE 表名；删除表所有数据（行），但不破坏表结构

DROP TABLE 表名；删除表（表结构、数据、约束等所有内容）
DROP DATABASE 数据库名；删除数据库

IDENTITY 自动添加ID数值，默认从1开始递增
IDENTITY(1000,1)从1000起始，每次递增1
CREATE TABLE 表名
( ID INT PRIMARY KEY IDENTITY,
NAME VARCHAR(30))

CHECK(列=/>/</<=/>=值) 约束用于限制列中的值的范围
CREATE TABLE 表名
(
姓名 VARCHAR (100) ,
年龄 INT CHECK ( 年龄 >= 18 AND 年龄 <= 30 ), -- 这里不能用OR
性别 CHAR(4) CHECK (性别 = '男' OR 性别 = '女') -- 这里不能用AND
);

DEFAULT'值' 约束用于向列中插入默认值，
如果不再为具有DEFAULT约束的列额外进行值输入的话，则自动填入默认值；
反之，则填入所输入的值
CREATE TABLE INSERT_DEFAULT_EXAM_DATA
(
Id INT PRIMARY KEY IDENTITY ,
姓名 VARCHAR(100) NOT NULL ,
考试时间 DATE DEFAULT '2018-05-20'
);

查询某列为NULL ：列n IS NULL SELECT * FROM 表名 WHERE 列n IS NULL
查询某列不为NULL： 列n IS NOT NULL SELECT * FROM 表名 WHERE 列n IS NOT NULL

SQL 函数（function) 应用于列
SELECT function(单个列的列名) FROM 表

ISNULL() 函数用于规定如何处理 NULL 值
ISNULL(列名，值）

AVG(列n） 列n的平均值
SELECT AVG(列n） AS 别名 FROM 表名******在表中查询列n的平均值

子查询例句：统计出小于平均年龄且低于Selenium平均分的学员
SELECT 姓名 FROM dbo.软件测试考试分数表_重制版
WHERE
年龄<(SELECT AVG(年龄)FROM dbo.软件测试考试分数表_重制版)
AND
Selenium得分<(SELECT AVG(Selenium得分)FROM dbo.软件测试考试分数表_重制版);

MAX()返回一列中的最大值
SELECT MAX(列n) FROM 表名

MIN() 函数返回一列中的最小值
SELECT MIN(列n) FROM 表名

COUNT() 函数返回匹配指定条件的行数，
COUNT(列名）函数返回指定列的值的数目（NULL 不计入）
SELECT COUNT(列名) FROM 表名
COUNT(*) 函数返回表中的记录数
SELECT COUNT(*) FROM 表名
COUNT(DISTINCT 列名) 函数返回指定列的不同值的数目
SELECT COUNT(DISTINCT 列名) FROM 表名

SUM()函数返回数值列的总数（总额）--只针对数值类型
SELECT SUM(列名) FROM 表名

LEN() 函数返回文本字段中值的长度
SELECT LEN(列名) FROM 表名

ROUND() 函数用于把数值字段舍入为指定的小数位数
SELECT ROUND(列名,指定位数) FROM 表名

FOREIGN KEY约束 指向另外一个表中的 PRIMARY KEY（含有FOREIGN KEY的为 附表，指向的为主表）
作用在于预防破坏表之间的连接动作
FOREIGN KEY 约束的 列的数据类型必须与所指向表PRIMARY KEY 约束的数据类型相一致
新建表时就添加外键约束：
CREATE TABLE 学生表
(
Student_Id INT PRIMARY KEY,
姓名 VARCHAR(10),
Student_Class_Id INT FOREIGN KEY REFERENCES 班级表(Class_Id)
);

新建表时，添加外键约束名或同时添加多个外键约束时使用：
CREATE TABLE 学生表
(
Student_Id INT PRIMARY KEY,
姓名 VARCHAR(10),
Student_Class_Id INT,
CONSTRAINT [fk_class_and_student] FOREIGN KEY (Student_Class_Id) REFERENCES 班级表(Class_Id)
);

在表已经存在的情况下，添加外键约束:
ALTER TABLE 学生表 ADD FOREIGN KEY (Student_Class_Id) REFERENCES 班级表(Class_Id);

在表已经存在的情况下，添加外键约束名或同时添加多个外键约束时使用:
ALTER TABLE 学生表 ADD CONSTRAINT [fk_class_and_student] FOREIGN KEY (Student_Class_Id) REFERENCES 班级表(Class_Id);

撤销FOREIGN KEY约束: ALTER TABLE 学生表 DROP CONSTRAINT [fk_class_and_student];

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组
SELECT column_name_1,column_name_2, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name_1, column_name_2;

HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用
HAVING只能和GROUP BY绑定使用
HAVING = WHERE，只不过它是GROUP BY专属的“WHERE”
SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

(INNER) JOIN 返回两张表都有的共同的内容
SQL JOIN用于根据两个或多个表中的列之间的关系，从这些表中查询数据
同样是通过主键和外键进行连接
SELECT * FROM 香水信息表 JOIN 商场专柜销售情况表 ON 香水编号 = 香水编号_外键 ORDER BY Id_Perfume;

LEFT JOIN
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
实例：
SELECT * FROM 香水信息表 LEFT JOIN 商场专柜销售情况表 ON 香水编号 = 香水编号_外键;

UNION 操作符
用于合并两个或多个 SELECT 语句的结果集
UNION 内部的 SELECT 语句必须拥有相同数量的列,，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的顺序必须相同。
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION 操作符选取不同的值，两个表中对应列有两个相同值则只取一个值
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

UNION ALL 操作符 跟UNION功能相同的基础上允许重复的值，
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2