MySQL语句大全：数据库基础知识篇

一、符号解释

#表示注释，若前面有文字空三格

[]表示可省略

<>表示不可省略

|表示或者

*表示所有属性

""表示引用

二、登录MySQL

三、数据定义

1.数据库的定义和删除

create database 库名;

use 库名;   #选择需要操作的数据库，数据库不能改名！！！

drop database 库名;

show databases;   #展示所有的数据库

2.基本表的定义、删除和修改

1.创建表

create table 表名(
列名 数据类型 [not null] [default 默认值],

列名 数据类型 [not null] [default 默认值]   #最后一行无论内容是什么省略符号,
);

show tables;   #展示当前数据库所有的表

desc [ribe] 表名;   #显示表结构

2.利用子查询创建表
create table 新表名 select 列名 from 原表名;   #新表包括原表的记录和结构

3.修改表的结构

alter table 表名
   add [column] 列名 数据类型;   #alter…add…只能新增加一个列

   modify [column] 列名 新数据类型 [default 新默认值];   #alter…modify…只能修改一个列的数据类型
   change [column] 旧列名 新列名 数据类型;   #数据类型不能为空
   drop [column] 列名;   #alter…drop…只能删除一个列，无法恢复

4.截断表和删除表
truncate table 表名;   #只删除记录，不删除表结构

drop table 表名;   #既删除记录，又删除表结构

四、数据查询

1.select语句的基本语法

select [[all] | distinct] <* |列名 [[as] 别名]>   #默认all即输出包括重复记录

from 表名[[as] 别名]

where 行条件表达式

group by 列名 having 行条件表达式   #被分组列名select中必须有！

order by 列名 [desc]   #默认升序,desc是降序，写在最后！

2.常见的比较运算符

算术比较判断：<，<=，>，>=，!=或<>，=

逻辑比较判断：not，and，or

之间判断：[not] between 小值 and 大值

字符串模糊判断：[not] like 匹配模式   #_表示任意一个字符，%表示0个或任意多个字符

空值判断：is [not] null

之内判断：[not] in

3.聚合函数

count()，sum()，avg()，max()，min()

#聚合函数只能出现在select查询列、order by字句、having by字句中

#count(*)返回NULL和非NULL值的行数，count(列名)返回非NULL值的行数，count(distinct 列名)返回非NULL且唯一值的行数

4.连接查询

内连接：from 表1 inner join 表2 on 表1.列 = 表2.列   #on指定连接条件

左连接：from 表1 left outer join 表2 on 表1.列 = 表2.列

右连接：from 表1 right outer join 表2 on 表1.列 = 表2.列

#内连接也可以写成：from 表1,表2 where 表1.列 = 表2.列，where指定连接条件

5.子查询

用于 WHERE 子句：根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。常见的多值比较运算符如[not] in、[not] exists、any、all

用于 FROM 子句：一般返回多行多列数据，相当于返回一张临时表

6.∪、∩、−的查询结果

select 语句1

union [all]   #联合

select 语句2;

select 语句1

interset   #相交

select 语句2;

select 语句1

interset   #相减

select 语句2;

#集合运算中select查询结果的数目和数据类型必须相匹配

#union默认去除重复记录并按默认升序排序，union all不会去除重复记录且不会排序

五.数据维护

1.插入数据

insert into 表名[(列名…)] values(值1)[,(值2)…];

#列名和值的数据必须在数目和数据类型上相匹配

#省略"(列名…)"即表示为所有列插入数据

#insert into…values…可以插入多条记录

insert into 表名[(列名…)] select 语句;   #利用子查询插入数据

2.更新数据

update 表名 set 列名 = 值[,列名 = 值…] [where <条件>];

#没有where子句的限定条件即表示更新整个表的数据

#"set sql_safe_updates = 0;"后方可在非主键条件下执行update和delete命令

3.删除数据

delete from 表名 [where <条件>];

#没有where子句的限定条件即表示删除整个表的数据

六.索引和视图

1.索引

1.创建索引

create [unique] index 索引名 on 表名(列名[,列名…]);

2.查看索引

show index from 表名;

3.删除索引

drop index 索引名 on 表名;

2.视图

1.创建视图

create [or replace] view 视图名[别名[,别名]…]

as

select 语句

[with check option];

#or replace：如果视图已存在则重建该视图

#别名：视图产生的列的别名

#with check option：用视图插入或修改的数据必须满足select语句的约束条件

2.修改视图

alter view 视图名[别名[,别名]…]

as

select 语句

[with check option];

3.删除视图

drop view 视图名[,视图名]…;

七.数据库编程

1.控制语句

1.条件判断语句

1.if语句

if <条件> then

   语句1;

[else

   语句2;]

end if;

if <条件1> then

   语句1;

elseif <条件1> then

   语句2;

…

else

   语句n;

end if;

2.case语句

case <表达式>

   when <表达式值1> then 语句1;

   when <表达式值2> then 语句1;

…

   when <表达式值n> then 语句n;

   [else 语句n+1;]

end;

case

   when <条件1> then 语句1;

   when <条件2> then 语句1;

…

   when <条件n> then 语句n;

   else 语句n+1;

end;

2.循环语句

1.loop循环

标签:loop

   语句块;

   if <条件表达式> then

      leave 标签;

   end if;

end loop;

2.while循环

while <条件表达式> do

   语句块;

end while;

3.repeat循环

repeat

   语句块;

   until <条件表达式>

end repeat;

#先循环再判断：loop、repeat，条件为真循环结束

#先判断再循环：while，条件为真循环继续

2.存储函数

1.创建存储函数

create function 函数名(参数名 数据类型[,…])

returns 数据类型

begin

   函数体；

   return语句;

end

2.调用存储函数

select 函数名(参数值[,…]);

3.删除存储函数

drop function 函数名;

3.存储过程

1.创建存储过程

create procedure 存储过程名(

[in | out | inout] 参数1 数据类型,

…

[in | out | inout] 参数n 数据类型

)

begin

   过程体;

end

2.调用存储过程

call 存储过程名();

3.删除存储过程

drop procedure 存储过程名;

4.游标

1.声明游标

declare 游标名 cursor for select 语句;

2.打开游标

open 游标名;

3.提取数据

fetch 游标名 into 变量名1[,变量名2…];

4.关闭游标

close 游标名;

5.异常处理

declare continue | exit handle for 异常 异常处理;

6.触发器

create trigger 触发器名

   before | after

   select | delete | update

   on 表名

   for each row

   <触发体>;

八.数据库完整性

1.实体完整性

1.创建主键

1.形式1

create table 表名(
列名 数据类型 [constraint 主键名] primary key
);

2.形式2

create table 表名(
列名1 数据类型,

…

列名n 数据类型,

[constraint 主键名] primary key(列名[,列名…])
);

#值唯一且不为空值

#如果省略"constraint 主键名"，系统会随机生成主键名

2.删除主键

alter table 表名 drop primary key;

3.添加主键

alter table 表名 add [constraint 主键名] primary key(列名[,列名…]);

4.创建唯一约束

create table 表名(
列名1 数据类型,

…

列名n 数据类型,

[constraint 唯一约束名] unique(列名)
);

5.删除唯一约束

alter table 表名 drop index 唯一约束名;

6.添加唯一约束

alter table 表名 add [constraint 唯一约束名] unique(列名);

2.参照完整性

1.创建外键

create table 表名(
列名1 数据类型,

…

列名n 数据类型,

[constraint 外键名] foreign key(列名) references 主表名(列名) [on update | delete | cascade]
);

2.删除外键

alter table 表名 drop foreign key 外键名;

3.添加外键

alter table 表名

add [constraint 外键名] foreign key(列名) references 主表名(列名)

[on update | delete | cascade];

3.用户定义完整性

1.创建检查约束

1.形式1

create table 表名(
列名 数据类型 [constraint 检查约束名] check(条件表达式)
);

2.形式2

create table 表名(
列名1 数据类型,

…

列名n 数据类型,

[constraint 检查约束名] check(条件表达式)
);

2.添加检查约束

alter table 表名 add [constraint 检查约束名] check(条件表达式);