MySQL基础

　　MySQL是关系型数据库管理系统（Relational Database Management System），它是当前最流行的RDMS之一，MySQL分为社区版和企业版，由于其体积小、速度快，总拥有成本低而且开放源代码，所有很多中小型网站开发都选择MySQL作为网站数据库。

　一、开始使用

mysql -h host -u username -p 与数据库建立连接

show databases 显示所有的数据库

use database_name 选择数据库

show tables 显示当前数据库下所有的表

show columns from table_name 显示表的列信息，作用和desc table_name是一样的

show status 显示MySQL服务器状态信息

show create database_name和show create table_name 分别显示创建特定数据库和数据表的MySQL语句

show grants 显示授予用户的安全权限

show errors和show warnings 显示服务器错误或警告信息

二、库表分析

数据库(database): 保存有组织的数据的容器。

表(table): 某种特定类型数据的结构化清单。

列(column): 表中的一个字段，所有表都是由一个或多个列组成的。

数据类型(datatype): 所容许的数据的类型，每个表列都有相应的数据类型，它限制该类所存储的类型。

行(row): 是表中的一个记录。

主键(primary key): 是一列(或者一组列)，其值能够唯一区分表中每个行。

三、数据类型

CREATE TABLE `user_accounts` (
  `id`             int(100) unsigned NOT NULL AUTO_INCREMENT primary key,
  `password`       varchar(32)       NOT NULL DEFAULT '' COMMENT '用户密码',
  `reset_password` tinyint(32)       NOT NULL DEFAULT 0 COMMENT '用户类型：0－不需要重置密码；1-需要重置密码',
  `mobile`         varchar(20)       NOT NULL DEFAULT '' COMMENT '手机',
  `create_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `update_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  -- 创建唯一索引，不允许重复
  UNIQUE INDEX idx_user_mobile(`mobile`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
COMMENT='用户表信息';

数据类型属性解释

NULL：数据列可包含NULL值；

NOT NULL：数据列不允许包含NULL值；

DEFAULT：默认值；

PRIMARY：KEY 主键；

AUTO_INCREMENT：自动递增，适用于整数类型；

UNSIGNED：是指数值类型只能为正数；

CHARACTER SET name：指定一个字符集；

COMMENT：对表或者字段说明；

整数类型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节范围(-128~127)
smallint(m)	2个字节范围(-32768~32767)
mediumint(m)	3个字节范围(-8388608~8388607)
int(m)	4个字节范围(-2147483648~2147483647)
bigint(m)	8个字节范围(+-9.22*10的18次方)

1、取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

2、int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

浮点数类型

MySQL数据类型	含义
float(m,d)	单精度浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）
double(m,d)	双精度浮点型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）
decimal(m,d)	定点型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位）

1、浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

2、浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围。

3、m指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。

4、d指定小数点右边可以存储的十进制数字的最大个数。m<65，d<30且 d<m。

5、超出精度范围的数会被强制进位并只显示数据类型定义的格式。

字符串类型

MySQL数据类型	大小	用途
CHAR(n)	0-255字节	定长字符串
VARCHAR(n)	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

char和varchar区别：

1、char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节， $m * 3 <= 255$

2、char不管实际value都会占用n个字符的空间，而VARCHAR，BLOB 和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。

3、超过char和varchar的n设置后，字符串会被截断。

4、char在存储的时候会截断尾部的空格，varchar和text不会。

BLOB和TEXT相同：

1、BLOB和TEXT列不能有默认值。

2、对于BLOB和TEXT列的索引，必须指定索引前缀的长度。

3、当保存或检索BLOB和TEXT列的值时不删除尾部空格。

BLOB和TEXT不同：

TEXT

1、TEXT值是大小写不敏感的

2、Text被视为非二进制字符串

3、TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较

4、可以将TEXT列视为VARCHAR列

5、BLOB 可以储存图片,TEXT不行，TEXT只能储存纯文本文件。

BLOB

6、BLOB值的排序和比较以大小写敏感方式执行;

7、BLOB被视为二进制字符串;

8、BLOB列没有字符集，并且排序和比较基于列值字节的数值值。

9、在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列

10、一个BLOB是一个能保存可变数量的数据的二进制的大对象。

日期和时间类型

类型	大小（字节）	范围	格式	用途
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	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

四、常用关键词

INSERT

INSERT INTO 语句用于向表格中插入新的行。

语法：INSERT INTO 表名称 VALUES (值1, 值2,....)

语法：INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

语法：INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....),(值1, 值2,....).....

DELETE

DELETE 语句用于删除表中的行。

语法：DELETE FROM 表名称 WHERE 列名称 = 值

UPDATE

UPDATE 语句用于修改表中的数据。

语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

SELECT

SELECT 语句用于从表中选取数据。

语法：SELECT 列名称 FROM 表名称

语法：SELECT * FROM 表名称

LEFT \ RIGHT \ INNER \ FULL [OUTER] JOIN

JOIN: 如果表中有至少一个匹配，则返回行

INNER JOIN:在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

UNION

UNION - 操作符用于合并两个或多个 SELECT 语句的结果集，SELECT查询列数量需要相同。

语法：SELECT 列名称1 FROM 表名称1 UNION SELECT 列名称2 FROM 表名称2

EXISTS

EXISTS - 操作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。（可以配合NOT使用）
语法：SELECT * FROM 表名称1 WHERE EXISTS(SELECT * FROM 表名称2 WHERE 表名称1.列名称 = 表格名2.列名称)

IN - 操作符允许我们在 WHERE 子句中规定多个值。（可以配合NOT使用）
IN - 操作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。
语法：SELECT * FROM 表名称 WHERE 列名称 IN ('值一', '值二', ...)

NOT

NOT - 操作符总是与其他操作符一起使用，用在要过滤的前面。

BETWEEN AND

BETWEEN AND - 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。通常值1小于值2，取值范围：值1<= x <= 值2（可以配合NOT使用）

语法：SELECT * FROM 表名称 WHERE BETWEEN 值1 AND 值2

IS NULL

IS NULL - 判断列值为NULL。（可以配合NOT使用）

语法：SELECT * FROM 表名称 WHERE 列名称 IS NULL

GROUP BY

GROUP BY - 对SELECT查询相同列分组

语法：select [columns] from table_name [where..] group by [columns] [having ...]

HAVING

HAVING - 分组后进行条件筛选

HAVING - where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚合函数，使用where条件过滤出特定的行。

HAVING - having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

DISTINCT

DISTINCT - distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条件(count(distinct id))，其原因是distinct只能返回他的目标字段，而无法返回其他字段。

语法：SELECT DISTINCT 列名称 FROM 表名称

ORDER BY

ORDER BY - 语句用于根据指定的列对结果集进行排序。

DESC - 按照降序对记录进行排序。

ASC - 按照顺序对记录进行排序。

LIMIT

LIMIT - 对SELECT结果集截取。

语法：SELECT * FROM 表名称 LIMIT 整数值1,整数值2

其它常用

AND、OR、LIKE、AS、= > >= < <= <> + - * \

五、常用函数

1、数学函数

名称	说明	示例
ABS(X)	返回X的绝对值	SELECT ABS(-1) --1
BIN(X)	返回X的二进制，OCT(X)返回8进制，HEX(X)返回16进制	SELECT BIN(15) --1111
CEILING(X)	返回大于X的最小整数，也可以用CEIL(X)	SELECT CEILING(15.6) --16
EXP(X)	返回e的X次方	SELECT EXP(2)
FLOOR(X)	返回小于X的最大整数值	SELECT FLOOR(2.5) --2
GREATEST(x1,x2,x3...xn)	返回集合中最大的数	SELECT GREATEST('1','2','a') --a
LEAST(x1,x2,x3...xn)	返回集合中最小的数	SELECT LEAST('1','2','a') --1
LN(x)	返回x的自然对数	SELECT LN(3)
LOG(x,y)	返回x的以y为底的对数	SELECT Log(3,9) -- 2
MOD(x,y)	返回x/y的模	SELECT MOD(3,2) --1
RAND()	返回0到1内的随机值，可以通过一个参数（种子）使rand()随机生成一个指定的值	SELECT SQRT(5)
SQRT(X)	返回X的平方根	SELECT SIGN(16) --4
TRUNCATE(x,y)	返回数字x截短为y位小数的结果	SELECT TRUNCATE(3.3333,2) -3.33

2、聚合函数

　　常用于group by从句的select查询中。

名称	说明
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和
GROUP_CONCAT(col)	返回由属于一组的列值逗号隔开连接组合而成的结果

3、字符串函数

名称	说明	示例
ASCII(char)	返回一个字符的ASCII码值	SELECT ASCII("a") --97
BIT_LENGTH(str)	返回字符串的比特长度	SELECT BIT_LENGTH("a") --8
CONCAT(s1,s2...sn)	将s1...sn拼成字符串	SELECT CONCAT("a","b","c") --abc
CONCAT_WS(sep,s1,s2...sn)	将s1...sn用符号sep拼成字符串	SELECT CONCAT_WS(",","a","b","c") --a,b,c
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换成字符串instr	SELECT INSERT("abcdef",1,3,"1") --1def
LCASE(str) / LOWER(str) \| UCASE(str) / UPPER(str)	返回将字符串中所有字符变成小写\|大写后的结果	SELECT UPPER("ab") --AB
LEFT(str,x) / RIGHT(str,x)	返回str中最左/右边的x个字符	SELECT LEFT("abcd",2) --ab
LENGTH(x)	返回x的中的字符数	SELECT LENGTH("ab") --2
LTRIM(str)/RTRIM(str)	从字符串str中切掉开头的空格	SELECT RTRIM(" ab ") --" ab"
QUOTE(str)	用反斜杠转义str中的单引号	SELECT QUOTE("'abcd'bc") --'\'abcd\'bc'
REPATE(str,x)	返回str中用srchstr替换成rplcstr的结果	SELECT REPLACE("abcd","c","1") --ab1d
REVERSE(str)	返回字符串str颠倒后的结果	SELECT REVERSE("abcd") --bcda
TRIM(str)	去掉字符串的收尾空格	SELECT TRIM(" a bc ") --"a bc"

4、时间日期函数.

名称	说明	示例
CURDATE()或CURRENT_DATE()	返回当前的日期	SELECT CURDATE() --2018-08-03
CURTIME()或CURRENT_TIME()	返回当前的时间	SELECT CURTIME() --13:30:31
DATE_ADD(date,INTERVAL int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)	SELECT DATE_ADD('2018-08-03',INTERVAL 1 MONTH) --2018-09-03
DATE_FORMAT(date,fmt)	依照指定的fmt格式格式化日期date值	SELECT DATE_FORMAT('2018-08-03','%Y') --2018
DATE_SUB(date,INTERVAL int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)	SELECT DATE_SUB("2018-08-03",INTERVAL 1 month) --2018-07-03
DAYOFWEEK(date)	返回date所代表的一星期中的第几天(1~7)	SELECT DAYOFWEEK('2017-08-23') --4
DAYOFMONTH(date)	返回date是一个月的第几天(1~31)	SELECT DAYOFMONTH('2017-08-23') --23
DAYOFYEAR(date)	返回date是一年的第几天(1~366)	SELECT DAYOFYEAR('2017-08-23') --235
DAYNAME(date)	返回date的星期名	SELECT DAYNAME('2017-08-23') --Wednesday
HOUR(time)	返回time的小时值(0~23)	SELECT HOUR('13:55:30') --13
MINUTE(time)	返回time的分钟值(0~59)	SELECT MINUTE('13:55:30') --55
MONTH(date)	返回date的月份值(1~12)	SELECT MONTH('2018-08-06') --8
MONTHNAME(date)	返回date的月份名	SELECT MONTHNAME('2018-08-06') --August
NOW()	返回当前的日期和时间	SELECT NOW() --2018-08-07 09:05:33
QUARTER(date)	返回date在一年中的季度(1~4)	SELECT QUARTER('2018-08-07') --3
WEEK(date)	返回日期date为一年中第几周(0~53)	SELECT WEEK('2018-08-07') --31
YEAR(date)	返回日期date的年份(1000~9999)	SELECT YEAR('2018-08-07') --2018
EXTRACT(f,date)	返回date或time中的f部分	SELECT EXTRACT(YEAR_MONTH FROM '2017-08-09 12:50:00'); --201708
DATEDIFF(f1,f2)	返回两个日期间的差值	SELECT DATEDIFF('2018-05-06','2018-05-08') --(-2)

5、格式化函数

名称	说明	示例
DATE_FORMAT(date,fmt)	依照字符串fmt格式化日期date值	SELECT DATE_FORMAT(NOW(),'%Y-%y;%M-%m;%D-%d;%H-%h;%i;%p;%s;%r') --2018-18;August-08;7th-07;09-09;39;AM;15;09:39:15 AM
FORMAT(x,y)	把x格式化为以逗号隔开的数字序列，y是结果的小数位数	SELECT FORMAT(100,3) --100.000
INET_ATON(ip)	返回IP地址的数字表示	SELECT INET_ATON('192.168.1.10') --3232235786
INET_NTOA(num)	返回数字所代表的IP地址	SELECT INET_NTOA(3232235785) --192.168.1.9
TIME_FORMAT(time,fmt)	依照字符串fmt格式化时间time值	SELECT TIME_FORMAT('97:15:40','%H %k %h %I %l'); --97 97 01 01 1

6、控制流函数

名称	说明	示例
CASE [test] WHEN[val1] THEN [result]...ELSE [default] END	如果test和valN相等，则返回resultN，否则返回default	示例代码
CASE WHEN[test1] THEN [result1]...ELSE [default] END	如果testN是真，则返回resultN，否则返回default	示例代码
IF(test,t,f)	如果test是真，返回t；否则返回 f	示例代码
IFNULL(arg1,arg2)	如果arg1不是空，返回arg1，否则返回arg2	示例代码
NULLIF(arg1,arg2)	如果arg1=arg2返回NULL；否则返回arg1	示例代码

 1 #CASE [test] WHEN[val1] THEN [result]...ELSE [default] END
 2 SELECT *,
 3 CASE REMARK
 4 WHEN 1 THEN '线上'
 5 WHEN 2 THEN '线下'
 6 ELSE '其它' END
 7 FROM TABLE_1;
 8 
 9 #CASE WHEN[test1] THEN [result1]...ELSE [default] END
10 SELECT *,
11 CASE 
12 WHEN REMARK=1 THEN '线上'
13 WHEN REMARK=2 THEN '线下'
14 ELSE '其它' END
15 FROM TABLE_1;
16 
17 #IF(test,t,f)
18 SELECT *,
19 IF(REMARK=1,'线上','线下') AS '产品'
20 FROM TABLE_1;
21 
22 #IFNULL
23 SELECT *,
24 IFNULL(REMARK,'线下') AS '产品'
25 FROM TABLE_1;
26 
27 #NULLIF
28 SELECT *,
29 NULLIF(REMARK,'1') AS '产品'
30 FROM TABLE_1;

示例代码

7、系统信息函数

名称	说明
DATABASE()	返回当前数据库名
BENCHMARK(count,expr)	将表达式expr重复运行count次
CONNECTION_ID()	返回当前客户的连接ID
FOUND_ROWS()	返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER()	返回当前登陆用户名
VERSION()	返回MySQL服务器的版本

参考文章：

https://www.cnblogs.com/luoxn28/p/5366273.html

https://blog.csdn.net/lisonglisonglisong/article/details/46660813

https://blog.csdn.net/Gane_Cheng/article/details/52316408

https://www.cnblogs.com/Anidot/articles/7668158.html

https://blog.csdn.net/zuiaituantuan/article/details/6115938

https://blog.csdn.net/bzhxuexi/article/details/43700435

https://segmentfault.com/a/1190000006876419#articleHeader6

http://www.cnblogs.com/picaso/archive/2012/05/17/2505895.html

https://my.oschina.net/zimingforever/blog/287629

posted @ 2018-08-07 10:37 凉秋阅读(120) 评论(0) 收藏举报

刷新页面返回顶部

凉秋

MySQL基础

公告