MySQL—常用查询语法

1.元数据查询

show databases;
show tables;
show create table table_name;

2.DDL语句

• 建库建表

//建库
cretae database db_name;
//建表
 create table table_name(field1 field_type comment 'describe1', field2 field_type comment 'describe2');
//复制表
create table table_name select * from table_name2;

• 表结构修改(alter)

//-------添加字段(alter table ... add ...)
			
//末尾追加字段
alter tabe table_name add field field_type;			
//首位添加字段
alter tabe table_name add field field_type first;			
//field2字段后添加字段
alter tabe table_name add field1 field_type after field2;

		
//--------修改字段(alter table ...modify|change ...)
			
//修改字段类型及位置
alter table table_name modify field field_type [after field2];
//修改字段名称和类型
alter table table_name change old_field new_field field_type;

		
//---------销毁字段(alter table ... drop ...)
alter table table_name drop field;

• 表库销毁(drop)

//销毁字段(alter table ... drop ...)
alter table table_name drop field;
//表库销毁(drop)
库销毁: drop database db_name;
表销毁: drop table table_name;
表重命名: rename table old_name to new_name;

3.DML语句

• insert(插入数据)

//单条插入
insert into table_name[(field1,field2)] values(v1,v2);
//批量插入
insert into table_name[(field1,field2)] values(v1,v2),(v3,v4);
//复制表数据插入
insert into table_name select * from table_name2;

• delete(删除数据)

//删除数据
delete from table_name where ...
//清空表
truncate table table_name;

• update(修改数据)

//更新字段值
update table_name set field=v1, field=v2 where ...

4.​​​​​DQL语句

• 语句: select ... from ...  where ... group by ...  having ... order by ...  limit ...
  • sql逻辑执行顺序
    • from: 数据源,即来自那个表
    • on: join的关联逻辑
    • join: 连接表源
    • where: 过滤表数据
    • group by: 根据字段值分组
    • having: 根据分组后的聚合值过滤结果集
    • select: 查询
    • order by: 对结果集排序
    • limit: 截断结果集
  • 查询其他关键字
    • distinct: 去重
    • between: 在某个范围之间
    • in: 在某个集合中
    • union: 连接两个结果集(去重)
    • union all: 连接两个结果集
    • is not null: 匹配非空null值
    • and: 同时满足
    • or: 满足其一
• 模糊查询: %通配符，_占位符

select * from table_name where field like '%tree%'

• 关联查询

//内连接
select * from table_name1 t1, table_name2 t2 where t1.field = t2.field

select * from table_name1 t1 inner join table_name2 t2 on t1.field = t2.field where ...

//using(field)关联字段名必须相同
select * from table_name1 t1 inner join table_name2 using(field) where ...
 

外连接
select * from table_name t1 left join table_name t2 on t1.field = t2.field where ...

• 嵌套查询

//in: 子查询结果集与主查询结果集做笛卡尔积后根据条件筛选
select * from table_name where field in (select field from table_name2)
//exists: 主查询遍历表每条记录去对子查询进行匹配，匹配上立即返回
select * from table_name t1 where exists (select field from table_name2 t2 where t1.field = t2.field)) )

5.数据完整性

• 主键(primary key)

//创建表时指定主键
create table table_name(field1 field_type, field2 field_type, primary key(field) [auto_increment])
//添加主键约束
alter table table_name add primary key [PK_name] (field);
//添加主键约束
alter table table_name modify field field_type primary key [auto_increment];
//删除主键(主键自增先删除自增)
alter table table_name drop primary key;

• 唯一键(unique)

//创建表时指定唯一约束
create table table_name(field1 field_type unique, field2 field_type )
//添加唯一键约束
alter table table_name add unique [UK_name] (field);
//添加唯一键约束
alter table table_name modify field field_type unique;
//删除唯一约束
alter table table_name drop index UK_name;

• 外键约束(foregin key)

//----------添加外键约束
//创建表时指定外键
create table table_name(field1 field_typec, field2 field_type, cconstraint FK_field foreign key(field1) references ref_table(field2));
//添加外键
alter table table_name add foreign key [FK_name] (field1) references ref_table(field2);

//-----------删除外键约束
alter table table_name drop foreign key FK_name;

• 非空约束(not null)

​​​​​​

//创建表指定非空约束
create table table_name(field1 field_type not null, field2 field_type)
//添加非空约束
alter table table_name modify field field_type not null;
//删除非空约束
alter table table_name modify field field_type;

• 区别
  • 主键一张表只能有一个，可以作为其他表的外键，当然也可以创建联合主键，主键字段唯一，不可以为null
  • 唯一键时针对列的，一个表可以有多个唯一键，唯一键的列值不重复

6.存储过程(类似于开发语言中的方法)

• 定义存储过程

// 参数int: 接受的输入参数, out：将查询结果通过into关键字赋给此参数，类似于返回结果
create procedure procedure_test(in in_id int, out out_name varchar(32))
begin
    select name from person where id = in_id
    into out_name;
end;

• 调用存储过程

//参数列表必须对应, 输入参数传入具体的值, 对应的输出参数传入变量接收返回结果，且变量必须以@开头
call procedure_test(1, @name);
// 通过输处参数获取存储过程返回的结果
select @name;

• 销毁存储过程

drop procedure procedure_name;

7.触发器

• 定义触发器

// 触发器类型: insert,delete,update,一个触发器只能定义一种类型
// 触发时机: after,before,更新操作前或后
// NEW可代替虚拟表访问被插入的行.OLD可代替被虚拟表访问被删除的行，update使用OLD虚拟表访问更新前的数据，NEW虚拟表访问更新后的数据
create trigger trigger_name after|before insert|delete|update on table_name [for each row] select ...

//案例
create trigger trigger_test after insert on person for each row select new.id into @out

• 销毁触发器

drop trigger trigger_name;