MySQL个人学习记录

// MySQL语句
// WHERE 语句
// 读取数据通过 SELECT 语句来完成
// 如果需要有条件的选择数据，可以将WHERE 语句添加到SELECT语句中

// WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
// WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。
// 以下为操作符列表，可用于 WHERE 子句中。
// 下表中实例假定 A为10 B为20
// 操作符	描述	实例
// =	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
// <>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
// >	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
// <	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
// >=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
// <=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。
// 如果我们想再MySQL数据表中读取指定的数据，WHERE 子句是非常有用的。
// 使用主键来作为 WHERE 子句的条件查询是非常快速的。
// 如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

// 例子
// BINARY关键字设定where子句的字符串是区分大小写的
// SELECT * from table1 WHERE BINARY student_name="zhangsanfeng";

// 修改命令符UPDATE
// 将更新数据表table中 tudent_id 为 123 的 student_name和student_card字段值
// UPDATE table SET student_name="wwangwei",student_card="xxxxxxx" WHERE tudent_id="123";


// DELETE 删除数据
// SQL DELETE 语句从MySQL数据表中删除数据的通用语法：

// DELETE FROM table_name [WHERE Clause]
// 如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
// 你可以在 WHERE 子句中指定任何条件
// 您可以在单个表中一次性删除记录。
// 当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

// 将删除 runoob_tbl 表中 runoob_id 为3 的记录：
// DELETE FROM runoob_tbl WHERE runoob_id="3";

// LIKE语句
// 在MySQL中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
// WHERE 子句中可以使用等号 (=) 来设定获取数据的条件，如 "runoob_author = 'Sanjay'"。
// 但是有时候我们需要获取 runoob_author 字段含有 "jay" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
// SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。
// 如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的

// 将runoob_tbl表中获取runoob_author字段中以"jay"为结尾的的所有记录
// SELECT * from runoob_tbl WHERE runoob_author LIKE '%jay';

// 如果我们需要对读取的数据进行排序，我们就可以使用MySQL的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。
// 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
// 你可以设定多个字段来排序。
// 你可以使用 ASC升序 或 DESC 降序关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升排列。
// 你可以添加 WHERE...LIKE 子句来设置条件。

// 在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 runoob_tbl 中的数据
// SELECT * from runoob_tbl ORDER BY  runoob_username ASC;
// SELECT * FROM runoob_tbl WHERE runoob_username LIKE "%WANG" ORDER BY runoob_username DESC;





// 如何使用MySQL 的 JOIN 在两个或多个表中查询数据。
// 你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询。
// 以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处。

// [email protected]# mysql -u root -p password;
// Enter password:*******
// mysql> use RUNOOB;
// Database changed
// mysql> SELECT * FROM tcount_tbl;
// +-----------------+----------------+
// | runoob_author | runoob_count |
// +-----------------+----------------+
// | mahran          |             20 |
// | mahnaz          |           NULL |
// | Jen             |           NULL |
// | Gill            |             20 |
// | John Poul       |              1 |
// | Sanjay          |              1 |
// +-----------------+----------------+
// 6 rows in set (0.01 sec)
// mysql> SELECT * from runoob_tbl;
// +-------------+----------------+-----------------+-----------------+
// | runoob_id | runoob_title | runoob_author | submission_date |
// +-------------+----------------+-----------------+-----------------+
// |           1 | Learn PHP      | John Poul       | 2007-05-24      |
// |           2 | Learn MySQL    | Abdul S         | 2007-05-24      |
// |           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |
// +-------------+----------------+-----------------+-----------------+
// 3 rows in set (0.00 sec)
// mysql>


// 使用MySQL的JOIN来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
// SELECT a.runoob_id,a.runoob_author,b.runoob_count 
// FROM runoob_tbl a,tcount_tbl b
// WHERE a.runoob_author=b.runoob_author;

// MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
// SELECT a.runoob_id,a.runoob_author,b.runoob_count
// FROM runoob_tbl a LEFT JOIN tcount_tbl
// ON  a.runoob_author = b.runoob_author;
// LEFT JOIN，该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，
// 即便在右侧表tcount_tbl中没有对应的runoob_author字段值
// mysql> 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;
// +-------------+-----------------+----------------+
// | runoob_id | runoob_author | runoob_count |
// +-------------+-----------------+----------------+
// |           1 | John Poul       |              1 |
// |           2 | Abdul S         |           NULL |
// |           3 | Sanjay          |              1 |
// +-------------+-----------------+----------------+


// MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,
// 但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作

// IS NULL: 当列的值是NULL,此运算符返回true。
// IS NOT NULL: 当列的值不为NULL, 运算符返回true。
// <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
// 关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

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

// MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
// 查询为null的数据
// SELECT * from  runoob_tbl WHERE runoob_count IS NULL;
// mysql> SELECT * FROM tcount_tbl 
//     -> WHERE runoob_count IS NULL;
// +-----------------+----------------+
// | runoob_author | runoob_count |
// +-----------------+----------------+
// | mahnaz          |           NULL |
// | Jen             |           NULL |
// +-----------------+----------------+
// 查询不为空的数据
// SELECT  * FROM runoob_tbl WHERE runoob_count IS NOT NULL;

// mysql> SELECT * from tcount_tbl 
//     -> WHERE runoob_count IS NOT NULL;
// +-----------------+----------------+
// | runoob_author | runoob_count |
// +-----------------+----------------+
// | mahran          |             20 |
// | Gill            |             20 |
// +-----------------+----------------+



// MySQL可以通过 LIKE ...% 来进行模糊匹配

// 下表中的正则模式可应用于 REGEXP 操作符中。

// 模式	描述
// ^	匹配输入字符串的开始位置。如果设置了 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 次。
// 实例

// 查找name字段中以'st'为开头的所有数据：
// SELECT NAME FROM TABLE1 WHERE NAME REGEXP '^st';

// 查找name字段中以'ok'为结尾的所有数据
// SELECT NAME FROM TABLE WHERE NAME REGEXP 'OK$';

// 查找name字段中包含'mar'字符串的所有数据：
// SELECT NAME FROM TABLE WHERE NAME REGEXP 'MAR';

// 查找name字段中以元音字符[aeiou]开头且以'ok'字符串结尾的所有数据：
// SELECT NAME FROM TABLE WHERE NAME REGEXP '^[aeiou]|ok$';



// MySQL 事务主要用于处理操作量大，复杂度高的数据
// 比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，
// 也要删除和该人员相关的信息，
// 如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

// 在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 ALTER命令。
// 开始本章教程前让我们先创建一张表，表名为：testalter_tbl

// ALTER 命令及 DROP 子句来删除以上创建表的 i 字段
// ALERT TABLE testalert_tbl DROP i;
// ----->>>如果数据表中只剩余一个字段则无法使用DROP来删除字段。

// ADD 子句来想数据表中添加列，
// 如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:
// ALERT TABLE testalter_tbl ADD i INT COMMENT "COMMENT---->>字段注释";


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

// 例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

// mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
// 使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：

// mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
//  mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
// ALTER TABLE 对 Null 值和默认值的影响
// 当你修改字段时，你可以指定是否包含只或者是否设置默认值。

// 以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

// mysql> ALTER TABLE testalter_tbl 
//     -> MODIFY j BIGINT NOT NULL DEFAULT 100;
// 如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

// 修改字段默认值
// 你可以使用 ALTER 来修改字段的默认值，尝试以下实例：
// 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 ：
// ALTER TABLE testalter_tbl TYPE = MYISAM;


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

// 尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

// mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;


// SET FOREIGN_KEY_CHECKS = 0;//去除外键约束

// DROP TABLE IF EXISTS `loggs`;
// CREATE TABLE `loggs`  (
//   `id` int(11) NOT NULL AUTO_INCREMENT,
//   `uid` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '操作者id',
//   `mold` int(11) NOT NULL DEFAULT 0 COMMENT '0登录 1添加 2删除 3修改',
//   `texts` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述',
//   `ip` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '操作ip',
//   `type` int(11) NOT NULL DEFAULT 0 COMMENT '操作类型 0管理员 1教师 2学生',
//   `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
//   PRIMARY KEY (`id`) USING BTREE
// ) ENGINE = InnoDB AUTO_INCREMENT = 2799 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

// SET FOREIGN_KEY_CHECKS = 1;//加上外键约束

// ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;详解

// ENGINE=InnoDB：将mysql数据库引擎设置为InnoDB。InnoDB表示带行锁定和外键的事务安全表。

// AUTO_INCREMENT=42：表示自增字段的值，每次自增42

// DEFAULT CHARSET=utf8：设置默认字符集为utf8

// CHECKSUM=1:设为1表示对所有行进行实时检验求和，用于mysql检测主从数据一致性

// DELAY_KEY_WRITE=1 ：设置为1表示在表关闭之前，将对表的update操作更新数据到磁盘，不更新索引到磁盘，
// 把对索引的更改记录在内存，这样可以使索引更新更快。在关闭表的时候，一起更新索引到磁盘

// ROW_FORMAT=DYNAMIC：在sql里面，如果一张表不存在varchar、text、及其变形、blob及其变形的字段的话，
// 这样的表就是静态表，即ROW_FORMAT=FIXED，每条记录所占用的字节一样；
// 否则是动态表，每条记录所占用的字节是动态的，优点是节省空间，缺点是增加读取的时间的开销。