mysql 基本操作練習

標籤：

 1 create table employee (name varchar(30), sex char(2), age int, address varchar(30)); 2  3 insert into employee values (‘張三‘, ‘女‘, 19, ‘北京‘); 4 insert into employee values (‘李四‘, ‘男‘, 20, ‘上海‘); 5 insert into employee values (‘王五‘, ‘女‘, 25, ‘廣州‘); 6 insert into employee values (‘薛六‘, ‘女‘, 20, ‘北京‘); 7 insert into employee values (‘王五‘, ‘男‘, 22, ‘北京‘); 8 insert into employee values (‘趙七‘, ‘男‘, 28, ‘上海‘); 9 insert into employee values (‘張四‘, ‘女‘, 23, ‘北京‘);10 11 #（1）. 寫出sql語句，查詢所有年齡大於20歲的員工（2分）12 select *from employee where age>20;13 #（2）. 寫出sql語句，查詢所有年齡小於25歲的女性員工（3分）14 select *from employee where sex=‘女‘ and age<25;15 #（3）. 寫出sql語句，統計男女員工各有多少名（3分）16 #count 函數 17 select count(*) from employee;#統計表元素個數18 select count(*) from employee where sex=‘女‘;#統計女生個數19 select sex,count(*) from employee group by sex;#group by sex 按性別分組，用性別標記分組20 select sex,count(*) as num from employee group by sex;#as 起別名21 22 #（4）. 寫出sql語句，按照年齡倒序擷取員工資訊（3分）23 select * from employee order by age desc;24 #（5）. 寫出sql語句，擷取員工中哪個姓名具有重名現象（3分）25 SELECT *  26   FROM employee  27  WHERE name IN (SELECT name  28                   FROM employee  29                  GROUP BY name  30                 HAVING COUNT(*) > 1)  31 #（6）. 寫出sql語句，查詢所有姓張的員工（3分）32 select * from employee where name like ‘%張%‘;#模糊查詢，只要含張字33 select * from employee where name like ‘張%‘;#只要姓張34 #預留位置寫法35 select * from employee where name like ‘張__‘;36 #（7）. 寫出sql語句，查詢住址為北京的前3條記錄（3分）37 select * from employee where address=‘北京‘  order by name asc limit 0,3;#從下標幾開始 ，幾個38 select * from employee limit 3,3;39 #（8）. 寫出sql語句，查詢員工總數（3分）40 select count(*) as allnum from employee;41 #（9）. 寫出sql語句，向表中插入一條記錄（2分）42 insert into employee(name,sex,age,address) values(‘七七‘,‘男‘,20,‘深圳‘);43 #（10）.寫出sql語句，修改員工張四的住址為南京（2分）44 update employee set address=‘南京‘ where name=‘張四‘;45 #（11）.寫出sql語句，刪除年齡大於24歲的女員工（2分）46 delete from employee where age>24 and sex=‘女‘;

View Code

 

mysql 基本操作練習