mysql命令用法复习笔记-白红宇

mysql命令用法复习笔记

阅读量：6695 次

发布时间：2019-06-25

本文共 30431 字，大约阅读时间需要 101 分钟。

show DATABASES ;create database nulige character set utf8;use nulige;show tables;#创建表CREATE TABLE ExamResult(   id INT PRIMARY KEY  auto_increment,   name VARCHAR (20),   JS DOUBLE ,   Django DOUBLE ,   flask DOUBLE);#往表中插入数据INSERT INTO ExamResult VALUES  (1,"yuan",98,98,98),                               (2,"xialv",35,98,67),                               (3,"alex",59,59,62),                               (4,"wusir",88,89,82),                               (5,"alvin",88,98,67),                               (6,"yuan",86,100,55);#查询表中所有学生的信息select * from ExamResult;#查询表中所有学生的姓名和对应的英语成绩SELECT name,JS FROM ExamResult;#过滤表中重复数据select DISTINCT Js,name from ExamResult;SELECT name,JS,Django,flask FROM ExamResult;SELECT name,JS+10,Django+10,flask+20 FROM ExamResult;SELECT name as 姓名,JS+10 as JS成绩,Django+10,flask+20 FROM ExamResult;SELECT name JS from ExamResult;select * from ExamResult WHERE name='yuan'SELECT name,Js from ExamResult WHERE JS>90;SELECT name,JS from ExamResult WHERE JS!=88;#between在什么之间  在88=100之间SELECT name,JS FROM ExamResult WHERE JS BETWEEN 88 and 100;#in在什么之内SELECT name,JS FROM ExamResult WHERE JS IN (88,99,77);#like 模糊查询，查询名字内有y的同学SELECT name,JS FROM ExamResult WHERE name LIKE "y%";SELECT name,JS FROM ExamResult WHERE name LIKE "a____";#查询js分=98同学SELECT name,JS from ExamResult WHERE name='yuan' and JS=98;insert into ExamResult (name) VALUE ('刘洋');SELECT * from ExamResult;#查询值为空SELECT name from ExamResult WHERE JS is NULL;#排序SELECT name,JS from ExamResult WHERE JS>70 ORDER BY JS;SELECT name,JS FROM ExamResult WHERE JS>70 ORDER BY Js DESC ;#as重命名SELECT name,JS+Django+flask as 总成绩 from ExamResult ORDER BY 总成绩 DESC;#按yuan 总成绩进行降序排列（mysql语句有执行顺序：from where select group by having order by)SELECT name,JS+Django+flask as 总成绩 from ExamResult WHERE name="yuan" ORDER BY 总成绩;#分组查询 group bySELECT * from ExamResult;#按名字进行分组SELECT * from ExamResult GROUP BY name;#按JS进行分组SELECT * from ExamResult GROUP BY JS;#按第3列进行分组SELECT * from ExamResult GROUP BY 3;#按名字进行排序SELECT name,sum(JS) from ExamResult GROUP BY name;#对成绩表按名字分组后，显示每一类名字的Django的分数总和>150的SELECT name,sum(Django) from ExamResult GROUP BY name having sum(Django)>150;#having和where两者都可以对查询结果进行进一步的过滤，差别有：# <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；# <2>使用where语句的地方都可以用having进行替换# <3>having中可以用聚合函数，where中就不行。SELECT * from ExamResult WHERE id=3;SELECT * from ExamResult HAVING id=3;#聚合函数：先把要求的内容查出来再包上聚合函数即可。#count(列名)：统计行的个数SELECT count(name) from ExamResult WHERE js>70;#统计一个班级共有多少学生select count(*) from ExamResult;SELECT sum(JS)/count(name) from ExamResult;SELECT AVG(JS) from ExamResult;#遇到ifnull的时候转换成0#统计总分大于280的人数有多少？select count(name) from ExamResult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(flask,0))>280;SELECT * from ExamResult;#max最大值SELECT max(JS) FROM ExamResult;#遇到null的时候，就转成0#min最小值SELECT min(ifnull(JS,0)) FROM ExamResult;SELECT max(JS+Django+flask) from ExamResult;#limit 跳过几条显示几条SELECT * FROM  ExamResult LIMIT 1;#跳过1,从2开始到5SELECT * FROM  ExamResult LIMIT 1,4;

增加外键

#查看数据库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || lessens            || mysql              || performance_schema || sys                |+--------------------+5 rows in set#创建abc数据库mysql> create database abc character set utf8;Query OK, 1 row affected#进入abc表mysql> use abc ;Database changed#创建表mysql> CREATE TABLE ClassCharger(       id TINYINT PRIMARY KEY auto_increment,       name VARCHAR (20),       age INT ,       is_marriged boolean)ENGINE=INNODB;Query OK, 0 rows affected#往表中插入数据mysql> INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",52,0),                                                       ("丹丹",34,0),                                                       ("歪歪",32,0),                                                       ("姗姗",28,0),                                                       ("小雨",61,0);Query OK, 5 rows affectedRecords: 5  Duplicates: 0  Warnings: 0#创建表mysql> CREATE TABLE Student(       id INT PRIMARY KEY auto_increment,       name VARCHAR (20),       charger_id TINYINT) ENGINE=INNODB;Query OK, 0 rows affected#往表中插入数据mysql> INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),                                            ("alvin2",4),                                            ("alvin3",1),                                            ("alvin4",3),                                            ("alvin5",5);Query OK, 5 rows affectedRecords: 5  Duplicates: 0  Warnings: 0#查看表中数据mysql> select * from Student;+----+--------+------------+| id | name   | charger_id |+----+--------+------------+|  1 | alvin1 |          2 ||  2 | alvin2 |          4 ||  3 | alvin3 |          1 ||  4 | alvin4 |          3 ||  5 | alvin5 |          5 |+----+--------+------------+5 rows in set#查看表中数据mysql> select * from ClassCharger;+----+------+-----+-------------+| id | name | age | is_marriged |+----+------+-----+-------------+|  1 | 冰冰 |  52 |           0 ||  2 | 丹丹 |  34 |           0 ||  3 | 歪歪 |  32 |           0 ||  4 | 姗姗 |  28 |           0 ||  5 | 小雨 |  61 |           0 |+----+------+-----+-------------+5 rows in set#创建外键mysql> ALTER TABLE Student  ADD CONSTRAINT abc                     FOREIGN KEY(charger_id)                     REFERENCES  classcharger(id);Query OK, 5 rows affectedRecords: 5  Duplicates: 0  Warnings: 0#查看表结构mysql> desc Student;+------------+-------------+------+-----+---------+----------------+| Field      | Type        | Null | Key | Default | Extra          |+------------+-------------+------+-----+---------+----------------+| id         | int(11)     | NO   | PRI | NULL    | auto_increment || name       | varchar(20) | YES  |     | NULL    |                || charger_id | tinyint(4)  | YES  | MUL | NULL    |                |+------------+-------------+------+-----+---------+----------------+3 rows in set#查看表结构mysql> desc ClassCharger;+-------------+-------------+------+-----+---------+----------------+| Field       | Type        | Null | Key | Default | Extra          |+-------------+-------------+------+-----+---------+----------------+| id          | tinyint(4)  | NO   | PRI | NULL    | auto_increment || name        | varchar(20) | YES  |     | NULL    |                || age         | int(11)     | YES  |     | NULL    |                || is_marriged | tinyint(1)  | YES  |     | NULL    |                |+-------------+-------------+------+-----+---------+----------------+4 rows in set#查看建表语句mysql> show CREATE TABLE Student;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                                                                                                                                                                                                                                                                                |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Student | CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT NULL,  `charger_id` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `abc` (`charger_id`),  CONSTRAINT `abc` FOREIGN KEY (`charger_id`) REFERENCES `classcharger` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set

mysql> select * from student;

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

| id | name | charger_id |

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

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

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

5 rows in set

#插入数据

mysql> insert into student(name,charger_id) values("alvin1",2),("alvin1",4);

Query OK, 2 rows affected

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from student;

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

| id | name | charger_id |

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

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

| 6 | alvin1 | 2 |

| 7 | alvin1 | 4 |

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

7 rows in set

#查看表

mysql> show tables;

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

| Tables_in_abc |

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

| classcharger |

| student |

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

2 rows in set

#查看表内容

mysql> select * from classcharger;

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

| id | name | age | is_marriged |

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

| 1 | 冰冰 | 52 | 0 |

| 2 | 丹丹 | 34 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 4 | 姗姗 | 28 | 0 |

| 5 | 小雨 | 61 | 0 |

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

5 rows in set

#查看表内容

mysql> select * from student;

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

| id | name | charger_id |

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

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

| 6 | alvin1 | 2 |

| 7 | alvin1 | 4 |

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

7 rows in set

#更新表中数据

mysql> update student set charger_id=4 where id=1 or id=6;

Query OK, 2 rows affected

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from student;

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

| id | name | charger_id |

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

| 1 | alvin1 | 4 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

| 6 | alvin1 | 4 |

| 7 | alvin1 | 4 |

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

7 rows in set

#删除掉id=2 的丹丹，再插入数据就会报错。

mysql> delete from classcharger where id=2;

Query OK, 1 row affected

mysql> select * from classcharger;

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

| id | name | age | is_marriged |

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

| 1 | 冰冰 | 52 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 4 | 姗姗 | 28 | 0 |

| 5 | 小雨 | 61 | 0 |

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

4 rows in set

再插入数据就会报错

mysql> insert into student (name,charger_id) values("alvin8",2);

1452 - Cannot add or update a child row: a foreign key constraint fails (`abc`.`student`, CONSTRAINT `abc` FOREIGN KEY (`charger_id`) REFERENCES `classcharger` (`id`))

#删除外键

mysql> alter table student drop foreign key abc;

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

#查看建表语句

mysql> show create table student;

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

| Table | Create Table |

| student | CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `abc` (`charger_id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

1 row in set

#增加外键

mysql> ALTER TABLE student ADD CONSTRAINT abc

FOREIGN KEY(charger_id)

REFERENCES classcharger(id);

Query OK, 7 rows affected

Records: 7 Duplicates: 0 Warnings: 0

#查看增加的外键

mysql> show create table student;

| Table | Create Table |

| student | CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `abc` (`charger_id`),

CONSTRAINT `abc` FOREIGN KEY (`charger_id`) REFERENCES `classcharger` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

1 row in set

INNODB支持的ON语句

#查看数据库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || abc                || mysql              || performance_schema || sys                |+--------------------+5 rows in set#创建数据库并设置字符集mysql> create database s1 character set utf8;Query OK, 1 row affectedmysql> use s1;Database changed#创建表ccmysql> CREATE TABLE CC(       id TINYINT PRIMARY KEY auto_increment,       name VARCHAR (20),       age INT ,       is_marriged boolean);Query OK, 0 rows affected#创建表s3，并设置外键为 delete cascade 方式mysql>  create table s3(     id int primary key auto_increment,     name varchar(20),     charger_id tinyint,     foreign key (charger_id) references CC(id) on delete cascade     ) engine=innodb;Query OK, 0 rows affected#往表中插入数据mysql> INSERT INTO CC (name,age,is_marriged) VALUES ("冰冰",52,0),                                             ("丹丹",34,0),                                             ("歪歪",32,0),                                             ("姗姗",28,0),                                             ("小雨",61,0);Query OK, 5 rows affectedRecords: 5  Duplicates: 0  Warnings: 0#往表中插入数据mysql> INSERT INTO S3(name,charger_id) VALUES ("alvin1",2),                                        ("alvin2",4),                                        ("alvin3",1),                                        ("alvin4",3),                                        ("alvin5",5);Query OK, 5 rows affectedRecords: 5  Duplicates: 0  Warnings: 0#查看表内容mysql> select * from cc;+----+------+-----+-------------+| id | name | age | is_marriged |+----+------+-----+-------------+|  1 | 冰冰 |  52 |           0 ||  2 | 丹丹 |  34 |           0 ||  3 | 歪歪 |  32 |           0 ||  4 | 姗姗 |  28 |           0 ||  5 | 小雨 |  61 |           0 |+----+------+-----+-------------+5 rows in set#查看表内容mysql> select * from s3;+----+--------+------------+| id | name   | charger_id |+----+--------+------------+|  1 | alvin1 |          2 ||  2 | alvin2 |          4 ||  3 | alvin3 |          1 ||  4 | alvin4 |          3 ||  5 | alvin5 |          5 |+----+--------+------------+5 rows in set#删除表中数据mysql> delete from cc where id=4;Query OK, 1 row affected#再查看表内容，4的数据被删除了。mysql> select * from s3;+----+--------+------------+| id | name   | charger_id |+----+--------+------------+|  1 | alvin1 |          2 ||  3 | alvin3 |          1 ||  4 | alvin4 |          3 ||  5 | alvin5 |          5 |+----+--------+------------+4 rows in set#查看建表语句mysql>  show create table s3;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                                                                                                |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| s3    | CREATE TABLE `s3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT NULL,  `charger_id` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `charger_id` (`charger_id`),  CONSTRAINT `s3_ibfk_1` FOREIGN KEY (`charger_id`) REFERENCES `cc` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set#########################设置外键为 set null 方式########################查看建表语句的外键信息mysql>  show create table s3;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                                                                                                |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| s3    | CREATE TABLE `s3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT NULL,  `charger_id` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `charger_id` (`charger_id`),  CONSTRAINT `s3_ibfk_1` FOREIGN KEY (`charger_id`) REFERENCES `cc` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set#删除外键mysql> alter table s3 drop foreign key s3_ibfk_1;Query OK, 0 rows affectedRecords: 0  Duplicates: 0  Warnings: 0#查看删除结果mysql>  show create table s3;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                   |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| s3    | CREATE TABLE `s3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT NULL,  `charger_id` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `charger_id` (`charger_id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set#增加外键为set null 方式mysql> alter table s3 add constraint s3_fk_cc foreign key (charger_id)    -> references cc(id) on delete set null;Query OK, 4 rows affectedRecords: 4  Duplicates: 0  Warnings: 0mysql> select * from cc;+----+------+-----+-------------+| id | name | age | is_marriged |+----+------+-----+-------------+|  1 | 冰冰 |  52 |           0 ||  2 | 丹丹 |  34 |           0 ||  3 | 歪歪 |  32 |           0 ||  5 | 小雨 |  61 |           0 |+----+------+-----+-------------+4 rows in setmysql> delete from CC where id=3;Query OK, 1 row affectedmysql> select * from s3;+----+--------+------------+| id | name   | charger_id |+----+--------+------------+|  1 | alvin1 |          2 ||  3 | alvin3 |          1 ||  4 | alvin4 | NULL       ||  5 | alvin5 |          5 |+----+--------+------------+4 rows in setmysql>

mysql> show databases;

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

| Database |

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

| information_schema |

| abc |

| crm |

| mysql |

| performance_schema |

| s1 |

| sys |

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

7 rows in set

#创建数据库

mysql> create database t2 character set utf8;

Query OK, 1 row affected

mysql> use t2;

Database changed

#创建表

mysql> create table tableA(id int primary key,name varchar(20));

Query OK, 0 rows affected

#创建表

mysql> create table tableB(id int primary key,name varchar(20),tableA_id int);

Query OK, 0 rows affected

#往表中插入数据

mysql> insert into tableA values(1,'alvin'),(2,'xialv'),

(3,'yuan');

Query OK, 3 rows affected

Records: 3 Duplicates: 0 Warnings: 0

#往表中插入数据

mysql> insert into tableB values(1,'小雨',1),(2,'冰冰',2),(3,'周周',4);

Query OK, 3 rows affected

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tableA;

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

| id | name |

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

| 1 | alvin |

| 2 | xialv |

| 3 | yuan |

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

3 rows in set

mysql> select * from tableB;

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

| id | name | tableA_id |

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

| 1 | 小雨 | 1 |

| 2 | 冰冰 | 2 |

| 3 | 周周 | 4 |

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

3 rows in set

#笛卡尔积查询

mysql> select * from tableA,tableB;

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

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

| 1 | alvin | 1 | 小雨 | 1 |

| 2 | xialv | 1 | 小雨 | 1 |

| 3 | yuan | 1 | 小雨 | 1 |

| 1 | alvin | 2 | 冰冰 | 2 |

| 2 | xialv | 2 | 冰冰 | 2 |

| 3 | yuan | 2 | 冰冰 | 2 |

| 1 | alvin | 3 | 周周 | 4 |

| 2 | xialv | 3 | 周周 | 4 |

| 3 | yuan | 3 | 周周 | 4 |

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

9 rows in set

#内连接查询

mysql> select * from tableA,tableB where tableA.id=tableB.id;

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

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

| 1 | alvin | 1 | 小雨 | 1 |

| 2 | xialv | 2 | 冰冰 | 2 |

| 3 | yuan | 3 | 周周 | 4 |

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

3 rows in set

#只显示关联人的id,姓名，

mysql> select tableA.id,tableA.name,tableB.name from tableA,tableB where tableA.id=tableB.id;

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

| id | name | name |

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

| 1 | alvin | 小雨 |

| 2 | xialv | 冰冰 |

| 3 | yuan | 周周 |

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

3 rows in set

mysql> select * from tableB inner join tableA on tableB.tableA_id =tableA.id;

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

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

| 1 | 小雨 | 1 | 1 | alvin |

| 2 | 冰冰 | 2 | 2 | xialv |

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

2 rows in set

#多表查询之连接查询

#创建表

mysql>create table employee(

emp_id int auto_increment primary key not null,

emp_name varchar(50),

age int,

dept_id int

);

Query OK, 0 rows affected

#往表中插入数据

mysql> insert into employee(emp_name,age,dept_id) values

('A',19,200),

('B',26,201),

('C',30,201),

('D',24,202),

('E',20,200),

('F',38,204);

Query OK, 6 rows affected

Records: 6 Duplicates: 0 Warnings: 0

#创建表

mysql>create table department(

dept_id int,

dept_name varchar(100)

);

Query OK, 0 rows affected

#往表中插入数据

mysql>insert into department values

(200,'人事部'),

(201,'技术部'),

(202,'销售部'),

(203,'财政部');

Query OK, 4 rows affected

Records: 4 Duplicates: 0 Warnings: 0

#查看表

mysql> show tables;

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

| Tables_in_t2 |

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

| department |

| employee |

| tablea |

| tableb |

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

4 rows in set

#笛卡尔积查询

mysql> select * from employee,department;

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

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

| 1 | A | 19 | 200 | 200 | 人事部 |

| 1 | A | 19 | 200 | 201 | 技术部 |

| 1 | A | 19 | 200 | 202 | 销售部 |

| 1 | A | 19 | 200 | 203 | 财政部 |

| 2 | B | 26 | 201 | 200 | 人事部 |

| 2 | B | 26 | 201 | 201 | 技术部 |

| 2 | B | 26 | 201 | 202 | 销售部 |

| 2 | B | 26 | 201 | 203 | 财政部 |

| 3 | C | 30 | 201 | 200 | 人事部 |

| 3 | C | 30 | 201 | 201 | 技术部 |

| 3 | C | 30 | 201 | 202 | 销售部 |

| 3 | C | 30 | 201 | 203 | 财政部 |

| 4 | D | 24 | 202 | 200 | 人事部 |

| 4 | D | 24 | 202 | 201 | 技术部 |

| 4 | D | 24 | 202 | 202 | 销售部 |

| 4 | D | 24 | 202 | 203 | 财政部 |

| 5 | E | 20 | 200 | 200 | 人事部 |

| 5 | E | 20 | 200 | 201 | 技术部 |

| 5 | E | 20 | 200 | 202 | 销售部 |

| 5 | E | 20 | 200 | 203 | 财政部 |

| 6 | F | 38 | 204 | 200 | 人事部 |

| 6 | F | 38 | 204 | 201 | 技术部 |

| 6 | F | 38 | 204 | 202 | 销售部 |

| 6 | F | 38 | 204 | 203 | 财政部 |

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

24 rows in set

外连接之左外连接

mysql> select * from employee left join department on employee.dept_id = department.dept_id;

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

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

| 1 | A | 19 | 200 | 200 | 人事部 |

| 5 | E | 20 | 200 | 200 | 人事部 |

| 2 | B | 26 | 201 | 201 | 技术部 |

| 3 | C | 30 | 201 | 201 | 技术部 |

| 4 | D | 24 | 202 | 202 | 销售部 |

| 6 | F | 38 | 204 | NULL | NULL |

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

6 rows in set

mysql> select employee.emp_name,department.dept_name from employee,department where employee.dept_id = department.dept_id and employee.emp_name="A";

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

| emp_name | dept_name |

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

| A | 人事部 |

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

1 row in set

#外连接之右外连接

mysql> select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

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

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

| 1 | A | 19 | 200 | 200 | 人事部 |

| 2 | B | 26 | 201 | 201 | 技术部 |

| 3 | C | 30 | 201 | 201 | 技术部 |

| 4 | D | 24 | 202 | 202 | 销售部 |

| 5 | E | 20 | 200 | 200 | 人事部 |

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

6 rows in set

#外连接之全外连接

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

-- mysql不支持全外连接 full JOIN

-- mysql可以使用此种方式间接实现全外连接

mysql> select * from tableB full join tableA on tableB.tableA_id = table.id;

1054 - Unknown column 'tableB.tableA_id' in 'on clause'

mysql> select employee.emp_name,department.dept_name from employee,department where employee

-> .dept_id=department.dept_id and department.dept_name="技术部";

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

| emp_name | dept_name |

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

| B | 技术部 |

| C | 技术部 |

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

2 rows in set

#显示大于25岁所有的部门

mysql> select distinct department.dept_name from employee,department where employee.dept_id=department.dept_id and employee.age>25;

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

| dept_name |

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

| 技术部 |

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

1 row in set

多表查询之子查询

#带IN关键字的子查询

mysql> select * from employee where dept_id in(200,201,202,203);

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

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

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

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

5 rows in set

mysql> select dept_id from department

;

+---------+

| dept_id |

+---------+

| 200 |

| 201 |

| 202 |

| 203 |

+---------+

4 rows in set

#带IN关键字的子查询

mysql> select * from employee where dept_id in(select dept_id from department);

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

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

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

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

5 rows in set

#用select查询语句，建表，会丢失主键信息

mysql> create table aa(select * from employee);

Query OK, 6 rows affected

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from aa;

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

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

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

| 6 | F | 38 | 204 |

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

6 rows in set

mysql> desc aa;

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

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

| emp_id | int(11) | NO | | 0 | |

| age | int(11) | YES | | NULL | |

| dept_id | int(11) | YES | | NULL | |

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

4 rows in set

#带EXISTS关键字的子查询（结果存在就返回true,不存在就返回false）

mysql> select * from employee where exists (select dept_name from department where dept_id=203);

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

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

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

| 6 | F | 38 | 204 |

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

6 rows in set

#不存在就返回false

mysql> select * from employee where exists (select dept_name from department where dept_id=205);

Empty set

转载于:https://www.cnblogs.com/dengbingbing/p/10429061.html

你可能感兴趣的文章