JavaWeb05- 就这几步轻松操作MySQL数据库!

mysql-1

一.数据库

1. 数据库介绍

数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以通过sql语句对数据库中的数据进行增加,修改,删除及查询操作

2. 关系型数据库

关系数据库(Relationship DataBase Management System 简写:RDBMS) ,描述是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。说白了就是描述实体与实体之间的关系的数据库.例如用户购物下订单,订单包含商品.他们之间的关系可以通过E-R图表示(明天讲).

数据库发展历程如下所示:

l没有数据库,使用磁盘文件存储数据;

l层次结构模型数据库;

l网状结构模型数据库;

l关系结构模型数据库,使用二维表格来存储数据;

l关系-对象模型数据库;

3. 常见的关系型数据库

Oracle数据库:Oracle是殷墟(yīn Xu)出土的甲骨文(oracle bone inscriptions)的英文翻译的第一个单词,在英语里是“神谕”的意思,由Larry Ellison 和另两个编程人员在1977创办,他们开发了自己的拳头产品,在市场上大量销售,1979 年,Oracle公司引入了第一个商用SQL 关系数据库管理系统。Oracle公司是最早开发关系数据库的厂商之一,其产品支持最广泛的操作系统平台。目前Oracle关系数据库产品的市场占有率名列前茅。

SQL Server数据库:1987 年,微软和 IBM合作开发完成OS/2,IBM 在其销售的OS/2 ExtendedEdition 系统中绑定了OS/2Database Manager,而微软产品线中尚缺少数据库产品。为此,微软将目光投向Sybase,同Sybase 签订了合作协议,使用Sybase的技术开发基于OS/2平台的关系型数据库。1989年,微软发布了SQL Server 1.0 版。

DB2数据库: 作为关系数据库领域的开拓者和领航人,IBM在1997年完成了System R系统的原型,1980年开始提供集成的数据库服务器—— System/38,随后是SQL/DSforVSE和VM,其初始版本与SystemR研究原型密切相关。DB2 forMVSV1 在1983年推出。该版本的目标是提供这一新方案所承诺的简单性,数据不相关性和用户生产率。1988年DB2 for MVS 提供了强大的在线事务处理(OLTP)支持,1989 年和1993 年分别以远程工作单元和分布式工作单元实现了分布式数据库支持。最近推出的DB2 Universal Database 6.1则是通用数据库的典范,是第一个具备网上功能的多媒体关系数据库管理系统,支持包括Linux在内的一系列平台。

Sybase数据库:Sybase公司成立于1984年,公司名称“Sybase”取自“system”和 “database” 相结合的含义。Sybase公司的创始人之一Bob Epstein 是Ingres 大学版(与System/R同时期的关系数据库模型产品)的主要设计人员。公司的第一个关系数据库产品是1987年5月推出的Sybase SQLServer1.0。Sybase首先提出Client/Server 数据库体系结构的思想,并率先在Sybase SQLServer 中实现。

MySQL数据库: mySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,Sun公司又被Oracle公司收购。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库

4. 数据库管理系统

我们通常将数据库管理系统(DataBase Management System,简写dbms)称为数据库,大白话就是我们安装的软件,当我们安装了数据库之后(数据库服务器),就可以在数据库服务器中创建数据库,每个数据库中还可以包含多张表.

通过上述图我们知道了数据库与表之间的关系,那么我们的数据又是怎样存储在数据库中。

数据库中的表就是一个多行多列的表格。在创建表时,需要指定表的列数,以及列名称,列类型等信息。而不用指定表格的行数,行数是没有上限的。

表中的列我们称之为字段,表中的行我们称之为记录。

用我们熟悉的java程序来与关系型数据对比,就会发现以下对应关系。

类-------表

类中属性-------表中的字段

对象--------记录。

5. mysql数据库安装,卸载与配置

mysql的安装与配置详见附件:MySQL的安装.doc

6. mysql登录

进入命令窗口

格式:mysql [-h主机地址] -u用户名 -p[密码]

启动mysql服务命令 net start mysql

关闭mysql服务命令 net stop mysql

7. mysql数据库密码重置

a. 停止mysql服务器 运行输入services.msc 停止mysql服务

b. 在cmd下,输入mysqld --console --skip-grant-tables 启动服务器,出现一下页面,不要关闭该窗口

c. 新打开cmd,输入mysql -uroot 不需要密码

use mysql;

update user set password=password('abc') WHERE User='root';

d. 关闭两个cmd窗口

二.SQL语句

1. SQL介绍

SQL:结构化查询语言(Structured Query Language),是一种数据库查询和程序设计语言,用于存取、查询、更新数据以及管理关系数据库系统.

2. SQL分类

数据定义语言:简称DDL(Data Definition Language)

用来定义数据库对象:数据库,表,列等,例如创建、删除、修改数据库和表结构等;

数据操作语言:简称DML(Data Manipulation Language)

用来对数据库中表的记录进行更新,例如:增、删、改表记录;

数据查询语言:简称DQL(Data Query Language)

用来查询数据库中表的记录。

数据控制语言:简称DCL(Data Control Language)

用来定义数据库的访问权限和安全级别,及创建用户;

DDL是对数据库或表的结构进行操作,而DML是对表的记录进行操作(增、删、改)。我们以后最常用的就是DDL、DML、DQL。

3. 书写注意事项

lSQL语句可以单行或多行书写,以分号结尾

lMySQL数据库的SQL语句不区分大小写,建议使用大写,例如:SELECT * FROM user

4. DDL数据定义语言

对数据库的操作

创建数据库

格式:create database 数据库名称;

例如:create database day04;-- 使用数据库默认字符集.

扩展:

格式:create database [if not exists] 数据库名称 [character set 字符集] [collate 校对规则];

例如:

create database day0401 character set utf8;-- 使用指定的编码创建数据库

create database day0402 character set utf8 collate utf8_general_ci;-- 使用指定的编码和校对规则创建数据库

删除数据库

格式:drop database数据库名称;

例如:drop database day0402;

修改数据库

修改数据库编码或者校对规则

格式:alter database 数据库名称 character set 编码 collate 校对规则

例如:alter database day0401 character set gbk;-- 修改其字符集为gbk

常见的命令

查看所有数据库

show databases;

查看数据库创建语句

格式:show create database 数据库名称;

例如:show create database day04;

查看字符集和校对规则

show character set;

对表的操作

常见的命令

切换数据库:

格式:use 数据库名称;

例如:use day04;

查看当前所在的数据库

select database();

查看当前数据库下所有的表

show tables;

查看表结构

格式:desc 表名称;

例如:desc user;

查看建表语句

格式: show create table 表名称;

例如: show create table user01;

mysql的数据类型

java中的数据类型

mysql中的数据类型

备注

byte

tinyint

short

smallint

int

int

long

bigint

float

float

double

double

double(m,d) m数字长度,d精度及小数位,double(5,2)表示它的最大值是:999.99

String

charvarchar()

char固定长度的字符串.默认255,如果存储的字符没有达到指定长度,mysql将会在其后面用空格补足到指定长度;varchar可变长度的字符串,长度可以由我们自己指定,它能保存数据长度的最大值是65535,如果存储的字符没有达到指定的长度,不会补足到指定长度;

java.sql.Date

date

日期,格式为yyyy-MM-dd

java.sql.Time

time

时间,格式为hh:mm:ss

java.sql.Timestamp

timestamp

时间戳,格式'YYYY-MM-DD HH:MM:SS'.若设置为空,将该列设置为当前的日期和时间

datetime

时间,日期,格式'YYYY-MM-DD HH:MM:SS'

大文本 Clob

tinytext 255Btext 64kblongtext 4gb

大数据 Blob

tinyblob 255Bblob 64kblongblob 4gb

创建表

格式:

create table 表名(字段描述 ,字段描述 ,....);-- 最后一个字段没有逗号

字段描述格式:

字段名称 字段类型 [字段约束]

例如:

create table user(

id int,

username varchar(50),

password varchar(20)

);

练习:

创建一个张表user,该表具有以下字段:

id 整型 主键 自动增长

username 字符串 长度20 非空

password 字符串 长度20 非空

gender 字符串 长度10

email 字符串 长度50 唯一 非空

role 字符串 长度10 默认值 “admin”

registTime 时间戳

create table user(

id int,

username varchar(20),

password varchar(20),

gender varchar(10),

email varchar(50),

role varchar(10),

registTime timestamp

);

修改表名称修改表

格式:

1.alter table 表名 rename [to] 新表名;

2.rename table 表名 to 新表名;

例如:

1.alter table user rename to user10;

2.rename table user10 to user;

添加字段(列) add

格式:alter table 表名 add [column] 字段描述;

例如:alter table user add column memo varchar(100);

修改字段(列)类型 modify

格式:alter table 表名 modify [column] 新字段描述

例如:alter table user modify memo int;

修改字段(列)名称 change

格式:alter table user change [column] 旧字段名称 新字段描述;

例如: alter table user change memo introduce varchar(100);

删除字段(列) drop

格式:alter table 表名 drop [column] 列名;

例如:alter table user drop introduce;

删除表

格式:drop table 表名;

例如:drop table user;

5. DML数据操作语言

插入数据

不指定列插入

格式:insert into 表名 values(字段值1,字段值2,...);

例如:insert into user values(null,'laobai','1234','male','laobai@126.com',null,null);

注意:

l 没有给出要插入的列,表示插入所有列;

值的个数必须是该表的列的个数;

值的顺序,必须与表创建时给出的列的顺序相同。

插入指定列值

格式:insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...);

例如:insert into user(username,password,email,registTime)

values('laoqi','1234','laoqi@126.com',null);

注意:表名后面是当前表中部分字段名称

插入全部列值

格式:insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...);

例如:insert into user(id,username,password,gender,email,role,registTime)

values(1,'shijin','1234','male','shijin@126.com',null,null);

注意:

表名后面是当前表中所有字段(列)

多个字段之间使用逗号分隔

字段值必须使用引号(建议单引号),如果是整型数据引号可以省略。

注意(编码问题):

插入中文的时候会报错,如下图:

因为mysql的客户端编码的问题我们的是utf8,而系统的cmd窗口编码是gbk.

查看所有的mysql编码

show variables like 'character%';

解决方案:

1.临时修改

set 变量名称=gbk;

例如:

set character_set_client=gbk,character_set_connection=gbk,character_set_results=gbk;

重启mysql服务器后,就回复原样了.

2.永久修改

在mysql安装目录下,

更新数据

格式:update 表名 set 字段名1=字段值1, 字段名2=字段值2, .....[where 条件];

例如:

1.update user set gender='male' ;## 修改所有人的性别为’male’

2.update user set username='xusanduo',email='xusanduo@qq.com' where id=3;## 修改id为3的人username和email.

删除数据

格式:delete from 表名 [where 条件];

例如:

1.delete from user;## 删除所有

2.delete from user where id=6; ##删除id为6的记录.

扩展:

删除整张表的内容,两种实现方式:

1.delete from 表名;

2.truncate [table] 表名;

区别:

1.delete属于DML语句 ,truncate属于DDL语句

2.delete是一行一行删除,truncate是将表结构销毁,再重新创建表结构,数据多的时候,效率高.

6. 约束

mysql中常用的约束有主键约束,非空约束,唯一约束,外键约束(明天讲)

主键约束(primary key)

用于标识当前记录的字段。可以是一个字段,也可以是多个字段。它的特点是:数据唯一,不能为null.

三种写法:

格式1:定义表,声明字段时,定义主键.特点:primary key 只能修饰一个字段

例如:

create table user01(

id int primary key,

username varchar(20)

);

格式2:定义表,声明字段之后,在约束区域定义主键。特点 [constraint] primary key (字段1,字段2,....) 可以设置多个字段

例如:

create table user02(

id int ,

username varchar(20),

primary key(id)

);

格式3:定义表,声明字段,表创建之后。修改表结构添加约束。特点:也可以设置多个字段,更灵活。

create table user03(

id int ,

username varchar(20)

);

alter table user03 add primary key(id);

一般主键id为int的时候,还习惯配合 auto_increment使用,使主键自增.插入数据的时候,id列可以不写,写的时候写null,让数据库维护id.

例如:

create table user04(

id int primary key auto_increment,

username varchar(20)

);

唯一约束(unique)

被修饰的字段不能重复,但是对null值不起作用.

三种写法:

格式1:定义表,声明字段时,定义唯一约束.特点:unique 只能修饰一个字段

例如:

create table user01(

id int unique,

username varchar(20)

);

格式2:定义表,声明字段之后,在约束区域定义唯一约束。特点 [constraint] unique (字段1,字段2,....) 可以设置多个字段

例如:

create table user02(

id int ,

username varchar(20),

unique(id)

);

格式3:定义表,声明字段,表创建之后。修改表结构添加约束。特点:也可以设置多个字段,更灵活。

create table user03(

id int ,

username varchar(20)

);

alter table user03 add unique(id);

非空约束(not null)

被修饰字段不能为null.

格式:定义表,声明字段时,添加非空约束.

create table user01(

id int,

username varchar(20) not null default ‘xu’

);

还可以使用default,给定一个默认值.

7. DQL数据查询语言

select基本查询

查询指定列

select 字段 from 表名;

查询指定字段信息,如果要查询多个字段

select 字段1,字段2,...from 表名;

查询所有列

select * from 表名;

注意:使用"*"在练习,学习过程中可以使用,在实际开发中,不建议使用。

去掉重复记录

select distinct 字段 from 表名;

注意:distinct它的作用是去除重复.

使用别名

使用as 别名可以给表中的字段,表设置别名.

例如: select name as 书名 from products;

在查询中可以直接对列进行运算

我们在sql操作中,可以直接对列进行运算。

ifnull函数使用

在对数值类型的列做运算的时候,如果做运算的列的值为null的时,运算结果都为null,为了解决这个问题可以使用ifnull函数,使用方法 ifnull(字段,0)

练习:

创建表products

CREATE TABLE `products` (

`id` INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(40) ,

`price` DOUBLE ,

`category` VARCHAR(40) ,

`pnum` INT(11) ,

`description` VARCHAR(255)

);

向表中插入数据

INSERT INTO `products` VALUES(NULL,'感悟',100,'励志',100,'一次心灵的鸡汤');

INSERT INTO `products` VALUES(NULL,'java与模式',67,'计算机',200,'让你的编程,从些不一样');

INSERT INTO `products` VALUES(NULL,'java并发编程实战',190,'计算机',49,'实战大于理论');

INSERT INTO `products` VALUES(NULL,'设计模式解析',88,'计算机',86,'头脑风暴');

INSERT INTO `products` VALUES(NULL,'搭地铁游上海',28,'生活百科',120,'一次不一样的旅行');

INSERT INTO `products` VALUES(NULL,'时空穿行',65,'科技',87,'这是一本好书');

INSERT INTO `products` VALUES(NULL,'中国国家地理',45,'生活百科',100,'了解你生活的国家');

INSERT INTO `products` VALUES(NULL,'欧洲', NULL,'生活',200,'你梦中向往的地方');

INSERT INTO `products` VALUES(NULL,'网管员必备宝典',35,'计算机',120,'上网新手必备书籍');

1. 查询出所有商品信息

2. 查询出所有商品的名称,价格,类别及数量信息

3. 查询出所有的商品类别

4. 查询出所有商品的名称及价格,将所有商品价格加10

5. 查询出每一个商品的总价及名称

where字句

我们在开发中,使用select操作,一般都是有条件查询,那以我们介绍一下关于where子句的使用

格式 :select 字段 from 表名 where 条件;

where条件种类:

1.比较运算符

> >= < <= = !=(<>)

2.逻辑运算符

and or not

3.between ...and

相当于 >= and <=

注意:between 后面的值必须是小值 and后面的是大值 4.in

可以比较多个值

5.like

模糊查询

通配符使用:

1.% 匹配多个

2._ 匹配一个

6.null值操作

is null; 判断为空

is not null; 判断不为空

练习:

1.查询所有计算机类商品信息

2.查询出商品价格大于90的商品信息

3.查询出商品总价大于10000的商品信息

4.查询出价格在100-200之间的商品信息

5.查询出商品价格是65,100或190的商品信息

6.查询出商品的名称中包含java的商品信息。

7.查询出书名是两个字的商品信息

8.查询出商品价格不为null商品信息

order by 排序

在开发中,我们从数据库中查询出的数据经常需要根据某些字段进行排序,可以使用order by关键字,后面跟的就是要排序的列,order by 子句是select的最后的一个子句。 `

asc 升序 (默认)

desc 降序

练习:

1.查询出所有商品,并根据价格进行升序排序

2.查询出所有商品,根据数量进行升序排列,如果数量相同,根据价格进行降序排列

聚集函数

之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个单一的值;另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数:

count:统计指定列不为NULL的记录行数;

sum:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;

max:计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;

min:计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;

avg:计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;

练习:

1.统计商品表中共有多少条记录

2.统计商品表中价格大于50的有多少条记录

3.统计有多少商品

4.统计所有商品的总价值

5.统计所有商品的平均价格

6.统计出记录中pnum的平均值

7.统计出商品表中price最大值

8.统计出商品表中price最小值

9.统计出生活百科类图书的总数量

group by分组

分组查询是指使用group by字句对查询信息进行分组,例如:我们要统计出products表中所有分类商品的总数量,这时就需要使用group by 来对products表中的商品根据category进行分组操作.

分组后我们在对每一组数据进行统计。

分组操作中的having子句是用于在分组后对数据进行过滤的,作用类似于where条件。

注意:

having和where的区别

1.having是在分组后对数据进行过滤. where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数),where后面不可以使用分组函数

练习:

1.对商品分类别统计,求出每一种类商品的总数量

2.对商品分类别统计,求出每一种类商品的总数量,数量要大于100

DQL语句操作总结

综合我们学习的查询相关关键字:select,from,where,group by,having,order by;它们的执行顺序是如下:

lfrom:首先执行from,找到要查询的表;

lwhere:判断条件,筛选符合条件所有记录;

lgroup by:根据之前操作对记录按照指定列进行分组

lhaving:对分组后的信息进行筛选;

lselect:选择所需要的列信息;

lorder by:对查询信息进行排序。

在SQL语言中,第一个被处理的子句是from字句,尽管select字句最先出现,但是几乎总是最后被处理。

原文发布于微信公众号 - Java帮帮(javahelp)

原文发表时间:2017-01-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

mysql索引优化

当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,...

1404
来自专栏西枫里博客

rand()随机的效率问题

在平时开发过程中,数据量不超过1W条的,通常执行随机查询是通过对order进行rand操作的进行的。但是随着数据量的增加,rand严重制约了整站的访问速度。...

551
来自专栏程序猿

系统设计之数据库范式

在设计数据库时,必须遵守一定的规则,在关系数据库中,就是范式。 字数虽少,信息量大。 什么是范式呢?(这个概念...

3916
来自专栏WindCoder

网易MySQL微专业学习笔记(八)-MySQL字符集

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

672
来自专栏恰童鞋骚年

走向面试之数据库基础:一、你必知必会的SQL语句练习-Part 2

本文是在Cat Qi的参考原帖的基础之上经本人一题一题练习后编辑而成,非原创,仅润色而已。另外,本文所列题目的解法并非只有一种,本文只是给出比较普通的一种而已,...

1281
来自专栏WindCoder

网易MySQL微专业学习笔记(五)-SQL语言进阶

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

651
来自专栏MongoDB中文社区

玩转MongoDB: 索引,速度的引领

数据库索引与书籍的索引类似,有了索引就不需要翻整本书,数据库可以直接在索引中查找,在索引中找到条目后,就可以直接跳到目标文档的位置,这可以让查找的速度提高几个数...

1514
来自专栏栗霖积跬步之旅

java多线程编程核心技术——第六章总结

1.0立即加载/“饿汉式”   立即加载:实用类的时候已经将对象创建完毕,常见的实现方法就是直接new实例化。   注:是在调用方法前,就已经实例化了(通常是...

1926
来自专栏架构师小秘圈

高效sql性能优化极简教程

一,sql性能优化基础方法论 对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为: cpu消耗 ...

5475
来自专栏程序员的SOD蜜

同样的SQL语句在查询分析器执行很快,但是网站上执行超时的诡异问题

    同样的SQL语句在查询分析器执行很快,但是网站上执行超时,这个问题以前遇到过,解决办法是重新启动服务器,但过一段时间后(时间长短不一定,一般为一天后),...

2907

扫码关注云+社区

领取腾讯云代金券