第四章 SELECT语句

一：查询注意的地方

    SQL语法忽略大小写 ，但是对查询内容区分大小写

   

use test
create table student(
	id int identity primary key,
	stdName nvarchar(10),
	age int,
	address nvarchar(20),
	class nvarchar(10),
	speciality nvarchar(10),
	javaMark numeric(4,1),
	networkMark numeric(4,1),
	htmlMark numeric(4,1)
)
insert into student select '张三',20,'咸宁','200801','软件开发',75,67,45
insert into student select '李四',18,'武汉','200801','网络',95,89,85
insert into student select '王五',22,'宜昌','200801','软件开发',55,89,45
insert into student select '赵六',23,'宜昌','200801','网络',90,76,23
insert into student values('林奇',19,'襄樊','200801','软件开发',45,45,85)
insert into student values('赵柳',23,'荆州','200801','网络',95,23,98)
insert into student values('方成',25,'咸宁','200801','软件开发',89,45,78)
insert into student values('小李',17,'枝江','200801','网络',67,89,98)
insert into student select '小明',18,'孝感','200801','软件开发',90,78,54
insert into student select '小王',19,'钟祥','200801','网络',100,85,78
insert into student select '小红',21,'武汉','200801','软件开发',99,54,96
insert into student select '小黄',20,'宜昌','200801','网络',87,100,45
insert into student select '史蒂夫',19,'荆州','200801','软件开发',NULL,NULL,NULL
insert into student select '陈古',19,'宜昌','200801','网络',98,62,85

--查找出指定字段的信息
select id,stdName from student

--查找出所有字段的信息
select * from student

--查找不重复的信息
--例如：列出学生住的地方有哪些
select address from student   --此时会有重复的数据出现
select distinct address from student   --DISTINCT关键字消除重复项

--给字段起别名
select stdName as '学生姓名',address as '地址' from student
select stdName '学生姓名',address'地址' from student

--求学生三门课程的平均分
select stdName as '学生姓名',(javaMark+networkMark+htmlMark)/3 as '平均分' from student

--精确查找地址为武汉的学生
select * from student where address='武汉'

--精确查找地址不为武汉的学生
select * from student where address<>'武汉'
select * from student where address !='武汉'

--模糊查找学生姓名里包含‘小’字的学生
select * from student where stdName like '小%'

--模糊查找学生姓名第三个字为‘夫’的学生
select * from student where stdName like '__夫%'

--模糊查找学生姓名里包含'当'的学生
select * from student where stdName like '%李%'
--模糊匹配用LIKE,LIKE语句对匹配内容大小写敏感,通配符'%','_'

--and运算符,只有当两个表达式的值都为真的时候才会返回真,如果任意一个表达式的值不是真,那么结果就会是假的.
--查找htmlMark,javaMark都在60以上的学生信息
select * from student where htmlMark >=60 and javaMark>=60

--or当其中的任一个条件为真时,其结果就会为真值
--查找htmlMark或javaMark有一门不及的学生信息
select * from student where htmlMark <60 OR javaMark<60

--is not, is 操作符, 一般用于判断字段的值是不是null
--查找学生html成绩是null的学生信息
select * from student where htmlMark = null --错误写法
select * from student where htmlMark is null --正确写法

--查找学生HTML成绩不是null的学生信息
select * from student where htmlMark is not null

--从属运算(in ,between)
--查找学生是武汉或是宜昌或是咸宁的学生
select * from student where address='武汉' OR address='宜昌' OR address='咸宁' --老式写法
select * from student where address in('武汉','宜昌','咸宁')--新式写法

--查找JAVAMARK在80-100之间的学生
select * from student where javaMark>=80 and javaMark<=100 --老式写法
select * from student where javaMark between 80 and 100--新式写法