MySQL基础

1 基础

<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或都为 NULL 时返回 true。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

修改表ALERT

# NULL值缺省为允许NULL

# 添加ADD或删除DROP字段。ADD或MODIFY中，可用FIRST或AFTER指定字段位置
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

# 修改字段名称或类型。MODIFY或CHANGE
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
# CHANGE参数分别为旧字段名、新字段名和字段类型
ALTER TABLE testalter_tbl CHANGE i j BIGINT;

# 修改默认值，SET DEFAULT修改，DROP删除。
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

# 修改表类型，使用ALERT或TYPE字句
ALTER TABLE testalter_tbl ENGINE = MYISAM;

# 修改表名，RENAME
ALTER TABLE testalter_tbl RENAME TO alter_tbl;

# 创建或删除索引
ALERT TABLE tableName DROP FOREIGN KEY keyName;

2 事务

事务控制语句：

• BEGIN 或 START TRANSACTION： 显式开启一个事务；
• COMMIT 或COMMIT WORK：等价的。COMMIT 会提交事务，并将所有修改成为永久性的；
• ROLLBACK 或 ROLLBACK WORK：等价的。回滚会结束用户的事务，并撤销所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；可用于标记当前事务状态，再嵌套事务过程。
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

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

1、用 BEGIN, ROLLBACK, COMMIT来实现

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

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

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

3 索引

索引能提高查询速率，同时也降低了更新速率。

#BLOB或TEXT必须指定length
# 创建表时声明
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);
# 单独创建
CREATE INDEX indexName ON table_name (column_name);

# 添加
ALTER table tableName ADD INDEX indexName(columnName);

# 删除
DROP INDEX [indexName] ON mytable;

#唯一索引UNIQUE
# 创建
CREATE UNIQUE INDEX indexName ON mytable(username(length));
# 或
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);
# 添加
ALTER table mytable ADD UNIQUE [indexName] (username(length));

# 主键PRIMARY,唯一且不能为NULL。删除时不用指定字段名
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
ALTER TABLE testalter_tbl DROP PRIMARY KEY;

# 全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

# 显示索引信息。\G用于格式化输出
SHOW INDEX FROM table_name; \G

4 临时表

仅在当前会话中可用的表。使用同普通表，但是不能使用SHOW TABLES查询

# 创建
CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL, 
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, 
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

# 删除，DROP或关闭会话
DROP TABLE SalesSummary;

5 复制表

CREATE TABLE SELECT只能复制表结构和数据、NULL约束等，不能复制索引、默认值等其他约束和触发器，会提交当前未完成的事务，整个过程占用元数据锁，不被基于GTID的复制支持。

# 方法一
# 1 获取原表建表语句
SHOW CREATE TABLE runoob_tbl \G;

# 2 编辑新表建表语句
CREATE TABLE `clone_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY  (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB;

# 3 复制数据
INSERT INTO clone_tbl (
    runoob_id,
	runoob_title,
	runoob_author,
	submission_date)
SELECT runoob_id,runoob_title,
	runoob_author,submission_date
FROM runoob_tbl;


# 方法二：LIKE
# 1 完整复制表结构
CREATE TABLE targetTable LIKE sourceTable;
# 2 复制数据
INSERT INTO targetTable SELECT * FROM sourceTable;

6 元数据

7 序列

即AUTO_INCREMENT

# 使用
CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected
);

# 重置，需要保证过程中没有插入
# 先删除自增列，在重建自增列
ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

# 指定起始值
# 建表时指定
CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, 
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
# 修改表时指定
ALTER TABLE t AUTO_INCREMENT = 100;

8 重复数据

(1) 防止重复数据

使用主键PRIMARY KEY或唯一索引UNIQUE

(2) 统计重复数据

GROUP BY分组，COUNT计数，HAVING过滤出重复数据

SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;

(3) 过滤重复数据

# DISTINC
SELECT DISTINCT last_name, first_name
FROM person_tbl;

# 将查询字段作为分组依据
SELECT last_name, first_name
FROM person_tbl
GROUP BY (last_name, first_name);

(4) 删除重复数据

# 方法一：将所有字段作为分组依据，过滤重复数据并新建表、删除原表
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;

# 方法二：添加主键
ALTER IGNORE TABLE person_tbl
ADD PRIMARY KEY (last_name, first_name);

9 SQL 注入

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双”-“进行转换等。

2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

10 数据导出

(1) SELECT … INTO OUTFILE

用户需要具有读取数据和文件写入的权限

不能使用已有的文件，为了避免数据被修改。

在Unix中，文件可读且所有者为MySQL所有者。

SELECT 
	* 
FROM 
	runoob_tbl 
INTO OUTFILE 
	'/tmp/runoob.txt';
	
# CSV文件格式
SELECT
	* 
FROM 
	passwd 
INTO OUTFILE 
	'/tmp/runoob.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

(2) mysqldump

生成建表脚本

# --tab指定文件目录
mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl

# 为特定数据库的特定表指定文件
mysqldump -u root -p RUNOOB runoob_tbl > dump.txt

# 导入数据
mysql -u root -p database_name < dump.txt

# 使用管道导出并导入到远程主机，需要保证两台主机可以互相访问
mysqldump -u root -p database_name | mysql -h other-host.com database_name

11 数据导入

# 方法一: mysql命令
mysql -u 用户名 -p 密码 < 要导入的数据库数据

# 方法二: source命令
create database abc;      # 创建数据库
use abc;                  # 使用已创建的数据库 
set names utf8;           # 设置编码
source /home/abc/abc.sql  # 导入备份数据库

# 方法三: LOAD DATA命令
# LOCAL指定文件在客户端所在主机
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
# 指定字段分隔符和行分隔符
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);

# 方法四: mysqlimport命令
# --local指定客户端文件
mysqlimport -u root -p --local mytbl dump.txt
#--fields-terminated-by指定字段分隔符，--lines-terminated-by指定行分隔符
mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
# --columns指定列顺序
mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt

12 函数

13 运算符

安全等于<=>：严格比较两个值是都均为NULL，是则返回1，其中一个是返回0

优先级：从上到下依次增重

参考资料

MySQL 教程

慎用create table as select,一定要注意默认值的问题