。MySQL可以说是最常用的数据库了,初学者最熟悉的无非就是增删改查四斧头,但在实际开发过程中,SQL语句涉及到的一些语法操作还是比较多的,有关数据库优化方面的知识更是面试必被问、开发必要会的猿人立家之本。本篇博客主要是先对MySQL的基础知识做一个比较全的梳理与汇总,方便复习与查找。测试代码所用的数据库为MySQL Server 5.5。
- 不积硅步,无以至千里;
- 不积小流,无以成江海。
文章目录
- 数据库和SQL概述
- 数据库的概念
- 数据库存储数据的特点
- MySQL的安装与使用
- MySQL安装与启动
- MySQL的使用
- MySQL语法规范
- SQL 语句分类
- MySQL基本命令
- 数据库处理之查询
- 基本的SELECT语句
- 列的别名
- 字符串
- 显示表结构
- 过滤和排序数据
- WHERE子句过滤
- 操作符过滤
- ORDER BY子句
- 分组查询
- 分组函数
- GROUP BY分组子句
- 过滤分组:HAVING子句
- 多表查询
- 笛卡尔集
- MySQL 连接
- 常见函数
- 字符函数
- 数学函数
- 日期函数
- 流程控制函数
- 条件表达式
- 数据库处理之增删改
- 使用DBL语句
- 插入数据INSERT 语句语法
- 更新数据UPDATE 语句语法
- 删除数据DELETE 语句语法
- 子查询
- 创建数据库和管理表
- 数据类型
- 约束和分页
- 描述约束
- 数据库分页
- 事务
- 事务的概念和特性
- 事务的使用
- 事务的隔离级别
- 视图
- 存储过程和函数
- 更多
数据库和SQL概述
数据库的概念
利用数据库可以持久化数据到本地,可以实现结构化查询,方便管理。
- DB:数据库(Database),保存一组有组织的数据的容器。
- DBMS:数据库管理系统(Database Management System),又称为数据库软件(产品),用于管理DB中的数据。
常见的数据库管理系统:MySQL、Oracle、DB2、SqlServer等。 - SQL:结构化查询语言(Structure Query Language),用于和DBMS通信的语言。
创建数据库、创建数据表、向数据表中添加一条条数据信息均需要使用SQL语句。
数据库存储数据的特点
- 将数据放到表中,表再放到库中。
- 一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
- 表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。
- 表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的”属性”。
- 表中的数据是按行存储的,每一行类似于java中的“对象”。
MySQL的安装与使用
MySQL安装与启动
这里有数据库安装与启动的详细步骤文档.
MySQL的使用
MySQL语法规范
- 不区分大小写,但建议关键字大写,表名、列名小写
- 每条命令最好用分号结尾
- 每条命令根据需要,可以进行缩进或换行
- 注释
单行注释:#注释文件
单行注释:–注释文字
多行注释:/注释文字/
SQL 语句分类
- DML(Data Manipulation Language):数据操纵语句,用于添加、删除、修改、查询数据库记录,并检查数据完整性。
- DDL(Data Definition Language):数据定义语句,用于库和
表的创建、修改、删除。 - DCL(Data Control Language):数据控制语句,用于定义用
户的访问权限和安全级别。
MySQL基本命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(
列名 列类型,
列名 列类型,
。。。
);
6.查看表结构
desc 表名;
7.查看服务器的版本
方式一:登录到mysql服务端
select version();
方式二:没有登录到mysql服务端
mysql --version
或
mysql --V
数据库处理之查询
基本的SELECT语句
SELECT * | {[DISTINCT] column | expression [alias], …}
FROM table;
- SELECT 标识选择哪些列。
- FROM 标识从哪个表中选择。
#选择全部列
SELECT *
FROM departments;
#选择特定的列
SELECT department_id, location_id
FROM departments
注意: SQL语言大小写不敏感,可以写在一行或多行,关键字不能被缩写也不能分行,各子句一般要分行,使用缩进提高语句的可读性。
列的别名
别名是指为了便于计算重命名一个列,紧跟列名,也可以在列名和别名之间加入关键字“AS”,别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
SELECT last_name AS name, commission_pct comm
FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;
字符串
字符串可以是SELECT列表的一个字符,数字,日期。日期和字符只能在单引号中出现。每当返回一行时,字符串被输出一次。
显示表结构
DESCRIBE employees
过滤和排序数据
WHERE子句过滤
使用WHERE子句,将不满足体条件的行过滤掉。
SELECT * | {[DISTINCT] column | expression [alias], …}
FROM table
[WHERE condition(s)];
WHERE子句要紧随FROM子句
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;
操作符过滤
操作符 | 含义 |
= | 等于(不是 ==) |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于(也可以是 !=) |
BETWEEN…AND… | 在两个值之间包括边界 |
IN | 等于值列表中的一个 |
LIKE | 模糊查询 |
IS NULL | 空值 |
AND | 逻辑并 |
OR | 逻辑或 |
NOT | 逻辑否 |
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201)
# %代表零个或多个字符(任意字符),_ 代表一个字符, % 和 _ 可以一起使用
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%'
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
SELECT last_name, manager_id
FROM employees
WHERE manager id IS NULL;
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 1000
AND job_id LIKE '%MAN%';
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 1000
OR job_id LIKE '%MAN%';
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
ORDER BY子句
使用OREDER 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列表中的列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
#department_id升序排列,salary降序排列
分组查询
分组函数
分组函数作用用于一组数据,并对一组数据返回一个值。常见组函数类型有
AVG()、COUNT()、MAX()、MIN()、SUM().
SELECT [column,] group function(column), …
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
可以对数值型数据使用AVG和SUM函数。
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
可以对任意数据类型的数据使用MIN和MAX函数。
SELECT MIN(hiredate), MAX(hiredate)
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分组子句
在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。
SELECT department_id, 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子句中使用组函数。
过滤分组:HAVING子句
使用HAVING过滤分组:
- 行已经被分组
- 使用了组函数
- 满足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)>1000;
多表查询
笛卡尔集
存在beauty和boys两个表
如果现在需要查询女神名称和对应的男神名称,要怎么写sql语句?如果你这样写
select name, boyName from beauty, boys;
/*
笛卡尔集的错误情况:
select count(*) from beauty;
假设输出12行
select count(*)from boys;
假设输出4行
最终结果:12*4=48行
*/
笛卡尔集会在下面条件下产生:
- 省略廉连接条件
- 连接条件无效
- 所有表中的所有行互相连接
为了避免笛卡尔集,可以在WHERE加入有效的连接条件。
MySQL 连接
使用连接在多个表中查询数据
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
**在WHERE子句中写入连接条件,在表中有相同列时,在列名之前加上表名前缀。
**
- 等值连接
SELECT beauty.id, NAME, boyName
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id;
/*
在不同表中具有相同列名的列可以用表的别名加以区分
如果使用了表别名,则在SELECT语句中需要使用表别名代替表名
表别名最多支持32个字符长度,但是建议越少越好
使用别名可以简化查询,使用表名前缀可以提高执行效率
*/
SELECT bt.id, NAME, boyname
FROM beauty bt, boys b
WHERE bt.boyfriend_id = b.id;
/*
查询出公司员工的last_name,department_name,city
**连接n个表,至少需要n-1个连接条件。**
*/
SELECT last_name, department_name, city
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id
- 使用ON子句创建连接
- 自然连接中是以具有相同名字的列为连接条件的。
- 可以使用ON子句指定额外的连接条件。
- 这个连接条件是与其他条件分开的。
- ON子句使语句有更高的易读性。
分类:
内连接[inner] join on
外连接:
左外连接left[outer] join on
右外连接right[outer]join on
#使用ON子句创建多表连接
SELECT bt.id, Name, boyname
FROM beauty bt
Inner join boys b
ON bt.boyfriend_id = b.id
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;
JOIN连接总结
常见函数
字符函数
函数 | 处理结果 |
LOWER(‘SQL Course’) | sql course |
UPPER(‘SQL Course’) | SQL COURSE |
CONCAT(‘Hello’, ‘World’) | HelloWorld |
SUBSTR(‘HelloWorld’,1,5) | Hello(注意索引从1开始) |
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.93
TRUNCATE:截断
TRUNC(45.926,2) ---- 45.92
MOD:求余
MOD(1600,300) ---- 100
日期函数
now : 获取当前日期
str_to_date:将日期格式的字符转换成指定格式的日期
STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) 1999-09-13
date_format:将日期转换成字符
DATE_FORMAT(‘2018/6/6’,’%Y年%m月%d日’) 2018年06月06日
格式符 | 功能 |
%Y | 四位的年份 |
%y | 二位的年份 |
%m | 月份(01, 02…11, 12) |
%c | 月份(1,2…11,12) |
%d | 日(01,02…) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01…59) |
%s | 秒(00,01…59) |
流程控制函数
条件表达式
在SOL语句中使用IF-THEN-ElSE逻辑
- CASE表达式
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_expr
ELSE else_expr]
END
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_RED' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees
数据库处理之增删改
使用DBL语句
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)
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');
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 (301,'Louis','Popp',
'LPOPP', '515.124.4567',
NOW(),'AC_ACCOUNT', 6900,
NULL, 205,100);
# 从其它表中拷贝数据
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
更新数据UPDATE 语句语法
使用UPDATE语句更新数据可以一次更新多条数据。如果需要回滚数据,需要保证在DML前进行设置:SET AUTOCOMMIT = FALSE;
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
# 使用WHERE子句指定需要更新的数据。
# 如果不指定具体的行,会导致表中所有数据都将被更新
UPDATE employees
SET department_id = 70
WHERE employee_id = 303;
删除数据DELETE 语句语法
使用DELETE语句从表中删除数据,可以使用WHERE子句删除指定的记录,否则表中的全部数据将被删除。
DELETE FROM table
[WHERE condition];
DELETE FROM departments
WHERE department_name = 'Finance';
#下列删除语句会报数据完整性错误
# 不能删除该行,因为该id是其他表的外键。
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 last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
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';
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';
创建数据库和管理表
创建一个保存员工信息的数据库
create database emploees;
#查看当前所有数据库
show databases;
#"使用"一个数据库,使其作为当前数据库
use employees;
数据库命名规则
• 数据库名不得超过30个字符,变量名限制为29个 • 必须只能包含 A–Z, a–z, 0–9, _共63个字符。
• 不能在对象名的字符间留空格。
• 不能和用户定义的其他对象重名。
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。
假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了。
创建表 CREATE TABLE 语句
#必须指定表名,列名,数据类型,尺寸
CREATE TABLE [schema.] table (column datatype [DEFAULT expr][,...]);
CREATE TABLE dept
(deptno INT(2),
dname VARCHAR(14),
loc VACHAR(13));
#查看表结构
DESCRIBE dept
CREATE TABLE emp(
#int类型,自增
emp_id INT AUTO_INCREMENT,
#最多保存20个中英文字符
emp_name CHAR(20),
#总位数不超过15位
salary DOUBLE,
#日期类型
birthday DATE,
#主键
PRIMARY KEY (emp_id)
);
常用数据类型
数据名 | 表示作用 |
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 |
使用子查询创建表
使用 AS subquery 选项,将创建表和插入数据结合起来,指定的列和子查询中的列要一一对应,通过列名和默认值定义列
CREATE table emp1
AS SELECT * FROM emploees;
CREATE table emp2
AS select * from employees where 1 = 2;
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);
#删除一个列
ALTER TABLE dept80
DROP COLUMN job_id;
#重命名一个列
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
#改变对象的名称,执行RENAME语句改变表、视图的名称。
ALTER table dept
RENAME TO detail_dept;
删除表
数据和结构都被删除,所有正在运行的相关事务被提交,所有相关索引被删除,DROP TABLE语句不能回滚。
DROP TABLE dept80;
清空表
TRUNCATE TABLE 语句删除表中所有的数据,释放表的存储空间,不能回滚,使用DLETE语句可以回滚。
TRUNCATE TABLE detail_dept;
数据类型
数据类型主要包括数值类型,字符类型,日期类型。这里只说需要特别注意的一些地方。
char和varchar类型
两者用来保存MySQL中较短的字符串。
- char(M),M为0~255之间的整数
- varchar(M),M为0~65535之间的整数
char和varchar类型
说明:类似于char和varchar,不同的是它们包含二进制字符串而不包含非二进制字符串。
Enum类型
说明:又称为枚举类型哦,要求插入的值必须属于列表中指定的值之一。
如果列表成员为1-255,则需要1个字节存储。
如果列表成员为255-65535,则需要2个字节存储。
Set类型
说明:和Enum类型类似,里面可以保存0~64个成员。和Enum类型最大的区别是SET类型一次可以选取多个成员,而Enum只能选一个,根据成员个数不同,存储所占字节不同。
成员数 | 字节数 |
1-8 | 1 |
9-16 | 2 |
17-24 | 3 |
25-32 | 4 |
32-64 | 8 |
日期类型
Datetime和Timestamp的区别
Timestamp支持的时间范围较小:19700101080001–2038年的某个时间
Datetime支持的时间范围较大:1000-1-1 – 9999-12-31
Time和实际时区有关,更能反应实际的日期,不过受MySQL版本和SQLMode的影响较大
Datetime只能反映出插入时的当地时区。
约束和分页
描述约束
- 为了保证数据的一致性和完整性,SQL规范以约束的方式对表数据进行额外的条件限制。
- 约束是表级的强制规定
- 可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)
常见约束种类 - NOT NULL 非空约束,规定某个字段不能为空
- UNIOUE 唯一约束,规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)
- FOREIGN KEY 外键
- CHECK 检查约束
- DEFAULT 默认值
**注意:**MySQL不制止check约束,但可以使用,只是没有任何效果。
根据约束数据列的限制,约束可分为:
- 单列约束:每个约束只约束一列
- 多列约束:每个约束可约束多列数据
根据约束的作用范围,约束可以分为
- 列级约束只能作用在一个列上,跟在列的定义后面
- 表级约束可以作用在多个列上,不与列一起,而是单独定义
#约束代码测试
# NOT NULL约束
#创建not null约束
CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VACHAR(20) NOT NULL DEFAULT 'abc',
sex CHAR NULL);
#增加not null约束:
ALTER TABLE emp
MODIFY sex VACHAR(30) NOT NULL;
#取消not null 约束
ALTER TABLE emp
MODIFY sex VACHAR(30) NULL;
#取消not null 约束,增加默认值
ALTER TABLE emp
MODIFY NAME VACHAR(15) DEFAULT 'abc' NULL;
#UNIQUE约束
#同一个表中可以有多个唯一约束,多个列组合的约束。在创建唯一约束的时候,如果不给唯一约束名称
#就默认和列名相同。
#MySQL会给唯一约束的列上默认创建一个唯一索引
#表示用户名和密码组合不能重复
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
#使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE (NAME, PASSWORD));
#添加唯一约束
ALTER TABLE USER
ADD UNIQUE(NAME, PASSWORD)
ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);
ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;
#删除约束
ALTER TABLE USER
DROP INDEX uk_name_pwd;
#PRIMARY KEY 约束
/*
主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值
• 如果是多列组合的主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
• 每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建。
• MySQL的主键名总是PRIMARY,当创建主键约束时,
系统默认会在所在的列和列组合上建立对应的唯一索引。*/
#列级模式的PRIMARY KEY约束
CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);
#表级模式
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);
#组合模式
CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);
#删除主键约束
ALTER TABLE emp5
DROP PRIMARY KEY;
#添加主键约束
ALTER TABLE emp5
ADD PRIMARY KEY(NAME,pwd);
#修改主键约束
ALTER TABLE emp5
MODIFY id INT PRIMARY KEY;
#FOREIGN KEY 外键约束
/*
• 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字
段之间的参照关系。
• 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时,主表的记录将不允许删除,
如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据。
• 还有一种就是级联删除子表数据。
• 注意:外键约束的参照列,在主表中引用的只能是主键或唯一键约束的列
• 同一个表可以有多个外键约束
*/
#创建外键约束
#主表
CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);
CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT
) CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
# 删除外键约束,增加外键约束
ALTER TABLE emp
DROP FOREIGN KEY emp_dept_id_fk;
#增加外键约束
ALTER TABLE emp
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);
# FOREIGN KEY 约束的关键字
# FOREIGN KEY 在表级指定子表中的列
# REFERENCES 标识在父表中的列
# ON DELETE CASCADE(级联删除):当父表中的列被删除时,子表中相应的列也被删除
# ON DELETE SET NULL(级联置空):子表中相应的列置空
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20),
classes_name VARCHAR(20),
classes_number INT,
/*表级别联合外键*/
FOREIGN KEY (classes_name, classes_number)
REFERENCES classes(NAME, number) ON DELETE CASCADE;
数据库分页
查询返回的记录太多了,查看起来很不方便,怎么样能够实现分页查询呢?所谓分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件。
MySQL中使用limit实现分页
#(当前页数-1)*每页条数, 每页条数
SELECT * FROM table LIMIT(PageNo-1)*PageSize, PageSize;
limit子句必须放在整个查询语句的最后!
事务
事务的概念和特性
概念: 事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
事务的ACID属性
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 - 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 - 隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。 - 持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
事务的使用
以第一个 DML 语句的执行作为开始,以下面的其中之一作为结束:
- COMMIT 或 ROLLBACK 语句
- DDL 或 DCL 语句(自动提交)
- 用户会话正常结束
- 系统异常终了
事务的隔离级别
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
- 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
- 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了。
- 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插 入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行。
- 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
- 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。
MySQL支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。
在MySQL中设置隔离级别
- 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
- 查看当前的隔离级别: SELECT @@tx_isolation。
- 设置当前 mySQL 连接的隔离级别。
- set transaction isolation level read committed;设置数据库系统的全局的隔离级别: set global transaction isolation level read committed。
视图
MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果 。如果多个地方用到同样的查询结果,该查询结果使用的sql语句较复杂时可以考虑使用视图。
视图的特点
• 重用sql语句
• 简化复杂的sql操作,不必知道它的查询细节
• 保护数据,提高安全性
# 创建视图的语法:
create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]
# 修改视图的语法:
alter view view_name
As select_statement
[with|cascaded|local|check option]
CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;
# 删除视图的语法:
#用户可以一次删除一个或者多个视图,前提是必须有该视图的drop权限。
drop view [if exists] view_name,view_name …[restrict|cascade]
# 查看视图的语法:
show tables;
#如果需要查询某个视图的定义,可以使用show create view
#命令进行查看:
show create view view_name \G
# \G是改变表的显示方向,变成纵向显示
视图的可更新性和视图中查询的定义有关系,以下类型的视图是不能更新的。
• 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表
存储过程和函数
存储过程和函数:事先经过编译并存储在数据库中的一段sql语句的集合。使用好处:
- 简化应用开发人员的很多工作;
- 减少数据在数据库和应用服务器之间的传输;
- 提高了数据处理的效率;
# 创建存储过程:
create procedure 存储过程名 ([proc_parameter[,…]])[characteristic…]routine_body
# 创建函数:
create function 函数名([func_parameter[,…]])
returns type [characteristic…]routine_body
# proc_parameter: [in|out|inout] param_name type
# Func_paramter: param_name type
# Type: 任何有效的mysql数据类型
# Characteristic:
language sql(默认,且推荐)
|[not] deterministic
|{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’
# Rountine_body: 有效的sql 过程语句
# 调用存储过程:
call 存储过程名(参数列表) • 调用函数:
Select 函数名(参数列表)
# 修改存储过程:
alter procedure 存储过程名 [charactristic…]
# 修改函数:
alter function 函数名 [charactristic…]
# characteristic包括:
{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’
#说明:一次只能删除一个存储过程或者函数,并且要求有该过程或函数的alter routine 权限
#删除存储过程:
drop procedure [if exists] 存储过程名
#删除函数:
drop function [if exists] 函数名
#1.查看存储过程或函数的状态:
show {procedure|function} status like 存储过程或函数名
#2.查看存储过程或函数的定义:
show create {procedure|function} 存储过程或函数名
#3.通过查看information_schema.routines了解存储过程和函数的信息(了解)
select * from rountines where rounine_name =存储过程名|函数名