第四章 SELECT语句
一：查询注意的地方
??? SQL语法忽略大小写 ，但是对查询内容区分大小写
???
use testcreate 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,45insert into student select '李四',18,'武汉','200801','网络',95,89,85insert into student select '王五',22,'宜昌','200801','软件开发',55,89,45insert into student select '赵六',23,'宜昌','200801','网络',90,76,23insert 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,54insert into student select '小王',19,'钟祥','200801','网络',100,85,78insert into student select '小红',21,'武汉','200801','软件开发',99,54,96insert into student select '小黄',20,'宜昌','200801','网络',87,100,45insert into student select '史蒂夫',19,'荆州','200801','软件开发',NULL,NULL,NULLinsert 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 studentselect 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--新式写法?
?