SQL常用语句的学习小结

@目录

SQL常用语句的学习小结

之前学习SQL的基本操作的知识，现在隔一段时间后对其进行总结。
下列语句中，[]表示可有可无的语句

1. SQL最基本的命令

1.1 数据库的基本操作

通过命令提示符对数据库进行操作（后期直接用Navicat对数据库操作更加方便）：

1. 连接数据库：mysql -u用户名 -p密码
2. 创建数据库并确定编码方式：create database db_name character set = charset_name
3. 修改数据库的编编码方式：alter [db_name] character set = charset_name
4. 删除数据库：drop [database] db_name
5. 打开数据库：use db_name
6. 查看当前打开的数据库：select database()

1.2 数据表的基本操作

数据表是数据库的成员，是其他对象的基础。例如关系型数据表通常是二维表格，行为记录，列为字段。

1. 创建数据表：create table tb_name (column_name data_type … ,)
   例：create table tb1 (username varchar(20) not null, product varchar(20))
2. 查看数据库中的数据表成员：show tables [from db_name]
3. 查看tb_name的结构：show columns from tb_name [DESC]
4. 插入记录：insert tb_name [(col_name, …)] values (vol, …)
5. 查找记录：select expr from tb_name（后面详述）
6. 添加/删除多列记录：alter table tb_name add/drop (col_name1 col_definition1, col_name2 col_definition2)
7. 添加和删除同时进行：alter table tb_name add (col_name1 col_definition1, col_name2 col_definition2), drop (col_name3 col_definition3, col_name4 col_definition4)
8. 修改列定义：alter table tb_name modify col_name col_definition
9. 修改列名称：alter table change old_col_name new_col_name
10. 数据表更名：alter table tb_name rename new_tb_name（建议不要随意修改列、表名称）

2. 常用的数据类型

1. 字符型：char(M), varchar(M), tinytext, text, mediumtext, longtext（M：“字节”）
2. 整型：tinyint, smallint, nediumint, int, bigint
3. 浮点型：float[(M, D)] (M：数字总位数；D：小数点后面的位数), double[(M, D)]
4. 日期时间型：time, date, datetime, timestamp

3. 完整性约束

1. 添加或删除主键约束/唯一约束：alter table tb_name add/drop primary/unique key [index_name] [index_type] (index_col_name1, index_col_name2, …)
2. 删除主键约束/唯一约束：alter table tb_name drop primary/unique key index_name
3. 添加外键约束：alter table tb_name add foreign key [index_name] (index_col_name) reference father_tb_name(col_name)
4. 删除外键约束：alter table tb_name drop foreign key index_name

4. 操作数据表中的记录

1. 插入记录：（1）insert tb_name (expr1, expr2, …) value (co_expr1, co_expr2)（2）insert tb_name set col_name = (expr1) （3）insert tb_name select…
2. 更新记录：update tb_name set col_name1 = (expr1), col_name2 = (expr2)
3. 删除记录：delete from tb_name where where_condition
4. 查询记录：select select_expr from tb_name as alias_name [where where_condition] [group by col_name {ASC|DESC}]+[having where_condition] [order by col_name {ASC|DESC}] [limit row_count] （查询语句是操作数据表最有难度的语句，多加练习）

5. 子查询（嵌套查询）

1. 使用比较运算符的子查询：select * from t1 where col1 > (select avg(col2) from t2)
2. 子查询返回多个结果时，可用Any, All等修饰：select * from t1 where col1 > any/all (select avg(col2) from t2)
3. 使用[not] in / [not] exists 等的子查询：select * from t1 where col1 [not] in/exists (select avg(col2) from t2)

6. 多表连接

1. 语法结构：tb1 [inner|left|right|outer] join tb2 on conditional_expr
2. inner join是两者的交集， outer join是两者的并集，left join是左表的全集+右表与左表的交集，右表没有的用左表中的值代替，right join同上

7. 函数

7.1 字符函数

1. 连接字符函数：concat(‘A’, ‘B’)
2. 大小写转换字符函数：lower(‘A’), upper(‘a’)
3. 删除字符函数：ltrim/rtrim/trim(’ MY SQL ')
4. 截取字符函数：substring(‘MYSQL’, 1, 2)
5. 替换字符函数：replace(‘MYSQI’, ‘I’, ‘L’)
6. 模糊匹配：like(‘M%’)
7. 其他：length('MYSQL)

7.2 数值运算符函数

1. 进一取整：ceil()
2. 整数除法：div()
3. 舍一取整：floor()
4. 取余：mod()
5. 幂运算：power(3, 3)
6. 四舍五入：round(12.617, 2)
7. 数字截取：truncate(12.243, 2)

7.3 比较运算符函数

1. [not] between … and …
2. [not] in
3. is null

7.4 日期函数

1. date_add(‘2018-3-10’, interal + 1 year)
2. datediff(‘2018-5-12’, ‘2019-5-2’)
3. date_format(‘2018-8-9’, ‘%m%d%Y’)

7.5 聚合函数

1. avg(), count(), max(), min(), sum()等

7.6 自定义函数

create function function_name(parameter)
returns result_format
begin
routine_body
return result
end

eg:
create function add_user(username varchar(20))
returns int unsigned
begin
insert test(username) values (username);
return last_insert_id();
end

drop function func_name

8. 存储过程

Mysql语句的执行流程：

创建存储过程：
create procedure sp_name (in/out/inout para_name, para_typies)
begin
routine_body
end

in: 参数必须在调用存储时可指定，该值只进不出
out：参数在存储过程可改变可返回，该值只出不进
inout：参数值在调用存储时可指定，在存储过程可改变可返回

eg:
create procedure RemoveUserById (in p_id int unsigned)
begin
delete from users where id = p_id
end

调用存储过程：call sp_name(parameter[… , …])
删除存储过程：drop procedure sp_name
修改存储过程：alter procedure sp_name [characteristic]

存储过程与自定义函数的区别：

• 工作中，常用存储过程对表操作，少用自定义函数对表操作
• 存储过程可返回多个值，自定义函数返回一个值
• 存储过程一般是独立执行的，自定义函数常作为SQL语句的一部分

9. 索引

1. 创建索引：create [unique] index index_name on tb_name(column name)
2. 组合索引：create index index_name on tb_name(column1, column2)
3. 适合使用索引的字段：（1）用于表结合的字段（2）常在order by 和 group by 里引用的字段（3）具有大量唯一值的字段
4. 不适合使用索引的字段：（1）小规模的表（2）经常在where中作为过滤器使用的字段（3）经常被更新和操作的字段 （4）包含大量NULL的字段

10. 优化SQL语句

1. FROM字句的表顺序安排：较小的表放在前面，较大的表放在后面
2. WHERE字句的表结合顺序安排：有基表，来自基表的字段放在结合操作右侧，被结合的表字段按表从小到大次序排列；无基表，最大的表的字段放在结合操作右侧，被结合的表字段按表从小到大次序排列
3. 最严格条件：最严格条件指返回最少记录的条件。把它放在where子句末尾，让优化器优先读取
4. 使用LIKE操作符和通配符的结合
5. 尽量用IN代替OR操作符
6. 避免使用HAVING子句
7. 避免大规模排序
8. 常用的语句使用存储过程

11.视图和异名

1. 从一个表创建视图：create view view_name as select …
2. 从多个表创建视图：create view view_name as select … from tb1, tb2 where tb1.id = tb2.id
3. 从视图创建视图：create view2 as select * from view1
4. 异名可使访问其他用户的表或图时不必使用完整限制名
5. 创建异名：create [public | private] synonym synonym_name for table/view