Mysql知识总结

 

mysql 登录命令：

mysql -uroot -pbank  这句话的意思是用root用户登录，密码是bank。

mysql -uroot -p  bank  这句话的意思是用root用户登录，bank是进入后切换到bank这个数据库，此时按下回车会提示输入密码。进入后不用use bank 来切换到bank数据库，自动就是bank数据库。

mysql -uroot -pbank   <=等价于=>  mysql -u  root -pbank    也就是说-uroot  等价于 -u   root 。不论中间是否有空格隔开都一样。代表用root这个用户进行登录。

 

查看当前时间：

mysql> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2017-10-25 22:05:38 |
+---------------------+
1 row in set (0.03 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-10-25 22:05:47 |
+---------------------+
1 row in set (0.00 sec)

之所以要使用from dual是为了兼容oracle数据库，如果只是MySQL的话，可以省略from dual；因为某些数据库服务器规定查询语句中必须包含from字句，并在其中至少指明一个表名。

 

MySQL字符型数据

字符型数据分为定长或者变长两类，不同点在于固定长度的字符串使用空格向右填充，以保证占用同样的字节数；变长字符串不需要向右填充，并且所有字节数可变。当定义一个字符串类型时，必须要指定能存放的字符串最大长度。例如，需要存储最大不超过20个字节的字符串，可以用下列方式：

　　　　char(20)  /* fixed-length */

　　　　varchar(20)  /* variable-length  */

char最大可以设置为255个字节，varchar则可以最多保存65535个字节。要存储比65535更长的字符串则要使用文本类型了。

文本数据

如果需要存储的数据超过64KB（varchar的上限），就需要使用文本类型。

　　　　文本类型　　　　　　　　　　最大能存储的字节数（B）

　　　　tinytext　　　　　　　　　　　　　　255

　　　　text　　　　　　　　　　　　　　   65535

　　　　mediumtext　　　　　　　　　　   16777215

　　　　longtext　　　　　　　　　　　　4294967295

观察可以发现，其实是用规律的，255 = 2^8 -1    ;  65535 = 2^16 -1     ; 16777215 = 2^24 -1  ; 4294967295 = 2^32 -1  ;

注意事项：

　　1.如果被装载到文本列中的数据超出了该类型的最大长度，数据会被截断。

　　2.在向文本列装载数据时，不会消除数据的尾部空格。而char类型会。

　　3.使用文本列进行排序、分组的时候，只会使用前1024个字符

　　4.这些不同的文本类型只是针对MySQL，sql Server对于大的字符型数据只提供text类型，而db2和oracle使用的数据类型是Clob。

　　5.现在MySQL允许varchar列最大容纳65535个字节了（在MysQL4中为255个字符），也就是和text一样大了，所以一般情况不会用到tinytext、text了，超过65535用mediumtext或者longtext；少于65535则使用varchar。

　　6.Oracle中，char能容纳2000个字节，varchar2能容纳4000个，sql Server中char和varchar都能容纳8000个字节。

MySQL中的浮点数

　　浮点数是用来表示实数的一种方法，它用 M(尾数) * B( 基数)的E(指数）次方来表示实数，相对于定点数来说，在长度一定的情况下，具有表示数据范围大的特点。但同时也存在误差问题。 浮点数有多种实现方法，计算机中浮点数的实现大都遵从 IEEE754 标准，IEEE754 规定了单精度浮点数和双精度浮点数两种规格：

　　单精度浮点数用4字节（32bit）表示浮点数，格式是： 1位符号位      8位表示指数   23位表示尾数 

　　双精度浮点数用8字节（64bit）表示浮点数，格式是：  1位符号位    11位表示指数   52位表示尾数  

　　IEEE754标准还对尾数的格式做了规范：d.dddddd...，小数点左面只有1位且不能为零，计算机内部是二进制，因此，尾数小数点左面部分总是1。显然，这个1可以省去，以提高尾数的精度。

下图是IEEE754的浮点数标准图：

 

　　由上图可知，单精度浮点数的尾数虽然只有23位，但是由于第一位一定是1，所以其实是用24bit表示的；

　　　　　　　　双精度浮点数的尾数虽然只有52位，但是由于第一位一定是1，所以其实是用53bit表示的；

　　

IEEE754标准中，一个规格化32位的浮点数x的真值表示为： 

　　　　x=(−1)S×(1.M)×2^e

　　　　e=E−127

其中尾数域表示的值是1.M。因为规格化的浮点数的尾数域最左位总是1，故这一位不予存储，而认为隐藏在小数点的左边。

8 位的阶码e指数为可以表达 0 到 255 之间的 256 个指数值。但是，指数可以为正数，也可以为负数。为了处理负指数的情况，实际的指数值按要求需要加上一个偏差（Bias）值作为保存在指数域中的值，单精度数的偏差值为 127；偏差的引入使得对于单精度数，实际可以表达的指数值的范围就变成 -127（0 -  偏差值127） 到 128 （255 -  偏差值127）之间（包含两端）。在本文中，最小指数和最大指数分别用 emin 和 emax 来表达。实际的指数值 -127（保存为全0）以及 +128（保存为全 1）保留用作特殊值的处理。

（1）最大正数 
单精度浮点数最大正数值的符号位S=0，阶码E=254，指数e=254-127=127，尾数M=111 1111 1111 1111 1111 1111，其机器码为：0 11111110 111 1111 1111 1111 1111 1111。

那么最大正数值: 

PosMax=(−1)S×1.M×2^e=+(1.11111111111111111111111)×2^127  ≈  3.402823e+38

 

（2）最小正数 最小正数符号位S=0，阶码E=1，指数e=1-127=-126，尾数M=0，其机器码为0 00000001 000 0000 0000 0000 0000 0000。 那么最小正数为：

PosMin=(−1)S×1.M×2e=+(1.0)×2−126  ≈  1.175494e−38

 

当使用浮点类型时，可以指定其精度（小数点左边右边所允许的数字总位数）和有效位（小数点右边所允许的数字位数）。比如float（5,2）代表的是小数点左边3位，小数点右边2位。因此，如果添加11.275会被四舍五入到11.28。最大支持添加的数字是999.99，当添加比999.99大的数字的时候，就会报错。

注意，float也可以声明为unsigned类型，但是这声明只会禁止列中存放负数，并没有改变该列存储数据的范围。

 

MySQL中的整数类型

类型　　　　　　带符号的范围　　　　　　　　无符号的范围

tinyint　　　　　　-128~127　　　　　　　　　　0~255

smallint　　　　  -32768~32767　　　　　　　　0~65535

mediumint　　　-8388608~8388607　　　　　　0~16777215

int 　　　　　　-2147483648~2147483647　　  0~4294967295

bigint　　　　-9223372036854755808　　　　0~18446744073709551615

　　　　　　~ 9223372036854755807

 

当使用tinyint时，分配1字节；使用smallint时，分配2字节；使用mediumint时，分配3字节；使用int时，分配4字节；使用bigint，分配8字节。

选择类型时，只需要确保能够容纳预期的最大数字即可，这样可以避免不必要的浪费。

 

MySQL的时间类型

 类型　　　　　　默认格式　　　　　　　　允许的值

date　　　　　　YYYY-MM-DD　　　　　　  1000-01-01~9999-12-31

datetime　　　　YYYY-MM-DD HH:MI:SS　　1000-01-01~9999-12-31 23:59:59

timestamp　　　 YYYY-MM-DD HH:MI:SS　　 1000-01-01~9999-12-31 23:59:59

year　　　　　　YYYY　　　　　　　　　　 1901~2155

time　　　　　　HHH:MI:SS　　　　　　　　-838:59:59~838:59:59

 

如上表，如果需要向默认格式为YYYY-MM-DD的日期列插入日期2017年1月23日，必须使用字符串'2017-01-23'。

timestamp类型和datetime类型一样（包括了年月日  时分秒），但是Mysql服务器可以支持在向表中添加或者修改数据行时，自动为timestamp类型列产生当前的日期和时间。

 

GMT和UTC时间

GMT时间就是著名的格林威治标准时间，其他所有地区都可以使用与GMT的差距的小时数来表示，比如GMT +8代表中国北京的时间。

UTC时间是克服了GMT时间的一些缺陷后，用原子钟提供的时间，比GMT更加准一些，其实还是以格林威治作为标准的。北京的时间可以表示为UTC +8

简单地理解他俩，GMT旧一些，不太准；UTC新一些，用的技术更加高级，更加准。其他区别可以忽略不计。

 

现在计算机中都用了更加准确的utc时间，所以MySQL中可以使用utc_timestamp()来获取。

mysql> select utc_timestamp();
+---------------------+
| utc_timestamp() |
+---------------------+
| 2017-10-26 02:42:02 |
+---------------------+
1 row in set (0.02 sec)

 

可以看到返回的格式是YYYY-MM-DD HH:MI:SS。Sql Server可以用getutcdate（）函数获取。

 

查看全局时区和会话时区

mysql> SELECT @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)

 

 可以看到，结果值为system，这表示服务器根据数据库所在地使用相应的时区设置。

 

假如你坐在中国，远程一台纽约的mysql服务器，可以通过执行下面的指令暂时改变当前会话的时区设置：

　　SET time_zone = '+08:00'；

mysql> select @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM　　　　　   |　　　　 +08:00　　　  |
+--------------------+---------------------+
1 row in set (0.00 sec)

此时会话中显示的所有日期都符合北京时间。

 

如果需要向datetime或者timestamp列中添加一条2017年1月23日下午15:00的数据，可以使用下列的字符串：

　　　　‘2017-01-23 15:00:00’

　　　　'2017/01/23 15,00,00'

　　　　'2017,01,23 15,00,00'

　　　　'20170123150000'

以上的字符串都可以产生日期，但是会比较死板，必须要有固定的格式，缺一不可。

产生日期的函数

 如果你现在手上的数据不是像上边一样的格式，而是其他类型的字符串，比如‘September 17, 2008’ 或者'September  2008 , 17' 等等更加随意的排列方式，那么怎么办呢，要不要一个一个转化成上边的那种格式呢，显然，MySQL也考虑到了这种情况，所以提供了一个str_to_date（）函数，可以将这种类型的数据不用修改成上边那种格式就能直接插入到数据库中。是不是很方便呢，来看看怎么用的。

　　UPDATE  TABLE1

　　SET  birth_date = STR_TO_DATE('September  17 , 2008'  ,  '%M  %d , %Y')

　　WHERE cust_id = 9999;

 

　　或者

　　

　　UPDATE  TABLE1

　　SET  birth_date = STR_TO_DATE('September  2008 , 17'  ,  '%M  %Y , %d')

　　WHERE cust_id = 9999;

这样就可以插入到数据库了,当然，此时在数据库中保存的格式就变成　2008-09-17 00:00:00 这种格式的了.

用这个str_to_date()函数的时候，需要查阅下边的表，来知道%M 代表什么？%Y代表什么？ 就像C语言中%d代表整数，%f代表浮点数一样，多用就自然记住了。

 

日期格式表

格式部件 　　　　　　　　　　      描述

%M　　　　　　　　　　 月名称（January,February,March.....）

%m　　　　　　　　　　 月序号(01  ~  12)

%d　　　　　　　　　　  日序号（01  ~  31）

%j　　　　　　　　　　   日在一年中的序号（001  ~  366）

%W　　　　　　　　　　星期名称（Sunday , Monday,Tuesday....）

%Y　　　　　　　　　　 4位数表示的年份（2011，2012 , 2013.....）

%y　　　　　　　　　　  两位数代表的年份（11,12,13......）

%H　　　　　　　　　　 小时（00  ~ 23）

%h　　　　　　　　　　  小时（01  ~ 12）

%p　　　　　　　　　　  A.M. 或者 P.M.

%i　　　　　　　　　　   分钟(00  ~  59 )

%s　　　　　　　　　　  秒钟(00 ~ 59 )

%f 　　　　　　　　　　  微秒(000000  ~ 999999 ）

 

注意：

　　1.str_to_date()函数将根据格式字符串的内容自动决定返回datetime，date 或者 time 类性值。举例来说，如果格式字符串只包含%H  %i或者 %s，那么将返回time值。

　　2.Oracle数据库，to_date()函数和mysql的str_to_date()函数具有同样的功能。

　　3.可以使用select  current_date()返回当前date，使用select current_time()返回当前time，使用select current_timestamp()返回当前timestamp/datetime。

 

操作时间数据的几个函数

　　1.date_add()函数

　　  2.last_day()函数

　　  3.convert_tz()函数

　　  4.dayname()函数

　　  5.extract()函数

　　  6.datediff()函数

　　下面依次来看一下这几个函数。

 

1.date_add()函数

　　MySQL的date_add()函数可以为指定的日期增加任意一段时间间隔（如天、月、年），并产生另外一个日期。

 

#在现在的时间基础上添加5天。

mysql> select date_add(current_timestamp(),interval 5 day);
+----------------------------------------------+
| date_add(current_timestamp(),interval 5 day) |
+----------------------------------------------+
| 2017-10-31 11:58:00 |
+----------------------------------------------+
1 row in set (0.00 sec)

#在现在的时间的基础上增加3小时25分钟0秒

mysql> select date_add(current_timestamp(),interval '3:25:00' hour_second);
+--------------------------------------------------------------+
| date_add(current_timestamp(),interval '3:25:00' hour_second) |
+--------------------------------------------------------------+
| 2017-10-26 15:23:53 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

#在现在的时间的基础上增加了1年12月

mysql> select date_add(current_timestamp(),interval '1-12' year_month);
+----------------------------------------------------------+
| date_add(current_timestamp(),interval '1-12' year_month) |
+----------------------------------------------------------+
| 2019-10-26 11:59:23 |
+----------------------------------------------------------+
1 row in set (0.00 sec)

 #在现在的时间基础上增加5分钟10秒

mysql> select date_add(current_timestamp(),interval '5:10' minute_second);
+-------------------------------------------------------------+
| date_add(current_timestamp(),interval '5:10' minute_second) |
+-------------------------------------------------------------+
| 2017-10-26 12:07:59 |
+-------------------------------------------------------------+
1 row in set (0.02 sec)

常用的时间间隔类型表

间隔名称　　　　　　　　　　　　描述

second　　　　　　　　　　　　　秒数

minute　　　　　　　　　　　　　分钟数

hour　　　　　　　　　　　　　　小时数

day　　　　　　　　　　　　　　  天数

month　　　　　　　　　　　　　 月份数

year　　　　　　　　　　　　　　年份

minute_second　　　　　　　　    分钟数和秒数，中间用“：”隔开

hour_second 　　　　　　　　　   小时数、分钟数、秒数，中间用“：”隔开

year_month 　　　　　　　　　　 年份和月份，中间用“-”隔开

 

2.last_day()函数

　　要获取当前时刻的月底是哪一天，可以使用last_day（）函数，这个函数的作用就是简单地返回传入的日期的月末日期。

mysql> select last_day(current_timestamp());
+-------------------------------+
| last_day(current_timestamp()) |
+-------------------------------+
| 2017-10-31 |
+-------------------------------+
1 row in set (0.00 sec)

可以像上边一样传入datetime类型的值，也可以传入date的类型值，都一样会返回date类型的值。

 

3.convert_tz()函数

该函数能够将某个时区的datetime类型的值转化为另外一个时区的对应时间。在Oracle中，这个函数叫new_time()函数。

mysql> select convert_tz('2014-09-28','US/Eastern','UTC');
+---------------------------------------------+
| convert_tz('2014-09-28','US/Eastern','UTC') |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
1 row in set (0.00 sec)

可以看到此时的返回值为null，是因为我的电脑上没有安装mysql的时区数据，就是说，系统现在不认识US/Eastern 这个地区，UTC地区也不认识。所以返回了一个空值。

 

4.dayname()函数

dayname()函数可以直接返回传入的日期那天是星期几。

mysql> select dayname('2008-09-18');
+-----------------------+
| dayname('2008-09-18') |
+-----------------------+
| Thursday |
+-----------------------+
1 row in set (0.00 sec)

比如上面的例子中，返回了2008年9月18日那天，是星期四。

除了dayname，还有monthname()函数，同理可以返回传入日期代表的月份的名称。比如下面的例子：

mysql> select monthname('2008-09-18');
+-------------------------+
| monthname('2008-09-18') |
+-------------------------+
| September |
+-------------------------+
1 row in set (0.00 sec)

 很简单就能理解，不多说了。

 

5.extract()函数

该函数用于从日期或者时间中提取出某一部分内容。

mysql> select extract(hour_second from '2008-09-18 22:19:05');
+-------------------------------------------------+
| extract(hour_second from '2008-09-18 22:19:05') |
+-------------------------------------------------+
| 221905 |
+-------------------------------------------------+
1 row in set (0.02 sec)

mysql> select extract(year_month from '2008-09-18 22:19:05');
+------------------------------------------------+
| extract(year_month from '2008-09-18 22:19:05') |
+------------------------------------------------+
| 200809 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select extract(minute from '2008-09-18 22:19:05');
+--------------------------------------------+
| extract(minute from '2008-09-18 22:19:05') |
+--------------------------------------------+
| 19 |
+--------------------------------------------+

 1 row in set (0.00 sec)

 

6.datediff()函数

该函数用于传入两个时间，求出他们之间的时间间隔。

比如

mysql> select datediff('2009-08-28','2009-08-24');
+-------------------------------------+
| datediff('2009-08-28','2009-08-24') |
+-------------------------------------+
| 4 |
+-------------------------------------+
1 row in set (0.00 sec)

可以看到该日期不包含最后一天，即24,25,26,27.不过，也有可能是25,26,27,28，总之是包含其中一天，不包含两天。

注意点：

　　datediff函数会忽略参数中的时间值，就算我把前一个日期设置为一天的最后一秒，后一个日期设置为一天的第一秒，也不会影响计算结果。

比如：

mysql> select datediff('2009-08-28 23:59:59','2009-08-24 00:00:01');
+-------------------------------------------------------+
| datediff('2009-08-28 23:59:59','2009-08-24 00:00:01') |
+-------------------------------------------------------+
| 4 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

 可以发现，该函数并没有关注时间，而是选择直接忽略了。

假如交换一下第一个参数和第二个参数的话，就会发现日期的值变成了负数。

 

MySQL的查询语句

1.select子句

query字句表

字句名称　　　　　　　　　　使用目的

select　　　　　　　　　　 确定结果集中应该包含哪些列

from　　　　　　　　　　   指明所要提取数据的表，以及这些表示如何连接的

where　　　　　　　　　　 过滤掉不需要的数据

group by　　　　　　　　　用于对具有相同列值的行进行分组

having　　　　　　　　　　过滤掉不需要的组

order by　　　　　　　　　 按一个或多个列，对最后结构集中的行进行排序

 

从最简单的例子开始：

（1）选择一个 表中所有的数据 

（2）选择其中的某一列或者多列

如果数据库限制了只能返回from字句后面各个表所包含的列，就显得相当乏味了。幸运的是，我们可以在select字句中加上一些“调料”，例如

　　1.字符，比如数字或字符串

　　2.表达式，比如transaction.amount*-1

　　3.调用内建函数，比如round（transaction.amount,2）;

　　4.用户自定义的函数调用。

（4）展示对于刚才的Department表，在查询中添加一些调料，比如使用自定义的列名、字符、表达式、内建函数调用：

(5)调用更多的内建函数

可以看到三个内建函数分别返回不同的值，version代表的是数据库版本，user代表的是当前登录的用户名称，database代表的是现在正在使用的数据库的名称。

现在先在department表中先插入一个同名的公寓，插入语句和插入后结果如下：

（6）去除重复的行

如果此时只选择name列的话，会有四个值，但是有两个Loans重复的，我需要独一无二的，重复值不论出现多少次，我都只取一次，那么就需要使用到distinct关键字了。

注意此处使用的distinct，添加这个关键词对小数据量没啥影响，但是如果数据量大了之后，性能影响较大。原因是要产生无重复的结果集需要首先对数据排序，这对于大的结果集来说是相当耗时的。因此不要为了确保去除重复行而随意地使用distinct，而是应该先了解所使用的数据是否可能包含重复行，以减少对distinct的不必要的使用。

 

2.from子句

from子句定义了查询中所使用的表，以及连接这些表的方式。

当使用术语“表”时，往往想到的是实实在在存在的表格，就像上面的Department表。其实from子句后边不仅仅可以跟这种实体表（用create  table创建），还可以从临时表（子查询返回的表）以及虚拟表（使用create  view 子句所创建的视图）中返回数据。

（1）从子查询返回的表中查询数据

上图中会先执行括号中的select，然后会在内存中生成一个虚的表，这个虚的表叫d。然后再执行外层的select，从这个虚的表中选择相应的dept_id列和name列。

假如这个虚拟表在选择的时候给一个别名的话，外层也就只能通过这个别名来选择。比如：

可以看到，内部选择的时候，给dept_id出了个别名，叫做id，给name出了一个别名，叫做department_name。外层再选择的时候，就得通过这个新的别名去虚表d中选择了。

 

(2)从视图中返回数据

下面先定义一个查询Department表的视图，在Department表的基础上多了一列当前操作时候的时间，并将dept_id重命名为id，将name重命名为department_name。

然后从这个视图返回所有的数据：

可以发现，从视图选择数据成功了。

 

3. where子句

现在假如我有一个员工表，主要有4个字段，emp_id（员工id）、fname(姓）、lname（名字）、start_date（入职时间）。

让我们一起解决一些问题：

　　（1）如何获得员工id为99号的员工的所有信息？

　　（2）如何获得id 大于等于20，小于等于40的员工信息？（请用两种不同方式分别实现）

　　（3）如何获得11,45,99,124号员工的信息？（用两种方式实现）

　　（4）如何获得除了11,45,99,124号员工外，其他员工的信息？（用两种方式实现）

　　（5）如何获得入职时间在2011年10月1日前的，并且姓 ‘李’的所有员工？（用三种方式实现）

　　（6）如何获取所有emp_id的末尾为1的所有记录，比如emp_id为1,11,21,31.。。。101，121，。。。1001，1011，。。。。。（用三种方式来实现）

　　（7）如何获取101,111,121,131,141,151,161,171,181,191这几个员工的记录？（分别用通配和正则来实现）

上面的这些问题基本涵盖了where语句中的所有知识点，大家可以先试试看，按照题目的描述和括号中的条件来实现。

　　思考后，再查看下面的答案。

 

 

　　答案：

　　（1）select * from employee where emp_id = 99;

　　（2）select * from employee where emp_id between 20 and 40;

　　　　  select * from employee where emp_id >=20 and emp_id <=40;

　　（3）select * from employee where emp_id = 11 or emp_id = 45 or emp_id = 99 or emp_id = 124;

　　　　  select * from employee where emp_id in (11,45,99,124);

　　（4）select * from employee where emp_id !=11 and emp_id !=45 and emp_id != 99 and emp_id !=124;

　　　　  select * from employee where emp_id not in (11,45,99,124);

　　（5）select * from employee where start_date<'2011-10-01'  and fname = '李';

　　　　  select * from (select * from employee where fname = '李' )  d  where d.start_date < '2011-10-01';

　　　　  select * from employee where emp_id in (select emp_id from employee where fname = '李' ) and start_date < '2011-10-01';

　　（6）select * from employee where emp_id like '%1';

　　　　  select * from employee where emp_id regexp '.*1$';

　　　　  select * from employee where right(emp_id,1) = 1;

　　（7）select * from employee where emp_id like '1_1;'

　　　　  select * from employee where emp_id regexp '1.1';