Mysql练习

练习1：

代码演示：

mysql> create database zye1;
Query OK, 1 row affected (0.00 sec)mysql> use zye1
Database changed
mysql> create table employees(-> id int primary key,-> name varchar(50) not null,-> age int,-> gender varchar(10) not null default 'unknown',-> salary float);
Query OK, 0 rows affected (0.08 sec)mysql> create table orders (-> id int primary key,-> name varchar(100) not null,-> price float,-> quantity int,-> category varchar(50)-> );
Query OK, 0 rows affected (0.01 sec)mysql> create table invoices(-> number int primary key auto_increment,-> order_id int,-> in_date date,-> total_amount float check (total_amount > 0),-> foreign key (orser_id)-> references orders(id)-> );

 题目：

1、显示所有职工的基本信息。

mysql> select * from t_worker;

2、查询所有职工所属部门的部门号，不显示重复的部门号。  

mysql> select distinct department_id  from t_worker;

3、求出所有职工的人数。  

mysql> select count(worker_id)-> from t_worker;

4、列出最高工资和最低工资。

mysql> select max(wages)as max,min(wages) as min-> from t_worker;

5、列出职工的平均工资和总工资。   

mysql> select avg(wages)as avg,sum(wages)as sum-> from t_worker;

6、创建一个只有职工号、姓名和工作时间的新表，名为工作日期表。 

mysql> create table work_date_table-> select worker_id,name,worker_date-> from t_worker;

7、显示所有党员的年龄。 

mysql> select name,timestampdiff(year,birth_date,curdate()) as age-> from t_worker-> where politics = '党员';

10、列出工资在4000-8000之间的所有职工姓名。 

mysql> select name from t_worker where wages between 4000 and 8000;

11、列出所有陈孙和李姓的职工姓名。

mysql> select name-> from t_worker-> where name like '孙%' or name like '李%';

12、列出所有部门号为102和103的职工号、姓名、党员否。  

mysql> select worker_id,name-> from t_worker-> where (department_id = 102 or department_id = 103) and politics != '党员';

13、将职工表worker中的职工按出生的先后顺序排序。

mysql> select * from t_worker order by birth_date;

14、显示工资最高的前3名职工的职工号和姓名。 

mysql> select worker_id,name-> from t_worker-> order by wages desc-> limit 3;

15、求出各部门党员的人数。 

mysql> select department_id,count(*) as count from t_worker where politics = '党员'-> group by department_id;

16、统计各部门的工资和平均工资并保留2位小数

mysql> select department_id, sum(wages) as sum,round(avg(wages),2)as avg from t_worker group by department_id;

17、列出总人数大于3的部门号和总人数。 

mysql> select department_id,count(*) as count-> from t_worker-> group by department_id-> having count >=3;