参考mysql 教程 | 菜鸟教程：http://www.runoob.com/mysql/mysql-tutorial.html
自我梳理，做个备份
一， UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
UNION [ALL | DISTINCT(默认)]二，在 group by 分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
@1, WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
对name进行聚和，多出一个 null 的统计总数
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;@2, 使用 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;
@3，分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by
SELECT name ,sum(*) FROM employee_tbl WHERE id<>1 GROUP BY name HAVING sum(*)>5 ORDER BY sum(*) DESC;三，mysql正则表达式：
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， 'abc' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
四，mysql事务：
事务满足四个条件：1，原子性；2，一致性；3，隔离性；4，持久性
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务控制语句：
-
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 开启自动提交
PHP中使用事务实例
MySQL ORDER BY 测试：
<?php $dbhost = 'localhost:3306'; // mysql服务器主机地址 $dbuser = 'root'; // mysql用户名 $dbpass = '123456'; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('连接失败: ' . mysqli_error($conn)); } // 设置编码，防止中文乱码 mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'RUNOOB' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行 mysqli_begin_transaction($conn); // 开始事务定义 if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚 } if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚 } mysqli_commit($conn); //执行事务 mysqli_close($conn);
五，alter mysql语句
1，展示建表columns字段
SHOW COLUMNS FROM testalter_tbl;2, alter table tab1 drop 字段；
alter table tab1 add 字段；
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;// 指定字段后添加新的字段
ALTER TABLE testalter_tbl MODIFY c CHAR(10);// 修改字段类型以及名称
show columns from tab1; // 查看字段以及类型
3，使用 CHANGE （小写：change）子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;// i--要被修改 j--要修改成的 int--字段类型
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;ALTER TABLE 对 Null 值和默认值的影响
mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;MySQL [my_test]> alter table tab1 alter j set default 1000;
MySQL [my_test]> show columns from tab1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | j | int(1) | YES | | 1000 | | | a | int(11) | YES | | NULL | | | c | char(12) | YES | | NULL | | +-------+----------+------+-----+---------+-------+
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;// ALTER 命令及 DROP子句来删除字段的默认值
ALTER TABLE testalter_tbl ENGINE = MYISAM; // 修改引擎
ALTER TABLE testalter_tbl RENAME TO alter_tbl; // 修改表名
alter table tableName drop foreign key keyName; // 删除外键约束：keyName是外键别名
// 修改字段的相对位置：
// 这里name1为想要修改的字段，type1为该字段原来类型，
// first和after二选一，这应该显而易见，
// first放在第一位，after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;六，mysql索引
1，创建索引的几种方式：
@1 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
create index index_column_name on 表名 （column_name(length)）
@2 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
@3 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
2,删除索引
DROP INDEX [indexName] ON mytable;3,唯一索引 unique index
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
主键索引 primary key ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
唯一索引 unique index ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
普通索引 index ALTER TABLE tbl_name ADD INDEX index_name (column_list)
全文索引 fulltext ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
使用 ALTER 命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i); // 添加主键
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY; // 删除主键显示索引信息
mysql> SHOW INDEX FROM table_name; \G七，mysql 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
1，创建临时表：
create temporary table tmp_tab(
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
);
INSERT INTO tmp_tab
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES
('cucumber', 100.25, 90, 2);
select * from tmp_tab;2,默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁
DROP TABLE tmp_tab;//手动删除 临时表八，mysql 复制表
1，获取数据库表的完整结构：
show create table 表名 \G
CREATE TABLE `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;2,修改SQL语句的数据表名，并执行SQL语句
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 tbl;九，mysql 元数据
1，在PHP中，你可以使用 mysqli_affected_rows( ) 函数来获取查询语句影响的记录数。
2，php输出 MySQL 服务器上的所有数据库
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
$db_list = mysqli_query($conn, 'SHOW DATABASES');
while ($db = mysqli_fetch_object($db_list))
{
echo $db->Database . "<br />";
}
mysqli_close($conn);获取服务器元数据
以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。
| 命令 | 描述 |
|---|---|
| SELECT VERSION( ) | 服务器版本信息 |
| SELECT DATABASE( ) | 当前数据库名 (或者返回空) |
| SELECT USER( ) | 当前用户名 |
| SHOW STATUS | 服务器状态 |
| SHOW VARIABLES | 服务器配置变量 |
3, mysql 序列使用：
PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中 AUTO_INCREMENT列的值。
重置序列
如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：
mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);4，设置序列开始值：
ALTER TABLE t AUTO_INCREMENT = 100;
5，mysql 处理重复数据：
@1. insert ignore into 与 insert into
INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的
@2,设置双主键：
mysqlCREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);@3， replace into 如果存在 primary 或者 unique相同的记录，先删再增
@4，设置数据的唯一性，除了主键外还可以使用 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)
);@5, 统计重复数据
select count(*) from 表名 group by id having total >1;//分组后的二次筛选
@6，过滤重复数据： distinct
十，mysql以及sql注入
1，PHP的MySQL扩展提供了mysqli_real_escape_string()函数来转义特殊的输入字符
// get_magic_quotes_gpc — 获取当前 magic_quotes_gpc(魔术引号开关) 的配置选项设置
if (get_magic_quotes_gpc())
{
$name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");2, like语句中的注入 addcslashes()函数在指定的字符前添加反斜杠
$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");3，mysql 导出数据：
select * from 表名 into outfile '/tmp/1.txt';
生成一个文件，各值用逗号隔开
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;@1，导出整个数据库的数据 RUNOOB--表名
mysqldump -u root -p RUNOOB > database_dump.txt@2, 需要备份所有数据库 --all-databases
mysqldump -u root -p --all-databases > database_dump.txt@3，将数据表以及数据库拷贝到其他主机
mysqldump -u root -p database_name table_name > dump.txt
mysql -u root -p database_name < dump.txt // 完整备份数据库
// 将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的
mysqldump -u root -p database_name \ | mysql -h other-host.com database_name
// 将远程服务器的数据拷贝到本地
mysqldump -h other-host.com -P port -u root -p database_name > dump.txt4，mysql 导入数据
@1,mysql命令注入
mysql -h 127.0.0.1 -u root -p 123456 < xxx.sql@2, source 命令注入
use 库名；
set names utf-8；
source /home/xxx.sql；@3，使用 load data 导入数据
load data local infile 'dump.txt' into table 表名如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。
如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
-> INTO TABLE mytbl (b, c, a);
593
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
