MySQL

1.创建数据库

create database mydb character set utf8;

2.查看删除数据库

show databases; //显示数据库

show create database mydb; //显示数据库创建语句

drop database mydb;

3.修改、备份、恢复数据库

alter database mydb character set gb2312; //修改数据库编码方式

mysqldump -uroot -p mydb>d:\mydb.sql (windows命令) //备份数据库

source d:\mydb.sql; // 恢复数据库，前提是库必须存在，如果不存在，创建了以后再恢复。

4.建表

create table users
(
 id int,
 username varchar(50)
);

5.数据库数据类型

数值类型：
   bit  位。默认值为 1 ，只能存放 1bit(位) 数据。
   tinyint  对应 Java 中的 byte。1个字节(1b)。
   boolean 使用 0 或 1 表示真或假。
   smallint 对应 Java 中的 shot，2的16次方。
   int   对应 Java 中的 int， 2的32次方。
   bigint 对应 Java 中的 long，2的64次方。
   float[M,D]    M 指定长度，D 指定小数位数。
   double[M,D]
   decimal(M,D)

文本、二进制类型(2的10次方是 1k )：
        
           char
    varchar
    tinyblob、tinytext  2的8次方
           blob、text   2的16次方，64k。
           mediumblob、mediumtext 2的24次方，6mb。
    longblob、longtext  2的32次方，4G。

日期类型：
    date 日期(yyyy-mm-dd)
    datetime 日期时间(yyyy-mm-dd hh:mm:ss)
    timestamp 时间戳，自动记录 insert、update 操作时间

6.创建表练习：

use mydb;
create table emploee
(
 id int,
 name varchar(50),
 sex bit,
 birthday date,
 entry_date date,
 job varchar(50),
 salary decimal(10,2),
 resume text
);

7.修改表

alter table emploee add column image blob; //增加列

alter table emploee modify column job varchar(60); //修改列

alter table emploee drop column sex; //删除列

alter table emploee character set utf8; //修改表字符编码

alter table emploee change name username varchar(40); //修改列名

rename table emploee to users; //重命名表名

8.数据库 CURD (增删改查)

(1)
use mydb;
insert into users (id,username,birthday,entry_date,job,salary,resume) values
 ('1','dk','1988-8-8','2008-8-8','xxx','30000','xxx'); //增加记录

(2)
update users set salary=5000;

(3)
update users set salary=3000 where username='dk';

(4)
update users set salary=4000,job='ccc' where username='dk';

(5)
update users set salary=salary+1000 where username='dk';  //修改记录 

(6)
delete from users where username='dk';

(7)
delete from users;

(8)
truncate users; //删除记录

9.Select 语句

MySQL 中字符乱码问题

 由于我们使用的 MySQL 客户端与 MySQL 使用的字符编码方式不一样，从而导致我们在插入数据和取出数据时出现乱码。我们在本地客户端输入中文的时候，客户端一般

使用的是 GB2312 的编码方式，而我们的 MySQL 的编码方式不一定是 GB2312，当我们将中文以 GB2312 编码后，以二进制的形式发给 MySQL，那么 MySQL 用其它码表进行编码，

导致了我们插入数据时出现乱码。当我们从数据库取出数据时，是根据 MySQL 使用的编码方式编码，然后以二进制数据的形式发送给客户端，客户端再以 GB2312 进行解码，又导

致了我们查询时出现乱码。
 分析出原因后，我们应该控制 MySQL 在接受数据时以我们指定的形式进行解码，制定码，从而我们可以获得正确的中文字符。
        show variables like 'character%';  查询 MySQL 编码方式
 set character_set_client='gb2312'  根据查询到的变量名指定接受客户端数据的码表
 set character_set_results='gb2312' 根据查询到的变量名指定向客户端输出数据的码表

(1) 查询表中所有学生的信息

select * from student;

(2) 查询表中所有学生的姓名和对应的英语成绩

select name,english from student;

(3) 过滤表中重复数据

select distinct english from student;

(4) 在所有学生总分上加10分特长分

select (chinese+english+math+10) 总分 from student;

(5) 统计每个学生的总分

select (chinese+english+math) 总分,name from student;

(6) 查询姓名为王五的学生成绩

select * from student where name='王五';

(7) 查询英语成绩大于90分的同学

select name,english from student where english>90;

(8) 查询总分大于200分的所有同学

select name,(chinese+english+math) 总分 from student where (chinese+english+math) > 200;

(9) 查询英语分数在 80－90之间的同学

select name,english from student where english between 80 and 90;
select name,english from student where english >= 80 and english <= 90;

(10) 查询数学分数为89,90,91的同学

select name,math from student where math=89 or math=90 or math=91;
select name,math from student where math in (89,90,91);

(11) 查询所有姓李的学生成绩

select * from student where name like '李%';

(12) 查询数学分>80，语文分>80的同学

select name,math,chinese from student where math>80 and chinese>80;

(13) 对数学成绩排序后输出

select math,name from student order by math;

(14) 对总分排序后输出，然后再按从高到低的顺序输出

select name,(chinese+math+english) 总分 from student order by (chinese+math+english) desc;

(15) 对姓李的学生成绩排序输出

select name,(chinese+math+english) 总分 from student where name like '李%' order by (chinese+math+english) ;

10.合计函数 count

(1) 统计一个班级共有多少学生

select count(*) from student;

(2) 统计数学成绩大于等于90的学生有多少个

select count(math) from student where math>=90;

(3) 统计总分大于250的人数有多少

select count(*) from student where (chinese+math+english) > 250;

11.合计函数 sum

(1) 统计一个班级数学总成绩

select sum(math) from student;

(2) 统计一个班级语文、英语、数学各科的总成绩

select sum(chinese) 语文,sum(english) 英语,sum(math) 数学 from student;

(3) 统计一个班级语文、英语、数学的成绩总和

select sum(chinese)+sum(english)+sum(math) from student;

(4) 统计一个班级语文成绩平均分

select sum(chinese)/count(*) from student;

12.合计函数 avg

(1) 求一个班级数学平均分

select avg(math) from student;

(2) 求一个班级总分平均分

select avg(chinese+math+english) from student;

13.合计函数 max/min

求班级最高分和最低分

select max(chinese+math+english) from student;
select min(chinese+math+english) from student;

14. 使用group by 子句对列进行分组、使用having 子句过滤(含有聚合函数时，只有having才能过滤数据)

查询购买了几类商品，并且每类总价大于100的商品

select sum(price) 总价,product from orders group by product having 总价>100;

15.主键约束 (主键约束同时包含唯一约束和非空约束)

create table test
(
 id int primary key,
 name varchar(20)
);

16.自动增长 (通常我们不使用自动增长，而使用UUID，方便表合并)

create table test
(
 id int primary key auto_increment,
 name varchar(20)
);

17.非空约束

create table test
(
 id int primary key auto_increment,
 name varchar(20) not null
);

18.外键约束、唯一约束

create table husband
(
 id int primary key auto_increment,
 name varchar(20) not null
);

create table wife
(
 id int primary key auto_increment,
 name varchar(20) not null,
 husband_id int unique,
 constraint husband_id_FK foreign key(husband_id) references husband(id)
);

 

 

数据库表的设计

 

数据库表与表之间的关系有四种：一对一，一对多（多对一），多对多，自关联

我们在设计数据库表的时候往往取决于 Java 应用的实例对象。在一个应用中，有多少个对象就应该对应多少张表。

设计步骤：1.根据应用程序的对象分别创建对应的表 2.根据对象的简单属性为表增加列 3.对对象之间的关系进行分析，分析出是表四种关系中的哪一种。

 

1、一对一 关系

      一对一关系是指两个对象之间一一对应的关系。在一对一中，通常存在着主从关系。即一个对象完全依赖于另一个对象。比如人与身份证之间的关系。一个人对应着一个唯一的身份证，一个身份证又唯一指向一个人，但是人可以离开身份证而存在，身份证失去了人便没有意义。在这种关系中，通常在次要对象中增加一个对主要对象 id 的外键约束。并且还要有唯一约束和非空约束。因为一张身份证对应着一个人，如果没有唯一约束则可能出现两个人用一张身份证。如果没有非空约束，那么这个身份证就失去了意义。

 

create table person

(

     id int primary key auto_increment,

     name varchar(50)

);

 

create table card

(

     id int primary key auto_increment,

     city varchar(10),

     person_id int not null unique,

     constraint person_id_FK foreign key(person_id) references person(id)

);

 

2、一对多（多对一）关系

     一对多关系是指一个对象被多个对象引用，另一个对象维护着多个对象的集合，换句话说就是多个对象指向一个对象。在数据库中，我们在多的一方使用外键进行约束。比如公司部门与员工之间的关系，一个部门对应着多个员工，一个员工只从属于一个部门，那么我们在设计这个数据库的时候需要在员工表中增加一个对部门表 id 的引用，这个 id 的数据必须来自部门表。与一对一不同，一对一的外键除了外键约束，还具有唯一和非空约束。在多对一中不能进行唯一约束，因为多个员工可以从属于一个部门。

 

create table department

(

       id int primary key auto_increment,

      name varchar(20)

);

 

create table emploee

(

        id int primary key auto_increment,

       name varchar(20),

       department_id int not null,

       constraint department_id_FK foreign key(department_id) references department(id)

);

 

3、多对多关系

      多对多关系是指一个对象不仅同时指向多个对象，还有多个对象同时指向自己。在 Java 应用程序中，一个对象维护着另一个对象的集合，另一个对象也同时维护着这个对象的集合。比如教师与学生之间的关系，一个教师可以有多个学生，一个学生也拥有多个教师，所以在教师类中有一个学生对象的集合，一个学生对象中也拥有一个教师对象的集合。在数据库中，我们用两张表分别保存教师和学生的信息，为了维护他们之间的引用关系，我们还需要第三张表，第三张表保存着教师的 id 和学生的 id。表中的每一条记录分别保存着某个教师和某个学生的对应关系。在数据库表的设计中我们通常为每一张表指定一个主键列，在这张表中，我们使用教师 id 和学生 id 作为联合主键。

 

create teacher

(

      id int primary key auto_increment,

      name varchar(50)

);

 

create student

(

      id int primary key auto_increment,

      name varchar(50)

);

 

create teacher_student

(

      teacher_id int ,

      student_id int ,

      constraint teacher_id_FK foreign key(teacher_id) references teacher(id),

      constraint student_id_FK foreign key(student_id) references student(id),

      primary key (teacher_id,student_id)

);

 

4、自关联

      自关联是指对象引用了对象的本身。也就是在一个对象中包含了该对象字段。比如人，每个人都有一个父亲，而父亲又是属于人类。那么在人类中就维护了另一个人类的对象实例。在这种关系数据库设计中，我们应该加一个对本身 id 的引用。来指明该对象的父亲是谁。

 

create table person

(

      id int primary auto_increment,

      name varchar(50),

      parent int ,

      constraint persion_id_FK foreign key(parent) references person(id)

)