11 第五章 mysql查询技术 实例

yuweixian4230

打开数据库：

1. ./bin/mysqld_safe --defaults-file=./my.cnf --user=root &
   
2. ./bin/mysqladmin --defaults-file=./my.cnf -u root password 123456
   
3. ./bin/mysql --defaults-file=./my.cnf -u root -p
   
4. Enter password:

2. 新建数据库及添加内容

1. mysql> create database emp;
   
2. mysql> use emp;
   
3. mysql> create table emp_infor(
   
4.     -> emp_id int primary key,
   
5.     -> emp_name varchar(20) not null,
   
6.     -> emp_*** enum('f','m') not null,
   
7.     -> emp_birth date not null,
   
8.     -> emp_cer varchar(25),
   
9.     -> emp_address varchar(50),
   
10.     -> emp_phone varchar(11) not null,
    
11.     -> emp_telhome char(14),
    
12.     -> emp_telwork char(14),
    
13.     -> emp_blood char(2),
    
14.     -> emp_salary float not null,
    
15.     -> emp_level int not null,
    
16.     -> emp_dep char(20) not null,
    
17.     -> emp_pos char(10) not null,
    
18.     -> emp_start date not null,
    
19.     -> emp_end date,
    
20.     -> emp_status enum('y','n'));
    
21. mysql> insert into emp_infor(emp_id,emp_name,emp_***,emp_birth,emp_address,
    
22. emp_phone,emp_blood,emp_salary,emp_level,emp_dep,emp_pos,emp_start,emp_status)
    
23. values (100,'liqiang','m','1985-05-03','beijing','13500000000','ab',7000,1,
    
24. 'kaifa','jingli','2007-07-01','y');
    
25. 
    
26. mysql> insert into emp_infor(emp_id,emp_name,emp_***,emp_birth,emp_address,
    
27. emp_phone,emp_blood,emp_salary,emp_level,emp_dep,emp_pos,emp_start,emp_status)
    
28. values (101,'liuguang','m','1975-11-03','shangdong','13700000001','0',18000,3,
    
29. 'kaifa','jingli','1995-07-01','y');
    
30. 
    
31. mysql> insert into emp_infor(emp_id,emp_name,emp_***,emp_birth,emp_address,
    
32. emp_phone,emp_blood,emp_salary,emp_level,emp_dep,emp_pos,emp_start,emp_status)
    
33. values (103,'liwen','m','1955-11-11','guangxi','13600000001','o',5700,1,
    
34. 'kaifa','gongcs','2008-07-01','y');

1. 总人数查询

1. mysql> select count(*) from emp_infor;
   
2. 
   
3. mysql> select count(emp_id) from

2.部门总数查询

1. mysql> select distinct emp_dep from emp_infor;
   
2. +---------+
   
3. | emp_dep |
   
4. +---------+
   
5. | kaifa |
   
6. +---------+

有多少个部门

1. mysql> select count(distinct emp_dep) from emp_infor;
   
2. +-------------------------+
   
3. | count(distinct emp_dep) |
   
4. +-------------------------+
   
5. | 1 |
   
6. +-------------------------+

3. as语句的使用

1. mysql> select count(distinct emp_dep) as 'bumeng zongshu' from emp_infor;
   
2. +----------------+
   
3. | bumeng zongshu |
   
4. +----------------+
   
5. | 1 |
   
6. +----------------+

4. like语句使用

1. mysql> select count(emp_id) from emp_infor where emp_dep like 'kai%';
   
2. +---------------+
   
3. | count(emp_id) |
   
4. +---------------+
   
5. | 3 |

5.sum函数

1. mysql> select sum(emp_salary) as 'xinshui' from emp_infor;
   
2. +---------+
   
3. | xinshui |
   
4. +---------+
   
5. | 30700 |
   
6. +---------+

1. mysql> select sum(emp_salary) as 'zongzhichu' from emp_infor where emp_dep like 'kai%';
   
2. +------------+
   
3. | zongzhichu |
   
4. +------------+
   
5. | 30700 |
   
6. +------------+

6.avg函数 平均

1. mysql> select (select sum(emp_salary) from emp_infor)/(select count(emp_id) from emp_infor) as 'pingjunshouru';
   
2. +------------------+
   
3. | pingjunshouru |
   
4. +------------------+
   
5. | 10233.3333333333 |
   
6. +------------------+

1. mysql> select avg(emp_salary) from emp_infor;
   
2. +------------------+
   
3. | avg(emp_salary) |
   
4. +------------------+
   
5. | 10233.3333333333 |
   
6. +------------------+

 max函数
 min函数


排序和分类
   
   1.order by 是按某一个字段进行排序，排序的方式可以是从低到高，也可以从高到低，分别对应asc 和 desc属性

1. mysql> select emp_name as 'xingming',emp_dep as 'bumeng',emp_salary as 'xinshui' from emp_infor order by emp_salary desc;
   
2. +----------+--------+---------+
   
3. | xingming | bumeng | xinshui |
   
4. +----------+--------+---------+
   
5. | liuguang | kaifa | 18000 |
   
6. | liqiang | kaifa | 7000 |
   
7. | liwen | kaifa | 5700 |
   
8. +----------+--------+---------+

   2. group by 的作用是对结果进行分组，将相同的归纳为一组。