初识MySQL （2）

MySQL Day02

1. select 查询

-- group by 分组查询
-- 按照性别分组
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
group by sex; -- 如何分组

-- 按照性别分组，找出人数大于5的结果
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
group by sex -- 如何分组
having count(1) > 5; -- 分组之后条件约束

-- 需按照性别分组，但是需要找出年龄大于35岁的数据
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
where age > 35 -- 分组之前条件约束
group by sex; -- 如何分组

-- 需按照性别分组，但是需要找出年龄大于35岁的数据，并且人数大于5人
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
where age > 35 -- 分组之前约束查询添加
group by sex -- 按照性别分组
having count(1) > 5; -- 分组之后条件约束

-- 排序查询
-- order by
-- asc 升序 desc 降序
-- 年龄升序
mysql > select *
from person
order by age asc;

-- 工资降序
mysql > select *
from person
order by salary desc ;

-- 第一条件是年龄升级，如果年龄一致，工资也升序
mysql > select *
from person
order by age asc, salary asc;

2. 数据约束【重点】

2.1 默认值

-- default
-- 当前数据表中对应字段在没有赋值的情况下，存在一个默认值
mysql > create table person1
(
    id int,
    name varchar(30),
    country varchar(30) default '河南'
);

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| country | varchar(30) | YES  |     | 河南    |       |
+---------+-------------+------+-----+---------+-------+
-- 插入数据，但是默认数据不做任何操作
mysql > insert into person1(id, name) value(1, '杰克');

-- 插入数据，带有默认值的数据给予当前所需数据
mysql > insert into person1(id, name, country) value(2, '杰瑞', '地球');

-- 插入数据，带有默认值的数据给予null
mysql > insert into person1(id, name, country) value(3, 汤姆', null);

2.2 非空

-- not null
-- 要求当前字段不能为空
mysql > create table person2
(
    id int not null,
    name varchar(30) not null,
    info text
);
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 插入数据
mysql > insert into person2 value(1, '熊大', '熊二他哥');

-- 没有给予ID赋值，导致报错！！！
mysql > insert into person2(name, info) value('李白', '青莲剑歌');
-- ERROR 1364 (HY000): Field 'id' doesn't have a default value

-- 没有给予name赋值，导致报错
mysql > insert into person2(id, info) value(1, '青莲剑歌');
-- ERROR 1364 (HY000): Field 'name' doesn't have a default value

-- ID，name使用not null修饰之后，不能给予null
mysql > insert into person2(id, name, info) value(null, null, '随便');
-- ERROR 1048 (23000): Column 'id' cannot be null

2.3 唯一

-- unique
-- 唯一
mysql > create table person3
(
    id int unique,
    name varchar(30) not null,
    info text
);

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 插入数据
mysql > insert into person3(id, name, info) value(1, '杰克', '狂炫酷帅叼');

-- 插入数据 重复ID无法添加到当前数据表中
mysql > insert into person3(id, name, info) value(1, '杰瑞', '抓不住的耗子');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'id'

-- 唯一索引可以是null, 而且在数据参考中，null不作为唯一索引的约束条件
mysql > insert into person3(id, name, info) value(null, '灰太狼', '好惨一只狼');
mysql > insert into person3(id, name, info) value(null, '迪迦', '童年的回忆');

2.4 主键

-- 非空 + 唯一
-- primary key
mysql > create table person4
(
    id int primary key,
    name varchar(30),
    info text
);

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 插入数据
mysql > insert into person4 value(1, '盲僧', '闪现锤空气');

-- 插入数据，但是ID重复，无法添加
mysql > insert into person4 value(1, '哪吒', '闹海');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

-- 插入数据，但是ID为null，无法添加
mysql > insert into person4 value(null, '美猴王', '不许叫我弼马温');
-- ERROR 1048 (23000): Column 'id' cannot be null

2.5 自增长

-- auto_increment
-- 实现数据自增长
-- 要求自增长数据是需要主键约束，或者唯一约束
mysql > create table person5
(
    id int primary key auto_increment,
    name varchar(30) not null,
    info text
);

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
| info  | text        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 插入数据 非指定ID数据，ID会随着自增长计数，给予对应ID值
mysql > insert into person5(name, info) value('郭德纲', '流氓头子');
mysql > insert into person5(name, info) value('于谦', '相声皇后');
-- 指定ID值之后，自增长会随之改变
mysql > insert into person5(id, name, info) value(3, '高峰', '德云社总教习');
mysql > insert into person5(id, name, info) value(4, '栾云平', '怼怼');
mysql > insert into person5(name, info) value('岳云鹏', '贱贱');
mysql > insert into person5(id, name, info) value(10, '孙越', '把大象养瘦的人');
mysql > insert into person5(name, info) value('郭麒麟', '少班主');
mysql > insert into person5(id, name, info) value(9, '阎鹤祥', '壮壮');
mysql > insert into person5(name, info) value('朱云峰', 'SB');

-- delete操作不会影响自增长值
mysql > delete from person5;
mysql > insert into person5(name, info) value('小李子', '岁月是把杀猪刀');

-- truncate 清空整个数据表数据，并且自增长【重置】
mysql > truncate person5;
mysql > insert into person5(name, info) value('强森', '可以打我十个');

2.6 外键

-- 设计数据表
mysql > create table employee
(
    id int primary key auto_increment,
    name varchar(30) not null,
    deptName varchar(30) not null
);

-- 插入数据
insert into employee(name, deptName) value('杰克', 'JavaEE开发部');
insert into employee(name, deptName) value('杰瑞', 'JavaEE开发部');
insert into employee(name, deptName) value('汤姆', '帅部');
insert into employee(name, deptName) value('爱丽丝', '恐部');
insert into employee(name, deptName) value('喜羊羊', '帅部');
insert into employee(name, deptName) value('小鲤鱼泡泡', 'iOS开发部');
insert into employee(name, deptName) value('沸羊羊', '黑部');
insert into employee(name, deptName) value('磁力超人', '也黑部');
insert into employee(name, deptName) value('美羊羊', '美部');
insert into employee(name, deptName) value('费玉清', '污部');

-- 删除原数据表
drop table employee;

-- 设计两张数据表
-- 员工表 【从表】
mysql > create table employee
(
    id int primary key auto_increment,
    name varchar(30) not null, 
    deptId int not null
);

-- 部门表 【主表】
mysql > create table dept
(
    id int primary key auto_increment,
    deptName varchar(30) not null
);

-- 插入部门数据
insert into dept(deptName) value('JavaEE研发部');
insert into dept(deptName) value('帅部');
insert into dept(deptName) value('恐部');
insert into dept(deptName) value('iOS研发部');
insert into dept(deptName) value('黑部');

-- 插入员工数据
insert into employee(name, deptId) value('杰克', 1);
insert into employee(name, deptId) value('杰瑞', 1);
insert into employee(name, deptId) value('汤姆', 2);
insert into employee(name, deptId) value('爱丽丝', 3);
insert into employee(name, deptId) value('喜羊羊', 2);
insert into employee(name, deptId) value('小鲤鱼泡泡', 4);
insert into employee(name, deptId) value('沸羊羊', 5);
insert into employee(name, deptId) value('磁力超人, 5);

-- 目前存在问题，当前employee员工数据添加的过程中，deptId不存在，依然可以添加成功！！！【不合适】
insert into employee(name, deptId) value('小马宝莉', 10);

-- 删除原数据表
drop table employee;
drop table dept;

-- 设计带有【外键约束】两张数据表
-- 部门表 【主表】
mysql > create table dept
(
    id int primary key auto_increment,
    deptName varchar(30) not null
);

-- 插入部门数据
insert into dept(deptName) value('JavaEE研发部');
insert into dept(deptName) value('帅部');
insert into dept(deptName) value('恐部');
insert into dept(deptName) value('iOS研发部');
insert into dept(deptName) value('黑部');

-- 员工表 【从表】
mysql > create table employee
(
    id int primary key auto_increment,
    name varchar(30) not null, 
    deptId int not null,
    -- 声明外键约束
    -- fk_emp_dept 外键名
    -- foreign key (deptId) 表示从表中哪一个字段作为外键约束字段选择字段为deptId
    -- references dept(id) 表示主表中哪一个字段作为外键约束目标字段，选择主表dept中id字段为目标字段
    constraint fk_emp_dept foreign key (deptId) references dept(id)
);

-- 插入员工数据
insert into employee(name, deptId) value('杰克', 1);
insert into employee(name, deptId) value('杰瑞', 1);
insert into employee(name, deptId) value('汤姆', 2);
insert into employee(name, deptId) value('爱丽丝', 3);
insert into employee(name, deptId) value('喜羊羊', 2);
insert into employee(name, deptId) value('小鲤鱼泡泡', 4);
insert into employee(name, deptId) value('沸羊羊', 5);
insert into employee(name, deptId) value('磁力超人, 5);

-- 从表添加数据，主表中不存在对应的deptId, 添加失败
insert into employee(name, deptId) value('小马宝莉', 10);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`javaee1911`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

-- 主表无法删除被从表已经外键约束的数据行
delete from dept where id = 5;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`javaee1911`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

-- 主表不能修改被从表已经使用的数据表关键数据，外键约束目标字段数据
update dept set id = 10 where id = 5; 
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`javaee1911`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

-- 可以修改和没有外键约束的非目标字段数据
update dept set deptName = '包青天部' where id = 5;

2.7 级联操作

-- 在外键使用的情况下，更好的操作主表数据，而且从表会随着修改对应的数据
-- 级联删除 主表数据删除，从表对应使用该外键约束目标字段数据行随着删除
-- on delete cascade

-- 级联修改 主表修改外键约束目标字段数据，从表中所有使用该数据的数据行全部修改
-- on delete cascade

-- 删除原数据
-- 删除原数据表
drop table employee;
drop table dept;

-- 设计带有【外键约束】两张数据表
-- 部门表 【主表】
mysql > create table dept
(
    id int primary key auto_increment,
    deptName varchar(30) not null
);

-- 插入部门数据
insert into dept(deptName) value('JavaEE研发部');
insert into dept(deptName) value('帅部');
insert into dept(deptName) value('恐部');
insert into dept(deptName) value('iOS研发部');
insert into dept(deptName) value('黑部');

-- 员工表 【从表】
mysql > create table employee
(
    id int primary key auto_increment,
    name varchar(30) not null, 
    deptId int not null,
    -- 声明外键约束
    -- fk_emp_dept 外键名
    -- foreign key (deptId) 表示从表中哪一个字段作为外键约束字段选择字段为deptId
    -- references dept(id) 表示主表中哪一个字段作为外键约束目标字段，选择主表dept中id字段为目标字段
    constraint fk_emp_dept foreign key (deptId) references dept(id)
    on delete cascade -- 级联删除
    on update cascade -- 级联修改
);

-- 插入员工数据
insert into employee(name, deptId) value('杰克', 1);
insert into employee(name, deptId) value('杰瑞', 1);
insert into employee(name, deptId) value('汤姆', 2);
insert into employee(name, deptId) value('爱丽丝', 3);
insert into employee(name, deptId) value('喜羊羊', 2);
insert into employee(name, deptId) value('小鲤鱼泡泡', 4);
insert into employee(name, deptId) value('沸羊羊', 5);
insert into employee(name, deptId) value('磁力超人, 5);

-- 修改主表操作
update dept set id = 10 where id = 5;

-- 删除主表操作
delete from dept where id = 3;

3. JDBC

3.1 JDBC介绍

Java连接数据库的规范
	SUN公司制定的数据库连接接口
	java.sql.*
	javax.sql.*

SUN要求数据库软件提供商，需要对应Java程序，完成对应接口，实现对应方法，让Java程序可以连接数据库。

3.2 JDBC连接数据库所需资源

cmd > mysql -hlocalhost -uroot -p123456

1. 明确告知连接的数据库是MySQL数据库
2. MySQL数据库所处主机地址，ip地址，域名
3. User用户名
4. 密码

JDBC连接连接数据库
	JDBC连接数据库使用的URL
	URL jdbc:mysql://localhost:3306/javaee1911
	user root
	password 123456

需要准备MySQL数据库提供给Java程序的第三方程序Jar包
	mysql-connector-java-5.1.47.jar

3.3 JDBC连接数据库

package com.qfedu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Demo1 {
    public static void main(String[] args)
            throws ClassNotFoundException, SQLException {
        /*
        1. 加载驱动
         */
        Class.forName("com.mysql.jdbc.Driver");

        /*
        2. 准备必要的数据看连接资源
         */
        String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
        String user = "root";
        String password = "123456";
        
        /*
        3. 通过JDBC规范中的DriverManager 驱动管理类，获取数据库连接对象Connection
         */
        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println(connection);

        /*
        4. 关闭数据库连接
         */
        connection.close();
    }
}

3.4 JDBC核心API【能记住最好，记不住拉倒】

class java.sql.DriverManager 驱动管理类
--| static java.sql.Connection getConnection(String url, String user, String password);
	根据提供的JDBC URL信息和对应用户名密码连接数据库，获取数据库连接对象java.sql.Connection对象;

interface java.sql.Connection 数据库连接接口
--| java.sql.Statement createStatement(); 
	获取数据库SQL语句搬运工对象。同时也是一个资源对象。
--| java.sql.PreparedStatement PrepareStatement(String sql);
	获取数据库SQL语句【预处理】搬运工对象，预处理操作可以解决一些【SQL注入问题】，以及提高一定的数据库效率


interface java.sql.Statement 数据库SQL语句搬运工接口
--| int executeUpdate(String sql);
	执行创建，添加，修改，删除等对于数据库数据进行修改的SQL，返回值是当前修改操作对应数据库数据库影响行数。 CREATE DELETE UPDATE INSERT
--| java.sql.ResultSet executeQuery(String sql);
	执行查询SELECT SQL语句，返回值是java.sql.ResultSet 结果集对象。

        
java.sql.PreparedStatement 数据库【预处理】SQL语句搬运工接口
--| int executeUpdate();
	执行创建，添加，修改，删除等对于数据库数据进行修改的SQL，返回值是当前修改操作对应数据库数据库影响行数。 CREATE DELETE UPDATE INSERT
--| java.sql.ResultSet executeQuery();
	执行查询SELECT SQL语句，返回值是java.sql.ResultSet 结果集对象。
--| void set(int parameterIndex, Object value);
	处理【预处理SQL语句】中对应的参数占位符！！！
        
java.sql.ResultSet 数据库结果集接口
--| XXX getXXX(int ColumnIndex);
	根据查询结果集字段下标顺序获取对应的字段数据
--| XXX getXXX(String ColumnName);
	根据查询结果集字段名获取对应的字段数据
     
ORM思想
后期Java操作数据库所需考虑的内容        
1. SQL语句和对应参数
2. 执行对应的update或者query方法