MySQL notes 2

一些数据类型：
数字类型

MySQL使用标准的 ANSI SQL 数字类型，所以如果你在学习MySQL之前，接触过其他数据库系统，那么肯定对这些定义不会感到陌生。下面就列举出常见的一些数字类型及其说明：

INT 正常大小的整数，可以有符号，也可以没有符号。如果是有符号整数，其允许的取值范围是-2147483648~2147483647；无符号整数的取值范围是从0至4294967295。最高可指定11位数字。
TINYINT 非常小的整数，分为有无符号两种。前有符号时，其允许取值范围是-128~127；无符号时的取值范围为0~255。所以，最高可指定4位数字。
SMALLINT 较小的整数，分为有无符号两种。前有符号时，其允许取值范围是-32768~32767；无符号时的取值范围为0~65535。所以最高可指定5位数字。
MEDIUMINT 中型大小的整数，分为有无符号两种。前有符号时，其允许取值范围是-8388608~8388607；无符号时的取值范围为0~16777215。所以，最高可指定9位数字。
BIGINT 较大型的整数，分为有无符号两种。前有符号时，其允许取值范围为-9223372036854775808~9223372036854775807；无符号时的取值范围为0~18446744073709551615。最高可指定20位数字。
FLOAT(M,D) 不带符号的浮点数。M 代表显示长度，D 代表小数位数。这两个参数都不是必需参数，它们默认为10, 2，表示小数点后有2位数字，而整个数字的位数为10（包含小数位数）。FLOAT 类型的小数精度可以达到24位。
DOUBLE(M,D) 不带符号的双精度浮点数。M 代表显示长度，D 代表小数位数。这两个参数都不是必需参数，它们默认为16, 4，表示小数点后有4位数字，而整个数字的位数为 16（包含小数位数）。DOUBLE 类型的小数精度可以达到53位。DOUBLE 与 REAL 同义。
DECIMAL(M,D) 非压缩的无符号浮点数。 在未压缩十进制中，每一位十进制数都对应一个字节。需要定义显示长度（M）和小数位数（D）。DECIMAL 与 NUMERIC 同义。

日期与时间类型

MySQL 包含以下几种日期与时间类型：

DATE YYYY-MM-DD （年-月-日）格式显示的日期，取值范围从1000-01-01 到 9999-12-31。比如1973年的12月30日就存为 1973-12-30。
DATETIME 按照 YYYY-MM-DD HH:MM:SS 格式组合显示的日期与时间，取值范围从1000-01-01 00:00:00 到 9999-12-31 23:59:59。比如说1973年的12月30日下午3 : 30就存为1973-12-30 15 : 30 : 00。
TIMESTAMP 介于1970年1月1日凌晨与2037年某个时间点之间的一种时间戳。这种格式与之前的 DATETIME 格式相仿，只不过少了数字间的连字符。1973年12月30日下午3 : 30被存为19731230153000（YYYYMMDDHHMMSS）。
TIME 按照 HH:MM:SS 格式存储的时间。
YEAR(M) 用2位或4位格式存储的时间。如果把长度定为2，比如说YEAR(2)，那么可以表示从1970年到2069年的这些年份（70-69）。如果把长度定为4，YEAR(4)，则可以表示从1901年到2155年。默认长度为4。

字符串类型

虽然数字与日期类型都很有趣，但通常我们存储最多的就是字符串了。下面列出了 MySQL 中常见的字符串类型。

CHAR(M) 长度固定的字符串，长度范围从1~255个字符，比如CHAR(5)。在存储时，会向右用空格补齐指定长度。长度并非必须参数，默认长度为1。
VARCHAR(M) 长度不定的字符串，长度范围从1~255个字符。比如：CHAR(25)。在创建VARCHAR字段时，必须定义长度。
BLOB or TEXT 最大长度为65535个字符的字段。BLOB是Binary Large Objects（二进制大型对象）的缩写，专用于保存大量的二进制数据，比如图片或其他类型的文件。TEXT 类型的文件也能保存大型数据。这两者的区别在于存储数据的排序和对比方面，BLOB类型数据是大小写敏感的，而TEXT类型数据则不是。另外，不能指定它们的长度。
TINYBLOB or TINYTEXT 最大长度为255个字符的 BLOB 或 TEXT 字段。同样也不能指定它们的长度。
MEDIUMBLOB or MEDIUMTEXT 最大长度为16777215个字符的 BLOB 或 TEXT 字段。同样也不能指定它们的长度。
LONGBLOB or LONGTEXT 最大长度为4294967295个字符的 BLOB 或 TEXT 字段。同样也不能指定它们的长度。
ENUM 枚举类型，是一种很独特的列表类型。ENUM 类型的数据实际是一个包含多个固定值的列表，只能选择这些值（包括 NULL 值）。例如，如果希望某个字段包含 "A"、"B" 和 "C"，必须这样定义：ENUM ('A', 'B', 'C')，只有这些值（或 NULL 值）能够填充到该字段中。

这是今天写的一些脚本和注释

/* Author: Chenhui Wang
   Date: 2018-05-24  */
/*---------------初始化------------------*/
net start MySQL -- start the service
mysqladmin -u root -p create TUTORIALS -- 用root权限创造名为tutorial的database
mysqladmin -u root -p drop TUTORIALS -- 用root权限删除名为tutorial的database

mysql -D samp_db -u root -p -- login to the atabase
create database samp_db character set gbk;
show databases; -- display current databases
use samp_db; -- use the databse called samp_db

show tables; -- 展示目前的relation
show columns from students; -- 展示table中列的信息
show index from students; -- 展示table中index的信息
show table status; -- 报告MySQL DBMS的性能及统计的详细信息

-- 创建一个名为tutorails_tbl的table(关系)
create table tutorials_tbl(
	tutorial_id int not null auto_increment, -- not null 指值不能为null
	-- 字段属性 AUTO_INCREMENT 告诉 MySQL 继续为 id 字段增加下一个可能的数值
	tutorial_title varchar(100) not null,
	tutorial_author varchar(40) not null,
	submission_date date,
	primary key (tutorial_id) -- primary key会将tutorial_id定义为主键
);
-- 注意，主键不是必须的，查询主键可以用

show keys from tutorials_tbl where key_name="primary";

-- 插入insert的用法

/*
利用 INSERT INTO 命令为表插入数据的一般语法如下所示：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
*/
insert table tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) values (10, "ass", "link", NOW())
insert table tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) values (11, "Ass", "link", NOW())
insert table tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) values (12, "Asshole", "link", NOW())
-- 选择select的用法

/*
SELECT field1, field2,...fieldN table_name1, table_name2...  
[WHERE condition1 [AND [OR]] condition2.....
[OFFSET M ][LIMIT N]
*/
select * from tutorials_tbl where tutorial_author="ass"; -- 会返回第2,3行结果，因为默认对大小写不敏感
select * from tutorials_tbl where binary tutorial_author="Ass"; -- 只返回第二行结果

-- 更新update的用法
/*
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
*/

update tutorials_tbl set tutorial_title="Asshole" where binary tutorial_author="Van Darkholme";

-- 删除delete的用法
/*
DELETE FROM table_name [WHERE Clause]
如果未指定 WHERE 子句，将删除指定表中的所有记录。
可以在 WHERE 子句中指定任意条件。
可以一次删除一张表中的所有记录。
*/

delete from tutorials_tbl where tutorial_id=3;

-- like元匹配的用法
/*
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

如果 SQL 的 LIKE 子句带有 % 字符，则相当于 UNIX 中的元字符（*），在命令行中列出所有的文件或目录。
如果 LIKE 子句不带 % 字符，则就相当于 WHERE 子句中带有等号的情况。
*/

select * from tutorials_tbl where tutorial_author like "%e"; -- 模糊匹配，会返回以e结尾的tutorial_author元组
select * from tutorials_tbl where tutorial_author like "e%"; -- 模糊匹配，会返回以e开头的元组
select * from tutorials_tbl where binary tutorial_author like "e%"; -- 模糊匹配，会返回以小写e开头的元组

-- order by排序
/*
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
默认是ASC升序排列，可以用DESC进行降序排列
*/
select * from tutorials_tbl order by tutorial_author ASC; -- 根据tutorial_author字母进行排序，ASC代表ascending
select * from tutorials_tbl where tutorial_author like "%lme" order by tutorial_author ASC; -- 选取tutorial_author符合条件的进行排序

-- join连接操作
/*
tutorials_tbl表
+-------------+------------------+------------------+-----------------+
| tutorial_id | tutorial_title   | tutorial_author  | submission_date |
+-------------+------------------+------------------+-----------------+
|          10 | Norwegian Wood   | Cunshang Chunshu | 2018-05-24      |
|          11 | Ass we Can!      | Van Darkholme    | 2018-05-24      |
|          12 | Motherfucker     | van darkholme    | 2018-01-01      |
|          13 | Hell of the Fame | Link Wong        | 2018-05-23      |
+-------------+------------------+------------------+-----------------+
tcount_tbl表
+------------------+----------------+
| tutorial_author  | tutorial_count |
+------------------+----------------+
| maharan          |             20 |
| Jen              |           NULL |
| John Poul        |              1 |
| Cunshang Chunshu |             10 |
| Van Darkholme    |             15 |
+------------------+----------------+
*/
select a.tutorial_id, a.tutorial_author, b.tutorial_count from tutorials_tbl a join tcout_tbl b on a.tutorial_author=b.tutorial_author
/* 这句SQL语句的意思是，将tutorials_tbl表中的tutorial_id, tutorial_author提取出来，和tcount_tbl的tutorial_count按照共有属性tutorial_author连接在一起
即author一样的，合并
下面为结果
+-------------+------------------+----------------+
| tutorial_id | tutorial_author  | tutorial_count |
+-------------+------------------+----------------+
|          10 | Cunshang Chunshu |             10 |
|          11 | Van Darkholme    |             15 |
|          12 | van darkholme    |             15 |
+-------------+------------------+----------------+
*/
select a.tutorial_count, a.tutorial_author, b.tutorial_id from tcount_tbl a join tutorials_tbl b on a.tutorial_author=b.tutorial_author;
/* 这句SQL交换了顺序，将tutorial_count放前面，tutorial_id放最后
+----------------+------------------+-------------+
| tutorial_count | tutorial_author  | tutorial_id |
+----------------+------------------+-------------+
|             10 | Cunshang Chunshu |          10 |
|             15 | Van Darkholme    |          11 |
|             15 | Van Darkholme    |          12 |
+----------------+------------------+-------------+
*/
select a.tutorial_id, a.tutorial_title, b.tutorial_count from tutorials_tbl a left join tcount_tbl b on a.tutorial_author=b.tutorial_author;
/* 这句用的是left join，虽然还是on tutorial_author，但这次用的是left join，区别在于left join会将左表中的所有author读取进来，而右表还是按照公共author来排
所以左边有的右边没有的属性，即Link Wong对应的tutorial_count 会被填上NULL(前提是tcount_tbl定义tutorial_count不能是not null)结果如下
+-------------+------------------+----------------+
| tutorial_id | tutorial_title   | tutorial_count |
+-------------+------------------+----------------+
|          10 | Norwegian Wood   |             10 |
|          11 | Ass we Can!      |             15 |
|          12 | Motherfucker     |             15 |
|          13 | Hell of the Fame |           NULL |
+-------------+------------------+----------------+
*/
select a.tutorial_id, a.tutorial_title, b.tutorial_count from tutorials_tbl a right join tcount_tbl b on a.tutorial_author=b.tutorial_author; -- 镜像结果