MySQL（一）：语言分类、数据类型、约束条件、常用函数

一、数据可操作语言分类

• DDL：数据定义语言，创建、修改、删除数据库中的对象(库/表/视图/索引)，如create、alter、drop
• DML：数据操作语言，操作表中的记录，如insert、update、delete
• DQL：数据查询语言，查询表中的记录，如select
• DCL：定义数据库访问权限和安全级别，如grant、revoke

二、数据类型

• int：整数型，默认长度11位
• float：单精度浮点型，float(10,2)——最长10位，包含2位小数
• decimal：十进制小数型，用于金额、价格等，默认decimal(10,0)—10位，包含0位小数
• char：固定长度字符串型，1-255，默认char(1)
• varchar：可变字符串型，1-255，必须指定长度
• text：长文本字符串型，最长65535
• date：日期型，'yyyy-mm-dd'
• time：时间型，'hh:mm:ss'
• datetime：日期时间型，'yyyy-mm-dd hh:mm:ss'
• timestamp：时间戳，在1970-01-01 00:00:00和2037-12-31 23:59:59之间

三、约束条件

1. primary key：主键约束，可在建表语句的任意部分，联合主键primary key(A,B)
2. foreign key：外键约束，只能在建表语句的结尾，如foreign key(字段名) references 主表(主键字段名)
3. auto_increment：自动增长约束，仅限主键列
4. not null：非空约束
5. default：默认约束，如默认0即defult 0
6. unique：唯一性约束

四、常见函数

1、字符串函数：

• concat('a','b','c')：合并字符串
• instr('abc','a')：查找
• left('abc',1)：截取左起
• mid('abc',1)：截取中间起
• substring('abc我和你',1,3)：截取中间起
• ltrim(' abc')：去掉左边空格
• trim(' abc ')：去掉两边空格
• upper('abc')：大写转换
• lower('ABC')：小写转换
• replace('abc我和你','abc','ABC')：替换

2、数学函数：

• abs(-2)：绝对值
• floor(-1.23)：向下取整
• ceiling(1.23)：向上取整
• round(1.1314,2)：四舍五入保留小数，默认0位

3、日期函数：

• date('2020-01-01 15:37:00')：文本转换成日期或截取标准日期部分
• week('20200101',1)：返回一年中的第几周
• year('2020-01-01')：返回年份
• month('20200101')：返回月份
• day('20200101')：返回日
• hour('2020-01-01 01:00:01')：返回小时
• date_add('20200101',interval 1 day)：日期相加，同adddate()
• date_sub('20200101',interval 1 day)：日期相减，同subdate()
• curdate()：返回当前日期
• curtime()：返回当前时间
• now()：返回当前日期和时间
• datediff('20200101','20200606')：返回日期的间隔天数
• unix_timestamp('20200608')：返回时间戳格式的日期
• from_unixtime('1588990000')：日期由时间戳格式转换为标准格式
• date_formate('2010-03-06','%Y-%m')：返回X年X月格式的时间

4、其他函数

（1）分组合并函数：group_concat([distinct] 字段名 [order by 字段名])

• 说明：可以去重、排序，会忽略空值null
• 使用：查询语句通常中有group by，且放在select或having后面
• 案例：部门员工姓名、选课课号、投资币种

-- 查询各部门的员工姓名
select deptno,group_concat(distinct ename order by sal desc)
from emp
group by deptno;

（2）逻辑函数：ifnull(字段名,value)

• 说明：判断值是否为空null，不为空则返回实际值，为空则返回指定值

-- 查询每位员工的实发工资(基本工资+提成，没有提成记为0)
select ename,sal+ifnull(comm,0) 实发工资
from emp;

（3）if函数：if(expression,value1,value2)

• 说明：判断expression条件满足与否，是则返回value1，否返回value2

-- 查询每位员工的工资等级，≥3000为高，1500-3000为中，＜1500为低
select *,if(sal>=3000,'高',if(sal>=1500,'中','低')) 等级
from emp;

（4）逻辑表达式：case...when...else...end

• 说明：通常用于区分类别、等级等场景，与if函数可互换使用

-- 查询每位同学的性别，返回“男”、“女”或“其他”，2种方式
select *,
    case when sex='1' then '男'
         when sex='0' then '女'
         else '其他' 
    end 性别
from student;  
-- case...when...另一种写法
select *,
    case sex
         when '1' then '男'
         when '0' then '女'
         else '其他' 
    end 性别
from student;