数据库基础语句

一、数据库

SQL语言是数据库的核心语言,SQL语言分为四类:

DDL语言:数据定义语言;

DML语言:数据操纵语言

DQL语言:数据查询语言;

DCL语言:数据控制语言;

1.数据定义语言DDL

DDL语言是用来创建数据库中的各种对象------表,视图,索引,同义词,聚簇等;
DDL语言是隐形提交的,不能rollback(不能回滚,撤销);

-- 创建库:
create database 库名;

-- 使用库:
use	database 库名;

-- 创建表:
create table 表名 (
表的字段名 该字段的数据类型
);

-- 删除表
drop table 表名;

-- 删除表之前先查询表是否存在,如果存在就删除
drop table if exists 表名;

-- 查表结构
desc 表名;

-- 修改表的字段名称
alter table 表名 change 字段名 修改后的字段名 该字段的数据类型;

-- 修改表的字段类型
alter table 表名 modify 字段名 改变后的字段数据类型;

-- 添加字段
alter table 表名 add 添加的字段名 该字段类型;

-- 删除字段
alter table 表名 drop 字段名;

2.数据操纵语言DML

数据操纵语言主要有三种形式:
	1)插入:	INSERT;
	2)更新:	UPDATE;
	3)删除:	DELETE;

-- 插入数据
	-- 插入全表数据
	INSERT INTO 表名 VALUES(值1,值2.....);
	
	-- 插入全表:一次插入多条数据
	INSERT INTO 表名 VALUSE
	(值1,值2,......),
	(值1,值2,......),
	(值1,值2,......);
	
	-- 插入部分字段数据
	INSERT INTO 表名(需要插入的字段名) VALUES (值1,值2,....);
	
-- 修改表的数据 update
UPDATE 表名 SET 要修改的字段名称 = 要修改的值 WHERE 字段名称 = 值;

-- 删除表的数据
	-- 删除指定记录
	delete from 表名 where 字段名称 = 值;
	
	-- 删除全表数据
	delete from 表名;//删除全表的数据,但是表的结构还在;
	truncate table 表名;//删除全表的数据,同时删除表,然后在创建一张一模一样的空表

3.数据查询语言DQL

数据查询语言基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块;
	SELECT 字段名
	FROM 表名或视图名
	WHERE 查询条件;
DQL带条件查询可以使用基本运算符:比较运算符,逻辑运算符,赋值运算符;
mysql 语句判断某个字段为null: is null / is not null;
排序:DESC降序排列,ASC:升序排列

-- 查询全表的数据
select * from 表名;

-- 查询指定字段
select 查询的字段名称 from 表名;

-- 模糊查询 like
select 字段列表 from 表名 where 字段名称 like '%字符%';
/*
	% :包含的指定的字符  使用'%字符值%' 模糊查询包含指定字符的信息
	_ :代表单个字符(一个_下划线代表一个字符)
	两个相结合使用: '_%字符值%_'  三个字符:中间字符包含指定的值进行模糊查询
*/

-- 聚合函数 count(列名)/max(列名)/min(列名)/sum(列名)/avg(列名)
select count(字段列名)/max(列名)/min(列名)/sum(列名)/avg(列名) from 表名;

-- 排序查询 order by
select 字段列表 from 表名 order by 字段名 排序规则; -- 单个字段进排序

-- 分组查询 group by  -- group by 后面不能使用聚合函数
select 查询分组字段 from 表名 (where条件) group by 按照该字段进行分组;

-- 筛选查询 having  -- having 必须置于group by 之后,where置于group by 之前,having 后可以跟聚合函数
select 查询分组字段 from 表名 where 条件 group by 分组字段 having 筛选条件;

-- 分页查询limit  -- 起始行数= (当前页码数-1)*每页显示的条数
select 字段列表 from 表名 limit 起始行数,每页显示多少条;

4.数据控制语言DCL

数据控制语言用来授予或回收访问数据库的某种特权.并控制数据库操纵事物发生时间及效果,对数据库进行监视等;
如:
	1)GRANT:	授权;
	2)ROLLBACK[WORK]TO[SAVEPOINT]:	回退到某一点
	回滚命令是数据库状态回到上次最后提交的状态,其格式为:
		SQL>ROLLBACK

5.数据库约束

约束用户操作的一种行为
1)默认约束:防止出现非法数据null;
2)非空约束
3)唯一约束:可以有null值,但是不能重复

通过sql语句来实现约束

-- 默认约束
	-- 修改表加入默认约束
	ALTER TABLE 表名 MODIFY 字段名称 字段类型  DEFAULT 字段值 ;
	-- 删除默认约束
	ALTER TABLE  表名 MODIFY 字段名称 字段类型 ;

-- 非空约束
	-- 创建表加入非空约束
	CREATE TABLE test(
	id INT ,
	NAME VARCHAR(10) NOT NULL  
	);
	
	-- 修改表添加非空约束
	ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL  ;
	
	-- 修改表删除非空约束
	ALTER TABLE 表名 MODIFY 字段名 字段类型 ;

-- 唯一约束
	-- 创建表加入唯一约束
	CREATE TABLE 表名 (字段名称) UNIQUE ;
	
	-- 删除唯一约束
	alter table 表名 drop index 字段名称;
	
	-- 添加唯一约束
	ALTER TABLE 表名 MODIFY 字段名 字段类型 UNIQUE ;

-- 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY ; 

-- 添加主键约束
ALTER TABLE test MODIFY id INT PRIMARY KEY ;

-- 外键约束(两张表设置一个外键):两种设置方式
	-- 1)创建表的直接添加外键
	-- 2)通过alter table 表名 add .... 方式添加外键
	-- 创建员工表的时候就需要添加外键(外键所在表称为"从表")
	CREATE TABLE emp( 
		id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
		NAME VARCHAR(20), -- 员工姓名
		gender VARCHAR(10), -- 性别
		dept_id INT, -- 部门编号   
		CONSTRAINT  fk_demp_emp  -- 声明 外键名称
		FOREIGN KEY (dept_id) -- 作用在指定从表的指定字段
		REFERENCES 	-- 关联
		dept(id)    -- 部门表的主键id
	) ;
	
	-- 删除外键约束 
	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
	
	-- 通过修改表添加外键
	ALTER TABLE 
		emp
 	ADD 
	CONSTRAINT --  声明
	 fk_demp_emp  -- 外键名称
	FOREIGN KEY (dept_id) -- 作用在指定从表的指定字段
	REFERENCES 	-- 关联
	dept(id)  ;  -- 部门表的主键id
-- 通过sql语句添加外键以及级联操作
	alter table 表名 add 	constraint 外键名称(fk_主表_从表)
	foreign key (外键字段名称)
	references 主表(主键字段) 
	-- on update cascade	-- 级联修改
	-- on delete cascade	-- 级联删除

6.数据库的备份与还原

两种方式:
	1)图形界面化:简单直观
		备份:选择数据库---右键----备份---->选择导出的路径(结构以及数据都保存)
		还原:将之前存在库,然后选择 执行指定sql脚本----> 选择指定的sql脚本---进行执行即可!
	2)命令行方式:
		备份:mysqldump -uroot -p 输入密码 备份数据库名称 > 保存的地址
		还原:将原来删除,新建库,使用库,source 将保存的本地地址

7.数据库的三大范式

第一范式:1NF: 数据库的每一列都是不可分割的原子数据项,不能出现复合项;
第二范式:2NF: 在第一范式的基础上所有列完全依赖于主键列;
第三范式:3NF: 在第二范式的基础上非主键字段不能传递依赖于主键

8.多表查询

多表查询方式: 内连接查询 , 外连接查询 , 子查询

内连接查询

-- 隐式内连接:通过where语句作为连接条件来查询(按照外键关系)
    select  字段列表   from  表名1 ,表名2  where  连接条件  如果还要条件 and  连接条件2...
-- 显示内连接:inner join
    select 字段列表 from 表名1 inner join 表名2  on 连接条件...

外连接查询

-- 左外连接查询 left(outer) join将两张表中左表的数据全部进行查询并且以及他们交集部分的数据全部查询
select 字段列表 from 表名1 left outer join 表名2 on 连接条件...

-- 右外连接查询
-- select 字段列表 from 表名1 right outer join 表名2 on 条件

9.事务

事务是关系型数据库的一种机制,目的最终为了保证数据的完整性!   
  当同时操作多个sql语句或者是多张表时,那么需要保证数据的一致性(要么同时执行成功,要么同时执行失败!)
  需要将业务功能代码(update/add/delete),需要使用事务进行管理!
 
 事务的特点:
 	原子性:操作目的数据时候,要么同时成功,要么同时失败
 	一致性:操作数据的前后,数据量保持不变
 	隔离性:事务和事务之间不相互影响,因为一个事务就管理某个业务,事务是一个独立的! 
 	持久性:一旦事务提交,对数据的影响是永久性的;
 	
开启事务: start transaction; 需要手动提交

如果中间出现问题,需要使用rollback ;事务回滚
如果没有提交,语句执行结束,必须提交 commit ;

10.数据库的隔离级别

读未提交:read uncommitted ;   安全性最差 (一个事务读取到另一个没有提交的事务,会造成脏读)
读已提交:read committed ; 相对读未提交安全一些,有效防止脏读,但是会出现不可重复读问题(一个事务当没有提交之前,一般通过update语句,造成前后内容不一致)
可重复读:repeatable read: 能够有效防止脏读,不可重复读问题(mysql的默认隔离级别)
串行话:serializale :安全性最高,两个事务,当一个事务没有提交,这个事务不能操作其他事情

设置隔离级别 set global transaction isolation level 级别字符串;
查询隔离级别 select @@tx_isolation;

隔离级别不同,会出现不同的问题
脏读:一个事务读取另一个没有提交的事务(最严重的问题)
不可重复读: 一般都是update语句影响,两个事务中,一个事务读取的前后的内容不一致!
幻读:一般insert/delete :影响两个事务中,前后数量不一致!

二、JDBC

1.本质

JDBC的本质就是能够实现java.sql包下的相关的接口的实现类

2.使用JDBC连接数据库的过程

1)在java项目添加额外的第三方的jar包
idea中 file---->  project structure (ctrl+Alt+shift+s:默认快捷键)---model---->denpendies---->添加---jar包 ---将指定路径拿过来
2)注册驱动
3)获取数据库的连接对象
4)准备静态sql语句
5)通过数据库连接对象创建执行对象Statement
6)执行更新操作
7)释放资源

JDBC的原生操作步骤

public class JdbcDemo {
   
    public static void main(String[] args) throws Exception {
   
        //2)注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver") ;
        //3)获取数据库的连接对象
        //使用驱动管理类 DriverManager:用于管理一组JDBC驱动程序的基本服务。
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb_01?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                "root",
                "123456");
        //4)准备sql语句
        String sql = "insert into account(name,balance) values('赵又廷',1000)" ;

        //5)通过数据库连接对象获取执行对象
        Statement stmt = connection.createStatement();

        //6)执行更新操作
        //通用方法
        int count = stmt.executeUpdate(sql);
        System.out.println("影响了"+count+"行");

        //7)释放资源
        stmt.close() ;
        connection.close();
    }
}

3.封装JDBC操作的工具类

封装JDBC工具类的步骤

1)定义一个类JdbcUtils
2)构造方法私有化
3)成员变量声明:静态的字符串变量 url,user,password,driverClass 初始化为null
4)提供静态代码块
 读取src目录下的jdbc.properties 
 	url=jdbc:mysql://localhost:3306/数据库名称characterEncoding=utf8&timeZone=UTC...
 	user=root
 	password=123456
 	driverClass=com.mysql.cj.jdbc.Driver
 	
 	通过属性集合类Properties; 将配置文件的内容加载进来
    属性集合类对象.getProperty(String key)---->String value
    注册驱动
    Class.forName(获取的配置文件中的driverClass对应的value值) ;
    
 5)提供静态的功能getConnection()----->Connection对象
 6)封装功能:close(ResultSet rs,Statement stmt,Connnection conn) 
 	    	closeStatement stmt,Connnection conn) 
    

需要有配置文件

public class JdbcUtils {
   
    private static String url = null ;
    private static String user = null ;
    private static String password = null ;
    private static String drivceClass = null ;
    //构造方法私有化
    private JdbcUtils(){
   }
    //静态代码块
    //JdbcUtils工具类一加载就执行静态代码块
    static{
   
        try {
   
            //创建一个Properties属性列表
            Properties prop = new Properties() ;
            //读取src下面的jdbc.properties配置文件
            //直接获取当前类的字节码文件对象,获取类加载,获取资源文件所在的输入流对象
            InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            //将流中内容加载到属性列表中
            prop.load(inputStream);
            System.out.println(prop);//测试属性列表中的内容

            //通过key获取value
            url = prop.getProperty("url") ;
            user = prop.getProperty("user") ;
            password = prop.getProperty("password") ;
            drivceClass = prop.getProperty("driverClass") ;

            //注册驱动
            Class.forName(drivceClass) ;

        } catch (IOException e) {
   
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
   
            e.printStackTrace();
        }
    }

    //提供功能都是static
    //通用的功能:获取数据库连接对象
    public static Connection getConnection(){
   
        Connection  conn = null ;
        try {
   
            //通过驱动类管理类获取
             conn = DriverManager.getConnection(url,user,