JAVA萌新学习day17.18天 数据库MySQL基本操作
MySQLDemo
// name age address 小明 18 大连 小明 18 大连 小明 18 大连
/**
* 1.数据库 -> 数据库管理系统MySQL
* 2.没有数据库 -> 使用文件->每种类型的文件都有自己的格式
* 3.数据库 在磁盘上的保存，也是一个文件，只是有它自己特定的格式
* 4.数据库管理系统:MySQL 叫关系型数据库(关系 指的 表 二维表格)
* 5.数据库里面保存的是一张一张的二维表 ：有行和列的组成，每张表需要有一个唯一的名字
* 6.每一列我们都起一个字名，我们管它叫属性或字段，需要有类型（类似我们类的属性）
* 7.每一行我们叫一条记录（类似我们类的对象）
* 8.一张表格的数据，在我们程序中可以用什么来保存:可以使用自定义类的对象类型数组来保存
* 9.要求：类的属性和表中的字段一一对应
* 10. SQL语句以分号结束 ;
* 11. show databases;查看当前有多少个数据库
* 12. create database java1904;//创建数据库java1904
* 13. drop database java1905;//删除数据库java1905
* 14. use java1904;//使用数据库，也就把当前要操作的数据库切换成java1904
* 15. show tables;//显示当前数据库中的所有表
*
* //如果不存在 创建一个叫 student 的表 表名后面用小括号括起来，里面
* 使用 字段名 字段类型 修饰关键字格式，多个字段间中逗号分隔
* 16. create table if not exists student
* ( id int not null,
* name varchar(50),
* address varchar(100)
* );
* 17. drop table student; //删除表 student
* 17.desc student;//查看 表student的结构
* 18. alter table student add phone int;//向表中添加一个新字段
* 19. alter table student drop phone;//删除表中的一个字段
* 20. alter table student add phone int after name;//在某个字段后添加
* 21. alter table student modify phone varchar(50);//修改指定的字段类型
* 22. alter table student change phone telnumber varchar(20);//修改字段名和类型
* // 向数据表student中插入一条记录
* 23. insert into student (id,name,telnumber,address) values (1,"name1","13812345678","qwerty");
* insert into student (id,name,telnumber,address) values (2,"name2","13912345678","iuyt");
* // 修改表中某些条记录某个字段的值(如果没有条件，影响所有记录) update table_name set field_name = field_value;
* 24. update student set name = "name2";
* 25. select * from student;//查看表student中的所有记录（每条记录包括所有列）
* // 根据指定的条件改表中某个字段的值 set关键字后面的字段名可以有多个，用逗分隔
* 26. update student set name = "name2" where telnumber = "13912345678";
* 26. update student set name = "name5" ,id = 5 where telnumber = "13912345678";
* // 从表student 中删除 id值为 5的记录(所有批配的记录都会删除)
* 27. delete from student where id = 5;
* // 从表student中查询 name字段值为 name1的记录，返回结果中每条记录只包括 一个 name字段
* // select关键字后面是要查询的所有字段的名称，多个之间用逗号分隔
* 28. select name from student where name = "name1";
* //修改当前表的字符集为utf8
* 29. alter table student character set utf8;
* //批量导入指定文件中的内容
* 30. source c:\\sql\\student.sql;
*/
/**
* //查询 学生表中的所有记录，只返回名字
* SELECT `name` FROM student;
* //查询 学生表的年龄小于10的所有记录 ，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE age<10;
* //查询 学生表的年龄在8和10之间的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE age>8 AND age<10;
* //查询 学生表的年龄小于8或者大于10的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE age<8 OR age>10;
* //查询 学生表的年龄是12,13,14的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE age IN (12,13,14);
* //查询 学生表的姓名以b开头的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE `name` LIKE "b%";
* //查询 学生表的姓名第二个字母是b的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE `name` LIKE "_b%";
* //查询 学生表的姓名中包含aa的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE `name` LIKE "%aa%";
* //查询 学生表的姓名中包含b_b(中间的_可以是任意字符)的所有记录，返回满足条件记录的 名字和年龄
* SELECT `name`,age FROM student WHERE `name` LIKE "%b_b%";
* //查询 所有记录，相当于没有限制条件
* SELECT `name`,age FROM student WHERE `name` LIKE "%";
* //查询 去掉 age值相同的 重复记录，
* SELECT DISTINCT age FROM student;
* //查询 去掉 age值相同的 重复记录，结果按 age升序排列
* SELECT DISTINCT age FROM student ORDER BY age;
* //查询 去掉 age值相同的 重复记录，结果按 age 降序排列
* SELECT DISTINCT age FROM student ORDER BY age DESC;
* //查询 age,id，结果按 先按 age 升序排列，再按 id 降序排列
* SELECT age,id FROM student ORDER BY age ASC,id DESC;
*/
/**
* 从学生表中 查询 专业为 English 的学生的总数量
* SELECT COUNT(*) FROM student WHERE major="English";
* 从学生表中 查询 专业为 English 的学生的年龄最大值和最小值
* SELECT MAX(age),MIN(age) FROM student WHERE major="English";
* 从学生表中 查询 专业为 English 的学生的平均年龄
* SELECT AVG(age) FROM student WHERE major="English";
* 学生表中 查询 专业为 English 的学生的年龄总和
* SELECT SUM(age) FROM student WHERE major="English";
* 从学生表中 按专业分组 查询 此专业学生的数量 和所在专业
* SELECT major,COUNT(*) FROM student GROUP BY major;
* 从学生表中 按专业分组 查询 此专业学生的年龄总和
* SELECT major,SUM(age) FROM student GROUP BY major;
* 从学生表中 按专业分组 查询 年龄大于20的学生的年龄总和
* SELECT major,SUM(age) FROM student WHERE age>20 GROUP BY major;
* 从学生表中 按专业分组 查询 年龄大于20的学生 结果按学生总量排序
* SELECT major,SUM(age) AS total FROM student WHERE age>20 GROUP BY major ORDER BY total;
*
*/
/**多表联合
* //徐学生表内链接成绩表，条件是学生表id和成绩表的学生id相等
* //没有条件的内连接，结果是两个表笛卡尔积，我们需要根据主外键关系，去掉一些不满足条件的记录
* select * from student inner join score on student.id = score.id;
* //课表坐外链接成绩表条件是课程id和成绩表的课程id相等
* //左外链接是以左表为主（匹配条件），如果右表不满足条件，添NULL
* select * from course Left outer join score on course.id = score.cid;
* //课程表右外链接成绩表，条件是课程id和成绩表的课程id相等
* //右外链接是以右表为主（匹配条件），如果左表不满足条件，添NULL
* select * from course right outer join score on course.id = score.cid;
*/
/**子查询
* 1.比学生 XXX 学号大的所有学生
* select * from score where cid = (select id from course where name = "java");
* 2.查询选修了java的所有学生的总成绩
* select id,sum(score) from score where cid = (select id from course where name = "java");
* 3.查询每个学科的学生
* select cid,count(*) from score group by cid;
* 4.查询学生数大于400的学科
* select cid,count(*) from score group by cid having count(*)>400;
* 5.查询前10名的学生的姓名和成绩
* select score.sid,student.name,score.score from score inner join student on score.sid = student.id limit 0,10;
* */
public class MySQLDemo {
public static void main(String[] args) {
System.out.println();
StudentManager.test();
}
}
Student
import java.util.Random;
public class Student {
//学生名
private String name;
//学生id
private int id;
//学生地址
private String address;
//学生年龄
private int age;
//学生性别
private char sex;
//专业
private String major;
public Student(){
this("","",0,'男',0);
}
public static String majorString(){
Random r = new Random();
String[] majorArray = {"Computer","English","Math","Article","Sport"};
return majorArray[r.nextInt(majorArray.length)];
}
public Student(String name, String address, int age, char sex,int id) {
this.name = name;
this.id = id;
this.address = address;
this.age = age;
this.sex = sex;
this.major = majorString();
}
@Override
//通过重写toString,把当前对象内容转成插入语句
public String toString(){
// insert into student (id,name,age,sex,address) values (1,"a",6,'男',"ttt");
String str = "insert into student (id,name,age,sex,address,major) values (";
str += this.id+",";
str += "\""+ this.name+"\",";
str += this.age+",";
str += "\'"+ this.sex+"\',";
str += "\"" +this.address+ "\",";
str += "\"" +this.major+ "\"" + ");";
return str;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public char getSex() {
return sex;
}
public void setSex(char sex) {
this.sex = sex;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
}
Course
public class Course {
//课程id
private int id;
//课程名
private String name;
//课程所属专业
private String major;
//学分
private int score;
public Course(String name, String major, int score,int id) {
this.name = name;
this.major = major;
this.score = score;
this.id = id;
}
public String toString(){
// insert into student (id,name,age,sex,address) values (1,"a",6,'男',"ttt");
String str = "insert into course (name,major,score) values (";
str += "\""+ this.name+"\",";
str += "\"" +this.major+ "\",";
str += this.score + ");";
return str;
}
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 getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
public int getScore() {
return score;
}
public void setScore(int score) {
this.score = score;
}
}
Score
//成绩类
public class Score {
//成绩id
private int id;
//学生id
private int sid;
//课程id
private int cid;
//成绩
private int score;
public Score(){
this(0,0,0);
}
public Score(int sid, int cid, int score) {
this.sid = sid;
this.cid = cid;
this.score = score;
}
@Override
public String toString(){
// insert into student (id,name,age,sex,address) values (1,"a",6,'男',"ttt");
String str = "insert into score (sid,cid,score) values (";
str += this.sid + ",";
str += this.cid + ",";
str += this.score + ");";
return str;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public int getScore() {
return score;
}
public void setScore(int score) {
this.score = score;
}
}
StudentManager
import day16.Manager;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
public class StudentManager {
//用来保存所有学生对象的数组
private ArrayList<Student> students;
//用来保存所有课程对象的数组
private ArrayList<com.qf.java1904.day17.Course> courses;
//用来保存所有课程对象的数组
private ArrayList<com.qf.java1904.day17.Course> score;
//定义一个常量，用来保存程序产生的学生的数据的条数
private static final int STUDENTCOUNT = 500;
//定义一个常量，用来保存程序产生的课程的数据的条数
private static final int COURSECOUNT = 9;
//定义一个常量，用来保存程序产生的课程的数据的条数
private static final int SCORECOUNT = 9;
public StudentManager(){
students = new ArrayList<>();
courses = new ArrayList<>();
//产生指定数量的学生，生成.sql 保存到指定文件中
randomStudents();
//产生指定数量的课程，生成.sql 保存到指定文件中
randomCourse();
//随机产生每个学生所选课程的成绩，生成.sql 保存到指定文件中
randomScore();
}
public static String chineseName(){
String firstNames = "赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤";
Random random = new Random();
String lastNames = "明国华建文平志伟东海强晓生光林小民永杰军波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群松克清长嘉红山贤阳乐锋智青跃元南武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷保秋君劲栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨";
int indexIndex = random.nextInt(firstNames.length());
String firstName = firstNames.substring(indexIndex,indexIndex+1);
indexIndex = random.nextInt(lastNames.length());
String middleName = lastNames.substring(indexIndex,indexIndex+1);
indexIndex = random.nextInt(lastNames.length());
String lastName = lastNames.substring(indexIndex,indexIndex+1);
return firstName+middleName+lastName;
}
//静态测试方法
public static void test() {
StudentManager sm = new StudentManager();
}
//产生某个学生的选课数据
public List<com.qf.java1904.day17.Course> coursesByStudent(){
Random r = new Random();
int startIndex = r.nextInt(courses.size());
//int count = r.nextInt(courses.size() - startIndex) + 1;
//count = count >= courses.size()?courses.size()-1 : count;
List<com.qf.java1904.day17.Course> subCourse = courses.subList(startIndex,courses.size());
return subCourse;
}
public void randomScore(){
try{
String filename = "c:\\sql\\score.sql";
PrintStream ps = new PrintStream(filename);
System.setOut(ps);
}
catch (Exception e){
}
Random random = new Random();
for(Student s : students){
//每个学生最多可选的课程数量
List<com.qf.java1904.day17.Course> list = coursesByStudent();
for (int i = 0; i < list.size(); i++) {
//获得数组的第i个元素
com.qf.java1904.day17.Course c = list.get(i);
int scoreValue = random.nextInt(40) + 60;
//用学生id,课程id，随机的成绩，构造成绩对象
com.qf.java1904.day17.Score score = new com.qf.java1904.day17.Score(s.getId(),c.getId(),scoreValue);
//输出到文件中
System.out.println(score);
}
}
}
public void randomCourse(){
try{
String filename = "c:\\sql\\course.sql";
PrintStream ps = new PrintStream(filename);
System.setOut(ps);
}
catch (Exception e){
}
Random random = new Random();
String[] names = {"数学","英语","java","PHP","C++","政治","历史","数据库","毛概"};
for (int i = 0; i < names.length; i++) {
//创建课程对象
//获得随机的专业名称
String major = com.qf.java1904.day17.Student.majorString();
//获得随机的课程名称
String name = names[i];
//获得随机学分
int score = random.nextInt(7)+1;
com.qf.java1904.day17.Course c = new com.qf.java1904.day17.Course(name,major,score,i+1);
System.out.println(c);
courses.add(c);
}
}
//获得随机的课程名
public static String courseName(){
String[] names = {"数学","英语","java","PHP","C++","政治","历史","数据库"};
Random r = new Random();
return names[r.nextInt(names.length)];
}
//清楚数据库中所有表的记录，同时把自增长主键设置成初始值 1
public void clearData(){
//因为尊在主外键约束，所以当清楚数据表后，需要把自增长主键设置成初始值 1
System.out.println("delete from score;");
System.out.println("alter table score auto_increment = 1;");
System.out.println("delete from course;");
System.out.println("alter table course auto_increment = 1;");
System.out.println("delete from student;");
System.out.println("alter table student auto_increment = 1;");
}
public void randomStudents(){
try{
String filename = "c:\\sql\\student.sql";
PrintStream ps = new PrintStream(filename);
System.setOut(ps);
}
catch (Exception e){
}
//调用方法，清除原有数据表中的数据
clearData();
Random random = new Random();
for (int i = 0; i < STUDENTCOUNT; i++) {
int age = random.nextInt(20)+6;
char sex = random.nextInt(2)==0?'男':'女';
String name = chineseName();
String address = Manager.randomString(20);
Student s = new Student(name,address,age,sex,i+1);
String value = s.toString();
System.out.println(value);
//保存学生对象到成员变量数组
students.add(s);
}
}
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
