数据库管理系统(DBMS=管理程序 + 多个数据库)
常见DBMS:Orcale、MySQL、SQL Server、DB2、Sybase
SQL
- 全称:结构化查询语言(Structured Query Language)。
- 作用:通过SQL来操作数据库。
- 方言: 某种DBMS不只会支持SQL标准,而且还会有一些自己独有的语法,这就称之为方言!例如limit语句只在MySQL中可以使用
- 如果明确知道只有一条结果返回,limit 1能够提高效率(select * from user where login_name=? limit 1)
- SQL语句可以在单行或多行书写,以分号结尾
- MySQL不区别大小写,但是linux操作系统是区分的(统一使用小写)
SQL语句分类
- DDL(Data Definition Language):数据定义语言,用来定义数据库对象: 创建、删除、修改:库、表结构等;
- DML(Data Manipulation Language):数据操作语言,用来定义数据库记录( 增、删、改:表数据);
- DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别,对用户的创建,及授权;
- DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
DDL
-
常用命令:
查看所有数据库:SHOW DATABASES
切换数据库:USE 数据库名
创建数据库:CREATE DATABASE DBName
删除数据库:DROP DATABASE DBName
查看当前数据库中所有表名称:SHOW TABLES;
查看表结构:DESC 表名;
删除表:DROP TABLE 表名;
修改表:ALTER TABLE 表名 -
修改列类型(如果被修改的列已存在数据,那么新的类型可能会影响到已存在数据)
ALTER TABLE 表名 MODIFY 列名 列类型; -
修改列名
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型; -
删除列
ALTER TABLE 表名 DROP 列名; -
修改表名称
ALTER TABLE 原表名 RENAME TO 新表名; -
添加列
alter table 表名
add (
列名 列类型,
列名 列类型,
…
列名 列类型
) -
数据类型:
int:整型
double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,(不包含小数点)
decimal:浮点型,在表单钱方面使用该类型,因为不会出现精度缺失问题;
char:固定长度字符串类型; char(255),数据的长度不足指定长度,补足到指定长度!
varchar:可变长度字符串类型;
text(clob):字符串类型;
blob:字节类型;
date:日期类型,格式为:yyyy-MM-dd;
time:时间类型,格式为:hh:mm:ss
timestamp:时间戳类型;
DML
- 插入数据
INSERT INTO 表名(列名1,列名2, …) VALUES(列值1, 列值2, …);
如果在VALUES后给出列值,值的顺序和个数必须与前面指定的列对应,
如果没有给出要插入的列,那么表示插入所有列的 值的顺序,必须与表创建时给出的列的顺序相同。
- 修改数据
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, …WHERE 条件(条件可选的):
运算符:=、!=、<>、>、<、>=、<=、BETWEEN…AND、IN(…)、IS NULL、NOT、OR、AND
注意:NULL之前应该是 IS 并不是= - 删除数据
DELETE FROM 表名 [WHERE 条件];
在数据库中所有的字符串类型,必须使用单引,不能使用双引!
日期类型也要使用单引!
DCL
创建用户:
用户只能在指定的IP地址上登录: CREATE USER 用户名@IP地址 IDENTIFIED BY ‘密码’;
用户可以在任意IP地址上登录:CREATE USER 用户名@’%’ IDENTIFIED BY ‘密码’;
给用户授权
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP地址
例: GRANT ALTER,DROP,INSER,SELECT ON dbName.* TO user1@localhost;
给用户分派指定数据库上的所有权限: GRANT ALL ON 数据库. TO 用户名@IP地址;
撤销授权
REVOKE 权限1, … , 权限n ON 数据库.* FROM 用户名@IP地址;
例如 REVOKE CREATE,ALTER ON dbName.* FROM user1@localhost;
查看权限
SHOW GRANTS FOR 用户名@IP地址
删除用户
DROP USER 用户名@IP地址
注意:一个项目创建一个用户!一个项目对应的数据库只有一个;这个用户只能对这个数据库有权限,其他数据库你就操作不了了!
DQL
- 字段(列)控制
查询指定列: SELECT 列1 , 列2, … FROM 表名; - 去重复查询(重复仅记录一次)
SELECT DISTINCT 列1 , 列2, … 列N FROM 表名; - 列运算( 数量类型的列可以做加、减、乘、除运算)
SELECT sal*1.5 FROM emp - 转换NULL值
有时需要把NULL转换成其它值,例如com+1000时,如果com列存在NULL值,那么NULL+1000还是NULL,而我们这时希望把NULL当前0来运算
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;
其中AS可以省略 - 条件控制
SELECT语句也可以使用WHERE子句来控制记录。
SELECT * FROM emp WHERE sal > 10000 AND comm IS NOT NULL - 模糊查询
当你想查询姓张,并且姓名一共两个字的员工时,这时就可以使用模糊查询
SELECT * FROM table1 WHERE ename LIKE ‘张_’
模糊查询需要使用运算符:LIKE,其中_匹配一个任意字符,注意,只匹配一个字符而不是多个.
例:姓名由3个字组成的员工*/。
SELECT * FROM emp WHERE ename LIKE ‘___’;
其中%匹配0~N个任意字符
SELECT * FROM emp WHERE ename LIKE ‘%阿%’;
姓名以阿开头和结尾的员工也都会查询到
排序(ORDER BY)
- 升序
SELECT * FROM emp ORDER BY sal ASC;
按sal排序,升序. 其中ASC是可以省略的 - 降序
SELECT * FROM emp ORDER BY comm DESC;
按comm排序,降序, 其中DESC不能省略 - 使用多列作为排序条件
SELECT * FROM emp ORDER BY sal ASC, comm DESC;
使用sal升序排,如果sal相同时,使用comm的降序排
聚合函数
- COUNT
SELECT COUNT(*) FROM emp;
计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
云计算emp表中comm列不为NULL的记录的行数 - MAX
SELECT MAX(sal) FROM emp;
查询最高工资 - MIN
SELECT MIN(sal) FROM emp;
查询最低工资 - SUM
SELECT SUM(sal) FROM emp;
查询工资合 - AVG
SELECT AVG(sal) FROM emp;
查询平均工资
分组查询( GROUND BY)
分组查询是把记录使用某一列进行分组,然后查询组信息。
例如:查看所有部门的记录数。
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3
组条件( HAVING表示筛选之后再次筛选),以部门分组,查询每组记录数。条件为记录数大于3
- limit子句(方言)
LIMIT用来限定查询结果的起始行,以及总行数。
例如:查询起始行为第5行,一共查询3行记录
SELECT * FROM emp LIMIT 4, 3;
其中4表示从第5行开始,其中3表示一共查询3行。即第5、6、7行记录。
事务
事务的四大特性(ACID)
- 原子性(Atomicity):事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功,要么全部执行失败。
- 一致性[根本](Consistency):事务执行后,数据库状态与其它业务规则保持一致。如转账业务,无论事务执行成功与否,参与转账的两个账号余额之和应该是不变的。
- 隔离性(Isolation):隔离性是指在并发操作中,不同事务之间应该隔离开来,使每个并发中的事务不会相互干扰。
- 持久性(Durability):一旦事务提交成功,事务中所有的数据操作都必须被持久化到数据库中,即使提交事务后,数据库马上崩溃,在数据库重启时,也必须能保证通过某种机制恢复数据。
事务简述
- 关键字
开启事务:start transaction;
结束事务:commit或rollback;
当语句最后使用出错时,Mysql会自动回滚; - 在jdbc中处理事务,都是通过Connection完成的!同一事务中所有的操作,都在必须使用同一个Connection对象!
- JDBC中的事务
con.setAutoCommit(boolean):设置是否为自动提交事务,如果true(默认值就是true)表示自动提交,也就是每条执行的SQL语句都是一个单独的事务,如果设置false,那么就相当于开启了事务了;con.setAutoCommit(false)表示开启事务!!!
con.commit();表示提交事务.
con.rollback();表示回滚事务.
jdbc处理事务的代码格式:
try {
con.setAutoCommit(false);//开启事务…
….
…
con.commit();//try的最后提交事务
con.close();
} catch() {
con.rollback();//回滚事务
con.close();
}
- 保存点:保存点的作用是允许事务回滚到指定的保存点位置。在事务中设置好保存点,然后回滚时可以选择回滚到指定的保存点,而不是回滚整个事务!注意,回滚到指定保存点并没有结束事务!!!只有回滚了整个事务才算是结束事务了!
- 校验数据库服务器是否支持保存点!
boolean b = con.getMetaData().supportsSavepoints(); - Connection类的设置保存点,以及回滚到指定保存点方法:
设置保存点:Savepoint setSavepoint();
回滚到指定保存点:void rollback(Savepoint)。
事务隔离级别
- SERIALIZABLE(串行化)serializable
- REPEATABLE READ(可重复读)(MySQL默认)repeatable read
防止脏读和不可重复读,不能处理幻读问题; - READ COMMITTED(读已提交数据)(Oracle默认)read committed
防止脏读,没有处理不可重复读,也没有处理幻读; - READ UNCOMMITTED(读未提交数据)read uncommitter
不同事务的隔离级别,实际上是一致性与并发性的一个权衡与折衷。
事务的并发读问题
- 丢失更新:是不可重复读的特殊情况。如果两个事物都读取同一行,然后两个都进行写操作,并提交,第一个事物所做的改变就会丢失。
- 脏读:一个事务读取到另一个事务未提交的更新数据。
- 幻读也叫虚读:一个事务执行两次查询,第二次结果集包含第一次中没有或某些行已经被删除的数据,造成两次结果不一致,只是另一个事务在这两次查询中间插入或删除了数据造成的。
- 不可重复读:一个事务两次读取同一行的数据,结果得到不同状态的结果,中间正好另一个事务更新了该数据,两次结果相异,不可被信任。
不可重复读是读取到了另一事务的更新;幻读是读取到了另一事务的插入
InnoDB的四种事务的隔离级别简述
InnoDB使用不同的锁策略来实现不同的隔离级别。
- 未提交读(Read uncommitted)
定义:就是一个事务读取到其他事务未提交的数据,是级别最低的隔离机制。
缺点:会产生脏读、不可重复读、幻读。 - 提交读(Read committed)
定义:就是一个事务读取到其他事务提交后的数据。Oracle默认隔离级别。
缺点:会产生不可重复读、幻读。 - 可重复读(Repeatable read),这是InnoDB默认的隔离级别
定义:就是一个事务对同一份数据读取到的相同,不在乎其他事务对数据的修改。MySQL默认的隔离级别。
缺点:会产生幻读。 - 串行化(Serializable)
定义:事务串行化执行,隔离级别最高,牺牲了系统的并发性。
缺点:可以解决并发事务的所有问题。但是效率地下,消耗数据库性能,一般不使用。
附
- 快照读: MySQL数据库,InnoDB存储引擎,为了提高并发,使用MVCC机制,在并发事务时,通过读取数据行的历史数据版本,不加锁,来提高并发的一种不加锁一致性读
- RC,RR 两个不同的事务的隔离级别下,快照读有什么不同?
1.事务总能够读取到,自己写入(update /insert /delete)的行记录
2.RC下,快照读总是能读到最新的行数据快照,当然,必须是已提交事务写入的
3.RR下,某个事务首次read记录的时间为T,未来不会读取到T时间之后已提交事务写入的记录,以保证连续相同的read读到相同的结果集
RR下,事务在第一个Read操作时,会建立Read View
RC下,事务在每次Read操作时,都会建立Read View
存储引擎
使用SHOW ENGINES语句查看系统中所有的存储引擎
- InnoDB
如果要提供提交,回滚和崩溃恢复能力的事务安全(ACID兼容)能力,并要求实现并发控 制 - MyISAM
如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较 高的处理效率; - Memory
如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果。 - Archive
如果只有INSERT和SELECT操作,可以选择Archive引擎,Archive存储引擎支持高并发的插 入操作,但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据,如记录日志信 息可以使用Archive引擎。
外键约束(FOREIGN KEY)不能跨引擎使用。MySQL支持多种存储引擎,每一个表都可 以指定一个不同的存储引擎,但是要注意:外键约束是用来保证数据的参照完整性,如果表之间 需要关联外键,却指定了不同的存储引擎,这些表之间是不能创建外键约束的。
TIMESTAMP与DATATIME两者的区别
TIMESTAMP与DATETIME除了存储字节和支持的范围不同外,还有一个最大的区别就是: DATETIME在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关; 而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的,存储时对当前时区进行转换, 检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
使用场景
- 如果对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎,比如bbs中的发帖表,回复表,
- 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.
- “我们数据变化频繁。 不需要入库,同时又频繁的查询和修改我们考虑使用memory, 速度极快.
表锁与行锁
- 哪些存储引擎使用表锁
MySQL,除InnoDB支持行锁外,MySQL的其他存储引擎均只使用表锁,例如:MyISAM, MEMORY, MERGE等。 - 表锁有什么好处
(1)表锁占用内存少很多,行锁的数量与行记录数相关,非常耗内存;
(2)如果业务经常读写表中很大一部分数据时,表锁会更快,因为此时只涉及一个锁,而不是同时管理N多个锁;
(3)如果业务经常使用group by,表锁会更快,原因同(2); - 表锁释放时
如果写锁队列和读锁队列里都有锁,写有更高的优先级,即写锁队列先出列。这么做的原因是,如果有“大查询”,可能会导致写锁被批量“饿死”,而写锁往往释放很快
TRUNCATE和DELETE的区别
- TRUNCATE是DDL语句,会隐式的自动提交。执行TRUNCATE相当于执行以下操作1.commit 2. TRUNCATE 3.commit;
- DELETE可以通过闪回操作查询数据,TRUNCATE不支持闪回查询
- DELETE并不会释放空间,也不会重置高水位线,TRUNCATE则会重置
- DELETE会产生大量的UNDO和REDO,所以一次删除大量数据时需要有足够的UNDO表空间。TRUNCATE只会产生少量REDO和UNDO。一般是由于维护数据字典产生的