SQL
Structure Query Language(结构化查询语言),被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。
SQL 是一种标准化的语言,它允许你在数据库上执行操作,如创建项目,查询内容,更新内容,并删除条目操作(CRUD)。
SQL语言分类
DDL:数据定义语言,定义数据库对象,create、drop、alter
DML:数据操作语言,操作数据库记录,insert into、update、delete
DCL:数据控制语言,控制数据库权限
DQL:数据查询语言,查询数据库记录
MySql DDL
创建数据库
CREATE DATABASE DATABASE_NAME CHARACTER SET utf8;
删除数据库
DROP DATABASE IF EXISTS DATABASE_NAME;
使用数据库
USE DATABASE_NAME;
删除表
DROP TABLE IF EXISTS TABLE_NAME;
创建表
CREATE TABLE TABLE_NAME (
字段1 字段类型(长度) 约束,
字段2 字段类型(长度) 约束,
字段n 字段类型(长度) 约束
);
常见字段类型:
int:java int
bigint:java long
double:java double
decimal:java single/double
char:固定长度字符串,输入字串符长度不够会自动补齐
varchar:范围内自适应长度字符串,范围内输入长度是多少就是多少
text:长字符串型
blob:字节型
date:java.sql.date
datetime:java.util.date
bit:一位
常用约束:
主键约束:primary key,唯一,不能为null
唯一约束:unique key,唯一,可以为null
非空约束:not null
默认约束:default
外键约束:foreign key(colum) REFERENCES ohter_table(colum)
自动增长:auto_increment
增加列
ALTER TABLE TABLE_NAME ADD COLUM_NAME 字段类型 约束;
删除列
ALTER TABLE TABLE_NAME COLUM_NAME;
修改列
ALTER TABLE TABLE_NAME MODIFY COLUM_NAME 字段类型 约束;
MySql DML
插入数据
INSERT INTO TABLE_NAME(colum1,column2,...) VALUES(val1,val2,...)
删除数据
DELETE FROM TABLE_NAME WHERE COLUM = VAL
修改数据
UPDATE TABLE_NAME SET colum1=val1,colum2=val2,...WHERE COLUM = VAL
MySql DQL
SELECT COLUM/内置函数
FROM TABLE_NAME/视图/子查询
WHERE 条件
GROUP BY ...
HAVING ...
ORDER BY colum ASC(升序,默认)/DESC(降序)...
LIMIT start(行号从0开始,=(pageno-1)*size),size
内置函数
CASE
WHEN ... THEN ...
ELSE ...
END
COUNT(colum)
SUM(colum)
AVG(colum)
MIN(colum)
MAX(colum)
条件运算符
=
!=
<
<=
>
>=
is null
is not null
in()
not in()
like ...(%代表任意个字符,_表示一个字符)
and
or
union查询
union会自动去重,union all不会自动去重
union一般在查询结果对应相同,查询条件不同时使用
join查询
表和表之间的关系:一对一,一对多,多对多
SELECT T1.*,T2.*
FROM T1
LEFT JOIN T2
ON T1.colum = T2.colum(消除笛卡尔积)
分类:
内连接 (INNER) JOIN,必须连接的表都有数据才能查出来
左连接 LEFT (OUTER) JOIN,以左边的表为基准
右连接 RIGHT (OUTER) JOIN,以右边的表为基准
注意:
超过三张表禁止JOIN;
JOIN字段数据类型必须一致;
关联字段需要有索引;
数据库优化手段
1、不使用SELECT *
2、不使用LIKE做全文查询
3、union取代or、in
4、不建立外键
5、建立索引
MySql DCL
创建用户
CREATE USER 'USER_NAME'@'主机名' IDENTIFIED BY '密码'
主机名:用户可以在哪个主机上登录,本地可用 localhost,任意远程主机可用通配符 %
授权
GRANT 权限1,权限2... ON 数据库名.表名 TO '用户名'@'主机名'
权限:CREATE、ALTER、SELECT等
撤销授权
REVOKE 权限 ON 数据库名.表名 FROM '用户名'@'主机名'
查看权限
SHOW GRANTS FOR '用户名'@'主机名'
删除用户
DROP USER '用户名'@'主机名'
JDBC连接MySql数据库
导入mysql-connector-java驱动包;
查询操作:
// 动态加载驱动类
Class.forName("com.mysql.jdbc.Driver");
// 获得数据库的连接对象
String url = "";
Connection con = DriverManager.getConnection(url,"账号","密码");
// 获得数据库的操作对象
String sql = "SELECT * FROM TABLE_NAME WHERE colum_id=?";
PrepareStatement ps = con.prepareStatement (sql);
// 设置占位符
ps.setString(1,"700039");
// 执行操作
ResultSet rs = ps.executeQuery();
// 解析结果集
while(rs.next()){
String(字段对应类型) col1 = rs.getString(index从1开始/"col_name");
}
// 依次关闭结果集、操作对象、连接对象
增删改操作:
// 动态加载驱动类
Class.forName("com.mysql.jdbc.Driver");
// 获得数据库的连接对象
String url = "";
Connection con = DriverManager.getConnection(url,"账号","密码");
// 获得数据库的操作对象
String sql = "DELETE FROM TABLE_NAME WHERE colum_id=?";
PreparedStatement ps = con.prepareStatement (sql);
// 设置占位符
ps.setString(1,"700039");
// 执行操作
int count = ps.executeUpdate();
// 依次关闭操作对象、连接对象
Statement和PreparedStatement
都是数据库操作对象
// PreparedStatement对象
String sql = "DELETE FROM TABLE_NAME WHERE colum_id=?";
PreparedStatement ps = con.prepareStatement (sql);
ps.setString(1,"700039");
int count = ps.executeUpdate();
// Statement 对象
String sql = "DELETE FROM TABLE_NAME WHERE colum_id=700039";
Statement statement = con.createStatement ();
int count = statement .executeUpdate(sql);
区别:
1、PreparedStatement需要设置占位符,可以防止sql注入攻击,更加安全
2、Statement每次执行都要编译,PreparedStatement可以预存,不用每次都编译,效率更高
3、PreparedStatement 是 Statement 的子接口
数据库事务
事务:一组要么同时执行成功,要么同时执行失败的sql语句,是数据库操作的基本执行单元。
怎么做?(以下操作保证是同一个连接对象connection)
// 连接对象connection关闭自动提交
connection.setAutoCommit(false);
// 全部执行完后手动提交
connection.commit();
// 中间出现任何问题,回滚
connection.rollback();
事务的特征ASID
actomicity(原子性),一个事务内的操作是一个整体,要么一起成功,要么一起失败;
consistency(一致性),一个事务失败时,所有的数据必须回滚到事务前的状态,保证数据的一致性;
isolation(隔离性),事务之间相互隔离,互不影响
durability(持久性),事务成功后,对数据库的修改是持久的
线程容器
ThreadLoacl threadLoacl = new ThreadLoacl ();
特性:
一个线程能往threadloacl中存放一个元素,这个元素只能由存放的线程取出来。
连接池
创建数据库连接对象很消耗系统资源,使用连接池可以实现连接对象的重复使用。
常见产品:c3p0、durid。
索引
使用索引可以避免全表搜索,提高查询效率。
索引也是一张表,作用相当于图书目录,查询的时候,先从索引中找到符合查询条件的索引值,然后通过保存在索引中的ROWID快速找到表中对应的数据。
索引分类
主键索引
主键字段会自动创建主键索引
唯一索引
唯一字段会自动创建唯一索引
ALTER TABLE_NAME
ADD UNIQUE INDEX INDEX_NAME (colum) USRING BTREE;
普通索引
ALTER TABLE_NAME
ADD INDEX INDEX_NAME (colum) USRING BTREE;
全文索引
ENGINE = InnoDB 支持事务,不支持全文索引
ENGINE = MyIsam 支持全文索引,不支持事务
组合索引
可以把经常查询的字段绑在一起创建索引,组合索引适用最左匹配原则。
ALTER TABLE_NAME
ADD INDEX INDEX_NAME (colum1,colum2) USRING BTREE;
使用索引的优缺点
优点:可以提高查询效率
缺点:降低更新效率,因为更新数据时,还要更新索引
创建索引的原则
经常作为查询条件的列要建立索引;
经常GROUP BY,Order BY的字段要建立索引;
不同值较少的字段不需要创建索引,如性别;
经常更新的列不需要创建索引。
索引失效
使用explain解析sql。
三大范式
第一范式1NF
列的原子性,每列不可分割
第二范式2NF
在1NF基础上,其他列完全依赖于主键
第三范式3NF
在2NF基础上,列不依赖于其它非主键列,列直接依赖于主键
存储过程
创建存储过程
CREATE PROCDURE PROCDURE _NAME (IN|OUT 参数名 数据类型...)
BEGIN
sql
END
IN代表输入数据,OUT代表输出数据(返回值)