MySQL数据库day20190922
1.dos命令
set names gbk;
2.MySQL练习
#创建school数据库:
create database school;
#切换school数据库:
use school;
# primary key : 主键约束,不可重复
# auto_increment : 自动增长
# not null : 非空约束
# default : 默认约束
# unique : 唯一约束,不可重复
# comment : 注释
# foreign key : 外键约束
/*
在从表中直接定义外键约束语法:
foreign key(从表中的外键) references 主表(主表主键)
*/
#创建学生表,该学生表的数据存储引擎是MyISAM:
create table if not exists student(
studentno int(4) primary key auto_increment not null comment '学号',
loginpwd varchar(20) not null comment '密码',
studentname varchar(20) not null comment '学生姓名',
sex char(2) default '男' not null comment '性别',
gradeId int(11) not null comment '年级编号',
phone varchar(50) comment '联系方式,允许为空',
address varchar(255) comment '地址,允许为空',
borndate datetime not null comment '出生日期',
email varchar(50) comment '邮箱账号,允许为空',
indentityCard varchar(18) unique comment '身份证号',
foreign key(gradeId) references grade(gid)
)comment='学生表' engine='MyISAM';
#创建学生表,默认不写,该学生表的数据存储引擎是InnoDB:
create table if not exists student(
studentno int(4) primary key auto_increment not null comment '学号',
loginpwd varchar(20) not null comment '密码',
studentname varchar(20) not null comment '学生姓名',
sex char(2) default '男' not null comment '性别',
gradeId int(11) not null comment '年级编号',
phone varchar(50) comment '联系方式,允许为空',
address varchar(255) comment '地址,允许为空',
borndate datetime not null comment '出生日期',
email varchar(50) comment '邮箱账号,允许为空',
indentityCard varchar(18) unique comment '身份证号',
foreign key(gradeId) references grade(gid)
)comment='学生表';
##添加一条测试数据
insert into student(loginpwd,studentname,sex,gradeId,borndate) values('123','张三','男',1,'2000-10-10');
insert into student(loginpwd,studentname,sex,gradeId,borndate) values('123','张无','女',10,now());
##创建年级表
create table if not exists grade(
gid int(11) primary key auto_increment not null comment '主键,年级编号',
gname varchar(32) comment '年级名称'
);
## 添加测试数据
insert into grade(gname) values('S1');
insert into grade(gname) values('S2');
3.当前MySql数据库默认的存储引擎是InnoDB
查看当前数据库所有存储引擎:
show engines;
查看某张表的存储引擎:
show table status from 数据库名 where name='指定表名称';
MySQL
#作业：新建subject表
create table if not exists subject(
subjectno int(11) comment '课程编号',
subjectname varchar(50) not null comment '课程名称',
classhour int(4) not null comment '学时',
gradeid int(4) not null comment '年级编号'
)comment='课程表' engine='MyISAM';
清楚定时器：clearTimeout(timeOut[i]);
一.DML语句: 数据操作语言
1.1. insert语法:
新增单条数据语法:
insert into 表名[(字段1,字段2,...,字段n)] values('值1','值2',...,'值3');
新增多条数据语法:
inser into 表名[(字段1,字段2,...,字段n)]
values('值1','值2',...,'值3'),
('值1','值2',...,'值3'),
('值1','值2',...,'值3');
新增测试数据:
insert into grade(gname) values('S1');
insert into grade(gname) values('S2'),('Y2'),('课工场'),('UI');
insert into grade(gname) values('在dos命令添加中文数据,出现中文添加失败在dos命令添加中文数据,出现中文添加失败, 设置命令: set names gbk');
查询语句:
select * from grade;
1.2. 删除语法:
问题：delete，drop，truncate的区别？
delete DML语句,只删除表中数据,不会重置自增列
delete from 表名 [where 条件];
drop删除语法 : DDL语句,删除整张表
drop table [if exists] 表名;
truncate 删除语法: 删除表中数据,重新计算自增列
truncate table 表名;
1.3.修改语法
update 表名 set 字段1 = '值1',字段2 = '值2',...,字段n = '值n'
[where 条件]
二、DQL语句 数据查询语言
语法: select 字段列表 from 表名
[where 条件]
1.去重重复数据: distinct
2.给字段列、表定义别名 : [ as ]
select distinct(studentname) as 学生姓名,sex 性别 ,phone 联系方式 from student s;
3.模糊查询方式一
select distinct(studentname) as 学生姓名,sex 性别 ,phone 联系方式 from student s
where studentname like '%李%';
4.模糊查询方式二
select studentno,studentname as 学生姓名,sex 性别 ,phone 联系方式 from student s
where studentname like '_李_';
5.根据指定学号(1011,1012,1013,1033)查询学生信息
5.1.between ... and :查询连续区号
select studentno,studentname as 学生姓名,sex 性别 ,phone 联系方式 from student s
where studentno between 1011 and 1013;
5.2. or
select studentno,studentname as 学生姓名,sex 性别 ,phone 联系方式 from student s
where studentno = 1011 or studentno = 1012 or studentno = 1013 or studentno = 1033;
5.3. in
select studentno,studentname as 学生姓名,sex 性别 ,phone 联系方式 from student s
where studentno in(1011,1012,1013,1033);
# 添加测试数据
insert into student(studentno,studentname,sex,gradeId,phone,address,email,indentityCard,loginpwd,borndate)
values(1031,'王李','男',1,'13500000001','北京海淀区中关村大街1号','guojing@bdqn.cn','450323198612111021','222',now()),
(1032,'文李才','男',4,'13500000012','河南洛阳号','liwencai@bdqn.cn','450323198112311021','123',now()),
(1033,'李梅','女',3,'13500000025','上海卢湾区','limei@bdqn.cn','450323198612311021','223',now());
三.将数据库中的数据导出(数据备份)
1.dos命令备份:
mysqldump -uroot -p 数据库 [指定数据表] >指定磁盘路径地址:/xxx.sql文件
2.dos命令导入数据
source 导入的sql文件地址
四、##创建年级表
create table if not exists grade(
gid int(11) primary key auto_increment not null comment '主键,年级编号',
gname varchar(32) comment '年级名称'
);
create table if not exists student(
studentno int(4) primary key auto_increment not null comment '学号',
loginpwd varchar(20) not null comment '密码',
studentname varchar(20) not null comment '学生姓名',
sex char(2) default '男' not null comment '性别',
gradeId int(11) not null comment '年级编号',
phone varchar(50) comment '联系方式,允许为空',
address varchar(255) comment '地址,允许为空',
borndate datetime not null comment '出生日期',
email varchar(50) comment '邮箱账号,允许为空',
indentityCard varchar(18) unique comment '身份证号',
foreign key(gradeId) references grade(gid)
)comment='学生表';
注: 建库、建表 、再操作数据;
在dos命令添加中文数据,出现中文添加失败, 设置命令: set names gbk;
day20190923 MySql_Chapter03笔记.txt
练习案例：
一、自定义数据表 demo
## auto_increment : 自动增长，默认每隔1个数值进行增长
## comment :注释
## primary key : 主键约束
## 字段属性: unsigned 无符号，限制该字段值出现负号
## 字典属性: zerofill 自动补零，给字段列添加了zerofill自动补零属性，该字段会默认添加unsigned无符号
## not null : 非空约束
## default : 默认约束
## unique : 唯一约束，不可重复
create table if not exists demo(
did int(10) unsigned primary key auto_increment comment '序号',
dname varchar(32) comment '姓名'
)comment '测试表';
## 新增数据
insert into demo() values(1001,'张三');
insert into demo() values(-1001,'张三');
insert into demo() values(0001,'嘎嘎');
##查询demo表
select * from demo;
##删除demo表
drop table if exists demo;
2. MySQL练习
#创建school数据库:
create database school;
#切换school数据库:
use school;
#创建学生表:
create table if not exists student(
studentno int(4) primary key auto_increment not null comment '学号',
loginpwd varchar(20) not null comment '密码',
studentname varchar(20) not null comment '姓名',
sex char(2) not null comment '性别',
gradeid int(11) not null comment '年纪编号',
phone varchar(50) comment '联系方式，允许为空',
address varchar(255) comment '地址，允许为空',
borndate datetime not null comment '出生日期',
email varchar(50) comment '邮箱账号，允许为空',
indentityCard varchar(18) unique comment '身份证号'
)comment='学生表' engine='MyISAM';
3.当前MySql数据库默认的存储引擎是InnoDB
查看当前数据库所有存储引擎:
show engines;
查看某张表的存储引擎
----------- ------------------- -----------------------------------------------------------------------
数据库：
1.数据库:存储数据的"仓库"；
数据库分为：
关系型数据库：
MySql
SqlServer
Oracle
DB2
.......
非关系型数据库:(NoSQL)
Redis
MongoDB
2.命令
win+R services.msc
dos命令
set names gbk;
3.MySql常用命令
show databases; //显示所有数据
create database [if not exists] 数据库名; //创建数据库语法
use 数据库名; //切换数据库
show tables; //显示所有数据表
desc 指定数据表名; //查看指定数据表结构
drop database [if exists] 数据库名; //删除数据库
create table [if not exists] 表名（
字段列 字段列数据类型 [索引] [注释],
字段列2 字段列数据类型 [索引] [注释],
字段列3 字段列数据类型 [索引] [注释],
............
字段列n 字段列数据类型 [索引] [注释],
）[注释][字符编码格式][存储引擎];
注：
字段列数据类型分:数值类型、字符串类型、时间类型、Null;
数值类型
tinyint :通过用来定义程序中的boolean类型,使用状态码0，1来替换
int
double
字符串类型
char（指定字符长度）、varchar（指定字符长度）区别：
char固定长度字符串,通常性别男、女定义,开、关这些已知固定的字符串
，就可定义char类型,优点是检索速度快，缺点是浪费内存资源;
varchar可变长度字符串,不确定该字符的字节长度时，定义varchar类型,
优点是节省内存资源，缺点是检索速度慢;
-------------------------- ----------------------------------------------------------------------------------
MySql_Chapter03笔记
1.表连接查询
内连接
等值连接和非等值连接(inner join ... on : 查询的是公共的数据)
自连接
外连接(公司查询多表数据,通常使用左外连接方式)
左外连接
右外连接
2. 排序、分组、筛选、聚合函数、子查询
语法:
select 字段列表 from 表名
[ where 条件 ]
[ group by ]
[ having ]
[ order by 排序字段列,(默认asc升序,降序desc)]
---------------------------------- ----------------------------------------------
MySql_Chapter03案例
1.内连接查询使用
1.1. 查询student显示学生的对应年级名称
select s.studentNo,s.studentName,
s.gradeID,g.gradeName from student s
inner join grade g on s.gradeID = g.gradeID ;
1.2. 查询student显示学生的对应年级名称,每页显示5条
select s.studentNo,s.studentName,
s.gradeID,g.gradeName from student s
inner join grade g on s.gradeID = g.gradeID
limit 10,5 ;
分页:
limit 起始行位置(从0开始),每页显示的数目 ;
注:
起始行位置公式 = (当前页数-1)*每页显示的数目;
1.3. 查询student显示学生的对应年级名称、考试成绩
select s.studentNo,s.studentName,
s.gradeID,g.gradeName,r.studentResult
from student s
inner join grade g on s.gradeID = g.gradeID
inner join result r on r.studentNo = s.studentNo ;
1.4. 查询student显示学生的对应年级名称、考试成绩 、考试科目
select s.studentNo,s.studentName,
s.gradeID,g.gradeName,r.studentResult ,
sub.subjectName,sub.classHour
from student s
inner join grade g on s.gradeID = g.gradeID
inner join result r on r.studentNo = s.studentNo
inner join subject sub on sub.subjectNo = r.subjectNo ;
1.5. 查询学生信息以及成绩(内连接、外连接区别比较)
# inner join ... on
select s.studentNo,studentName,s.sex,r.studentResult
from student s
inner join result r on r.studentNo = s.studentNo;
# left join ... on
# left join 以left左边的表为主表,将主表中的数据依次匹配从表中的每条数据,
从表中匹配不上,以null显示
select s.studentNo,studentName,s.sex,r.studentResult
from student s
left join result r on r.studentNo = s.studentNo;
#right join ... on
## right join 以right右边的表为在主表,将主表中的数据依次匹配从表中的每条数据,
从表中匹配不上,以null显示
select s.studentNo,studentName,s.sex,r.studentResult
from student s
right join result r on r.studentNo = s.studentNo;
1.6.自连接: 以自身表为主表,再虚拟出了一张表
create table demo(
sid int,
sname varchar(32),
tid int
);
insert into demo values(1,'张三',2);
insert into demo values(2,'Tom',3);
insert into demo values(3,'Tonny',5);
insert into demo values(4,'Emma',11);
insert into demo values(5,'Lilei',8);
insert into demo values(6,'王八',9);
insert into demo values(7,'刘鑫',7);
select s.sname 学生,t.sname 老师 from demo s
left join demo t on s.sid = t.tid ;
2. 排序查询
select s.studentNo,studentName,s.sex,r.studentResult
from student s
inner join result r on r.studentNo = s.studentNo
order by r.studentResult ;
3. 分组,通常和聚合函数一起使用
select s.studentNo,studentName,s.sex,r.studentResult
from student s
inner join result r on r.studentNo = s.studentNo
group by s.sex
3.1.根据男女分组,查询人数
select count(1),sex from student
group by sex
3.2.根据男女分组,查询总成绩/平局分/最大值/最小值
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
group by s.sex ;
4.having , 通常结合分组一起使用,作为条件筛选
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
group by s.sex
having s.sex = '男'
5.聚合函数
count(1)/count(0)/count(*)
sum()
avg()
max()
min()
注: count(*),数据量达到10w以上,相对查询效率低,count(1)相比较查询效率块
6. where条件、 分组、筛选、排序
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
where studentName like '%张%'
group by s.studentNo
having avg(r.studentResult) > 70
order by avg(r.studentResult) ;
7.字符串函数
concat()使用:
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
where studentName like concat('%','张','%')
group by s.studentNo
having avg(r.studentResult) > 70
order by avg(r.studentResult) ;
8.时间函数
now()
select now() from dual; #dual是临时表
9.子查询(SQL查询语句中又嵌套了一层SQL语句查询)
需求 嵌套一层子查询: 查询比李斯文大的学生的考试成绩
解决一: 查询出李斯文的出生日期 1993-07-23 00:00:00 |
select bornDate from student where studentName = '李斯文' limit 0,1 ;
解决二:
select s.studentNo,studentName,bornDate,r.studentResult from student s
inner join result r on r.studentNo = s.studentNo
where bornDate <
(select bornDate from student where studentName = '李斯文' limit 0,1) ;
LIMIT [m,]n 或 LIMIT n OFFSET m
限制SELECT返回结果的行数
m 制定第一个返回记录行的偏移量
n 制定返回记录行的最大数目
10. 面试题
纵向表数据转成横向显示数据
create table tb(name varchar(10) , subject varchar(10) , score int);
insert into tb values('张三' , '语文' , 74);
insert into tb values('张三' , '数学' , 83);
insert into tb values('张三' , '物理' , 93);
insert into tb values('李四' , '语文' , 74);
insert into tb values('李四' , '数学' , 84);
insert into tb values('李四' , '物理' , 94);
横向显示:
select name AS 姓名,
sum(case when subject = '语文' then score else 0 end ) as 语文,
sum(case when subject = '数学' then score else 0 end ) 数学,
sum(case when subject = '英语' then score else 0 end ) 英语,
sum(case when subject = '物理' then score else 0 end ) 物理
from tb
group by by name;
---------------------------------------------------
1.表连接查询内连接
等值连接和非等值连接(inner join )
1.1查询student显示学生对应年级名称
1.3 查询student显示学生的对应年级名称、考试成绩
select s.studentNo,s.studentName,
s.gradeID,g.gradeName,r.studentResult
from student s
inner join grade g
1.5查询学生信息及成绩
#inner join ... on
select s.studentNo,studentName,s.sex,r.studentResult from student s inner join result r on r.studentNo = s.studentNo;
#left join ... on
#left join 以left左边的表为主表，将主要的数据依次匹配从表中的每条数据，从表中匹配不上，以null显示
select s.studentNo,s.studentName,s.sex,r.studentResult from student s left join result r on r.studentNo = s.studentNo;
#right join ... on
#right join 以right右边的表为主表，将主要的数据依次匹配从表中的每条数据，从表中匹配不上，以null显示
select s.studentNo,s.studentName,s.sex,r.studentResult from student s right join result r on r.studentNo = s.studentNo;
1.6自连接：以自身表为主表，再虚拟出了一张表
create table demo(
sid int,
sname varchar(32),
tid int
);
insert into demo values(1,'张1',9);
insert into demo values(2,'张2',2);
insert into demo values(3,'张3',10);
insert into demo values(4,'张4',6);
select s.sname 学生,t.sname 老师 from demo s left join demo t on s.sid = t.tid;
2.排序查询
select s.studentNo,s.studentName,s.sex,r.studentResult from student s right join result r on r.studentNo = s.studentNo
order by r.studentResult;
3.分组,通常与聚合函数一起使用
select s.studentNo,studentName,s.sex,r.studentResult from student s inner join resutl r on r.studentNo = s.studentNo
group by s.sex;
3.1根据男女分组，查询人数
select count(1),sex from student group by sex;
3.2
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
group by s.sex;
4.having ,通常结合分组一起使用，作为条件使用
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
having s.sex = '男';
5.聚合函数
count(1)
sum()
avg()
max()
min()
注：count(*),数据量达到10w以上，相对查询效率低，count(1)相比较查询效率快
6.where 条件、分组、筛选、
select s.studentNo,studentName,s.sex,
sum(r.studentResult) 总成绩,
avg(r.studentResult) 平均分,
max(r.studentResult) 最高成绩,
min(r.studentResult) 最低成绩
from student s
inner join result r on r.studentNo = s.studentNo
group by s.studentNo
having avg(r.studentResult) > 70
order by avg(r.studentResult);
7.时间函数
select now() from dual; #dual是临时表
8.子查询(SQL查询语句中又嵌套了一层SQL语句查询)
需求：查询比李斯文大的学生的考试成绩
解决一：查询出李斯文的出生日期 1993-07-23 00:00:00
select bornDate from student where studentName = '李斯文' limit 0,1;
解决二：
select s.studentNo,studentName,bornDate,r.studentResult from student s
inner join result r on r.studentNo = s.studentNo
where bornDate < (select bornDate from student where studentName = '李斯文' limit 0,1);
LIMIT [m,]n 或 LIMIT n OFFSET m
限制SELECT返回结果的行数
m 制定第一个返回记录行的偏移量
n 制定返回记录行的最大数目
10.面试题
纵向表数据转成横向显示数据
day20191924
SQL笔记：
一、事务
概念: 将同一组sql语句放在同一批次执行
事务的4个特性:
原子性 、 一致性 、隔离性 、持久性
事务的隔离级别:
读未提交 、读提交 、重复读(MySQL默认的隔离级别) 、序列化
根据事务的隔离级别可能会产生这些数据:
脏读 : 事务A读取了事务B执行而未提交的数据,如果事务B进行回滚,此时事务A读取了事务B的数据,就是脏数据
不可重复读：对同一个共享数据进行操作,事务A执行了select查询,事务B执行了更新并提交,
事务B提交成功，它所做的修改已经可见。然而，事务A已经读取了一个其它的值。
在序列化和可重复读的隔离级别中，数据库管理系统会返回旧值，即在被事务B修改之前的值。
在提交读和未提交读隔离级别下，可能会返回被更新的值，这就是“不可重复读”。
不可重复读侧重点在于更新(修改);
幻读：对同一个共享数据进行操作，事务A执行了两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。幻读的侧重点在于新增、修改;
二、通过Excel公式生成数据库中的测试数据
示例:
="insert into account(name,money) values('"&A2&"',"&B2&");"
三、索引
常规索引: 关键字 index/key
表已经创建好,再添加索引语法:
alter table 表名 add index 索引别名(字段列表);
四、数据库3大范式
第一范式:确保每列的原子性(每一列字段都应拆分到不能拆分为止)
第二范式:确保每张表只描述一件事情
第三范式: 如果满足以上两大设计范式,且该数据表中除了主键列,其他字段列没有依赖传递主键列关系,此时,则满足数据库第三范式;
注:数据库3大范式太理想化,一般数据库设计时会允许数据库适当的数据冗余。
--------------------------------------------------------------------- ---------------------
MySQL_Chapter04.sql
#查看当前事务提交方式; 1:代表自动提交 0:代表需手动commit提交
SELECT @@autocommit;
#更改当前事务提交方式,由默认的自动提交更改成手动提交
SET @@autocommit= 0;
##事务演示案例
CREATE DATABASE bank;
USE bank;
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL ,
NAME VARCHAR(32),
money DOUBLE(10,2)
);
INSERT INTO account(NAME,money) VALUES('张三',1000);
INSERT INTO account(NAME,money) VALUES('李四',1000);
INSERT INTO account(NAME,money) VALUES('王五',1000);
INSERT INTO account(NAME,money) VALUES('赵六',1000);
ROLLBACK; ##回滚
COMMIT; ##提交
##张三转账500给李四
UPDATE account SET money = money - 100 WHERE NAME = '张三';
UPDATE account SET money = money1 + 100 WHERE NAME = '李四';
##使用事务执行
## 开启一个事务
START TRANSACTION;
UPDATE account SET money = money - 100 WHERE NAME = '张三';
UPDATE account SET money = money1 + 100 WHERE NAME = '李四';
ROLLBACK;
END;
SELECT * FROM account;
##调用存储过程
CALL pro_test;
CALL pro_test1;
---------------------------------------- ------------------------------------
创建存储过程二(演示sql语句异常).sql
DELIMITER $$
CREATE
PROCEDURE `bank`.`pro_test1`()
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;-- 异常时设置为1
UPDATE account SET money = money - 100 WHERE NAME = '张三';
UPDATE account SET money1 = money + 100 WHERE NAME = '李四';
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
------------------ --------------------------------------- ------------------------------
创建存储过程语法一(sql语句正常).sql
DELIMITER $$
CREATE
PROCEDURE `bank`.`pro_test`()
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;-- 异常时设置为1
UPDATE account SET money = money - 100 WHERE NAME = '张三';
UPDATE account SET money = money + 100 WHERE NAME = '李四';
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
day20190925
JDBC_Chapter01:
笔记：
1.JDBC: Java连接数据库的一门技术;
2.使用JDBC连接数据库的步骤
一 、加载驱动(Driver:数据库厂商提供,作用区分管理不同的数据库标识)
二 、使用DriverManager连接数据库(DriverManager:管理连接不同的数据库驱动)
三 、创建PreparedStatement对象,建立数据库连接通道,传输执行sql语句
四、获取数据库返回的ResultSet结果集
五、释放资源
PS: lib:专门存放jar包
------------------- ---------------------------- -----------------
ConnDB.java
package com.kgc.kh76.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 使用JDBC连接数据库
*
*/
public class ConnDB {
public static void main(String[] args) {
try {
// 1 、加载驱动(Driver:数据库厂商提供,作用区分管理不同的数据库标识)
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("数据库驱动jar包加载失败");
e.printStackTrace();
}
try {
// 2、使用DriverManager连接数据库,返回Connection数据库连接信息
// 2.1. url 连接数据库的地址
// jdbc:mysql://IP地址 : 数据库服务的端口号/数据库名
String url = "jdbc:mysql://localhost:3306/myschool";
// 2.2. user 数据库的用户名
String user = "root";
// 2.3. password 数据库的密码
String password = "ok";
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("数据库连接成功:"+conn);
//3.释放资源
conn.close();
} catch (SQLException e) {
System.out.println("数据库连接失败");
e.printStackTrace();
}
}
}
----------------------- --------------------- -----------------------------------------------------
conn_DML操作数据中的表信息：
---------------------- --------------------------------------
CoonDB_Insert.java：
package com.kgc.kh76.conn_DML操作数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class CoonDB_Insert {
//创建数据库连接信息,driver、url、user、password
protected static final String DRIVER = "com.mysql.jdbc.Driver";
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入姓名:");
String name = sc.next();
System.out.print("请输入密码:");
String pwd = sc.next();
System.out.print("请输入性别:");
String sex = sc.next();
System.out.print("请输入年级编号:");
int gid = sc.nextInt();
System.out.print("请输入联系方式:");
String phone = sc.next();
try {
//1.加载驱动
Class.forName(DRIVER);
//2.建立数据库连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建新增的sql语句
String sql = " INSERT INTO student(loginPwd,studentName,sex,gradeID,phone," +
" address,bornDate,email,identityCard) "
+ " VALUES(?,?,?,?,?,?,?,?,?) ";
//4.创建PreparedStatemenet对象,建立数据库连接通道,传输sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置占位符参数
pstmt.setString(1, pwd);
pstmt.setString(2, name);
pstmt.setString(3, sex);
pstmt.setInt(4, gid);
pstmt.setString(5, phone);
pstmt.setString(6, "南京雨花台区");
pstmt.setString(7, "2000-10-10");
pstmt.setString(8, "222@qq.com");
pstmt.setString(9, "320123456789000");
//6.触发执行sql语句
int addRes = pstmt.executeUpdate();
if(addRes>0) {
System.out.println("新增成功");
}else {
System.out.println("新增失败!!!");
}
//7释放资源
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}
----------------------------------------------------------- ----------------------------
CoonDB_Update.java：
package com.kgc.kh76.conn_DML操作数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class CoonDB_Update {
//创建数据库连接信息,driver、url、user、password
protected static final String DRIVER = "com.mysql.jdbc.Driver";
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入要修改的学生ID:");
int id = sc.nextInt();
System.out.print("请输入要修改的学生姓名:");
String name = sc.next();
System.out.print("请输入要修改的学生密码:");
String pwd = sc.next();
System.out.print("请输入要修改的学生性别:");
String sex = sc.next();
System.out.print("请输入要修改的学生年级编号:");
int gid = sc.nextInt();
System.out.print("请输入要修改的学生联系方式:");
String phone = sc.next();
try {
//1.加载驱动
Class.forName(DRIVER);
//2.建立数据库连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建修改的sql语句
String sql = " UPDATE student SET loginPwd = ? ,studentName = ? ,"
+ " sex = ?,gradeID = ?,phone = ?, " +
"address = ?,bornDate = ?,email = ?,identityCard = ? WHERE studentNo = ? ";
//4.创建PreparedStatemenet对象,建立数据库连接通道,传输sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置占位符参数
pstmt.setString(1, pwd);
pstmt.setString(2, name);
pstmt.setString(3, sex);
pstmt.setInt(4, gid);
pstmt.setString(5, phone);
pstmt.setString(6, "南京雨花台区");
pstmt.setString(7, "2000-10-10");
pstmt.setString(8, "222@qq.com");
pstmt.setString(9, "320123456789001");
pstmt.setInt(10, id);
//6.触发执行sql语句
int updateRes = pstmt.executeUpdate();
if(updateRes>0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败!!!");
}
//7释放资源
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}
------------------------------- --------------------------------------
CoonDB_Delete.java：
package com.kgc.kh76.conn_DML操作数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class CoonDB_Delete {
//创建数据库连接信息,driver、url、user、password
protected static final String DRIVER = "com.mysql.jdbc.Driver";
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入要删除的学生ID:");
int id = sc.nextInt();
try {
//1.加载驱动
Class.forName(DRIVER);
//2.建立数据库连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建删除的sql语句
String sql = " delete from student where studentNo = ? ";
//4.创建PreparedStatemenet对象,建立数据库连接通道,传输sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置占位符参数
pstmt.setInt(1, id);
//6.触发执行sql语句
int delRes = pstmt.executeUpdate();
if(delRes>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败!!!");
}
//7释放资源
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}
------------------------- -------------------- ----------------------------
conn_查询数据中的表信息
ConnQuery.java：
package com.kgc.kh76.conn_查询数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 使用JDBC连接数据库,并查询student表信息
*
*/
public class ConnQuery {
//定义数据库连接对象信息,url user password
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立数据连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建PreparedStatement对象,建立 数据库传输通道, 传输sql语句
/*
* PreparedStatement 对象 和 Statement对象都可以建立数据库传输通道,
*Statement实例对象 传输sql语句时,可以sql注入,有安全隐患;
*PreparedStatement传输sql语句时,可预编译sql语句,提高程序的安全性
*/
//定义查询sql
String sql = " SELECT studentNo,loginPwd,studentName,sex,gradeID,phone," +
"address,bornDate,email,identityCard FROM student ";
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.触发执行查询sql,返回rs结果集对象
ResultSet rs = pstmt.executeQuery();
System.out.println("学生编号\t密码\t姓名\t性别\t年级编号\t手机\t\t\t\t地址\t\t出生日期\t邮箱\t身份证号");
//5.循环遍历rs结果集
while(rs.next()) {
int stuNo = rs.getInt("studentNo");
String loginPwd = rs.getString("loginPwd");
String studentName = rs.getString("studentName");
String sex = rs.getString("sex");
int gradeID = rs.getInt("gradeID");
String phone = rs.getString("phone");
String address = rs.getString("address");
String bornDate = rs.getString("bornDate");
String email = rs.getString("email");
String identityCard = rs.getString("identityCard");
//控制台输出
System.out.println(stuNo+"\t"+loginPwd+"\t"+studentName+
"\t"+sex+"\t"+gradeID+"\t"+phone+"\t\t"+address+
"\t"+bornDate+"\t"+email+"\t"+identityCard+"\t");
}
//释放资源
rs.close();
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("数据驱动加载失败");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
------------------------------- -------------------------
ConnQuery_SearchStuNameAndPwd.java
package com.kgc.kh76.conn_查询数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
/**
* 使用JDBC连接数据库,根据 学生姓名和密码查询学生信息
*
*/
public class ConnQuery_SearchStuNameAndPwd {
//定义数据库连接对象信息,url user password
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入姓名:");
String stuName = sc.next();
System.out.print("请输入密码:");
String stuPwd = sc.next();
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立数据连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建PreparedStatement对象,建立 数据库传输通道, 传输sql语句
//定义查询sql
String sql = " SELECT studentNo,loginPwd,studentName,sex,gradeID,phone," +
"address,bornDate,email,identityCard FROM student "
+ " WHERE studentName = ? AND loginPwd = ? ";
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置占位符参数
pstmt.setString(1, stuName);
pstmt.setString(2, stuPwd);
//4.触发执行查询sql,返回rs结果集对象
ResultSet rs = pstmt.executeQuery();
System.out.println("学生编号\t密码\t姓名\t性别\t年级编号\t手机\t\t\t\t地址\t\t出生日期\t邮箱\t身份证号");
//5.遍历rs结果集
if(rs.next()) {
int stuNo = rs.getInt("studentNo");
String loginPwd = rs.getString("loginPwd");
String studentName = rs.getString("studentName");
String sex = rs.getString("sex");
int gradeID = rs.getInt("gradeID");
String phone = rs.getString("phone");
String address = rs.getString("address");
String bornDate = rs.getString("bornDate");
String email = rs.getString("email");
String identityCard = rs.getString("identityCard");
//控制台输出
System.out.println(stuNo+"\t"+loginPwd+"\t"+studentName+
"\t"+sex+"\t"+gradeID+"\t"+phone+"\t\t"+address+
"\t"+bornDate+"\t"+email+"\t"+identityCard+"\t");
}
//释放资源
rs.close();
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("数据驱动加载失败");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
-------------------------------------- -----------------------------
ConnQueryLike.java
package com.kgc.kh76.conn_查询数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
/**
* 使用JDBC连接数据库,并查询student表信息+ 姓名模糊查询
*
*/
public class ConnQueryLike {
//定义数据库连接对象信息,url user password
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入要模糊查询的学生姓名:");
String name = sc.next();
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立数据连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建PreparedStatement对象,建立 数据库传输通道, 传输sql语句
//定义查询sql
String sql = " SELECT studentNo,loginPwd,studentName,sex,gradeID,phone," +
"address,bornDate,email,identityCard FROM student "
+ " WHERE studentName LIKE CONCAT('%', ? , '%') ";
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置占位符参数
pstmt.setString(1, name);
//4.触发执行查询sql,返回rs结果集对象
ResultSet rs = pstmt.executeQuery();
System.out.println("学生编号\t密码\t姓名\t性别\t年级编号\t手机\t\t\t地址\t\t出生日期\t邮箱\t身份证号");
//5.循环遍历rs结果集
while(rs.next()) {
int stuNo = rs.getInt("studentNo");
String loginPwd = rs.getString("loginPwd");
String studentName = rs.getString("studentName");
String sex = rs.getString("sex");
int gradeID = rs.getInt("gradeID");
String phone = rs.getString("phone");
String address = rs.getString("address");
String bornDate = rs.getString("bornDate");
String email = rs.getString("email");
String identityCard = rs.getString("identityCard");
//控制台输出
System.out.println(stuNo+"\t"+loginPwd+"\t"+studentName+
"\t"+sex+"\t"+gradeID+"\t"+phone+"\t\t"+address+
"\t"+bornDate+"\t"+email+"\t"+identityCard+"\t");
}
//释放资源
rs.close();
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("数据驱动加载失败");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
-------------------------------- ---------------------------------------
ConnQueryPage.java
package com.kgc.kh76.conn_查询数据中的表信息;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 使用JDBC连接数据库,并查询student表信息+分页
*
*/
public class ConnQueryPage {
//定义数据库连接对象信息,url user password
protected static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool";
protected static final String USER = "root";
protected static final String PASSWORD = "ok";
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立数据连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建PreparedStatement对象,建立 数据库传输通道, 传输sql语句
/*
* PreparedStatement 对象 和 Statement对象都可以建立数据库传输通道,
*Statement实例对象 传输sql语句时,可以sql注入,有安全隐患;
*PreparedStatement传输sql语句时,可预编译sql语句,提高程序的安全性
*/
//定义查询sql
String sql = " SELECT studentNo,loginPwd,studentName,sex,gradeID,phone," +
"address,bornDate,email,identityCard FROM student "
+ " LIMIT 15,5 ";
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.触发执行查询sql,返回rs结果集对象
ResultSet rs = pstmt.executeQuery();
System.out.println("学生编号\t密码\t姓名\t性别\t年级编号\t手机\t\t\t\t地址\t\t出生日期\t邮箱\t身份证号");
//5.循环遍历rs结果集
while(rs.next()) {
int stuNo = rs.getInt("studentNo");
String loginPwd = rs.getString("loginPwd");
String studentName = rs.getString("studentName");
String sex = rs.getString("sex");
int gradeID = rs.getInt("gradeID");
String phone = rs.getString("phone");
String address = rs.getString("address");
String bornDate = rs.getString("bornDate");
String email = rs.getString("email");
String identityCard = rs.getString("identityCard");
//控制台输出
System.out.println(stuNo+"\t"+loginPwd+"\t"+studentName+
"\t"+sex+"\t"+gradeID+"\t"+phone+"\t\t"+address+
"\t"+bornDate+"\t"+email+"\t"+identityCard+"\t");
}
//释放资源
rs.close();
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("数据驱动加载失败");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
--------------------------------------- ---------------------------------------
stu.sql
## 查询学生信息
SELECT `studentNo`,`loginPwd`,`studentName`,`sex`,`gradeID`,`phone`,
`address`,`bornDate`,`email`,`identityCard` FROM student
LIMIT 5,5 ;
##根据学生姓名模糊查询
SELECT `studentNo`,`loginPwd`,`studentName`,`sex`,`gradeID`,`phone`,
`address`,`bornDate`,`email`,`identityCard` FROM student
WHERE studentName LIKE '%何睛%' ;
SELECT `studentNo`,`loginPwd`,`studentName`,`sex`,`gradeID`,`phone`,
`address`,`bornDate`,`email`,`identityCard` FROM student
WHERE studentName LIKE CONCAT('%','何','%') ;
##根据 学生姓名和密码查询学生信息
SELECT `studentNo`,`loginPwd`,`studentName`,`sex`,`gradeID`,`phone`,
`address`,`bornDate`,`email`,`identityCard` FROM student
WHERE studentName = '金涛' AND loginPwd = '123'
##新增sql语句
INSERT INTO student(`loginPwd`,`studentName`,`sex`,`gradeID`,`phone`,
`address`,`bornDate`,`email`,`identityCard`) VALUES('222','金涛','男',4,'123456789','南京晓航路',
NOW(),'123@qq.com','');
INSERT INTO student(`loginPwd`,`studentName`,`sex`,`gradeID`,`phone`,
`address`,`bornDate`,`email`,`identityCard`) VALUES(?,?,?,?,?,?,?,?,?) ;
##修改
UPDATE student SET `loginPwd` = '222',`studentName` = '小马' ,`sex` = '女' ,`gradeID` = 1,
`phone` = '123456',
`address` = 'aa',`bornDate` = '2000-10-10',`email` = '123@qq.com' ,
`identityCard` = '11' WHERE `studentNo` = 10000
UPDATE student SET `loginPwd` = ,`studentName` = ,`sex` = ,`gradeID` = ,`phone` = ,
`address` = ,`bornDate` = ,`email` = ,`identityCard` = WHERE `studentNo` =
------------------------------ --------------------------------
test.sql
/*年级表*/
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeID` INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '年级编号',
`gradeName` VARCHAR(32) NOT NULL COMMENT '年级名'
)COMMENT = '年级表' CHARSET = 'utf8';
/*学生表*/
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentNo` INT(4) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '学号',
`loginPwd` VARCHAR(20) NOT NULL COMMENT '密码',
`studentName` VARCHAR(50) NOT NULL COMMENT '姓名',
`sex` CHAR(2) DEFAULT '男' NOT NULL COMMENT '性别',
`gradeID` INT(4) COMMENT '年级编号(student表的外键用来关联grade表的主键)',
`phone` VARCHAR(50) COMMENT '联系电话',
`address` VARCHAR(255) DEFAULT '地址并详' COMMENT '地址',
`bornDate` DATETIME COMMENT '出生日期',
`email` VARCHAR(50) COMMENT '邮箱账号',
`identityCard` VARCHAR(18) UNIQUE KEY COMMENT '身份证号',
FOREIGN KEY(`gradeID`) REFERENCES `grade`(`gradeID`) /*建表时就填加外键*/
)COMMENT = '学生表' CHARSET = 'utf8';
/*科目表*/
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectNo` INT(4) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '科目编号',
`subjectName` VARCHAR(32) NOT NULL COMMENT '科目名称',
`classHour` INT(4) COMMENT '课时数量',
`gradeID` INT(4) COMMENT '年级编号'
)COMMENT = '科目表' CHARSET = 'utf8';
/*成绩表*/
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentNo` INT(4) COMMENT '学号',
`subjectNo` INT(4) COMMENT '科目编号',
`examdate` TIMESTAMP DEFAULT NOW() COMMENT '考试日期',
`studentResult` INT(4) COMMENT '考试成绩'
)COMMENT = '成绩表'CHARSET = 'utf8';
/*查询表的信息*/
SELECT * FROM `student`;
SELECT * FROM `grade`;
SELECT * FROM `subject`;
SELECT * FROM `result`;
------------------------------- -----------------------------------
insert.sql
/*创建school数据库*/
CREATE DATABASE school;
/*给年级表添加数据*/
INSERT INTO `grade` VALUES(1,'S1');
INSERT INTO `grade`(`gradeName`) VALUES('S2');
INSERT INTO `grade`(`gradeName`) VALUES('Y2');
INSERT INTO `grade`(`gradeName`) VALUES('课工场');
/*student表数据*/
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10000','123','郭靖','男','1','13645667783','天津市河西区','1990-09-08 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10001','123','李文才','男','1','13645667890','地址不详','1994-04-12 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10002','123','李斯文','男','1','13645556793','河南洛阳','1993-07-23 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10003','123','张萍','女','1','13642345112','地址不详','1995-06-10 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10004','123','韩秋洁','女','1','13812344566','北京市海淀区','1995-07-15 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10005','123','张秋丽','女','1','13567893246','北京市东城区','1994-01-17 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10006','123','肖梅','女','1','13563456721','河北省石家庄市','1991-02-17 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10007','123','秦洋','男','1','13056434411','上海市卢湾区','1992-04-18 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10008','123','何睛睛','女','1','13053445221','广州市天河区','1997-07-23 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('20000','123','王宝宝','男','2','15076552323','地址不详','1996-06-05 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('20010','123','何小华','女','2','13318877954','地址不详','1995-09-10 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30011','123','陈志强','男','3','13689965430','地址不详','1994-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30012','123','李露露','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30022','123','李张露','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30032','123','李露张','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30042','123','张露露','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30052','123','露露张','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30062','123','张李露','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30020','123','李斯文','男','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30031','123','李斯文','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30041','123','李斯文','男','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30051','123','李斯文','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`,`phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30061','123','李斯文','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);
/*result表数据*/
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10000','1','2016-02-15 00:00:00','71');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10000','1','2016-02-17 00:00:00','60');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10001','1','2016-02-17 00:00:00','46');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10002','1','2016-02-17 00:00:00','83');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10003','1','2016-02-17 00:00:00','60');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10004','1','2016-02-17 00:00:00','60');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10005','1','2016-02-17 00:00:00','95');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10006','1','2016-02-17 00:00:00','93');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10007','1','2016-02-17 00:00:00','23');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10003','2','2016-02-17 00:00:00','90');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10004','2','2016-02-17 00:00:00','70');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10005','3','2016-02-17 00:00:00','85');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10006','3','2016-02-17 00:00:00','83');
INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) VALUES('10007','3','2016-02-17 00:00:00','23');
/*给subject添加数据*/
INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`) VALUES
('Logic Java',120,1),
('Htm5',50,2),
('MySQL',30,4);
-------------------------------------- -------------------------------------
