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表中定义外键约束的语句进行详细解释:
    在这里插入图片描述

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页