数据库一

数据库基础

1、基本增删改查

1、查询

SELECT 字段名 FROM 表名;
SELECT * FROM good;
查询商品表中的所有字段
-- 注：*表示所有字段名，可查询表的所有内容

2、添加数据

INSERT INTO 表名(字段列表) VALUES(？,？,？)
-- 建一个商品表
CREATE TABLE good (
    id INT(10),
    g_name VARCHAR(50),
    price DECIMAL 
);
-- 新增一个牙膏商品.
INSERT INTO good (id,g_name,price) VALUES (1,'牙膏',25);

3、删除数据

DELETE FROM 表名 WHERE 条件;
-- 根据商品名删除牙膏商品.
DELETE FROM good WHERE g_name='牙膏';
-- 删除商品表.

4、修改数据

UPDATE 表名 SET 进行的修改 WHERE 条件
-- 根据商品名修改牙膏的价格.
UPDATE good SET price=30 WHERE g_name='牙膏'; 

2、分组查询

SELECT 聚合函数(展示字段),其它展示字段
FROM 表名
GROUP BY 字段名
HAVING 判断;

3、子查询

SELECT 展示字段 FROM 主表名 
WHERE 主表字段 IN 
(SELECT 从表字段 FROM 从表名
WHERE 条件);

SELECT 展示字段 FROM 主表名 
WHERE EXISTS
(SELECT 从表字段 FROM 从表名
WHERE 条件 AND 主表名.主表字段=从表名.从表字段);

4、联表查询

1、内联查询

SELECT 展示字段
FROM 表1名
INNER JOIN 
表2名
ON 判断;
-- 内联查询根据判断条件，展示出两张表符合条件的共有数据

2、外联查询

-- 左联和右联等价，只是表的顺序不同
SELECT 展示字段
FROM 表1名
LEFT JOIN
表2名
ON 判断;
-- 左/右联查询根据判断条件，展示出两张表符合条件的共有数据以及左/右表的剩余所有数据

1、查询所有参加过英语考试的学员全字段信息

1、子查询

SELECT * FROM student 
WHERE id IN 
(SELECT stu_id FROM score
WHERE c_name='英语');

或者

SELECT * FROM student 
WHERE EXISTS
(SELECT stu_id FROM score
WHERE c_name='英语'AND student.id=score.stu_id);

2、联表查询

SELECT stu.*
FROM student stu
INNER JOIN 
score sc
ON sc.c_name='英语'
AND stu.id=sc.stu_id;

2、查询各科平均成绩（只显示85分以上）

SELECT c_name,AVG(grade)
FROM score
GROUP BY c_name
HAVING AVG(grade)>85;

3、查询各学号的平均成绩

SELECT stu_id 学号,AVG(grade) 平均成绩
FROM score
GROUP BY stu_id;

4、在score表成绩单中 同时查询出每一行数据的学员姓名

SELECT sc.*,stu.`name` 姓名
FROM score sc
LEFT JOIN
student stu
ON sc.stu_id=stu.id;

5、接第3题，在各行的学号后，增加显示姓名 [分组+联表]

SELECT sc.stu_id 学号,stu.`name` 姓名,AVG(sc.grade) 平均分
FROM score sc
LEFT JOIN
student stu
ON sc.stu_id=stu.id
GROUP BY sc.stu_id;

6、接第四题 需要学员数据全选 用什么联 需要成绩记录数据全选[哪怕有些成绩记录不是学员表内的学员考出来的] 用什么联

1、学员数据全选

SELECT sc.*,stu.`name` 姓名
FROM student stu
LEFT JOIN
score sc
ON sc.stu_id=stu.id;

2、成绩记录全选

SELECT sc.*,stu.`name` 姓名
FROM score sc
LEFT JOIN
student stu
ON sc.stu_id=stu.id;

左右联可以互换，JOIN前面的表全选用左联，JOIN后面的表全选用右联。

在java中操作数据库

首先创建一个学生类保存学生的属性

import java.text.SimpleDateFormat;
import java.util.Date;

public class Student {
    private SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
    private int id;
    private String name;
    private String sex;
    private Date birth;
    private String department;
    private String address;

    public Student() {
    }

    public Student(int id, String name, String sex, Date birth, String department, String address) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birth = birth;
        this.department = department;
        this.address = address;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return id + "\t\t" + name + "\t" + sex + "\t\t" + sd.format(birth) + "\t" + department;
    }
}

导入mysql官方jar包，利用jar包访问数据库，并执行操作语句

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class MainClass {
    public static void main(String[] args) {
        SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
        
        List<Student> stuList = new ArrayList<>();
        try {
            //反射驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获得连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/20190805", "root", "123456");
            //创建statement
            Statement stmt = conn.createStatement();
            //执行sql
            ResultSet rs = stmt.executeQuery("select * from student");
            //将学生属性注入学生对象，并保存至学生集合中
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String birth = rs.getString("birth");
                String department = rs.getString("department");
                String address = rs.getString("address");
                Date birthDate = sd.parse(birth);
                Student stu = new Student(id, name, sex, birthDate, department, address);
                stuList.add(stu);
            }
            System.out.println("学号\t姓名\t性别\t出生日期\t\t院系");
            for (Student stu : stuList) {
                System.out.println(stu);
            }
        } catch (ClassNotFoundException | SQLException | ParseException e) {
            e.printStackTrace();
        }
    }
}