首次登录MySQL数据库
1. 运行 -> cmd, xxxx 为root密码:
mysql -uroot -pxxxx
2. 创建新用户,例如创建用户 scott,密码 tiger
create user 'scott'@'localhost' identified by 'tiger';
同时赋予权限
grant select, insert, update, delete, create, create view, drop, execute, references on *.* to 'scott'@'localhost';
之后可以用新的帐号连接到MySQL
mysql -uscott -ptiger
如果允许任意IP使用该帐号:
grant all privileges on *.* to 'scott'@'%' identified by 'tiger'; (但MySQL 8 这条指令报错....)
MySQL数据库操作
1. 创建数据库
create database dbName;
2. 删除数据库
drop dbName;
或者应该是: drop database dbName; 不是很确定,但这个命令有效。
3. 查看存在的所有数据库
show databases;
4. 切换到另一个数据库:
use dbName;
5. 查看当前数据库中存在的所有表
show tables;
表操作
1. 创建表举例:
创建表Course并增加记录
create table Course (
courseId char(5),
subjectId char(4) not null,
courseNumber integer,
title varchar(50) not null,
numOfCredits integer,
primary key (courseId)
);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11111', 'CSCI', '1301', 'Introduction to Java I', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11112', 'CSCI', '1302', 'Introduction to Java II', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11113', 'CSCI', '3720', 'Database Systems', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11114', 'CSCI', '4750', 'Rapid Java Application', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11115', 'MATH', '2750', 'Calculus I', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11116', 'MATH', '3750', 'Calculus II', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11117', 'EDUC', '1111', 'Reading', 3);
insert into Course(courseId, subjectId, courseNumber, title, numOfCredits)values('11118', 'ITEC', '1344', 'Database Administration', 3);
创建表student并增加记录
create table Student (
ssn char(9),
firstName varchar(25),
mi char(1),
lastName varchar(25),
phone char(11),
birthDate date,
street varchar(25),
zipCode char(5),
deptId char(4),
primary key (ssn)
);
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111110', 'Jacob', 'R', 'Smith', '9129219434', 19850409, '99 Kingston Street', '31435', 'BIOL');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111111', 'John', 'R', 'Stevenson','9129219434', null, '100 Main St.', '31435', 'BIOL');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111112', 'George', 'R', 'Smith', '9129213454', 19741010, '1200 Abercorn Street', '31435', 'CS');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111113', 'Frank', 'R', 'Jones', '9125919434', 19700909, '100 Main Street', '31435', 'BIOL');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111114', 'Jean', 'R', 'Smith', '9129219434', 19700209, '100 Main Street', '31435', 'CHEM');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111115', 'Josh', 'R', 'Woo', '7075989434', 19700209, '555 Franklin St.', '31435', 'CHEM');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111116', 'Josh', 'R', 'Smith', '9129219434', 19730209, '100 Main Street', '31435', 'BIOL');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111117', 'Joy', 'R', 'Kennedy', '9129229434', 19740309, '103 Bay Street', '31435', 'CS');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111118', 'Toni', 'R', 'Peterson', '9129229434', 19640429, '103 Bay Street', '31435', 'MATH');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111119', 'Patrick','R', 'Stoneman', '9129229434', 19690429, '101 Washington St.', '31435', 'MATH');
insert into student (ssn, firstName, mi, lastName, phone, birthDate, street, zipCode, deptId)
values ('444111120', 'Rick', 'R', 'Carter', '9125919434', 19860409, '19 West Ford St.', '31435', 'BIOL');
创建表Enrollment并增加记录
create table Enrollment (
ssn char(9),
courseId char(5),
dateRegistered date,
grade char(1),
primary key (ssn, courseId),
foreign key (ssn) references
Student(ssn),
foreign key (courseId) references
Course(courseId)
);
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111110', '11111', 20040319, 'A');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111110', '11112', 20040319, 'B');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111110', '11113', 20040319, 'C');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111111', '11111', 20040319, 'D');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111111', '11112', 20040319, 'F');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111111', '11113', 20040319, 'A');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111112', '11114', 20040319, 'B');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111112', '11115', 20040319, 'C');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111112', '11116', 20040319, 'D');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111113', '11111', 20040319, 'A');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111113', '11113', 20040319, 'A');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111114', '11115', 20040319, 'B');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111115', '11115', 20040319, 'F');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111115', '11116', 20040319, 'F');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111116', '11111', 20040319, 'D');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111117', '11111', 20040319, 'D');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111118', '11111', 20040319, 'A');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111118', '11112', 20040319, 'D');
insert into Enrollment(ssn, courseId, dateRegistered, grade)
values('444111118', '11113', 20040319, 'B');
由于MySQL命令写错了就要重写一遍,把命令写到一个脚本里再执行效率比较高:
source scriptName.sql
以上3段指令,可以分别写到3个sql脚本里再执行。
2. 删除表
drop table tableName;
3. 在表中插入一条记录
insert into tableName [(column1, column2, ..., column)] values (value1, value2, ..., valuen);
例如在数据库表Course中插入一条记录
insert into Course (courseId, subjectId, courseNumber, title, numOfCredits)values ('11113', 'CSCI', '3720', 'Database Systems', 3);
4. 在表中删除记录
delete from tableName [where condition];
5. 清空表的所有记录
delete from tableName;
6. 简单查询命令
select column-list from table-list [where condition];
例如:查找CS系所有学生:
select firstName, mi, lastName from Student where deptId = 'CS';
查询结果:
NetBeans: 使用 JDBC driver 连接 MySQL 数据库
首先,到MySQL网站下载 mysql-connector-java-8.0.11.zip,并解压:
然后在NetBean项目右键选属性, 选 ”添加 JAR/文件夹(F)“选择mysql-connector-java-8.0.11.jar,
JDBC 连 MySQL 的 code 如下:
package simplejdbc;
import java.sql.*;
/**
*
* @author alicewang
*/
public class SimpleJDBC {
/**
* @param args the command line arguments
*/
public static void main(String[] args) throws SQLException, ClassNotFoundException {
System.out.println("-------- MySQL JDBC Connection Testing ------------");
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your MySQL JDBC Driver?");
//e.printStackTrace();
return;
}
System.out.println("MySQL JDBC Driver Registered!");
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook?serverTimezone=GMT&useSSL=false" , "scott", "tiger");
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
//e.printStackTrace();
return;
}
if (connection != null) {
System.out.println("You made it, take control your database now!");
} else {
System.out.println("Failed to make connection!");
}
}
}
输出结果:
-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
You made it, take control your database now!
如果在 jdbc:mysql://localhost/javabook 后面不加 serverTimezone=GMT 和 useSSL = false,要么出现异常,要么出现警告。
Class.forName("com.mysql.jdbc.Driver"); 根据编译提示改为: Class.forName("com.mysql.cj.jdbc.Driver");
Introduction to Java Programming 10th Chapter 32.
Connect to MySQL with JDBC driver
Connecting to MySQL Using JDBC Driver