数据库的概念
DB
数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS
数据库管理系统（Database Management System）
数据库是通过DBMS创建和操作的容器，常见的数据库管理系统：MySQL、Oracle、DB2、SqlServer等
SQL
结构化查询语言（Structure Query Language）：用来与数据库通信的语言
数据库的特点
将数据放到表中，表再放到库中
一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
表中的数据是按行存储的，每一行类似于java中的“对象”。
SQL语言分类
1、DML（Data Manipulation Language):数据操纵语句，用于添加、删除、修改、数据库记录，并检查数据完整性
2、DDL（Data Definition Language):数据定义语句，用于库和表的创建、修改、删除。
3、DCL（Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。
DML
DML用于查询与修改数据记录，包括如下SQL语句：
INSERT：添加数据到数据库中
UPDATE：修改数据库中的数据
DELETE：删除数据库中的数据
SELECT：选择（查询）数据
SELECT是SQL语言的基础，最为重要
DDL
DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：
CREATE TABLE：创建数据库表
ALTER TABLE：更改表结构、添加、删除、修改列长度
DROP TABLE：删除表
CREATE INDEX：在表上建立索引
DROP INDEX：删除索引
DCL
DCL用来控制数据库的访问，包括如下SQL语句：
GRANT：授予访问权限
REVOKE：撤销访问权限
COMMIT：提交事务处理
ROLLBACK：事务处理回退
SAVEPOINT：设置保存点
LOCK：对数据库的特定部分进行锁定
MySql
MySQL产品的特点
MySQL数据库隶属于MySQL AB公司，总部位于瑞典，后被oracle收购
– 性能高：执行很快
– 简单：很容易安装和使用
MySQL的版本
Windows平台下下载：http://dev.mysql.com/downloads/mysql
MySql数据库的安装
详见《MySQL安装.docx》
配置文件常用配置分析
安装目录下的my.ini文件
[mysqld] 服务端配置
port=3306 端口3306
base dir 安装目录
data dir 数据文件目录
character-set-server 服务端字符编码
default-storage-engine 默认引擎
sql-mode 语法模式
max_connections 最大连接数
用户名 : root
注释
单行注释 # 或 --
多行注释 /* 注释文本 */
示例
- 进入 mysql, 在命令行中输入: mysql –u root –p
- 查看 mysql 中有哪些个数据库: show databases;
- 使用一个数据库: use 数据库名称;
- 新建一个数据库: create database 数据库名
- 查看指定的数据库中有哪些数据表: show tables;
- 查看mysql版本
- 登录后select version();或退出重新登录
- 未登录 mysql --version或mysql -V
SQL语句
启动和关闭MySQL服务
#启动
net start mysql
#关闭
net stop mysql
#mysql -h IP地址(本地可省略) -u root -proot
mysql -uroot -proot
mysql -uroot -p退出
exit
quit显示所有数据库
show databases;创建数据库
#create batabase 数据库名字;
create database mysql1;
#create databases if not exists 数据库名字;
create database if not exists mysql1;选择数据库
#use 数据库名字
use mysql1;删除数据库
#drop
drop database if exists `mysql1`;创建表
create table if not exists `grade`(
`id` int not null comment `年级编号`,
`gradeName` varchar(20) not null comment `年级名称`
)comment `年级表`选中数据库中的所有表
show tables;修改表名
alter table gra rename to gral;基本SQL语句 : 查询
• SELECT 标识选择哪些列。
• FROM 标识从哪个表中选择。
注意
• SQL 语言大小写不敏感。
• SQL 可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写。
• 使用缩进提高语句的可读性
列的别名
• 重命名一个列。
• 便于计算。
• 紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
SELECT last_name AS name, commission_pct
FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;字符串
• 字符串可以是 SELECT 列表中的一个字符,数字,日期。
• 日期和字符只能在单引号中出现。
显示表结构
使用 DESCRIBE 命令，表示表结构
DESC[RIBE] tablename
DESCRIBE employees
#select *
from `表名`
select studentName
from student
select phone
from student
#查询员工编号与员工名称
SELECT DISTINCT `employee_id`,CONCAT(`first_name`,`last_name`) AS 姓名
FROM `employees`过滤和排序数据
where
WHERE 子句紧随 FROM 子句
#查询90号部门工作的所有员工的信息
SELECT *
FROM employees
WHERE department_id=90
#查询最低工资是4000的职位
SELECT job_id,job_title
FROM jobs
WHERE min_salary=4000
#查询部门编号100以上的部门
SELECT manager_id,department_name
FROM departments
WHERE department_id>100
#查询工资低于5000的员工信息
SELECT DISTINCT `employee_id`,manager_id,department_id,hiredate,email,phone_number,job_id,commission_pct,CONCAT(`first_name`,`last_name`) AS 姓名
FROM employees
WHERE salary<5000
#查询最高工资超过10000的岗位
SELECT job_id,job_title
FROM jobs
WHERE max_salary>10000
#查询国家编码不是IT的地址信息
SELECT location_id,street_address,postal_code,city,state_province
FROM locations
WHERE country_id<>'IT'比较运算
赋值使用 := 符号
不等于 : <>或!=
BETWEEN
使用 BETWEEN 运算来显示在一个区间内的值
#查询工资在2500到3500之间的员工
SELECT *
FROM employees
WHERE salary BETWEEN 2500 AND 3500
#查询部门编号是30到80的部门
SELECT *
FROM departments
WHERE department_id BETWEEN 30 AND 80
#查询入职日期是2000年的员工
SELECT DISTINCT `employee_id`,CONCAT(`first_name`,`last_name`) AS 姓名
FROM employees
WHERE hiredate BETWEEN '2000-1-1' AND '2020-12-31'
#查询最低工资1000到5000的职位
SELECT job_id,job_title
FROM jobs
WHERE min_salary BETWEEN 1000 AND 5000IN
使用 IN运算显示列表中的值。
#查询经理编号是100 , 101 , 201的员工编号,姓名,工资,经理id
SELECT employee_id,last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,201)
#查询地址id是1700,1800,2100,2200的部门
SELECT department_id,department_name
FROM departments
WHERE location_id IN (1700,1800,2100,2200)
#查询job_id是AD_VP HR_REP PR_REP的职位信息
SELECT *
FROM jobs
WHERE job_id IN ('AD_VP','HR_REP','PR_REP')
#查询 城市是Roma Tokyo Toronto Sydney的地址
SELECT street_address,country_id,state_province
FROM locations
WHERE city IN ('Roma','Tokyo','Toronto','Sydney')LIKE
•使用 LIKE 运算选择类似的值
•选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
‘%’和‘-’可以同时使用
#查询姓中有S的员工名字
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
#查询姓李的学生信息
select *
from student
where studentName like '李%';
#查询姓中s开头的员工信息
SELECT *
FROM employees<br>
WHERE first_name LIKE '%S%'
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';IS NULL
使用 IS (NOT) NULL 判断空值
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;AND
AND 要求并的关系为真
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';OR
OR 要求或关系为真
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';NOT
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');ORDER BY子句
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ; 降序排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ; 按别名排序
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal; 多个列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC; 按照ORDER BY 列表的顺序排序
可以使用不在SELECT 列表中的列排序
分组函数
什么是分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
组函数类型
• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()
组函数语法
SELECT [column,] group_function(column), ...
FROM table[WHERE condition][GROUP BY column][ORDER BY column];AVG（平均值）和 SUM （合计）函数
可以对数值型数据使用AVG 和 SUM 函数。
#REP员工工资平均值,最大值,最小值,总和
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';MIN（最小值）和 MAX（最大值）函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;COUNT（计数）函数
COUNT(*) 返回表中记录总数,适用于任意数据类型。
SELECT COUNT(*)
FROMemployees
WHERE department_id = 50; COUNT(expr) 返回expr不为空的记录总数
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;分组数据
分组数据: GROUP BY 子句语法
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition][GROUP BY group_by_expression][ORDER BY column];GROUP BY 子句
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;使用多个列分组
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;非法使用组函数
• 不能在 WHERE 子句中使用组函数。
• 可以在 HAVING 子句中使用组函数
WHERE 子句中不能使用组函数
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;过滤分组
使用 HAVING 过滤分组:
- 行已经被分组。
- 使用了组函数。
- 满足HAVING 子句中条件的分组将被显示。
SELECT column, group_function
FROM table
[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];HAVING 子句
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;多表查询
笛卡尔集
为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件
Mysq连接
使用连接在多个表中查询数据
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;在 WHERE 子句中写入连接条件。
在表中有相同列时，在列名之前加上表名前缀
等值连接
SELECT beauty.id,NAME,boyname FROM beauty ,boys
WHERE beauty.<code>boyfriend_id</code>=boys.id
#多表查询
#查询员工的信息及部门名称
SELECT `employees`.*,`departments`.`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id`=`departments`.`department_id`
#查询部门信息及领导名称
SELECT departments.*,employees.first_name,employees.last_name
FROM departments,employees
WHERE departments.manager_id=employees.employee_id
#查询部门信息及办公地点
SELECT departments.*,locations.street_address
FROM departments,locations
WHERE departments.location_id=locations.location_id区分重复的列名
• 使用表名前缀在多个表中区分相同的列。
• 在不同表中具有相同列名的列可以用表的别名加以区分。
• 如果使用了表别名，则在select语句中需要使用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越好
表的别名
• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。
SELECT bt.id,NAME,boyname
FROM beauty bt,boys b;
WHERE bt.boyfriend_id=b.id ;
#查询员工的信息及职位名称
SELECT e1.*,e2.first_name,e2.last_name
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_idJoin连接
• 分类：
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
ON 子句
SELECT bt.id,NAME,boyname
eauty bt
FROM b
Inner join boys b
On bt.boyfriend_id=b.id 连接多个表
连接 n个表,至少需要 n-1个连接条件。
例如：连接三个表，至少需要两个连接条件。
练习：查询出公司员工的 last_name, department_name, city
使用 ON 子句创建多表连接
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;John连接总结
#内连接
#查询部门信息及办公地点
SELECT departments.*,locations.street_address
FROM departments INNER JOIN locations
ON departments.location_id=locations.location_id字符函数
大小写控制函数
这类函数改变字符的大小写。
LOWER('SQCourse')
sqcourse
UPPER('SQCourse')
SQCOURSE字符控制函数
CONCAT('Hello', 'World')
HelloWorld
SUBSTR('HelloWorld',1,5)
Hello
LENGTH('HelloWorld')
10
INSTR('HelloWorld', 'W')
6
LPAD(salary,10,'*')
*24000
RPAD(salary, 10, '*')
24000*
TRIM('H' FROM 'HelloWorld')
elloWorld
REPLACE('abcd','b','m')
amcd数字函数
ROUND: 四舍五入
ROUND(45.926, 2) 45.93TRUNCATE: 截断
TRUNC(45.926, 2) 45.92MOD: 求余
MOD(1600, 300) 100日期函数
now：获取当前日期
str_to_date: 将日期格式的字符转换成指定格式的日期
STR_TO_DATE('9-13-1999','%m-%d-%Y') 1999-09-13date_format:将日期转换成字符
DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) 2018年06月06日#函数
SELECT LOWER('Hello')
SELECT UPPER('hello')
SELECT first_name,
UPPER(first_name),
LOWER(first_name),
CONCAT(first_name,last_name),
SUBSTR(first_name,3),
LENGTH(first_name),
INSTR(first_name,'W'),
LPAD(first_name,10,'*'),
RPAD(first_name,10,'*'),
TRIM('H' FROM 'HHelloWorldH'),
REPLACE(first_name,'b','m')
FROM employees
SELECT ROUND(45.926,2),TRUNCATE(45.926,2),MOD(1600,300)
FROM employees
#now
SELECT NOW()
FROM employees
#1999-09-13
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y')
FROM employees
#2018年6月6日
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日')
FROM employees
#年
SELECT DATE_FORMAT(hiredate,'%Y')
FROM employees
#2000年入职的员工信息
SELECT *
FROM employees
WHERE DATE_FORMAT(hiredate,'%Y')=2000条件表达式
• 在 SQ语句中使用IF-THEN-ELSE 逻辑
• 使用方法: – CASE 表达式
CASE 表达式
在需要使用 IF-THEN-ELSE 逻辑时
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
下面是使用case表达式的一个例子：
SELECT last_name, job_id, salary,
CASE department_id WHEN 10 THEN 1.10*salary
WHEN 20 THEN 1.2*salary
WHEN 30 THEN 1.3*salary
ELSE salary END "REVISED_SALARY"
FROM employees;数据操纵语言
DML(Data Manipulation Language –数据操纵语言) 可以在下列条件下执行: – 向表中插入数据
– 修改现存数据
– 删除现存数据
事务是由完成若干项工作的DML语句组成的
插入数据
INSERT 语句语法
使用 INSERT 语句向表中插入数据。
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);使用这种语法一次只能向表中插入一条数据
插入数据
为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在 INSERT 子句中随意列出列名和他们的值。
• 字符和日期型数据应包含在单引号中。
INSERT INTO departments(department_id, department_name,manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700)INSERT INTO
employees(employee_id,last_name,email,hire_date,job_id)
VALUES (300,’Tom’,’tom@126.com’,to_date(‘2012-3-
21’,’yyyy-mm-dd’),’SA_RAP’);向表中插入空值
隐式方式: 在列名表中省略该列的值
INSERT INTO departments (department_id, department_name )
VALUES (30, 'Purchasing');显示方式: 在VALUES 子句中指定空值
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);插入指定的值
NOW()函数：记录当前系统的日期和时间
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number,hire_date, job_id, salary, commission_pct, manager_id,department_id)
VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', NOW(), 'AC_ACCOUNT', 6900, NULL, 205, 100);
从其它表中拷贝数据
在 INSERT 语句中加入子查询
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
不必书写 VALUES 子句。
子查询中的值列表应与 INSERT 子句中的列名对应
更新数据
UPDATE 语句语法
• 使用 UPDATE 语句更新数据
UPDATE table
SET column = value [column = value, ...]
[WHERE condition];• 可以一次更新多条数据。
• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
• 使用 WHERE 子句指定需要更新的数据
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;• 如果省略 WHERE 子句，则表中的所有数据都将被更新
UPDATE copy_emp
SET department_id = 110;
更新中的数据完整性错误
UPDATE employees
SET department_id = 55
WHERE department_id = 110;删除数据
DELETE 语句
使用 DELETE 语句从表中删除数据。
DELETE FROM table
[WHERE condition]删除数据
使用 WHERE 子句删除指定的记录
如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM departments
WHERE department_name = 'Finance';
DELETE FROM copy_emp;删除中的数据完整性错误
DELETE FROM departments
WHERE department_id = 60;子查询
概念
出现在其他语句内部的select语句，称为子查询或内查询,内部嵌套其他select语句的查询，称为外查询或主查询
示例：
select first_name from employees where
department_id in(
select department_id from departments
where location_id=1700
)注意事项
• 子查询要包含在括号内。
• 将子查询放在比较条件的右侧。
• 单行操作符对应单行子查询，多行操作符对应多行子查询
单行子查询
• 只返回一行。
• 使用单行比较操作符。
子查询语法
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);• 子查询 (内查询) 在主查询之前一次执行完成。
• 子查询的结果被主查询(外查询)使用 。
使用子查询解决问题
SELECT *
FROM employees
WHERE salary >(
SELECT salary FROM employees WHERE last_name = 'Abel'
)执行单行子查询
题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
SELECT first_name,last_name,job_id,salary
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`)
#查询公司最晚入职的员工信息
SELECT `last_name`,`job_id`,hiredate
FROM `employees`
WHERE `hiredate` =(SELECT MAX(`hiredate`) FROM `employees`)子查询中的 HAVING 子句
• 首先执行子查询。
• 向主查询中的HAVING 子句返回结果。
题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT `department_id`,MIN(`salary`)
FROM `employees`
GROUP BY `employee_id`
HAVING MIN(`salary`)>(
SELECT MIN(`salary`)
FROM `employees`
WHERE `department_id`=50
)子查询中的空值问题
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');子查询不返回任何行
多行子查询
• 返回多行。
• 使用多行比较操作符
使用in操作符
题目：返回location_id是1400或1700的部门中的所有员工姓名
查询location_id是1400或1700的部门
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN (1400,1700)根据部门编号查员工
#返回location_id是1400或1700的部门中的所有员工姓名
SELECT `last_name`
FROM `employees`
WHERE department_id IN (
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN (1400,1700)
)在多行子查询中使用 ANY 操作符
题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY (
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)
题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ALL (
SELECT salary
FROM employees
WHERE job_id='IT_PROG')
AND job_id<> 'IT_PROG'子查询中的空值问题
select emp.last_name
from employees emp
where emp.employee_id not in(
select mgr.manager_id
from employees mgr);
Comments | NOTHING