mysql的表操作

表是建⽴在数据库中的数据结构，是⼀类数据的存储集。

对表中数据的操作⼀般分为四类, 常记做 "CURD":

C: 创建（Create）

U: 更新（Update）

R: 读取（Retrieve）

D: 删除（Delete）

表的创建

create table [if not exists] `表名称` (字段1 类型 [属性],字段2 类型 [属性],...) engine='myisam' charset='编码格式';

字符集如果不指定, 默认继承库的字符集. engine 默认innodb

查询

• 查看所有的表

  show tables;

  选择数据库后才能查看这个数据库的所有的表

• 显示表的结构

  desc `表的名称`;

• 查询表里的数据

  select * from `表名`;
  select * from `表名` where `字段`>80;
  select `字段1`,`字段2` from `表名` where `字段`>80;

删除

1.删除表

drop table [if exists] `表名称`;

删除表必须在数据库中删除

2.删除字段

alter table `表名` drop `字段名`;

3.删除表数据

-- 删除表中的所有数据 (逐⾏删除)
delete from `表名`;
-- 清空全表 (⼀次性整表删除)
truncate `表名`;
-- 使⽤ where 修改满⾜条件的⾏
delete from `表名` where `字段`='值';
delete from `表名` where `字段` in (1,2,3,4);

修改

1.修改表

-- 修改表的名称
alter table `表的名称` rename `新的表名称`;
-- 修改表的引擎
alter table `表名称` engine=innodb/myisam;
-- 移动表到指定的数据库
alter table `表名称` rename [to] 数据库名称.新表名称;

2.修改表里的字段

-- 修改字段名称
alter table `表名称` change `旧字段名` `新的字段名` 类型 [属性];
-- 修改字段属性
alter table `表名称` modify `字段名` 数据类型 [属性];
-- 修改字段的位置
alter table `表名` change `旧字段名` `新的字段名` 类型 after `字段名`;

3.修改表数据

-- 修改全表数据
update `表名称` set `字段1`='值',`字段2`='值';
-- 使⽤ where 修改满⾜条件的⾏
-- where 类似于 if 条件, 只执⾏返回结果为 True 的语句
update `表名称` set `字段1`='值',`字段2`='值' where `字段`='值' and `字段`='值';

增加

1.增加表字段

alter table `表名` add `字段名` 类型 [属性][,add `字段名` 类型 [属性]];
alter table `表名` add `字段名` 类型 [属性] first;
alter table `表名` add `字段名` 类型 [属性] after `字段名`;

2.增加表数据

--一次插入一行
insert into `表名` set `字段名`=值,`字段名`=值;
--按照指定字段一次插入多行
insert into `表名` (`字段1`,`字段3`,...) values ('值','值',...),('值','值',...),...;
--一次插入多行
insert into `表名` values (null,'值','值',...),(null,'值','值',...),...;

其中的 into 在 MySQL 数据库中可以省略, 但在某些数据库中必须要有。

3.增加表

create table `表名`(字段1 数据类型 [属性],字段1 数据类型 [属性],...) engine='myisam' charset='编码格式';

 

复制表

1.复制表结构和数据，但是字段属性会丢失

create table `新表名` select * from `表名1`;

2.复制表的结构

create table `新表名` like `表名1`;

3.复制表的数据

insert into `表名` select * from `原表名`;

 

多表查询

UNION联合查询 (并集)

UNION 操作符⽤于合并两个或多个 SELECT 语句的结果集。

union要求:

1. 两边 select 语句的字段数必须⼀样

2. 两边可以具有不同数据类型的字段

3. 字段名默认按照左边的表来设置

SELECT column_name(s) FROM table1 
UNION 
SELECT column_name(s) FROM table2;

INNER JOIN : 内连接 (交集)

INNER JOIN 关键字在表中存在⾄少⼀个匹配时返回⾏。

SELECT 字段
FROM 表1 INNER JOIN 表2
ON 表1.字段=表2.字段;
SELECT 字段
FROM 表1 INNER JOIN 表2
ON using(字段);
-- 或：
SELECT column_name(s)
FROM table1 JOIN table2
ON table1.column_name=table2.column_name;
​
SELECT column_name(s)
FROM table1 JOIN table2
ON using(column_name);

LEFT JOIN : 左连接

LEFT JOIN 关键字从左表（table1）返回所有的⾏，即使右表（table2）中没有匹配。如果右表中 没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
-- 或：
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

RIGHT JOIN : 右连接

RIGHT JOIN 关键字从右表（table2）返回所有的⾏，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
-- 或：
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

FULL JOIN : 全连接

FULL JOIN 的连接⽅式是只要左表（table1）和右表（table2）其中⼀个表中存在匹配，则返回 ⾏。相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

特别注意: MySQL 并不⽀持 full join

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

⼦查询

查询的语句中还有⼀个查询

select name from student where id in (select id from score where math > 10);

视图表

• 视图表的特点

  视图是数据的特定⼦集，是从其他表⾥提取出数据⽽形成的虚拟表，或者说临时表。

  创建视图表依赖⼀个查询。

  视图是永远不会⾃⼰消失的除⾮⼿动删除它。

  视图有时会对提⾼效率有帮助。临时表不会对性能有帮助，是资源消耗者。

  视图⼀般随该数据库存放在⼀起，临时表永远都是在 tempdb ⾥的。

  视图适合于多表连接浏览时使⽤；不适合增、删、改，这样可以提⾼执⾏效率。

  ⼀般视图表的名称以 v_ 为前缀，⽤来与正常表进⾏区分。

  对原表的修改会影响到视图中的数据。

• 创建视图

  • 语法: create view 视图名 as 查询语句

  create view v_user_score as
  select a.id, a.name, b.math, b.english
  from student a inner join score b on a.id=b order by id;
  -- 查询
  select * from v_user_score;
  -- 删除
  drop view v_user_score;