MySQL常用语句

MySQL常用语句

建库–create database 库名;
建表–create table 表名(字段名 字段类型 约束, 字段名 字段类型 约束, 字段名 字段类型 约束);

- 更改表结构

改表名– alter table taboldname rename to newname;
改类型 alter table tabname modify colname coltype;
改列 alter table tabname change oldcolname newcolname coltype;
添加一列 alter table tabname add newcolname coltype first | after colname; 不写默认添加在最后
删除一列 alter table tabname drop colname;
修改列的位置 alter table tabname modify colname coltype first|after xxx;

• 添加外键的语法
  alter table 从表名 add constraint 约束名 foreign key(字段–外键) references 主表(字段–主键)
• 删除外键的语法
  alter table 表名 drop foreign key 外键名
• 添加复合主键–由多列组合而成的主键
  primary key(字段名，字段名)

- 处理数据的基本操作CURD

• 数据添加–insert into 表名 values(值列表);
  1）值列表必须和列举出的字段对应，个数类型对应
  2）insert into 表名 values(值列表1),(值列表2),(值列表3);

• 插入多条记录的方法
  1）insert into 表名 values (值列表1),(值列表2),(值列表3)；
  2） create table 表名 as select… 数据表不存在，语句执行后会创建
  3）insert into 表名 select …:插入表中字段必须和查询的字段保持一致

• 数据删除
  delete from 表名 where 条件
  注意：
  1）如果删除父表中的记录，必须得看子表中是否有记录引用，没有引用的数据才可以删除
  2）如果delete 后面没有条件，就会删除表中的所有的记录
  3）truncate table 表名-- 属于 ddl
  功能相当于 delete from 表名 会删除表中的所有的记录
  区别：
  delete 删除的数据可以恢复，不会清除主键的自增量
  truncate 删除的数据不能恢复,主键自增从1开始，不能用在有主外键约束的关系的表中

• 数据更新
  update 表名 set 列=值,列=值… where 条件

• 数据查询
  1）select … from 表名where 条件[多条件用and 或者 or 连接]
  2）select 聚合函数 from tabname where 条件 [多条件用and 或者 or 链接] group by…having…order by 排序字段1,排序字段2 limit …;
  3）获取所有行与列–SELECT * FROM 表名;
  4）获取部分列–select 列名 as 别名，列名 as 别名 from 表名
  5）查询的表起别名–select 表别名.列名 别名 from 表名 表别名
  6）查询多个条件 列 > 值 and 列 < 值 相当于 列 between 值and值
  7）查询多个条件 列<值 or 列 >值 相当于 列 not between 值and值
  8）列等于参数中的任意值，列 in (值1，值2，值3)

• 空/非空查询
  列 is null 或者 列 =’’
  列 is not null 或者 列 != ‘’
  区别：
  列 != ‘’ 包括列 is not null
  列 is not null 结果–空字符串不是空

• 模糊查询—like
  1）包含a ---- 字段名 like ‘%a%’
  2）查看a开头的 ，匹配任意长度 字段名 like ‘a%’
  匹配4个长度a___
  一个长度_

• 排序–order by
  asc：从小到大（可以省略）
  desc：从大到小

• 查看前10条记录limit 0,10
  查看10条后的10条记录limit 10,10
  查看20条后的10条记录 limit 20,10
  每次看num条记录limit (n-1)*num,num;

统计分析-五大聚合函数

count–统计个数
sum–求和
avg–平均
max–最大值
min–最小值
共同点：
1）都会忽略null值
2）聚合函数返回的是单行单列的结果
3）使用聚合后，前面不能添加其他的列，所添加的列必须在group by 子句中

分组查询

• group by 要和聚合函数结合一起使用
• having 用于对分组后的记录再次过滤筛选（where 过滤筛选满足条件的记录）

单表查询

• 去除重复distinct

多表查询

- 连接查询

• 全连接–返回的是两张表的笛卡儿积
  select *from 表1，表2；
• 内连接–内关联 返回的是两张表相关联的数据 inner join
  select … from tab1 inner join on 关联的条件 where …
• 外连接 outer join
  1）左外链接 left join 展示左边表的所有信息，即使没有右边表的信息需要展示，也要为null填充
  2）右外链接 right join 以右边表为基准，返回右边表中的所有的数据
  select … from tab1 left join |right join tab2 on 关联的条件 where …
• 隐式连接
  select *from 表1，表2 where 关联条件

- 子查询

• select 表1.字段，（select 表2.字段 from 表2 where 关联的条件 ）from 表1 where 其他条件

• 将查询的结果当成一个虚拟表来查询
  select * from 表1,(子查询) 表别名 where 关联条件

合并查询

• 将两个表的数据按行合并在一起，两个表重复的数据只保留一个
  SELECT * FROM tb1 UNION SELECT * FROM tb2;

• 将两个表的数据按行合并在一起并保留重复行
  SELECT * FROM tb1 UNION ALL SELECT * FROM tb2;

查询关键字

- EXISTS
1）当子查询返回结果的时候，会进行外部的查询， 如果子查询没有结果，外层查询不进行
SELECT * FROM 主表 WHERE EXISTS (SELECT * FROM 子表WHERE 条件);
2）not exists 没有结果才进行外部查询

- case when 等值判断
select 字段 case 字段 when 值 then 返回值 when 值 then 返回值 else 其他返回值 end from 表

- any 任意

-条件只要满足子表任意一个数

- all 所有

–条件必须满足子表中所有数

- regexp 匹配正则表达式

1）以s开头 regexp ‘^s’
2）以n结尾 regexp ‘n$’
3）J后面有任意一个字母 regexp ‘J.hn’
4）J后面有任意个字母 regexp ‘J*hn’
5）开头是任意字母，第二字母是e的 regexp ‘^.e+’
6）手机号以13/15/17/18开头，regex ‘^1[3578].{9}’

[3578]表示第二位的数字为3578中的任意一个