MySQL（1）操作表、函数

1.表的创建、查看、修改、删除

 

1.1.创建表

1.1.1.主键

（1）在定义列的同时指定主键

CREATE TABLE tb_dept_1
(
id INT(11) Primary Key,
name VARCHAR(25),
location VARCHAR(50)
);

（2）在定义完所有列之后指定主键

CREATE TABLE tb_emp1

(

id INT(11),

name VARCHAR(25),

deptId INT(11),

salary FLOAT,

Primary Key(id)

);

（3）多个主键

Primary Key(id，name)

1.1.2.外键

CREATE TABLE tb_emp5
(
id INT(11) Primary Key,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept_1(id)
);

1.1.3.非空

　　字段名 数据类型 not null

1.1.4.唯一性约束

　　字段名 数据类型 UNIQUE

1.1.5.默认约束

　　字段名 数据类型 DEFAULT 默认值

1.1.6.设置表的属性值自动增加

　　字段名 数据类型 AUTO_INCREMENT

1.2.查看表结构

　　DESCRIBE 表名;

或：

　　DESC 表名;

查看表详细结构语句：

　　SHOW CREATE TABLE <表名\G>;

1.3.修改表

1.3.1.修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

1.3.2.修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

     

1.3.3.修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

1.3.4.添加字段

ALTER TABLE <表名> ADD <新字段名> <新数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

1.3.5.删除字段

ALTER TABLE <表名> DROP <字段名>;

1.3.6.修改字段的排列位置

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

1.3.7.删除表的外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

1.4.删除表

DROP TABLE [IF EXISTS]表1, 表2,…表n;

2.函数

2.1.数学函数

2.1.1.绝对值

ABS(x)

2.1.1.平方根

SQRT(x)

2.1.3.求余

MOD(x,y)

2.1.4.获取整数

不小于x的最小整数：

CEIL(x) 或 CEILING(x)

不大于x的最大整数值：

FLOOR(x)

2.1.5.随机数

RAND() 或 RAND(x)

返回一个随机浮点值，范围在0到1之间

不带参数的RAND()每次产生的随机数值是不同的

当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同

2.1.6.四舍五入

ROUND(x)        最接近于参数x的整数，对x值进行四舍五入

ROUND(x,y)        返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。例如：ROUND(232.38,-2)，返回200

TRUNCATE(x,y)        返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。例如：TRUNCATE(1.31,1)，返回1.3；TRUNCATE(1.99,1)，返回1.9；TRUNCATE(1.99,0)，返回1；TRUNCATE(19.99,-1)，返回10

2.1.7.符号函数

SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1

2.1.8.幂方

POW(x,y)或者POWER(x,y)

2.2.字符串函数

2.2.1.字符数

CHAR_LENGTH(str)        返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符

2.2.2.字符长度

LENGTH(str)        返回值为字符串的字节长度，使用utf8（UNICODE的一种变长字符编码，又称万国码）编码字符集时，一个汉字是3字节，一个数字或字母算1字节。

2.2.3.合并字符串

CONCAT(s1,s2,…)        返回结果为连接参数产生的字符串，或许有一个或多个参数。

如有任何一个参数为NULL，则返回值为NULL。

CONCAT_WS(x,s1,s2,…)        第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。例如：CONCAT_WS('-','1st','2nd','3nd')  ,返回'1st-2nd-3nd'

2.2.4.字母大小写

LOWER (str)或者LCASE (str)可以将字符串str中的字母字符全部转换成小写字母。

UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。

2.2.5.获取指定长度的字符串

LEFT(s,n)返回字符串s开始的最左边n个字符

RIGHT(s,n)返回字符串str最右边的n个字符

2.2.6.删除空格

LTRIM(s)返回字符串s，字符串左侧空格字符被删除

RTRIM(s)返回字符串s，字符串右侧空格字符被删除

TRIM(s)删除字符串s两侧的空格

2.2.7.获取子串

SUBSTRING(s,n,len)        从字符串s返回一个，起始于位置n，长度为len的子字符串。

2.2.8.匹配子串开始位置

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str,str1)3个函数的作用相同，返回子字符串str1在字符串str中的开始位置。

2.2.9.字符串反转

REVERSE(s)

2.3.日期和时间函数

2.3.1.获取当前日期

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回

2.3.2.获取当前时间

CURTIME()和CURRENT_TIME()函数的作用相同，将当前时间以‘HH:MM:SS’或HHMMSS的格式返回

2.3.3.获取当前日期及时间

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE() 4个函数的作用相同，均返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS

2.3.4.时间戳

UNIX_TIMESTAMP(date)        把时间转换成时间戳

FROM_UNIXTIME        将UNIX时间戳转换为普通格式时间

2.3.5.获取月份

MONTH(date)函数返回date对应的月份，范围值为1~12。

MONTHNAME(date)函数返回日期date对应月份的英文全名。

2.3.6.获取星期

DAYNAME(d)函数返回d对应的工作日的英文名称，例如Sunday、Monday等

DAYOFWEEK(d)函数返回d对应的一周中的索引（位置，1表示周日，2表示周一，...，7表示周六）

WEEKDAY(d)返回d对应的工作日索引：0表示周一，1表示周二，...，6表示周日。

2.3.7.获取天数

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1~366。

DAYOFMONTH(d)函数返回d是一个月中的第几天，范围是1~31。

2.3.8.获取年份、季度、小时、分钟和秒钟

YEAR(date)返回date对应的年份，范围是1970~2069

QUARTER(date)返回date对应的一年中的季度值，范围是1~4。

MINUTE(time)返回time对应的分钟数，范围是0~59。

SECOND(time)返回time对应的秒数，范围是0~59。

2.3.9.计算日期和时间

日期增加：

DATE_ADD('2010-12-31 23:59:59',INTERVAL 1 SECOND)

将时间增加1秒后返回，结果为‘2011-01-01 00:00:00’

ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND)

将时间增加1秒后返回，结果为‘2011-01-01 00:00:00’

DATE_ADD('2010-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND)

将指定时间增加1分1秒后返回，结果为‘2011-01-01 00:01:00’

ADDTIME(date,expr)函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

日期减少：

DATE_SUB('2011-01-02', INTERVAL 31DAY)

SUBDATE('2011-01-02', INTERVAL 31 DAY)

以上两个函数执行的结果是相同的，将日期值减少31天后返回，结果都为“2010-12-02”

DATE_SUB('2011-01-01 00:01:00',INTERVAL '1 0:1:1' DAY_SECOND)

将指定日期减少1天，时间减少1分1秒后返回，结果为“2010-12-30 23:59:59”。

SUBTIME(date,expr)函数将date减去expr值，并返回修改后的值。其中，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

两个日期之间的天数：

DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。

2.3.10.将日期和时间格式化

DATE_FORMAT(date,format)根据format指定的格式显示date值。

TIME_FORMAT(time,format)根据表达式format的要求显示时间time。

DATE_FORMAT时间日期格式：

2.4.条件判断函数

2.4.1.IF(expr,v1,v2)

IF(expr, v1, v2)：如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则返回值为v1；否则返回值为v2。

2.4.2.IFNULL(v1,v2)

IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。

2.4.3.CASE

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END：如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

2.5.窗口函数

2.5.1.排名

（1） Row_number()

排名是序号 连续 不重复

（2）Rank()

重复 不连续

（3）Dense_rank()

连续 重复

（4）Ntile()

案例：

 select score,dense_rank() over(order by score desc) as 'rank' from Scores;