表的增删改

 


  1. 创建表

 

create table 表名(

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

...,

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

 )engine=innodb defalut charset=utf8;

 

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

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 表名 rename 新表名 

 

  • 修改列的字段名 

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

 

  • 修改列的数据类型 

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

 

  • 添加一列到表中 

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

 

  • 删除表中一列 

alter table 表名 drop column 列名 

 

  • 查看表的结构 

desc 表名

 


 

表的查询

 


  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. 连接分类

 

 


  4. 子查询

 

4.1使用子查询的目的 

 

  • 数据库连接耗时长,避免多次连接数据库
  • 尽可能减少次数
  • 提升数据库性能
  • 能用连接解决时,不使用子查询

 

4.2无关子查询 

 

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

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

 

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

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

 

4.3相关子查询 

 

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

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

 

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

 

4.4嵌套子查询 

 

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

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

 

4.5多列查询 

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

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

 

4.6多行查询 

 

  • 字段 in(子查询) 与任意返回值相同
  • 字段 <或或= any(子查询) 比最小返回值大或比最大返回值小或同in
  • 字段 <或或= all(子查询) 比最小返回值小或比最大返回值大或完全相同 

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

 

4.7当子查询出现null时 

 

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

(select comm from emp where comm is not null)

 

  • 去除子查询返回结果集中的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开始添加索引号