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

本文主要是介绍专业实习第二天 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或不写，降序：DESCSELECT * 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..whereSELECT 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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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");}}
}