Mysql之SQL操作和数据类型 雨点打透心脏的1/2处 2022-05-18 09:57 152阅读 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的任何值; 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> create table cv(c char(2),v varchar(5)); Query OK, 0 rows affected (0.02 sec) mysql> desc cv; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(2) | YES | | NULL | | | v | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into cv(c) values('cc'); Query OK, 1 row affected (0.00 sec) mysql> insert into cv(c) values('ccc'); ERROR 1406 (22001): Data too long for column 'c' at row 1 mysql> insert into cv(c) values('你好'); Query OK, 1 row affected (0.00 sec) mysql> insert into cv(c) values('你好吗'); ERROR 1406 (22001): Data too long for column 'c' at row 1 //这个说明,一个汉字占用一个字符,char(n)里的n表示的是字符数而不是字节数 //但是char类型占用的字节数是一定的,也就是说,同样为char类型,实际存储数字、字母或是汉字,实际存储的字符数是不一定的 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
还没有评论,来说两句吧...