MySQL个人学习记录

落日映苍穹つ 2021-09-17 08:46 385阅读 0赞

// 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 的使用的不同之处。
    
    // root@host# 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，每条记录所占用的字节一样；
    // 否则是动态表，每条记录所占用的字节是动态的，优点是节省空间，缺点是增加读取的时间的开销。