MySQL笔记03

-------------------帮助的使用----------------------
使用MySQL安装后自带的帮助文档

--按照层次看帮助
显示所有可供查询的的分类：？contents
“? 类别名称”的方式针对用户感兴趣的内容做进一步的查看
example:
mysql> ? data types
--查看 MySQL 中都支持哪些数据类型
mysql> ? BINARY
--查看类型的具体介绍
Name: 'BINARY'
Description:
BINARY(M)


The BINARY type is similar to the CHAR type, but stores binary byte
strings rather than nonbinary character strings. M represents the
column length in bytes.


URL: http://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html

--快速查阅帮助
快速查阅某项语法--MySQL对空格貌似比较敏感
example:
mysql> ? create table

--常用的网络资源
MySQL官网资源下载：https://dev.mysql.com/downloads/
MySQL 数据库及工具的在线手册：https://dev.mysql.com/doc/
MySQL已发布的bug列表及bug报告：http://bugs.mysql.com/
关于MySQL的最新消息：http://www.mysql.com/news-and-events/newsletter/



-------------------查看版本号----------------------
关于MySQL 8.0.0：http://www.linuxprobe.com/mysql-8-0.html


--1.使用命令行模式进入mysql会看到最开始的提示符
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.0-dmr-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


--2.命令行中使用status可以看到 
mysql> status;
--------------
mysql  Ver 14.14 Distrib 8.0.0-dmr, for Win64 (x86_64)


Connection id:          15
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         8.0.0-dmr-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 12 hours 26 min 47 sec


Threads: 1  Questions: 118  Slow queries: 0  Opens: 292  Flush tables: 2  Open tables: 268  Queries per second avg: 0.002
--------------


--3.使用系统函数 
mysql> select version(); 
+---------------+
| version()     |
+---------------+
| 8.0.0-dmr-log |
+---------------+
1 row in set (0.00 sec)


--4. $ mysql --help | grep Distrib 
mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1 


--5.5 包管理工具（根据不同系统 rh系列或则是bsd系列）
$ rpm -qa|grep mysql 
qt-mysql-4.6.2-20.el6.x86_64 
mysql-server-5.1.69-1.el6_4.x86_64 
mysql-libs-5.1.69-1.el6_4.x86_64 
mysql-5.1.69-1.el6_4.x86_64 
mysql-devel-5.1.69-1.el6_4.x86_64 




-------------------MySQL创建用户与授权方法----------------------
--在MySQL查看MySQL的安装目录
mysql> select @@basedir;
+------------------------------------------------+
| @@basedir                                      |
+------------------------------------------------+
| C:\Program Files (x86)\MySQL\MySQL Server 5.1\ |
+------------------------------------------------+
1 row in set (0.00 sec)

--创建用户及授权
CREATE USER 'lin'@'localhost' IDENTIFIED BY 'mypass';
grant all on *.* to 'lin'@'localhost';
http://www.jb51.net/article/31850.htm
https://www.cnblogs.com/bethal/p/5512755.html

-------------------MySQL执行SQL脚本的方法----------------------
1.在命令行下(未连接数据库),输入 mysql -h localhost -u root -p123456 < F:\hello world\niuzi.sql (注意路径不用加引号的!!) 回车即可.
2.在命令行下(已连接数据库,此时的提示符为 mysql> ),输入 
source F:\hello world\niuzi.sql (注意路径不用加引号的) 或者 
\. F:\hello world\niuzi.sql (注意路径不用加引号的) 回车即可

-------------------MySQL支持的数据类型----------------------
--1.数值类型
如果超出类型范围的操作，会发生 “Out of range ”错误提示。
如果不显示指定宽度则默认为 int(11)。
一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够
的空间用字符“0”填满。

--zerofill的使用
mysql> alter table t1 modify id2 int(5) zerofill;
+------------+-------+
| id1 | id2 |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)

--设置了宽度限制后，如果插入大于宽度限制的值，会不会截断或者插不进去报错?
答案：不会；
对插入的数据有任何影响，还是按照类型的实际精度进行保存。
这时，宽度格式实际已经没有意义，左边不会再填充任何的“0”字符。

--UNSIGNED：整型类型的可选属性之一
如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，
它的取值范围是正常值的下限取 0，上限取原值的 2 倍。
例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。
如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。

--AUTO_INCREMENT：整型类型的可选属性之一
在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。

--对于小数：浮点数和定点数
浮点数： float（单精度）和 double（双精度）
定点数：decimal不指定精度时，默认的整数位为 10，默认的小数位为 0。
MySQL 保存值时进行四舍五入
--浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；
--定点数如果不写精度和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

--BIT(M)
对于 BIT（位）类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，M 范围从 1～
64，如果不写则默认为 1 位。对于位字段，直接使用 SELECT 命令将不会看到结果，可以用
bin()（显示为二进制格式）或者 hex()（显示为十六进制格式）函数进行读取。

example:
select bin(t_bit),hex(t_bit) from t_book;


--2.日期时间类型
这些数据类型的主要区别如下：
--1.如果要用来表示年月日，通常用 DATE 来表示。
--2.如果要用来表示年月日时分秒，通常用 DATETIME 表示。
--3.如果只用来表示时分秒，通常用 TIME 来表示。
--4.如果需要经常插入或者更新日期为当前系统时间，则通常使用 TIMESTAMP 来表示。
--5.TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为 19 个字符。如果想要获得数字值，应在 TIMESTAMP 列添加+0。
--6.如果只是表示年份，可以用 YEAR 来表示，它比 DATE 占用更少的空间。YEAR 有 2 位或4 位格式的年。
--默认是 4 位格式。在 4 位格式中，允许的值是 1901～2155 和 0000。在
--2 位格式中，允许的值是 70～69，表示从 1970～2069 年。
--MySQL 以 YYYY 格式显示 YEAR值。

--TIMESTAMP
MySQL规定TIMESTAMP类型字段只能有一列的默认值为current_timestmap，如果强制修改，系统会报错.
TIMESTAMP还有一个特性是与时区有关：当插入日期时，会先转换为本地时区后存放；
而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。
这样，两个不同时区的用户看到的同一个日期可能是不一样的。
TIMESTAMP的取值范围为19700101080001到2038年的某一天，因此它不适合存放比较久远的日期；
可以往TIMESTAMP类型的字段插入一个很长的数字试试看

--查看当前时区
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set, 1 warning (0.01 sec)




--修改时区为东九区
mysql> set time_zone='+9:00';


--采用不同的格式将日期“2007-9-3 12:10:10”插入到 DATETIME列中
mysql> create table t6(dt datetime);
mysql> insert into t6 values('2007-9-3 12:10:10');
mysql> insert into t6 values('2007/9/3 12+10+10');
mysql> insert into t6 values('20070903121010');
mysql> insert into t6 values(20070903121010);
mysql> select * from t6;


--3.字符串类型
--1.CHAR 和 VARCHAR 类型
CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0～255 的任何值；
而 VARCHAR 列中的值为可变长字符串，长度可以指定为 0～255 （5.0.3以前）或者 65535 （5.0.3以后）之间的值。
在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格。

example:
mysql> create table vc(v varchar(4),c char(4));
mysql> insert into vc values('ab ','ab  ');
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         3 |         2 |
+-----------+-----------+
1 row in set (0.03 sec)

--2.BINARY 和 VARBINARY 类型
它们包含二进制字符串而不包含非二进制字符串
BINARY和VARBINARY的长度是字节长度而不是字符长度。 
mysql> create table t(c binary(3));
mysql> insert into t set c = 'a';
mysql> select * ,hex(c),c='a',c='a\0',c='a\0\0' from t;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0     |       0 |        1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

--3.ENUM (枚举)类型
ENUM 类型只允许从值集合中选取单个值，而不能一次取多个值
example：
mysql> create table t (gender enum('M','F'));
mysql> INSERT INTO t VALUES('M'),('1'),('f'),(NULL);
mysql> select * from t;
+--------+
| gender |
+--------+
| M |
| M |
| F |
| NULL |
+--------+
4 rows in set (0.01 sec)
--4.SET 类型
Set 和 ENUM 除了存储之外，最主要的区别在于 Set 类型一次可以选取多个成员，而 ENUM则只能选一个.
SET 类型可以从允许值集合中选择任意 1 个或多个元素进行组合，所以对于输入的值只要是
在允许值的组合范围内，都可以正确地注入到 SET 类型的列中。对于超出允许值范围的值例
如（ ' a,d,f ' ）将不允许注入到上面例子中设置的 SET 类型列中，而对于（ ' a,d,a ' ）这样包含重
复成员的集合将只取一次，写入后的结果为“a,d”，这一点请注意。

example：
mysql> create table t(col set('a','b','c','d'));
mysql> insert into t values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
mysql> select * from t;
+------+
| col |
+------+
| a,b |
| a,d |
| a,b |
| a,c |
| a |
73
+------+
5 rows in set (0.00 sec)

-------------------MySQL中的运算符----------------------
--1.算术运算符
mysql> select 0.1+ 0.3333 ,0.1-0.3333, 0.1*0.3333, 1/2,1%2;
%:用一个数除以另一个数然后取余；效果等同于取模mod(a,b)。

--2.比较运算符
除了常规的<、>、>=外，还有以下几种：
<>或!= ：不等于
<=>   ：NULL 安全的等于(NULL-safe)
BETWEEN   ：存在与指定范围
IN ：存在于指定集合
IS NULL ：为 NULL
IS NOT NULL ：不为 NULL
LIKE   ：通配符匹配
REGEXP 或 RLIKE：正则表达式匹配

* NULL 不能用于“=”比较
* “IN”运算符的使用格式为“a IN (value1,value2,…)”
* “REGEXP”运算符的使用格式为“str REGEXP str_pat”,当 str 字符串中含有 str_pat相匹配的字符串时，
 则返回值为 1，否则返回 0。
 
--3.逻辑运算符(布尔运算符)
MySQL 支持 4 种逻辑运算符
运算符 作用
NOT 或！ 逻辑非
AND 或&& 逻辑与
OR 或 || 逻辑或
XOR   逻辑异或

* mysql> select not 0, not 1, not null ;
+-------+-------+----------+
| not 0 | not 1 | not null |
+-------+-------+----------+
| 1 | 0| NULL   |
+-------+-------+----------+
1 row in set (0.00 sec)

* “XOR”表示逻辑异或。当任意一个操作数为 NULL 时，返回值为 NULL。
对于非 NULL 的操作数，如果两个的逻辑真假值相异，则返回结果 1；否则返回 0。
mysql> select 1 xor 1 ,0 xor 0,1 xor 0,0 xor 1,null xor 1;
+---------+---------+---------+---------+------------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | 0 xor 1 | null xor 1 |
+---------+---------+---------+---------+------------+
| 0  | 0| 1  | 1| NULL |
+---------+---------+---------+---------+------------+
1 row in set (0.00 sec)

--4.位运算符
位运算符一般用于加密，或者提高程序的性能等

运算符 作用
&   位与（位 AND）
|   位或 （位 OR ）
^   位异或（位 XOR）
~   位取反
>> 位右移
<< 位左移

* 十  二
2->10
 & 3->11
 --------
   10 -> 十
 --------
2 -> 二
 
* 异或^的规律之一: a = a^b^b;
* 位取反~：
mysql> select ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1                   | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 |                     1 |
+----------------------+-----------------------+
1 row in set (0.00 sec)

在 MySQL 中，常量数字默认会以 8 个字节来表示，8 个字节就是 64 位，常量 1 的二进制表示为 63 个“0”
加1个“1”，位取反后就是63个“1”加一个“0”，转换为二进制后就是18446744073709551614。
mysql> select bin(18446744073709551614);
+------------------------------------------------------------------+
| bin(18446744073709551614)   |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

--5.运算符的优先级
很多情况下我们都是用“（）”来将需要优先的操作括起来，
这样既起到了优先的作用，又使得其他用户看起来更加易于理解。


优先级顺序   运算符
1   :=
2   ||, OR, XOR
3   &&, AND
4   NOT
5   BETWEEN, CASE, WHEN, THEN, ELSE
6   =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7   |
8   &
9   <<, >>
10   -, +
11   *, /, DIV, %, MOD
12   ^
13   - (一元减号), ~ (一元比特反转)
14   !


-------------------MySQL的常用函数----------------------
--字符串函数(常用)
函数   功能
CANCAT(S1,S2,…Sn)  连接 S1,S2,…Sn 为一个字符串
INSERT(str,x,y,instr)  将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr
LOWER(str)   将字符串 str 中所有字符变为小写
UPPER(str)   将字符串 str 中所有字符变为大写
LEFT(str ,x)  返回字符串 str 最左边的 x 个字符
RIGHT(str,x)  返回字符串 str 最右边的 x 个字符
LPAD(str,n ,pad)  用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度
RPAD(str,n,pad)  用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度
LTRIM(str)   去掉字符串 str 左侧的空格
RTRIM(str)   去掉字符串 str 行尾的空格
REPEAT(str,x)  返回 str 重复 x 次的结果
REPLACE(str,a,b)  用字符串 b 替换字符串 str 中所有出现的字符串 a
STRCMP(s1,s2)  比较字符串 s1 和 s2
TRIM(str)   去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)  返回从字符串 str x 位置起 y 个字符长度的字串

mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008                         | beijing                      |
+------------------------------+------------------------------+
1 row in set (0.03 sec)

--数值函数
MySQL 中的常用数值函数
函数   功能
ABS(x)   返回 x 的绝对值
CEIL(x)   返回大于 x 的最大整数值
FLOOR(x)   返回小于 x 的最大整数值
MOD(x，y)   返回 x/y 的模
RAND()   返回 0 到 1 内的随机值(不用带参数)
ROUND(x,y)   返回参数 x 的四舍五入的有 y 位小数的值
TRUNCATE(x,y)  返回数字 x 截断为 y 位小数的结果

--日期和时间函数
MySQL 中的常用日期时间函数
函数   功能示例
CURDATE()   返回当前日期 (2017-11-22)
CURTIME()   返回当前时间 (21:28:33)
NOW()   返回当前的日期和时间 (2017-11-22 21:28:38)
UNIX_TIMESTAMP(date)  返回日期 date 的 UNIX 时间戳 select UNIX_TIMESTAMP(now());
FROM_UNIXTIME  返回 UNIX 时间戳的日期值 select FROM_UNIXTIME(1184134516);
WEEK(date)   返回日期 date 为一年中的第几周 select WEEK(now()),YEAR(now());
YEAR(date)   返回日期 date 的年份
HOUR(time)   返回 time 的小时值 select HOUR(CURTIME()),MINUTE(CURTIME());
MINUTE(time)  返回 time 的分钟值
MONTHNAME(date)  返回 date 的月份名 select monthname(now());
DATE_FORMAT(date,fmt) 返回按字符串 fmt 格式化日期 date 值 select DATE_FORMAT(now(),'%M,%D,%Y');
DATE_ADD(date,INTERVAL expr type)  返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)  返回起始时间 expr 和结束时间 expr2 之间的天数
select DATEDIFF('2008-08-08',now());--计算出当前距离 2008 年 8 月 8 日的奥运会开幕式还有多少天


unix时间戳(Unix timestamp)是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。[1] 
Unix时间戳（英文为Unix epoch, Unix time, POSIX time 或 Unix timestamp）
是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。
UNIX时间戳的0按照ISO 8601规范为 ：1970-01-01T00:00:00Z.
一个小时表示为UNIX时间戳格式为：3600秒；一天表示为UNIX时间戳为86400秒，闰秒不计算。
在大多数的UNIX系统中UNIX时间戳存储为32位，这样会引发2038年问题或Y2038。


--第 1 列返回了当前日期时间，第 2 列返回距离当前日期31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。
mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;

--第 1 列返回了当前日期时间，第 2列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间。
mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,
date_add(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;


MySQL 中的日期时间格式
格式符   格式说明
%S,%s 两位数字形式的秒（00,01,...,59）
%i 两位数字形式的分（00,01,...,59）
%H   两位数字形式的小时，24 小时（00,01,...,23）
%h,%I 两位数字形式的小时，12 小时（01,02,...,12）
%k 数字形式的小时，24 小时（0,1,...,23）
%l   数字形式的小时，12 小时（1,2,...,12）
%T 24小时的时间形式（hh:mm:ss）
%r   12小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
%p AM 或 PM
%W 一周中每一天的名称（Sunday,Monday,...,Saturday）
%a   一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d   两位数字表示月中的天数（00,01,...,31）
%e   数字形式表示月中的天数（1,2，...,31）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...）
%w   以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
%j   以 3 位数字表示年中的天数（001,002,...,366）
%U   周（0,1,52），其中 Sunday 为周中的第一天
%u   周（0,1,52），其中 Monday 为周中的第一天
%M 月名（January,February,...,December）
%b   缩写的月名（January,February,...,December）
%m   两位数字表示的月份（01,02,...,12）
%c   数字表示的月份（1,2,...,12）
%Y   4 位数字表示的年份
%y   两位数字表示的年份
%%   直接值“%”

--流程函数
MySQL 中的流程函数
函数   功能
IF(value,t f)  如果 value 是真，返回 t；否则返回 f;(有点像java中的三目运算符)
+-----------------------------+------------------------------------+
IFNULL(value1,value2)  如果 value1 不为空返回 value1，否则返回 value2
+-----------------------------+------------------------------------+
CASE WHEN [value1]如果 value1 是真，返回 result1，否则返回 default
THEN[result1]…ELSE[default]END
+-----------------------------+------------------------------------+
CASE [expr] WHEN [value1]如果 expr 等于 value1，返回 result1，否则返回 default
THEN[result1]…ELSE[default]END

IFNULL(value1,value2)函数：这个函数一般用来替换 NULL 值的，我们知道 NULL 值是不
能参与数值运算的，下面这个语句就是把 NULL 值用 0 来替换。

mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00   |
| 2000.00          |
| 3000.00          |
| 4000.00          |
| 5000.00          |
| 0.00           |
+------------------+
6 rows in set (0.00 sec)

mysql> select case when salary<=2000 then 'low' else 'high' end from salary;

mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
--MySQL 中的其他常用函数
函数   功能
DATABASE()   返回当前数据库名
VERSION()   返回当前数据库版本
USER()   返回当前登录用户名
INET_ATON(IP)  返回 IP 地址的网络字节序表示
INET_NTOA(num)  返回网络字节序代表的 IP 地址
PASSWORD(str)  返回字符串 str 的加密版本，一个 41 位长的字符串；
MD5()   返回字符串 str 的 MD5 值；常用来对应用中的数据进行加密

mysql> select INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+
1 row in set (0.00 sec)


--PASSWORD(str) 
返回字符串 str 的加密版本，一个 41 位长的字符串；
此函数只用来设置系统用户的密码，但是不能用来对应用的数据加密。如果应用方面有加密的需求，可以使用 MD5 等加密函数来实现。

-------------------查看 mysql 64位 还是 32位----------------------
mysql> show variables like '%version_%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| slave_type_conversions  |                              |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set, 1 warning (0.11 sec)