创建数据库
创建一个保存员工信息的数据库
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数据库、表的管理，数据库的增删改查的内容。