语法:
SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …] #指定结果按照哪几个字段来分组
[ HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条
1.查询单表中所有列的记录:
select * from tablename;
2.查询单表中某些字段的记录:
select tab1,tab2,tab3... from tablename;
x
3.查询多表中,存在重复字段的情况下,需要指明重复字段所在的表名:
select student.studentNo from student,result where student.studentNo=result.studentNo;
4.AS起别名:
(1).可给数据列取一个新别名
SELECT StudentNo AS “学号” FROM student;
(2).可给表取一个新别名
SELECT a.StudentNo FROM student AS a;
(3).可把经计算或总结的结果用另外一个新名称来代替
SELECT StudentNo-1000 AS '学号',StudentName AS '姓名' FROM student;
注意点:select中对结果列进行的运算,不会对原表中的数据有所改变。
5.DISTINCT关键字-去除查询结果中重复的记录
观察区别:
SELECT * FROM result; //将所有记录都显示出来
SELECT DISTINCT StudentNo FROM result; //将StudentNo去重之后,显示出来。
SELECT DISTINCT StudentNo,StudentName from result;//注意:StudentNo与StudentName看做整体,再去重。
6.sql中的表达式:
SELECT语句返回结果列中使用
SELECT version() as MySQL_V , 123.44*100 AS EXPRESSION;
SELECT语句的ORDER BY、HAVING等子句中使用
DML语句中的where条件语句中使用表达式
SELECT * FROM result WHERE StudentNo >1012;
2016-03-22:
1.where条件:
逻辑运算符:
and/or/xor(^)/not(!):
and:SELECT * FROM result WHERE studentresult>=80 AND studentresult<=90;
//查询所有大于等于80并且小于等于90的学生信息。
or:SELECT * FROM result WHERE studentresult>=80 or studentresult<=90;
//查询所有大于等于80或者小于等于90的学生信息。
not:SELECT * FROM result WHERE NOT(studentresult>=80);
//查询所有不大于80的学生信息。
xor:SELECT * FROM result WHERE (studentresult>=80) XOR (studentresult<=90); //查询所有>90或者<80的学生成绩
等价于:
(studentresult>=80) AND !(studentresult<=90)
OR
!(studentresult>=80) AND (studentresult<=90)
2.IS NULL/IS NOT NULL:判断字段值是否为null。(前提是此字段允许为null)
select * from student where Address is not NULL;
select * from student where Address is NULL;
3.in:使用in进行范围选择
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );
等价于:
SELECT * FROM subject where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120;
4.LIKE模糊查询:
“%”:表示匹配0或任意多个字符
“_”:表示匹配单个字符
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '%s';
//最后一个字母为s的SubjectName
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '_s_';
//一共三个字母,中间字母为s的subject1信息
SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …] #指定结果按照哪几个字段来分组
[ HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条
1.查询单表中所有列的记录:
select * from tablename;
2.查询单表中某些字段的记录:
select tab1,tab2,tab3... from tablename;
x
3.查询多表中,存在重复字段的情况下,需要指明重复字段所在的表名:
select student.studentNo from student,result where student.studentNo=result.studentNo;
4.AS起别名:
(1).可给数据列取一个新别名
SELECT StudentNo AS “学号” FROM student;
(2).可给表取一个新别名
SELECT a.StudentNo FROM student AS a;
(3).可把经计算或总结的结果用另外一个新名称来代替
SELECT StudentNo-1000 AS '学号',StudentName AS '姓名' FROM student;
注意点:select中对结果列进行的运算,不会对原表中的数据有所改变。
5.DISTINCT关键字-去除查询结果中重复的记录
观察区别:
SELECT * FROM result; //将所有记录都显示出来
SELECT DISTINCT StudentNo FROM result; //将StudentNo去重之后,显示出来。
SELECT DISTINCT StudentNo,StudentName from result;//注意:StudentNo与StudentName看做整体,再去重。
6.sql中的表达式:
SELECT语句返回结果列中使用
SELECT version() as MySQL_V , 123.44*100 AS EXPRESSION;
SELECT语句的ORDER BY、HAVING等子句中使用
DML语句中的where条件语句中使用表达式
SELECT * FROM result WHERE StudentNo >1012;
2016-03-22:
1.where条件:
逻辑运算符:
and/or/xor(^)/not(!):
and:SELECT * FROM result WHERE studentresult>=80 AND studentresult<=90;
//查询所有大于等于80并且小于等于90的学生信息。
or:SELECT * FROM result WHERE studentresult>=80 or studentresult<=90;
//查询所有大于等于80或者小于等于90的学生信息。
not:SELECT * FROM result WHERE NOT(studentresult>=80);
//查询所有不大于80的学生信息。
xor:SELECT * FROM result WHERE (studentresult>=80) XOR (studentresult<=90); //查询所有>90或者<80的学生成绩
等价于:
(studentresult>=80) AND !(studentresult<=90)
OR
!(studentresult>=80) AND (studentresult<=90)
2.IS NULL/IS NOT NULL:判断字段值是否为null。(前提是此字段允许为null)
select * from student where Address is not NULL;
select * from student where Address is NULL;
3.in:使用in进行范围选择
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );
等价于:
SELECT * FROM subject where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120;
4.LIKE模糊查询:
“%”:表示匹配0或任意多个字符
“_”:表示匹配单个字符
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '%s';
//最后一个字母为s的SubjectName
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '_s_';
//一共三个字母,中间字母为s的subject1信息