Mysql常用的基本语法

1、系统函数

--1、案例：显示表名的结构
DESC employees

--2、版本号
SELECT VERSION();

--3、数据库
SELECT DATABASE();

--4、用户
SELECT USER();

--5、返回服务器的连接数，也就是到现在为止MySQL服务的连接次数；
SELECT CONNECTION_ID();

--6、查询某列是否有索引
EXPLAIN SELECT * FROM Table

2、字符串函数

--DISTINCT = 去重
--1、案例：查询员工表中所涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees


--CONCAT(last_name,first_name)字符串拼接
--3、案例：查询员工名和姓连接成一个字段，并且显示为姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees


--IFNULL(可能为NULL的值,转化成特定的值)
--4、案例：将Null转换为0
SELECT IFNULL(commission_pct,0),commission_pct FROM employees

--ESCAPE = 转义
--5、案例：查询员工中第二个字符为_的员工们
SELECT last_name FROM employees WHERE last_name LIKE '_A_%' ESCAPE 'A'

--6、案例：姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;

--7、案例：lpad用指定的长度实现左填充指定长度
SELECT LPAD('周小雨',5,'-') AS out_put

--8、案例：lpad用指定的长度实现右填充指定长度
SELECT RPAD('周小雨',5,'-') AS out_put

--9、案例：Trim去掉左右的空格
SELECT TRIM('  张翠山  ') AS 姓名;

--10、案例：REVERSE将值进行颠倒
SELECT REVERSE('abc');

3、数学函数

--1、Round 四舍五入
SELECT ROUND(1.5);

--保留多少位
SELECT ROUND(1.457333,3);

--2、Ceil 向上取整,取大的
SELECT CEIL(1.6);

--3、Floor 向下取整 取小的
SELECT FLOOR(1.6);

--4、TRUNCATE 截断,保留1位小数点
SELECT TRUNCATE(1.6541,1);

--5、MOD取余 1
SELECT MOD(10,3);

4、日期函数

--1、NOW() 返回当前系统时间
SELECT NOW();

--2、CURDATE() 返回当前系统时间,但是不包含时间
SELECT CURDATE();

--3、CURTIME() 返回当前时间,不包含日期
SELECT CURTIME();

--4、获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月份英文;
SELECT Day(NOW()) 日;
SELECT HOUR(NOW()) 小时;
SELECT MINUTE(NOW()) 分钟;
SELECT SECOND(NOW()) 秒;

--5、查询日期之间之差
SELECT DATEDIFF('2020-03-27','2020-03-20');

--6、返回data在一年中的日数,值为1~366范围内
SELECT DAYOFYEAR('2020-03-27');

--7、返回date一年中的季度，范围1到4
SELECT QUARTER('2020-03-27'); 

--8、DATE_SUB从当前日期减去指定的时间间隔  INTERVAL日期之间的计算
DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= create_time

5、流程控制函数

--1、If函数：if-else的效果
SELECT IF(10>5,'大','小');
--案例：查询某些员工是否有奖金,有输出1,没有输出2
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,哈哈') AS 备注 FROM employees

--2、Case函数 Switch效果
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE 0
END AS 新工资
FROM employees;
--案例：查询工资>20000显示A,>15000显示B,>10000显示C,ELSE D
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>11000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees

6、多表连接

/*-----------------------------------------------连接查询-----------------------------------*/
--1、等值连接
--查询员工名和他所对应的部门名
SELECT CONCAT(last_name,'_',first_name) 姓名,department_name 部门名
FROM employees,departments
WHERE employees.department_id = departments.department_id

--2、非等值连接
SELECT salary 工资,grade_level 等级
FROM employees,job_grades
WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal
ORDER BY grade_level
AND job_grades.grade_level='A'


--3、自连接,把一张表当成两张表看(一般用于三级分类,子分类)
SELECT e.employee_id,e.first_name,m.employee_id,m.first_name
FROM employees e,employees m
WHERE e.employee_id = m.manager_id

7、SQL建表

--1、设置主键
ALTER TABLE b1 add PRIMARY key(c1)
--2、修改列的长度
ALTER TABLE b1 MODIFY c2 VARCHAR(100)
--3、添加字段,设置长度
ALTER TABLE b1 add c3 VARCHAR(50)
--4、将b2中的数据复制到b1表中
INSERT INTO b1 SELECT * FROM b2
--5、创建表设置主键和自增
CREATE TABLE ROLE1(
	id INT AUTO_INCREMENT PRIMARY KEY,
	role_name VARCHAR(50)
)

8、行转列

--1、使用CASE WHEN THEN行转列
SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score
GROUP BY userid

--2、使用IF行转列
SELECT userid,
SUM(IF(`subject` = '语文',score,0)) as '语文',
SUM(IF(`subject` = '数学',score,0)) as '数学',
SUM(IF(`subject` = '英语',score,0)) as '英语',
SUM(IF(`subject` = '政治',score,0)) as '政治'
FROM tb_score
GROUP BY userid