MySQL命令与数据类型简介

目录

基本操作命令语句

mysql命令

数据导入导出

数据导出

数据导入

复制表结构

基础说明

大小写及字符集规则

sql_mode

AUTO_INCREMENT（自增）列

group by分组

Having

基本类型

数值类型

字符串类型

字符集转换

字符串匹配

日期时间类型

日期获取函数

日期计算函数

日期转换函数

---

MySQL是最流行的关系型数据库管理系统之一，所使用的SQL语言是用于访问数据库的最常用标准化语言。其采用‘客户端/服务器’体系结构，mysqld作为服务程序运行在数据库服务器上，监听网络上的客户端请求。

基本操作命令语句

SQL命令语句以分号(;)结束，通过\G可以垂直显示结果（按行显示查询结果）：
Select Now(), User(), Version() \G;

通过参数-e可把执行结果保存到文件（类似实现脚本功能）：
mysql -uroot -p -e "use mysql; show tables;" > c:\out.txt

创建表（并判断是否存在）

Drop Database If Exists 'n1'
Create Database 'n1'

Drop Table If Exists n1.t1
Create Table n1.t1( ... )

CREATE TABLE IF NOT EXISTS `student` (
  id` varchar(40) NOT NULL,
  ...
);

mysql命令

通过mysql客户端，可方便连接MySQL服务器，并对其进行操作（执行SQL语句）：
mysql -h [host] -p -u [user]
如：mysql -h 192.168.1.100 -u root -p -P 3306

进入命令行后（mysql>）后，就可使用sql语句进行各种操作（创建、删除表）与查询了，除常规Sql操作语句（创建、修改、删除、查询等）外，还有：

• show databases; 显示所有数据库名
• show tables; 显示所有表名
• show create table table_name; 显示完整的表创建命令
• show full columns from table_name; 显示列详细信息（包括权限）
• show table status; 显示表的状态
• show full processlist; 当前所有连接的详细情况(没有full，只显示前100个连接)
• use ; 切换当前数据库
• describe ; 显示列的详细信息（列名、类型、是否空、键、默认值等）
• select version();
• select database(); 显示当前数据库名
• SELECT CURRENT_DATE; 显示‘年-月-日’
• select now();或select current_timestamp(); 显示‘年-月-日 时分秒’
• quit或\q：退出
• show status like '%%'; 查看状态，var如下：
• Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
• Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
• Connections 试图连接MySQL服务器的次数。
• Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
• Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
• Delayed_writes 用INSERT DELAYED写入的行数。
• Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
• Flush_commands 执行FLUSH命令的次数。
• Handler_delete 请求从一张表中删除行的次数。
• Handler_read_first 请求读入表中第一行的次数。
• Handler_read_key 请求数字基于键读行。
• Handler_read_next 请求读入基于一个键的一行的次数。
• Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
• Handler_update 请求更新表中一行的次数。
• Handler_write 请求向表中插入一行的次数。
• Key_blocks_used 用于关键字缓存的块的数量。
• Key_read_requests 请求从缓存读入一个键值的次数。
• Key_reads 从磁盘物理读入一个键值的次数。
• Key_write_requests 请求将一个关键字块写入缓存次数。
• Key_writes 将一个键值块物理写入磁盘的次数。
• Max_used_connections 同时使用的连接的最大数目。
• Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
• Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
• Open_tables 打开表的数量。
• Open_files 打开文件的数量。
• Open_streams 打开流的数量(主要用于日志记载）
• Opened_tables 已经打开的表的数量。
• Questions 发往服务器的查询的数量。
• Slow_queries 要花超过long_query_time时间的查询数量。
• Threads_connected 当前打开的连接的数量。
• Threads_running 不在睡眠的线程数量。
• Uptime 服务器工作了多少秒。

数据导入导出

数据导出

使用mysqldump可以方便导出数据库文件

• -d 只导出表结构（没有数据）
• --add-drop-table 在每个create语句之前增加一个drop table

 mysqldump -u{user} -p -A > c:\out.sql -- 导出所有数据库
 mysqldump -u{user} -p {db_name} > c:\out.sql -- 导出指定数据库的数据和表结构

mysqldump -h localhost -u root -p mydb mytable>c:\out.sql -- 将mydb中的mytable 表结构与数据导出到c:\out.sql中
mysqldump -h localhost -u root -p mydb -d --add-drop-table >c:\out.sql -- 只导出表结构，且在Create语句前添加drop语句（删除表）

数据导入

从客户端本地可加载数据文件，把内容插入到数据库表中：

c:\> mysql --local-infile [database]  -- 使用客户端本地文件
c:\> mysql -u root –p <  c:\out.sql

mysql> load data local infile 'test.txt' into table [tbname] -- local表示使用本地（而非服务器上）的文件，若是不允许可尝试服务端启动时添加--local-infile参数；
mysql>source  c:\out.sql  -- 使用source命令导入

复制表结构

mysql中可通过create table as 和create table like来复制表结构：

• as创建出来的新表没有源表的索引结构，且只是表结构相似（如bigint可能会变成int）；这个语句其实只是把select语句的结果建一个表，因此，可以复制表全部内容或选定的内容。
• like创建出来的新表包含源表的完整表结构和索引信息。

create table t2 as select * from t1 [where 1=2 或 limit 0];
create table t2 like t1;

真正的复制一个表：

CREATE TABLE newT LIKE oriT; 
INSERT INTO newT SELECT * FROM oriT;

也可以拷贝一个表中其中的一些字段或修改字段名。

CREATE TABLE newT AS 
( 
SELECT id, username AS uname, password AS pass FROM oriT 
)；

也可以拷贝一部分数据。

CREATE TABLE newT AS 
( 
SELECT * FROM oriT WHERE username like 's%' 
)

 

基础说明

大小写及字符集规则

SQL语句大小写规则

• SQL关键字和函数名不区分大小写；
• 数据名、表名和视图名是否区分大小写与底层操作系统有关；
• 存储过程、函数和事件名字不区分大小写；
• 触发器名字区分大小写；
• 列名和索引名不区分大小写；
  如，Linux下的MySQL，数据库名与表名是严格区分大小写的；列名忽略大小写；变量名严格区分大小写；

字符集与排序规则，默认通过系统变量character-set-server和collation-server设定；

show variable like 'charcter\_set%' # 查询客户端与服务器间通讯所用字符集；
character set [charset]和collate [collation] # 用于指定数据库、表和列的字符集与排序规则
CREATE DATABASE `MyDb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `myTable`( ... ) CHARACTER SET utf8 COLLATE utf8_general_ci;
SELECT c FROM t ORDER BY c COLLATE utf8_general_cs;

sql_mode

数据库服务器上的系统变量sql_mode会影响数据库的行为：

• STRICT_ALL_TABLES和STRICT_TRANS_TABLES：启用‘严格’模式（会直接拒绝坏数据的接收）；
• TRADITIONAL：是一个组合模式，类似严格模式但会引入跟多的附加限制条件；使其行为接近于传统SQL服务器；
• ANSI_QUOTES：把双引号识别为标识符引用字符；
• PIPES_AS_CONCAT：把‘||’作为标准的SQL字符串连接运算符；
• ANSI：是一个组合模式，启用ANSI_QUOTES和PIPES_AS_CONCAT等，使服务器行为更接近于标准SQL；

Set sql_mode="TRADITIONAL"; -- 设定本地会话
Set Global sql_mode="TRADITIONAL"; - 设定全局SQL模式
Select @@SESSION.sql_mode;
Select @@GLOBAL.sql_mode;

AUTO_INCREMENT（自增）列

每个表只能有一个自增列，一般为整数类型；必须建立索引（一般是主键或唯一索引），且为NOT NULL。
LAST_INSERT_ID()能获取当前会话中最近生成的序号值（若还未生成返回0）；一次insert多行时（生成多个序号值），只会返回其中一个。
删除列的序号值，一般不会再重用；Truncate Table清空表后，会把序号值重新从1开始。

group by分组

根据给定数据列的每个成员对查询结果进行分组统计：
select中字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中，如：

• sum(列名)：求和：　　　　
• max(列名)：最大值：　　　　
• min(列名)：最小值：　　　　
• avg(列名)：平均值：　
• count(列名)/count(*)：统计记录数；

Having

Having：筛选满足条件的组需要用Having，Where是分组前查询的筛选条件；
with rollup：在分组统计的基础上再次进行汇总统计（统计汇总：最小值则取总体最小；总体则加总；平均则总体平均）；汇总时列名称默认为NULL，可用coalesce设定列名；

Select 
    Coalesce(class, '总计'), 
    Min(score) as minimum, 
    Sum(score) as total, 
    avg(score) as average, 
    Count(score) as count 
From Scores 
Group by class With RollUp;

class	minimum	total	average	count
一班	9	439	15.13	29
二班	7	383	78.23	31
…
总计	7	6373	36.85	173

 

基本类型

基本说明：

• 对于数值列，若插入的值超过类型范围：在严格的SQL模式下，则报错；否则会截断（允许的极值）后插入，并生成一条告警消息。
• 除ENUM和SET外，所有字符串类型的值都存储为一串字节；插入值太长时，会截断他们（严格模式下，若截断后的字符为空格，才会出错提示）。实际字符串类型列最大有效长度，取决于MySQL‘客户端/服务器’通讯协议所支持的最大包长度（默认1M）。
• CHAR(L)列长度由字符集最宽字符所占字节数（设为w）确定，其占用L*w个字节。插入数据不足时，用空格补齐；检索数据时，会自动移除尾部空格（除非启用PAD_CHAR_TO_FULL_LENGTH模式，此时保留尾部空格）。
• CHAR(0)是合法的，允许为NULL时可用来表示‘开/关’值（只能为NULL或空串）；其所占非常少的存储空间（仅一个二进制位）。
• ENUM和SET是比较特殊的字符串数据类型，只能从一个固定（预先定义好的）的字符串列表里取值：Enum列值必须且只能一个值成员，而Set列值则允许包含任意多个值成员（空、或全体都可以）。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 223 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，3.402 823 466 351 E+38)	(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308)	(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	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字节	极大文本数据

字符集转换

CONVERT(str USING charset)
Convert('ABCD' USING ucs2) #转换双字节的宽字符

字符串匹配

MySQL中可以使用Like进行字符串匹配（'%'匹配任意长度的字符串，'_'匹配任一字符）；除此之外还支持REGEXP正则匹配

• .点号：匹配任意单字符；
• […]方括号：匹配括号内的任意字符；
• -连字符：表示范围，[a-z]所有小写字母；
• ^取反：[^abcd] abcd外的所有字符；
• *：前面字符重复任意次数（0到n）；
• +：前面字符至少重复一次（1到n）；

日期时间类型

类型	大小(字节)	范围	格式	用途
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	混合日期和时间值，时间戳

从5.6.4版本开始，增加了对小数秒59[.123456]（最多6位，微妙）的支持；要使用小数，定义时需要指定小数位数，如Time(3)。

设定自动初始化属性和自动更新特性(5.6.5起)：
colName TIMESTAMP [DEFAULT default_vlue] [ON UPDATE CURRENT_TIMESTAMP]
TIMESTAMP默认为NOT NULL，此时把列显示设置为NULL（插入或更新时），则自动使用当前时间戳；设定‘ON UPDATE ’属性后，其他列值发生任何变化时，都会自动更新时间戳。

日期获取函数

获取当前时间：

• now()：在执行语句开始时获取（同一条sql语句中，多个返回值是相同的），相同的有current_timestamp()、localtime()等
• sysdate()：在具体执行到时获取（同一条sql语句中，多个返回值可能不同）；
• curdate()：当前日期；
• curtime()：当前时间（没有日期部分）；
• UTC时间：utc_date(), utc_time(), utc_timestamp()；

选取函数Extract：

• 简写：date、time、year、quarter、month、week、day、hour、minute、second、microsecond；
• Extact(partname from @dt)：partname为上面各函数

辅助函数：

• dayofweek：一周中第几天；（1 = Sunday, 2 = Monday,…, 7 = Saturday）
• dayofmonth：一月中第几天；
• dayofyear：一年中第几天；1月1日为第一天；
• week：一年中第几周；第一周为0；
• weekofyear：一年中第几周；第一周为1；
• weekday：一周中第几天；(0 =Monday, 1 = Tuesday, …, 6 = Sunday)
• dayname：返回星期几的名称，如‘monday’；
• monthname：返回月份名称，如‘may’；
• last_day：月份中最后一天；

set @dt = '2019-05-13 07:15:30.123456';
select date(@dt);  --2019-05-13
select extract(month from @dt);  --5
select  dayofyear(@dt); -- 133
select  dayname(@dt); -- 'monday'
select last_day(@dt); -- 2019-05-31
select CAST(NOW() AS UNSIGNED), NOW()+0 -- 转换为数值表示'20190715172705'

日期计算函数

日期计算函数

• date_add(@dt, interval-type)：增加日期（负数则为减少）
• date_sub(@dt, interval-type)：减少日期
• datediff(date1,date2)：两个日期相减 date1 - date2，返回天数
• timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。

类型(type值)	含义	expr表达式的形式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
HOUR	时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR_MONTH	年和月	YY和MM之间用任意符号隔开
DAY_HOUR	日和小时	DD和hh之间用任意符号隔开
DAY_MINUTE	日和分钟	DD和mm之间用任意符号隔开
DAY_SECOND	日和秒钟	DD和ss之间用任意符号隔开
HOUR_MINUTE	时和分	hh和mm之间用任意符号隔开
HOUR_SECOND	时和秒	hh和ss之间用任意符号隔开
MINUTE_SECOND	分和秒	mm和ss之间用任意符号隔开

简单示例：

select date_add(@dt, interval 1 day);
select date_add(@dt, interval 190 MINITE);
select date_add(@dt, interval '01:15:30' hour_second);
select date_add(@dt, interval ':1:0' minute_second);
select date_sub(@dt, interval '1 1:1:1' day_second);
select datediff('2008-08-01 08:08:08', '2008-08-08 00:00:00'); -- -7  
select timediff('2008-08-08 08:08:08', '2008-08-01 00:00:00'); -- 176:08:08

日期转换函数

日期时间转换：

• time_to_sec(time), sec_to_time(seconds)：时间与秒间转换；
• to_days(date), from_days(days)：天数与日期转换；
• str_to_date(str, format)：字符串转换为时间；
• makdedate(year,dayofyear)：年与第几天生成日期；
• maketime(hour,minute,second)：生成时间；
• timestamp(date)：日期转换为时间戳；若无参数则使用当前时间；
• timestamp(dt,time)：dt + time
• date_format(date,format)：日期格式化；
• time_format(time,format)：时间格式化；

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

简单示例：

select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7