一,选择表中的若干组员查询
1,当使用通配符的时候, 字符长本身就含有通配符,如“%” ,“\”,需要使用escape'%',
将通配符进行转意
SELECT * FROM users WHERE NAME LIKE 's\_o' ESCAPE '\'
2,查询某一列的数值为空的组员
SELECT * FROM users WHERE gender Is NULL
3,查询某一列使其按升序或者降序排列
SELECT * FROM users ORDER BY ID DESC(asc)
二,聚集函数
1,求平均数用avg: SELECT AVG(id) FROM users
2,求最小/大值用min/max: SELECT MIN(id) FROM users
3,求某列的和用sum: SELECT SUM(id) FROM users
4,统计组员的条数用count: SELECT COUNT(*/distict) AS '总人数' from users
5,分组统计组员的条数时用group by 列名(可理解为将原来的一张大表分成了若干个小表):
SELECT userroleid,count(*) AS '总人数' from users GROUP BY userroleid
6,having短语按一定的条件将分组进行删选,即把符合条件的分组进行输出:
select sno(学生的编号) from sc(成绩表) where grade(学生的成绩)>=90 group by sno having count(*)>3
三,多表连接查询
1,等值连接与非等值连接查询,基本形式:
where <表名1>.<列名1> <比较运算符> <表名2>.<列名2> (比较运算符包括>,<,!=)
2,自身连接查询,课看做是在一张表的两张副表之间进行的连接,必须给两张副表指定别名
3,外连接
a,左外连接,以student为主体,列出每个学生的基本情况及其选课情况,求没有选课的学生也希望
列出来,这是需要使用外连接实现:
select student.*,cno(课程编号),grade(成绩)from student left outer join sc(成绩表) on student.sno=sc.sno
b,两个表:A(id,name)数据:(1,张三)(2,李四)(3,王五)
B(id,name)数据:(1,学生)(2,老师)(4,校长)
左连接结果:select A.*,B.* from A left join B on A.id=B.id;
1 张三 1 学生
2 李四 2 老师
3 王五 NULL NULL
右链接结果:select A.*,B.* from A right join B on A.id=B.id;
1 张三 1 学生
2 李四 2 老师
NULL NULL 4 校长
--查询操作
select stock_id,stock_name,speci from stock;
--datediff(day,start_time,end_time)函数
select prj_name,start_time,end_time,datediff(day,start_time,end_time) as '抢修天数' from saving;
select department from saving;
--distinct去重
select distinct department from out_stock;
select stock_name from stock where unit not between 0 and 80;
select * from stock where warehouse like ('%1_')
--escape转为一般字符
select * from stock where warehouse like '救灾\_1号' escape '\';
select * from out_stock where department is null;
select stock_id,unit from stock order by unit desc;
--先在仓库降序,在按库存量升序
select * from stock order by warehouse desc,stock_num;
--top 2和top 50 percent
select top 50 percent * from stock order by stock_num desc
select count(distinct prj_id) from out_stock
select max(out_num) as 最大值,min(out_num) as 最小值,avg(out_num) as 平均值 from out_stock where stock_id=000001
--查询每个抢险项目使用的物资种类.执行顺序:group by子句,,count(*) 函数,, having子句
select prj_id ,count(*) 数 from out_stock group by prj_id having count(stock_id)>2
select saving.* ,stock.* from saving,stock where saving.prj_id=stock.prj_id