1.查詢所有的列
2.查詢指定列
3.查詢時(shí)使用別名
4.查詢時(shí)添加常量列
5.查詢時(shí)合并列
6.查詢時(shí)去除重復(fù)記錄（distinct）
7.條件查詢
8.聚合查詢（使用聚合函數(shù)查詢）
9.分頁(yè)查詢（limit）
10.查詢排序
11.分組查詢（grop）
12.分組查詢后篩選（having）
1.查詢所有的列
select * from student;
2.查詢指定列
select id,name from student;
3.查詢時(shí)使用別名
select id as '編號(hào)',name as '姓名' from student;
4.查詢時(shí)添加常量列
select id,name,age,'軟件1501' as '班級(jí)' from student;
5.查詢時(shí)合并列
查詢每個(gè)學(xué)生的java和和html總成績(jī)
select id ,name ,(java+html)as '總成績(jī)' from student;
6.查詢時(shí)去除重復(fù)記錄（distinct）
select distinct(html) from student;
7.條件查詢
1)邏輯條件：and(與) or(或)
2)比較條件： > < >= <= == <>(不等于) between and
3)判斷條件（null 空字符串）：is null / is not null /=' '(等于空字符串) / <>' '
4)模糊條件：like
- 1)邏輯查詢
需求：查詢姓名為xiaoming html為88 的學(xué)生信息
select * from student where html=88 and name="xiaoming";
需求:查詢id為2或者java為98的學(xué)生信息
select * from student where id=2 or java=98;
- 2)邏輯查詢
需求:查詢java大于70小于80的人
select * from student where java>70 and java<80;
需求:查詢java大于70小于80的人用between and
select * from student where java between 70 and 80;
- 3)判空查詢
需求：查詢姓名為空的學(xué)生
select * from student where name is null;
- 4)模糊查詢
需求：查詢所有姓小的人
select * from student where name like '小%';
需求：查詢姓小但是名字為兩個(gè)字的學(xué)生信息（'小_'）
select * from student where name like '小__';
8.聚合查詢（使用聚合函數(shù)查詢）
常用的聚合函數(shù)：
1.sum(), 求和
2.avg(), 求平均值
3.max(),求最大值
- min(), 求最小值
5.count() 統(tǒng)計(jì)數(shù)量，不包含null
需求：查詢學(xué)生的math總成績(jī)
select sum(math) as 'math的總成績(jī)' from students;
需求：查詢學(xué)生math的平均成績(jī)
select avg(math) as '數(shù)學(xué)平均成績(jī)' from students;
需求：查詢學(xué)生math的最高分
select max(math) from students;
需求：查詢學(xué)生math的最低分
select min(math) from students;
需求：查詢學(xué)生math的最低分
select count(id) from students;
9.分頁(yè)查詢（limit）
帶兩個(gè)參數(shù)：起始和長(zhǎng)度
一般分頁(yè)查詢當(dāng)前頁(yè)的數(shù)據(jù)用：select * from students limit(當(dāng)前頁(yè)-1)*每頁(yè)顯示數(shù)據(jù)條數(shù)冒嫡。
select * from students limit 0,2;
10.查詢排序
默認(rèn)為插入記錄的順序查詢
order by 字段 asc/desc
asc:順序，正序全释。數(shù)據(jù)：遞增，字母：自然順序（a-z）
desc:倒序误债，反序恨溜。數(shù)據(jù)：遞減符衔，字母：自然反序（z-a）
select * from students order by name asc;
select * from students order by name desc;
需求：按照english 正序，按照math倒序排列
select * from students order by english asc,math desc;
11.分組查詢（grop）
需求：查詢成績(jī)相同的人數(shù)
select english from students group by english;
select english,count(*) from students group by english;
12.分組查詢后篩選（having）
需求：查詢成績(jī)相同數(shù)大于等于2的人數(shù)
select english,count(*) from students group by english having count(*)>1 ;
小練習(xí)（下附students.sql）：
students.sql
create table students(
id int,
name varchar(20),
chinese int,
english int,
math int
);
select * from students;
insert into students(id,name,chinese,english,math) values(1,'小明',89,78,90);
insert into students(id,name,chinese,english,math) values(2,'小紅',67,53,95);
insert into students(id,name,chinese,english,math) values(3,'張小非',87,78,77);
insert into students(id,name,chinese,english,math) values(4,'小花花',88,98,92);
insert into students(id,name,chinese,english,math) values(5,'楊飛',82,84,67);
insert into students(id,name,chinese,english,math) values(6,'張三',55,85,45);
insert into students(id,name,chinese,english,math) values(7,'照小飛',75,65,30);
代碼演示:
select * from students;
select name,english from students;
select distinct(english) from students;
select name,chinese as '中文',english as '英語(yǔ)',math as '數(shù)學(xué)'from students;
select (chinese+english+math) '總成績(jī)' from students where (chinese+english+math)>200;
select name,(chinese+english+math) as '總成績(jī)' from students;
select name,(chinese+english+math+10) as '總成績(jī)' from students;
上接文章：Mysql學(xué)習(xí)筆記(2)-管理表數(shù)據(jù)
