华清远见重庆中心—MySQL数据库/个人总结

 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);
        }
    }
}