MySql基本文法及練習（4）

標籤：mysql基本文法   sql語句練習   

1.建立一個員工表（並指明字元集為UTF8）
drop table if exists employee;
create table employee(
   id int,
   name varchar(20),
   gender varchar(6),
   birthday date,
   entry_date date,
   job varchar(30),
   salary float(5,1),
   resume text
);

2.插入資料：
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume)
values(1,‘jack‘,‘male‘,‘2011-10-8‘,‘2011-12-31‘,‘software‘,5000.1,‘hello‘);

3.在上面員工表的基本上增加一個image列。
alter table employee  add image blob;

4.修改job列，使其長度為60。
alter table employee
modify job varchar(60) default ‘teacher‘;

5.刪除gender列。
alter table employee drop gender;

6.表名改為user。
rename table employee to user;

注意：對於MySql而言，不能修改資料庫的名字，但是可以修改表名

7.修改表的字元集為gbk。
alter table user
character set UTF8;

8.列名name修改為username。
alter table user
change column name username varchar(20); 

9.向user表插入一條中文記錄
insert into user(username,id,birthday,entry_date,job,salary,resume)
values(‘傑克‘,2,‘2011-10-8‘,‘2011-12-31‘,‘software‘,5000.1,‘你好‘);

insert into user values(3,‘馬利‘,‘2011-10-8‘,‘2011-12-31‘,‘software‘,5000.1,‘你好‘,NULL);

insert into user values(4,‘馬利‘,‘2011-10-8‘,‘2011-12-31‘,‘software‘,5000.1,NULL,NULL);

insert into user(id,username,birthday,entry_date,job,salary,image) 
values(5,‘馬利‘,‘2011-10-8‘,‘2011-12-31‘,‘software‘,5000.1,NULL);

10.修改用戶端輸入和輸出使用的編碼方式，與WindowXP平台一致
set character_set_client=gbk;
set character_set_results=gbk;

11.將所有員工薪水修改為6000元。
update user set salary = 6000;

12.將姓名為’馬利’的員工薪水修改為7000元。
update user set salary = 7000 where username = ‘馬利‘;

13.將’jack’的薪水在原有基礎上增加1000元。
update user set salary = salary + 1000 where username = ‘jack‘;

14.刪除表中名稱為’jack’的記錄。
delete from user where username = ‘jack‘;

15.刪除表中所有記錄。
delete from user;

16.使用truncate刪除表中記錄。
truncate table user;

17.查詢表中所有學生的資訊。
select * from student;
select id,name,math,chinese,english from student;
select name,id,math,chinese,english from student;
select name,math from student;

18.查詢表中所有學生的姓名和對應的英語成績。
select name,english from student;

19.過濾表中重複資料。
select distinct english from student;
select distinct name,english from student;

20.在所有學生分數上加10分特長分。
select name,math+10 from student;
select name as 姓名,math+10 as 數學 from student;

21.統計每個學生的總分。
select name,math+chinese+english
from student;

22.使用別名表示學生分數。
select name,math+chinese+english as 總分
from student;

23.查詢姓名為’張小明’的學產生績
select * from student 
where name = ‘張小明‘;

24.查詢英語成績大於90分的同學
select * from student 
where english > 90;

24.查詢總分大於200分的所有同學
select name,chinese+math+english as 總分
from student
where chinese+math+english > 200;

25.查詢英語分數在 80－90之間的同學。
select *
from student
where english>=80 and english<=90;
或 
select *
from student
where english between 80 and 90;

26.查詢數學分數為89,90,91的同學。
select *
from student
where math=89 or math= 90 or math=91;
或
select *
from student
where math [not] in(89,90,91);

27.查詢所有姓’李’的學產生績。
select *
from student
where name LIKE ‘李%‘;

select * from student
where name LIKE ‘%李‘;

select * from student
where name LIKE ‘%李%‘;

28.在網站開發中多條件查詢中常用到
select * from student
where name LIKE ‘%%‘;

select * from student
where name LIKE ‘__李‘;

select *  from student
where math IS [NOT] NULL;

29.查詢數學分>80且語文分>80的同學。
select * 
from student
where math >80 and chinese>80;

30.對數學成績排序後輸出。
升序：
select * 
from student
order by math asc; 

降序：
select * 
from student
order by math desc; 

對總分降序後輸出。
select name,math+chinese+english as 總分
from student
order by math+chinese+english desc;

31.對姓’李’的學生總分降序輸出。
select name,math+chinese+english as 總分
from student
where name LIKE ‘李%‘
order by math+chinese+english desc;

32.統計一個班級共有多少學生？
select count(*) as 總人數
from student;

33.統計數學成績大於80的學生有多少個？
select count(*) as 總人數
from student
where math > 80;

34.統計總分大於250的人數有多少？
select count(*) as 總人數
from student
where (math+chinese+english) > 250;

select count(english) as 總人數
from student;//13

select count(math) as 總人數
from student;

35.統計一個班級數學總成績。
select sum(math)
from student;

select sum(name)
from student;//0

36.統計一個班級語文、英語、數學各科的總成績。
select sum(math) as 數學總分,sum(chinese) as 語文總分,sum(english) as 英語總分
from student;

37.統計一個班級語文、英語、數學的成績總和。
select sum(math)+sum(chinese)+sum(english) as 班級總分
from student;

38.統計一個班級語文成績平均分。
select sum(math)/count(math)
from student;

select sum(math)/count(*)
from student;

總結：

  (1).delete from 或truncate table或drop table的各自的區別：
     delete from：按行刪除表中的所有記錄，但會保留表，適合刪除資料量不大的資料，可按條件刪除
     truncate table：複製原表結構-〉一次性刪除整表 -> 自動回復原表結構，適合刪除資料量較大的資料，不能按條件刪除
     drop table：刪除表本身
     刪除記錄時，一定要留意表間的關聯關係

 (2).排序：NULL值為最小，使用order by子句，預設升序，order by子句必須放置在最後
 (3).複合函數
   (1)count()函數，統計之用，不統計NULL值
   (2)sum()函數，統計和之用，不要統計非數值，如果統計非數值，返回0

 (4).合計函數
  avg()
  max(),min()，當max()和min()函數位於日期類型時，分別取得最近日期和最早日期