MySQL杂记

/*MySQL语句示例*/

//创建数据表(CREATE TABLE table_name(column_name, column_type);)
CREATE TABLE IF NOT EXISTS `runoob_tb1`(
    `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;
/**********************************************************************/
//删除数据表(DROP TABLE table_name)
/**********************************************************************/
//插入数据(INSERT INTO table_name(field1, field2, field3, ..., fieldN) VALUES(value1, value2, value3, ..., valueN));
INSERT INTO runoob(runoob_id, runoob_title, runoob_author, submission_date) VALUES(0, 'HAH','1', DateTime.Now.ToString()), (0, 'LEVEL', '2', DateTime.Now.ToString());
//如果添加了主键自增，数据可以写为0或者null.
/*********************************************************************/
//查询数据
SELECT column_name, column_sex
FROM table_name 
[WHERE Clause] 
[LIMIT N] [OFFSET M]
//查询语句中可以使用一个或多个表表之间用逗号分割，并使用WHERE语句来设定查询条件
//SELECT命令可以读取一条或者多条记录.
//可以使用*来代替其他字段，SELECT语句会返回表的所有字段数据
//可以使用WHERE语句来包含任何条件
//可以使用LIMIT属性来设定返回的记录数
//可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0(就是跳过多少条数据开始查询))

//子查询（嵌套查询）
1、带ANY(SOME)关键字的子查询
这里ANY和SOME是同义词，都是表示满足任一条件
SELECT num1 FROM tb1 WHERE num1 > ANY(SELECT num2 FROM tb2);
2、带ALL关键字的子查询
SELECT num1 FROM tb1 WHERE num1 > ALL(SELECT num2 FROM tb2);
3、带EXISTS关键字的子查询
SELECT * FROM tb1 WHERE col1 > 1 AND WHERE EXISTS(SELECT col2 FROM tb1 WHERE col2 = 1)//如果表中存在col2 = 1时，就查询表中col1大于1的记录
4、带有IN关键字的子查询
SELECT * FROM tb1 WHERE id IN(SELECT col1 FROM tb2);
5、带比较运算符的子查询
SELECT * FROM tb1 WHERE col1 > (SELECT...)

//UNION操作符
SELECT col1 , col2 ,...,colN
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT col1, clo2, ..., colN
FROM tables02
[WHERE conditions];
ALL:可选，返回所有结果集，包含重复数据
DISTINCT:可选，删除结果集中重复的数据。默认情况下UNION操作符已经删除了重复数据，所以DISTINCT操作符对结果没啥影响。
UNION:默认过滤了重复的数据
UNION ALL:显示所有的

//GROUP BY语句
//根据一个或多个列对结果集进行分组
//在分组的列上我们可以使用COUNT,SUM,AVG等函数
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

//事务控制语句
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 //开启自动提交


SAVEPOINT savepoint_name;  //声明一个savepoint_name
ROLLBACK TO savepoint_name；//回滚到savepoint_name
RELEASE SAVEPOINT savepoint_name;//删除一个savepoint_name

//MySQL索引（单列索引、组合索引）
1、普通索引
    创建索引
    CREATE INDEX indexName ON mytable(username(length));
    修改表结构（添加索引）
    ALTER table tablename ADD INDEX indexname(columnname);
    创建表的时候直接指定
    CREATE TABLE mytable(
        ID INT NOT NULL,
        username VARCHAR(16) NOT NULL,
        INDEX [indexname] (username(length))
    );
    删除索引的语法
    DROP INDEX [indexname] ON mytable;
2、唯一索引（与普通索引类似，不同处：索引的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一）
    创建索引
    CREATE UNIQUE INDEX indexname ON mytable(username(length));
    修改表结构
    ALTER table mytable ADD UNIQUE [indexname] (username(length));
    创建表的时候直接指定
    CREATE TABLE mytable(
        ID INT NOT NULL,
        username VARCHAR(16) NOT NULL,
        UNIQUE [indexname] (username(length))
    );