专业实习第二天 MySql5.0安装+SQLyog+数据库基本语句操作+java实现数据库连接

基本语句—增删改查

##选中要执行的语句，F8执行
#删除数据库 
#第一句写这个可以重复执行
DROP DATABASE IF EXISTS School;

#创建数据库
CREATE DATABASE School;

#使用数据库
USE School;

#创建表
CREATE TABLE Student
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    hobby VARCHAR(20) NOT NULL
);

INSERT INTO Student VALUES(NULL,'老大',8,'蜂蜜');
INSERT INTO Student VALUES(NULL,'老二',7,'光头强');
INSERT INTO Student VALUES(NULL,'老三',20,'蓝球');
INSERT INTO Student VALUES(NULL,'老师',21,'污');
INSERT INTO Student VALUES(NULL,'老五',99,'足球');

#查询
SELECT * FROM Student;

##############################我是分割线####################################

#添加数据
INSERT INTO Student VALUES(NULL,'走石飞沙',21,'学习');

#删除
DELETE FROM student; #数据全部删除，自增列不受影响

DELETE FROM Student WHERE NAME = '老师'; #删除某条记录，自增列不受影响

TRUNCATE TABLE Student; #删除全部数据，重置表，自增列恢复原值

#修改
UPDATE Student SET hobby = '段子',age = 45 WHERE id = 4 #修改多个列用逗号分隔

##################################我是分割线#################################

#查询数据

SELECT * FROM Student; #查询全部列

SELECT id ,NAME,age,hobby FROM Student

#条件查询

SELECT * FROM Student WHERE NAME = '老师'; #多个条件查询使用AND(并且)或者OR(或者)查询


#排序查询     ORDER BY    升序：ASC或不写，降序：DESC

SELECT * FROM Student ORDER BY age DESC;

外键+多表查询

DROP DATABASE IF EXISTS BookDB;
CREATE DATABASE BookDB;
USE BookDB;
CREATE TABLE TYPE(
    tid INT PRIMARY KEY AUTO_INCREMENT,
    tname VARCHAR(20) NOT NULL
);

CREATE TABLE Book(
    bid INT PRIMARY KEY AUTO_INCREMENT,
    bname VARCHAR(20) NOT NULL,
    bprice INT NOT NULL,
    bdesc VARCHAR(20) NOT NULL,
    tid INT NOT NULL,
    #外键
    FOREIGN KEY(tid) REFERENCES TYPE(tid)
);
#另外一种外键
#alter table Book add CONStraint fk_tid FOREIGN key (tid) REFERENCES type(tid);

INSERT INTO TYPE VALUE(NULL,'chinese');
INSERT INTO TYPE VALUE(NULL,'english');

INSERT INTO Book VALUE(NULL,'book1',10,'desc1',1);
INSERT INTO Book VALUE(NULL,'book2',20,'desc2',1);
INSERT INTO Book VALUE(NULL,'book3',20,'desc3',1);
INSERT INTO Book VALUE(NULL,'book4',50,'desc4',2);
INSERT INTO Book VALUE(NULL,'book5',30,'desc5',2);

SELECT * FROM TYPE;
SELECT * FROM Book;

#多表查询
#select..where

SELECT b.bid,b.bname,b.bprice,b.bdesc,t.tname
FROM TYPE t,Book b
WHERE t.tid = b.tid;

# inner..join
SELECT b.bid,b.bname,b.bprice,b.bdesc,t.tname
FROM TYPE t INNER JOIN Book b
ON t.tid = b.bid;

连接数据库小实现—–上面第一个School数据库连接测试使用

1.创建学生类

public class Student {
    private int id;
    private String name;
    private int age;
    private String hobby;
    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 int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getHobby() {
        return hobby;
    }
    public void setHobby(String hobby) {
        this.hobby = hobby;
    }
    public Student(int id, String name, int age, String hobby) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.hobby = hobby;
    }
    public Student() {
        super();
    }

}

2.数据库基本工具类

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

public class BaseDao {
    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/School";
    private String name = "root";
    private String pwd = "123456";

    public Connection getCon(){
        Connection con = null;

            try {
                Class.forName(driver);
                con = DriverManager.getConnection(url,name,pwd);
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        return con;
    }

    public void closeAll(Connection con,PreparedStatement ps,ResultSet rs){
        try {
            if(rs != null){
                rs.close();
            }
            if(ps != null){
                ps.close();
            }
            if(con != null){
                con.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    Connection con = null;
    PreparedStatement ps = null;

    //万能正删改方法
    public int execute(String sql,Object ... param){
        int result = 0;
        try {
            con = this.getCon();
            ps = con.prepareStatement(sql);
            int i = 1;
            for(Object obj : param) {
                ps.setObject(i, obj);
                i++;
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            this.closeAll(con, ps, null);
        }
        return result;
    }
}

3.学生工具类

public class StudentDao extends BaseDao {
    public int add(Student stu){
        String sql = "insert into Student values(null,?,?,?)";
        return this.execute(sql,stu.getName(),stu.getAge(),stu.getHobby());
    }

    public int update(Student stu){
        String sql = "update Student set name = ?,age = ?,hobby = ? where id = ?";
        return this.execute(sql,stu.getName(),stu.getAge(),stu.getHobby(),stu.getId());
    }

    public int delete(int id){
        String sql = "delete from Student where id = ?";
        return this.execute(sql, id);
    }
}

4.测试

public class Test {
    public static void main(String[] args) {
        StudentDao sd = new StudentDao();

        //int result = sd.add(new Student(0,"老八",10,"游戏"));
        //int result = sd.update(new Student(8,"老八",55,"game"));
        int result = sd.delete(8);

        if(result == 1){
            System.out.println("success");
        }
        else {
            System.out.println("error");
        }
    }
}

小知识 & 快捷键

1. 导入mysql jar包 —>>>项目名右键然后3 3 2，左边第三个，中间第三个，右边第二个
2. 异常快捷键 alt+shift+z 松开摁y
3. gets and sets alt+shift+s 选择最大快的第二个
4. 构造方法 alt+shift+s 最大块倒数第二个 全选为含参构造 全不选为无参构造
5. 导包 ctrl+shift+O
6. 整理代码 ctrl+shift+F
7. 快捷提示 lat+/
8. 删除一行 ctrl+D