欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-13 23:01:35
...

修改表

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 age123;

等于

SELECT * FROM user WHERE age123;

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

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

SELECT * FROM user WHERE ageNULL;

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

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:相当于IN,不能写不等于

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

ALL:相当于NOT IN

SELECT * FROM employee WHERE depidALL(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;

相关标签: 数据表