MySQL入门-基本命令（查询&常见函数)

MySQL服务启动

1. 计算机管理–服务和应用程序–服务–MySQL_xxx–双击点击启动/关闭（手动/自动可设置更换）
2. cmd–管理员身份运行–输入net start mysql_xxx / net stop mysql_xxx进行启动/关闭

SQL分类

1. DQL
2. DML（Data Manipulation Language)：数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性。
3. DDL（Data Definition Language)：数据定义语句，用于库和表的创建、修改、删除。
4. TCL（Transaction Control Language）：事物控制语言

SQLyog使用

1. 右键点击root@localhost – 执行sql脚本 --刷新【导入数据库】
2. 在目录栏查看数据库下各表结构

常见命令语句

• 显示所有数据库show databases;
• 显示表结构并查询其中的全部数据desc test;
  
• 使用某个库use test;
• 加 # or --为添加单行注释，加/* */添加多行注释，加着重号可以标识字段。

DQL语言——查询

基础查询

select 查询列表 from 表名;

• 查询列表可以是：表中字段、常量值、表达式、函数
• 查询结果是一个虚拟的表格

-- 查询一个表的单个字段
select last_name from employees;
-- 查询一个表的多个字段
select last_name, salary, email from employees;
-- 查询表中的所有字段
SELECT 
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;
 #可双击选择字段，并右键选择格式化，应该是可以F12快捷键调整，但是我电脑做不到...
select * from employees;  # *会使得字段顺序与表顺序一致
-- 查询常量值
select 100;
select 'john';
-- 查询表达式
select 100%98;
select 100*98;

重命名、函数运算等基础操作

-- 查询函数
select version(); # 5.5.62
-- 为字段重新定义名称
/*
便于理解；如果查询的字段有重名的情况，可以区分开来
但需注意，如别名存在特殊字符，需添加双引号
*/
select 100%98 as result;
select last_name as 姓氏, first_name as 名字 from employees; # 使用as
select last_name 姓氏, first_name 名字 from employees; # 使用空格
-- 查询多个字段，只对一个字段进行重命名
select last_name, job_id, salary as sal from employees;
select last_name, job_id, salary*12 as annual_sal from employees;

去重查询、拼接字段

-- 去重 distinct
-- 如：查询员工表中的涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
-- + 号的作用 --> 运算符 
select 100+90; 
# 两个操作数均为数值型，则作加法运算
select '123'+90; # 213
select 'john'+90; # 90
select null+90; # (NULL)
# 其中一方为字符型，试图将字符型转换为数值型，如果转换成功则继续做加法运算，如果转换失败，则将字符型数值转换为0；其中一方为null，则结果肯定为null。
-- 拼接 concat
SELECT 
  CONCAT(last_name, first_name) AS 姓名 
FROM
  employees ;
# 注意NULL的情况，因此需要在拼接时对NULL进行识别，由此引入IFNULL（）函数
SELECT 
  CONCAT(
    `last_name`,
    `first_name`,
    IFNULL(commission_pct, 0)
  ) AS output 
FROM
  employees ;

条件查询

select 
	查询列表
from
	表名
where
	筛选条件;

分类：

1. 条件表达式筛选：
   条件运算符 >, <, =, !=, <>, >=, <=
2. 逻辑表达式筛选
   逻辑运算符 &, |, !, and, or, not
3. 模糊查询
   like, between and, in, is null

条件表达式筛选

-- 筛选工资大于12000的全部员工信息
SELECT 
	* 
FROM
	`employees` 
WHERE 
	`salary` > 12000 ;
-- 查询部门编号不为90的员工名+部门编号
SELECT
	CONCAT(`last_name`,`department_id`) AS info
FROM
	`employees`
WHERE
	`department_id`<>90;
-- 查询员工号为176的员工的姓名和部门号和年薪
SELECT
`last_name`,
`department_id`,
`salary`*12*(1+IFNULL(`commission_pct`,0))  AS 年薪
FROM
`employees`;

逻辑表达式筛选

-- 查询工资在1w-2w区间的员工名字工资及奖金
SELECT 
  `last_name`,
  `salary`,
  `commission_pct` 
FROM
  `employees` 
WHERE `salary` >= 10000 
  AND `salary` <= 20000 ;
-- 查询部门编号不是90-110之间或者工资在1.5w以上的员工名字工资及奖金
 SELECT 
  `last_name`,
  `salary`,
  `commission_pct` 
FROM
  `employees` 
WHERE `salary` >= 15000 
  OR `department_id` > 110 
  OR `department_id` < 90 ;

SELECT 
  `last_name`,
  `salary`,
  `commission_pct` 
FROM
  `employees` 
WHERE `salary` >= 15000 
  OR NOT (
    `department_id` <= 110 
    AND `department_id` >= 90
  ) ;

模糊查询

• like一般与 % 通配符（任意字符，包括0个）搭配使用
• 如果确定字符个数的通配符，使用下划线
• 如果需要使用通配符查询，则添加转义字符\或者escape

-- 查询员工名字中包含字符a的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `last_name` LIKE '%a%' ;
-- 查询员工名字中第一个字符为e、第五个字符为a的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `last_name` LIKE '__e_a%' ;
-- 查询员工名字中第二个字符为_的员工信息
SELECT
`last_name`
FROM 
`employees`
WHERE
`last_name` LIKE '_\_%';

SELECT
`last_name`
FROM 
`employees`
WHERE
`last_name` LIKE '_$_%' ESCAPE '$';

• between and：可以提高语句简洁度；且包含临界值；不可以颠倒顺序。

--查询员工编号在100-120的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `employee_id` BETWEEN 100 
  AND 120 ;

• in提高语句简洁度；in列表的值得类型必须一致或兼容；

--查询员工的工种编号时IT_PROG、AD_PRES、AD_VP中一个的员工名字和工种编号
SELECT 
  `job_id`,
  `last_name` 
FROM
  `employees` 
WHERE `job_id` IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;

• is null 用于判断空值
• 仅仅可以判断null值，但可读性较高

-- 查询没有奖金得员工名和奖金率
SELECT
`last_name`,
`commission_pct`
FROM
`employees`
WHERE
`commission_pct` IS NULL;
-- 查询有奖金得员工名和奖金率
SELECT
`last_name`,
`commission_pct`
FROM
`employees`
WHERE
`commission_pct` IS NOT NULL;

安全等于<=>

• 可读性较差，但是可以判断null值和其他值

-- 查询没有奖金得员工名和奖金率
SELECT
`last_name`,
`commission_pct`
FROM
`employees`
WHERE
`commission_pct` <=> NULL;
-- 查询工资为12000的员工信息
SELECT
`last_name`,
`commission_pct`
FROM
`employees`
WHERE
`salary` <=> 12000;

排序查询

• 添加 order by 排序列表 asc/desc。一般语句放在除了limit子句以外的最后。
• asc代表的是升序（默认是升序）、desc代表的是降序。

--查询员工信息，要求工资从高到低排序
SELECT
*
FROM 
`employees`
ORDER BY
`salary` DESC;
-- 查询部门编号>=90的员工信息，按入职时间的先后顺序排序
SELECT
*
FROM
`employees`
WHERE
`department_id` >=90
ORDER BY
`hiredate` ASC;

按表达式排序

-- 按年薪的高低显示员工的信息和年薪
SELECT
*,
salary *12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
`employees`
ORDER BY
salary *12*(1+IFNULL(`commission_pct`,0)) ASC; # 此处可以order by 年薪 asc; 效果一致

按函数排序

-- 按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT 
`last_name`,
`salary`
FROM
`employees`
ORDER BY
LENGTH(`last_name`) DESC;

多排序条件

-- 查询员工信息，要求先按照工资排序，再按照员工编号排序
SELECT 
  * 
FROM
  `employees` 
ORDER BY `salary` ASC,
  `employee_id` DESC ;

常见函数

• select 函数名(实参列表) 【from 表】

单行函数

字符函数

• length() 获取参数值的字节个数：SELECT LENGTH('你好'); # 6
• concat() 拼接字符串：

SELECT 
  CONCAT(`last_name`, '_', `first_name`) 
FROM
  `employees` ;

• upper() / lower()

SELECT 
  CONCAT(
    LOWER(`last_name`),
    '_',
    UPPER(`first_name`)
  ) 
FROM
  `employees` ;

• substr() 截取字符：索引从1开始，第一个pos为指定索引处至后面所有字符；第二个pos即为第一个pos指定索引处开始第二个pos字符长度的字符

SELECT 
  SUBSTR(
    '我家小豆丁什么时候来呀！',
    3
  ) output ;
  # 小豆丁什么时候来呀！
  SELECT 
  SUBSTR(
    '我家小豆丁什么时候来呀！',
    3,3
  ) output ;
  # 小豆丁

-- 姓名中首字符大写，其他字符小写然后用_拼接，显示成 姓名
SELECT 
  CONCAT(
    UPPER(SUBSTR(`last_name`, 1, 1)),
    LOWER(SUBSTR(`last_name`, 2)),
    '_',
    UPPER(SUBSTR(`first_name`, 1, 1)),
    LOWER(SUBSTR(`first_name`, 2))
  ) AS 姓名 
FROM
  `employees` ;

• instr() 返回子串第一次出现的索引，如果找不到则返回为0

SELECT 
  INSTR(
    '小豆丁是一只可爱的黑柴柴~',
    '小豆丁'
  ) AS output ;
# 1

• trim() 去前后指定字符，如不指定则为去空格

SELECT 
  TRIM('   小豆丁    ') AS output ; 
# 小豆丁
SELECT 
  TRIM('*' FROM '****^^小*豆丁****') AS output ;
# ^^小*豆丁

• lpad() / rpad() 用指定的字符实现左填充或右填充指定长度，若数字小于字符原有长度，则为左截取或右截取

SELECT 
LPAD('小豆丁什么都爱吃哇',5, '$') AS output;
# 小豆丁什么
SELECT 
LPAD('小豆丁',5, '$') AS output;
# $$小豆丁

• replace()

SELECT 
  REPLACE(
    'MOMO喜欢小豆丁',
    'MOMO',
    '大可'
  ) AS output ;
# 大可喜欢小豆丁

数学函数

• round() 四舍五入

SELECT ROUND(1.45); # 1
SELECT ROUND(1.457989,3); # 1.458

• ceil() 向上取整，返回>=参数的最小整数

SELECT CEIL(1.50); # 2
SELECT CEIL(-1.50); # -1

• floor() 向下取整，返回<=参数的最大整数

SELECT FLOOR(-9.50); # -10

• truncate() 保留小数后几位

SELECT TRUNCATE(-9.50,4); # -9.5000

• mod() 取余

SELECT MOD(-9.50,4); # -1.50

日期函数

• now() 返回当前系统日期时间

select now(); # 2021-12-15 16:03:01

• curdate() 返回当前系统日期不包含时间
• curtime() 返回当前系统时间不包含日期
• 获取指定的部分：年year()、月month()、月英文名字monthname()、日day()、小时hour()、分钟minute()、秒second()
• str_to_date() 将日期格式的字符转换为日期类型
  

-- 查询入职日期为1992年4月3日的员工信息
SELECT 
*
FROM
`employees`
WHERE
`hiredate`=STR_TO_DATE('1992-4-3','%Y-%c-%d');

• date_format() 将日期转换为字符

SELECT 
  DATE_FORMAT(NOW(),
  '%y年%m月%d日' ) AS output;
# 21年12月15日  

-- 查询有奖金的员工名和入职日期（月/日/年）
SELECT 
  `last_name`,
  DATE_FORMAT(`hiredate`, '%m/%d/%Y') AS hire_time 
FROM
  `employees` 
  WHERE
  `commission_pct` IS NOT NULL;

其他函数

• version() 查看当前版本号
• database()
• user()

流程控制函数

• if()

SELECT
`last_name`,
`commission_pct`,
IF(`commission_pct` IS NULL, 'no bonus','bonus!')
FROM
`employees`;

• case()
  case 要判断的字段或表达式
  when 常量1 then 要显示的值1或语句1；
  when 常量2 then 要显示的值2或语句2；
  ……
  else 要显示的值n或语句n；
  end

/*查询员工的工资：
部门号=30， 显示的工资为1.1倍
部门号=40， 显示的工资为1.2倍
部门号=50， 显示的工资为1.3倍
其他部门， 显示的工资为原工资
*/
SELECT 
 `salary` AS initial_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 `salary` 
 END AS new_salary 
FROM
 `employees` ;

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n或语句n；
end

/*查询员工工资情况
如果大于2w 显示A级别
如果大于1.5W 显示B级别
如果大于1w 显示C级别
否则 显示D级别
*/
SELECT 
  `salary`,
  CASE
    WHEN `salary` > 20000 
    THEN 'A' 
    WHEN `salary` > 15000 
    THEN 'B' 
    WHEN `salary` > 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS salary_rank 
FROM
  `employees` ;

分组函数

• 一般做统计使用。

分组查询

连接查询

子查询

分页查询

union联合查询