mysql笔记2

#进阶1：基础查询
/*
语法：
select 查询列表 from 表明
类似于java system.out.println(打印东西)
特点：
1、查询表中的字段、常量值、表达式、函数
2、查询结果可以是一个虚拟的表格
*/
USE myemployees;
#查询表中单个字段
SELECT last_name FROM employees;
#2、查询表中的多个字段
SELECT `last_name`,`email` FROM employees;
SELECT `employee_id`,`first_name`,`job_id` FROM employees;
SELECT last_name FROM employees;
SELECT last_name FROM employees;
#方式二
SELECT * FROM employees;
#这里之前不知道为什么employees打错了是什么原因导致无法执行
#查询常量值
SELECT 100;
SELECT 100/98;
#6\查询函数
SELECT VERSION();
#7起别名使用as 或者空格别名有特殊含义加双引号或者单引号
SELECT 100 AS 2;
#8去重 DISTINCT
SELECT DISTINCT `department_id` FROM employees;
#9员工名和行连接在一起
SELECT CONCAT(`email`,`first_name`,`salary`) AS d FROM employees;

#进阶2条件查询
/*      select 查询列表
    from   表明
    where  筛选条件
    分类：
    条件运算符：>  < <>
    逻辑运算符：&& || ！and or not
    模糊查询 like between and in is null
    
*/

    
SELECT  
*
FROM
employees
WHERE
salary<>200;
#逻辑表达式
SELECT
`last_name`,
`salary`,
`commission_pct`
FROM
employees
WHERE
salary>=10000 AND salary<=20000;
#模糊查询
#1、like
#案例1
SELECT
    *
FROM
    employees
WHERE
    `last_name` LIKE 'aa';
    
    
SELECT
    `last_name`,
    `salary`
FROM
    employees
WHERE
    `last_name` LIKE 'j_';
    
#in  33
SELECT
`last_name`,`job_id`
FROM
employees
WHERE
job_id IN('IT_PORT','AD_%');

#IS NULL is not null
#34
SELECT
`last_name`,`commission_pct`
FROM employees
WHERE
commission_pct IS NULL;
#安全等于<=>
SELECT
`last_name`,`commission_pct`,`salary`
FROM employees
WHERE
salary <=> 12000;

#39
#3进阶3排序查询
#order bu 排序列表 ASC升序不写默认升序
SELECT * FROM employees ORDER BY salary DESC;

SELECT
`last_name`,`commission_pct`,`salary`
FROM employees
WHERE
salary < 12000;
ORDER BY salary DESC;

SELECT *,SALARY*12 年薪
FROM employees
ORDER BY 年薪 DESC;

#order by 按多个字段排序
#not in between an exist

#45
#length
SELECT LENGTH('john');
SELECT LENGTH('张三丰john')

SELECT ROUND(-1.55);

SELECT CEIL(-1.02);

SELECT TRUNCATE(-1.022,1);

SELECT MOD(10,-3);

SELECT  NOW;

SELECT CURTIME();

#五、流程控制函数

SELECT IF(10>5,'1','XIOA');

#case when then

SELECT salary,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
ELSE salary
END AS
FROM employees;

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;