mysql实例名 如何查询_MySQL查询方法实例（左右内联查询）13例

CREATE database test;

USE `test`;

/*Table structure for table `dept` */

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (

`id` int(11) NOT NULL,

`name` varchar(50) NOT NULL COMMENT '部门名称',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

insert into `dept`(`id`,`name`) values (1,'教育部'),(2,'销售部'),(3,'市场部'),(4,'人事部'),(5,'综合部');

/*Table structure for table `person` */

DROP TABLE IF EXISTS `person`;

CREATE TABLE `person` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL,

`age` tinyint(4) DEFAULT NULL,

`sex` enum('男','女','中性') NOT NULL,

`dept_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `person` */

insert into `person`(`id`,`name`,`age`,`sex`,`dept_id`) values (1,'小三',28,'中性',1),(2,'小四',23,'男',1),(3,'小五',30,'男',1),(4,'小六',18,'女',1),(5,'张三',20,'女',2),(6,'张四',20,'女',2),(7,'张五',20,'女',2),(8,'张六',20,'女',2),(9,'黄总',21,'女',3),(10,'李总',22,'女',3),(11,'小月',17,'女',4),(12,'小王',33,'女',NULL);

1.查询部门

SELECT * FROM dept;

2.查询人员

SELECT * FROM person;

3.查询人员和部门所有信息

select * from person,dept where person.id = dept.id;

多表连接查询语法(重点)

4.内连接查询 inner join on

查询人员和部门所有信息

select * from person a inner join dept d on a.id =d.id;

5.左外连接查询 left join on(注以左边的表为主，右边没有数据填NULL)

select * from person a left join dept d on a.id =d.id;

6.右外连接查询 right join on (注以右边的表为主，左边没有数据填NULL)

select * from person a right join dept d on a.id =d.id;

7.UNION(UNION 操作符用于合并两个或多个 SELECT 语句的结果集)

(SELECT * FROM person a LEFT JOIN dept d ON a.id =d.id)

UNION

(SELECT * FROM person a RIGHT JOIN dept d ON a.id =d.id)

8. UNION ALL (UNION ALL 操作符选取不同的值。如果允许重复的值)

(SELECT * FROM person a LEFT JOIN dept d ON a.id =d.id)

UNION ALL

(SELECT * FROM person a RIGHT JOIN dept d ON a.id =d.id)

9.DISTINCT (关键词 DISTINCT 用于返回唯一不同的值。)去重

SELECT DISTINCT * FROM person;

11.LIMIT 分页 从第几条读到第几条

SELECT * FROM person LIMIT 1,3

12. LIKE 操作符

SELECT * FROM person where name like '张%'

13. IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT * FROM person where id in (1,2,3)