一、SQL语句概述
1、概念
SQL（Structured Query Language）结构化查询语言。是数据库使用的标准语言。
2、分类
数据定义语言（DDL）CREATE，ALTER，DROP等命令
数据操作语言（DML）INSERT，DELETE，UPDATE等命令
数据查询语言（DQL）SELECT等命令
数据控制语言（DCL）GRANT，REVOKE等命令
二、DDL（数据定义语言）
1、数据库
（1）显示所有数据库
SHOW DATABASES;
（2）切换数据
USE `数据名称`;
（3）创建数据库
CREATEDATABASE [IF NOT EXISTS] `数据名称` [CHARSET=UTF8];
（4）删除数据库
DROPDATABASE [IF EXISTS] `数据名称`;
（5）修改数据库编码
ALTERDATABASE `数据名称` CHARACTERSET UTF8;
2、数据表
2.1 创建表
（1）基本语法
CREATETABLE [IF NOT EXISTS] `表名称` (
`列名1` 列类型 [属性] [索引] [注释],
`列名2` 列类型 [属性] [索引] [注释],
`列名3` 列类型 [属性] [索引] [注释],
……
`列名N` 列类型 [属性] [索引] [注释]
)[表类型][表字符集][注释];
（2）列的数据类型
A、数值
|
类型 |
取值范围和说明 |
存储需求 |
|
TINYINT |
整数 -27~27-1 / 无符号 0~28-1 |
1字节 |
|
SMALLINT |
整数 -215~215-1 / 无符号 0~216-1 |
2字节 |
|
MEDIUMINT |
整数 -223~223-1 / 无符号 0~224-1 |
3字节 |
|
INT[(M)] 宽度 |
整数 -231~231-1 / 无符号 0~232-1 |
4字节 |
|
BIGINT |
整数 -263~263-1 / 无符号 0~264-1 |
8字节 |
|
FLOAT |
浮点数 |
4字节 |
|
DOUBLE |
浮点数 |
8字节 |
|
DECIMAL(M,D) |
浮点数 M 整数+小数长度 D 小数长度 |
M字节 |
B、字符串
|
类型 |
取值范围和说明 |
存储需求 |
|
CHAR(M) 长度 |
固定长度字符串 0 <= M <= 255 |
M字节 |
|
VARCHAR(M) 长度 |
可变字符串 0 <= M <= 65535 |
M+1或个2字节 |
|
TINYTEXT |
微型字符串 |
255字节 |
|
TEXT |
字符串 |
65535字节 |
C、日期和时间
|
类型 |
取值范围和说明 |
|
DATE |
日期 yyyy-MM-dd 1000-01-01 – 9999-12-31 |
|
TIME |
时间 hh:mm:ss -838:59:59 – 838:59:59 |
|
DATETIME |
日期和时间 yyyy-MM-dd hh:mm:ss 1000-01-01 00:00:00 – 9999-12-31 23:59：59 |
|
TIMESTAMP |
时间戳 |
|
YEAR |
年份 1901-2155 |
D、NULL（空）
不能应用于列的类型
（3）属性
A.UNSIGNED 无符号的
B.ZEROFILL 用0补齐数字（默认启用UNSIGNED）
C. AUTO_INCREMENT自增加一，用于主键（设置主键 PRIMARY KEY），整数类型
可以设置初始值和步长（默认 1,1）
创建表：AUTO_INCREMENT= 100;（写在表后面）
修改表：ALTERTABLE `表名` AUTO_INCREMENT = 200;
SET@@AUTO_INCREMENT_INCREMENT = 5; -- 影响所有的自动
自动增长如何归0（DELETE FROM `表名` 自增不能归零，用 TRUNCATE `表名` 归零）
D. NOTNULL（不允许为空）
E.NULL（允许为空）
F.DEFAULT 设置默认值
（4）索引
A.分类
主键（PRIMARYKEY），唯一（UNIQUE），常规（INDEX），全文（FULLTEXT）
B.创建主键索引
列名 … PRIMARYKEY,
#或 PRIMARY KEY(`列名`)
C.创建唯一索引
列名 … UNIQUE,
#或 UNIQUE KEY `索引名`(`列名`)
D.创建常规索引
INDEX/KEY`索引名`(`列名1`,`列名2`)
E.创建全文索引
FULLTEXT`索引名`(‘列名’)
注意：只能用于MYISAM 类型的数据表中
只能用于 CHAR，VARCHAR，TEXT 等字符数据类型中
适合大型数据集
MATCH(`列名`) AGAINST(‘关键字’)
F.修改表创建索引
ALTERTABLE 表名 ADD 索引类型 `索引名字`(数据列名)
G.删除索引
DROPINDEX 索引名 ON 表名;
ALTERTABLE 表名 DROP INDEX 索引名;
ALTERTABLE 表名 DROP PRIMARY KEY;
H.查看索引
SHOWINDEX/KEYS FROM 表名;
（5）注释
列上加注释COMMENT ‘列注释’
表上加注释COMMENT=‘列注释’
代码注释 单行 #
多行 /* */
（6）表类型
A. ENGINE=MYISAM或INNODB
|
名称 |
MYISAM |
INNODB |
|
事务处理 |
不支持 |
支持 |
|
数据行锁定 |
不支持 |
支持 |
|
外键约束 |
不支持 |
支持 |
|
全文索引 |
支持 |
不支持 |
|
表空间大小 |
较小 |
较大 |
（7）字符集
A. 库可以设置
CHARACTERSET 字符集
COLLATE校验规则（用于比较规则）
B. 列可以设置
CHARACTERSET 字符集
C. 表可以设置
[DEFAULT]CHARSET = 字符集;
D. 设置窗口字符集
SETNAMES 字符集;
2.2 查看数据库所有表名称
SHOWTABLES;
2.3 查看指定表的创建语句
SHOWCREATE TABLE `表名称`;
2.4 查看表结构
DESC `表名称`;
2.5 删除表
DROPTABLE [IF EXISTS] `表名称`;
2.6 修改表
（0）前缀
ALTERTABLE `表名称`
（1）添加列
ADD `列名` 列类型 [属性];
（2）删除列
前缀 + DROP `列名`;
（3）修改列名及其内容
前缀 + CHANGE`旧列名` `新列名` 列类型 [属性];
（4）修改列内容
前缀 + MODIFY`列名` 列类型 [属性];
（5）修改列主外键
前缀 + ADDCONSTRAINT `主键名` PRIMARY KEY(`列名`);
前缀 + DROPPRIMARY KEY;
前缀 + ADDCONSTRAINT `外键名` FOREIGN KEY(`列名`) REFERENCES `表名`(`列名`);
前缀 + DROPFOREIGN KEY `外键名`;
（6）修改表名称
前缀 + RENAMEAS `新表名`;
三、DML（数据操作语言）
1、插入
1.1 插入一行
INSERTINTO `表名`[(`字段1`,`字段2`,`字段3`,…`字段N`)]
VALUES(值1, 值2, 值3,…, 值N);
1.2 插入多行
INSERTINTO `表名`[(`字段1`,`字段2`,`字段3`,…`字段N`)] VALUES
(值1, 值2, 值3,…, 值N)，
……
(值1, 值2, 值3,…, 值N);
值 可以是 NULL 或 DEFAULT
2、更新
2.1 基本语法
UPDATE `表名` SET `字段1` = 值1[,`字段2` = 值2, …] [WHERE 条件];
如果不指定条件则全部更新！
2.2 运算符
|
运算符 |
含义 |
范例 |
结果或说明 |
|
= |
等于 |
5=6 |
FALSE |
|
<> 或 != |
不等于 |
5!=6 |
TRUE |
|
> |
大于 |
5>6 |
FALSE |
|
< |
小于 |
5<6 |
TRUE |
|
>= |
大于等于 |
5>=6 |
FALSE |
|
<= |
小于等于 |
5<=6 |
TRUE |
|
BETWEEN … AND … |
在某个范围之间 |
列名 BETWEEN 5 AND 10 |
如果列的值在5和10之间为TRUE 否者为FALSE |
|
IS NULL |
是否为空 |
列名 IS NULL |
空为真，非空为假 |
|
IS NOT NULL |
是否为不空 |
列名 IS NOT NULL |
空为假，非空为真 |
|
LIKE |
模式匹配 |
列名 LIKE ‘_张%’ |
特殊符号 _(1个字符) %(0个或多个字符) ESCAPE ‘\’转义字符 |
|
IN |
等于某个值则为真 |
列名 IN (v1, v2, v3, ……) |
|
AND 或 %% |
并且 |
同时为真，才为真 |
|
OR 或 || |
或者 |
一个为真，就是真 |
|
XOR 或 ^ |
异或 |
一真一假，才为真 |
|
NOT 或 ! |
非 |
真为假，假为真 |
3、删除
DELETEFROM `表名` [WHERE 条件]
如果不指定条件则全部删除！
TRUNCATE`表名`; # 截断表 完全清空重新设置 AUTO_INCREMENT 不会对事务有影响
4、事务
4.1 启用事务
SETAUTOCOMMIT = 0; # 关闭自动提交模式（启动事务）
SETAUTOCOMMIT = 1; # 开启自动提交模式（默认）
4.2 事务编写过程
SETAUTOCOMMIT = 0;
STRATTRANSACTION;
ROLLBACK;# 回滚
COMMIT;# 提交
四、DQL（数据查询语言）
1、基本语法
SELECT[ALL|DISTINCT]
{*|`表名`.*|`表名`.`列1`,`表名`.`列2` [AS] `别名`, ……}
FROM `表名1` [[AS] `别名1`]
[LEFT[OUTTER] | RIGHT [OUTTER] | INNER JOIN `表名2` ON 条件]
[WHERE条件]
[GROUPBY `列1`[,`列2`]……]
[HAVING条件]
[ORDERBY `列1` ASC | DESC [,`列2` ASC | DESC]……]
[LIMITOFFSET, ROW_COUNT]
2、扩展用法
2.1 查询变量和系统变量
SELECT@@AUTO_INCREMENT_INCREMENT;
2.2 查询函数计算结果
SELECTVERSION();
2.3 查询出来的列可以参与计算后再显示
SELECT`classhour` AS `旧课时`, `classhour`+10AS `新课时` FROM `subject`
3、系统函数
|
CONCAT(‘年级’,列名) |
字符串连接 |
|
NOW() |
当前时间用于TIMESTAMP不能用于DATETIME |
|
COUNT(*),COUNT(`列名`) |
记录总和数 |
|
SUM() |
总和 |
|
AVG() |
平均值 |
|
MAX() |
最大值 |
|
MIN() |
最小值 |
4、左外连接、右外连接、内连接
SELECTt1.id, t1.name FROM t1 LEFT OUTTER JOIN t2 ON t1.id = t2.id;
SELECTt1.id, t1.name FROM t1 RIGHT OUTTER JOIN t2 ON t1.id = t2.id;
SELECTt1.id, t1.name FROM t1 INNER JOIN t2 ON t1.id = t2.id;
5、分组及分组后条件筛选
GROUPBY 列名 # 按列名分组
HAVING条件 # 分组后再筛选
6、排序
ORDERBY 列名 ASC # 升序
ORDERBY 列名 DESC # 降序
7、LIMIT M,N
返回从第M+1行开始的N条记录
M=(page-1)*N, N
8、子查询
在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句
五、数据库备份和恢复
1、使用MYSQLDUMP备份数据库
mysqldump-h 主机名 -u 用户名 -p 数据库名 [table1 table2 table3] >path/filename.sql
2、使用SOURCE恢复数据库
2.1 注意在 MySQL 命令行里
2.2 先选择数据库 USE dbname;
2.3 恢复数据 SOURCE /path/db_name.sql;
3、使用MySQL客户端恢复数据库
mysql-uroot -p dbname < /path/db_name.sql;
六、JDBC 连接数据库
1、StudentDao 接口
importjava.util.List;
publicinterface StudentDao {
List<Student> findAll();
Student findById(String id);
int insert(Student s);
int update(Student s);
int delete(String id);
}
2、BaseDao 类
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.sql.Timestamp;
publicclass BaseDao {
public static final String DRIVER ="com.mysql.jdbc.Driver";
public static final String URL ="jdbc:mysql://localhost:3306/t74";
public static final String USER ="root";
public static final String PASSWORD ="mysql";
public Connection getConnection(){
Connection conn = null;
try {
Class.forName(DRIVER);
conn =DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public void closeResource(Connectionconn,Statement stmt,ResultSet rs){
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public int executeUpdate(String sql,Object[]objs){
int row = -1;
Connection conn = null;
PreparedStatement ps = null;
try{
conn = getConnection();
ps = conn.prepareStatement(sql);
if(objs!=null){
for(inti=0;i<objs.length;i++){
if(objs[i] instanceofjava.util.Date){
objs[i]= new Timestamp(((java.util.Date)objs[i]).getTime());
}
ps.setObject(i+1, objs[i]);
}
}
row = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeResource(conn, ps, null);
}
return row;
}
}
3、StudentDaoImpl 类
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.sql.Timestamp;
importjava.util.ArrayList;
importjava.util.List;
publicclass StudentDaoImpl extends BaseDao implements StudentDao {
public List<Student> findAll() {
List<Student> list = newArrayList<Student>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "select * fromstudent";
try{
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
Student s = new Student();
s.setStuId(rs.getString(1));
s.setStuName(rs.getString(2));
s.setAge(rs.getInt(3));
s.setSex(rs.getInt(4));
s.setBirthday(rs.getTimestamp(5));
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeResource(conn, stmt, rs);
}
return list;
}
public Student findById(String id) {
Student s = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select * from studentwhere stuid = ?";
try{
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, id);
rs = ps.executeQuery();
if(rs.next()){
s = new Student();
s.setStuId(rs.getString(1));
s.setStuName(rs.getString(2));
s.setAge(rs.getInt(3));
s.setSex(rs.getInt(4));
s.setBirthday(rs.getTimestamp(5));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeResource(conn, ps, rs);
}
return s;
}
public int insert(Student s) {
String sql = "insert into studentvalues(?,?,?,?,?)";
Object[] objs ={s.getStuId(),s.getStuName(),s.getAge(),s.getSex(),s.getBirthday()};
return super.executeUpdate(sql, objs);
}
public int update(Student s) {
String sql = "update student setstuname=?,age=?,sex=?,birthday=? where stuid=?";
Object[] objs ={s.getStuName(),s.getAge(),s.getSex(),s.getBirthday(),s.getStuId()};
return super.executeUpdate(sql, objs);
}
public int delete(String id) {
String sql = "delete from studentwhere stuid = ?";
Object[] objs = {id};
return super.executeUpdate(sql, objs);
}
}