sql语法练习

这不算一篇博客吧。。。。只是突然发现草稿箱里还有这个，这是很早以前的了，就把以前的练习代码发一下，也算是存个笔记吧。。。。使用的库和表的下载地址：尚硅谷MySQL，如果有兴趣了解更多相关知识的话，可以来我的个人网站看看：eyes++的个人空间

代码如下：

-- 创建数据库
CREATE DATABASE demo;
CREATE DATABASE IF NOT EXISTS demo;

-- 修改库的字符集
ALTER DATABASE demo CHARACTER SET utf8;

-- 库的删除
DROP DATABASE IF EXISTS demo;

-- 指定数据库
USE demo;

/*
表的创建
create table 表名(
    列名  列的类型[(长度) 约束]
    列名  列的类型[(长度) 约束]
    列名  列的类型[(长度) 约束]
    ...
    列名  列的类型[(长度) 约束]
)
*/

-- 创建两个表
CREATE TABLE test1(
PersonID INT, 
HeadUrl VARCHAR (255)
);

CREATE TABLE test2(
ManagerID INT, 
HeadUrl VARCHAR (255)
);

-- 删除表
DROP TABLE test2;

-- 查看是否创建成功
SHOW TABLES;
SELECT * FROM test1;
DESC test1;
SELECT * FROM test2;
DESC test2;

-- 插入数据(方法一)
INSERT INTO test1(PersonID, HeadUrl) 
VALUES(1, 'https://www.github.com/1.png');

-- 插入数据(方法二)
INSERT INTO test1
SET PersonID=2, HeadUrl='https://www.github/2.png';

-- 修改数据
UPDATE test1 SET 
WHERE PersonID = 2;

-- 无重复查询
SELECT DISTINCT PersonId FROM test1;

-- 测试+号的作用
SELECT '123'+2;
SELECT '你好'+2;
SELECT '你好'+'不好';

-- 测试concat()
SELECT 
  CONCAT(PersonID, HeadUrl) AS 和 
FROM
  test1 ;

-- 使用myemployees库
USE myemployees;

-- 查看表
SHOW TABLES;

-- 表内所有内容
SELECT * FROM employees;

/*
模糊查询
like
between and
in
is null | is not null
*/

/* 
模糊查询 ———— like
一般和通配符搭配使用
% 匹配任意多个字符，包含0个
_ 匹配任意单个字符
\ 转义字符
自定义转义字符(示例的意思是$充当转义字符)： like '_$_%' escape '$'
*/

-- 查询员工中电话号码第三位是0，第五位为7的员工名和工资
SELECT 
  CONCAT(first_name, ' ', last_name) 姓名,
  salary,
  phone_number
FROM
  employees 
WHERE phone_number LIKE '__0_5%' ;

/*
模糊查询 ———— between and
该方法可以提高语句的简洁度
包含临界值
需要注意的是，R between A and B 等价于 R >= A and R <= B ， 顺序不能反
*/
SELECT 
  CONCAT(first_name, ' ', last_name) 姓名,
  salary
FROM
  employees 
WHERE salary BETWEEN 4000 AND 7000 ;

/*
模糊查询 ———— in
提高语句简洁度
in 列表的值类型必须统一或兼容
不支持通配符写法
*/
-- 查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的员工名和工种编号
SELECT 
  CONCAT(first_name, ' ', last_name),
  job_id
FROM
  employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;

/*
模糊查询 ———— is null | is not null
= 或 <> 不能判断null值
<=> 安全等于  也可以判断Null值
*/
-- 查询没有奖金的员工名和奖金率
SELECT 
  CONCAT(first_name, ' ', last_name) AS 姓名,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NULL ;

-- 查询有奖金的员工名和奖金率
SELECT 
  CONCAT(first_name, ' ', last_name) AS 姓名,
  commission_pct AS 奖金率
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

-- 查询没有奖金的员工名和奖金率
SELECT 
  CONCAT(first_name, ' ', last_name) AS 姓名,
  commission_pct 
FROM
  employees 
WHERE commission_pct <=> NULL ;

-- 查询薪水为4000的员工名和奖金率和薪水
SELECT 
  CONCAT(first_name, ' ', last_name) AS 姓名,
  commission_pct 奖金率,
  salary 薪水 
FROM
  employees 
WHERE salary <=> 4000 ;

-- 按年薪高低显示员工名字、职位和年薪
SELECT 
  CONCAT(first_name, ' ', last_name) 姓名,
  job_id 职位,
  salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪 
FROM
  employees 
ORDER BY 年薪 DESC ;

-- 按名字长度升序排序显示员工姓名和工资，长度相同时按工资降序
SELECT
  CONCAT(first_name, ' ', last_name) 姓名,
  salary 
FROM
  employees 
ORDER BY LENGTH(last_name) ASC, salary DESC ;

/*
常见函数
单行函数：concat()， length(), ifnull()....
单行函数又分为字符函数、数学函数、日期函数、其他函数、流程控制函数
多行函数：做统计使用，又称为统计函数，或者聚合函数，或者组函数
*/

/*
字符函数： 
length()： 获取参数值的字节数
concat()： 拼接字符串
upper(), lower(): 转换成大写或小写
substr(), substring()： 截取字符串(索引从1开始)，索引是字符长度
    substr(string, beging, [length])
instr(string, string)：返回字符第一次出现的位置索引，索引从1开始
trim()：去除字符串首尾的空格或指定字符
lpad()：使用指定字符实现左填充
rpad(): 使用指定字符实现右填充
replace()： 替换
*/

SELECT LENGTH("eyes++");
SELECT LENGTH("瞳孔");  # 6
SHOW VARIABLES LIKE '%char%';  # 查看当前使用的字符集

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

SELECT UPPER("Eyes++");
SELECT LOWER("Eyes++");

SELECT SUBSTR('你好SQL！', 3) out_put;
SELECT SUBSTR('你好SQL！', 3, 2) out_put;

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

-- instr()
SELECT INSTR('你好SQL！S', 'S') out_put;

-- trim()
SELECT TRIM('    hello    ') out_put;
SELECT TRIM('a' FROM 'aaaaaaa你好aaaaa哈哈aaaaaaaaaaaaaaaa') out_put;
SELECT TRIM('aa' FROM 'aaaaaaa你好aaaaa哈哈aaaaaaaaaaaaaaaa') out_put;

-- lpad()和rpad()，注意，指定长度小于原字符串的话会自动截断
SELECT LPAD('你好', 10, '*');
SELECT RPAD("你好", 10, "*");

-- replace() 替换
SELECT REPLACE('eyes++是一个程序员', '程序员', '自由职业者');

/*
数学函数
round()：四舍五入，只有1个参数默认取整，第二个参数是小数位数
ceil() 向上取整
floor() 向下取整
truncate() 截断，第二个参数是指留取小数点后几位
mod() 取余
*/

-- round()
SELECT ROUND(1.55);
SELECT ROUND(-1.55);
SELECT ROUND(1.552, 2);

-- ceil()和floor()
SELECT CEIL(-1.02);
SELECT FLOOR(5.3);

-- truncate() 
SELECT TRUNCATE(13.21456, 2);

-- mod()
SELECT MOD(-10, -3);

/*
日期函数
now() 返回当前系统日期和时间
curdate() 返回当前系统日期，不包含时间
curtime() 返回当前时间，不包含日期
str_to_date()：将日期格式的字符串转换成指定格式的日期
date_format()：将日期转换成字符
*/

SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

-- 返回时间的指定部分
SELECT YEAR(NOW()) 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
SELECT DAY(NOW()) 日;
SELECT HOUR(NOW()) 时;
SELECT MINUTE(NOW()) 分;
SELECT SECOND(NOW()) 秒;
SELECT YEAR('1998-2-3') 年;
SELECT YEAR(hiredate) FROM employees;

-- 日期格式转换
SELECT STR_TO_DATE('2021-8-12', '%Y-%c-%d') out_put;
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') out_put;

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

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

/*
其他函数
verson() 当前数据库版本号
database() 当前使用的库
user() 当前用户
*/

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

/*
流程控制函数
if函数：实现类似于 if else的效果，类似于三元运算符
case函数
*/

SELECT IF(10>5, '大', '小');
SELECT 
  last_name,
  commission_pct,
  IF(
    commission_pct IS NULL,
    '没奖金',
    '有奖金'
  ) 备注
FROM
  employees ;

-- switch case模式
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 salary 
  END AS 新工资 
FROM
  employees ;

-- 类似于多重if
SELECT 
  salary ,
  CASE
    WHEN salary > 20000 
    THEN 'A' 
    WHEN salary > 15000 
    THEN 'B' 
    WHEN salary > 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS 工资级别 
FROM
  employees ;

/*
分组函数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
count() 计算个数(只计算非空)
*/

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

-- 统计多少种工资
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;

-- 统计行数
SELECT COUNT(*) FROM employees;  -- 该方法效率高，推荐使用
SELECT COUNT(1) FROM employees;

/*
分组查询

select 分组函数, 列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]

*/
-- 查询每个部门的平均工资
SELECT 
  department_id,
  AVG(salary) 
FROM
  employees 
GROUP BY department_id ;

-- 查询每个工种的最高工资
SELECT 
  MAX(salary),
  job_id
FROM
  employees 
GROUP BY job_id ;


-- 查询每个位置上的部门个数
SELECT 
  COUNT(*),
  location_id 
FROM
  departments 
GROUP BY location_id ;

-- 查询邮箱中包含a字符的，每个部门的平均工资
SELECT 
  AVG(salary),
  department_id,
  email
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY department_id ;

-- 查询每个部门的员工个数
SELECT 
  COUNT(*),
  department_id
FROM
  employees 
GROUP BY department_id ;

-- 格局上一个结果进行筛选，查询哪个部门的员工个数大于2
SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 2 ;

/*
连接查询
按功能分类：
  内连接：等值连接
          非等值连接
          自连接
  外连接：左外连接
          右外连接
          全外连接
  交叉连接
*/

/*
等值连接
多表等值连接的结果为多表的交集部分
n表连接至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面的所有内容
*/
SELECT 
  NAME,
  boyname 
FROM
  boys,
  beauty 
WHERE beauty.`boyfriend_id` = boys.`id`;

-- 查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT `department_name`, d.`manager_id`, MIN(`salary`)
FROM departments d, employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY `department_name`, d.`manager_id`;

-- 查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title, COUNT(*)
FROM employees e, jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

-- 三表连接：查询员工名、部门名和所在城市
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

/*
非等值连接
*/
CREATE TABLE job_grades(
grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT
);
DESC job_grades;
INSERT INTO job_grades
VALUES ('F', 1000, 2999);
INSERT INTO job_grades
VALUES ('E', 3000, 5999);
INSERT INTO job_grades
VALUES ('D', 6000, 9999);
INSERT INTO job_grades
VALUES ('C', 10000, 14999);
INSERT INTO job_grades
VALUES ('B', 15000, 24999);
INSERT INTO job_grades
VALUES ('A', 25000, 40000);
SELECT * FROM job_grades;

-- 查询员工工资级别为A的员工名字、薪水
SELECT 
  last_name,
  salary,
  grade_level 
FROM
  employees e,
  job_grades g 
WHERE salary BETWEEN g.`lowest_sal` 
  AND g.`highest_sal` 
  AND g.`grade_level` = 'A' ;

/*
自连接
*/
-- 查找员工的上司
SELECT e.`employee_id`, e.`last_name`, m.`employee_id`, m.`last_name`
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;

/*
sql99语法
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]

内连接 ： inner
外连接
      左外： left[outer]
      右外： right[outer]
      全外： full[outer]
交叉连接： cross
*/
-- 查询员工名以及对应的部门名
SELECT 
  last_name,
  department_name 
FROM
  departments d 
INNER JOIN employees e 
ON e.`department_id` = d.`department_id` ;

-- 查询名字中包含e的员工名和工种名
SELECT last_name, e.job_id, job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE e.`last_name` LIKE '%e%';

-- 查询员工的工资级别
SELECT salary, grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY e.`salary`;

-- 查询工资级别的个数大于20的个数，并且按工资级别降序
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

-- sql99的自连接查询
SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`;

/*
外连接
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
1. 外连接的查询结果为主表中的所有记录
   如果从表中有和他匹配的则显示匹配的值
   如果从表中没有和它匹配的则显示null
   外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录

2. 左外连接： left join左边的是主表
   右外连接： right join右边的是主表
3. 左外和右外交换两个表的顺序，可以实现同样的效果
*/
USE girls;
SELECT * FROM beauty;
SELECT * FROM boys;

-- 查询男朋友不在男神表的女神名
-- 示范
SELECT bu.name, bo.*
FROM beauty bu
LEFT OUTER JOIN boys bo
ON bu.`boyfriend_id` = bo.`id`;
-- 实际
SELECT bu.name
FROM beauty bu
LEFT OUTER JOIN boys bo
ON bu.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NOT NULL;

/*
子查询
含义：出现在其他语句中的select语句称为子查询或内查询

按子查询出现的位置：
   select后面： 仅仅支持标量子查询
   from后面： 支持表子查询
   where或having后面： 标量子查询、列子查询、行子查询
   exists后面：（相关子查询）
按结果集的行列数不同：
   标量子查询（结果集只有一行一列）
   列子查询（结果集只有一列多行）
   行子查询（结果有一行多列）
   表子查询（结果集一般为多行多列）
*/

-- 案例： 查询谁的工资比Abel高
USE myemployees;
-- 1.查询Abel的工资
SELECT 
  salary 
FROM
  employees 
WHERE last_name = 'Abel' ;

-- 2.查询员工的信息，满足salary大于步骤一的结果
SELECT 
  * 
FROM
  employees 
WHERE salary > 11000 ;

-- 3.合并两次查询
SELECT 
  * 
FROM
  employees 
WHERE salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;

-- 案例：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
-- 1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
-- 2.查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
-- 3.查询员工姓名，job_id和工资
SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id = 
  (SELECT 
    job_id 
  FROM
    employees 
  WHERE employee_id = 141) 
  AND salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE employee_id = 143) ;

-- 案例：查询最低工资是谁
SELECT 
  * 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

一些截图：