MySQL学习笔记

阿茄的数据库学习笔记，将持续更新。。。。。。。。
欢迎大家留言交流讨论蛤

MySQL服务的启动和停止

 启动：net start mysql5.5
 停止：net stop mysql5.5

MySQl服务的登入和退出

 登入：mysql -h localhost -P 3306 -u root -p
 退出：exit

SQL的语言分类

DQL（Data Query Language）：数据查询语言
    select 
DML(Data Manipulate Language):数据操作语言
    insert 、update、delete
DDL（Data Define Languge）：数据定义语言
    create、drop、alter
TCL（Transaction Control Language）：事务控制语言
    commit、rollback

MySQL常用命令

 显示所有数据：show databases;
 进入数据库中的某一个库（比如test库）：use test;
 显示所在库的表：show tables;
 显示其他库的表：show tables from test;
 查看自己在哪一个库：select database();
 显示表的具体结构（列）：desc 表名；（不用show）= SHOW COLUMNS FROM 表名;
 查看表的所有数据：select * from 表名；

进阶一：基础查询

select 查询列表（表中字段、常量值、表达式、函数） from 表名；

USE myemployees
SELECT first_name,last_name FROM employees;
SELECT * FROM employees
SELECT 100;
SELECT 100*90;
/*+号的特殊用法*/
SELECT '100'+90;#190
SELECT NULL+90;#NULL
SELECT 'chat'+90;#90
SELECT 'chat'+'char';#0
/*起别名*/
SELECT last_name 姓,first_name AS 名 FROM employees;
SELECT salary "out put" FROM employees;#有空格加引号
/*去重*/
SELECT department_id FROM employees;#有重覆
SELECT DISTINCT department_id FROM employees;
/*拼接*/
SELECT CONCAT(last_name,' ',first_name) AS "姓 名" FROM employees;

进阶二：条件查询

 select 查询列表（表中字段、常量值、表达式、函数） from 表名 where 筛选条件；
 分类：
     一、按条件运算符筛选： >,<,=,!=(<>),<=,>=
     二、按逻辑运算符筛选：&&(and) ||(or) ！(not)
     三、模糊查询：like, between and, in, is null
 模糊查询：
 LIKE：配合通配符使用；
 BETWEEN AND:包含等号；顺序不能颠倒；
 IN:括号里面内容不支持通配符；
 IS NULL:=不能判断NULL；<=>可以判断NULL和其他(=升级版：安全等于)
 IS NOT NULL:判断是否非空；
 IS NULL VS. <=> VS. =(三者比较):
    IS NULL:只能判断是否为空；
    <=>：可以判断是否为空和普通数值；
    =：只能判断普通数值；USE `myemployees`

/*条件表达式*/
SELECT * FROM employees WHERE department_id=90;
SELECT last_name,department_id FROM employees WHERE department_id=90;
/*逻辑表达式*/
#查询部门编号不是90-100之间，或者工资高于30000的员工信息
SELECT * FROM employees WHERE NOT(department_id >=90 AND department_id<=100) OR salary>30000;
/*模糊查询*/
/*LIKE：匹配*/
#查询员工名中包含a字符的员工信息
SELECT last_name FROM employees WHERE last_name LIKE '%a%';#包含a
SELECT last_name FROM employees WHERE last_name LIKE 'a%';#以a开头
SELECT last_name FROM employees WHERE last_name LIKE '%a';#以a结尾
SELECT last_name FROM employees WHERE last_name LIKE '_a_a%';
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';#转义
SELECT last_name FROM employees WHERE last_name LIKE '_&_%' ESCAPE '&';#自定义转义
/*BETWEEN AND*/
SELECT * FROM employees WHERE salary>=10000 AND salary<=30000;
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 30000;#10000~30000
/*IN:属于*/
#查询工种编号为‘SH_CLERK’或‘AD_VP’的员工信息
SELECT * FROM employees WHERE job_id = 'SH_CLERK' OR job_id='AD_VP';
SELECT * FROM employees WHERE job_id IN('SH_CLERK','AD_VP');
/*IS NULL*/
#查询没有奖金的员工姓名和工资
#SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct= null;#错误写法
SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct <=> NULL;
SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct IS NULL;
/*IS NOT NULL*/
#查询有奖金的员工姓名和工资
SELECT CONCAT(last_name,' ',first_name) AS '姓 名', salary FROM employees WHERE commission_pct IS NOT NULL;
#查询员工号为176的员工的姓名，部门编号和年薪
SELECT 
      CONCAT(last_name,' ',first_name) AS '姓 名',
      department_id,
      salary*12*(1+IFNULL(commission_pct,0)) AS '年 薪'
FROM  
      employees
WHERE 
      employee_id = 176;

面试案例：
SELECT * FROM employees;#（1）
SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';#like不能匹配null，所以结果和（1)不一样
SELECT * FROM employees WHERE commission_pct LIKE '%%' OR last_name LIKE '%%';#last_name没有null,所以结果和(1)一样

进阶三：排序查询

 select   查询列表
 from     表
 where    条件
 order by 排序的字段|表达式|函数|别名 (asc/desc)(不写默认升序)；

#查询部门编号>=90的员工信息，并按照薪水降序排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY salary DESC;     
#先按工资降序排，再按员工编号降序排：支持多字段排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY salary DESC, employee_id DESC;
     

进阶四：常用函数

单行函数

字符函数

• length：获取字节个数（汉字占用三个字节）（只有length以字节为尺度）
• concat: 拼接字符串
• substr：截取子串

#与java中不同，需要留意
SELECT SUBSTR('fatcat',2) AS out_put;#actcat:索引从1开始而不是0
SELECT SUBSTR('fatcat',1,4) AS out_put;#fatc:第二位表示截取长度而不是结束索引

• instr：返回子串第一次出现的索引，找不到返回0

SELECT INSTR('fatcat','cat') AS out_put;#4

• upper：转换成大写
• lower：转换成小写
• trim：去前后指定的空格和字符

#只去前后（不去中间）指定字符，不指定指的空格
SELECT TRIM('k' FROM 'kkkkfatkkkcatkkkk') AS out_put;#fatkkkcat

• ltrim：去左边空格（只能去空格不能指定）
• rtrim：去右边空格（只能去空格不能指定）
• replace：替换

#全部替换
SELECT REPLACE('catfatcatcat','cat','dog') AS out_put;#dogfatdogdog

• lpad：左填充
• rpad：右填充

#中间是最终总长度而不是需要填充的长度
#无论左填充还是右填充，长度不足是均是从左到右截取
SELECT LPAD('fatcat',8,'k') AS out_put;#kkfatcat
SELECT LPAD('fatcat',3,'k') AS out_put;#fat
SELECT RPAD('fatcat',8,'k') AS out_put;#fatcatkk
SELECT RPAD('fatcat',3,'k') AS out_put;#fat

数学函数

• round：四舍五入

SELECT ROUND(-1.54) AS out_put;#-2
SELECT ROUND(-1.547,2) AS out_put;#-1.55:表示小数点后保留两位

• rand：随机数([0,1)

#用rand产生[i,j]的随机数:floor(i+rand()*(j-i+1))

• floor：向下取整
• ceil：向上取整

SELECT CEIL(-1.1) AS out_put;#-1
SELECT FLOOR(-9.9) AS out_put;#-10

• mod：取余

#符号和被除数一致（a-a/b*b）
SELECT MOD(-10,3) AS out_put;#-1
SELECT MOD(10,-3) AS out_put;#1

• truncate：截断

SELECT TRUNCATE(-9.987,2) AS out_put;#-9.98:小数点后保留两位，直接截断，不用四舍五入

日期函数

• now：当前系统日期+时间
• curdate：当前系统日期
• curtime：当前系统时间
• year: 获取年（同理：month,day,hour,minute,second）
• str_to_date：将字符转换成日期
• date_format：将日期转换成字符
• datediff:求两个日期的天数差

流程控制函数（挺有意思的）

• if：类似三元运算符

if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
SELECT IF(10>5,'large','small');#large

• case：

case 情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
#根据部门编号就算加权工资
SELECT salary,department_id,
CASE department_id
WHEN 50 THEN salary*2
WHEN 80 THEN salary*3
WHEN 100 THEN salary*4
ELSE salary
END AS 'wighted salary'
FROM employees
ORDER BY department_id

case 情况2
case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
#根据工资显示级别
SELECT last_name,salary,
CASE 
WHEN salary>=20000 THEN 'A'
WHEN salary>=10000 THEN 'B'
WHEN salary>=5000 THEN 'C'
ELSE 'D'
END AS '级别'
FROM employees
ORDER BY 级别 

其他函数

• version(): 版本
• database(): 当前库
• user(): 当前连接用户

分组函数（统计函数）

• max：最大值
• min：最小值
• sum：和
• avg：平均值
• count：计算个数

特点：
1、以上五个分组函数都忽略null值，除了count(*)
2、sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果:select sum(distinct salary) from employees;
4、count的参数可以支持：字段、*、常量值，一般放1，建议使用 count(*)
5、count(1)和count(*):统计结果集的行数
6、效率上：MyISAM存储引擎，count(*)最高；InnoDB存储引擎，count(*)和count(1)效率>count(字段)
7、和分组函数一同查询的字段，要求是group by后出现的字段

进阶五：分组查询

语法：
select 分组函数/group by后面的字段
from 表
(where 分组前筛选条件)
group by 分组的字段(即，按该字段分组)
(having 分组后筛选条件)
(order by 子句)

特点：
    1、可以按单个字段分组
    2、和分组函数一同查询的字段最好是分组后的字段
    3、分组筛选
       分组前筛选：原始表，group by的前面，关键字where
       分组后筛选：分组后的结果集，group by的后面，关键字having
    4、可以按多个字段分组，字段之间用逗号隔开
    5、可以支持排序
    6、having后可以支持别名

/*单字段分组查询*/
#下述两种写法效果一样（都是按照部门分组输出，不重复）
SELECT department_id
FROM employees
GROUP BY department_id;

SELECT DISTINCT department_id
FROM employees;

#查询每个领导手下有奖金的员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#查询没有奖金的员工所属的部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY department_id;

#(上面案例相当于先筛选后分组，下面这个案例需要先分组计算出相应结果后再筛选)
#查询哪个部门的员工数大于7
#首先得先查出各个部门的员工数，然后判断是否大于7
#引入新关键字having(用于分组后的筛选)
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>7;

#查询公种编号和员工最高工资（要求该工种下有奖金的员工的工资最高工资高于12000）
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary)>12000;

#查询领导ID（要求ID>102且手下员工的最低工资高于5000）
SELECT manager_id
FROM employees
WHERE manager_id>102 
GROUP BY manager_id
HAVING MIN(salary)>5000;

/*多字段分组查询*/
#查询每个部门每个工种的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

进阶六：多表连接查询

笛卡尔乘积现象：如果连接条件省略或无效则会出现全部匹配的情况（表1为m行，表2为n行，输出结果为m*n行）。
解决办法：添加上连接条件。

分类：
    按标准分类：
             sql92（在mysql中仅支持内连接）
             sql99（在mysql中不支持全外连接）
    按功能分类：
             内连接：等值连接、非等值连接、自连接
             外连接：左外连接、右外连接、全外连接
             交叉连接

思想：多张表的匹配，没有加连接条件情况下就是每一行匹配，加上连接条件相当与加了约束后匹配。

内连接

（表的交集）

sql92标准等值连接：

#查询员工名和对应的部门名
SELECT CONCAT(last_name,' ',first_name) fullname, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

#查询员工名及其对应的工种号和工种名(为表起别名)
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id = j.job_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
AND city LIKE 's%'
ORDER BY LENGTH(city);

sql92标准非等值连接：

#查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;

sql92标准自连接：

#利用给同一张表其不同的别名实现区分
#查询员工名和其领导的名字
SELECT e.employee_id,e.last_name, m.employee_id 对应领导ID, m.last_name 对应领导名
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

sql99语法结构：

select 查询列表
from 表1 别名
【inner|left outer|right outer|cross】join 表2 别名 on  连接条件
【inner|left outer|right outer|cross】join 表3 别名 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】
好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

sql99标准等值连接：

#查询部门个数大于3的城市名和相应的部门个数
#sql92
SELECT city,COUNT(*)
FROM locations l,departments d
WHERE l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*)>3;

#sql99
SELECT city,COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*)>3;

#查询员工名、部门名、工种名、并按部门名降序排序（三表连接）
#sql92
SELECT last_name,department_name,job_title
FROM employees e,departments d,jobs j
WHERE e.department_id = d.department_id
AND j.job_id = e.job_id
ORDER BY department_name DESC;

#sql99
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON j.job_id = e.job_id
ORDER BY department_name DESC;

sql99标准非等值连接：

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

sql99标准自连接：

#利用给同一张表其不同的别名实现区分
#查询员工名和其领导的名字
SELECT e.employee_id,e.last_name, m.employee_id 对应领导ID, m.last_name 对应领导名
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

外连接

（查询一张表中有，另一张表中没有的记录）

特点：
    1、外连接会显示主表的所有记录
       如果从表中有和它匹配的，则显示匹配的值；
       如果从表中没有和它匹配的，则显示null；
       外连接查询=内连接结果+主表中有但从表中没有的记录
    2、左外连接：left join左边是主表
       右外连接：right join右边是主表
    3、左外和右外交换两个表顺序，结果一致；

sql99左外和右外连接：（查询主表中有，从表中没有的记录）

#查询那个部门没有员工
SELECT department_name,d.department_id,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

SELECT department_name,d.department_id,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

sql99全外连接：（内连接+表一中有表二没有记录+表二中有表一没有记录）

mysql不支持。

交叉连接

害，就是笛卡尔乘积的叫法糖而已，用cross join表征。

进阶七：子查询（内查询）

含义：
    一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询；
    在外面的查询语句，称为主查询或外查询；

分类：
    1、按出现位置
        select后面：
                  仅仅支持标量子查询
        from后面：
                  表子查询
        where/having后面：（重点）
                  标量子查询
                  列子查询
                  行子查询
        exists后面：
                  标量子查询
                  列子查询
                  行子查询
                  表子查询

    2、按结果集的行列
        标量子查询（单行子查询）：结果集为一行一列
        列子查询（多行子查询）：结果集为多行一列
        行子查询：结果集一般为一行多列（也可以多行多列）
        表子查询：结果集一般为多行多列

特点：
    1、子查询都放在小括号内
    2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
    3、子查询优先于主查询执行，主查询使用了子查询的执行结果
    4、子查询根据查询结果的行数不同分为以下两类：
    ① 单行子查询
        结果集只有一行
        一般搭配单行操作符使用：> < = <> >= <= 
        非法使用子查询的情况：
        a、子查询的结果为一组值
        b、子查询的结果为空
    ② 多行子查询
        结果集有多行
        一般搭配多行操作符使用：any、all、in、not in
        in： 属于子查询结果中的任意一个就行
        any和all往往可以用其他查询代替

where/having后面：

#查询谁的工资比高 Greenberg高
#首先查出Greenberg的工资
#再查比他高的员工信息
SELECT *
FROM employees
WHERE salary>(
    SELECT salary 
    FROM employees  
    WHERE last_name = 'Greenberg'
);

#返回job_id和141号员工相同，salary比143号员工多的员工信息
SELECT * 
FROM employees
WHERE job_id=(
    SELECT job_id
    FROM employees
    WHERE employee_id = 141 
) 
AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id = 143
);

#返回公司中工资最少的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

#查询最低工资大于50号部门最低工资的部门id及其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);

select后面：

#查询每个部门的员工个数
SELECT department_id,(
    SELECT COUNT(*) 
    FROM employees e
    WHERE e.department_id = d.department_id
) AS 个数
FROM departments d

SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id;

from后面：

#查询每个部门的平均工资的工资级别
SELECT ag_dep.*, jg.grade_level
FROM job_grades jg
INNER JOIN(
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
)ag_dep
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal
ORDER BY grade_level;

exit后面：(相关子查询)

#查询有员工名和部门名
SELECT department_name
FROM departments d
WHERE EXISTS(#（是否有值的意思）
    SELECT * 
    FROM employees e
    WHERE d.department_id = e.department_id
);

练习：

#查询各个部门中比部门平均工资高的员工的信息
SELECT *
FROM employees e
INNER JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) AS avg_dep
ON e.department_id = avg_dep.department_id
WHERE salary>ag;

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id

#查询和姓名中包含u的员工在同一个部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);

#查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id 
    FROM departments
    WHERE location_id = 1700
);

#查询领导为King的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
    SELECT employee_id 
    FROM employees 
    WHERE last_name = 'K_ing'
);

#查询工资最高的员工的姓名，要求姓名整合成一列
SELECT CONCAT(last_name,' ',first_name) 姓名,salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees 
);


#查询和K_ing 相同部门的员工姓和工资
SELECT last_name,salary
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM employees
    WHERE last_name = 'K_ing'
);

#查询工资比公司平均工资高的员工的员工号和姓名
SELECT employee_id, last_name
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);

Quiet and Quick! Salute !