MySQL 基础day1——查询、替换、排序、去重

1.基础查询

 -- 查看所有的库
 SHOW DATABASES;
 USE mysql;
 -- 选库
 -- 格式：use 库名；
 USE myemployees;
 #查看库中所有的表
 SHOW TABLES;
 /*
 多行注释
 查看表中内容
 格式： select 字段名1，字段名2，........
 from 表名；
 select *
 from 表名；
 */
 SELECT first_name,salary
FROM employees;
SELECT first_name, job_id
FROM employees;
SHOW DATABASES;

SELECT 1+1;
FROM DUAL;  # dual是一张虚拟表

SELECT 1 + NULL;
SELECT "a" + NULL; #任意类型的数据和null做运算结果为null

SELECT "a" + 2; # a无法转换为数值 用0替代
SELECT CONCAT("a",2);
#字符串拼接
/*
字段的别名：
格式 ：
select 字段名1 as 别名1，字段名2 别名2........
别名中有空格 需要单引号或双引号或飘号``
*/
SELECT salary, salary + 1000 , salary
FROM employees;
SELECT salary + 1000, salary +2000
FROM employees;
SELECT salary + 1000 AS new_salary,
	salary + 2 AS "new salary",#别名中最好别出现空格
	salary + 2000 AS 'new salary2',
	salary + 4 AS 薪水,
	salary + 3  `new salary3`#hive 里只能用这个
FROM employees;

注意

/*
SQL语言大小写不敏感
可写在一行或多行
关键字不可简写或分行写
各字句一般分行
使用缩进来提高代码的可读性

*/

between   .... and 包含边界

IN 包含边界

/*
过滤
select 字段名1，字段名2，。。。
from 表名
where 过滤条件
where必须在from 后面
*/
SELECT first_name,department_id
FROM employees
WHERE department_id = 50; # = 非赋值号

SELECT *
FROM employees
WHERE first_name = "Steven";

SELECT * 
FROM employees
#where first_name = 'Steven' && last_name = 'king';
WHERE first_name = 'Steven' AND last_name = 'king';#推荐and

#除90号部门外信息
SELECT * 
FROM employees
#where department_id != 90;
WHERE department_id <> 90;#推荐
#查询奖金率是null的员工信息
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#查询不是null的
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#需求 ：查询薪水5000到8000的员工信息 包含边界
SELECT first_name , salary
FROM employees
#where salary >= 5000 and salary<=8000;
WHERE salary BETWEEN 5000 AND 8000;#包含边界
/*
使用between..and 包含边界
between 后为小的值后面为大的值

*/
#薪水不在5000-8000
SELECT first_name, salary
FROM employees
#where salary< 5000 || salary >8000;
#where salary< 5000  or salary >8000;#推荐
WHERE salary NOT BETWEEN 5000 AND 8000;

#部门号50和80号的员工信息
SELECT first_name, department_id
FROM employees
#where department_id = 90 or department_id= 80;
WHERE department_id IN(80,90);
#除镇两个部门
SELECT first_name, department_id
FROM employees
#where department_id = 90 or department_id= 80;
WHERE department_id NOT IN(80,90);

2.替换

——代表 字符个数  % 代码任意字符   

# 姓名中包含f的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE "%f%"; #%表示任意个数的任意字符
SELECT first_name
FROM employees
WHERE first_name LIKE "%f%";
SELECT first_name
FROM employees
WHERE first_name LIKE "_d%";

#查名字中第二个字符为d
SELECT first_name
FROM employees	
WHERE first_name LIKE "_d%";
#查询名字中第二个字符为_的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE '_\_%';#转义字符表示内容为下划线
SELECT first_name
FROM employees
WHERE first_name LIKE '_\_%';

#替换转义字符 
SELECT first_name
FROM employees
WHERE first_name LIKE '_$_%'ESCAPE'$';# 指定某个字符为转义字符
 SELECT first_name
 FROM employees
 WHERE first_name LIKE '_$_%' ESCAPE'$';
 
SELECT first_name
FROM employees
WHERE first_name LIKE "%a%e%" OR first_name LIKE"%e%a%";
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%e%' OR first_name LIKE "%e%a%";
SELECT first_name
FROM employees
WHERE first_name LIKE "%a%" AND first_name LIKE"%e%";
SELECT first_name
FROM employees
WHERE first_name LIKE "%a%" AND first_name LIKE "%e5";

3.排序

ASC升序 

DESC 降序

#排序
/*
select 字段名1，字段名2....
from 表名
where 过滤条件
order by 字段名1 asc/desc， 字段名2 asc/desc，.....
asc升序 desc降序
*/
#查询姓名和薪水并对薪水进行排序——升序
SELECT first_name, salary
FROM employees
ORDER BY salary ASC;
SELECT first_name, salary
FROM employees
ORDER BY salary ASC;
#查询50号部门所有员工姓名工种薪水 并按薪水降序
SELECT first_name, salary,department_id, job_id
FROM employees
WHERE department_id = 50
ORDER BY salary DESC;#如果没有指定排序方式默认是升序
SELECT first_name, salary, department_id, job_id
FROM employees
WHERE department_id = 50
ORDER BY salary DESC;
#对所有员工的薪水+ 1000 对新的降序
SELECT salary, salary + 1000 AS new_salary
FROM employees
ORDER BY new_salary DESC;
SELECT salary , salary + 1000 AS new_salary
#需求：查询所有员工的姓名 薪水及部门号 。要求降序 部门号同按薪水升序
SELECT first_name,department_id,salary
FROM employees
ORDER BY department_id DESC, salary ASC;

4.去重

distinct

#查询员工所在的部门有哪些
/*
distinct :去重
格式
distinct 字段名1，字段名2，......
*/
SELECT DISTINCT department_id //
FROM employees
WHERE department_id IS NOT NULL;
SELECT DISTINCT department_id , job_id//两个字段一样再去重
FROM employees
WHERE department_id IS NOT NULL;