软件测试相关SQL语句

首先，先熟悉书写SQL查询语句的优先顺序：
(1) SELECT
(2) FROM
(3) WHERE
(4) GROUP BY
(5) HAVING
(6) ORDER BY
(7) LIMIT

开始~~~~~~~
1.创建数据库
CREATE DATABASE prc
2.创建表tb_name，tb_stu
下面是创建两个表的SQL语句
create table tb_name(
`id` int (11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘序号‘,
`uid` int (11) NOT NULL COMMENT ‘主键‘,
`name` varchar (150) NOT NULL COMMENT ‘姓名‘ ,
`search_mall_id` int (11) NOT NULL,
`dateline` int (11) NOT NULL COMMENT ‘时间‘,
INDEX uid(`uid`)
)
create table tb_stu(
`sid` int (11) NOT NULL PRIMARY KEY COMMENT ‘主键‘ ,
`name` varchar (150) NOT NULL COMMENT ‘姓名‘,
`stu_id` int (11) NOT NULL COMMENT ‘学号‘,
`stu_datetime` datetime NOT NULL COMMENT ‘入学时间‘,
INDEX sid(`sid`)
)


3. 查询数值型数据
SELECT name FROM tb_name WHERE id != 2;
SELECT name FROM tb_name WHERE id <> 2;
4.查询字符型数据
select * from tb_name where name like "%g%"
select * from tb_name where name like "%x%"
select * from tb_name where name like "g%"
select * from tb_name where name like "%g"
select * from tb_name where name =‘没得‘
5.查询日期型数据
SELECT *FROM tb_name where DATE(datetime) =‘2021-06-01 ‘
select now() as ‘日期加时间‘,select curdate() as ‘当前的日期‘,select curtime() as ‘当前的时间‘
SELECT EXTRACT(YEAR from datetime ) as ‘year‘,
EXTRACT(month from datetime ) as ‘month‘,
EXTRACT(day from datetime ) as ‘day‘
from tb_name;
SELECT DATEDIFF(‘2021-06-10‘,‘2021-06-01‘) as ‘间隔天数‘
6.查询前几条数据
SELECT * from tb_name GROUP BY search_mall_id LIMIT 3;
SELECT * from 表名 GROUP BY 列名 LIMIT 条数;
7.按月份查询统计数据
SELECT *from tb_name where MONTH(datetime )=6 GROUP BY datetime LIMIT 2;
8.查询结果不显示重复记录
SELECT DISTINCT search_mall_id from tb_name;
9.连表查询很高同学的学号和序号
SELECT id as ‘序号‘,stu_id as ‘学号‘ from tb_name a INNER JOIN tb_stu b on a.uid=b.sid where a.name=‘很高‘
SELECT a.name as ‘姓名‘,stu_id as ‘学号‘ from tb_name a INNER JOIN tb_stu b on a.uid=b.sid GROUP BY search_mall_id;
10.对字段名进行升序降序排列
SELECT dateline as ‘降序‘ FROM tb_name ORDER BY dateline desc;
SELECT dateline as ‘升序‘ FROM tb_name ORDER BY dateline asc;
11.多表分组统计
SELECT a.search_mall_id,a.name,b.sid FROM tb_name a INNER JOIN tb_stu b on a.uid=b.sid where a.name !=‘没得‘ GROUP BY a. search_mall_id desc;

记录今天的学习~~