MySql 学习笔记
来源:互联网 发布:金山数据恢复安卓版 编辑:程序博客网 时间:2024/06/01 08:02
一、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);
}
}
- 【MySQL】MySQL学习笔记
- 【mysql】mysql学习笔记
- Mysql学习笔记 --- mysql数据类型
- MySQL学习笔记--MySQL编程
- JBoss-MySql学习笔记
- mysql学习笔记
- Mysql学习笔记(1)
- Mysql学习笔记
- MySQL学习笔记
- Tomcat Mysql 。。。学习笔记
- mysql学习笔记
- mysql DB(学习笔记)
- MySQL 学习笔记一
- MySQL学习笔记
- mysql学习笔记
- mysql学习笔记
- MySQL学习笔记
- MySql学习笔记
- 【caffe-windows】 caffe-master 之 matlab接口配置
- 左神的书——《程序员代码面试指南》之打印两个有序链表的公共部分 c++实现
- 课程实践:大奖赛计分
- 部分软件在ubuntu14.04下的安装参考
- Fragment中含有WebView,回退按键交给WebView的处理
- MySql 学习笔记
- 个人整理的关于UITableViewCell的常用属性的知识,如有雷同,纯属巧合
- 利用openstack建设适合中小型互联网企业的私有云(五)
- c++与C#的区别
- Download Linux Kernel
- Scala练习
- SQL server in 用法
- 《温故而知新》系列之自定义View基本知识
- 有关二维数组面试---根据二维数组的特点