Mysql之SQL操作和数据类型

雨点打透心脏的1/2处 2022-05-18 09:57 189阅读 0赞

## [Mysql之SQL操作和数据类型][Mysql_SQL] ##

# 【SQL分类：DDL DML DCL】 #

## 一、DDL（数据库定义语言） ##

定义不同的数据段、数据库、表、列、索引等数据库对象，常用语句关键字：create drop alter等

1、修改表字段，alter table语句的用法如下

1）修改表字段的数据类型：alter table table\_name modify…   
2）新增表字段：alter table table\_name add…   
3）删除表字段：alter table table\_name drop…   
4）字段改名：alter table table\_name change…   
5）更改表名：alter table table\_name rename…

*小技巧：*   
*进入mysql后，可以通过”help；”或者“\\h”命令来显示帮助内容，通过“\\c”命令来清除命令行buffer*   
\*change和modify都可以修改表中指定字段的数据类型，不同的是change后面要写两次列名，不方便；但是change的优点是可以修改字段名称，modify不能   
\*

## 二、DML（数据操作语句） ##

1、用于添加、删除、更新和查询数据库记录，并检测数据完整性，常用语句关键字：insert delete update select等

1）增删改查：insert update delete select

update里两表关联操作例子：
    update cv inner join cv2 on cv.c=cv2.c set cv.v='vvv';
    或者
    update cv,cv2 set cv.v=cv2.v where cv.c=cv2.c;

2）查询不重复的记录：distinct关键字   
3）条件查询：where关键字   
4）排序和限制：   
limit限制显示数据条数；   
desc和asc是排序关键字；order by按某个字段来排序。   
order by后面可以跟多个不同的排序字段，每个排序字段可以有不同的排序规则：如果排序字段的值为一样的，则值相同的字段按照第二个排序字段进行排序；如果只有一个排序字段，则这些字段相同的记录将会无序排列   
5）聚合：   
常用的聚合函数有sum（求和）、count(\*)（记录数）、max（最大值）、min（最小值）   
group by关键字表示要进行分类聚合的字段；   
with rollup是可选语法，表名是否对分类聚合后的结果进行再汇总；   
having关键字表示对分类后的结果再进行条件的过滤；   
*having和where的区别在于：having是对聚合后的结果进行条件的过滤，where是在聚合前就对记录进行过滤，如果逻辑允许，建议尽可能用where过滤，这样因为结果集减少，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤*   
6）表连接：   
表连接分为：left join（左连接）、right join（右连接）、inner join（内连接）   
举例如下：

mysql> select * from t1;
    +----+------+
    | id | name |
    +----+------+
    |  2 | test |
    |  3 | test |
    |  4 | test |
    |  5 | test |
    +----+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from t2;
    +----+-------+
    | id | name  |
    +----+-------+
        |  3 | test3 |
    |  4 | test4 |
    |  5 | test6 |
    |  0 | test  |
    +----+-------+
    4 rows in set (0.00 sec)
    
    mysql> select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id; 
    +----+------+------+-------+
    | id | name | id   | name  |
    +----+------+------+-------+
    |  3 | test |    3 | test3 |
    |  4 | test |    4 | test4 |
    |  5 | test |    5 | test6 |
    |  2 | test | NULL | NULL  |
    +----+------+------+-------+
    4 rows in set (0.00 sec)
    
    mysql> select t1.id,t1.name,t2.id,t2.name from t1 right join t2 on t1.id=t2.id;    
    +------+------+----+-------+
    | id   | name | id | name  |
    +------+------+----+-------+
    |    3 | test |  3 | test3 |
    |    4 | test |  4 | test4 |
    |    5 | test |  5 | test6 |
    | NULL | NULL |  0 | test  |
    +------+------+----+-------+
    4 rows in set (0.00 sec)
    
    mysql> select t1.id,t1.name,t2.id,t2.name from t1 inner join t2 on t1.id=t2.id;     
    +----+------+----+-------+
    | id | name | id | name  |
    +----+------+----+-------+
    |  3 | test |  3 | test3 |
    |  4 | test |  4 | test4 |
    |  5 | test |  5 | test6 |
    +----+------+----+-------+
    3 rows in set (0.00 sec)

7）子查询：   
用于子查询的关键字主要包括：in not in = != exists not exists等   
子查询一般可以转换为表连接，表连接在很多情况下用于优化子查询的；   
8）记录联合：关键字是union、union all，union是将union all后的结果进行一次distinct，去除重复记录后的结果

*小技巧：*   
*（1）多表同时更新数据：update table\_a table\_b set ….where table\_a.xxx=table\_b.xxx*   
*（2）多表同时删除数据：delete table\_a,table\_b from table\_a,table\_b where table\_a.xxx=table\_b.xxx and …\[其他条件\]，这样符合where条件的记录，table\_a,table\_b表里都将被删除*

## 三、DCL（数据控制语句） ##

控制不同数据段之间的许可和访问级别，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，语句关键字：grant revoke等

================================================

# 【MySQL里的数据类型汇总】 #

MySQL支持多种数据类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

## 一、数值类型 ##

下面的表显示了需要的每个整数类型的存储和范围：   
![540235-20160927073100781-1223871751.png][]

1、tinyint smallint mediumint int bigint整数类型：

如果超出类型范围的操作，会有“out of range”的错误提示

2、整型数据类型，支持在类型名称后面括号内指定显示宽度，以int类型为例：

（1）可以直接定义为int类型，这种不显示指定宽度的，默认是int(11)；也可以显示指定宽度，例如定义字段为int(5)，表示，当数值宽度不够5位的时候在数字前面填满宽度，一般配合zerofill使用。（zerofill是在数字位数不满足指定位数，用0填充；否则用空格填空）   
*如果一个列指定为zerofill，则mysql自动为该列添加unsigned属性，原来int默认int(11)，有了zerofil属性后，默认为int(10)*   
*值得注意的是，在设置了宽度限制后，如果插入大于宽度限制，是不会截断或插不进去报错的，显示指定宽度，只是在不足位数宽度的时候用0填充，插入数值大于宽度，还是安装类型的实际精度进行保存。*   
*宽度格式实际已经没有意义了*

mysql> desc t3;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id1   | int(11) | YES  |     | NULL    |       |
    | id2   | int(5)  | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    mysql> insert into t3 values(1,1);
    Query OK, 1 row affected (0.01 sec)
    mysql> select * from t3;
    +------+------+
    | id1  | id2  |
    +------+------+
    |    1 |    1 |
    +------+------+
    1 row in set (0.00 sec)
    mysql> select length(id1),length(id2) from t3;
    +-------------+-------------+
    | length(id1) | length(id2) |
    +-------------+-------------+
    |           1 |           1 |
    +-------------+-------------+
    
    mysql> alter table t3 modify id1 int zerofill;
    Query OK, 1 row affected (0.06 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    mysql> select * from t3;                         
    +------------+------+
    | id1        | id2  |
    +------------+------+
    | 0000000001 |    1 |
    +------------+------+
    1 row in set (0.00 sec)
    
    mysql> select length(id1),length(id2) from t3;   
    +-------------+-------------+
    | length(id1) | length(id2) |
    +-------------+-------------+
    |          10 |           1 |
    +-------------+-------------+
    *注意，填充0后的长度是10而不是11，原因是：选择属性zerofill后，就同时是unsigned的了。（加入负值会报warnings值超出范围或者默认转化成0）,*
    mysql> insert into t3(id1,id2) values(-1,1);
    ERROR 1264 (22003): Out of range value for column 'id1' at row 1

3、小数类型：

分为浮点数和定点数，浮点数包括float（单精度）和double（双精度）；定点数则是decimal   
*定点数decimal在mysql内部是以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据*   
（1）浮点数和定点数都可以用类型名称后加“（M.D）”（精度，标度）的方式进行表示，“（M,D）”表示该值一共显示M位数字（整数位+小数位），其中D位位于小数点后面。   
mysql在保存数值时进行四舍五入；   
float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示；如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错   
decimal在不指定精度时，默认的整数位是10位，默认的小数位是0位，也就是默认decimal(10,0)来进行操作，如果数据超越了精度和标度，系统会报错；

4、BIT位类型：

用于存放位字段值，BIT(M)可以用来存放多为二进制数，M范围从1~64，如果不写则默认1位，对于位字段，直接使用select无法看到结果，需要使用bin()（显示为二进制格式）或者hex()（显示为十六进制格式）函数进行读取

## 二、日期时间类型 ##

![540235-20160927073101438-2073998396.png][]   
![540235-20160927073102016-2142672087.png][]

1、总结：

年：year类型   
年月日：date类型   
时分秒：time类型   
年月日时分秒：datatime类型   
年月日时分秒：timestamp类型

一个timestamp类型的特性测试：
    （1）定义第一个timestamp类型字段：add column t timestamp;默认的t字段属性是NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；定义第二个及以上timestamp类型字段：add column t timestamp；默认的tt字段属性是NOT NULL DEFAULT '0000-00-00 00:00:00'。也即是，timestamp列没有显式定义为null,默认都会设置为not null。
    （2）表中的第一个timestamp列，如果没有定义为null、定义default值或者on update，会自动分配default     current_timestamp和on update current_timestamp属性；
    表中第一个timestamp列之后的所有timestamp列，如果没有被定义为null、定义default值，会自动被指定默认值'0000-00-00 00:00:00'。在插入时，如果没有指定这些列的值，会自动指定为'0000-00-00 00:00:00'，且不会产生警告。
    （3）mysql5.6后，上述timestamp类型的默认设置方法被废弃了，在mysql启动时会告警，取消告警的方法是设置explicit_defaults_for_timestamp=true参数，设置该参数后，timestamp类型的列的默认处理方式也发生变化：
        （3.1）timestamp列如果没有显式定义为not null，则支持null属性。设置timestamp的列值为null，就不会被设置为current timestamp；
        （3.2）不再自动分配default current_timestamp和on update current_timestamp属性，这些属性必须显式指定；
        （3.3）声明为not null且没有显式指定默认值是没有默认值的。表中插入列，又没有给timestamp列赋值时，如果是严格sql模式，会抛出一个错误；如果严格sql模式没有启用，该列会赋值为’0000-00-00 00:00:00′，同时出现一个警告。（这和mysql处理其它时间类型数据一样，如datetime）

2、测试，

mysql> desc date_time;
    +-------+-----------+------+-----+-------------------+-----------------------------+
    | Field | Type      | Null | Key | Default           | Extra                       |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    | y     | year(4)   | YES  |     | NULL              |                             |
    | d     | date      | YES  |     | NULL              |                             |
    | t     | time      | YES  |     | NULL              |                             |
    | dt    | datetime  | YES  |     | NULL              |                             |
    | ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    5 rows in set (0.00 sec)
    
    mysql> insert into date_time values(now(),now(),now(),now(),now());
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from date_time;
    +------+------------+----------+---------------------+---------------------+
    | y    | d          | t        | dt                  | ts                  |
    +------+------------+----------+---------------------+---------------------+
    | 2016 | 2016-08-04 | 08:52:58 | 2016-08-04 08:52:58 | 2016-08-04 08:52:58 |
    +------+------------+----------+---------------------+---------------------+

*注意：datetime类型和timestamp类型的区别：*   
*1、timestamp类型也用来表示日期，但是和datetime有所不同，对于timestamp类型，系统自动创建了默认值current\_timestamp（系统日期），这样，即便插入的是null值，甚至不插入数值，也会默认更新为系统时间。datetime类型则没有这个特性*   
*2、timestamp类型还和时区相关，当插入日期时，会先转换为本地时区后存放，而从数据库取出时，也同样需要将日期转换为本地时区后显示，这样，两个不同时区的用户看到的同一个日期可能是不一样的。*   
*3、timestamp支持的时间范围比较小，比datetime类型的范围小*

mysql> desc dt_ts;
    +-------+-----------+------+-----+-------------------+-----------------------------+
    | Field | Type      | Null | Key | Default           | Extra                       |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    | dt    | datetime  | YES  |     | NULL              |                             |
    | ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    2 rows in set (0.00 sec)
    
    mysql> insert into dt_ts values(now(),now());
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from dt_ts;
    +---------------------+---------------------+
    | dt                  | ts                  |
    +---------------------+---------------------+
    | 2016-08-04 09:01:44 | 2016-08-04 09:01:44 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into dt_ts values(null,null);  
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from dt_ts;                
    +---------------------+---------------------+
    | dt                  | ts                  |
    +---------------------+---------------------+
    | 2016-08-04 09:01:44 | 2016-08-04 09:01:44 |
    | NULL                | 2016-08-04 09:02:19 |
    +---------------------+---------------------+
    2 rows in set (0.00 sec)
    mysql> insert into dt_ts(dt) values(null);      
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from dt_ts;               
    +---------------------+---------------------+
    | dt                  | ts                  |
    +---------------------+---------------------+
    | 2016-08-04 09:01:44 | 2016-08-04 09:01:44 |
    | NULL                | 2016-08-04 09:02:19 |
    | NULL                | 2016-08-04 09:05:05 |
    +---------------------+---------------------+
    3 rows in set (0.00 sec)

## 三、字符串类型 ##

![540235-20160927073102328-81825973.png][]

1、cahr和varchar类型的区别：

（1）char列的长度固定为创建表时声明的长度，长度可以为从0~255的任何值；

2、varcahr类型的只为可变长字符串，长度在0~65535之间。

在检索时，char列删除了尾部的空格，而varchar列则保留这些空格
    mysql> desc cv;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | c     | char(2)    | YES  |     | NULL    |       |
    | v     | varchar(5) | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> insert into cv values('ab ','ab ');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select length(c),length(v) from cv;
    +-----------+-----------+
    | length(c) | length(v) |
    +-----------+-----------+
    |         2 |         3 |
    +-----------+-----------+
    1 row in set (0.00 sec)
    
    mysql> select concat(c ,'+'),concat(v ,'+') from cv;
    +----------------+----------------+
    | concat(c ,'+') | concat(v ,'+') |
    +----------------+----------------+
    | ab+            | ab +           |
    +----------------+----------------+
    1 row in set (0.00 sec)

*注意！！*   
*1、修改字段时，例如，char(4)修改为char(2),如果已存在数据长度大于2，alter字段会报错失败*   
*2、mysql中char和varchar的区别：*   
*1）、char是固定长度的，如果长度不足，采用右补空格的方式来填充字符串至规定的长度，而varchar不是，有多长存多长。*   
*2）、对于检索效率来说，char的效率要高于varchar的*   
*3、CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。*   
*4、 在检索时，char列删除了尾部的空格，而varchar列则保留这些空格*

3、binary和varbinary类型

类似于char和varchar，不同的是他们包含二进制字符串而不是非二进制字符串。

4、enum类型

枚举类型，对于1~255个成员的枚举需要1个字节存储；对于255~65535个成员，需要2个字节存储，最多允许有65535个成员；

5、set类型

和enum类似，也是一个字符串对象，可以包含0~64个成员

====================================================================================

# 【常用sql函数】 #

## 一、数据类型转换函数 ##

1、CAST(xxx AS 类型)

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。   
![540235-20160927073102656-684668753.jpg][]   
（1）将字符类型转换为整型

mysql> SELECT CAST('12.1' AS signed);     
    +------------------------+
    | CAST('12.1' AS signed) |
    +------------------------+
    |                     12 |
    +------------------------+

（2）将时间类型转换为字符型

mysql> select cast(current_timestamp as char(20));
    +-------------------------------------+
    | cast(current_timestamp as char(20)) |
    +-------------------------------------+
    | 2016-08-11 07:56:15                 |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select cast(current_timestamp as char(10));
    +-------------------------------------+
    | cast(current_timestamp as char(10)) |
    +-------------------------------------+
    | 2016-08-11                          |
    +-------------------------------------+

（3）小数类型转换为整型，是四舍五入的

mysql> select cast(99.4 as signed); 
    +----------------------+
    | cast(99.4 as signed) |
    +----------------------+
    |                   99 |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> select cast(99.5 as signed); 
    +----------------------+
    | cast(99.5 as signed) |
    +----------------------+
    |                  100 |
    +----------------------+
    1 row in set (0.00 sec)

2、CONVERT(xxx,类型)

![540235-20160927073103047-1759686753.jpg][]

（1）将字符类型转换为整型

mysql> select convert('99.91',signed);
    +-------------------------+
    | convert('99.91',signed) |
    +-------------------------+
    |                      99 |
    +-------------------------+
    1 row in set, 1 warning (0.00 sec)

（2）将小数类型转换为整形（四舍五入）

mysql> select convert(99.91,signed);  
    +-----------------------+
    | convert(99.91,signed) |
    +-----------------------+
    |                   100 |
    +-----------------------+
    1 row in set (0.00 sec)

（3）将整数类型转换为decimal

mysql> select convert(99,decimal);
    +---------------------+
    | convert(99,decimal) |
    +---------------------+
    |                  99 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select convert(99,decimal(3,1));
    +--------------------------+
    | convert(99,decimal(3,1)) |
    +--------------------------+
    |                     99.0 |
    +--------------------------+
    1 row in set (0.00 sec)

（4）转换为日期类型、时间类型等   
mysql> select convert(current\_timestamp,date);   
\+———————————+   
| convert(current\_timestamp,date) |   
\+———————————+   
| 2016-08-11 |   
\+———————————+   
1 row in set (0.00 sec)

mysql> select convert(current_timestamp,time);
    +---------------------------------+
    | convert(current_timestamp,time) |
    +---------------------------------+
    | 10:34:57                        |
    +---------------------------------+
    1 row in set (0.01 sec)
    
    mysql> select convert(current_timestamp,datetime);
    +-------------------------------------+
    | convert(current_timestamp,datetime) |
    +-------------------------------------+
    | 2016-08-11 10:35:03                 |
    +-------------------------------------+

## 二、date\_format函数的使用 ##

mysql> select current_timestamp;
    +---------------------+
    | current_timestamp   |
    +---------------------+
    | 2016-08-11 10:36:57 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select date_format(current_timestamp,'%Y-%m-%d');
    +-------------------------------------------+
    | date_format(current_timestamp,'%Y-%m-%d') |
    +-------------------------------------------+
    | 2016-08-11                                |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select date_format(current_timestamp,'%H:%i:%s');
    +-------------------------------------------+
    | date_format(current_timestamp,'%H:%i:%s') |
    +-------------------------------------------+
    | 10:39:03                                  |
    +-------------------------------------------+

## 三、字符类型的实际长度换算 ##

![540235-20160927073103375-11304620.png][]

# ![540235-20160927073103641-159707726.png][] #

## 其他 ##

![540235-20160927073103938-276121814.png][]

转载：[http://www.cnblogs.com/zhoubaojian/articles/7866288.html][http_www.cnblogs.com_zhoubaojian_articles_7866288.html]

[Mysql_SQL]: http://www.cnblogs.com/cjing2011/p/1581ac6cfb7835c161d39955a6dd4cd1.html
[540235-20160927073100781-1223871751.png]: /images/20220518/24d51a88d66845dc9139735a7672b1cc.png
[540235-20160927073101438-2073998396.png]: /images/20220518/9f5c96c06d9243b3af2c1f134fabee67.png
[540235-20160927073102016-2142672087.png]: /images/20220518/a641cf3e56d34d758f630e52e8d563fc.png
[540235-20160927073102328-81825973.png]: /images/20220518/6aa2228a9d934a269b6cec289b1cb9c9.png
[540235-20160927073102656-684668753.jpg]: /images/20220518/fcc12a7183c743b1bcb43f142dd00ce9.png
[540235-20160927073103047-1759686753.jpg]: /images/20220518/3766185fa21d43c6b5291ffc88375f36.png
[540235-20160927073103375-11304620.png]: /images/20220518/d405e6acf0644a959c67252028f8a280.png
[540235-20160927073103641-159707726.png]: /images/20220518/c14a4cb324964052a7cd6c425b7077c3.png
[540235-20160927073103938-276121814.png]: /images/20220518/11366c4716454807911be4034f6f0d55.png
[http_www.cnblogs.com_zhoubaojian_articles_7866288.html]: http://www.cnblogs.com/zhoubaojian/articles/7866288.html