mysql 表的增删改查_MySQL之表的增删改查

1. 创建表

create table 表名(

列名 数据类型 [约束类型] [comment '备注'], ...,

constraint 约束名 约束类型(列名)

)engine=innodb defalut charset=utf8;12345

从其他表查询几列数据生成新的表

create table 表名1 as select 列1,列2 from 表名2

2. 向表中添加数据

按列名添加一行数据

insert into 表名[(列名1,列名2...)] values(列1数据,列2数据...);

从其他表中复制数据

insert into 表名1 select 列名 from 表名2

3. 修改表中的数据

按条件修改数据

update 表名 set 列名=列值,列2名=列2值...where 选择条件

将子查询结果赋值给表中数据

update 表名 set 列名=(子查询)

4. 删除表中的数据

按条件删除指定数据

delete from 表名 where 选择条件

销毁整张表或约束

drop table 表名;

drop index 约束名;

5. 修改表的结构

添加列

alter table 表名 add 列名 数据类型;

添加约束

alter table 表名 add [constraint 约束名] 约束类型(列名);

约束名

添加语法

撤销语法

外键约束

alter table 表名 add [constraint 约束名] foreign key(外键列) references 主键表名(主键列);

alter table 表名 drop foreign key 约束名

默认约束

alter table 表名 alter 列名 set default ‘默认值’

alter table 表名 alter 列名 drop default

检查约束

alter table 表名 add [CONSTRAINT 约束名] check (列名10)

alter table 表名 drop check 约束名

唯一约束

alter table 表名 add [CONSTRAINT 约束名] unique (列名)

alter table 表名 drop index 约束名

主键约束

alter table 表名 add [CONSTRAINT 约束名] primary key (列名)

alter table 表名 drop primary key

3. 修改表名

alter table 表名 rename 新表名

4. 修改列的字段名

alter table 表名 change cloumn 列名 新列名 新列数据类型

5. 修改列的数据类型

alter table 表名 alter column 列名 数据类型;

6. 添加一列到表中

alter table 表名 add 列名 数据类型;

7. 删除表中一列

alter table 表名 drop column 列名

7. 查看表的结构

desc 表名

2. 表的查询

1. 查询的基本语法

select 列名1 [as] [列别名],列名2

from 表1 [as] [表别名]

[left] join 表2 on 连接条件

[left] join 表3 on 连接条件 where 检索条件(不可用统计函数) group by 分组列1,列2

having 检索条件(可用统计函数min,max,sum,avg) order by 排序列 [desc降序]

limit 起始行号,显示行数123456789

2. 查询分类

专有名词

含义

选择

选择不同行

投影

选择不同列

连接

多表联合查询

3. 连接分类

连接名称

语法

含义

内部连接

a join b on …

只显示符合连接条件记录

外部连接

a left join b on …

a表显示不符合条件记录

自身连接

a join a on …

a表自身列存在关联

全外连接

a full outer join b on …

ab表都显示不符合条件记录

交叉连接

a cross join b on …

a*b产生笛卡尔积,生成a行*b行记录

自然连接

a natural join b

自动判断连接条件

4. 子查询

使用子查询的目的

数据库连接耗时长,避免多次连接数据库

尽可能减少次数

提升数据库性能

能用连接解决时,不使用子查询

无关子查询

常用于where/having后用于约束父查询的条件,先执行子查询语句一次,父子查询间字段无关

select * from emp where sal > (select avg(sal) from emp)

用于select后直接输出列,可以添加别名

select ename,(select avg(sal) from emp) as asal from emp

相关子查询

常用于where后,子查询返回字段与父查询字段相关联,父查询每次要执行子查询中的条件一次

select * from emp f where sal > (select avg(sal) from emp where deptno=f.deptno)

表示比与自己所在部门的平均工资相比更高的记录被选择

嵌套子查询

常用于from后,把子查询返回结果看作一个表与父查询的表做连接

select * from emp a join (select deptno from emp) b on a.deptno = b.deptno

多列查询

表示列1,列2分别与子查询返回的第一列,第二列值相同的记录被选择

select * from emp where (列1名,列2名) in (子查询)

多行查询

字段 in(子查询) 与任意返回值相同

字段

字段

select * from emp where 列名 in/<=any/<=all (子查询)

当子查询出现null时

子查询返回null会造成比对时结果全部为null,任意字段与null比对后均返回null

(select comm from emp where comm is not null)

去除子查询返回结果集中的null值