MySQL基础
**
第一部分:基本SELECT语句**
1.列的别名
(1) 重命名一个列,便于计算
(2) 紧跟列名,也可以在列名和别名之间加入关键字AS,别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
(3) AS 可以省略
#方式一:
SELECT last_name AS name, commission_pct comm
FROM employees;
#方式二:
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;
2.去除重复行(DISTINCT)
#在SELECT语句中使用关键字DISTINCT去除重复行,DISTINCT 需要放到所有列名的前面,
SELECT DISTINCT department_id
FROM employees;
3.显示表结构
DESC employees;
4.过滤数据( WHERE)
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
5.运算符
5.1 空运算符 (IS NULL或者ISNULL)判断一个值是否为NULL
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
5.2 非空运算符 非空运算符(IS NOT NULL)判断一个值是否不为NULL
SELECT employee_id,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
5.3 IN运算符 IN运算符用于判断给定的值是否是IN列表中的一个值
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
5.4LIKE运算符 LIKE运算符主要用来匹配字符串,通常用于模糊匹配
(1)“%”:匹配0个或多个字符。
(2)“_”:只能匹配一个字符。
#查询名字中第二个字母是o的人
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
部分习题:
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
#WHERE salary<50000 OR salary>12000
WHERE salary NOT BETWEEN 5000 AND 12000;
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,employee_id
FROM employees
#WHERE department_id=20 OR department_id=50;
WHERE department_id IN (20,50);
# 3.选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id,manager_id
FROM employees
#WHERE manager_id IS NULL;
WHERE manager_id <=> NULL;
# 4.选择公司中有奖金的员工姓名,工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
#WHERE commission_pct IS NOT NULL;
WHERE NOT commission_pct <=> NULL;
# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE "__a%";
# 6.选择姓名中有字母a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE "a%k%" OR last_name LIKE "k%a%";
# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE "%e";
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,first_name,department_id
FROM employees
#WHERE department_id IN (80,90,100);
WHERE department_id BETWEEN 80 AND 100;
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110);
第二部分:排序与分页
一、排序
- 排序规则
使用 ORDER BY 子句排序
ASC(ascend): 升序
DESC(descend):降序
ORDER BY 子句在SELECT语句的结尾。 - 单列排序
#升序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ASC ;( ASC可省略)
#降序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
3.多列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
二、分页
MySQL中使用 LIMIT 实现分页
格式 :LIMIT [位置偏移量,] 行数
注意:LIMIT 子句必须放在整个SELECT语句的最后!
--前10条记录:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
第三部分:多表查询
首先来个列子
#案例:查询员工的姓名及其部门名称
SELECT e.last_name, e.department_name
FROM employees e , departments d
WHERE e.department_id = d.department_id;
1.两表连接
#练习:查询出公司员工最高工资和最低工资的姓名,工资和等级
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal
AND j.highest_sal;
2.题目:查询employees表,返回“Xxx works for Xxx”
SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
- SQL99语法实现多表查询
`3.1 基本语法
使用JOIN…ON子句创建连接的语法结构:
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
3.2 内连接(INNER JOIN)的实现
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
3.3 外连接(OUTER JOIN)的实现
3.3.1 左外连接(LEFT OUTER JOIN)
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
3.3.2 右外连接(RIGHT OUTER JOIN)
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
4.7种SQL JOINS的实现
*
第四部分:子查询*
4.1 HAVING 中的子查询
首先执行子查询。
向主查询中的HAVING 子句返回结果。
#题目:查询最低工资大于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
);
4.2 CASE中的子查询
在CASE表达式中使用单列子查询:
#题目:显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800 的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name, (
CASE department_id
WHEN
(SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
4.3多行子查询
多行子查询操作符 IN ANY ALL SOME
4.3.1 IN
#查询与141号员工或174号的manager_id和department_id相同的其他员工的employee_id,manager_id和department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id IN (
SELECT manager_id
FROM employees
WHERE employee_id IN (141,174)
)
AND department_id in (
SELECT department_id
FROM employees
WHERE employee_id IN (141,174)
);
4.3.2 ANY/ALL
#ANY/ALL
#题目:返回其他job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、
#姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (SELECT salary
FROM employees
WHERE job_id='IT_PROG'
);
第五部分:创建和管理
5.1创建表
如果要创建的数据库已经存在,则创建不成功,但不会报错。
CREATE DATABASE IF NOT EXISTS mytest CHARACTER SET 'utf8';
5.2 管理数据库
#查看当前连接中的数据库都有哪些
SHOW DATABASES;
#切换数据库
USE mytest;
#查看当前数据库中保存的数据表
SHOW TABLES;
#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;
#查看指定数据库下保存的数据库
SHOW TABLES FROM mysql;
#修改数据库
#更改数据库字符集
SHOW CREATE DATABASE mytest;
ALTER DATABASE mytest CHARACTER SET 'utf8';
5.3创建数据表
CREATE TABLE IF NOT EXISTS myemp1(#需要用户具备创建表的权限
id INT,
emp_name VARCHAR(15), #使用VARCHAR来定义字符串,必须在使用VARCHAR时指明其长度。
hire_date DATE
);
#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1;#如果创建表时没有指明使用的字符集,则默认使用表所在的数据库的字符集。
5.4复制原有的表‘’
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
第六部分:增删改
6.1增加数据
#指明要添加的字段(推荐)
INSERT INTO emp1(id,hire_date,salary,name)
VALUES(002,'2022-7-27',25000,'zs');
INSERT INTO emp1(id,salary,name)
VALUES(003,25000,'zs');
#同时插入多条记录(推荐)
INSERT INTO emp1(id,NAME,salary)
VALUES
(004,'Jim',5000),
(005,'死',5000);
#复制表数据
#方式二:将查询结果插入到表中
SELECT *
FROM emp1;
INSERT INTO emp1(id,NAME,salary,hire_date)
6.2更新数据(或修改数据)
/*
UPDATE...SET...WHERE
可以实现批量修改数据的。
*/
UPDATE emp1
SET hire_date=CURDATE()
WHERE id=4;
SELECT * FROM emp1;
#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date=CURDATE(),salary=6000
WHERE id=4;
#表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary =salary*1.2
WHERE NAME LIKE '%c%';
6.3删除数据 DELETE…FROM…WHERE…
DELETE FROM emp1
WHERE id=2;
第七部分:约束
7.1添加非空约束
#建表前
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
#建表后
alter table 表名称 modify 字段名 数据类型 not null;
7.2删除非空约束
alter table 表名称 modify 字段名 数据类型;#
7.3添加唯一约束
#建表前
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);
eg:
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD) );
#表示用户名和密码组合不能重复
建表后
alter table 表名称 modify 字段名 字段类型 unique;
7.4添加主键约束
列级约束
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
表级约束
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);