数据库开发 MySQL

MySQL是Web世界中使用最广泛的数据库服务器。SQLite的特点是轻量级、可嵌入，但不能承受高并发访问，适合桌面和移动应用。而MySQL是为服务器端设计的数据库，能承受高并发访问，同时占用的内存也远远大于SQLite。

此外，MySQL内部有多种数据库引擎，最常用的引擎是支持数据库事务的InnoDB。

常用MySQL命令

设置mysql root账号密码

mysql admin -u root password root

修改root密码

mysql admin -u root -p password newpwd

连接数据库

mysql -u root -p
mysql -u root -p root

连接远程主机mysql

mysql -h 主机地址 -u 用户名 -p 用户密码

创建数据库

create database mydb;

设置字符集

alter database mydb default character set = utf8;

显示数据库列表

show databases;

切换到某个database

use mydb;

显示本库中的所有表

show tables;

显示某表（table1）的结构

describe table1;

显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

show columns from tableName;

显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

show index from tableName;

输出Mysql数据库管理系统的性能及统计信息。

SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] G:

SHOW TABLE STATUS from dbName; # 显示数据库 dbName 中所有表的信息
SHOW TABLE STATUS from dbName LIKE 'tableName%'; # 表名以tableName开头的表的信息
SHOW TABLE STATUS from dbName LIKE 'tableName%'G; # 加上 G，查询结果按列打印

建表

create table tableName (字段设定列表);
//
create table TestTable(
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(40) NOT NULL,
    _date DATE,
    PRIMARY KEY ( id )
); 

删库

drop database 库名;

删表

drop table 表名;

将表中的记录清空

delete from 表名;

为已经存在的表添加新列

alter table tableName add newColumn varchar(8) comment '新添加的字段' // comment为注释，就像在java中//作用是一样的。

为列设定默认值

create table tablename (columnname datatype default defaultvalue); // 新建并设置默认值
alter table tablename alter column columnname set default defaultvalue; // 修改现有列的默认值

删除列

alter table tableName drop column Gatewayid

插入新记录 

insert into User(birthday) values (NOW()); // NOW() 是一个 MySQL 函数，该函数返回日期和时间。

查询

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
// 通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
// 使用 LIMIT 属性来设定返回的记录数。

SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。
如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

Select语法

SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
// 可以在WHERE子句中指定任何条件。也可以在WHERE子句中使用LIKE子句。可以使用LIKE子句代替等号(=)。
// LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
// 可以使用AND或者OR指定一个或多个条件。

JOIN
按照功能大致分为如下三类：

1. INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
2. LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
3. RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

INNER JOIN

// 使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
// 以上 SQL 语句等价于：
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

LEFT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
// 上实例中使用了LEFT JOIN，该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的runoob_author字段值。

RIGHT JOIN

SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;
// 上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 runoob_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的runoob_author字段值。

NULL

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。 在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。 MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

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

正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

查找name字段中以'st'为开头的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中包含'mar'字符串的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'mar';

事务

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务，事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行，事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1. 事务的原子性：一组事务，要么成功；要么撤回。
2. 稳定性 ： 有非法数据（外键约束之类），事务撤回。
3. 隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4. 可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

在Mysql控制台使用事务来操作

1. 开始一个事务 start transaction
2. 做保存点 save point 保存点名称
3. 操作
4. 可以回滚，可以提交，没有问题，就提交，有问题就回滚。

修改数据库、数据表 

删除字段

mysql> ALTER TABLE testalter_tbl DROP age; // 如果数据表中只剩余一个字段则无法使用DROP来删除字段。

添加字段
使用 ADD 子句来想数据表中添加列，如下在表 testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

如果需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

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;

FIRST 和 AFTER 关键字只占用于 ADD 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); 把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

ALTER TABLE 对 Null 值和默认值的影响
当修改字段时，你可以指定是否包含空值或者是否设置默认值。

mysql> ALTER TABLE testalter_tbl 
-> MODIFY j BIGINT NOT NULL DEFAULT 100; 指定字段 j 为 NOT NULL 且默认值为100 。

修改字段默认值

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
使用 ALTER 命令及 DROP子句来删除字段的默认值
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

修改数据表类型
可以使用 ALTER 命令及 TYPE 子句来完成。我们将表 testalter_tbl 的类型修改为 MYISAM ：
注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;

修改表名
如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

普通索引

最基本的索引，它没有任何限制，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

添加索引:
create index index_time on Record(time(20)); 
alter table Record add index index_time (time(20));
create table User2(id integer primary key auto_increment, province varchar(20), index index_province (province(20)));

删除索引:
drop index index_time on Record;

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

CREATE INDEX indexName ON mytable(username(length)); 

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)));

删除索引

DROP INDEX [indexName] ON mytable;

唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

    create unique index index_name on table(column(length));
    alter table Record add unique index_time (time(20));
    create table User2(id integer primary key auto_increment, province varchar(20), unique index_province (province(20)));

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 

创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)));

使用ALTER 命令添加和删除索引

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

添加索引

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

删除索引

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

使用 ALTER 命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

mysql> SHOW INDEX FROM table_nameG 
可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 G 来格式化输出信息。

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

mysql> 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
);

MySQL 复制表
如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。

1. 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
2. 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
3. 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

mysql> show create table tt G;
mysql> ......
// 执行上面的SQL语句
// 拷贝数据表的数据使用 INSERT INTO... SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;

MySQL序列
MySQL序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现

设置序列的开始值
一般情况下序列的开始值为1，但如果需要指定一个开始值100，那我们可以通过以下语句来实现：

mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);

或者也可以在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 处理重复数据

防止表中出现重复数据
可以在MySQL数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLE person_tbl
(
　　first_name CHAR(20),
　　last_name CHAR(20),
　　sex CHAR(10)
);

如果想设置表中字段first_name，last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果设置了双主键，那么哪个键的默认值不能为NULL，可设置为NOT NULL。

CREATE TABLE person_tbl
(
　　first_name CHAR(20) NOT NULL,
　　last_name CHAR(20) NOT NULL,
　　sex CHAR(10),
　　PRIMARY KEY (last_name, first_name)
);

如果我们设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

REPLACE INTO

INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而REPLACE INTO 如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。
另一种设置数据的唯一性方法是添加一个UNIQUE索引，如下所示：

CREATE TABLE person_tbl
(
　　first_name CHAR(20) NOT NULL,
　　last_name CHAR(20) NOT NULL,
　　sex CHAR(10)
　　UNIQUE (last_name, first_name)
);

统计重复数据
以下我们将统计表中 first_name 和 last_name的重复记录数：

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

以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

• 确定哪一列包含的值可能会重复。
• 在列选择列表使用COUNT(*)列出的那些列。
• 在GROUP BY子句中列出的列。
• HAVING子句设置重复数大于1。

过滤重复数据
如果需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl
-> ORDER BY last_name;
# 也可以使用 GROUP BY 来读取数据表中不重复的数据：
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);

删除重复数据

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
-> FROM person_tbl;
-> GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);

约束

外键

create table User(id integer primary key auto_increment, name varchar(20));

create table Record(id integer primary key auto_increment, time varchar(20), ur_id integer not null, foreign key (ur_id) references User (id));

insert into User(name) values('ccf'); // id = 1
insert into User(name) values('yyn'); // id = 2

insert into Record(time, ur_id) values('1', '1'); // id = 1
insert into Record(time, ur_id) values('0', '2'); // id = 2
insert into Record(time, ur_id) values('0', '3'); // fail: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`record`, CONSTRAINT `record_ibfk_1` FOREIGN KEY (`ur_id`) REFERENCES `User` (`id`))

级联

alter table Record add constraint id_check foreign key(ur_id) references User(id) on delete cascade on update cascade; // ?

其它

防止SQL注入，我们需要注意以下几个要点：

• 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
• 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
• 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
• 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
• 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
• sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。