mysql9001_mysql学习笔记11_12（查询）

1、建表和插入值

创建company数据库

创建 department表

create table department(d_id int(10) primary key not null unique,d_name varchar(20) not null,function varchar(20),address varchar(30));

insert into department values(1001,'人事部','人事管理','北京');

insert into department values(1004,'销售部','产品销售','上海');

insert into department values(1003,'生产部','产品生产','天津');

insert into department values(1002,'科研部','研发产品','北京');

创建empoyee表

create table employee(id int(10) primary key not null unique,name varchar(20) not null,sex varchar(4),age int(5),d_id varchar(20),salary float,address varchar(50));

insert into employee values(9001,'Aric','男',25,'1002',4000,'北京市海淀区');

insert into employee values(9002,'Jim','男',26,'1001',2500,'北京市昌平区');

insert into employee values(9003,'Tom','男',20,'1003',1500,'湖南省永州市');

insert into employee values(9004,'Eric','男',30,'1001',3500,'北京市顺义区');

insert into employee values(9005,'Lily','女',21,'1002',3000,'北京市昌平区');

insert into employee values(9006,'Jack','男',28,'1003',1800,'天津市南开区');

employee表信息如下

mysql> select * from employee;

+------+------+------+------+------+--------+--------------------+

| id | name | sex | age | d_id | salary | address |

+------+------+------+------+------+--------+--------------------+

| 9001 | Aric | 男 | 25 | 1002 | 4000 | 北京市海淀区 |

| 9002 | Jim | 男 | 26 | 1001 | 2500 | 北京市昌平区 |

| 9003 | Tom | 男 | 20 | 1003 | 1500 | 湖南省永州市 |

| 9004 | Eric | 男 | 30 | 1001 | 3500 | 北京市顺义区 |

| 9005 | Lily | 女 | 21 | 1002 | 3000 | 北京市昌平区 |

| 9006 | Jack | 男 | 28 | 1003 | 1800 | 天津市南开区 |

+------+------+------+------+------+--------+--------------------+

6 rows in set (0.00 sec)

department表信息如下

mysql> select * from department;

+------+-----------+--------------+---------+

| d_id | d_name | function | address |

+------+-----------+--------------+---------+

| 1001 | 人事部 | 人事管理 | 北京 |

| 1002 | 科研部 | 研发产品 | 北京 |

| 1003 | 生产部 | 产品生产 | 天津 |

| 1004 | 销售部 | 产品销售 | 上海 |

+------+-----------+--------------+---------+

4 rows in set (0.00 sec)

2、查询表

(1)查询employee表的所有信息

(2)查询employee表的第4~5条记录

mysql> select * from employee limit 3,4;

+------+------+------+------+------+--------+--------------------+

| id | name | sex | age | d_id | salary | address |

+------+------+------+------+------+--------+--------------------+

| 9004 | Eric | 男 | 30 | 1001 | 3500 | 北京市顺义区 |

| 9005 | Lily | 女 | 21 | 1002 | 3000 | 北京市昌平区 |

| 9006 | Jack | 男 | 28 | 1003 | 1800 | 天津市南开区 |

+------+------+------+------+------+--------+--------------------+

3 rows in set (0.00 sec)

(3)从department表中查询部门号(d_id)、部门名称(d_name)和部门职能(function)

mysql> select d_id as 部门号,d_name as 部门名称,function as 部门职能

-> from department;

+-----------+--------------+--------------+

| 部门号 | 部门名称 | 部门职能 |

+-----------+--------------+--------------+

| 1001 | 人事部 | 人事管理 |

| 1002 | 科研部 | 研发产品 |

| 1003 | 生产部 | 产品生产 |

| 1004 | 销售部 | 产品销售 |

+-----------+--------------+--------------+

4 rows in set (0.02 sec)

(4)从employee表中查询人事部和科研部的员工的信息(嵌套查询)

mysql> select * from employee where d_id in (select d_id from department where (d_name ="人事部" or d_name ="科研部"));

+------+------+------+------+------+--------+--------------------+

| id | name | sex | age | d_id | salary | address |

+------+------+------+------+------+--------+--------------------+

| 9001 | Aric | 男 | 25 | 1002 | 4000 | 北京市海淀区 |

| 9002 | Jim | 男 | 26 | 1001 | 2500 | 北京市昌平区 |

| 9004 | Eric | 男 | 30 | 1001 | 3500 | 北京市顺义区 |

| 9005 | Lily | 女 | 21 | 1002 | 3000 | 北京市昌平区 |

(5)从employee表中查询年龄在25~30之间的员工的信息

mysql> select * from

-> employee

-> where age>25 and age<30;

+------+------+------+------+------+--------+--------------------+

| id | name | sex | age | d_id | salary | address |

+------+------+------+------+------+--------+--------------------+

| 9002 | Jim | 男 | 26 | 1001 | 2500 | 北京市昌平区 |

| 9006 | Jack | 男 | 28 | 1003 | 1800 | 天津市南开区 |

+------+------+------+------+------+--------+--------------------+

2 rows in set (0.00 sec)

(6)查询每个部门有多少员工

mysql> select d_id ,count(*) as 人数 from employee group by d_id;

+------+--------+

| d_id | 人数 |

+------+--------+

| 1001 | 2 |

| 1002 | 2 |

| 1003 | 2 |

+------+--------+

3 rows in set (0.00 sec)

更人性化的显示

mysql> select department.d_name as 部门,count(employee.d_id) as 人数 from employee,department where department.d_id = employee.d_id group by employee.d_id;

+-----------+--------+

| 部门 | 人数 |

+-----------+--------+

| 人事部 | 2 |

| 科研部 | 2 |

| 生产部 | 2 |

+-----------+--------+

3 rows in set (0.00 sec)

连表查询

内连接，外连接，全连接

内连接：根据表的依赖关系，只要相关表中存在意义相同的字段时，返回查询结果

外连接：外连接分为左外连接和右外连接，以左外连接(left jion)为例， table1 left jion table2 ,已左表为基础表，如果右表table2没有匹配到，则查询结果用Null补充。右连接同理，以右表为基础表，左表不能匹配的，查询结果用Null补充。

子查询

子查询：将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。在子查询中，通产用到IN,NOT IN , ANY ,ALL ,EXISTS和NOT EXISTS等关键字。