软件测试-数据库查询语句

创建数据库

CREATE DATABASE <数据库名>；
创建一个库名为lemon的数据库
create DATABASE lemon;
查看所有数据库
show databases;

删除数据库

drop database <数据库名>;
删除一个库名为test的数据库。
drop DATABASE test;

选择数据库

use <数据库名>;
选取数据库le进行操作。
use le;

创建表

create table <表名> ( <字段名1> <类型1>，<字段名2> <类型2>， …,<字段名n> <类型n>);
	需要创建一个学员信息表，用于存储学号、姓名、性别、班级、年龄。

		create table student(
sno int(8) not null primary key auto_increment comment ‘学号’,
sname varchar(20) NOT NULL comment ‘姓名’,
ssex varchar(4) NOT NULL comment ‘性别’,
sclass int(8) NOT NULL comment ‘班级’,
sage int(4) NOT NULL comment ‘年龄’
)DEFAULT CHARSET=utf8;

解析：int为整型，这里学号snso、班级编号sclass设定为整型；varchar字符型，这里姓名sname、性别ssex、年龄sage设定为字符型；字段属性设定为not null，表示字段值不允许空；primary key关键字用于定义列为主键。主键唯一、不重复，一般以编号为主键； auto_increment定义列属性为自增，一般用于主键，插入数据时数值会自动加1；comment相当于给列添加备注；DEFAULT CHARSET 设置编码，utf8对传值中文防止乱码。

显示数据表

show tables

删除表

drop table <表名>;

插入表数据

insert into <表名> ( 字段名1,字段名2,…字段名N) values ( 值1, 值2,…值N );

往学员信息表中插入一条信息insert into student (sno,sname,ssex,sclass,sage) values (6301,‘柠檬’,‘女’,20190163,18);注意:如果数据值为字符型必须用单引号或者双引号括起来。
2、批量插入，插入多条学员信息insert into student (sname,ssex,sclass,sage) values (‘毛毛’,‘女’,20190163,20),(‘大大’,‘女’,20190163,22);

修改表数据

update 表名 set 字段名1= 新值1,字段名2=新值2, … where 条件表达式1 and(or) 条件表达式2 …;
修改学员姓名为毛毛的性别为男update student set ssex = ‘男’ where sname = ‘毛毛’;

删除表数据

delete from 表名 where 条件1 and(or) 条件2……;

删除student表中年龄小于18岁的用户信息
delete from student where sage < 18;

多表联查

Select 多表联合查询语句

Select 列名 [[,列名]…] from 表名1,表名2,… Where 查询条件;

可以使用as为表或者字段（属性）取别名；
Select 列名 [[,列名]…] from 表名1 [as] 别名1,表名2 [as} 别名2,… Where 查询条件;

Select 列名1 [as] 列别名1 [[,列名2 [as] 列别名2]…] from 表名1 [as] 表别名1,表名2 [as] 表别名2,… Where 查询条件;

“[]”表示其中的内容可以省略；

原表数据：
学生表 student
老师表 teacher：

课程表 course：

选课表 student_course：
1 按“0001”号课程成绩由高到低顺序显示所有学生学号、姓名、成绩（二表连接）；

SELECT
	student.student_id,
	student.student_name,
	student_course.score
FROM
	student,
	student_course
WHERE
	student.student_id = student_course.student_id
AND student_course.course_id = '0001'
ORDER BY
	student_course.score DESC;
	

运行结果：

当两个（多个）表中有相同的字段（属性，列名）时，使用表名（或表别名）.列名的形式对查询内容和条件内容进行区分。

2 按“高等数学”课程成绩由高到低顺序显示所有学生姓名、学号、分数、课程名（三表连接）；

SELECT
	st.student_id,
	st.student_name,
	sc.score,
	co.course_name
FROM
	student AS st,
	student_course AS sc,
	course AS co
WHERE
	st.student_id = sc.student_id
AND sc.course_id = co.course_id
AND co.course_name = '高等数学'
ORDER BY
	sc.score DESC;

运行结果：

3 查询有薪水差额的任意两位教师（单表连接查询）；

SELECT
	t1.teacher_id AS '教工号1',
	t1.teacher_name AS '教师姓名1',
	t2.teacher_id AS '教工号2',
	t2.teacher_name AS '教师姓名2'
FROM
	teacher t1,
	teacher t2
WHERE
	t1.teacher_salary > t2.teacher_salary;

运行结果：

4 查询既学过“0001”又学过“0002”号课程的所有学生学号；

SELECT
	sc1.student_id '学号'
FROM
	student_course sc1,
	student_course sc2
WHERE
	sc1.student_id = sc2.student_id
AND sc1.course_id = '0001'
AND sc2.course_id = '0002';

运行结果：

查询“0001”号课程比“0002”号课程成绩低的所有学生学号姓名；

SELECT
	st.student_id '学号',
	st.student_name '姓名'
FROM
	student_course sc1,
	student_course sc2,
	student st
WHERE
	sc1.student_id = sc2.student_id
AND sc1.student_id = st.student_id
AND sc1.course_id = '0001'
AND sc2.course_id = '0002'
AND sc1.score < sc2.score;

运行结果：

外连接

左外：select * from 表1 别名1 left [ outer ] join 表2 别名2 on 等值条件；

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

左外的意思是：左表的记录无论是否满足条件，都会被查出来，而右表的记录只有满足条件的，才会被查出来。（左表中数据在右表中如果不存在，则右表的字段值为null）
理解左外右外：

先看两个表数据：


/左外/

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;

左外连接是以左边的表为基准。通俗的讲，先把左表的数据全都显示出来，然后右边的表id与左边表的id的值是一样的，就拼接上去，比如说上面的id=1的数据，就拼接了。

如果没有匹配的，比如说id=2的数据，右边没有，用null拼接。

例如：要查询所有学生的选课情况，包括已经选课的和还没有选课的学生，查询语句为
SELECT学生表.学号，姓名，班级，课程号，成绩
FROM学生表LEFT OUTER JOIN选课表ON学生表.学号=选课表.学号
左外连接查询中左端表中的所有元组的信息都得到了保留。

/*右外 */

SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;

右外跟左外的机制刚好相反。

如何去判断主副表？（左外连接：左边的是主表，右外连接：右边的是主表）

跟讲过的内连接做个简单的比较

SELECT * FROM table1 t1,table2 t2 WHERE t1.id = t2.id;