java学习笔记——mysql的安装与卸载、基本的 SELECT 语句、多表连接查询

mysql的安装与卸载、基本的 SELECT 语句、多表连接查询

mysql的安装与卸载

图中分别是windows系统下mysql 的64位与32位版本，以及客户端软件，我的系统是64位，所以安装啦64的。

卸载

找到mysql的安装路径并删除如图：

然后打开mysql的安装程序 进行卸载（mysql的安装与卸载都是同一个exe）

1.使用360卸载，并强力删除相关东东

2.清理注册表:

A.HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录

B.HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录

C.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目录

D.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl001\Services\MYSQL 目录

E.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl002\Services\MYSQL　目录

F.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MYSQL　目录

3.删除C:\Documents and Settings\All Users\Application Data\MySQL 目录 （隐藏的目录）

4.删除安装路径文件，重新安装新版MYSQL

基本SELECT语句

#1. 基本的 SELECT 语句

SELECT *
FROM employees;

SELECT employee_id, last_name, salary
FROM employees;

SELECT *
FROM departments;

SELECT department_id, department_name
FROM departments;

#列的别名 : 在 SQL 语句中只有给列起别名是才使用双引号
#空值问题 : 空值参与运算结果还是空值
SELECT employee_id id, last_name AS NAME, salary, commission_pct, salary * 12 * (1 + commission_pct) "annual_sal"
FROM employees;

SELECT last_name AS NAME, salary, commission_pct, salary * 12 * (1 + IFNULL(commission_pct, 0)) "annual_sal"
FROM employees;

#显示表结构
DESCRIBE employees;
DESC departments;

#2.过滤


-- where 子句紧跟 from 子句
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 10000;

SELECT employee_id, last_name, salary
FROM employees
-- WHERE department_id != 90;
WHERE department_id <> 90;

-- 其他比较运算

-- BETWEEN ... AND ... (包含边界)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 6000 AND salary <= 8000;

SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

-- IN(..., ..., ...)
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = 60 OR department_id = 80 OR department_id = 90;

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id IN(60, 80, 90);

-- LIKE 模糊查询
-- % : 表示零个或多个的任意字符
-- 注意：在 SQL 语句中对于日期和字符类型必须使用单引号
SELECT employee_id, last_name, salary
FROM employees
WHERE last_name LIKE '%a%';

-- _ : 表示一个任意字符
SELECT employee_id, last_name, salary
FROM employees
WHERE last_name LIKE '_a%';


/*update employees
set last_name = 'De_Haan'
where employee_id = 102;*/

SELECT employee_id, last_name, salary
FROM employees
WHERE last_name LIKE '%/_%' ESCAPE '/';

-- 查询名字中包含 a 和 e 的员工信息
SELECT employee_id, last_name, salary
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

SELECT employee_id, last_name, salary
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

#IS (NOT) NULL 空(非空)值
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# 3. ORDER BY 排序
# ORDER BY 子句需要写在 SELECT 语句的末尾（limit）
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 10000
-- order by salary ASC; -- 升序（默认值）
ORDER BY salary DESC;

-- 按别名排序
SELECT employee_id, last_name, salary, salary * 12 * (1 + commission_pct) "annual_sal"
FROM employees
ORDER BY annual_sal DESC;

-- 多列排序
SELECT employee_id, last_name, salary
FROM employees

ORDER BY salary DESC, employee_id, last_name DESC;

SELECT employee_id , last_name,
salary * 12  "ANNUAL  SALARY"
FROM employees;

DESC departments;

SELECT *
FROM departments;

-- 去重
SELECT DISTINCT job_id
FROM employees;

SELECT employee_id || ',' || last_name || ',' || salary
FROM employees;

SELECT CONCAT(employee_id, ',', last_name, ',', salary) "OUT_PUT"
FROM employees;

#1.查询工资大于12000的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary > 12000;

#2.查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;

#3.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

#4.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE department_id IN (20, 50);

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;

#5.选择公司中没有管理者的员工姓名及job_id
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

#6.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#7.选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#8.选择姓名中有字母a和e的员工姓名
SELECT last_name
FROM employees

WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

特别重要 ： # 多表连接查询
/*
按查询情况不同
①等值连接 --- 非等值连接
②内连接 --- 外连接
③自连接 --- 非自连接
*/

-- 问题：当完成多表连接查询时，容易引发迪卡尔集的情况。（因为没有提供表之间的连接条件）
SELECT employee_id, department_name
FROM employees, departments;

SELECT *
FROM employees; -- 107

SELECT *
FROM departments; -- 27

SELECT 107 * 27; -- 2889

#等值连接
-- 解决：提供表之间的连接条件
SELECT employee_id, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`; -- 106

-- 若完成 n 张表之间的连接查询的话，至少需要 n-1 个连接条件
SELECT employee_id, department_name, city
FROM employees, departments, locations
WHERE employees.`department_id` = departments.`department_id`
AND departments.`location_id` = locations.`location_id`;

SELECT employee_id, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`
AND employees.`manager_id` = departments.`manager_id`;

-- 表的别名
SELECT e.employee_id, d.department_name, e.department_id
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);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);*/

DESC job_grades;

SELECT *
FROM job_grades;

SELECT employee_id, last_name, salary, grade_level
FROM employees e, job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

-- SQL99 语法
-- join ... on ...
SELECT employee_id, department_name
FROM employees e
INNER JOIN departments d  -- inner 可以省略不写
ON e.`department_id` = d.`department_id`;

SELECT employee_id, department_name, city
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

-- 外连接：不仅将多个表中满足条件的数据查出，还将多个表中不满足条件的数据也查询出来
-- 左外连接：不仅将多个表中满足条件的数据查出，还将 左 表中不满足条件的数据也查询出来
SELECT employee_id, department_name
FROM employees e
LEFT OUTER JOIN departments d  -- outer 可以省略不写
ON e.`department_id` = d.`department_id` ;


-- 右外连接：不仅将多个表中满足条件的数据查出，还将 右 表中不满足条件的数据也查询出来
SELECT employee_id, department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

-- 满外连接：不仅将多个表中满足条件的数据查出，还将 左右 表中不满足条件的数据也查询出来
-- full join ... on ...  Oracle 支持，MySQL 不支持

-- 自连接
-- xxx works for xxx
SELECT CONCAT(emp.last_name, ' works for ', mgr.last_name)
FROM employees emp, employees mgr

WHERE emp.`manager_id` =  mgr.`employee_id`;

-----------------------------------------------------------------------------------------------------------------------------

练习：

#1.显示所有员工的姓名，部门号和部门名称。
SELECT last_name, e.department_id, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`;

SELECT last_name, e.department_id, department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`;

#2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id, location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;

SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

#3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name, department_name , l.location_id , city
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE e.`commission_pct` IS NOT NULL;

SELECT last_name, department_name , l.location_id , city
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND e.`commission_pct` IS NOT NULL;

#4.选择city在Toronto工作的员工的
#last_name , job_id , department_id , department_name
SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`city` = 'Toronto';

SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';

/*5.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees    Emp#    manager     Mgr#
kochhar            101    king     100
*/
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name "manager", mgr.employee_id "Mgr#"
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;