-- DQL練習題
--? 1.使用mydb1數據庫
use mydb2;
-- 2.創(chuàng)建學生表
create TABLE student(
sid INT PRIMARY KEY,
sname VARCHAR(20),
gender VARCHAR(2),
chinese DOUBLE,
english DOUBLE,
math DOUBLE
);
-- 3.向表中添加學生數據
INSERT INTO student VALUES(1,'張明','男',89,78,90);
INSERT INTO student VALUES(2,'李進','男',67,53,95);
INSERT INTO student VALUES(3,'王五','女',87,78,77);
INSERT INTO student VALUES(4,'李一','女',88,98,92);
INSERT INTO student VALUES(5,'李財','男',82,84,67);
INSERT INTO student VALUES(6,'張寶','男',55,85,45);
INSERT INTO student VALUES(7,'黃蓉','女',75,65,30);
INSERT INTO student VALUES(7,'黃蓉','女',75,65,30);
-- 4.按條件查找
-- 4.1查詢表中所有學生的信息
select *from student;
-- 4.2 查詢表中所有學生的姓名和對應的英語成績
select sname,english FROM student;
-- 4.3過濾掉表中的重復數據
SELECT DISTINCT *FROM student;
-- 4.4統計每一個學生的總分
SELECT sname,(chinese+english+math) as total_score FROM student;
-- 4.5在所有學生總分數上加上10分
SELECT sname,(chinese+english+math)+10 as total_score FROM student;
-- 4.6使用別名表示學生分數
SELECT sname? as '學生姓名',chinese as '語文成績',english as '英語成績',math as '數學成績' FROM student;
-- 查詢英語成績大雨90的同學
SELECT * from student where english>90;
-- 查詢總分大于200的同學
SELECT *FROM student where (chinese+english+math)>200;
-- 查詢英語成績在80-90之間的同學
SELECT *FROM student where english>=80 AND english<=90;
SELECT *FROM student where english BETWEEN 80 and 90;
-- 查詢英語成績不在80-90之間的同學
SELECT *FROM student WHERE? not english>=80 and english<=90;
SELECT *FROM student where? english not BETWEEN 80 and 90;
-- 查詢數學分數為89，90膀篮，91的同學
SELECT *FROM student WHERE math=89;
SELECT *FROM student WHERE math=90;
SELECT *FROM student WHERE math=91;
SELECT *FROM student where math in(89,90,91);
-- 查詢數學分數不為89享钞，90，91的同學
SELECT *FROM student where math not in(89,90,91);
-- 查詢所有姓李的同學英語成績
SELECT sname,english FROM student where sname like '李%';
-- 查詢數學英語都是80的同學
SELECT *FROM student where math=80 and english=80;
-- 查詢英語80或者總分大于等于200
SELECT *FROM student where english=80 or (chinese+english+math)>200;
-- 對數學成績進行降序排序
SELECT *FROM student ORDER BY math;
-- 對總分進行輸出并對總分進行排序
SELECT sname AS '學生姓名',(chinese+english+math)as total_score FROM student ORDER BY (chinese+english+math);
-- 對姓李的學生成績進行排序
SELECT sname as '學生姓名',(chinese+english+math)as total_score FROM student WHERE sname LIKE '李%' ORDER BY (chinese+english+math);
-- 查詢男生和女生分別有多少人并且按照降序輸出,查詢出人數大于4的性別
SELECT gender,count(*) as total_count FROM student GROUP BY gender? having total_count>3 order by total_count DESC ;
