Mysql数据库和SQL语句

一、数据库介绍&安装

1. 什么数据库软件

在前期学习的过程中，对于数据的保存方式有两种体现：

一种是将数据保存到本地的文件中，优点是可以持久保存，但是数据管理查询等相当麻烦。

一种是将数据保存到本地的内存中，优点是效率高，但是程序运行结束后数据会消失。

数据库软件：保存数据的仓库，俗称数据库。它体现我们电脑中，就是一种应用软件系统。用来管理数据的软件。需要使用固定的语言（SQL语言）去操作文件中的数据。

我们主要是学习数据库的操作，而不是开发数据库软件。

2. 数据库软件介绍

数据库软件都是由第三方公司研发提供，我们只需要下载安装使用即可。

常见的数据库软件：

Oracle：它是Oracle公司的产品，功能强大，收费高。和java程序兼容性好，适合中大型项目。

BD2：IBM公司的数据库，它是收费的。功能一般。帮助文档和问题解决方案没有Oracle丰富。

SqlServer：微软数据库，功能一般，收费一般。和微软开发语言兼容性好，适合中小项目。

Sybase：Sybase公司的产品，市场占有率低。 powerDesigner软件，拖一拖生成表单。

MySql：早期瑞典一个公司发明，后期被sun公司收购，后期被Oracle。是开源的，功能一般，免费。和java程序兼容性好，适合中小型项目

Java开发应用程序主要使用的数据库：Oracle、BD2、MySQl。

3. 关系型数据库

在开发软件的时候，软件中的数据之间必然会有一定的关系存在，当把数据保存到数据库中时候，也要在数据库中维护数据之间的关系，而前面介绍的几种数据库软件都是可以维护数据关系的。因此我们也把他们叫做关系型数据库。

还有一种数据库软件叫做非关系型数据库，他同样需要维护数据之间的关系，只是维护的方式不同。

关系型数据库：可以维护数据库中数据之间的关系。保存数据时，需要使用E-R图来描述数据关系，然后根据E-R图来生成对应的数据表单保存数据。

E-R实体关系图

实体：可以理解为我们Java程序中的一个类，在E-R图中使用矩形表示。

实体中的数据，我们称为这个实体的数据，可以理解为java中类的成员变量，在E-R图中使用 椭圆表示。

实体和实体之间的关系在E-R图中使用菱形表示。

二、数据库的操作

学习对数据库的操作，首先要学习mysql数据库的连接以及内部存储方式。

1. 连接数据库

当一台电脑安装了mysql数据库之后，这个台电脑就可以称为是一个数据库服务器。用户可以连接这台电脑中的mysql数据库，进行数据的查询等操作。在开发中会有专门的高性能计算机用于安装操作数据库。

用户通过在dos窗口中输入：mysql （-h 连接的主机ip -P端口3306）-u 用户名 -p 密码

mysql -h 192.168.1.253 -P 3306 -u root -p 1234 前提是 192.168.1.253 主机远程的访问开启。

连接本机： 省略 -h 和 -P  主机和端口。直接使用：mysql -u 用户名 -p 密码

2. mysql数据库的内部存储结构

当一个电脑安装了数据库之后，那么这个台电脑中就可以保存数据，但是数据不能直接保存在数据库软件中，我们需要先在数据库软件中创建数据仓库，然后在数据仓库中创建数据表，数据表中用来保存数据。

总结：数据库软件，可以管理多个数据仓库。一个数据仓库可以管理多个数据表。每个数据表中可以存储多行数据记录。

3. SQL语句

如果想操作数据库软件中的数据仓库和数据表以及表中的数据，需要使用SQL语句。

SQL语句：Structured Query Language结构化查询语言。SQL语句不依赖于任何平台，对所有的数据库是通用的。SQL语句的语法标准是由W3C组织制定的，具有查询、操纵、定义和控制关系型数据库的四方面功能。

SQL语句是一个非过程性的语言，每一条SQL执行完都会有一个具体的结果出现。

比如前面学习的Java等语言属于过程性语言，在其中可以定义变量等完成复杂的运算。而SQL语句本身是不支持这些的，但是不同的数据库厂商针对自己数据库的特点在原有的SQL语句上进行的增强。比如 Sqlserver中的T-SQL、Oracle中的PL/SQL等可以编写复杂的SQL语句。

4. SQL分类

DDL：数据定义语言 - Data Definition Language，用来定义数据库的对象，如数据表、视图、索引等。

DML：数据处理语言 - Data Manipulation Language，在数据库表中更新，增加和删除记录。

DCL：数据控制语言 – Data Control Language，指用于设置用户权限和控制事务语句。

DQL：数据查询语言 – Data Query Language，用于查询数据表中的数据，通过select关键字。

我们学习SQL语句的路线：

1. 学习SQL数据对数据仓库的操作
2. 学习SQL语句对数据表的操作
3. 学习SQL语句对数据表中的数据记录操作

5. 查询所有数据库

（1）查询当前数据库软件中的所有数据仓库

语法： show databases;

（2）查看数据库创建规则（编码表等）

语法： show create database 数据库名;

当我们创建数据库的时候，如果没有指定编码表，默认使用的是安装数据库软件时指定的编码表。

6. 创建数据库语法

create database 数据库名; #创建时没有指定编码表，因此会使用安装数据库时默认的编码表。
create database 数据库名 character set 编码表名; #创建数据库会使用指定的编码表。
create database 数据库名 character set 编码表名 collate 排序规则; #使用指定的编码表同时还可以根据编码表指定排序规则。 规则查看MySQL_5.1 版本的API。

（1）创建一个名称为mydb1的数据库。

create database mydb1;

（2）创建一个使用utf8字符集的mydb2数据库。

create database mydb2 character set utf8;  #在sql 语句中utf-8的编码表名称不能写 -

（3）创建一个使用utf8字符集，并带校对规则的mydb3数据库。

create database mydb3 character set utf8 collate utf8_bin;

7. 删除数据库

语法： drop database 数据库名;

（1）删除前面创建的mydb1数据库。

drop database mydb1;

8. 修改数据库编码集

语法：alter database 数据库名称 character set 字符集 collate 比较规则;

（1）修改mydb2字符集为gbk

alter database mydb2 character set gbk;

9. 切换数据库和查看正在使用的数据

当我们创建好了数据仓库之后，需要在数据库中创建数据表。最终在数据表中保存数据。我一个数据库软件中可以创建多个数据仓库，所以在创建数据表之前，我们需要先切换到指定的数据库中。

（1）查询正在使用的数据库语法

select database(); #null表示当前并没有在使用任何的数据库。

（2）切换数据库语法

use 数据库名;

  

重点练习： 创建库、删除库、切换库的语句。

三、创建表的操作

有了数据仓库之后，我们就可以在数据仓库创建数据表，用来保存数据。

1. 查看所有表

语法：show tables;

2. 数据表的创建

语法：create table 表名(列名 类型(长度),列名 类型(长度)... );

数据表是基于java中的类创建出来的，一个类可以对应数据库中的一张表。我们创建数据表的目的是给数据表中保存数据。而我们的数据最终来源于Java程序。

有如下一个Java类，通过java类来创建一个对应的数据库表单。

class Student{
    private String name;
    private String sex;
    private String password;
    private Date birthday;
}

一个数据表可以存在很多列（字段），每列需要指定数据类型和长度。

在创建学生的这个数据表的时候，数据表中最少需要定义和Java中Student类中相同的属性（成员变量）。就需要把Java中类的属性和数据库中表的列一一对应，因此我们就需要研究在Mysql数据库中表的每一列的数据类型。

2.1 MySQL的数据类型

MySQL的数据类型可以参考MySQL的API。

字符串型

Java中的String 和char 类型 对应 mysql  中的  varchar(列的长度)、char(列的长度)

varchar：变长

char：定长

假设我们要存储 abcd 这个字符串。如果定义成varchar(10) 这时储存的数据不足10个，这时会把多余的释放。

如果使用char(10)，它会把存储的数据添加到10个长度。如果存储的长度超出了表中列的长度，存储报错。

数值型

Java中 ：byte、   short 、 int 、long 、float、double   

mysql中：tinyint 、smallint、int、 bigint、float、double 在创建数据表的时候，数值型也有自己的长度，一般不需要指定，使用默认的长度。

逻辑型 

Java中： boolean

mysql中：BIT

日期型

Java中：Date、Time、DateTime、TimeStamp

mysql中：DATE（年月日）、TIME（时分秒）、DATETIME（年月日时分秒）、TIMESTAMP（年月日时分秒）。

timestamp和datetime都可以保存年月日时分秒，但是timestamp它在保存数据时会自动更新保存数据时的当前时间。

大数据类型（图片文件类型的数据）

Java中： 字节流：InputStream。   字符流：Reader。

mysql中：BLOB 保存的字节数据   TEXT 保存字符数据

2.2 练习创建表

练习：创建如下表单

create table student(
   name varchar(32) ,
   sex varchar(10),
   password varchar(32),
   birthday date
);

2.3 查看表结构语法

语法：desc 表名;

3. 单表创建时约束

约束：通过某些限制，来规定当前数据表中的某列数据是否可以为null，是否可以重复，某一列中的数据在当前表中必须唯一等限制。

约束的目的：只是为了保证存储在数据表中的数据完整性和有效性。

（1）唯一约束：unique

列名 列的类型 unique; #该列（字段）的值不允许重复。   

在一张表中唯一约束的列可以有多列。

（2）非空约束：not null

列名 列的类型  not null; #表示该字段的值不能为空。

（3）主键约束：primary key

列名 列的类型  primary key; 

主键只能标识数据表中的某一列，使用这一列来区分此列数据和其他数据的不同。这一列的数据在整个数据表中是不允许重复的。效果 = 唯一 + 非空。

① 主键的选型：主键我们开发中不建议使用和业务密切相关的字段充当，创建一个与业务无关的字段充当，比如student表，我们不使用学号充当主键，我们创建一个id的字段充当。

② 主键可以有多个：如果只有一个主键，我们成为唯一主键，如果有多个主键，我们成为联合主键。

（4）自增长

列名 列的类型  auto_increment; 

数值类型的字段，每次添加的时候，无须指定值，由数据库指定值，并且该值用后，就不在使用。

① 自增长从1开始，值不会重复使用，添加的时候无须为自增长字段设值

② 自增长的字段也可以手动赋值，但是不推荐，会造成冲突

（5）练习

创建一个 studnet2 表，添加相应的约束：

create table student2(
   id int primary key auto_increment,  
   name varchar(32)  not null ,  
   sex varchar(10),
   password varchar(32) unique not null,  
   birthday date
);

 查看studnet2 表结构：desc student2；

 

4. 数据表结构修改

修改数据表：可以对表名、表中的列名、列的类型、列的约束进行增删改。

（1）语法

alter  table 表名 增/删/改 列名 类型(长度) 约束;

（2）增加列

alter table 表名 add 列名 类型(长度) 约束;

（3）修改现有列类型、长度和约束语法

alter table 表名 modify 列名 类型(长度) 约束;

（4）修改现有列名称语法

alter table 表名 change 旧列名 新列名 类型(长度) 约束;

（5）删除现有列语法

alter table 表名 drop 列名 ;

（6）修改表名语法

rename table 旧表名 to 新表名;

（7）修改表的字符集语法 

alter table 表名 character set utf8;

（8）练习

① 在student2表上增加height列

alter table student2 add height double ;

  

② 修改birthday列不能为null

alter table student2 modify birthday date not null;

③ 修改列名name为username

alter table student2 change name username varchar(48) not null;

 

④ 修改username列的长度为60

alter table student2 modify username varchar(60) not null;

⑤ 删除sex列

alter table student2 drop sex;

⑥ 将student2表名修改为person表

rename table student2 to person;

⑦ 将person 表的编码表修改为gbk编码表。

alter table person character set gbk;

⑧ 查看数据表信息

show create table 表名;

5. 数据表删除

语法：drop table 表名;

（1）删除前面创建的student表

drop table student;

 

四、简单数据表的增删改查

数据表的常见操作简称：CRUD：create  read  update  delete

1. 向数据表插入数据

语法：insert into 表名 (列名，列名，列名......) values (值，值，值......);

下图为表的结构

（1）给person表中插入数据

insert into person
    (id,username,password,birthday,height,age)
    values 
    (1,’zhangsan’,’1234’,’1990-09-09’,178.8,18);

（2）查看表中数据语法

select * from 表名;

（3）在插入数据的时候，如果某些列可以为null，或者是自动增长的列，或者有默认值的，在插入的时候可以省略。

insert into person
    (id,username,password,birthday)
    values
    (2,'lisi','abcd','1998-10-10');

（4）如果给表中的所有列插入数据，这时可以省略表名后面的列名，直接写values

insert into person values (3,'wangwui','aaaa','1988-9-8',167.8,25);

2. 表中数据的默认值

在创建表单时，针对于表中的字段可以给出默认值，当在给表中插入数据时，如果当前字段有默认值，在插入时可以省略，此时当前字段的值就会自动赋值为默认值。同时在插入输入时，也可以给当前字段重新赋值。

如果某个字段被设置为了主键约束，那么则不可以设置默认值。

如果某个字段被设置为了唯一约束，那么第一个插入的数据可以不用赋值，结果使用默认值，但是第二个插入数据在赋值时，一定要重新赋值，因为唯一约束不允许出现重复的值。

语法

create table 表名(
    列名 类型(长度) default 默认值,
    列名 类型(长度)...
);

练习—准备数据

create table teacher(
    id int primary key auto_increment,
    age int default 18 unique,
    name varchar(30) default '磊哥'
);

（1）给表中添加数据，有默认值的字段直接使用默认值

insert into teacher(id)values(1);

（2）依然使用默认值给表中添加数据

insert into teacher(id)values(2);

因为age是被unique唯一约束，所以第二次赋值时，必须重新赋值，但是name并没有被唯一约束，所以可以继续使用默认值。

insert into teacher(id,age)values(,77);

3. 数据记录修改操作

update 表名 set 列名=值,列名=值.... where条件语句;

练习

（1）修改person表中的所有用户的age 为 30岁

update person set age = 30;

注意：一般在修改数据表中的数据时，都需要加条件，而不能直接修改表中的某一列的所有值。

（3）修改姓名为zhangsan的这个用户的年龄为88

在修改数据的时候，需要添加条件，如果要添加条件，需要在修改语句的后面加上where关键字，在where后面添加具体的条件。

update person set age= 88 where username=’zhangsan’;

（4）把id为2 的用户 的 username 和password 修改为 lisi

update person set username=’lisi’ , password=’lisi’ where id = 2;

（5）把id为3的用户, 用户名修改为 中文的 王五;

update person set username=’王五’ where id =3;

在对数据表中的数据进行修改、插入的时候，使用的是dos窗口，这时dos窗口需要对插入的中文进行编码，然后把数据插入到数据库中。而dos窗口中使用的编码表是gbk。但是在mysql数据库中针对客户端控制的设置的编码表是utf8。这时只能修改数据库中默认的客户端程序的编码表。

MySQL有六处使用了编码表，分别为：client、connection、database、results、server、system。

修改mysql的配置文件，my.ini

修改完mysql的配置文件之后，需要重启mysql的服务。重新进入到表所在的数据仓库中。

4. 数据记录的删除操作

delete from 表名 where条件语句;

注意：如果删除表中的记录时，没有添加where条件，这时会把表中的所有数据删除。

delete from person; 把person表中的所有数据全部删除，但是person的表还存在。

drop table person; 把person数据表从数据库中删除。

（1）删除person表中username 为zhangsan 的用户信息

delete from person where username = ‘zhangsan’;

（3）删除表中所有的数据

delete from person;

（4）如果要删除一张表中的所有数据，这时可以使用 truncate table 表名;

truncate table person;

（5）面试题

删除表中的数据时没有加where 条件，会删除表中的所有数据，它与truncate 有什么区别？

delete删除表中的数据是按照行逐行删除。 效率低

truncate它是先把表删掉，然后再把表创建出来。 效率高

五、数据表记录的查询

查询当前某个数据表的数据，查询的方式有多种，有时需要查询所有数据，有时需要查询部分满足条件的数据，所以我们需要知道如何对数据的条件进行判断。

1. MySQL运算符

判断两个数据是否相等：  相等=     不等 <>

逻辑运算：and 逻辑与  or 逻辑或  not 逻辑非

区间判断： between ...and... 在两者之间取值

如： between 70 and 80

    等价于 >=70 <=80    注意前面那个数要比后面那个数要小

age >= 24 and age <=50  between 24 and 50

列表值取一：in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90

where 列名 in (值，值，值.......);

where 列名 = 值 or 列名=值 or 列名=值 .......

模糊查询：like ' pattern' 进行模糊查询 ，表达式有两个占位符： % 任意字符串 ， _ 任意单个字符。

例如：

name like '张%' 所有姓张学员    %张% 中间有张字的学员，字数不限制

name like '张_' 所有姓张名字为两个字学员   _张_ 三字中间为张的学员

null空判断： is null 判断该列值为空。    is not null 判断该列值不为空。

sql中对null的判断，不能写 = null   在sql 中 null = null 结果不成立。

2. 准备数据

create table student(
    id int primary key auto_increment,
    age int ,
    name varchar(32) not null,
    sex varchar(10) not null,
    score double not null,
    birthday date
);

insert into student (id,age,name,sex,score,birthday) values(null,23,'zhangsan','male',98.99,'1990-09-09');
insert into student (id,age,name,sex,score,birthday) values(null,23,'lisi','男',56.99,'1990-02-09');
insert into student (id,age,name,sex,score,birthday) values(null,24,'王五','女',75.99,'1988-01-01');
insert into student (id,age,name,sex,score,birthday) values(null,25,'赵六','男',80.99,'1980-11-12');
insert into student (id,age,name,sex,score,birthday) values(null,null,'秋香','女',84,null);

3. 查询表中的数据

selse 查询的内容 from 从哪张表中查询 where 查询的条件。

查询语法

select * from; # 查询表中的所有数据
select * from 表名 where 查询条件; # 根据条件查询表中的所有数据
select 列名，列名... from 表名; # 查询指定列的所有数据
select 列名，列名... from 表名 where 查询条件; # 根据条件查询指定列的所有数据

（1）查询所有学生的姓名和年龄

select name , age from student;

（2）查询表中年龄大于等于24岁的学生展示姓名和年龄

select age,name from student where age >= 24;

（3）查询年龄等于23的所有学生信息。

select * from student where age = 23;

（4）查询成绩在80~100(包含)之间的学生信息

select * from student where score >=80 and score <=100;
select * from student where score between 80 and 100;

（5）查询年龄>23，成绩>80的同学信息

select * from student where age > 23 and score > 80;

（6）查询年龄为18，23，25的同学信息

select * from student where age in(18,23,25);
select * from student where age = 18 or age = 23 or age = 25;

（7）查询所有姓赵的学生信息

select * from student where name like ‘赵%’;

（8）查询没有年龄的学员信息

select * from student where age is null;

（9）查询有年龄的学员信息

select * from student where age is not null;

4. 排序查询

有时候我们需要对查询的结果进行排序显示：使用order by 子句排序查询结果。

语法：select * from 表名 order by 列名 asc|desc ;

asc是升序排列，desc是降序排列

（1）对年龄排序按从高到低（降序）的顺序输出。

select * from student order by age desc;

（2）对学生年龄按照升序排序，年龄相同按照成绩降序

select * from student order by age asc , score desc;

5. 别名

别名：可以对查询出来的列名 起别名（另外的名字，理解为外号也可以）。

语法：select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;

在使用别名的时候，as 关键字可以省略。

（1）查询年龄和成绩，并指定别名。

select age as 年龄,score as 成绩 from student;

（2）起别名时省略 as 语句。

select age 年龄 , score 成绩 from student;

（3）面试

select age score from student;

select age , score from student;

什么区别？

第一个是把查询出来的学生年龄 起别名为score。

第二个是查询 当前student表中中的age 和score两列数据。

6. 排重查询

重复的数据只查看一次。

语法

select distinct 列名 from 表名 where 条件;

（1）排重查看student表中的年龄

select distinct age from student;

7. 截取查询

截取的查询需要使用到表中数据的下标，数据表中的每个数据都有下标，和数组类似，也是从0开始。

语法

select * from 表名 limit n; # 表示从0下表开始截取前N个数据
select * from 表名 limit n , m; # 表示从n下表开始截取m个数据，如果m超过最大下标，则返回n下标开始的所有数据。

（1）截取student表中的前3个数据。

select * from student limit 3;

（2）截取student表中1下表开始的后5个数据。

select * from student limit 1,5; 

六、SQL中的函数

聚合函数，分组函数，聚集函数，内建函数，合计函数.....

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：

count()函数 统计个数

sum()函数：求和

avg()函数：求平均值

max()、min() 函数 求最大值和最小值

1. count函数

语法

select count(*)|count(列名) from 表名 where 条件;
    # count(*) ： 统计某一列的数据，包含null
    # count(列名)：统计指定列的数据，不包含null

（1）统计一个班级共有多少学生？

select count(*) from student;
select count(age) from student;

（2）统计成绩大于80的学生有多少个？

select * from student where score > 80;
select count(*) from student where score > 80;
select count(score) from student where score > 80;

2. sum函数

语法：select sum(列名) from 表名 where 条件;

（1）统计一个班级成绩和

select sum(score) from student;

（2）分别统计年龄和成绩的和

select sum(age) 年龄 , sum(score) 成绩 from student;

（3）统计年龄和成绩和值

select sum(age) + sum(score) 和值 from student;

select sum(age+score) 和值 from student;

使用上述语句发现统计的和值结果是不正确的，原因是如果使用sum() 多列进行求和的时候，如果某一列中的值有null，这一列所在的行中的数据结果为0，null和任何数据相加都等于0。

可以使用mysql 数据库提供的函数： ifnull(列名,值)

select sum(ifnull(age,0) + ifnull(score,0)) 和值 from student;

在数据库中定义double类型数据，是一个近似值，需要确定准确的位数，这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数) numeric可以替代 double float

3. avg函数

语法：select avg(列名) from 表名 where 条件;

（1）求一个班级平均分 先计算出成绩总分，在除以成绩人数。

select sum(score)/count(score) from student;

select avg(score) from student;

4. max，min函数

语法：select max(列名),min(列名) from 表名 where 条件;

（1）求班级最高分和最低分

select max(score) 最高分,min(score) 最低分 from student;

5. group by分组函数

语法：select * from 表名 group by 列名;

group by 它可以根据指定列对数据进行归类。如果这一列中有重复的数据会被合并成一个。

准备数据

create table orders(
    id int,
    product varchar(20),
    price float
);

insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

对订单表中商品归类后，显示每一类商品的总价。

（1）先对商品进行归类：

select * from orders group by product;

（2）归类后显示商品的总价

select product,sum(price) from orders group by product;

（3）查询购买的商品价格总和超过150的商品

select product , sum(price) from orders group by product where sum(price) > 150;
    # 在 where 条件后面不可以跟聚合函数
select product , sum(price) from orders group by product having sum(price) > 150;
    # 在 having 条件后面不可以跟聚合函数,条件的筛选和where是一样的。

如果使用group by 对数据进行分组之后还要过滤。这时一般不能使用where，因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数，只能使用having关键字。

（4）面试题

where和having 都可以完成数据的条件判断，但是having后面可以跟上述的函数。