知识库——SQL

ALTER命令

更改表名

ALTER TABLE old_name RENAME TO new_name;

删除，添加或修改表字段

ALTER TABLE testalter_tbl DROP i;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句

ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

修改列名

# 需要指定列的类型
ALTER TABLE <table_name> change column <old_name> <new_name> varchar(30);

改字段类型及名称

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

将字段名 i 改为 j ，并指定新类型

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

添加表列

alter table <table_name> add columns (<col_name1> varchar(10), <col_name2> string)

删除表列

alter table <table_name> drop column <col_name>

修改存储引擎：修改为myisam

alter table tableName engine=myisam;

删除外键约束：keyName是外键别名

alter table tableName drop foreign key keyName;

修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面

alter table tableName modify name1 type1 first|after name2;

插入数据

INSERT INTO `runoob_tbl`
    (runoob_title, runoob_author, submission_date)
    VALUES
    ("学习 PHP", "菜鸟教程", NOW());

查询数据

select _column,_column from _table [where Clause] [limit N][offset M]

•  select * : 返回所有记录
•  limit N : 返回 N 条记录
•  offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
•  limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

SELECT * FROM table LIMIT 5,10;    // 检索记录行 6-15   
  
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1  
SELECT * FROM table LIMIT 95,-1;   // 检索记录行 96-last. 

查询数据库中学生表逆序的 5 条数据：

select * from student order by id desc limit 0,5;

•  order by id: 通过id来查询
•  desc: 表示倒序，可替换成 asc ，表示升序
•  start: 开始（升序第一条是0，降序最后一条是0）
•  count: 查询的个数

分页查询语句的性能分析

子查询的分页方式

越往后分页，LIMIT 语句的偏移量就会越大，速度也会明显变慢。

此时，我们可以通过子查询的方式来提高分页效率，大致如下：

SELECT * FROM articles WHERE id >=
 (SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

 JOIN 分页方式

join 分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。

子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。

实际可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。

WHERE 子句

使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';

UPDATE 更新

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]

DELETE 语句

DELETE FROM table_name [WHERE Clause]

LIKE 子句

百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *

SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';

在 where like 的条件查询中，SQL 提供了四种匹配方式。

1. %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
2. _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
3. []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
4. [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
5. 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

UNION 操作符

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用形式如下：

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；

SELECT 列名称 FROM 表名称 UNION [ALL | DISTINCT] SELECT 列名称 FROM 表名称 ORDER BY 列名称；

DISTINCT :可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

排序 ORDER BY

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
  • 你可以添加 WHERE...LIKE 子句来设置条件。

分组 GROUP BY

1. 计算name出现的次数，并将结果按name排序

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

2. 对singin列使用函数处理并在结果展示中重命名

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

3. 使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

NULL 值处理

MySQL提供了三大运算符:

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。

查看已启动服务器

ps -ef | grep mysqld

管理数据库

mysql -u root -p

use mysql;

FLUSH PRIVILEGES;

SHOW DATABASES;        // 查看所有数据库

SELECT DATABASE();     // 查看正在使用的数据库

SHOW TABLES;

SHOW COLUMNS FROM runoob_tbl;

SHOW INDEX FROM runoob_tbl;

SHOW TABLE STATUS FROM RUNOOB;   // 显示数据库 RUNOOB 中所有表的信息

SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     // 表名以runoob开头的表的信息

SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   // 加上 \G，查询结果按列打印

创建数据库

mysql -u root -p
create DATABASE RUNOOB;

mysqladmin -u root -p create RUNOOB

不要将表中与业务相关的字段设置为主键，即使它可以唯一标识这一行，比如身份证号，学号等等，主键越没有意义，说明主键设置的越好。

删除数据库

drop database <数据库名>;

mysqladmin -u root -p drop RUNOOB

创建数据表

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建外部表

create external table bigdata_jr.e_zyb_city
(provice string, city string, province_id string)
row format delimited
fields terminated by ','
stored as textfile
location 'hdfs://nameservice/group/bigdata_jr/files/hanli'

删除数据表

delete，drop，truncate 都有删除表的作用，区别在于：

 1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除

 2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚

 3、执行的速度上，drop > truncate > delete

1、drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;

drop table student;

2、truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;

truncate table student;

3、delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

delete from student;

4、delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

delete from student where T_name = "张三";

5、delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;

delete from student where T_name = "张三";
optimize table student;

6、delete from 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

正则表达式

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

事务

事务用来管理 insert,update,delete 语句

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

索引

索引分单列索引和组合索引，索引大大提高了查询速度，同时却会降低更新表的速度

元数据

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

处理重复数据

指定字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性

INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

过滤重复数据

SELECT DISTINCT last_name, first_name FROM person_tbl;

SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

SQL 注入

定义：通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

导出数据

SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt';

// 设置导出格式
SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt' 
FIELDS TERMINATED BY ',' [OPTIONALLY] ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

导出表作为原始数据

结果是不包含首行表的内容。--tab 选项来指定导出文件指定的目录，该目标必须是可写的

mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl

导出 SQL 格式的数据

# 导出一张表
mysqldump -u root -p RUNOOB runoob_tbl > dump.txt

# 导出一个数据库
mysqldump -u root -p RUNOOB > database_dump.txt

# 导出所有数据库
mysqldump -u root -p --all-databases > database_dump.txt 

# 将远程主机的数据库拷贝到本地
mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

导入数据

mysql 命令导入

mysql -u root -p database_name < dump.txt

mysql -u 用户名 -p 密码 < 要导入的数据库数据(runoob.sql)

source 命令导入

create table abc;         -- 创建数据库
use abc;                  -- 使用已创建的数据库 
set names utf8;           -- 设置编码
source /home/abc/abc.sql  -- 导入备份数据库

使用 LOAD DATA 导入数据

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件

 FIELDS 和 LINES 子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。默认标记是定位符和换行符

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n';

更改列顺序

LOAD DATA LOCAL INFILE 'dump.txt' 
INTO TABLE mytbl (b, c, a);       // 在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a

使用 mysqlimport 导入数据

参考菜鸟教程：https://www.runoob.com/mysql/mysql-database-import.html