11 第五章 mysql查询技术 实例

打开数据库：

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

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

mysql> create database emp;
mysql> use emp;
mysql> create table emp_infor(
    -> emp_id int primary key,
    -> emp_name varchar(20) not null,
    -> emp_*** enum('f','m') not null,
    -> emp_birth date not null,
    -> emp_cer varchar(25),
    -> emp_address varchar(50),
    -> emp_phone varchar(11) not null,
    -> emp_telhome char(14),
    -> emp_telwork char(14),
    -> emp_blood char(2),
    -> emp_salary float not null,
    -> emp_level int not null,
    -> emp_dep char(20) not null,
    -> emp_pos char(10) not null,
    -> emp_start date not null,
    -> emp_end date,
    -> emp_status enum('y','n'));
mysql> insert into emp_infor(emp_id,emp_name,emp_***,emp_birth,emp_address,
emp_phone,emp_blood,emp_salary,emp_level,emp_dep,emp_pos,emp_start,emp_status)
values (100,'liqiang','m','1985-05-03','beijing','13500000000','ab',7000,1,
'kaifa','jingli','2007-07-01','y');

mysql> insert into emp_infor(emp_id,emp_name,emp_***,emp_birth,emp_address,
emp_phone,emp_blood,emp_salary,emp_level,emp_dep,emp_pos,emp_start,emp_status)
values (101,'liuguang','m','1975-11-03','shangdong','13700000001','0',18000,3,
'kaifa','jingli','1995-07-01','y');

mysql> insert into emp_infor(emp_id,emp_name,emp_***,emp_birth,emp_address,
emp_phone,emp_blood,emp_salary,emp_level,emp_dep,emp_pos,emp_start,emp_status)
values (103,'liwen','m','1955-11-11','guangxi','13600000001','o',5700,1,
'kaifa','gongcs','2008-07-01','y');

1. 总人数查询

mysql> select count(*) from emp_infor;

mysql> select count(emp_id) from

2.部门总数查询

mysql> select distinct emp_dep from emp_infor;
+---------+
| emp_dep |
+---------+
| kaifa   |
+---------+

有多少个部门

mysql> select count(distinct emp_dep) from emp_infor;
+-------------------------+
| count(distinct emp_dep) |
+-------------------------+
|                       1 |
+-------------------------+

3. as语句的使用

mysql> select count(distinct emp_dep) as 'bumeng zongshu' from emp_infor;
+----------------+
| bumeng zongshu |
+----------------+
|              1 |
+----------------+

4. like语句使用

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

5.sum函数

mysql> select sum(emp_salary) as 'xinshui' from emp_infor;
+---------+
| xinshui |
+---------+
|   30700 |
+---------+

mysql> select sum(emp_salary) as 'zongzhichu' from emp_infor where emp_dep like 'kai%';
+------------+
| zongzhichu |
+------------+
|      30700 |
+------------+

6.avg函数 平均

mysql> select (select sum(emp_salary) from emp_infor)/(select count(emp_id) from emp_infor) as 'pingjunshouru';
+------------------+
| pingjunshouru    |
+------------------+
| 10233.3333333333 |
+------------------+

mysql> select avg(emp_salary) from emp_infor;
+------------------+
| avg(emp_salary)  |
+------------------+
| 10233.3333333333 |
+------------------+

 max函数
 min函数


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

mysql> select emp_name as 'xingming',emp_dep as 'bumeng',emp_salary as 'xinshui' from emp_infor order by emp_salary desc;
+----------+--------+---------+
| xingming | bumeng | xinshui |
+----------+--------+---------+
| liuguang | kaifa  |   18000 |
| liqiang  | kaifa  |    7000 |
| liwen    | kaifa  |    5700 |
+----------+--------+---------+

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