Mysql入门到精通之数据表的操作

修改表

ALTER TABLE tb_name;

1.添加字段

ALTER TABLE tb_name ADD 字段名字 字段类型 约束条件 [FIRST/AFTER 字段名称]

1>添加用户名字段

ALTER TABLE test ADD username VARCHAR(20);

2>将字段test添加到表中第一个字段

ALTER TABLE test ADD test VARCHAR(20) FIRST;

3>在id之后添加年龄字段

ALTER TABLE test ADD age TINYINT UNSIGNED AFTER id;

4>一次添加多个字段

ALTER TABLE test ADD test1 INT UNSIGNED NOT NULL,

ADD test2 TINYINT UNSIGNED NOT NULL DEFAULT 12,

ADD test3 INT;

2.删除指定字段

ALTER TABLE tb_name DROP [COLUMN] 字段名称;

将test1字段删除

ALTER TABLE test DROP COLUMN test1;

将test字段删除

ALTER TABLE test DROP  test;

将test2,test3,字段删除,添加test4

ALTER TABLE test DROP test2,DROP test3,ADD test4 INT;

3.修改列定义MODIFY

ALTER TABLE tb_name MODIFY 字段名称 字段类型 约束条件 [FIRST|AFTER 字段名称];

将test4字段变为VARCHAR(20) NOT NULL DEFAULT ‘THIS IS A TEST‘ FIRST

ALTER TABLE test MODIFY test4 VARCHAR(20) NOT NULL DEFAULT ‘THIS IS A TEST‘ FIRST;

4.CHANGE修改字段名称

ALTER TABLE tb_name CHANGE 原字段名称 新字段名称 字段类型 约束条件 [FIRST|AFTER 字段名称]

将test4修改为test2

ALTER TABLE test CHANGE test4 test2 VARCHAR(32);(必须给新名称指定类型）

将test2修改为test2 数据类型 INT NOT NULL DEFAULT 100

ALTER TABLE test CHANGE test2 test2 INT NOT NULL DEFAULT 100;

将id字段修改为主键并自增长

ALTER TABLE test CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

将test2的字段位置改在username 之后

ALTER TABLE test CHANGE test2 test2 INT UNSIGNED NOT NULL DEFAULT 100 AFTER username;

5.添加主键ADD PRIMARY KEY

ALTER TABLE tb_name ADD PRIMARY KEY(字段名称）

CREATE TABLE test1(

id TINYINT);

将id字段添加为主键

ALTER TABLE test1 ADD PRIMARY KEY(id);

ALTER TABLE test1 ADD CONSTRAINT symbol PRIMARY KEY(id);

5.删除主键DROP PRIMARY KEY

ALTER TABLE tb_name DROP PRIMARY KEY;

删除TEST1的主键

ALTER TABLE test1 DROP PRIMARY KEY;

CREATE TABLE test2(

id TINYINT UNSIGNED AUTO_INCREMENT KEY

);

删除test2主键

ALTER TABLE test2 MODIFY id TINYINT UNSIGNED

ALTER TABLE test2 DROP PRIMARY KEY;

6.添加唯一

ALTER TABLE tb_name ADD UNIQUE [KEY|INDEX][index_name](字段名称)

age字段唯一

ALTER TABLE test ADD UNIQUE (age);

将username字段添加成唯一，同时指定索引名称

ALTER TABLE test ADD UNIQUE KEY un_username (username);

8删除索引DROP INDEX index_name

ALTER TABLE tb_name DROP INDEX index_name;

age索引删除

ALTER TABLE test DROP INDEX age;

username字段唯一索引删除

ALTER TABLE test DROP INDEX un_username;

9.添加默认约束

alter table tb_name ALTER 字段名称 SET DEFAULT 值；

username字段添加

ALTER TABLE test ALTER username SET DEFAULT ‘KING‘;

10.删除默认约束

ALTER TABLE tb_name ALTER 字段名称 DROP DEFAULT;

username删除

ALTER TABLE test ALTER username DROP DEFAULT;

11.修改表名

ALTER TABLE tb_name RENAME [TO|AS] new_name;

RENAME TABLE tb_name TO new_name;

test表命名kaikeba

ALTER TABLE test RENAME TO kaikeba;

kaikeba表命名test

ALTER TABLE kaikeba RENAME test;

test表命名ran

RENAME TABLE test TO ran;

12修改表的存储引擎

ALTER TABLE tb_name ENGINE=存储引擎名称；

ALTER TABLE test ENGINE=MyISAM;

13.修改自增长的值

ALTER TABLE tb_name AUTO_INCREMENT=值；

ran表AUTO_INCREMENT=100

ALTER TABLE ran AUTO_INCREMENT=100;

记录的操作DML（增删改）

1>插入记录的操作INSERT

INSERT [INTO] tb_name [(字段名称……)]VALUES(值|exp|)

DEFAULT……）

CREATE TABLE IF NOT EXISTS user(

id SMALLINT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL UNIQUE,

age TINYINT UNSIGNED

)ENGINE=InnoDB CHARSET=‘UTF8‘

--插入记录

INSERT INTO user VALUES(1,‘2E‘,2);

指定字段名称

INSERT user(username,age) VALUES(‘DSA‘,9);

INSERT user(id,username,age) VALUES(NULL,‘DSESA‘,9);

INSERT user(id,username,age) VALUES(DEFAULT,‘SDA‘,4+3);

一次插入多听记录

INSERT [INTO] tb_name [(字段名称……)]VALUES(值……)，(值……)

INSERT INTO user(username,age) VALUES (‘A‘,2),(‘W‘,9),(‘21‘,8);

--通过SET插入记录

INSERT [INTO] tb_name SET 字段名称=值，……；

INSERT user  SET username=‘sadfl‘,age=9;

2>更新记录UPDATE

UPDATE tb_name SET 字段名称=值|EXP|DEFAULT [WHERE 条件]

不加条件会更新表中所有记录

--user表中第一用户年龄改为45

UPDATE user SET age=45 WHERE id=1;

UPDATE user SET age=45,username=‘iopo‘ WHERE id=1;

添加默认值

ALTER TABLE user ALTER age SET DEFAULT 90;

--将user表中第三个用户的年龄改为默认值

UPDATE user SET age=DEFAULT WHERE id=3;

3>删除记录DELETE

DELETE FROM tb_name [WHERE 条件]

--删除id为1

DELETE FROM user WHERE id=1;

--删除所有记录

DELETE FROM user;

ALTER TABLE user AUTO_INCREMENT=1;

重置AUTO_INCREMENT

5>彻底清空一个表TRUNCATE,清空表时不用加条件会重置AUTO_INCREMENT,

TRUNCATE TABLE tb_name;

TRUNCATE TABLE user;

查询数据SELECT

SELECT exp[,..] FROM tb_name

[WHERE 条件]

[GROUP BY 分组[HAVING 子句对分组结果进行二次筛选]]

[ORDER BY 排序]

[LIMIT 限制显示条数]

SELECT * FROM user;

*代表所有字段

SELECT 字段名称，字段名称 FROM tb_name;

SELECT username FROM user;

SELECT username,age FROM user;

SELECT 表名.字段名称，...FROM tb_name;

SELECT user.id,user.age,user.username FROM user;

给字段起别名，给表起别名

SELECT 字段名称 [AS] 别名，...FROM tb_name [AS] 别名

SELECT id AS ‘编号‘,username AS ‘用户名‘,age AS ‘年龄‘FROM user;

别名时尽量不要省略AS

SELECT u.id,u.username,u.age FROM user AS u;

1》WHERE表达式

比较运算符

SELECT 字段名称...FROM tb_name

WHERE 条件；

比较运算符

> >= < <= = <=> != <>

--user表插入记录

INSERT user VALUES(11,‘EWRR‘,15);

INSERT user VALUES(6,‘EWR44R‘,14);

INSERT user VALUES(7,‘EW56RR‘，45);

INSERT user VALUES(8,‘EUYUYR‘,55);

INSERT user VALUES(9,‘EWRR‘,4);

INSERT user VALUES(12,‘EWXR4R‘,5);

INSERT user VALUES(4,‘EWSRR‘,9);

--用户名为‘sad‘

SELECT * FROM user WHERE username=‘EWRR‘;

SELECT * FROM user WHERE id=6;

--年龄大于18

SELECT * FROM user WHERE age>=18;

不等于

SELECT * FROM user WHERE age!=123;

SELECT * FROM user WHERE age<>123;

等于

SELECT * FROM user WHERE age<=>123;

//<=>可以检测某个值是否为空

INSERT user VALUES(36,‘test1‘,NULL);

SELECT * FROM user WHERE age<=>NULL;

SELECT * FROM user WHERE age=NULL;

--通过IS NULL 或者IS NOT NULL检测是否为空

SELECT * FROM user WHERE age IS NULL;

SELECT * FROM user WHERE age IS NOT NULL;

--年龄18-28

BETWEEN...AND或者NOT BETWEEN...AND

SELECT * FROM user WHERE age BETWEEN 18 AND 28;

SELECT * FROM user WHERE age NOT BETWEEN 18 AND 28;

IN在集合中的形式

SELECT * FROM user WHERE age IN(18 ,12,23,35,28);

SELECT * FROM user WHERE age NOT IN(18 ,12,23,35,28);

通过LIKE实现模糊查询

%：代表0个1个或多个任意字符；

—：1个任意字符

--包含张/w的用户

SELECT * FROM user WHERE username LIKE ‘%张%‘;

SELECT * FROM user WHERE username LIKE ‘%W%‘;

--以张开头

SELECT * FROM user WHERE username LIKE ‘张%‘;

查询用户名长度为3位的用户

SELECT * FROM user WHERE username LIKE ‘___‘;

SELECT * FROM user WHERE username LIKE ‘_E%‘;

逻辑运算符

and or

用户名为king并且年龄为28的信息

SELECT * FROM user WHERE username=‘king‘ AND age=28;

用户名包含k或者年龄小于50的用户

SELECT * FROM user WHERE username LIKE ‘%K%‘ OR age<50;

2》分组查询

CREATE TABLE IF NOT EXISTS department(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

depname VARCHAR(20) NOT NULL UNIQUE);

INSERT department(depname) VALUES(‘摄影部‘);

INSERT department(depname) VALUES(‘视频部‘);

INSERT department(depname) VALUES(‘教学部‘);

INSERT department(depname) VALUES(‘督导部‘);

CREATE TABLE IF NOT EXISTS employee(

id INT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

addr VARCHAR(50) NOT NULL DEFAULT ‘北京‘,

salary FLOAT(6,2) NOT NULL DEFAULT 0,

sex ENUM(‘男‘,‘女‘,‘保密‘),

depid TINYINT UNSIGNED

);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘张三‘,12,‘上海‘,6234.56,‘男‘,1);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘张三风‘,22,‘北京‘,1563.51,‘男‘,2);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘章子怡‘,14,‘云南‘,6454.14,‘女‘,1);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘丽丽‘,41,‘广州‘,1201.45,‘男‘,2);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘李成‘,52,‘深圳‘,6914.86,‘男‘,2);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘程程‘,25,‘成都‘,5631.12,‘女‘,3);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘白静‘,65,‘南阳‘,4823.45,‘男‘,4);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘程秀碧‘,48,‘广汉‘,5523.36,‘女‘,4);

按性别分组

SELECT * FROM employee GROUP BY sex;

按地址分组

SELECT * FROM employee GROUP BY addr;

按部门编号

SELECT * FROM employee GROUP BY depid;

根据性别和地址分组

SELECT * FROM employee GROUP BY sex,addr;

SELECT * FROM employee GROUP BY addr,sex;

查询结果跟顺序有关

--分组查询配合GROUP_CONCAT()

--按性别分组，得到每个组中人员的名称

SELECT * ,GROUP_CONCAT(username) FROM employee GROUP BY sex;

SELECT * ,GROUP_CONCAT(username) AS usernames FROM employee GROUP BY sex;

--配合聚合函数使用

COUNT():统计记录数目

SUM():求字段和

AVG():求字段平均值

MAX():

MIN():

--统计员工表中员工数目，以及薪水总和、最大最小值

SELECT id AS ‘编号‘,username AS ‘用户名‘,COUNT(*) AS ‘totalNUMS‘,SUM(salary) AS ‘总薪水‘

,AVG(salary) AS ‘平均薪水‘,MAX(salary) AS ‘最高薪水‘,MIN(salary) AS ‘最低薪水‘FROM employee;

SELECT id AS ‘编号‘,username AS ‘用户名‘,COUNT(*) AS ‘totalNUMS‘,SUM(salary) AS ‘总薪水‘

,AVG(salary) AS ‘平均薪水‘,MAX(salary) AS ‘最高薪水‘,MIN(salary) AS ‘最低薪水‘FROM employee\G;

COUNT(*),COUNT(字段名称):

*无论字段值是否为NULL都统计

字段名称：不统计NULL记录。

INSERT employee(username,age,addr,salary,sex,depid)

VALUES(‘程秀碧‘,NULL,‘广汉‘,5523.36,‘女‘,4);

SELECT COUNT(*) FROM employee;

SELECT COUNT(age) FROM employee;

--按性别分组，统计出每个组找那个年龄最大最小，最高薪水，每个组人数，以及平均薪水

SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total,AVG(salary) AS avg_salary FROM employee GROUP BY sex;

SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total,AVG(salary) AS avg_salary,GROUP_CONCAT(username) AS users FROM employee GROUP BY sex;

3》HAVING子句二次筛选

--按照性别分组

SELECT id,sex,COUNT(*) AS total FROM employee GROUP BY sex;

分组后组中人数大于3的有

SELECT id,sex,COUNT(*) AS total FROM employee GROUP BY sex HAVING COUNT(*)>6;

--按地址分组

SELECT id,addr,MIN(age) AS min_age FROM employee GROUP BY addr;

分组后要求组中最小人员年龄大于18；

SELECT id,addr,MIN(age) AS min_age FROM employee GROUP BY addr HAVING MIN(age)>25;

SELECT exp FROM tb_name

[WHERE 条件]

[GROUP BY 分组[HAVING对分组结果进行二次筛选]]

[ORDER BY 字段名称]

[LIMIT 限制显示条数 ]

4》对分组结果排序

ORDER BY 字段名称|位置 ASC|DESC;

--按照ID由小到大排序

SELECT * FROM department ORDER BY id ASC;

SELECT * FROM department ORDER BY id ;

默认升序，所以查询结果一样

SELECT * FROM department ORDER BY id DESC;

--按位置

SELECT * FROM department ORDER BY 1 DESC;

SELECT * FROM employee ORDER BY age DESC;

SELECT * FROM employee ORDER BY 3 DESC;

当有相同年龄时，按出现顺序进行排序

--按多个字段排序

ORDER BY 字段名称1，字段名称2

SELECT * FROM employee ORDER BY age DESC,id DESC;

SELECT * FROM employee ORDER BY id DESC,age DESC;

查询结果与字段名称的顺序有关

5》LIMIT

LIMIT 值：

代表显示前几条记录

LIMIT 偏移量，显示记录条数

实现分页的核心是通过LIMIT语句

--显示前5条

SELECT * FROM employee LIMIT 5;

SELECT * FROM employee ORDER BY id DESC LIMIT 5;

第一条记录偏移量为0

SELECT * FROM employee LIMIT 0, 5;

--下一页，显示后五条

SELECT * FROM employee LIMIT 5, 5;

SELECT * FROM employee LIMIT 10, 5;

--所有条件

SELECT *,GROUP_CONCAT(username) FROM employee WHERE id>2 GROUP BY sex HAVING COUNT(*)>6 ORDER BY age DESC,id DESC LIMIT 0,5;

子查询

1》通过IN引发的子查询

SELECT * FROM employee WHERE depid in(SELECT id FROM department);

SELECT * FROM employee WHERE depid NOT IN(SELECT id FROM department);

2》由于比较运算符引发的子查询

SELECT * FROM employee WHERE depid=(SELECT id FROM department WHERE depname=‘摄影部‘);

--

SELECT depname FROM department WHERE id IN(SELECT depid FROM employee WHERE age>25);

--摄影部年龄最小/大值

SELECT MIN(age) FROM employee WHERE depid=1;12

SELECT MAX(age) FROM employee WHERE depid=1;14

--年龄大于摄影部年龄最大的员工

SELECT * FROM employee WHERE age>(SELECT MAX(age) FROM employee WHERE depid=1);

3》EXIST(内层有结果才执行外层）

SELECT id FROM department WHERE depname=‘国防部‘;空

SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE depname=‘国防部‘);

SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE depname=‘督导部‘);

4》由ANY|SOME|ALL修饰的比较运算符

>=ANY|SOME:大于子查询中的最小值

>=ALL:大于子查询中的最大值

SELECT * FROM employee WHERE depid>ANY(SELECT id FROM department);depid>2

SELECT * FROM employee WHERE depid>SOME(SELECT id FROM department);depid>2

SELECT * FROM employee WHERE id>ALL(SELECT id FROM department);id>5

<=ANY|SOME:小于子查询中的最大值

<=ALL:小于子查询中的最小值

=ANY|SOME:相当于IN,不能写不等于

SELECT * FROM employee WHERE depid=ANY(SELECT id FROM department);全部成员

<>ALL:相当于NOT IN

SELECT * FROM employee WHERE depid<>ALL(SELECT id FROM department);空

等于：只能ANY|SOME;不等于：ALL只能。

开发中很少用子查询，效率不高，必须现执行子查询，再执行外层查询。

连接查询

连接查询：内连接+外连接

外连接：左连接+右连接

1》两表查询

--员工表中员工姓名、编号、性别、年龄、所属部门名称

SELECT e.id,e.username,e.sex,e.age,d.depname FROM employee AS e,department AS d;得到两边笛卡尔积

由于哪个桥梁连接起来的

员工表中的depid等于部门表的id

SELECT e.id,e.username,e.sex,e.age,d.depname FROM employee AS e,department AS d WHERE e.depid=d.id;

1>内连接

[INNER|CROSS] JOIN

通过ON连接条件

查询出两个或多个表都满足的结果

--员工表所属部门名称

SELECT e.id,e.username,e.sex,e.age,d.depname FROM department AS d INNER JOIN employee AS e ON e.depid=d.id;

WHERE通常用于条件塞选

--员工年龄大于25

SELECT e.id,e.username,e.sex,e.age,d.depname FROM department AS d INNER JOIN employee AS e ON e.depid=d.id WHERE e.age>25;

2》多表查询：

CREATE TABLE IF NOT EXISTS provinces(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

pname VARCHAR(10) NOT NULL UNIQUE

);

INSERT provinces(pname) VALUES(‘四川‘),(‘云南‘),(‘陕西‘),(‘广东‘);

ALTER TABLE employee ADD addr1 TINYINT UNSIGNED DEFAULT 1;

UPDATE employee SET addr1=2 WHERE id IN(2,4,6,8);

ALTER TABLE employee CHANGE addr1 pid TINYINT UNSIGNED DEFAULT 1;

UPDATE employee SET pid=2 WHERE id IN(2,4,6,8);

--查询员工所属省份

SELECT e.id,e.username,p.pname FROM employee AS e JOIN provinces AS p ON p.id=e.pid;

--部门名称、省份名称、用户的信息

SELECT e.id,e.username,p.pname,d.depname FROM employee AS e JOIN provinces AS p ON p.id=e.pid JOIN department AS d ON e.depid=d.id;

不断在后面加JOIN跟上ON条件。

SELECT e.id,e.username,p.pname,d.depname FROM employee AS e JOIN provinces AS p ON p.id=e.pid JOIN department AS d ON e.depid=d.id LIMIT 0,3;

INSERT employee(username,age,addr,salary,sex,depid,pid) VALUES(‘test5‘,25,‘北京‘,6542,‘女‘,6,9);

插入成功了，但这条数据部合法。省份只有4个，部门只有4个，没有保证记录完整性与一致性，那么就应当通过外键来实现。

外键

依赖已存在表的主键来设置外键。

作用：保证记录完整性与一致性

创建外键注意事项：

父表与子表必须使用相同的存储引擎，禁止使用临时表；

存储引擎只能为InnoDB

子表外键必须关联父表主键

外键列与参照列应具有相似的数据类型。

1》创建外键

创建表时

CREATE TABLE IF NOT EXISTS user(

id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE,

pid TINYINT UNSIGNED ,

FOREIGN KEY(pid) REFERENCES provinces(id)

);

CONSTRAINT 约束名称 FOREIGN KEY(外键列) REFERENCES 主表（字段）；

CREATE TABLE IF NOT EXISTS user1(

id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE,

pid TINYINT UNSIGNED ,

CONSTRAINT user1_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id)

);

INSERT user(username,pid) VALUES(‘DSFSA‘,1);

INSERT user(username,pid) VALUES(‘ZDX‘,2);

INSERT user(username,pid) VALUES(‘SSA‘,3);

INSERT user(username,pid) VALUES(‘HGA‘,4);

INSERT user(username,pid) VALUES(‘HGRE‘,5);省份没有id=5,插入失败

DELETE FROM provinces WHERE id=4;不成功，id=4的省份还有用户，应当先删除用户，在删除省份。

SELECT * FROM user;

DELETE FROM user WHERE pid=4;

DELETE FROM provinces WHERE id=4;

DELETE FROM provinces WHERE id=3;一样不成功，原因同id=4，有外键约束存在：

下面删除外键：

ALTER TABLE tb_name DROP FOREIGN KEY 约束名称；

--查看user约束名称

SHOW CREATE TABLE user;

--删除外键约束

ALTER TABLE user DROP FOREIGN KEY user_ibfk_1;

--现在做删除操作，成功

DELETE FROM provinces WHERE id=3;

添加外键

ALTER TABLE tb_name ADD CONSTRAINT 约束名称 FOREIGN KEY(字段） REFERENCES 主表（字段）；

ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id);不成功

DELETE FROM user WHERE pid=3;

ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id);删除pid=3的记录后添加成功

先删除子表记录，再删除父表记录

创建外键的时候可以指定一下，当我们删除父表记录时，子表应当进行什么样的操作。

CASCADE,级联

SET NULL(保证列没有非空约束)

RESTRICT

NO ACTION效果同RESTRICT

ON UPDATE|ON DELETE CASCADE|SET NULL|RESTRICT|NO ACTION

--测试CASCADE

ALTER TABLE user DROP FOREIGN KEY user_fk_provinces;

SHOW CREATE TABLE user;

ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE;

DELETE  FROM provinces WHERE id=2;实际上先删除user中的pid=2的记录，再删除provinces表中的记录。

实际开发中采用外键思想进行操作，而不是纯的物理外键

外连接

LEFT|RIGHT [OUTER] JOIN ON 条件

LEFT:显示左表中的全部记录和右表中符合条件的记录

RIGHT:显示右表中的全部记录和左表中符合条件的记录

--内连接

SELECT e.id,e.username,d.depname FROM employee AS e JOIN department AS d ON e.depid=d.id;

--外连接

SELECT e.id,e.username,d.depname FROM employee AS e LEFT JOIN department AS d ON e.depid=d.id;

当右表中没有满足条件时，NULL填充

SELECT e.id,e.username,d.depname FROM employee AS e RIGHT JOIN department AS d ON e.depid=d.id;

内连接查询两个或多个表中都符合条件的记录

INSERT department(depname) VALUES(‘测试部门‘);

SELECT e.id,e.username,d.depname FROM employee AS e RIGHT JOIN department AS d ON e.depid=d.id;

联合查询

多个表中的记录和在一起

UNION ALL，简单地将查询结果合并到一起

UNION会去掉重复记录

SELECT * FROM employee;9

SELECT * FROM user;1

SELECT username FROM user UNION ALL SELECT username FROM employee;

联合查询，字段顺序、字段数目一定要相同

SELECT id,username,age FROM employee UNION ALL SELECT id AS uid FROM user;不成功

SELECT id,username,age FROM employee UNION ALL SELECT id AS uid,username AS uname,1 FROM user;

多表更新和删除

--将employee表中的addr存为省份表中的id

UPDATE employee AS e JOIN provinces AS p ON e.pid=p.id SET e.addr=p.id;

SELECT * FROM employee;

--删除所有所在省份在省份表中省份的人

DELETE e FROM employee AS e JOIN provinces AS p ON e.pid=p.id;

多表操作，注意表和表间的连接条件

删除数据表

一张表

DROP TABLE tb_name;

多张表

DROP TABLE tb_name1,tb_name;