Mysql常用的增删改查语法

1. 表的增删改

1. 创建表

create table 表名(
 列名 数据类型 [约束类型] [comment '备注'],
 ...,
 constraint 约束名 约束类型(列名)  
 )engine=innodb defalut charset=uft8;

从其他表查询几列数据生成新的表
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 起始行号,显示行数

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 …	ab产生笛卡尔积,生成a行b行记录
自然连接	a natural join b	自动判断连接条件

4. 子查询

使用子查询的目的
1. 数据库连接耗时长,避免多次连接数据库
2. 尽可能减少次数
3. 提升数据库性能
4. 能用连接解决时,不使用子查询
无关子查询
1. 常用于where/having后用于约束父查询的条件,先执行子查询语句一次,父子查询间字段无关
  select * from emp where sal > (select avg(sal) from emp)
2. 用于select后直接输出列,可以添加别名
  select ename,(select avg(sal) from emp) as asal from emp
相关子查询
1. 常用于where后,子查询返回字段与父查询字段相关联,父查询每次要执行子查询中的条件一次
  select * from emp f where sal > (select avg(sal) from emp where deptno=f.deptno)
2. 表示比与自己所在部门的平均工资相比更高的记录被选择
嵌套子查询
1. 常用于from后,把子查询返回结果看作一个表与父查询的表做连接
  select * from emp a join (select deptno from emp) b on a.deptno = b.deptno
多列查询
1. 表示列1,列2分别与子查询返回的第一列,第二列值相同的记录被选择
  select * from emp where (列1名,列2名) in (子查询)
多行查询
1. 字段 in(子查询) 与任意返回值相同
2. 字段 <或或= any(子查询) 比最小返回值大或比最大返回值小或同in
3. 字段 <或或= all(子查询) 比最小返回值小或比最大返回值大或完全相同
  select * from emp where 列名 in/<=any/<=all (子查询)
当子查询出现null时
1. 子查询返回null会造成比对时结果全部为null,任意字段与null比对后均返回null
  (select comm from emp where comm is not null)
2. 去除子查询返回结果集中的null值

5. 纵向合并union

无all:一行记录
有all:28行相同记录
合并列仅限两列数据类型相同时,mysql环境下不同数据类型也可以合并但不正规

select 1,2 from emp union [all] select 1,2 from emp

6. mysql分页函数limit

从x+1行开始向下显示d行记录,放在select子句最后使用
limit x,d
从第3行显示到第8行结束
select * from emp limit 2,5

7. 索引

主键自增长用作索引时,删除的记录会被记住索引号,新添加的记录将跳过删除的索引号,为其恢复记录保留表的空间,也可在添加新记录时指定索引号,但在重启mysql服务后删除的索引失效
如插入主键值1,2,3,删除2,3行,再添加记录将从4开始添加索引号

秒客网