数据库阶段学习

MySQL学习

DDL:操作数据库、表

(Data Definition Language)数据库定义语言，用于操作数据库和表。

1.操作数据库：CRUD

​ 1.创建数据库：C(Create)

create database 数据库名;
create database if not exists 数据库名称; -- 判断不存在，再创建
create database 数据库 character set 字符集合; -- 指定字符集

​ 2.查询数据库：R(Retrieve) 查询时用关键字show。

show databases; 	-- 查询所有数据库的名称
show create table 数据库名称; -- 查询某个数据库的字符集：查询某个数据库的创建语句

​ 3.修改数据库：U(Update) 修改时用关键字alter。

alter database 数据库名称 character set 字符集合; #修改数据库的字符集

​ 4.删除数据库：D(Delete)删除时用关键字drop。

drop database 数据库名称; -- 删除数据库。
drop database if exists 数据库名称; -- 判断存在，存在再删除。

​ 5.使用数据库

select database(); --查询正在使用的数据库名称。
use 数据库名称; #数据库名称。 
#为mysql的方言注释
-- sql注释
/* sql多行注释 */

2.操作表：CRUD

​ 1.创建表：C(Create)

create table 表名(
	列名1 数据类型1,
   	列名2 数据类型2,
  		...
   	列名n 数据类型n
);
数据类型：整数类型 int
		小数类型 double
		日期	   date 只包含年月日，yyyy-MM-dd
		日期	   datetime 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
		时间戳类型 timestamp 包含年月日时分秒 yyyy-MM-dd HH:mm:ss -- 如果将来不给这个字段赋值，或者赋值为null，则使用当前系统时间来自动赋值。
		字符串	   varchar() -- 括号内必须定义大小
复制表：
	create table 表名 like 被复制的表名;

​ 2.查询表：R(Retrieve) 查询时用关键字show。

show tables; -- 查询某个数据库的所有表名称。
desc 表名; -- 查询表结构。

​ 3.修改表：U(Update) 修改时用关键字alter。

alter table 表名 rename to 新的表名;  -- 修改表名。
alter table 表名 character set 字符集名称;  -- 修改表的字符集。
alter table 表名 add 列名 数据类型;  -- 添加一列。
alter table 表名 chenge 列名  新列名 数据类型; -- 修改列的名称 类型。
alter table 表名 modify 列名 新数据类型;  -- 修改数据类型。
alter table 表名 drop 列名;  -- 删除列。

​ 4.删除表：D(Delete)删除时用关键字drop。

drop table 表名;  -- 删除表
drop table if exists 表名;  -- 判断存在删除表。

DML:增删改表中的数据

1.添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

列名和值要一一对应；如果表名之后不定义列名，则默认给所有列添加值。

2.删除数据

delete from 表名 [where 条件] --不加条件，则删除表中所有数据。

truncate table 表名;  --先删除表，然后再创建一张一模一样的表。

3.修改数据

update 表名 set 列名1=值1,列名2=值2,.... [where 条件]; --如果不加任何条件，则会修改全部记录

DQL:查询表中的记录

select * from 表名;
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定

--多个字段查询
	select 字段名1,字段名2... from 表名;	select * from 表名;
--去除重复
	distinct   select distinct 字段名1,字段名2... from 表名;去除重复
--计算列
	ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
		表达式1为可能为null的字段，表达式2为表达式1是null时候的替换值
--其别名
	as:as 也可以省略  as跟在字段后面

--条件查询
	1.where子句后跟的条件
	2.运算符
		> < <= >= = <>
		between...and
		in(集合)
		Like 模糊查询 占位符   _:单个占位符  %:多个任意字符
		is null
		and 或 &&
		or 或 ||
		not 或 ！

-- 排序查询
	order by 排序字段1 排序方式1,排序字段2 排序方式2...(多条判断时，前面的相等 才会判断后面的)
	ASC :升序 默认 DESC:降序

-- 聚合函数
将一列数作为一个整体，进行纵向计算
1.count: 计算个数 一般选择非空的列：主键 #当含有null的列作为参数时，count会越过不做计算
2.max：计算最大值
3.min：计算最小值
4.sum：计算和
5.avg：计算平均值

-- 分组查询
1.group by 分组字段;
2.分组前后查询where和having的区别：
	1.where在分组之前进行判断，having在分组之后进行判断
	2.where后面不可以跟聚合函数，having之后可以进行聚合函数的判断

-- 分页查询
1.limit 开始的索引,每页查询的条数;
2.公式：开始索引=（当前页码-1）*每页显示的条数。
limit是mysql的一个“方言”

约束

1.主键约束：primary key
	-- 主键非空且唯一，一张表只能有一个主键
	创建表时添加主键约束：id int primary key; -- 一般主键约束添加在id字段后面。
	创建完表之后添加：alter table 表名 mofify 字段名 字段类型 primary key;
	删除主键：alter table 表名 drop primary key;
	自动增长：-- 一般给主键id设置为自动增长
		创建表时添加自动增长：添加在主键之后 auto_increment
		创建完表之后添加：alter table 表名 modify 字段名 字段类型 auto_increment;
		删除自动增长：alter table 表名 modify 字段名 字段类型；
2.非空约束：not null
	值不能为null
	创建表时添加：添加在字段后面 not null
	创建完表之后添加：alter table 表名 modify 字段名 字段类型 not null;
	删除非空约束： alter table 表名 modify 字段名 字段类型;
3.唯一约束：unique
	创建表时添加：添加在字段后面 unique -- 唯一约束限定的字段可以有多个null值。
	创建完表之后添加：alter table 表名 modify 字段名 字段类型 unique;
	删除唯一约束：alter table 表名 drop index 字段名; -- 需要删除索引才能删掉唯一约束
4.外键约束：foreign key -- 让表和表之间产生关系 在一对多的情况下在多的一方添加外键
	创建表时添加：-- 新的一行
		constraint 外键名称 foreign key (外键列名称) references 主表名称(主列名称)
	创建表之后添加：alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主列名称)；
	删除外键约束：alter table 表名 drop foreign key 外键名称;
	级联操作：
		-- 添加外键时将级联操作增加在外键后面
		级联更新：on update cascade
		级联删除：on delete cascade

数据库的三大范式

第一范式（1NF）：每一列都是不可分割的原子数据项
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库的备份与还原

备份：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原：登陆创建使用数据库之后：source 文件路径

多表查询

1.内连接查询
	1.隐式内连接：select 表名1.字段名...,表名2.字段名... from 表名1，表名2 where 判断语句;
	2.显示内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件;
2.外连接查询
	1.左外连接：select 字段列表 from 表名1 left [outer] join 表名2 条件;
	2.右外连接：select 字段列表 from 表名1 right [outer] join 表名2 条件;
3.子查询(查询的嵌套)
	1.单行单列：子查询可以作为条件，使用运算符：> >= = <= <
	2.多行单列：子查询可以作为条件，使用运算符in来判断
	3.多行多列：子查询可以作为一张虚拟的表参与查询

事务

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
开启事务：start transaction;
回滚：rollback;
提交：commit;
mysql数据库默认自动提交，Oracle数据库默认是手动提交。
查看事务的提交方式：select @@autocommit; -- 1代表自动提交 0 代表手动提交
修改默认提交方式：set @@autocommit = 0;

事务的四大特征：
1.原子性：不可分割的最小操作单位，要么同时成功，要么同时失败。
2.持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3.隔离性：多个事务之间相互独立。
4.一致性：事务操作前后，数据总量不变。

事务的隔离级别：
	存在问题：
		1.脏读：一个事务，读取到另一个事务中没有提交的数据。
		2.不可重复读(虚读)：在同一个事务中，两次读取的事务不一样。
		3.幻读：一个事务操作(DML)数据表中所有记录，另一个表中添加了一条数据，则第一个事务查询不到自己的修改。
	隔离级别：
		1.read uncommitted:读未提交
			产生问题：脏读，不可重复读，幻读
		2.read committed:读已提交(Oracle)
			产生问题：不可重复读，幻读
		3.repeatable read:可重复度(Mysql)
			产生问题：幻读
		4.serializable:串行化
			解决了所有问题
	隔离级别从小到大越来越安全，效率越来越低
	数据库查询隔离级别：
		select @@tx_isolation;
	数据库设置隔离级别：
		set global transaction isolation level 级别字符串;

DCL：管理用户，授权

1.添加用户：create user '用户名'@'主机名' identified by '密码';
2.删除用户：drop user '用户名'@'主机名';
3.修改用户密码：
	update user set password=password('新密码') where user='用户名';
	set password for '用户名'@'主机名' = password('新密码');
4.查询用户：
	use mysql;
	select * from user;
	通配符：%表示可以在任意主机使用用户登录数据库。

忘记root用户密码

1.cmd --> net stop mysql -- 停止mysql服务，需要管理员运行该cmd
2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables
3.打开新的cmd窗口，直接输入mysql命令，敲回车。就可以登陆成功。
4.use mysql;
5.update user set password=password('新密码') where user='root'；
6.关闭两个窗口。
7.打开任务管理器，手动结束mysqld.exe的进程
8.启动mysql服务。
9.使用新密码登录。

权限管理

1.查询权限
	show grants for '用户名'@'主机名';
2.授予权限
	grants 权限列表 on 数据库名.表名 to '用户名'@'主机名';
	权限列表：All 所有权限  数据库.表名：*.*通配
3.撤销权限
	revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

JDBC

概念：Java Database Connectivity   Java数据库连接
本质：官方定义的一套操作所有数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库的jar包。我们可以使用这套接口(JDBC)编程，真正执行的代码是驱动jar包中的实现类。
1.DriverManager：驱动管理对象
	1.注册驱动：告诉程序该使用哪一个数据驱动jar registerDriver(Driver driver)
		Class.forName("com.mysql.jdbc.Driver")
	2.获取数据库连接：
		Connection getConnection(String url, String user, String password)
    	url:指定连接的路径 jdbc:mysql://ip地址(域名):端口号/数据库名称
    	user:用户名
    	password:密码
2.Connection：数据库连接对象
	1.获取执行sql的对象
		Statement createStatement()
		PreparedStatement prepareStatemrnt(String sql)
	2.管理事务
        开启事务：setAutoCommit(boolean autoCommit)：调用该方法设置参数为false，即开启事务。
        提交事务：commit()
        回滚事务：rollback()
3.Statement：执行sql的对象
	1.执行sql boolean execute(String sql)：可以执行任意的sql。
	2.int executeUpdate(String sql)：执行DML语句返回影响行数。
	3.ResultSet executeQuery(String sql)：执行DQL语句。
4.ResultSet：结果集对象，封装查询结果
	boolean next()：游标向下移动一行，判断若为最后一行返回false，不是返回true。
	getXxx(参数)：获取数据
	一般用while循环获取数据。
5.PreparedStatement：执行sql的对象
	1.SQL注入问题：在拼接字符串时，有一些sql的特殊关键字参与字符串的拼接，会造成安全性的问题。
	2.解决sql注入问题：使用PreparedStatement对象来解决。
	3.预编译sql：参数使用?作为占位符

抽取JDBC工具类：JDBCUtils

public class JDBCUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    private JDBCUtils(){}

    /**
     * 静态代码块加载jdbc.properties文件，获取数据之后赋值给成员变量。
     * 从文件中读取避免了传值的麻烦并且能够只读一次多次使用。
     */
    static {

        try {
            Properties pro = new Properties();
            //通过类对象获取类加载器对象
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            //用类加载器对象获取指定路径的资源url对象
            URL res = classLoader.getResource("jdbc.properties");
            //用url对象的getPath方法获取jdbc.properties文件的路径
            String path = res.getPath();
            //将文件以字符流的形式加载金propertirs集合
            pro.load(new FileReader(path));
            //通过指定的键获取对应值并赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //加载驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);//获取连接对象
    }

    public static void close(Statement stmt,Connection conn){//close方法的重载
        close(null,stmt,conn);
    }
    public static void close(ResultSet rs,Statement stmt, Connection conn){//判断参数不为空关闭资源
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

数据库连接池

1.概念：系统初始化好以后，连接池被创建，申请一些连接对象，用户访问数据库时从连接池中获取连接对象，访问完之后，会将对象归还给连接池。
2.优点：节约资源，用户访问效率更高。
3.接口DataSource
	获取连接：getConnection
    归还连接：close 线程池中获取的连接执行close方法不是关闭连接，而是归还连接。
    以下两种常用实现
    1.C3P0：数据库连接池技术。
    2.Druid：数据库连接池技术，由阿里巴巴提供的。
4.C3P0：数据库连接池技术
	1.导入jar包 c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar
	2.定义配置文件：
		名称：c3p0.properties 或者 c3p0-config.xml
		路径：将文件直接放在src目录下即可。
	3.创建连接池对象 ComboPooledDataSource
	4.获取连接：getConnection
5.Druid：数据库连接池实现技术
	1.导入jar包druid-1.0.9.jar
	2.定义配置文件：
		是properties形式的
		可以叫任意名字，可以放在任意目录下
	3.获取数据库连接池对象：通过工厂类来获取 DruidDataSourceFactory
	4.获取连接 getConnection

抽取JDBC工具类：JDBCUtils—-使用druid连接池

//注释与上面抽取工具类的注释差不多，就不再一一注释
public class JDBCUtils {

    private static DataSource ds;

    static {
        try {
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static DataSource getDataSource() {
        return ds;
    }

    public static void close(Statement stmt, Connection conn) {
        close(null, stmt, conn);
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Spring JDBC

Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
	1. 导入jar包
	2. 创建JdbcTemplate对象。依赖于数据源DataSource
		JdbcTemplate template = new JdbcTemplate(ds);

	3. 调用JdbcTemplate的方法来完成CRUD的操作
		update():执行DML语句。增、删、改语句
		queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
			注意：这个方法查询的结果集长度只能是1
		queryForList():查询结果将结果集封装为list集合
			注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
		query():查询结果，将结果封装为JavaBean对象
			query的参数：RowMapper
				一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
				new BeanPropertyRowMapper<类型>(类型.class)
		queryForObject()：查询结果，将结果封装为对象
			一般用于聚合函数的查询