MySQL概述
- 数据库相关概念
- 数据库(DataBase,DB):存储数据的仓库,数据是有组织的进行存储
- 数据库管理系统(DataBase Management System,DBMS):操纵和管理数据库的大型软件
- SQL(Structured Query Language,结构化查询语言):操作关系型数据库的编程语言,定义操作所有关系型数据库的统一标准
- MySQL数据库
关系型数据库(RDBMS):建立在关系模型基础上,由多张相互连接的二维表组成的数据库。使用表存储数据,格式统一,便于维护;使用SQL语言操作,标准统一,使用方便
SQL
SQL通用语法
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释:当行注释- -或#;多行注释/* */
SQL分类
分类 | 说明 |
---|---|
DDL(Data Definition Language) | 数据定义语言,用来定义数据库对象(数据库、表、字段) |
DML(Data Manipulation Language) | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL(Data Query Language) | 数据查询语言,用来查询数据库中表的记录 |
DCL (Data Control Language) | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
DDL
数据库操作
1.查询:show databases:查询所有数据库 select database():查询当前数据库
2.创建:create database[if not exists] 数据库名[default charset 字符集][ 排序规则collate];
3.删除:drop database[if exists]数据库名;
4.使用:use 数据库名;
表操作
1.查询
查询当前数据库所有表 show tables;
查询表结构 desc 表名;
查询指定表的建表语句 show create table 表名;
2.创建
create table 表名(
字段1 字段1类型,
字段2 字段2类型,
字段n 字段n类型
);
数据类型
- 数值类型
tinyint ( 1 byte) 、smallint (2 bytes)、 mediumint (3 bytes )、int或integer ( 4 bytes)、 bigint (8 bytes)、 float (4 bytes)、 double(8 bytes)、 decimal(范围依赖于M(精度,数值长度)和D(标度,小数位数)的值)
- 字符串类型
类型(范围) | 描述 |
---|---|
char(0-255bytes) | 定长字符串 |
varchar(0-65535bytes) | 变长字符串 |
tinyblob(0-255bytes) | 不超过255个字符的二进制数据 |
tinytext(0-255bytes) | 短文本字符串 |
blob(0-65535bytes) | 二进制形式的长文本数据 |
text(0-65535bytes) | 长文本数据 |
mediumblob(0-16777215bytes) | 二进制形式的中等长度文本数据 |
mediumtext(0-16777215bytes) | 中等长度文本数据 |
longblob(0-4294967295bytes) | 二进制形式的极大文本数据 |
longtext(0-4294967295bytes) | 极大文本数据 |
- 日期时间类型
类型 | 格式 | 描述 |
---|---|---|
date | YYYY-MM-DD | 日期值 |
time | HH:MM:SS | 时间值或持续时间 |
year | YYYY | 年份值 |
datetime | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
timestamp | YYYY-MM-DD HH:MM:SS | 混合日期和时间值、时间戳 |
3.修改
添加字段:alter table 表名 add 字段名 类型(长度);
修改数据类型:alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型(长度);
删除字段 :alter table 表名 drop 字段名;
修改表名:alter table 表名 rename to 新表名;
4.删除
删除表:drop table [if exists] 表名;
删除指定表,并重新创建该表,将数据删除,只剩下表结构:truncate table 表名;
DML
- 添加数据INSERT
-
给指定字段添加数据 :INSERT INTO 表名(字段名1,字段名2,…)VALUES(值1,值2,…);
-
给全部字段添加数据 :INSERT INTO表名VALUES(值1,值2,…);
(值1,值2,…),(值1,值2,…),(值1,值2,…)可以实现批量添加数据
字符串和日期数据类型应该包含在引号中
insert into user values (1,19,'张三','2005-5-5'),(2,20,'李四','2004-4-4');
- 修改数据UPDATE:UPDATE 表名 SET 字段名1=值1,字段名2=值2,…WHERE 条件;
如果没有 WHERE条件 ,表示修改整张表的所有数据
update user set birthdate = '2005-6-6' where id = 1;
- 删除数据delete:DELETE FROM 表名 WHERE 条件;
DELETE语句不能删除某一个字段的值,应使用UPDATE语句将该字段设为NULL。
delete from user where id = 2;
DQL
- 查询多个字段:SELECT 字段1,字段2,字段3… FROM 表名;
- 查询所有字段: SELECT * FROM 表名;
- 设置别名 :SELECT 字段1 AS 别名1,字段2 AS 别名2… FROM 表名;
- 去除重复:SELECT DISTINCT 字段列表 FROM 表名;
- 条件查询 :SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件
- 比较运算符:>,>=,<,<=,=,!=(<>),BETWEEN…AND…(包含边界值),IN(),LIKE
‘占位符’( _ 匹配单个字符,%匹配任意个字符),IS NULL- 逻辑运算符:AND或&&,OR或||,NOT或!
- 分组查询:SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段名 HAVING 分组后过滤条件
聚合函数:SELECT 聚合函数(字段列表)FROM 表名;null不参与聚合函数运算
常见聚合函数:count、max、min、avg、sum
分组之后,查询的字段只能为聚合函数或分组字段
- 排序查询:SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
ASC为升序(默认值), DESC为降序;
如果是多字段排序,当第一个字段相通时,根据第二个字段排序
- 分页查询:SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
DCL
运维
-
管理用户
创建用户:CREAT USER ‘用户名’@‘主机名’IDETIFIED BY ‘密码’;
修改用户密码:ALTER USER ‘用户名’@‘主机名’IDENTIFIED WITH mysql_native_passward BY ‘密码’;
删除用户:DROP USER ‘用户名’@‘主机名’; -
权限控制
常用权限:ALL (所有权限),SELECT,INSERT,UPDATE,DELECT,ALTER,DROP,CREATE
授予权限:GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
撤销权限:REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;
约束
约束是作用于表中字段上的规则,用于限制存储在表中的数据
关键字 | 描述 |
---|---|
NOT NULL | 非空约束:限制该字段的数据不能为null |
UNIQUE | 唯一约束:保证该字段所有数据都是不重复的 |
PRIMARY KEY | 主键约束:主键是一行数据的唯一标识,要求非空且唯一 |
DEFAULT | 默认约束:保存数据时,如果未指定该字段的值,则采用默认值 |
CHECK | 检查约束:保证字段值满足某一条件 |
FOREIGN KEY | 外键约束:让两张表的数据之间建立联系,保证数据的一致性和完整性 |
多表查询
多表关系
- 一对多:在多的一方建立外键,指向一的一方的主键
- 多对多:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
create table course(
id int auto_increment primary key ,
name varchar(10)
);
insert into course values (null,'Java'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');
create table student(
id int auto_increment primary key ,
name varchar(10),
no varchar(10)
);
insert into student values (null,'aaa','101'),(null,'bbb','102'),(null,'ccc','103'),(null,'ddd','104');
create table student_course(
id int auto_increment primary key ,
studentid int not null ,
courseid int not null ,
constraint fk_courseid foreign key (courseid) references course(id),
constraint fk_studentid foreign key (studentid) references student(id)
);
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);
- 一对一:用于单表拆分,在一方加入外键,关联另一方的主键,并设置外键是唯一的
create table tb_user(
id int auto_increment primary key ,
name varchar(10),
age int,
gender char(1),
phone char(11)
);
insert into tb_user values (null,'aaa',22,'1','01234567899'),(null,'bbb',22,'2','11234567899');
create table tb_user_edu(
id int auto_increment primary key ,
degree varchar(20),
major varchar(50),
userid int unique ,
constraint fk_userid foreign key (userid) references tb_user(id)
);
insert into tb_user_edu values (null,'本科','数学',1),(null,'硕士','物理',2);
- 内连接:查询两表交集部分数据
隐式内连接:SELECT 字段列表 FROM 表1,表2 WHERE 条件…;
显式内连接:SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件…; - 外连接:查询左或右表所有数据,以及两表交集部分数据
左外连接:SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;
右外连接:SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…; - 自连接:当前表与自身的连接查询,自连接必须使用表别名,可以是内连接或外连接,两表相同,别名不同
- 联合查询:两字段列表的数量和字段类型需要保持一致;UNION ALL 会将全部数据直接合并在一起,UNION会对合并后的数据去重
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
- 子查询:SELECT 字段列表 FROM 表1 WHERE column1 = (SELECT column1 From 表2);
外部语句可以是INSERT/UPDATE/DELETE/SELECT,条件内部语句含SELECT
事务
事务是一组操作的集合。
步骤:
-
开启事务: START TRANSACTION 或 BEGIN;
(或将事务提交方式由自动提交改为手动提交 : SET @@autocommit = 0;) -
如果执行成功,提交事务:COMMIT;
-
如果执行失败,回滚事务:ROLLBACK;
事务的四大特性:
- 原子性(Atomicity):事务是不可分割的最小单位,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须保证所有数据都保持一致状态
- 隔离性(Isolation):数据库提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下独立运行
- 持久性(Durability ):事务一旦提交或回滚,它对数据库中的数据改变就是永久的
并发事务问题
问题 | 描述 |
---|---|
脏读 | 读到另一个事务没提交的数据 |
不可重复读 | 两次读取的数据不同 |
幻读 | 查询时没有对应读数据行,插入数据时数据行已经存在 |
隔离级别由低到高:Read uncommitted,Read committed(解决了脏读问题),Repeatable Read(默认)(解决了不可重复读问题),Serializable串行化(解决了幻读问题)
查看事务隔离级别:SELECT @@TRANSACTION_ISLOTION;
设置事务隔离级别:SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {Read uncommitted|Read committed|Repeatable Read|Serializable};
JDBC
JDBC(Java DataBase Connectivity)是使用Java语言操作关系型数据库的一套接口,面向接口编程
使用JDBC编程,真正执行的代码是各数据库驱动jar包中的实现类
步骤
导入驱动jar包
- 注册驱动
- 获取连接
- 定义SQL语句
- 获取执行SQL对象
- 执行SQL
- 处理返回结果
- 释放资源
import java.sql.*;
import static java.sql.DriverManager.getConnection;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://localhost:3306/test ";
String username = "root";
String passward = "123456";
Connection con = DriverManager.getConnection(url,username,passward);
//3.定义sql
String sql = "select * from student";
//4.获取执行sql的对象Statement
Statement stmt = con.createStatement();
//5.执行sql
ResultSet rs = stmt.executeQuery(sql);//返回值为受影响的函数
//6.处理结果
Integer id = null;
String name = null;
String no = null;
while (rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
no = rs.getString("no");
System.out.println(id+"\t"+name+"\t"+no);
}
//7.释放资源
stmt.close();
con.close();
}
}
JDBC API
- DriverManager(驱动管理类)
- 注册驱动
- 获取数据库连接 static Connection getConnection(String url,String user,String password);
- Connection
- 获取执行SQL对象 Statement creatStatement()
- 管理事务
开启事务: setAutoCommit(boolean autoCommit):true 为自动提交事务;false为手动提交事务,即开启事务
提交事务:commit()
回滚事务:rollback(),该语句放在catch里执行
- Statement
执行SQL语句
方法 | 作用 | 返回值 |
---|---|---|
int executeUpdate(sql) | 执行DML DDL语句 | DML语句影响的行数,DDL执行成功也可能返回0 |
ResultSet executeQuery(sql) | 执行DQL语句 | ResultSet结果集对象 |
- ResultSet
结果集对象
方法 | 说明 |
---|---|
boolean next() | 将光标向下移动一行,返回是否为有效行 |
xxx getXxx(参数) | xxx表示数据类型,参数可以是列的编号或名称,编号从1开始 |
- PreparedStatement
1.预编译SQL语句:当使用PreparedStatement时,数据库系统会对SQL语句进行预编译处理(如果JDBC驱动支持的话)。预编译的SQL查询语句能够在将来的查询中重用,从而提高查询速度。
2.防止SQL注入攻击:通过PreparedStatement,开发者可以传递参数到SQL语句中,而不是直接将参数拼接到SQL字符串中。这样可以有效防止SQL注入攻击,提高应用程序的安全性。
SQL注入攻击
import java.sql.*;
import static java.sql.DriverManager.getConnection;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test ";
String username = "root";
String passward = "123456";
Connection con = DriverManager.getConnection(url,username,passward);
//接受用户输入
String name = "aaa";
String no = "'or'1'='1";
String sql = "select * from student where name = '"+name+"'and no = '"+no+"'";
//name = 'aaa'and no = ''or'1'='1' 登录成功
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
stmt.close();
con.close();
}
}
使用PreparedStatement
import java.sql.*;
import static java.sql.DriverManager.getConnection;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test ";
String username = "root";
String passward = "123456";
Connection con = DriverManager.getConnection(url,username,passward);
//接受用户输入
String name = "aaa";
String no = "' or '1' = '1";
//定义sql,name no 用占位符?替代
String sql = "select * from student where name =?and no =?";
//获取执行sql的对象PreparedStatement,要传递sql
PreparedStatement pstmt = con.prepareStatement(sql);
//设置?的值
pstmt.setString(1,name);
pstmt.setString(2,no);//对传入的值进行转义,将单引号转义,该密码不能登陆成功
//执行sql,不需要传递sql
ResultSet rs = pstmt.executeQuery();
if (rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
pstmt.close();
con.close();
}
}