MySQL技术总结
常用的命令:
- show databases; 查看所有数据库
- use 数据库名; 切换到指定数据库
- create database 数据库名; 创建指定数据库
- drop database 数据库名; 删除指定数据库
- show tables 在某个数据库中,查看其中的所有表
切换数据库
只需双击对应的数据库或使用指令 use 数据库名;
创建数据表
在展开后的数据库中,在表的选项上右键新建表或create table 表名;
保存时输入表名 每张表需要一个编号"id"列,通常设置为主键,目的是为了区分每条记录。主键列中的数据不能重 复,通常还需将主键列设置为自增列。 由于mysql中大小写不敏感,字段名使用全部小写字母,多个单词用_隔开 数据类型和所占长度根据实际情况选择 如果某列数据必须要填写,将"不是null"勾选 如果某个字段有默认值,可以在设计表的时候设置,字符串用单引号引起来
SQL
Structrued Query Language 结构化查询语言 用于操作关系型数据库的一门语言。可用来创建、维护数据库和数据。
操作数据表
创建数据表
create table 表名(
字段名1 数据类型 [字段特征],
字段名2 数据类型 [字段特征],
...
字段名n 数据类型 [字段特征]
)
修改数据表
修改表时,要保证不影响现有数据 对表重命名 添加新字段
-- mysql中的注释
alter table 旧表名 rename to 新表名;
修改字段 删除字段
添加约束
添加唯一约束 添加主键约束 添加默认值约束 添加外键约束 添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表,最好在创建表的时候就 设计好约束。
alter table 表名 add column 字段名1 数据类型 [字段特征];
alter table 表名 change 旧字段名 新字段名 数据类型 [字段特征];
alter table 表名 drop 字段名;
alter table 表名 add unique(字段名);
alter table 表名 add primary key(字段名);
alter table 表名 alter 字段名 set default '默认值'; alert table 从表表名 add foreign key(从表外键字段) references 主表表名(主表主键字
建表的同时添加约束
- 创建游戏数据库gamedb
create database gamedb;
-- 切换到gamedb
use gamedb;
-- 创建游戏人物表hero
create table hero(
-- 编号id主键 非空 自增
id int not null primary key auto_increment comment '人物编号',
-- 姓名 唯一 非空
name varchar(20) unique not null,
-- 性别 默认男 非空
sex char(1) not null default '男',
-- 价格 非空
price int not null ,
-- 发布时间 可以为空
make_time date
)
删除数据表
drop table 表名
操作数据CURD
数据的操作,是指数据的增加create,修改update,查询read和删除delete。 简称CURD。
数据添加insert
数据添加时,都是整行添加。不能只给一个字段添加数据。 如果只给某个字段添加数据,实际是修改。
给所有字段赋值
insert into 表名 values('值1','值2'...)
给指定字段赋值
insert into 表名(字段1,字段2...) values('值1','值2'...)
批量添加
可以用一个insert into语句添加多条记录
insert into 表名[(字段1,字段2...)] values
('值1','值2'...),
('值1','值2'...),
...
('值1','值2'...)
根据条件修改(where子句)
update 表名 set 字段 = '值',字段 = '值'... where 条件;
指定范围
- 使用>、、>=、表示范围,使用and、&&、or、||将多个条件关联
- 使用"字段 between A and B"表示字段在[A,B]范围内
- 使用!=或<>表示不等于
指定集合
某个字段的值在某个集合中时: 字段 in ('值1','值2'...)
某个字段的值不在某个集合中时: 字段 not in ('值1','值2'...)
模糊查询
- %表示未知长度字符串
- _表示一个字符
-- 字段 like '%琳%'
-- 带有'琳'字
-- 字段 like '张%'
-- 姓'张'
-- 字段 like '%儿'
-- 以'儿'结尾
-- 字段 like '__'
-- 两个字
-- 字段 like '%的_'
-- 倒数第二个字为'的'
数据删除delete
删除数据是删除一条或多条记录。
删除所有
delete from 表名;
-- 或
truncate table 表名;
- delete会保留自增列删除前的值,删除后再添加时,自动从删除前的值开始自增
- truncate会重置自增列的值。删除所有数据时效率更高
- 如果要删除主从关系且设置了外键的表中的数据。从表中如果有数据,不能直接删除主表中相关联 的数据,先删除从表数据,再删除主表数据。
数据查询select
查询所有字段
select * from 表名;
查询指定字段
select 字段名1,字段名2... from 表名;
实际开发中,很少使用 * 查询,因为 * 查询的效率远低于查询指定字段。
字段重命名
select 字段1 as '自定义名称',字段1 '自定义名称'... from 表名;
查询指定条数
-- 查询前N条记录
select * from 表名 limit N;
-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;
-- 每页显示size条记录,第page页
select * from 表名 limit (page-1)*size,size;
实际业务对应SQL操作
登录
登录通常是使用用户名和密码查询用户表
select * from 用户表 where 用户名 = ? and 密码 = ?
如果没有查询到数据,即null,说明用户不存在或输入有误。 如果能查询到数据,返回查询到的内容。
注册
注册通常是使用用户名和密码添加一条记录 注册时一般还会验证要注册的用户是否存在。
insert into 用户表 values(注册信息1,注册信息2...)
充值
充值、消费都是将数据进行更新
update 用户表 set 余额 = 余额+/-值 where 主键 = 值
create table userinfo(
用户名 电话 地址 是否删除 0-未删除 1-已删除
小张 123123123 水电费水电费 0
小李 123123123 水电费水电费 0
小王 123123123 水电费水电费 1
删除
逻辑删除
不删除数据,只是不显示数据。
查询所有未删除的数据
假设要删除小王,只需将是否删除的值改为1
物理删除
真实删除
userid int not null primary key auto_increment,
username varchar(20) not null,
password varchar(20) not null,
balance double not null
)
-- 注册
-- 检测要注册的用户名是否存在
select * from userinfo where username = 'admin';
-- 如果上一句没有查询到数据,才进行添加
insert into userinfo values(null,'admin','123123',0);
-- 登录
select * from userinfo where username ='amdmin' and password='123123';
-- 充值
-- 获取注册时自动生成的id
update userinfo set balance = balance + 100 where userid = 1
查询所有未删除的数据
select * from 用户表 where 是否删除=0
物理删除
真实删除
delete from 用户表 where 条件;
连接查询
交叉连接、笛卡尔积
将两张表中的数据两两组合,得到的结果就是交叉连接的结果,也称为笛卡尔积。
集合A:{a,b}
集合B:{1,2,3}
集合A x 集合B = {a1,a2,a3,b1,b2,b3}
select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;
内连接
在交叉连接的基础上,筛选出关联的数据。
select * from 表1,表2 where 表1.字段 = 表2.字段;
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;
-- 如查询所有图书详情和类型名
select * from 图书详情表 t1,图书类型表 t2 where t1.类型编号 = t2.类型编号;
select * from 图书详情表 t1 inner join 图书类型表 t2 on t1.类型编号 = t2.类型编号;
-
通常是通过主表的主键字段关联从表的外键字段
-
如果两张表中关联的字段名一致,一定要通过"表名.字段名"进行区分,通常还会给表重命名
-
如果使用inner join,带条件时需要加入where子句;如果使用,隔开多个表,带条件时需要使用and拼接条件
-
内连接只会显示两张表中有关联的数据
左连接
-- 保证左表数据显示完整的情况下,关联右表中的数据,没有关联的用null表示
select * from 表1 left join 表2 on 表1.字段 = 表2.字段
-- 最终显示表1中的所有数据,关联表2中的数据
右连接
-- 保证右表数据显示完整的情况下,关联左表中的数据,没有关联的用null表示
select * from 表1 right join 表2 on 表1.字段 = 表2.字段
-- 最终显示表2中的所有数据,关联表1中的数据
嵌套查询
将查询出的结果继续使用在另一个查询语句中
-- 查询价格最低的图书信息
select * from book_info where book_price = (select min(book_price) from book_info)
-- 按类型分组,查询每组中图书价格大于平均价格的图书
select * from book_info bi inner join
(select type_id,avg(book_price) as avg from book_info group by type_id)temp
on bi.type_id = temp.type_id where book_price > avg
单表查询
import java.sql.*;
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//连接mysql数据库实现单表查询
//1.加载连接MySQL的驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.连接数据库,提供要连接的数据库的URL、用户名、密码
String url = "jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai";
Connection conn = DriverManager.getConnection(url, "root", "root");
//3.构造查询的sql语句
String sql = "select * from hero";
//4.处理sql语句
PreparedStatement pst = conn.prepareStatement(sql);
//5.处理执行sql语句后的结果
ResultSet rs = pst.executeQuery();
//6.循环遍历读取查询后的数据
while (rs.next()) {
//根据字段名获取
int id = rs.getInt("id");
//根据字段顺序获取
String name = rs.getString(2);
String sex = rs.getString(3);
int price = rs.getInt(4);
String makeTime = rs.getString(5);
String position = rs.getString(6);
System.out.println(id+"\t"+name+"\t"+sex+"\t"+price+"\t"+makeTime+"\t"+position);
}
//7.关闭连接
rs.close();
pst.close();
conn.close();
}
}
SQL注入
在构造sql语句时,如果使用字符串拼接的方式构造可变的sql语句,可能会造成sql注入的风险,导致执行不是预期的sql语句
-- 如删除的sql String sql="delete from 表 where 主键="+参数;
-- 实际传递 '' or 1=1
delete from 表 where 主键= '' or 1=1
-- 导致条件永远成立,最终删除了所有数据
-- 查询的sql String sql="select * from 表 where name="+name +"and pwd="+pwd;
-- 实际第一个参数传递 '' or 1=1 --
select * from 表 where name = '' or 1=1 -- pwd=
-- 导致第二个参数被注释,第一个参数永远成立
简化JDBC
DBUtil数据库工具类
import java.sql.*;
/*
* 定义数据库工具类,简化JDBC操作
* 可以简化的操作
* 1.加载驱动 √
* 2.连接数据库 √
* 6.关闭 √
* */
public class DBUtil {
/*
* 定义静态代码块用于加载驱动
* */
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("驱动不存在" + e);
}
}
//定义静态常量保存连接数据库的字符串
private final static String URL = "jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai";
private final static String USERNAME = "root";
private final static String PASSWORD = "root";
/*
* 定义静态方法获取连接对象
* */
public static Connection getConn() {
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
System.out.println("连接数据库信息异常" + e);
}
return connection;
}
/*
* 定义静态方法释放资源
* */
public static void release(Connection conn, PreparedStatement pst, ResultSet rs) {
//使用了哪个接口就关闭那个接口
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("关闭异常" + e);
}
}
}