创建一个保存员工信息的数据库 create database employees
;
相关其他命令
show databases;
查看当前所有数据库
use employees;
“使用”一个数据库,使其作为当前数据库
命名规则
数据库名不得超过30个字符,变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
不能在对象名的字符间留空格
必须不能和用户定义的其他对象重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
CREATE TABLE 语句
必须具备:
CREATE TABLE权限
存储空间
必须指定:
表名
列名, 数据类型, 尺寸
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, …]);
创建表
语法
CREATE TABLE dept
(deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13));
确认
DESCRIBE dept
常用数据类型
INT 使用4个字节保存整数数据
CHAR(size) 定长字符数据。若未指定,默认为1个字符,最大长度255
VARCHAR(size) 可变长字符数据,根据字符串实际长度保存,必须指定长度
FLOAT(M,D) 单精度,M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30,默认M+D<=6
DOUBLE(M,D) 双精度。D<=M<=255,0<=D<=30,默认M+D<=15
DATE 日期型数据,格式’YYYY-MM-DD’
BLOB 二进制形式的长文本数据,最大可达4G
TEXT 长文本数据,最大可达4G
**创建表** CREATE TABLE emp ( #int类型,自增 emp_id INT AUTO_INCREMENT, #最多保存20个中英文字符 emp_name CHAR (20), #总位数不超过15位 salary DOUBLE, #日期类型 birthday DATE, #主键 PRIMARY KEY (emp_id) ) ;
使用子查询创建表
使用 AS subquery 选项,将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列
CREATE TABLE table [(column, column...)] AS subquery;
使用子查询创建表举例
CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;
ALTER TABLE 语句
使用 ALTER TABLE 语句可以实现:
向已有的表中添加列
ALTER TABLE dept80 ADD job_id varchar(15);` **修改现有表中的列** 可以修改列的数据类型, 尺寸和默认值 对默认值的修改只影响今后对表的修改 ``ALTER TABLE dept80 MODIFY (last_name VARCHAR(30));
ALTER TABLE dept80 MODIFY (salary double(9,2) default 1000);
丢弃现有表中的列
使用 DROP COLUMN 子句丢弃不再需要的列.
ALTER TABLE dept80 DROP COLUMN job_id;
重命名现有表中的列
使用 CHANGE old_column new_column dataType子句重命名列
ALTER TABLE dept80 *CHANGE department_name dept_name varchar(15);
丢弃表*
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚
DROP TABLE dept80;
清空表
TRUNCATE TABLE 语句:
删除表中所有的数据
释放表的存储空间
TRUNCATE TABLE detail_dept;
TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据,可以回滚
对比:
delete from emp2;
select * from emp2;
rollback;
select * from emp2;
改变对象的名称
执行RENAME语句改变表, 视图的名称
必须是对象的拥有者
ALTER table dept
RENAME TO detail_dept;
数据操纵语言
DML(Data Manipulation Language – 数据操纵语言) 可以在下列条件下执行:
向表中插入数据
修改现存数据
删除现存数据
事务是由完成若干项工作的DML语句组成的
使用 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)
向表中插入空值
隐式方式: 在列名表中省略该列的值。
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 语句中加入子查询。
不必书写 VALUES 子句。
子查询中的值列表应与 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%';
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; 不存在 55 号部门
使用 DELETE 语句从表中删除数据。 DELETE FROM table [WHERE condition];
使用 WHERE 子句删除指定的记录。
DELETE FROM departments WHERE department_name = 'Finance';
如果省略 WHERE 子句,则表中的全部数据将被删除
DELETE FROM copy_emp;
删除中的数据完整性错误
DELETE FROM departments WHERE department_id = 60; 不存在60号部门
1—基本select语句
SELECT 标识选择哪些列。
FROM 标识从哪个表中选择。
// 查询 select * from 表名;// 查询表中的所有数据, 查询所有列 // 虚表的列由select后面from前面的内容 select manager_id, manager_id, department_name from departments;
注 意:
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
列的别名:
重命名一个列。
便于计算。紧跟列名,也可以在列名和别名之间加入关键字‘AS’,别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
// 列的别名
select manager_id as manager, manager_id, department_name "dept name" from departments;
字符串可以是 SELECT 列表中的一个字符,数字,日期。
日期和字符只能在单引号中出现。
每当返回一行时,字符串被输出一次。
使用 DESCRIBE 命令,表示表结构
2—过滤和排序数据
使用WHERE 子句,将不满足条件的行过滤掉。 WHERE 子句紧随 FROM 子句。 SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
(1)比较运算
赋值使用 := 符号
SELECT last_name, salary FROM employees WHERE salary <= 3000;
(2)其它比较运算
SELECT last_name, salary FROM employeesWHERE salary BETWEEN 2500 AND 3500;
使用 IN运算显示列表中的值。
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
使用 LIKE 运算选择类似的值
选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
‘%’和‘-’可以同时使用。
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
使用 IS (NOT) NULL 判断空值。
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
(3)逻辑运算
AND 要求并的关系为真。
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';
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;
多个列排序:按照ORDER BY 列表的顺序排序。可以使用不在SELECT 列表中的列排序。
SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;
3 — 多表查询
从多个表中获取数据
笛卡尔集
笛卡尔集会在下面条件下产生:
省略连接条件
连接条件无效
所有表中的所有行互相连接
为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件。
使用连接在多个表中查询数据。
在 WHERE 子句中写入连接条件。
在表中有相同列时,在列名之前加上表名前缀
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
等值连接
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;
多个连接条件与 AND 操作符
区分重复的列名
使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。
表的别名
使用别名可以简化查询。
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;
连接多个表
连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
使用ON 子句创建连接
自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idF ROM employees e JOIN departments d ON (e.department_id = d.department_id);
使用 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;
4 — 单行函数
大小写控制函数:这类函数改变字符的大小写。
字符控制函数:这类函数控制字符
数字函数
ROUND: 四舍五入ROUND(45.926, 2) 45.93
TRUNCATE: 截断TRUNC(45.926, 2) 45.92
MOD: 求余MOD(1600, 300) 100
条件表达式(了解)
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用方法:
CASE 表达式
5 — 分组函数
分组函数作用于一组数据,并对一组数据返回一个值。
组函数类型
AVG() COUNT() MAX() MIN() SUM()
组函数语法
SELECT [column,] group_function(column), …
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
AVG(平均值)和 SUM (合计)函数
可以对数值型数据使用AVG 和 SUM 函数。
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(*) FROM employees WHERE department_id = 50;
COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;
分组数据: GROUP BY 子句语法
可以使用GROUP BY子句将表中的数据分成若干组
明确:WHERE一定放在FROM后面
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;
包含在 GROUP BY 子句中的列不是必须包含在SELECT 列表中
SELECT AVG(salary) FROM employees GROUP BY department_id ;
在GROUP BY子句中包含多个列
SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
非法使用组函数
不能在 WHERE 子句中使用组函数。
可以在 HAVING 子句中使用组函数。
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; WHERE AVG(salary) > 8000; ERROR at line 3: ORA-00934: group function is not allowed here
过滤分组: HAVING 子句
使用 HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;
6—子查询
子查询语法
SELECT select_listF ROM table WHERE expr operator (SELECT select_list FROM table);
子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
注意事项:
子查询要包含在括号内。
单行操作符对应单行子查询,多行操作符对应多行子查询。
子查询类型
单行子查询:子查询返回给主查询的结果为一个值。
只返回一行。
使用单行比较操作符。
题目:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id 和工资
SELECT 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);
在子查询中使用组函数
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
子查询中的 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);
非法使用子查询
SELECT employee_id, last_name FROM employees WHERE salary in (SELECT MIN(salary) FROM employees GROUP BY department_id);
多行子查询使用单行比较符
子查询中的空值问题
SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');
子查询不返回任何行
多行子查询:子查询返回给主查询的结果多与一个值。
返回多行。
使用多行比较操作符。
在多行子查询中使用 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') AND job_id <> 'IT_PROG';
在多行子查询中使用 ALL 操作符
题目:返回其它部门中比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); no rows selected
**
**
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成:
一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句
在数据库编程语言中,事务是将一个数据处理执行步骤的集合作为一个单元来处理。
也就是说,执行这些步骤就好像是执行一个单个的命令一样。
设置提交状态:SET AUTOCOMMIT = FALSE;
或者显式的执行 start transaction或 begin
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 语句(自动提交)
用户会话正常结束
系统异常终止
COMMIT和ROLLBACK语句的优点
使用COMMIT 和 ROLLBACK语句,我们可以:
确保数据完整性。
数据改变被提交之前预览。
将逻辑上相关的操作分组。
使用预处理语句
MySQL服务器支持预处理语句。当想要执行多个查询,而每个查询之间只有很小的差别时,预处理语句将会非常有用。
例如,可以预备一条语句,然后多次执行它,而每次只是数据值不同。
除了提供执行的方便外,预处理语句还能提高性能。
可以在mysql命令行客户端定义与使用预处理语句来测试与调试程序。
用户定义变量
用户可以保存一个值到用户定义的变量中(也称为用户变量),然后在以后执行预处理语句时使用它。
用户变量用@var_name表示。可使用SET语句来设置用户变量:
SET @var_name = expr [, @var_name = expr] …
下列示例预备了一条语句:
确定一个给定国家有多少种语言被使用
然后使用用户定义的变量来执行它多次,并显示结果:
PREPARE my_stmt FROM ' SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode= ? '; SET @code = 'ESP'; EXECUTE my_stmt USING @code; SET @code = 'RUS'; EXECUTE my_stmt USING @code; DEALLOCATE PREPARE my_stmt;
可使用PREPARE语句定义一条SQL语句,用以在以后执行。
语句可以是不完整的,在预备时的未知数据值可以由问号(?)来表示,它作为参数标记。
在语句被执行时,可以为语句的每个参数指定一个数据值。服务器将会用数据值来替换标记符号以完成该语句。
语句在每次执行时可以使用不同的值。
如果PREPARE语句使用的名称已经存在,服务器将丢弃该名称原有的预处理语句,然后预备一个新的语句。
释放预处理语句
当预处理语句被重新定义,或与服务器的连接被中断后,预处理语句将被自动删除。
可使用DEALLOCATE PREPARE语句来释放语句:
DEALLOCATE PREPARE namepop;
MySQL还提供了DROP PREPARE作为DEALLOCATE PREPARE语句的别名。
总 结
通过本章学习, 您应学会如何使用DML语句改变数据和事务控制
创建一个保存员工信息的数据库 create database employees
;
相关其他命令
show databases;
查看当前所有数据库
use employees;
“使用”一个数据库,使其作为当前数据库
命名规则
数据库名不得超过30个字符,变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
不能在对象名的字符间留空格
必须不能和用户定义的其他对象重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
CREATE TABLE 语句
必须具备:
CREATE TABLE权限
存储空间
必须指定:
表名
列名, 数据类型, 尺寸
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, …]);
创建表
语法
CREATE TABLE dept
(deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13));
确认
DESCRIBE dept
常用数据类型
INT 使用4个字节保存整数数据
CHAR(size) 定长字符数据。若未指定,默认为1个字符,最大长度255
VARCHAR(size) 可变长字符数据,根据字符串实际长度保存,必须指定长度
FLOAT(M,D) 单精度,M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30,默认M+D<=6
DOUBLE(M,D) 双精度。D<=M<=255,0<=D<=30,默认M+D<=15
DATE 日期型数据,格式’YYYY-MM-DD’
BLOB 二进制形式的长文本数据,最大可达4G
TEXT 长文本数据,最大可达4G
**创建表** CREATE TABLE emp ( #int类型,自增 emp_id INT AUTO_INCREMENT, #最多保存20个中英文字符 emp_name CHAR (20), #总位数不超过15位 salary DOUBLE, #日期类型 birthday DATE, #主键 PRIMARY KEY (emp_id) ) ;
使用子查询创建表
使用 AS subquery 选项,将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列
CREATE TABLE table [(column, column...)] AS subquery;
使用子查询创建表举例
CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;
ALTER TABLE 语句
使用 ALTER TABLE 语句可以实现:
向已有的表中添加列
ALTER TABLE dept80 ADD job_id varchar(15);` **修改现有表中的列** 可以修改列的数据类型, 尺寸和默认值 对默认值的修改只影响今后对表的修改 ``ALTER TABLE dept80 MODIFY (last_name VARCHAR(30));
ALTER TABLE dept80 MODIFY (salary double(9,2) default 1000);
丢弃现有表中的列
使用 DROP COLUMN 子句丢弃不再需要的列.
ALTER TABLE dept80 DROP COLUMN job_id;
重命名现有表中的列
使用 CHANGE old_column new_column dataType子句重命名列
ALTER TABLE dept80 *CHANGE department_name dept_name varchar(15);
丢弃表*
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚
DROP TABLE dept80;
清空表
TRUNCATE TABLE 语句:
删除表中所有的数据
释放表的存储空间
TRUNCATE TABLE detail_dept;
TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据,可以回滚
对比:
delete from emp2;
select * from emp2;
rollback;
select * from emp2;
改变对象的名称
执行RENAME语句改变表, 视图的名称
必须是对象的拥有者
ALTER table dept
RENAME TO detail_dept;
数据操纵语言
DML(Data Manipulation Language – 数据操纵语言) 可以在下列条件下执行:
向表中插入数据
修改现存数据
删除现存数据
事务是由完成若干项工作的DML语句组成的
使用 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)
向表中插入空值
隐式方式: 在列名表中省略该列的值。
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 语句中加入子查询。
不必书写 VALUES 子句。
子查询中的值列表应与 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%';
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; 不存在 55 号部门
使用 DELETE 语句从表中删除数据。 DELETE FROM table [WHERE condition];
使用 WHERE 子句删除指定的记录。
DELETE FROM departments WHERE department_name = 'Finance';
如果省略 WHERE 子句,则表中的全部数据将被删除
DELETE FROM copy_emp;
删除中的数据完整性错误
DELETE FROM departments WHERE department_id = 60; 不存在60号部门
1—基本select语句
SELECT 标识选择哪些列。
FROM 标识从哪个表中选择。
// 查询 select * from 表名;// 查询表中的所有数据, 查询所有列 // 虚表的列由select后面from前面的内容 select manager_id, manager_id, department_name from departments;
注 意:
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
列的别名:
重命名一个列。
便于计算。紧跟列名,也可以在列名和别名之间加入关键字‘AS’,别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
// 列的别名
select manager_id as manager, manager_id, department_name "dept name" from departments;
字符串可以是 SELECT 列表中的一个字符,数字,日期。
日期和字符只能在单引号中出现。
每当返回一行时,字符串被输出一次。
使用 DESCRIBE 命令,表示表结构
2—过滤和排序数据
使用WHERE 子句,将不满足条件的行过滤掉。 WHERE 子句紧随 FROM 子句。 SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
(1)比较运算
赋值使用 := 符号
SELECT last_name, salary FROM employees WHERE salary <= 3000;
(2)其它比较运算
SELECT last_name, salary FROM employeesWHERE salary BETWEEN 2500 AND 3500;
使用 IN运算显示列表中的值。
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
使用 LIKE 运算选择类似的值
选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
‘%’和‘-’可以同时使用。
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
使用 IS (NOT) NULL 判断空值。
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
(3)逻辑运算
AND 要求并的关系为真。
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';
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;
多个列排序:按照ORDER BY 列表的顺序排序。可以使用不在SELECT 列表中的列排序。
SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;
3 — 多表查询
从多个表中获取数据
笛卡尔集
笛卡尔集会在下面条件下产生:
省略连接条件
连接条件无效
所有表中的所有行互相连接
为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件。
使用连接在多个表中查询数据。
在 WHERE 子句中写入连接条件。
在表中有相同列时,在列名之前加上表名前缀
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
等值连接
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;
多个连接条件与 AND 操作符
区分重复的列名
使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。
表的别名
使用别名可以简化查询。
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;
连接多个表
连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
使用ON 子句创建连接
自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idF ROM employees e JOIN departments d ON (e.department_id = d.department_id);
使用 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;
4 — 单行函数
大小写控制函数:这类函数改变字符的大小写。
字符控制函数:这类函数控制字符
数字函数
ROUND: 四舍五入ROUND(45.926, 2) 45.93
TRUNCATE: 截断TRUNC(45.926, 2) 45.92
MOD: 求余MOD(1600, 300) 100
条件表达式(了解)
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用方法:
CASE 表达式
5 — 分组函数
分组函数作用于一组数据,并对一组数据返回一个值。
组函数类型
AVG() COUNT() MAX() MIN() SUM()
组函数语法
SELECT [column,] group_function(column), …
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
AVG(平均值)和 SUM (合计)函数
可以对数值型数据使用AVG 和 SUM 函数。
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(*) FROM employees WHERE department_id = 50;
COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;
分组数据: GROUP BY 子句语法
可以使用GROUP BY子句将表中的数据分成若干组
明确:WHERE一定放在FROM后面
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;
包含在 GROUP BY 子句中的列不是必须包含在SELECT 列表中
SELECT AVG(salary) FROM employees GROUP BY department_id ;
在GROUP BY子句中包含多个列
SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
非法使用组函数
不能在 WHERE 子句中使用组函数。
可以在 HAVING 子句中使用组函数。
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; WHERE AVG(salary) > 8000; ERROR at line 3: ORA-00934: group function is not allowed here
过滤分组: HAVING 子句
使用 HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;
6—子查询
子查询语法
SELECT select_listF ROM table WHERE expr operator (SELECT select_list FROM table);
子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
注意事项:
子查询要包含在括号内。
单行操作符对应单行子查询,多行操作符对应多行子查询。
子查询类型
单行子查询:子查询返回给主查询的结果为一个值。
只返回一行。
使用单行比较操作符。
题目:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id 和工资
SELECT 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);
在子查询中使用组函数
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
子查询中的 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);
非法使用子查询
SELECT employee_id, last_name FROM employees WHERE salary in (SELECT MIN(salary) FROM employees GROUP BY department_id);
多行子查询使用单行比较符
子查询中的空值问题
SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');
子查询不返回任何行
多行子查询:子查询返回给主查询的结果多与一个值。
返回多行。
使用多行比较操作符。
在多行子查询中使用 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') AND job_id <> 'IT_PROG';
在多行子查询中使用 ALL 操作符
题目:返回其它部门中比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); no rows selected
**
**
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成:
一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句
在数据库编程语言中,事务是将一个数据处理执行步骤的集合作为一个单元来处理。
也就是说,执行这些步骤就好像是执行一个单个的命令一样。
设置提交状态:SET AUTOCOMMIT = FALSE;
或者显式的执行 start transaction或 begin
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 语句(自动提交)
用户会话正常结束
系统异常终止
COMMIT和ROLLBACK语句的优点
使用COMMIT 和 ROLLBACK语句,我们可以:
确保数据完整性。
数据改变被提交之前预览。
将逻辑上相关的操作分组。
使用预处理语句
MySQL服务器支持预处理语句。当想要执行多个查询,而每个查询之间只有很小的差别时,预处理语句将会非常有用。
例如,可以预备一条语句,然后多次执行它,而每次只是数据值不同。
除了提供执行的方便外,预处理语句还能提高性能。
可以在mysql命令行客户端定义与使用预处理语句来测试与调试程序。
用户定义变量
用户可以保存一个值到用户定义的变量中(也称为用户变量),然后在以后执行预处理语句时使用它。
用户变量用@var_name表示。可使用SET语句来设置用户变量:
SET @var_name = expr [, @var_name = expr] …
下列示例预备了一条语句:
确定一个给定国家有多少种语言被使用
然后使用用户定义的变量来执行它多次,并显示结果:
PREPARE my_stmt FROM ' SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode= ? '; SET @code = 'ESP'; EXECUTE my_stmt USING @code; SET @code = 'RUS'; EXECUTE my_stmt USING @code; DEALLOCATE PREPARE my_stmt;
可使用PREPARE语句定义一条SQL语句,用以在以后执行。
语句可以是不完整的,在预备时的未知数据值可以由问号(?)来表示,它作为参数标记。
在语句被执行时,可以为语句的每个参数指定一个数据值。服务器将会用数据值来替换标记符号以完成该语句。
语句在每次执行时可以使用不同的值。
如果PREPARE语句使用的名称已经存在,服务器将丢弃该名称原有的预处理语句,然后预备一个新的语句。
释放预处理语句
当预处理语句被重新定义,或与服务器的连接被中断后,预处理语句将被自动删除。
可使用DEALLOCATE PREPARE语句来释放语句:
DEALLOCATE PREPARE namepop;
MySQL还提供了DROP PREPARE作为DEALLOCATE PREPARE语句的别名。
总 结
通过本章学习, 您应学会如何使用DML语句改变数据和事务控制
以上就是MySQL数据库、表的管理,数据库的增删改查的内容,更多相关内容请关注PHP中文网(www.php.cn)!