MySQL知识手册速查表
目录
连接到mysql服务器
使用MySQL命令行客户端，通过用户名和密码连接到MySQL服务器
MySQL将提示输入 密码：
Mysql -u [username] -p
使用用户名和密码连接到MySQL服务器上的特定数据库：
Mysql -u [username] -p [database]
使用mysqldump工具导出数据：
Mysqldump -u [username] -p \ [database] > data_backup.sql
退出客户端：
Quit or exit
帮助，输出命令的完整列表：
Help
创建和显示数据库
创建一个数据库
CREATE DATABAASE zoo;
列出服务器上的所有数据库：
SHOW DATABASES;
使用指定的数据库：
USE zoo;
删除指定的数据库：
DROP DATABASE zoo;
列出数据库下所有表：
SHOW TABLES;
输出指定表的信息：
DESCRIBE animal;
它输出列名，数据类型，默认值以及有关该表的更多信息。
创建表
创建表
CREATE TABLE habitat (
id INT,
name VARCHAR(64)
);
使用AUTO_INCREMENT可以创建随每条新记录自动增长的ID。
AUTO_INCREMENT列必须定义为主键或唯一索引：
CREATE TABLE animal (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (64),
npecies VARCHAR (64),
age INT,
habitat_id INT,
FOREIGN KEY (habitat_id)
REFERENCES habitat (id)
);
修改表
使用ALTER TABLE语句修改表结构
修改表名字：
ALTER TABLE animal RENAME pet;
给表添加一列：
ALTER TABLE animal ADD COLUMN name VARCHAR (64);
修改列的名字：
ALTER TABLE animal RENAME COLUMN id TO identifier;
修改列的数据类型：
ALTER TABLE animal MODIFY COLUMN name VARCHAR(128);
删除一列：
ALTER TABLE animal DROP COLUMN name;
删除一个表：
DROP TABLE animal;
查询数据
使用SELECT命令可以从表中查询数据：
一个单表查询的例子：
SELECT species, AVG ( age ) AS average_age
FROM animal
WHERE id != 3
GROUP BY species
HAVING AVG ( age ) > 3
ORDER BY AVG ( age ) DESC;
一个多表查询的例子：
SELECT city.name, country.name FROM city [ INNER | LEFT | RIGHT ] JOIN country ON city.country_id = country.id;
使用+ – * / 可以做基本运算
例：得到一周的秒数
SELECT 60 * 60 * 24 *7; — result: 604800
聚合与分组
l AVG ( expr ) – 分组的expr的平均值
l COUNT ( expr ) – 分组的expr的数量
l MAX ( expr ) – 分组的expr的最大值
l MIN ( expr ) – 分组的expr的最小值
l SUM ( expr ) – 分组的expr的总和
计算表中的行数：
SELECT COUNT ( * ) FROM animal;
计算指定列中非空值的数量：
SELECT COUNT ( name ) FROM animal;
计算列中唯一值的数量：
SELECT COUNT ( DISTINCT name ) FROM animal;
分组
按物种分组计算动物数量
SELECT species, COUNT( id )
FROM animal
GROUP BY species;
按栖息地分组得以平均，最小和最大年龄
SELECT habitat_id , AVG (age ), MAX ( age), MIN ( age)
FROM animal
GROUP BY habitat_id;
插入数据
用INSERT命令向表中插入数据：
INSERT INTO habitat VALUES
(1,’River’),
(2,’Forest’);
可以指定要插入的列，其余列会用默认值或NULL填充
INSERT INTO habitat ( name ) VALUES ( ’Savanna’ );
更新数据
用UPDATE命令更新表中的数据：
UPDATE animal
SET
species = ‘Duck’,
name = ‘Quack’
WHERE id = 2;
删除数据
用DELETE 命令从表中删除数据：
DELETE FROM animal WHERE id = 1;
用TRUNCATE TABLE命令从表中删除所有数据：
TRUNCATE TABLE animal;
转换数据类型
使用CAST()函数，可以改变一个值的类型mysql中，你可以转换为以下数据类型：
|
CHAR |
NCHAR |
BINARY |
DATE |
DATETIME |
|
DECIMAL |
DOUBLE |
FLOAT |
REAL |
SIGNED |
|
UNSIGNED |
TIME |
YEAR |
JSON |
Spatial_type |
把数字转换为有符号整数
SELECT CAST ( 1234.456 AS SIGNED ); –result:1235
把列的类型转换为double输出
SELECT CAST ( column AS DOUBLE );
文本函数
过滤输出
获取城市名字不是Berlin的行；
SELECT name
FROM city
WHERE name != ‘Berlin’;
文本操作符
获取以”P”开头或以”s”结尾的城市名称
SELECT name
FROM city
WHERE name LIKE ‘P%’ OR name LIKE ‘%s’;
获取以任意字母开头，后跟”ublin”的城市名称（如爱尔兰Dublin 或 波兰Lublin）:
SELECT name
FROM city
WHERE name LIKE ‘_ublin’;
文本连接
CONCAT( )函数可以连接两个字符串：
SELECT CONCAT ( ‘Hi’ , ’there!’ ); –result: Hi there!
如果任一字符串为NULL,则结果为NULL
SELECT CONCAT ( ‘Hi’ , ’there!’,NULL ); –result: NULL
使用CONCAT_WS( )函数指定分隔符。分隔符位于边接的值之间：
SELECT CONCAT_WS ( ‘ ’ ,1 , ‘ Olivier’,’Norris’ ); –result: 1 Olivier Norris
其它有用的文本函数
获取字符串中的字符数量：
SELECT LENGTH ( ‘LearnSQL.com’ ); –result: 12
SELECT LOWER ( ‘LearnSQL.com’ ); –result: learnsql.com
SELECT UPPER ( ‘LearnSQL.com’ ); –result: LEARNSQL.COM
得到字符串的一部分：
SELECT SUBSTRING ( ‘LearnSQL.com’,9 ); — result:.com
SELECT SUBSTRING ( ‘LearnSQL.com’,1,5 ); — result:.Learn
替换字符串的一部分：
SELECT REPLACE ( ‘LearnSQL.com’,’SQL’,’Python’ ); — result: LearnPython.com
数值函数
得到附法的余数：
SELECT MOD (13 , 2); — result : 1
把数字四舍五入
SELECT ROUND (1234.56789); — result : 1235
把数字四舍五入到小数点后三位：
SELECT ROUND (1234.56789,3); — result : 1235.568
向上取整
SELECT CEIL (13.1); — result : 14
SELECT CEIL (-13.9); — result : 13
向下取整
SELECT FLOOR (13.8); — result : 13
SELECT FLOOR (-13.2); — result : 14
按指定位数进行截取，不四舍五入
SELECT TRUNCATE (13.56,0); — result : 13
SELECT TRUNCATE (-13.56,1); — result : -13.5
得到数字的绝对值
SELECT ABS (-12); — result : 12
得到数字的平方根
SELECT SQRT (9); — result : 3
有用的NULL函数
得到rating 不为空的城市名字
SELECT name
FROM city
WHERE rating IS NOT NULL;
COALESCE(x,y,…)
把查询中的NULL替换为意义的内容
SELECT domain,
COALESCE (domain , ’domain missing’ )
FROM contacts;
COALESCE () 函数接受任意数量的参数，并返回第一个非NULL参数的值。
NULLIF(x,y)
为避免除0错误
SELECT last_month,this_month,this_month * 100.0 / NULLIF (last_month,0)
AS better_by_percent
FROM video_views;
如果x等于y，NULLIF(x,y) 函数返回NULL，否则返回x的值。
日期时间
MYSQL中5种主要的时间数据类型：
|
DATE |
TIME |
DATETIME |
TIMESTAMP |
YEAR |
DATE – 以YYYY-MM-DD 年月日
TIME – 以HH:MM:SS 时分秒
DATETIME – 以YYYY-MM-DD HH:MM:SS 支持的范围“1000-01-01 00:00:00“到”9999-12-31 23:59:59“
TIMESTAMP 存储时期和时间，范围为“1970-01-01 00:00:01“UTC到”2038-01-19 03:14:07“UTC。MYSQL把TIMESTAMP 值从当前时区转换为UTC进行存储，并将其从UTC转换回当前时区进行检索
YEAE – 以YYYY
时间间隔
指两个时间点之间持续的时间。
定义间隔：INTERVAL 1 DAY由 INTERVAL 关键字，值和时间部分关键字（YEAR, QUARTER,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND,MICROSECOND）组成。
可使用+或–运算符组合不同的表过式：
INTERVAL 1 YEAR + INTERVAL 3 MONTH
也可以使用MYSQL的标准表达式：
INTERVAL ‘1-3’ YEAR _MONTH – 1year and 3 months
INTERVAL ‘3-12’ HOUT_MINUTE – 3 hours 12 minutes
现在什么时间
l Current_TIME or CURTIME 当前时间
l Current_DATE or CURTDATE 当前日期
l NOW() or CURRENT_TIMESTAMP 包含日期和时间的当前时间戳
创建时间/日期值
计算指定列中非空值的数量：
SELECT CAST( ‘2021-12-31’ AS date ),
CAST( ’15:31’ AS time ),
CAST( ‘2021-12-31 23:59:29’ AS datetime );
计算指定列中非空值的数量：
SELECT airline,flight_no,departure_time
FROM airport_scheedule
WHERE departure_time < ’12:00’;
提取日期的一部分
使用YEAAR,MONTH,DAY,HOUR等函数
SELECT YEAR ( CAST (‘2021-12-31’ AS date )); –result: 2021
SELECT MONTH ( CAST (‘2021-12-31’ AS date )); –result: 12
SELECT DAY ( CAST (‘2021-12-31’ AS date )); –result: 31
日期运算
ADDDATE()可以为DATE添加或减去间隔：
ADDDATE(‘2021-10-31’, INTERVAL 2 MONTH); — result:’2021-12-31’
ADDDATE(‘2014-04-05’, INTERVAL -3 MONTH); — result:’2014-04-02’
TIMESTAMPADD()可以为TIMESTAMP 或 DATETIME 添加或减去间隔：
TIMESTAMPADD(MONTH , 2, ’2014-06-10 07:55:00’); — result:’2014-08-10 07:55:00 ’
TIMESTAMPADD(MONTH , -2, ’2014-06-10 07:55:00’); — result:’2014-04-10 07:55:00 ’
ADDTIME()可以为DATETIIME添加或减去TIME
ADDTIME(‘2018-02-12 10:20:24’,’12:43:02’); — result:’2018-02-12 23:03:26 ’
ADDTIME(‘2018-02-12 10:20:24’,’-12:43:02’); — result:’2018-02-11 21:37:22 ’
DATEDIFF()函数可以找出两个日期有之间的差异
DATEDIFF(‘2015-01-01’,’2014-01-02’); — result:364
TIMEDIFF()函数可以找出两个时间有之间的差异
TIMEDIFF(’09:30:00’,’07:55:00’); — result:01:35:00
TIMESTAMPDIFF()函数找出两个日期时间之间的差异（以给定的时间单位）。
例：差异以周为单位：
SELECT TIMESTAMPDIFF(WEEK, ‘2018-02-26’, ‘2018-03-21’); result:3
