Java数据库 Mysql和Idea环境

Java数据库 Mysql和Idea环境

一、数据库的基本概念

1.DataBase 简称：DB

2.什么是数据库？

   存储数据的仓库

3.特点：

持久化存储，其实是一个文件系统

二、SQL（Structured  Query Language）：结构化查询语言

   定义了所有操作关系型数据库的规则

1.注释方法

（1）单行注释  ：--  注释内容 两个横杠加上一个空格     #注释内容（mysql特有）

（2）多行注释：/*注释内容*/

2.分类

（1）数据查询语言（DQL，Data Query Language）
数据查询语言是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。

（2）数据定义语言（DDL，Data Definition Language）
数据定义语言 (, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集。

（3）数据操纵语言（DML，Data Manipulation Language）
数据操纵语言是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

（4）数据控制语言（DCL，Data Control Language）
数据控制语言 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

三、基本的操作语句

1.DDL

操作数据库

 · 修改数据库的字符集

      alter database 数据库名称 character set 字符集名称;

· 删除数据库

     drop database 数据库名称;

    drop database if exist 数据库名称;

· 查询当前正在使用的数据库名称

   select database();

· 使用数据库

   use 数据库名称;

操作表

· 查询某个数据库中所有表的名称

    show tables;

· 查询表结构

   desc 表名;

· 创建一个表

    create table 表名(列名1 数据类型1，

                                列名2 数据类型2，

                                列名3 数据类型3，

                                ········

                                列名n 数据类型n);   

  注意：最后一行没有逗号 

·  复制一个表

  create 表名 like 被复制的表名;

·  删除表

    drop table 表名；

    drop table if exist 表名；

·  修改表名

   alter table 表名 rename 童新的表名；

·  修改表的字符集

   alter table 表名 character set 字符集名称；

  ·  添加一列

   alter table 表名 add 列名 数据类型；

·  修改列的名称 类型

   alter table 表名 change 列名  新列名 新数据类型；

   alter table 表名 modify 列名   新数据类型；

·  删除列

   alter table 表名 drop 列名；

2.DML增删改表中的数据

·  添加数据

insert into 表名（列名1,列名2，...,列名n）values(值1，值2，...，值n)；

注意：列名和数值一一对应

           表明后不定义列名则默认给所有列添加

           除了数值其余的要添加引号

·  删除数据

   delete from 表名 [where 条件]；

注意：若不加条件删除表中所有记录

           有多少条记录删除多少次，效率低

   truncate table 表名；

注意：先删除表再创建一张一模一样的表

·  修改数据

  update 表名 set 列名1=值1，列名2=值2，...，列名n=值n [where 条件]；

注意：如果不加任何条件，所有记录都被修改

3.DQL查询表中的记录

·  select

           字段名列表

from

         表名列名

where

        条件列表

group by 

       分组字段

having

      分组之后的条件

limit

     分页限定

·  基础查询

（1）多个字段查询

  select 字段名1，字段名2；

注意：查询所有字段可以利用* 号    select *from 表名

（2）去除重复

    distinct

（3）计算列

  一般可以使用四则运算计算数值

ifnull（表达式1，表达式2）防止有NULL参与的运算结果都为NULL

表达式1：需要判断是否为NULL的字段

表达式2：如果为NULL的替换值

（4）起别名

as 也可以省略

·  条件查询

（1）where字句后跟条件

（2）运算符

等于（=）        不等号（！=或者<>）

between   and          IN

LIKE            IS NULL

(3)模糊查询

占位符：

_  单个任意字符

%  任意多个字符

·  排序

    order by 排序字段1  排序字段1，排序字段2  排序字段2...；

    注意：升序  ASC   默认的

              降序DESC

              有多个排序条件，当前面的条件一样时，才判断第二条件

·  聚合函数 

    将一列作为一个整体，进行纵向计算

    count，max，min，sum，avg

注意：排除null值

          选择不包含非空的列 ：主键

          ifnull（）函数

·  分组查询

   group by  分组字段

   注意：分组之后查询的字段：分组字段，查询函数

              where和having的区别： where在分组之前进行限定，若不满足条件，则不参与分组，且不能跟聚合函数

                                                     having在分组之后进行限定，不满足结果则不会被查询

·  分页查询

     limit  开始的索引，每页查询的条数； 只能在mysql中使用

开始的索引=（当前页数-1）*每页显示的条数

4、DCL

（1）管理用户

（2）授权

       ·查询权限:
        SHOW GRANTS FOR '户名'@' 主机名;

        SHOW GRANTS FOR 'lisi'@'%';
       ·授予权限:
        grant权限列表on数据库名.表名to '用户名'@'主机名';
        GRANT ALL ON *.* TO ' zhangsan' @'localhost' ;一给张三用户授予所有权限，在任意数据库任意表上
       ·撤销权限:
         revoke权限列表on数据库名.名from ' 驴名'@'主机名' ;|

四、约束

1、概念：对表中的数据进行限定，保证数据的完整性，正确性和有效性

2、分类

· 主键约束（Primary Key）

非空且唯一，一张表只能有一个主键

（1）在创建表时，添加主键约束
      create table stu(
      id int primary key,--给id添加主键约束name varchar(20)
      );
（2）删除主键
        alter table stu modify id int ;

       ALTER TABLE stu DROP PRIMARY KEY;
（3）创建完表后，添加主键
           ALTER TABLE stu MODIFY id INT PRIMARY KEY;

· 非空约束（not null）

    （1）创建表时添加约束

               CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL -- name);

   （2）创建表完后，添加非空约束

              ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

   （3）创建表完后，删除非空约束

              ALTER TABLE stu MODIFY NAME VARCHAR(20) ;

· 唯一约束（unique）

   （1）创建表时，添加唯一-约束
      CREATE TABLE stu(
      id INT,
      phone_ number VARCHAR(20) UNIQUE --添加了唯一约束）；
注意：mysql中，唯一约束限定的列的值可以有多个null
  （2）删除唯一约束
     alter table stu modify phone_ number VARCHAR(20) ;   错误方法

     ALTER TABLE stu DROP INDEX phone_ number;
（3）在创建表后，添加唯一约束
     ALTER TABLE stu MODIFY phone_ number VARCHAR(20) UNIQUE；

（4）自动增长

    CREATE TABLE stu (
    id INT PRIMARY KEY AUTO_ INCREMENT, -- 给Id添加主键约束

     VARCHAR (20)
    );
  删除自动增长
      ALTER TABLE stu MODIFY id INT;
  添加自动增长
      ALTER TABLE stu MODIFY id INT  AUTO_INCREMENT；
· 外键约束（Foreign Key）

让表与表产生关系

（1）创建表时，添加外键

     create table 表名（

                                . . . . 

                               外键列

                               constraint  外键名称  foreign  key（外键列名称）主表名称（主表列名称）

）；

（2）删除外键

      alter table 表名 drop foreign key 外键名称；

（3）创建表之后，添加外键

       alter table 表名 add constraint 外键名称  foreign key（外键字段）reference  主表名称（主表列名称）；

（4）级联操作

· 添加级联

     alter table 表名 add constraint 外键名称  foreign key（外键字段）reference  主表名称（主表列名称）on update cascade  / on delete cascade；

五、数据库的设计

1、多表之间的关系

（1）一对一关系

       可以在任意一方添加唯一外键指向另一张表的主键

（2）一对多关系（多对一关系）

         在多的一方建立外键，指向一的一方的主键

（3）多对多关系

         需要借助但三张中间表，至少包含两个字段，这两个字段做第三张表的外键，分别指向两张主表的主键

2、范式

概念：设计过程中遵循的规范

（1）分类

·  第一范式（1NF）：每一列都是不可分割的原子数据项

·  第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（消除非主属性对主码的部分函数依赖）

            函数依赖：A——>B 通过A属性的值可以唯一确定B属性 的值，则B依赖于A

            完全函数依赖：A——>B，A是一个属性组，B属性值得确定需要依赖A属性组中的所有值

            部分函数依赖：A——>B，A是一个属性组，B属性值得确定需要依赖A属性组中的某些值

            传递函数依赖：A——>B，B——>C，通过A属性的值可以唯一确定B属性 的值，再通过B属性的值可以唯一确定C属性的值，则称C属性传递依赖于属性A

·  第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF的基础上消除函数依赖）

六、数据库的备份和还原

1、命令行

     备份：mysqldump -u用户名 -p密码 数据库名称 >备份路径

     还原：登录、创建、使用、执行文件 source 文件路径

2、图像化工具

七、多表查询

需要消除无用数据

1、内连接查询

（1）隐式内连接：使用where条件

（2）显式内连接：select 字段列表 from 表名  inner join 表名 on 加入条件；

2、外连接查询

（1）左外连接：select 字段列表 from 表1 left outer join 表2 on 条件；

查询到的是左表所有数据以及其交集部分

（2）右外连接：select 字段列表 from 表1 right outer join 表2 on 条件；

查询到的是右表所有数据以及其交集部分

3、子查询

查询中嵌套查询

子查询的不同情况

        ·子查询的结果是单行单列的

                 可以作为条件，用运算符判断  < <= > >=  =

        ·子查询的结果是多行单列的

                可以作为条件，用运算符in判断

        ·子查询的结果是多行多列的

                子查询可以作为一张虚拟表参与查询

八、事务

1、概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

2、操作

     开启：start transaction；

     回滚：rollback；

     提交：commit；

注意：mysql中一条DML语句会自动提交一次事务

           set@@autocommit=0   手动提交，1则为自动提交

3、四大特征

（1）原子性：不可分割的最小操作单位，要么都成功要么都失败

（2）持久性：事务提交或者回滚后，数据库会持久化的保留数据

（3）隔离性：多个事务直接相互独立

              隔离级别：防止多个事务同时操作一批数据而引发一些问题 

                   read uncommitted: 读未提交  产生的问题:脏读、不可重复读、幻读

                   pread committed :读己提交    产生的问题:可重复读、幻读

                   repeatable read: 可重复读   产生的问题:幻读 

                   serializable :串行化     可以解决所有的问题

              注意:隔离级别从小到大安全性越来越高，但是效率越来越低

              脏读：一个事务读取到另一个事务没有提交的数据

              不可重复读（虚读）：同一个事务内两次读取的数据不一样

              幻读：一个事务操作数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

数据库查询隔离级别:    * select @@tx_ isolation;

数据库设置隔离级别: set global transaction isolation level级别字符串 ;
 

（4）一致性：事务操作前后，数据总量不变

九、JDBC（Java Database Connectivity）

定义了操作所有关系型数据库的规范，即接口。数据库厂商实现这些接口，提供数据库驱动jar包，真正执行的代码是驱动jar包中的类

 

十、数据库连接池