目录Contents
SQL命令模板
1. 引言
MySQL是通过SQL来对数据库进行增删查改等操作的。SQL按照其功能可以划分为以下四种：
- DQL：数据查询语言，主要用来查询表中的数据，主要有select；
- DML：数据操作语言，主要用对表中的数据进行增，删与改，主要有insert，delete和update；
- DDL：数据定义语言，主要用来对数据表进行操作，主要有create，drop与alter；
- DCL：数据控制语言，用来授予或回收访问数据库的权限，并控制数据库操纵事物发生的时间及效果，对数据库实现实时监视等，主要有grant，rollback，commit。
2. 数据查询命令
2.1 简单查询：
查询一个字段或多个字段
select <field_name1, field_name2, ...> from <table_name>;
- field_name：字段名；
- table_name：表名。
2.2. 条件查询：
查询符合条件字段
select <field_name1, field_name2, ...> from <table_name> where <conditions>;
- conditions：条件式，SQL条件式与Python大致相同(_表示一个字符，%表示一个字符串，如K%表示以“K开头”的字符串，使用’'取消转义)。
2.3. 排序查询：
按照排序方式查询字段
select <field_name1, field_name2, ...> from <table_name> order by <field_namei> <condition1>, <field_namej> <condition2>;
- field_namei：首先按照字段field_namei进行排序；
- field_namej：若field_namei的值相等，则按照字段field_namej进行排序；
- condition：若为‘asc’，为升序，并且默认为升序排序；若为’desc’，则为降序排序。
2.4. 分组查询：
先对字段进行分组，再对每一组进行查询：
select <field_name1, field_name2, ...> from <table_name> group by <field_namei>, <field_namej> ;
分组查询中，select后面只能跟参加分组的字段以及分组函数。
select <field_name1, field_name2, ...> from <table_name> group by <field_namei>, <field_namej> having <condition>;
使用having可以对分组进一步过滤，having不能单独使用，必须结合group by一起使用。
2.5. 综合查询：
找出符合条件的字段，并按照排序方式查询
select <field_name1, field_name2, ...>
from <table_name>
where <conditions>
order by <field_namei> <condition1>, <fieldnamej> <condition2>;
找出分组的字段，并按照排序方式查询
select <field_name1, field_name2, ...>
from <table_name>
where <conditions>
group by <field_namei>, <field_namej>
order by <field_nameii> <condition1>, <fieldnamejj> <condition2>;
注意上面语句的执行顺序为：from - - > where - - > group by - - > select - - > order by
2.6. 子查询(嵌套查询)
select <select ...> from <select ...> where <select ...>
2.7. 连接查询
从一张表中单独查询，称为单表查询。多张表联合起来查询，称为连接查询。其中表连接的方式可以分为内连接与外连接。内连接是指仅连接两张表对应值相等的部分，而外连接则把两张表中没有对应的部分也连接在结果中。内连接又可以分为等值连接、非等值连接与自连接，而外连接可以分为左外连接与右外连接。
在连接查询中需要进行条件约束，如果没有条件约束时会发生笛卡尔积现象，即查询结果是两张表查询数量的乘积。如：
select <table_name1.field_name1>, <table_name2.field_name2> from <table_name1>, <table_name2>;
-
内连接之等值连接与非等值连接
select <table_name1.field_name1>, <table_name2.field_name2> from <table_name1> inner join <table_name2> on <condition: table_name1.field_namei =\<\>\<>\... table_name1.field_namej> // where <conditions>- from table_name1 inner join table_name2：table_name1与table_name2内连接，其中inner可以省略；
- on condition：两个表连接的条件；
- where conditions：筛选条件。
-
内连接之自连接
把一张表看作两张表，只不过别名不同。 -
外连接
select <table_name1.field_name1>, <table_name2.field_name2> from <table_name1> right\left outter join <table_name2> on <condition: table_name1.field_namei =\<\>\<>\... table_name1.field_namej> // where <conditions>- 右连接：把关键字右边的表指定字段所有的项全部查询出来，左连接同理。
-
多表连接
select <table_name1.field_name1>, <table_name2.field_name2>,... from <table_name1> <inner\outter> join <table_name2> on <condition: table_name1 and table_name2> <inner\outter> join <table_name3> on <condition: table_name1 and table_name3> ...
2.8. 其它语法
-
union：把不同select查询的结果连接在一起，如
select <field_name1, field_name2, ...> from <table_name> where <condition1> union select <field_name1, field_name2, ...> from <table_name> where <condition2>union的效率比表的效率要高，因为表连接匹配的次数满足笛卡尔积，而union把匹配的乘法变成了匹配的加法。不仅如此，union还可以拼接两个结果表。但是union连接的两张结果表必须具有相同的列数与类型。
-
limit：限制显示的查询结果条数，如
select <field_name1, field_name2, ...> from <table_name> where <condition> limit i, j- i,j：i表示显示的结果从下标i开始，j表示显示j条结果
-
explain：查看查询的过程信息，如
explain select <field_name1, field_name2, ...> from <table_name>;
2.9. 数据处理函数
有时从数据库表中获取到的数据须要进行一些处理。如将小写字母替换为对应的大写字母。这个处理过程能够在客户机上进行。也能够在数据库上进行。数据库上进行会更高效。数据库中有对应的数据处理函数来处理这些数据，可是在SQL中使用特殊的数据处理函数会减少其可移植性。不同的DBMS系统，其数据处理函数不同。
大多数的SQL支持下面类型的函数：
- 用于处理文本串的函数，如删除、填充值、转换大写和小写；
- 用于在数值数据上进行算术操作的函数；
- 用于处理日期和时间值并从这些值中提取特殊成分；
- 返回DBMS正在使用的特殊信息的系统函数，如返回用户登录，检查版本号。
3. 数据定义命令
3.1. 操作数据库、表格或用户
-
创建对象：
create database/table/user <name> <...>- name：创建对象的名称；
- <…>：创建对象的描述，例如如果是user则可以规定其密码，如果是表格则可以规定其表格的字段。
-
删除对象：
drop database/table/user <name> // drop database/table/user if exists <name> // truncate table <table_name>- drop是直接把表删除，而truncate则是把表中的数据完全删除，表仍然存在。
-
修改对象：
1. 修改表格名称 alter table <old_table_name> rename <new_table_name>; 2. 增加字段 alter table <table_name> add <column> <value_type>; 3. 删除字段 alter table <table_name> drop <column>; 4. 修改字段 alter table <table_name> change <old_column> <new_column> <new_value_type> ;
3.2. 约束
在创建表的时候，可以给表添加一些约束(constriant)，在向表插入数据的时候需要满足约束条件，以保证表中数据的完整性和有效性。约束主要包括非空约束(not null)，唯一性约束(unique)，主键约束(primary key)，外键约束(foreign key)。约束条件常写在字段数据类型的后面，并且约束可以分为列级约束与表级约束。
-
列级约束
约束条件直接放在字段名的后面。create table <table_name>(<column1 value_type1 <constriant1, ...>, column2 value_type2 <constriant2, ...>, ....>);- constrianti：字段columni的约束条件。
-
表级约束
约束条件放在所有的字段名的后面。create table <table_name>(<column1 value_type1, column2 value_type2, ....>, <constriants>);- constriants：多个字段之间的约束条件
可以在表格创建的时候指定主键的字段，被指定为主键的值不能为NULL且唯一。后续每一行数据都可以用主键取得。一张表只能有一个主键，包括单一主键(一个字段)和复合主键(多个主键)或者分为自然主键(一个自然数)或业务主键。
create table <table_name>(<column1 value_type1 <primary key, ...>, column2 value_type2 <constriant2, ...>, ....>);
// create table <table_name>(<column1 value_type1, column2 value_type2, ....>, <primary key(column1), ...>);
- 指定column1为主键
外键约束的功能是使插入某个表的数据的值位于规定范围内，而这个范围又在另一张表中定义，所以为了约束插入的表，需要使用外键约束。
create table <table_name1>(no int primary key);
create table <table_name2>(no int primary key auto_increment, name varchar(255), cno int, foreign key(cno) reference <table_name1>(no));
- <table_name1>：父表；
- <table_name2>：子表，需要先生成父表再添加子表。
3.3. 添加索引
给数据表的某一个字段添加索引，有利于增加搜索速度，创建索引的方式如下：
create index <table_name>_<column>_index on <table_name>(<column>);
删除索引的方式如下：
drop index <table_name>_<column>_index on <table_name>;
4. 数据操作命令
-
对表格插入对象：
insert into <table_name>(<column1, column2, ....>) values(<value1, value2, ...>)(...)(...)- columni：字段名；
- valuei：对应字段columni的值，必须要满足预先规定的数据类型。
insert只会插入新的一行，不能修改原有的数据。
-
修改表格已有的数据：
update <table_name> set <column1> = <value1>, <column2> = <value2>, ... where <condition>- = ：把新的值value1赋给column1；
- ：指定需要修改的对象。如果不加条件，那么修改所有的对象。
-
删除表格数据：
delete from <table_name> where <condition>- ：指定需要删除的对象。如果不加条件，那么删除所有的对象。
delete删除表中数据，但是数据还在硬盘中存在。缺点是效率比较低，但是支持回滚。而truncate属于物理删除，效率比较高，但是不支持回滚。
5. 数据控制命令
-
管理用户权限：
授于用户权限。grant <privileges> on <database_name>.<table_name> to <'username'>@'localhost' <with grant option>;撤销用户权限。
revoke <privileges> on <database_name>.<table_name> from <'username'>@'localhost'; -
事务操作：
事务操作包括事务提交与事务回滚，提交事务如下：
begin <work>; ... commit <work>;提交事务之后，意味着事务停止。
事务回滚只适用于回滚数据操作命令(DML)，而不适用于数据定义命令(DDL)。需要注意的是rollback只会回滚到上一次提交的状态，MySQL支持自动提交功能，所以使用回滚之前需要先输入start transaction关闭自动提交机制。start \ begin transaction; rollback
6. SQL总结
本文只是给出了SQL使用的一些模板，具体内容还需要与实际结合。
版权声明
转载请注明出处
扫一扫
676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
