Java Web基础入门第四十一讲 SQL简单入门

数据库种类

市面上的数据库有如下几种：

• SQL Server、Oracle；
• MySQL、DB2；
• SyBase

在这一学习阶段中，我主要讲解MySQL数据库，并且我使用的是5.7.26 MySQL Community Server。

MySQL数据库的安装和配置

关于MySQL数据库的安装和配置，我在这里就不多说了，大家可以参考我的这篇博客——《如何在Windows10平台中安装MySQL数据库的最新版本》。MySQL数据库安装和配置好之后，可以使用命令行窗口连接MySQL数据库，命令如下：

mysql –u 用户名 –p 密码

怎么知道MySQL数据库是否安装和配置成功呢？可参考下图：

数据库服务器、数据库和表的关系

所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。数据库服务器、数据库和表的关系如下图所示：

数据在数据库中的存储方式

数据在数据库中的存储方式可用下图来粗略说明：

• Java是使用对象封装数据的。例如程序产生了如上所示2个User对象，这些对象的数据要想保存到数据库中，需要在数据库中创建与之对应的表，一个User对象中封装的数据，要保存到数据库中，一般就要在数据库中创建一个与之对应的表；
• 对象的属性定义为表头，对象的数据对应于表中的一条记录；
• 每个对象对应于表中的一条记录。

明白数据库使用表保存数据后，如何在数据库中创建表呢？不急，我们慢慢介绍。

创建数据库

创建数据库的语法为：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[, create_specification] …]

create_specification指代的是[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name。

• CHARACTER SET：指定数据库采用的字符集；
• COLLATE：指定数据库字符集的比较方式。

练习

练习一、创建一个名称为mydb1的数据库。

create database mydb1;

练习二、创建一个使用utf-8字符集的mydb2数据库。

create database mydb2 character set utf8;

练习三、创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

create database mydb3 character set utf8 collate utf8_general_ci;

通过查看MySQL5.1 参考手册，我们知道utf8(UTF-8 Unicode)校对规则有：

查看、删除数据库

显示数据库语句：

SHOW DATABASES

显示数据库创建语句：

SHOW CREATE DATABASE db_name

数据库删除语句：

DROP DATABASE [IF EXISTS] db_name

练习

练习一、查看当前数据库服务器中的所有数据库。

show databases;

练习二、查看前面创建的mydb2数据库的定义信息。

show create database mydb2;

练习三、删除前面创建的mydb1数据库。

drop database mydb1;

修改、备份、恢复数据库

修改数据库语法：

ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification[, alter_specification] …]

alter_specification指代的是[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name。
备份数据库表中的数据命令：

mysqldump -u 用户名 -p 数据库名 > 文件名.sql

千万要注意：上面是一个Windows命令，而不是一个SQL语句。
恢复数据库语法：

Source 文件名.sql

千万要注意：上面不是一个Windows命令，而是一个SQL语句。

练习

练习一、查看服务器中的数据库，并把其中某一个库的字符集修改为gbk。

alter database mydb2 character set gbk;
show create database mydb2;

练习二、演示数据库的恢复和备份。
为了演示数据库的备份，我们得提前准备好数据，如下：

接着，对test数据库作备份操作，启动一个Windows命令行窗口，执行如下命令：

这时，可以在D盘下找到备份好的test.sql脚本文件，打开它，内容如下：

然后，再演示数据库的恢复。数据库的恢复有2种方式，下面我会分别讲解这两种恢复方式。

• 第一种恢复方式
  
• 第二种恢复方式
  这里，我再次重申，恢复只能恢复库里面的数据，无法恢复库及库里面的数据。所以，为了恢复库，要先创建库：
  
  接着，对test数据库作恢复操作，启动一个Windows命令行窗口，执行如下命令：
  

创建表(基本语句)

创建表的语法为：

CREATE TABLE table_name
(
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校对规则

• field：指定列名；
• datatype：指定列类型。

注意以下两点：

1. 创建表前，要先使用use db语句使用库；
2. 创建表时，要根据需要保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

练习

创建一个员工表，如下所示：

创建表的语句为：

create table employee
(
	id int,
	name varchar(40),
	sex varchar(4),
	birthday date,
	entry_date date,
	job varchar(40),
	salary decimal(8,2),
	resume text
);

查看库的所有表：

show tables;

查看表的创建细节：

show create table employee;

查看表的结构：

desc employee;

MySQL常用数据类型

这里可参考我的这一篇博客——《Java Web基础入门第四十二讲 MySQL常见数据类型详解》。

修改表

使用ALTER TABLE语句追加，修改或删除列的语法分别如下：

ALTER TABLE table ADD (column datatype [DEFAULT expr][, column datatype]...);

ALTER TABLE table MODIFY (column datatype [DEFAULT expr][, column datatype]...);

ALTER TABLE table DROP (column);

修改表的名称：

Rename table 表名 to 新表名;

修改表的字符集：

alter table 表名 character set 字符集;

练习

练习一、在上面员工表的基础上增加一个image列。

alter table employee add image blob;

练习二、修改job列，使其长度为60。

alter table employee modify job varchar(60);

练习三、删除sex列。

alter table employee drop sex;

练习四、表名改为user。

rename table employee to user;

练习五、修改表的字符集为utf-8。

alter table user character set utf8;

练习六、列名name修改为username。

alter table user change column name username varchar(40);

删除表

删除表的语法为：

drop table 表名;

数据库中的CRUD语句

Insert语句

使用INSERT语句向表中插入数据，语法如下：

INSERT INTO table[(column [, column...])] VALUES(value [, value...]);

注意以下几点：

1. 插入的数据应与字段的数据类型相同；
2. 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中；
3. 在values中列出的数据位置必须与被加入的列的排列位置相对应；
4. 字符和日期型数据应包含在单引号中；
5. 插入空值，不指定或insert into table value(null)。

练习

使用insert语句向表中插入三个员工的信息。

千万要注意：使用insert语句向表中插入数据时，字符和日期要包含在单引号中。
插入数据的第一种方式：

insert into employee(id,username,birthday,entry_date,job,salary,resume) values(1,'liayun','1992-10-06','2015-09-12','Java程序员',7500,'我是一名合格的程序员');

插入数据的第二种方式：

insert into employee values(2,'yezi','1993-10-06','2015-09-12','Java程序员',7500,'我是一名合格的程序员');

插入数据的第三种方式：

insert into employee values('3','liyunling','1990-10-06','2015-09-12','Java程序员','7500','我是一名合格的程序员');

插入数据时，一定要注意乱码问题，乱码问题至今为止我也没搞清楚，只是知道怎么解决而已，关于这点我也做了笔记，可以参考我的这篇博客——《Java Web基础入门第四十三讲 MySQL中文乱码问题》。

Update语句

使用update语句修改表中数据，语法如下：

UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2, ...] [WHERE where_definition]

• UPDATE语法可以用新值更新原有表行中的各列；
• SET子句指示要修改哪些列和要给予哪些值；
• WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

练习

在上面创建的employee表中修改表中的纪录。

1. 将所有员工薪水修改为5000元；

   update employee set salary=5000;
   

2. 将姓名为‘张三’的员工薪水修改为3000元；

   update employee set salary=3000 where username='张三';
   

3. 将姓名为‘liayun’的员工薪水修改为4000元，job改为前端工程师；

   update employee set salary=4000,job='前端工程师' where username='liayun';
   

4. 将‘yezi’的薪水在原有基础上增加1000元。

   update employee set salary=salary+1000 where username='yezi';
   

Delete语句

使用delete语句删除表中数据，语法如下：

delete from tbl_name [WHERE where_definition]

• 如果不使用where子句，将删除表中所有数据；
• delete语句不能删除某一列的值（可使用update）；
• 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句；
• 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题；
• 删除表中数据也可使用TRUNCATE TABLE语句，它和delete语句有所不同：delete语句删除整个表时，是一行行删；truncate table语句删除整个表是摧毁整个表，再重建表结构。

练习

练习一、删除employee表中id为’5’的记录。

delete from employee where id=5;

练习二、删除employee表中所有记录。

delete from employee;

练习三、使用truncate删除表中记录。

truncate table employee;

Select语句(一)

基本select语句：

SELECT [DISTINCT] *|{column1, column2, column3...} FROM table;

• select指定查询哪些列的数据；
• column指定列名；
• *号代表查询所有列；
• from指定查询哪张表；
• DISTINCT可选，指显示结果时，是否剔除重复数据。

练习

使用如下SQL语句建一张学生表(student)：

create table student(
	id int,
	name char(20),
    chinese float,
    english float,
    math float
);

并插入一些数据，使student表显示为：

练习一、查询表中所有学生的信息。

select * from student;

练习二、查询表中所有学生的姓名和对应的英语成绩。

select name,english from student;

练习三、过滤表中重复的英语成绩数据。

select distinct english from student;

Select语句(二)

在select语句中可使用表达式对查询的列进行运算：

SELECT *|{column1｜expression, column2｜expression，...} FROM table;

在select语句中还可使用as语句：

SELECT column as 别名 from 表名;

练习

对于以上student表，有如下练习：

• 练习一、在所有学生总分上加10分特长分；

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

• 练习二、统计每个学生的总分；

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

• 练习三、使用别名表示学生分数。

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

  或者

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

Select语句(三)

使用where子句，进行过滤查询，在where子句中经常使用的运算符有：

注意：在like语句中，%代表零个或多个任意字符，_代表一个字符，例first_name like '_a%'';。

练习

对于以上student表，有如下练习：

• 练习一、查询姓名为王五的学生成绩；

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

• 练习二、查询英语成绩大于90分的同学；

  select * from student where english>'90';
  

• 练习三、查询总分大于200分的所有同学；

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

• 练习四、查询英语分数在80-90之间的同学；

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

  但是要注意以下sql语句：

  select name from student where english between 80 and 90;
  

  等价于：

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

• 练习五、查询数学分数为89、90、91的同学；

  select * from student where math in(89,90,91);
  

• 练习六、查询所有姓李的学生成绩；

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

• 练习七、查询数学分>80，语文分>80的同学。

  select * from student where math>80 and chinese>80;
  

Select语句(四)

使用order by子句排序查询结果：

SELECT column1, column2, column3, ... FROM table order by column asc|desc;

• order by指定排序的列，排序的列即可是表中的列名，也可以是select语句后指定的列名；
• asc是升序、desc是降序；
• order by子句应位于select语句的结尾。

练习

对于以上student表，有如下练习：

• 练习一、对数学成绩排序后输出；

  select name,math from student order by math;
  

• 练习二、对总分排序后输出，然后再按从高到低的顺序输出；

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

  或者

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

• 练习三、对姓李的学生成绩排序输出。

  select * from student where name like '李%' order by (chinese+english+math) desc;
  

Select语句(五)

使用group by子句对列进行分组：

SELECT column1, column2, column3, ... FROM table group by column;

还可使用having子句过滤：

SELECT column1, column2, column3, ... FROM table group by column having ...;

练习

先使用如下SQL语句新建一张订单表(orders)：

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

再向订单表中插入一些数据，如下：

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

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

练习二、查询购买了几类商品，并且每类总价大于100的商品。

select product from orders group by product where sum(price)>100;

注意：以上查询语句是错误的，因为where子句后面不能有合计函数。正确的查询语句应为：

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

记住：having和where均可实现过滤，但在having中可以使用合计函数，having通常跟在group by后，它作用于组。

合计函数

count

count(列名)返回某一列，行的总数。语法如下：

select count(*)|count(列名) from tablename [WHERE where_definition];

关于count函数的一个细节：count函数只统计这一列有值的行。

练习

对于以上student表，有如下练习：

• 练习一、统计一个班级共有多少学生？

  select count(name) from student;
  

  或者

  select count(*) from student;
  

• 练习二、统计数学成绩大于80的学生有多少个？

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

• 练习三、统计总分大于250的人数有多少？

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

sum

sum函数返回满足where条件的行的和。语法如下：

select sum(列名)｛,sum(列名),...｝from tablename [WHERE where_definition];

注意：

1. sum仅对数值起作用，否则会报错；
2. 对多列求和，,号不能少。

练习

对于以上student表，有如下练习：

• 练习一、统计一个班级数学总成绩；

  select sum(math) from student;
  

• 练习二、统计一个班级语文、英语、数学各科的总成绩；

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

• 练习三、统计一个班级语文、英语、数学的成绩总和；

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

• 练习四、统计一个班级语文成绩平均分。

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

avg

avg函数返回满足where条件的一列的平均值。语法如下：

select avg(列名)｛,sum(列名),...｝from tablename [WHERE where_definition];

练习

对于以上student表，有如下练习：

• 练习一、求一个班级数学平均分；

  select avg(math) from student;
  

• 练习二、求一个班级总分平均分。

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

max和min

max/min函数返回满足where条件的一列的最大/最小值。语法如下：

select max(列名) from tablename [WHERE where_definition]; 

练习

对于以上student表，求班级最高分和最低分（数值范围在统计中特别有用）。

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

时间日期相关函数

这里，我只稍微讲一下下面几个函数。

• ADDTIME(date2, time_interval)：将time_interval加到date2上去。
  
  温馨提示：字符串和时间日期的引号问题。
• CURRENT_TIMESTAMP( )：得到某一刻的时间值，如2016-08-12 08:59:11。
  
• NOW( )：得到当前这一刻的时间值，如2016-08-12 09:01:41。
  

字符串相关函数

数学相关函数

定义表的约束

定义主键约束

定义主键约束的关键字是primary key，即不允许为空，也不允许重复。主键列的数据是不能重复的，并且还要是唯一的。
例，使用如下SQL语句创建一个表。

create table student (
	id int primary key, 
    name varchar(40)
);

现在，可以使用如下insert语句向student表中插入数据了。

insert into student(id,name) values(1,'aaa');

但不能使用如下insert语句向student表中插入数据。

insert into student(name) values('aaa');

温馨提示：以后在设计表的时候，每个表必须要有一列是主键列，也即每个表必须要有一列来唯一的标识这个表里面的每一条记录，一般来说把表的id这一列设置为主键列。并且一个表只能有一个主键列。
当然了，还可使用关键字auto_increment定义主键自动增长，让数据库自己去维护这一列的值。例如，使用如下SQL语句创建一个表。

create table student (
    id int primary key auto_increment, 
    name varchar(40)
);

这时，可以使用如下insert语句向student表中插入数据。

insert into student(name) values('aaa');
insert into student(name) values('bbb');

此时，删除掉id为2的一条记录，然后再次插入一条新的记录，如下：

delete from student where id=2;
insert into student(name) values('ccc');

现在查询student表，姓名为ccc的id到底是2还是3呢？答案显然是3。

定义唯一约束

定义唯一约束的关键字是unique。
例，使用如下SQL语句创建一个表。

create table student (
    id int primary key auto_increment, 
    name varchar(40) unique
);

可使用如下insert语句向student表中插入数据。

insert into student(name) values('liayun');

温馨提示：约束加的越严格越好，宁可错杀一千，不可放过一个。

定义非空约束

定义非空约束的关键字是not null。
例，使用如下SQL语句创建一个表。

create table student (
    id int primary key auto_increment, 
    name varchar(40) unique not null
);

定义外键约束

假设现在要设计一个夫妻关系管理系统，丈夫表里面肯定会有一个id字段，除此之外还有name字段；妻子表里面除了有id、name等字段外，应该还要有一个husband_id字段，该字段用于表示这个妻子的丈夫是谁，这个时候就需要用到外键约束了。

• husband表

  create table husband (
      id int primary key,
      name varchar(40)
  );
  

• wife表

  create table wife (
      id int primary key,
      name varchar(40),
      husband_id int,
      constraint husband_id_FK foreign key(husband_id) references husband(id)
  );
  

  现在对wife表中定义外键约束的语句进行详细解释：