Java编程基础31——MySql数据库

一.关系型数据库概述

1.数据库定义和特点

• 关系型数据库：是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
• RDBMS即关系数据库管理系统(Relational Database Management System)的特点：
  1.数据以表格的形式出现
  2.每行为各种记录名称
  3.每列为记录名称所对应的数据域
  4.许多的行和列组成一张表单
  5.若干的表单组成database

2.RDBMS 术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

3.MySQL数据库

概念

• MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

特点

• MySQL 是开源的，所以你不需要支付额外的费用。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的SQL数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
• MySQL 对PHP有很好的支持，PHP是目前最流行的Web开发语言。
• MySQL 支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了GPL协议，你可以修改源码来开发自己的 MySQL 系统。

安装

• a.解压后在该文件夹下创建 my.ini 配置文件，编辑 my.ini 配置以下基本信息：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=E:\MySQL\mysql-8.0.12

# 设置mysql数据库的数据的存放目录
datadir=E:\MySQL\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3307
default-character-set=utf8

• b：初始化数据库：使用管理员打开cmd，在 bin 目录下执行命令：mysqld --initialize --console
• c：安装服务：mysqld --install [服务名]
• d：启动服务：net start mysql，net stop mysql停止服务，通过命令sc delete MySQL/mysqld -remove卸载 MySQL 服务
• e：登陆数据库：mysql -u root -p 输入上面初始化生成的密码。
• f：更改密码：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

二.MYSQL的SQL语言

• SQL：Structure Query Language结构化查询语言
• DDL:数据定义语言，定义数据库、数据表它们的结构：create(创建) drop(删除) alter(修改)
• DML:数据操作语言，主要是用来操作数据insert(插入) update(修改) delete(删除)
• DCL:数据控制语言，定义访问权限，取消访问权限，安全设置grant
• DQL:数据查询语言，select(查询) from字句 where字句

三.数据库CRUD的操作

 --1.首先要登陆数据库服务器
    mysql -uroot -p123456
--2.数据库的创建 : 
    create database 数据库的名 character set 字符集  collate 校对规则
    create database demodb character set utf8 collate utf8_bin;
--3.数据库的查询:  
    show databases;
    select database();    //正在使用的
    show create database 数据库的名字;
--4.数据库的修改: 
    alter database 数据库 character set 字符集(utf8);
--5.数据库的删除: 
    drop database 数据库名
--6.切换数据库 :
    use 数据库的名字;

四.表的操作

1.创建表

create table 表名（
    列名 列的类型(长度) 约束，
    sid int primary key auto_increment,  
    sname varchar(30),
    sex int,
    age int
）;

--列的类型：
    int
    char（固定长度）/varchar（可变长度），长度代表的是字符的个数
    double
    date:YYYY-MM-DD
    time:hh:mm:ss    默认值是null
    timestamp:YYYY-MM-DD hh:mm:ss默认使用当前时间   
    text:主要是存放文本
    blob:存放的是二进制
                
--列的约束：
    主键约束：primary key
    唯一约束：unique
    非空约束：not null  
    自动增长: auto_increment

2.查看表

--查看所有的表
    show tables;
--查看表的定义
    show create table 表名;
--查看表结构
    desc 表名;

3.修改表

--修改表名和字符集
    rename table 旧表名 to 新表名;
    alter table 表名 character set gbk;
--修改列
    添加列: alter table 表名 add 列名 列的类型 列的约束(not null);
    删除列: alter table 表名 drop chengji;
    改类型: alter table 表名 modify 列名 新的类型 新的约束(not null);
    改列名: alter table 表名 change 旧列名 新列名 varchar(2);

4.删除表

drop table allidea;

五.表中数据的CRUD的操作

1.增删改数据

--1.插入数据
    insert into 表名(列名1,列名2,列名3) values(值1,值2,值3),(值1,值2,值3); //全插入时列名可以省略
    --中文乱码问题
        暂停mysql服务,在mysql安装路径中找到my.ini配置文件，将57行改成gbk，重启mysql.
--2.删除记录
    delete from 表名 where 条件;
    delete from demodb where sid = 5;
    --delete和truncate 删除数据有什么差别
        delete: DML一条一条删除表中的数据，适合数据少，高效
        truncate:DDL 先删除表再重建表，适合数据比较多
--查看表中的数据
    select * from allidea;
--3.更新表记录
    update 表名 set 列名1=列的值1，列名2=列的值2 where 条件;
    update student set sname='李四' where sid=5;

4.查询记录

• select:选择显示哪些列的内容

--商品分类（栏目）
1.分类的id  
2.分类名称  
3.分类描述    

create table category(      
    cid int primary key auto_increment,      
    cname varchar(12),      
    cdesc varchar(31)
);    
insert into category values(null,'手机数码','中国制造');  
insert into category values(null,'皮鞋箱包','意大利');  
insert into category values(null,'服装内衣','中国制造');  
insert into category values(null,'烟酒食品','中国制造'); 

--所有商品（货品）
1.商品ID
2.商品名称
3.商品价格
4.生产日期
5.商品分类ID

--商品和商品分类：所属关系
create table product(
    pid int primary key auto_increment,
    pname varchar(10),
    price double,
    pdate timestamp,
    cno int
);

insert into product values(null,'小米手机',998,null,1);
insert into product values(null,'华为手机',2888,null,1);
insert into product values(null,'贵人鸟',399,null,2);
insert into product values(null,'杰克琼斯',500,null,3);
insert into product values(null,'海澜之家',500,null,3);
insert into product values(null,'茅台酒',388,null,4);
insert into product values(null,'西凤酒',128,null,4);
insert into product values(null,'饼干',5,null,4);

--查询所有的商品
    select * from product;
    
--查询商品名称和商品价格
    select pname,price from product;
    
--别名查询. as的关键字，as 关键字是可以省略
    --表别名(主要是用在多表查询);
        select p.pname,p.price from product as p;
    --列别名;
        select pname 名称,price 价格 from product;
        
--去掉重复的值 distinct
    select distinct price from product;
    
--select运算查询(仅仅在查询结果上做了运算)
    select *,price*0.5 折后价格 from product;
    
--where关键字
    --关系运算符:> >= < <= != <>(标准sql语法)
    --判断某一列是否为空: is null,is not null
    --逻辑运算: and , or , not
    --between ... and ...在某一区间
    --like:模糊查询
        _ :代表的是一个字符
        % :代表的是多个字符
    --in 在某个范围中获得值     
    --查询商品价格>60的所有商品信息
        select * from product where price > 60;
    
    --查询商品价格在10到100之间
        select * from product where price > 10 and price < 100;
        between...add...
        select * from product where price between 10 and 100;
    --查询商品价格小于 100 或者商品价格大于 999
        select * from product where price < 100 or price > 999;
        
    --查询出名字中带有 米 的商品
        select * from product where pname like '%米%';
    
    --查询第二个名字是熊的所有商品
        select * from product where pname like '_便%';
    
    --查询商品分类ID在1,4,5里面所有的商品
        select * from product where cno in (1,4);
        
--排序查询: order by 关键字
    asc : ascend 升序(默认的排序方式)
    desc : descend 降序
    --1.查询所有商品,按照价格进行排序
        select * from product order by price;
    --2.查询所有商品,按照价格进行降序
        select * from product order by price desc;
    --3.查询名称有 小 的商品,按照价格降序排序
        select * from product where pname like '%面%' order by price desc;
        
--聚合函数: select 函数(列名) from 表名;
    sum() : 求和
    avg() : 求平均值
    count() : 统计数量
    max() : 最大值
    min() : 最小值
    --1.获得所有商品价格的总和
        select sum(price) from product;
        
    --2.获得所有商品的个数
        select count(*) from product;
        
    --注意:where条件后面不能接聚合函数
    --查出商品价格 大于 平均价格 的所有商品
        select * from product where price > (select avg(price) from product);
--分组: group by
    --having 分组之后条件过滤关键字,可以接聚合函数,出现在分组之后
    --1.根据cno字段分组,分组后统计商品的个数
        select cno,count(*)
        from product group by cno;
    --2.根据con分组,分组统计每组商品的平均价格,并且商品平均价格 > 60
        select cno,avg(price)
        from product group by cno
        having avg(price) > 60;
        
--编写顺序
    select .. from .. where .. group by .. having .. order by
    
--执行顺序
    from .. where .. group by .. having .. select .. order by