mysql数据库详解

mysql基本操作

在终端输入以下命令，进行登录

mysql -h localhost -u root -p -P 8888 
#-h : 服务器地址 -u : 登录账户 -p : 回车输入密码 -P ： 端口号

查看当前mysql中所有的库。

show databases;  

选择需要操作的库，打开库

use 数据库名;

查看当前库中的所有数据表

show tables;

创建库

create database 库名 default charset=utf8; 

创建表的语法

create table 表名( 字段名 类型 字段约束, 字段名 类型 字段约束, 字段名 类型 字段约束, )engine=innodb default charset=utf8;

创建表: 如果表不存在,则创建, 如果存在就不执行这条命令

create table if not exists 表名(字段1 类型,字段2 类型);

删除表

drop table 表名;

表结构

desc 表名;

查看建标语句

show create table users; 

SQL ( Structure query language ) 结构化查询语言

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

添加数据

insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);

insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

查找数据

select * from 表名; 
select 字段1,字段2,字段3 from 表名; 
select * from 表名 where 字段=某个值;

修改数据

update 表名 set 字段=某个值 where 条件;
update 表名 set 字段1=值1,字段2=值2 where 条件; 
update 表名 set 字段=字段+值 where 条件;

删除数据

delete from 表名 where 字段=某个值; 

退出MySQL

exit; 或者 quit;

SQL语句中的快捷键

\G 格式化输出（文本式，竖立显示） 
\s 查看服务器端信息 
\c 结束命令输入操作
\q 退出当前sql命令行模式 
\h 查看帮助 

注意点：
sql语句可以换行，但要以’;'结尾
命令不区分大小写
如果提示符为 '> 那么需要输入一个’回车
命令打错了换行后不能修改, 可以用 \c 取消

MySQL数据类型

整型、浮点型、字符串、日期等

1、字符串数据类型

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电 话号码、邮政编码等。
不管使用何种形式的串数据类型，串值都必须括在引号内
有两种基本的串类型，分别为定长串和变长串
定长串：char

1. 接受长度固定的字符串，其长度是在创建表时指定的。 定长列不允许存储多于指定长度字符的数据。
2. 指定长度后，就会分配固定的存储空间用于存放数据

char(7) 不管实际插入多少字符，它都会占用7个字符位置

变长串 varchar
存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7 个字符。

注意 
既然变长数据类型这样灵活，为什么还要使用定长数据类型？
回答：因为性能，MySQL处理定长列远比处理变长列快得多。

2、数值类型

数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多
与字符串不一样，数值不应该括在引号内

decimal(5, 2) 表示数值总共5位, 小数占2位 
tinyint 1字节(8位) 0-255。-128，127 
int     4字节。    -21亿，21亿。0-42亿 
float
 
MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)

有符号或无符号
所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号
有符号数值列可以存储正或负的数值
无符号数值列只能存储正数。
默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

3、日期和时间类型

4、二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等

二、表的字段约束

unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
字段类型后面加括号限制宽度
char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度
int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)
int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。
not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错
default 设置默认值
primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。
auto_increment 定义列为自增属性，一般用于主键，数值会自动加1
unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

三、MySQL的运算符

算术运算符： +、 -、 *、 /、 %
比较运算符： =、 >、 <、 >=、 <=、!=
数据库特有的比较： in、not in、is null、is not null、like、between、and
逻辑运算符： and、or、not like
支持特殊符号%和_
其中%表示任意数量的任意字符，_表示任意一位字符

四、主键

1、表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值
2、应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主 键，以便于以后的数据操纵和管理。
要求
记录一旦插入到表中，主键最好不要再修改
不允许NULL
不在主键列中使用可能会更改的值。

例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键。

自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键 重复，也不用自己预先生成主键
可以使用多个列作为联合主键，但联合主键并不常用。使用多列作为主键时，所有列值的组合必须是唯一的

MySQL数据查询SQL

语法格式

select 字段列表|*  from 表名 
[where 搜索条件] 
[group by 分组字段 [having 分组条件]] 
[order by 排序字段 排序规则] 
[limit 分页参数]

基础查询

# 查询表中所有列 所有数据 
select * from users;
 
# 指定字段列表进行查询 
select id,name,phone from users;

Where 条件查询

可以在where子句中指定任何条件
可以使用 and 或者 or 指定一个或多个条件
where条件也可以运用在update和delete语句的后面
where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤

# 查询表中所有列 所有数据 
select * from users;
 
# 指定字段列表进行查询 
select id,name,phone from users;

-- 查询users表中 age > 22的数据 
select * from users where age > 22;
 
-- 查询 users 表中 name=某个条件值 的数据 
select * from users where name = '王五';
 
-- 查询 users 表中 年龄在22到25之间的数据 
select * from users where age >= 22 and age <= 25; 
select * from users where age between 22 and 25;
 
-- 查询 users 表中 年龄不在22到25之间的数据 
select * from users where age < 22 or age > 25; 
select * from users where age not between 22 and 25;
 
-- 查询 users 表中 年龄在22到25之间的女生信息 
select * from users where age >= 22 and age <= 25 and sex = '女';

and和or 使用时注意

查询 users 表中 年龄为22或者25 的女生信息 
select * from users where (age=22 or age = 25) and sex'女';

Like 子句

 -- 查询name字段中包含五的  
select * from users where name like '%五%';
-- 查询name字段中最后一个字符 为 五的  
select * from users where name like '%五';
 
-- 查询name字段中第一个字符 为 王 的  
select * from users where name like '王%';  
-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似
 
-- 查询表中 name 字段为两个字符的数据  
select * from users where name like '__';
 
-- 查询 name 字段最后为五，的两个字符的数据  
select * from users where name like '_五';

注意：where子句中的like在使用%或者_进行模糊搜索时，效率不高，使用时注意：
尽可能的不去使用%或者_
如果需要使用，也尽可能不要把通配符放在开头处

Mysql中的统计函数（聚合函数）

max(),min(),count(),sum(),avg()

# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄 
select max(age),min(age),sum(age),avg(age) from users;

-- 统计 users 表中的数据量 
select count(*) from users; 

Group BY 分组

group by 语句根据一个或多个列对结果集进行分组
一般情况下，是用与数据的统计或计算，配合聚合函数使用

--统计 users 表中 男女生人数
select sex,count(*) from users group by sex;

-- 统计1班和2班的人数 
select classid,count(*) from users group by classid; 

-- 分别统计每个班级的男女生人数 
select classid,sex,count(*) as num from users group by classid,sex; 

Having 子句

having时在分组聚合计算后，对结果再一次进行过滤，类似于where， where过滤的是行数据，having过滤的是分组数据

-- 要统计班级人数 
select classid,count(*) from users group by classid;
 
-- 统计班级人数，并且要人数达到5人及以上 
select classid,count(*) as num from users group by classid having num >=5;

## ```Order by 排序

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，
首先不要依赖默认的排序，另外在需要排序时要使用orderby对返回的结果进行排序 
Asc 升序，默认
desc降序

```java
 -- 按照年龄对结果进行排序，从大到小 
select * from users order by age desc;
 
-- 从小到大排序 asc 默认就是。可以不写 
select * from users order by age;
 
-- 也可以按照多个字段进行排序 
select * from users order by age,id; 

# 先按照age进行排序，age相同情况下，按照id进行排序 
select * from users order by age,id desc;

Limit 数据分页

limit n 提取n条数据
limit m,n 跳过m跳数据，提取n条数据

 -- 查询users表中的数据，只要3条 
select * from users limit 3;
 
-- 跳过前4条数据，再取3条数据 
select * from users limit 4,3;
 
-- limit一般应用在数据分页上面 
-- 每页显示10条数据
第一页  limit 0,10 
第二页  limit 10,10 
第三页  limit 20,10 
第四页  limit 30,10
 
-- 提取 user表中查询年龄最大的三个用户数据
select * from users order by age desc limit 3;

Mysql数据库导入导出和授权

数据导出

1.数据库数据导出

导出一个库中所有数据，会形成一个建表和添加语句组成的sql文件 之后可以用这个sql文件到别的库，或着本机中创建或回复这些数据 2.将数据库中的表导出

#   不要进入mysql，然后输入以下命令 导出某个库中的数据 
mysqldump -u root -p tlxy > ~/Desktop/code/tlxy.sql

2.将数据库中的表导出

#   不要进入mysql，然后输入以下命令 导出某个库中指定的表的数据 mysqldump -u root -p tlxy tts > ~/Desktop/code/tlxytts.sql

数据导入

把导出的sql文件数据导入到mysql数据库中

# 在新的数据库中 导入备份的数据，导入导出的sql文件 
mysql -u  root -p ops < ./tlxy.sql
 
 
# 把导出的表sql 导入数据库 
mysql -u  root -p ops < ./tlxy-tts.sql

权限管理

mysql中的root用户是数据库中权限最高的用户，千万不要用在项目中。
可以给不同的用户，或者项目，创建不同的mysql用户，并适当的授权，完成数据库的相关操作
这样就一定程度上保证了数据库的安全。
创建用户的语法格式：

grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;

# 在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限 
grant select,insert on tlxy.* to zhangsan@'%' identified by '123456';
 
# 用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限 
grant all on tlxy.* to lisi@'%' identified by '123456';
 
# 删除用户 
drop user 'lisi'@'%';

嵌套查询，子查询

就是在一个sql当中，它的where条件来源于另外一个sql，
或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。

--考虑如何获取每个客户的订单数，对每个客户进行count函数的统计计算
-- 把count()作为一个子查询
select cust_id,cust_name,
    (select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers;

关系表

表关系：表与表之间的关系
外键：
在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键
就是在一个表中的字段，代表着这个数据属于谁

了解：
    外键实现的方式，有两种：物理外键、逻辑外键
    物理外键：
        就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段
        需要在定义字段时，使用sql语句来实现
    逻辑外键：
        就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现

一对一

就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个
员工表：
    id，姓名、性别、年龄、籍贯、联系方式、学历、工龄、。。。。

由上面的一个表，拆分成两个表

员工表：
    id，姓名、联系方式、工龄、
    12  张三  1010    3
    13  李四  1020    2

详情表：
   yid 性别、籍贯、学历、、、、、
    12    男    山东 本科
    13    男    山西 本科


上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。
一个员工有着一个对应的详情信息，存储在详情表中，
在详情表中的数据，也只属于某一个员工。

一对多

在一个表中的一条数据对应着另外一个表中的多条数据
在一个表中的多条数据，对应着另外一张表中一个数据

商品分类
    id 分类名
    1  手机
    2  电脑

商品
    id 所属分类id，商品名
    1  1         小米手机
    2  1         华为手机

新闻分类
    id   分类名
    1    体育
    2    国际


新闻
    id  title             分类id
    1   国足加油              1
    2   特朗普加油            2
    3   特朗普被网民称为特没谱  2

多对多

举例：
    例如一本书，有多个标签，同时每一个标签下又对应多本书

books 图书
id  name          author
1   <跟川哥学编程>    川哥
2   <跟川哥学数据分析> 川哥
3   <川哥讲法律故事>   川哥

tags 标签
id   name
1    编程
2    计算机
3    互联网
4    法律
5    文学

从图书角度看，一本书有多个标签
1   <跟川哥学编程>    川哥   ， 编程、计算机、互联网
2   <跟川哥学数据分析> 川哥  ，  互联网、计算机
3   <川哥讲法律故事>   川哥 ，  法律

换一个角度，从标签这个角度看，一个标签包含多个图书
计算机， <跟川哥学编程>， <跟川哥学数据分析>

联结多个表
内部联结： where， inner join(join)

查询出订单号为20005的订单中购买的商品及对应的产品供应商信息


select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
    where products.vend_id = vendors.vend_id
    and orderitems.prod_id = products.prod_id
    and order_num = 20005;




改写为 join 的语法


select prod_name,vend_name,prod_price,quantity
from orderitems
inner join products on orderitems.prod_id = products.prod_id
inner join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;

## ```自联结

自联结:当前这个表与自己这个表 做联结（join）
自联结 :  是在一个sql中，用当前这个表，连接自己这个表进行关联查询
假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

```java
-- 使用子查询（嵌套查询）
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');

子查询（嵌套查询） 是目前可明确知道的 sql中运行效率最低的一种方式，尽可能不使用嵌套语句。

外部连接

# 使用 where 进行关联查询 （内部联结） 只能对两个表中相关联的数据进行查询
select customers.cust_id,orders.order_num 
from customers,orders 
where orders.cust_id = customers.cust_id;

select customers.cust_id,orders.order_num
from customers join orders
on orders.cust_id = customers.cust_id;

什么是外部联结
left join ： 是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null
right join ：是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null

以用户表为基准，去关联查询 订单表数据
select customers.cust_id,orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;




select customers.cust_id,orders.order_num
from orders right join customers
on customers.cust_id = orders.cust_id;

事务

基本事务操作

1. start transaction;/ begin;
2. commit; 使得当前的修改确认
3. rollback; 使得当前的修改被放弃
   –设置事务的隔离级别

mysql> set session transaction isolation level read uncommitted; 

-- 查看当前会话的隔离级别
select @@tx_isolation;

-- 读未提交
set session transaction isolation level read uncommitted;

-- 读已提交 READ_COMMITTED
set session transaction isolation level read committed;


-- 可重复读 REPEATABLE_READ
set session transaction isolation level repeatable read;


-- 顺序读 SERIALIZABLE
set session transaction isolation level SERIALIZABLE;

JDBC

使用步骤:

1.  引入jar文件. 
2.  加载数据库驱动 (JavaSE项目中可以省略 , JavaWeb项目必须编写此步骤)    
  Class.forName("com.mysql.jdbc.Driver"); 
3.  通过驱动管理器, 获取JDBC连接对象.    
  Connection conn = DriverManager.getConnection("数据库连接地址","帐号","密码");    
  //  数据库连接地址格式:  主协议:子协议://ip地址:端口号/数据库名称    
  //  mysql的连接地址: jdbc:mysql://localhost:3306/java35    
  //  oracle的连接地址: jdbc:oracle:thin:@localhost:1521:ORCL 
4.  通过连接对象, 创建SQL执行对象 (SQL执行环境)    
  Statement state = conn.createStatement(); 
5.  通过SQL执行对象 ,执行SQL语句.    
  state.execute(String sql语句); 
6.  释放资源
  state.close();    
  conn.close();

JDBC中常用的类型与方法

1.  DriverManager   :   驱动管理器
        常用方法:
         -   获取数据库连接:
         static Connection getConnection(String 数据库地址,String 账号 ,String 密 码)
2.  Connection  :   数据库连接对象
        常用方法:
          -   创建SQL执行对象:  Statement createStatement();
3.  Statement   :   SQL执行对象
        常用方法:
            -   执行SQL语句(查询语句返回true, 其它语句返回false)                    boolean execute(String sql);
            -   执行DML语句(INSERT UPDATE DELETE) 和 DDL语句(create alter drop)
                    (返回int值, 表示语句对数据库表格的影响行数 !)           
                    (通常我们认为 返回值>0 表示执行成功.)
                    int executeUpdate(String sql);
            -   执行DQL语句 (select)
                     ResultSet executeQuery(String sql);
4.  ResultSet   :   结果集对象 (指的是一个select语句的查询结果)
        常用方法:
            1.  控制游标移动的常用方法:
                    -   boolean next()  ****
                            作用: 控制游标向下一行移动.
                            返回值:    移动成功返回true , 下一行不存在移动失败, 返回 false

工厂方法设计模式

工厂方法模式一种创建对象的模式. 工厂方法模式基于"输入"，应用在超类和多个子类之间的情况，这种模式将创建对象的责任转移到工厂类；
工厂设计模式的优点:

1. 面向接口编程，体现了面向对象的思想
2. 降低了耦合, 将创建对象的工作转移到了工厂类

public  interface Person {
    void say();
}
public static class Student implements Person{
    @Override
    public void say() {
        System.out.println("我是学生");
    }
}
public class Teacher implements Person{
    @Override
    public void say() {
        System.out.println("我是老师");
    }
}
public static class PersonType{
    public static Person get(){
        return new Student();
    }
}

DAO

DAO(Data Access Object)是一个数据访问接口，数据访问：顾名思义就是与数据库打交道。夹在业务 逻辑与数据库资源中间。
为了建立一个健壮的Java应用，应该将所有对数据源的访问操作抽象封装在一个公共API中。用程序设计的 语言来说，就是建立一个接口，接口中定义了此应用程序中将会用到的所有事务方法。在这个应用程序中， 当需要和数据源进行交互的时候则使用这个接口，并且编写一个单独的类来实现这个接口在逻辑上对应这个 特定的数据存储。
DAO模式是标准的JavaEE设计模式之一.开发人员使用这个模式把底层的数据访问操作和上层的商务逻辑分 开.一个典型的DAO实现有下列几个组件：    
1. 一个DAO工厂类；    
2. 一个DAO接口；    
3. 至少一个实现DAO接口的具体类；    
4. 数据传递对象（有些时候叫做Bean对象）.

PreparedStatement 预编译的SQL执行环境

内部实现原理:
    1.  将未拼接参数的SQL语句, 作为SQL指令, 先传递给数据库 进行编译.
    2.  再将参数传递给数据库, 此时传递的参数不会再作为指令执行, 只会被当作文本存在.
操作流程与Statement基本一致:
    1.  如何得到一个PreparedStatement 对象
            PreparedStatement state = conn.prepareStatement("预编译的SQL语句");
    2.  预编译的SQL语句如何编写
            需要填充参数的位置, 使用?代替即可! 例如:
            select id from xzk_user where username=? and password=?
    3.  参数如何填充
            state.setXXX(int index,XXX value);
                setXXX中XXX指的是数据类型,
                参数1:    index   :   SQL语句中?的索引值 , 从1开始
                参数2:    value   :   填充的参数值.
    4.  如何执行填充完毕参数的SQL
            -   boolean execute();
            -   int executeUpdate();
            -   ResultSet executeQuery();

JAVA事务

JDBC事务通过连接对象开启, 回滚 ,提交. 只针对当前连接对象生效.
-   开启事务:   conn.setAutoCommit(false); 
-   回滚事务:   conn.rollback(); 
-   提交事务:   conn.commit();

批处理

将多条语句, 放到一起批量处理 . 批处理的原理: 将多条SQL语句, 转换为一个SQL指令.  显著的提高大量SQL语句执行时的数据库性能. 
Statement对象使用流程:
    1.  得到Statement对象
        Statement state = conn.createStatement();
    2.  将一条SQL语句, 加入到批处理中.
        state.addBatch(String sql);
    3.  执行批处理
        state.executeBatch();
    4.  清空批处理
        state.clearBatch();
         PreparedStatement对象使用流程:
    1.  得到PreparedStatement对象
        PreparedStatement state = conn.prepareStatement("预编译的SQL");
    2.  填充预编译的参数
        state.setXXX(1,填充参数);
    3.  将一条填充完毕参数的SQL, 加入到批处理中.
        state.addBatch();
    4.  执行批处理
        state.executeBatch();
    5.  清空批处理
        state.clearBatch();

Properties 作为配置文件

 Properties类 是Java中的Map集合的实现类. 
.properties文件 用于通过文件描述一组键值对! 
.properties文件 ,可以快速的转换为Properties类的对象.
文件中内容的格式:
    文件内容都是字符串 , 键与值之间通过等号连接 , 多个键值对之间换行分割.
    例如:
    url=xxx
    user=xxx
    password=xxx
如何将文件 转换为 集合:
     步骤:
        1.  创建Properties对象
                Properties ppt = new Properties();
        2.  创建一个字节输入流 , 指向.properties文件
                InputStream is = new FileInputStream("文件地址");
        3.  将字节输入流, 传递给properties对象, 进行加载                
                ppt.load(is);

DBCP连接池的使用步骤

1.  引入相关的jar文件
        -   dbcp.jar
        -   poll.jar
2.  将配置文件引入 3.  将配置文件, 转换为Properties对象
        Properties ppt = new Properties();
        ppt.load(配置文件的输入流);
4.  通过连接池的工厂类(BasicDataSourceFactory)的创建连接池的方法
(createDataSource())
        DataSource ds = BasicDataSourceFactory.createDataSource(ppt);
5.  从连接池中 获取连接对象
        Connection conn = ds.getConnection();