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	整数 -2⁷~2⁷-1 / 无符号 0~2⁸-1	1字节
SMALLINT	整数 -2¹⁵~2¹⁵-1 / 无符号 0~2¹⁶-1	2字节
MEDIUMINT	整数 -2²³~2²³-1 / 无符号 0~2²⁴-1	3字节
INT[(M)] 宽度	整数 -2³¹~2³¹-1 / 无符号 0~2³²-1	4字节
BIGINT	整数 -2⁶³~2⁶³-1 / 无符号 0~2⁶⁴-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（待开通）
-----------------------------------------------------------