MySql 学习笔记
一、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);
}
}
个人网址:http://wxson.cn(待开通)
-----------------------------------------------------------