MySQL

重要概念：DBS包括DB和DBMS 

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

一、连接：

C:\Users\11405>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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.

二、创建数据库：

mysql> create database bd1802 default character set utf8;
Query OK, 1 row affected (0.00 sec)

三、修改命令提示符界面显示编码集：

mysql> set names GBK;
Query OK, 0 rows affected (0.00 sec)

MySQL中的数据类型及其说明：

类型	大小	范围（有符号）	用途
TINYINT	1  字节	(-128，127)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	极大整数值
FLOAT	4 字节	单精度，不规定长度时，默认是(6,2)但整数位可以超出4位。	浮点型
DOUBLE	8 字节	双精度	浮点型
DECIMAL		DECIMAL(M,D）M代表数字总共位数，D代表小数位数。规定长度后，整数位最长为M-D。	浮点型
DATE	3字节	1000-01-01/9999-12-31  YYYY-MM-DD	日期值
TIME	3字节	'-838:59:59'/'838:59:59' HH:MM:SS	时间值或持续时间
YEAR	1字节	1901/2155    格式：YYYY	年份值
DATETIME	8字节	1000-01-01 00:00:00/9999-12-31 23:59:59    YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	8字节	1970-01-01 00:00:00/2037 年某时    YYYYMMDD HHMMSS	混合日期和时间值，时间戳
CHAR	0-255字节	char(10)	定长字符串
VARCHAR	0-65535 字节		变长字符串
TINYBLOB	0-255字节		不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节		短文本字符串
BLOB	0-65535字节		二进制形式的长文本数据
TEXT	0-65535字节		长文本数据
MEDIUMBLOB	0-16777 215字节		二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节		中等长度文本数据
LOGNGBLOB	0-4 294 967 295字节		二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节		极大文本数据

dual的概念：

在没有引用表的情况下，允许将dual指定为样本表名，dual纯粹是为了方便人们要求所有SELECT语句都应该有来自和可能的其他子句。MySQL可能忽略这些子句。如果没有其它参照表，MySQL就不需要dual表。

例如：select 1+2+2*3 from dual;或者select 1+2+2*3；

MySQL常用函数：

一、常用的字符串函数：
1、ascii(str):返回指定字符串的第一个字符的ascii码，字符串为空字符串时，返回0 ，字符串为null时，返回null
        例如：select ascii('你好');返回的是第一个字符的字节码的第一个字节。
2、ord(str):如果字符串的第一个字符是单字符时，与函数ascii(str)一样。如果不是单字节。如 UTF8中，是三个字节。 返回值为： ((first byte ascii code)*256+(second byte ascii code))[*256+third byte asciicode...] 
3、conv(n,from_base,to_base),将from_base进制中的数n转成to_base进制下的表示方式

mysql> select conv('f',16,2);--16进制中的字符a转成2进制来表示
+----------------+
| conv('f',16,2) |
+----------------+
| 1111           |
+----------------+
1 row in set (0.00 sec)

4、bin(n),oct(n),hex(n)：将数值n转成对应的二进制，八进制，十六进制

mysql> select bin(13),oct(100),hex(128);--查看十进制数 13的二进制，100的八进制，128的十六进制
+---------+----------+----------+
| bin(13) | oct(100) | hex(128) |
+---------+----------+----------+
| 1101    | 144      | 80       |
+---------+----------+----------+
1 row in set (0.00 sec)

5、char(n...):返回多个ascii码组成的字符串。

mysql> select char(104,101,108,108,111);
+---------------------------+
| char(104,101,108,108,111) |
+---------------------------+
| hello                     |
+---------------------------+
1 row in set (0.00 sec)

6、concat(str1,str2,...):将多个字符串拼接成一个字符串，如果有一个参数为null,返回的就是null。
7、length(str)/octet_length(str)：返回的是默认字符集下的字符串的所有字节数。
8、char_length(str)/character_length(str):这两个函数都是返回字符串的字符长度。

mysql> select length('上海自来水来自海上'),octet_length('上海自来水来自海上');
+------------------------------+------------------------------------+
| length('上海自来水来自海上')   |  octet_length('上海自来水来自海上') |
+------------------------------+------------------------------------+
|                           18 |                                 18 |
+------------------------------+------------------------------------+
1 row in set (0.00 sec)

mysql> select character_length('上海自来水来自海上'),char_length('上海自来水来自海上');
+----------------------------------------+-----------------------------------+
| character_length('上海自来水来自海上')   | char_length('上海自来水来自海上')  |
+----------------------------------------+-----------------------------------+
|                                      9 |                                 9 |
+----------------------------------------+-----------------------------------+
1 row in set (0.04 sec)

9、locate(substr,str)/position(substr in str)/instr(str,substr)；返回substr在str第一次出现的位置。如果没有，返回0。

mysql> select locate('zuo','no zuo no die'), position('bar' in 'foobarfoobarfoo'), instr('foobarfoobarfoo','baa');
+-------------------------------+--------------------------------------+--------------------------------+
| locate('zuo','no zuo no die') | position('bar' in 'foobarfoobarfoo') | instr('foobarfoobarfoo','baa') |
+-------------------------------+--------------------------------------+--------------------------------+
|                             4 |                                    4 |                              0 |
+-------------------------------+--------------------------------------+--------------------------------+
1 row in set (0.00 sec)

10、lpad(str,len,padstr)/rpad(str,len,padstr);在字符串str的左边/右边添加padstr,添加后的总长度为len。可称之为左补全/右补全函数, 右对齐/左对齐函数。

#查询emp表中的员工姓名，右对齐，总长度为20。
mysql> select lpad(ename,20,' ') from emp;
+----------------------+
| lpad(ename,20,' ')   |
+----------------------+
|                SMITH |
|                ALLEN |
|                 WARD |
|                JONES |
|               MARTIN |
|                BLAKE |
|                CLARK |
|                SCOTT |
|                 KING |
|                ADAMS |
|                JAMES |
|                 FORD |
|               MILLER |
|              IRONMAN |
|             SUPERMAN |
+----------------------+
15 rows in set (0.00 sec)

11、left(str,len)/right(str,len);返回字符串左端/右端的len个字符。

#获取'helloworld'的左端和右端的4个字符。
mysql> select left('helloworld',4), right('helloworld',4);
+----------------------+-----------------------+
| left('helloworld',4) | right('helloworld',4) |
+----------------------+-----------------------+
| hell                 | orld                  |
+----------------------+-----------------------+
1 row in set (0.00 sec)

12、substring(str,pos)/substring(str from pos);截取str的子串，从pos下标开始截取到最后。

#截取"no zuo no die"，从下标4开始截取;截取"no zuo no die"的最后六个字符。
mysql> select substring('no zuo no die',4),substring('no zuo no die',-6);
+------------------------------+-------------------------------+
| substring('no zuo no die',4) | substring('no zuo no die',-6) |
+------------------------------+-------------------------------+
| zuo no die                   | no die                        |
+------------------------------+-------------------------------+
1 row in set (0.00 sec)

13、substring_index(str,delim,count);截取str，截取到第count个分隔符delim。

#截取'www.mysql.com'，截取到第二个".";截取'www.mysql.com',从倒数第二个'.'开始截取。
mysql> select substring_index('www.mysql.com','.',2),substring_index('www.mysql.com','.',-2);
+----------------------------------------+-----------------------------------------+
| substring_index('www.mysql.com','.',2) | substring_index('www.mysql.com','.',-2) |
+----------------------------------------+-----------------------------------------+
| www.mysql                              | mysql.com                               |
+----------------------------------------+-----------------------------------------+
1 row in set (0.00 sec)

14、ltrim(str)/rtrim(str)；删除str的左端/右端的空格。
15、trim([[both | leading | trailing] [remstr] from] str);删除str里两端的remstr。左右两端由 both|leading|trailing来控制。

#删除'    bar  '字符串的左右空格。删除' hello '前后的空格。
mysql> select ltrim(rtrim('    bar  ')), length(ltrim(rtrim('    bar  '))),trim(' hello '),trim(both ' ' from ' hello ');
+---------------------------+-----------------------------------+-----------------+-------------------------------+
| ltrim(rtrim('    bar  ')) | length(ltrim(rtrim('    bar  '))) | trim(' hello ') | trim(both ' ' from ' hello ') |
+---------------------------+-----------------------------------+-----------------+-------------------------------+
| bar                       |                                 3 | hello           | hello                         |
+---------------------------+-----------------------------------+-----------------+-------------------------------+
1 row in set (0.00 sec)

16、replace(str,from_str,to_str)；使用to_str替换掉str中的from_str。

#使用'#' 替换掉'hellohello'里的"ll";
mysql> select replace('hellohello','ll','#');
+--------------------------------+
| replace('hellohello','ll','#') |
+--------------------------------+
| he#ohe#o                       |
+--------------------------------+
1 row in set (0.00 sec)

17、insert(str,pos,len,newstr);使用newstr替换str中的一部分,这部分从pos下标开始，len个。

# 使用"mysql"替换掉"i like you"中的'you';
mysql> select insert('i like you',8,3,'mysql');
+----------------------------------+
| insert('i like you',8,3,'mysql') |
+----------------------------------+
| i like mysql                     |
+----------------------------------+
1 row in set (0.00 sec)

18、lcase(str)/lower(str)/ucase(str)/upper(str);将字符串中的英文字母全变成大/小写。

#将‘I like yourName 3’变成全大写 /全小写。
mysql> select lower('I like yourName 3'),upper('I like yourName 3');
+----------------------------+----------------------------+
| lower('I like yourName 3') | upper('I like yourName 3') |
+----------------------------+----------------------------+
| i like yourname 3          | I LIKE YOURNAME 3          |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

19、abs(n);取参数n的绝对值。
20、sign(n);判断参数的符号。
21、mod(m,n);返回m对n的取余的结果。同(m%n),和java中的取余效果一样;
22、floor(n); 返回小于等于n的最大整数，
23、ceiling(n) ;返回大于等于n的最小整数。
24、round(n,d);对n进行四舍五入，保留d位小数。
25、pow(x,y)/power(x,y) ;计算x的y次方
28、sqrt(n);对n进行开平方。
29、rand();返回0~1.0的一个随机小数。
30、truncate(n,d);对数值n进行截断。 d为正数时，保留小数点后d位; d为0时，保留整数位;d为负数时，保留小数点前d位。

#对12345.12进行保留1位小数，保留整数位，保留到千位。
mysql> select truncate(12345.12,1),truncate(12345.12,0),truncate(12345.12,-3);
+----------------------+----------------------+-----------------------+
| truncate(12345.12,1) | truncate(12345.12,0) | truncate(12345.12,-3) |
+----------------------+----------------------+-----------------------+
|              12345.1 |                12345 |                 12000 |
+----------------------+----------------------+-----------------------+
1 row in set (0.00 sec)

31、least(n.....)/greatest(n....);统计多个参数中的最小值/最大值。

#注意:参数类型可以不一致，但是必须能互相自动转换
#统计1,2,'10','5'中最小值和最大值。
mysql> select least(1,2,'10','5'),greatest(1,2,'10','5');
+---------------------+------------------------+
| least(1,2,'10','5') | greatest(1,2,'10','5') |
+---------------------+------------------------+
| 1                   | 10                     |
+---------------------+------------------------+
1 row in set (0.00 sec)

32、dayofweek(date);查看指定日期date是所在周的第几天,返回的是数字,1--周日.......7--周六
33、weekday(date);返回指定date是所在周的周几。返回的也是数字,--0-周1........6--周日
34、dayname(date);返回指定日期是周几，注意:返回的是英文描述。
35、dayofmonth(date)/dayofyear(date);返回指定日期是当月/当年的第几天。
36、monthname(date);返回指定日期所在月的英文描述。
37、week(date,first);返回指定日期是当年的第几周.first可以取值0或1, 0表示周日是一周的第1天,1表示周一是一周的第1天。
39、year(date),month(date),hour(time),minute(time),second(time);返回指定日期或时间的时间分量。
40、extract(part from date);截取指定日期的一部分，part:year,month,day,hour,minute,second;
41、date_format(date,format) ;将指定日期date,按照自定义格式，转成字符串显示。

#格式:年: %y; 月: %M或%m；日: %d;时：H%；分：%i;秒：%s；
#将当前系统时间格式化 yyyy年mm月dd日 mm分ss秒HH时。
mysql> select date_format(now(),'%Y年%m月%d日 %i分%s秒%H');
+----------------------------------------------+
| date_format(now(),'%Y年%m月%d日 %i分%s秒%H')           |
+----------------------------------------------+
| 2018年09月05日 14分34秒20                              |
+----------------------------------------------+
1 row in set (0.00 sec)

42、curdate()/curtime();获取当前系统的日期/时间
43、now()/sysdate()/current_timestamp();获取当前系统时间:都包含年月日时分秒。
44、sec_to_time(n);将指定数值n转成时间格式

#计算一下36001转成时间格式
mysql> select sec_to_time(36001);
+--------------------+
| sec_to_time(36001) |
+--------------------+
| 10:00:01           |
+--------------------+
1 row in set (0.00 sec)

视图：

视图（View）是从一个或多个表（或视图）导出的表。

概念：视图与表不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

create view view_emp_deptno_10 as select * from emp where deptno=10;
#with check option:对视图的一种检查约束选项，如果在创建视图有此选项时，表示，只能对视图DML操作可见数据。对视图不可见的数据，是不可以通过视图进行DML操作的。

视图的分类：
简单视图:subQuery是一个表中的普通查询语句。
复杂视图:subQuery是一个可以带有分组函数的，或者关联查询的语句。
复杂视图不能对视图进行DML操作，只能进行DQL操作。

索引:

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。 

#给emp表中的empno创建索引。
create index index_emp_empno on emp(empno);