[大数据]java学习记录二十十八：MySql基础一

一、数据库基础

一、解释

数据库（DataBase，DB）：指长期保存在计算机的存储设备(硬盘)上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合. 还是以文件的方式存在服务器的电脑上的。就是数据的仓库, 数据库 用来持久化保存数据的。目前来说如果我们要进行数据存储，有几种方式:

1. 我们可以使用集合等方式将数据保存在内存中，但是数据不能持久化保存，断电/程序退出，数据就清除了
2. 我们还可以将数据保存在普通文件中，可以持久化保存，但是查找，增加，修改，删除数据比较麻烦，效率低

所以我们需要一个既可以持久化保存数据又可以方便操作的地方来存储数据，就是数据库。

二、数据库的分类

• 关系型数据库
• 非关系型数据库

三、常见的关系型数据库

• MySql ：开源免费的数据库，中小型的数据库，已经被Oracle收购了。MySql6.x版本也开始收费。后来Sun公司收购了MySql，而Sun公司又被Oracle收购,一般用5.x版本，因为免费。
• Oracle：收费的大型数据库.Oracle公司的产品.Oracle收购SUN公司，收购MySql.
• DB2：IBM公司的数据库产品,收费的.银行系统中.
• SQLServer：MS公司.收费的中型的数据库.
• SyBase：已经淡出历史舞台.提供了一个非常专业数据建模的工具PowerDesigner.
• SQLite: 嵌入式的小型数据库,应用在手机端.

四、数据库结构

数据库管理程序(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
1.一般情况下,一个系统(软件,项目) 就设计一个数据库;

2.一个数据库里面有多(>=1)张表. 一个实体(java类)对应一张表

3.一张表里面有多条(>=1)记录, 一个对象对应一条记录

二、 数据库的安装

自行搜索
1.数据启动
使用管理员版cmd
net start 数据库名一般是mysql,我的是workbench安装的是mysql80
或者
运行services.msc,找到数据库服务,右键启动即可
或者cmd
mysql -u 用户名 -p #然后再输入密码.
mysql -u用户名 -p密码
进行登录
2.数据库停止
net stop 数据库名一般是mysql,我的是workbench安装的是mysql80
运行services.msc,找到数据库服务,右键停止即可
输入: quit或exit退出数据库命令行界面

三、SQL

一、解释

• SQL：Structure Query Language。（结构化查询语言）,通过sql操作数据库(操作数据库,操作表,操作数据)
• SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准
• 各数据库厂商(MySql,oracle,sql server)都支持ISO的SQL标准。
• 各数据库厂商在标准的基础上做了自己的扩展。 各个数据库自己特定的语法

二、语法格式

• 每条语句以分号结尾(命令行里面需要)，如果在navicat,java代码中不是必须加的。
• SQL在window中不区分大小写，关键字中认为大写和小写是一样的

三、sql分类

• Data Definition Language (DDL数据定义语言) 如：操作数据库，操作表
• Data Manipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改
• Data Query Language(DQL 数据查询语言)，如：对表中的记录查询操作
• Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

1. 用的最多的是 DML和DQL , 对数据的增删改查
2. SQL 结构化查询语言, 用SQL来操作数据库

四、DDL操作数据库

一、创建数据库

create database 数据库名 [character set 字符集][collate  校对规则]     注: []意思是可选的意思

字符集(charset)：是一套符号和编码。安装数据库的时候已经指定字符了，我指定的是utf8，所以默认就是utf-8
在navicat中语句的结束分号加不加都无所谓
较对规则一般不加，可以设置是否区分大小写，utf8_general_ci是不区分大小写，utf_bin是区分大小写
在查询的时候会不区分大小写，比如输入a，会把A和a都查询出来。

二、查看数据库

1.查看所有的数据库

show databases; 

2.查看指定数据库

show create database 数据库名;

三、删除数据库

drop database 数据库名;

四、修改数据库

修改数据库只能修改编码

alter database 数据库名 character set 字符集;

修改web14_1这个数据库的字符集(gbk)
alter database web14_1 character set gbk;

注意：

• 是utf8，不是utf-8
• 不是修改数据库名

五、其他操作

切换数据库, 选定哪一个数据库

use 数据库名;   		//注意: 在创建表之前一定要指定数据库. use 数据库名

查看正在使用的数据库

select database();

五、DDL操作表

操作表的时候要注意是哪个数据库，要先选择数据库再操作

一.创建表

create table 表名(
   字段名 字段类型 [约束],
   字段名 字段类型 [约束],
   ........
   字段名 字段类型 [约束]
);

字段类型

1. 整型 一般使用int 或者bigint**

2. 浮点/双精度型

   • 默认的范围 float或者double
   • 指定范围 float(M,D) eg: float(4,2) 表达的范围: -99.99~99.99

3. 字符串

   • 固定长度 char(n) eg: char(20), 最大能存放20个字符. ‘aaa’, 还是占20个字符的空间
   • 可变长度 varchar(n) eg:varchar(20), 最大能存放20个字符. ‘aaa’, 占3个字符的空间

   一般使用varchar(n) 节省空间; 如果长度(eg:身份证)是固定的话 可以使用char(n) 性能高一点

4. 关于大文件

   • 一般在数据库里面很少存文件的内容, 一般存文件的路径
   • 一般不使用二进制存, 使用varchar(n)存文件的路径

5. 日期

   • DATE 只有日期
   • DATETIME 日期和时间

约束解释

• 即规则,规矩 限制;
• 作用：保证用户插入的数据保存到数据库中是符合规范的

约束种类:

• not null: 非空 ; eg: username varchar(40) not null username这个字段不能为空,必须要有数据

• unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo字段不能出现重复的数据

• primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的

• auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment

• id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.

注意:

1. 先设置了primary key 才能设置auto_increment
2. 只有当设置了auto_increment 才可以插入null 自己维护 否则插入null会报错

id列:

1. 给id设置为int类型, 添加主键约束, 自动增长
2. 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

例子：. 创建一张学生表(含有id字段,姓名字段,性别字段. id为主键自动增长)

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

创建数据库后，要选中数据库按F5刷新，才会把数据库显示出来

二、查看表

1.查看所有的表

show tables;

2.查看表的定义
desc student;

三、修改表

1.增加一列

alter table 表 add 字段 类型 约束；

2.修改列的类型约束

 alter table 表 modify 字段 类型 约束 ；

3.修改列的名称，类型，约束；

alter table 表 change 旧列 新列 类型 约束;

4.删除一列；

 alter table 表名 drop 列名；

5.修改表名 ;

 rename table 旧表名 to 新表名

例子

• 给学生表增加一个grade字段

alter table student add grade varchar(20) not null;

• 给学生表的sex字段改成字符串类型

alter table student modify sex varchar(10);

• 给学生表的grade字段修改成class字段

alter table student change grade class varchar(20);

• 把class字段删除

alter table student drop class;

• 把学生表修改成老师表(了解)

rename table student to teacher

四、删除表

drop table 表名；

把teacher表删除

drop table teacher;

六、DML操作表记录

先创建一个表

create table product(
	pid int primary key auto_increment,  //只有设置了auto_increment id列才可以赋值为null
	pname varchar(40),
	price double,
	num int
);

一、插入记录

方式一：插入指定列, 如果没有把这个列进行列出来, 以null进行自动赋值了
语法

insert into 表(列，列..) values(值，值..)；

只想插入pname, price ,
例子

insert into product(pname, price) values('mac',18000);

注意: 如果没有插入了列设置了非空约束, 会报错的

方式二: 插入所有的列
语法

insert into 表 values(值，值....);//有几列就按顺序填好

例子

insert into product values(null,'苹果电脑',18000.0,10);
insert into product values(null,'华为5G手机',30000,20);
insert into product values(null,'小米手机',1800,30);

要是在cmd中执行sql语法
出现插入中文数据错误，我们电脑是gbk编码，mysq服务器是utf编码

• 关闭服务, net stop MySql
• 在数据库软件的安装目录下面, 修改配置文件 my.ini中客户端的编码为gbk
• 重新打开命令行,开启服务, net start MySql
  mysql客户端设置gbk后cmd会自动把gbk转换为utf8存到mysql服务器中，因为安装的时候我们设置mysql是utf8。

注意点：

• 插入特定的列:没有赋值的列,系统自动赋为null(前提是当前列没有设置not null 约束)
• 列名与列值的类型、个数、顺序要一一对应。
• 值不要超出列定义的长度。
• 插入的日期和字符串，使用引号括起来。

二、修改数据

语法

update 表 set 列 =值, 列 =值 [where 条件]

例子

• 将所有商品的价格修改为5000元
• 将商品名是Mac的价格修改为18000元
• 将商品名是Mac的价格修改为17000,数量修改为5
• 将商品名是方便面的商品的价格在原有基础上增加2元

update product set price = 5000;
UPDATE product set price = 18000 WHERE name = 'Mac';
UPDATE product set price = 17000,num = 5 WHERE name = 'Mac';
UPDATE product set price = price+2 WHERE name = '方便面';

• 如果没有加where 更新整个的
• 一般是加where

三、删除数据

语法

delete from 表 [where 条件]    注意: 删除数据用delete,不用truncate

truncate table 表;

例子

• 删除表中名称为’Mac’的记录
• 删除价格小于5001的商品记录
• 删除表中的所有记录

delete from product where pname = 'Mac';
delete from product where price < 5001;
delete from product;

2. delete 和truncate区别
   • DELETE 删除表中的数据，表结构还在; 删除的记录可以找回,id会继续从上一次的id生成,一条一条删用这个
   • TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表(空)。删除的记录不可以找回,id会从0 开始,删除所有或者大量的数据用这个
3. 一般我们做的删除
   • 物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除
   • 逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state字段 1 启用 0禁用，一般都是用这个较多，而不是真的删除数据

七、DQL操作表记录

一、基础单表查询

1.基本查询语法

select [*][列名 ,列名][列名 as 别名 ...] [distinct 字段]  from 表名 [where 条件] 

2.查询所有表的所有列

select * form 表

*表示显示所有的列

3.查询某张表特定列的记录

select 列名,列名,列名... from 表

写了列名就是字段名,写几个就会显示几列

4.去重查询

SELECT DISTINCT 字段名 FROM 表名;   //要数据一模一样才能去重

要字段的值一样才能去重,写了几个字段,必须这写字段的值都一样,才算是一条重复的数据,
才能去重,

5.别名查询

就是把字段以另一种名字显示出来。

select 列名 as 别名 ,列名  from 表   //列别名  as可以不写
select 别名.* from 表 as 别名      //表别名(多表查询)

例子
查询商品名称和商品价格，商品价格通过别名‘价格’来显示

select pname , price as 价格 from product;

6.运算查询(+,-,*,/等)

把商品名，和商品价格+10查询出来

select pname ,price+10 from product;

注意

• 运算查询字段,字段之间是可以的
• 字符串等类型可以做运算查询，但结果没有意义

7.条件查询

select ... from 表 where 条件 
//取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

8.区间查询

between...and...  //区间查询
in(值,值..)       //指定查询

例子

where price between  1000 and 3000  //相当于 1000<=price<=3000 

-- 查询id为1,3,5,7的
select * from t_product where id = 1
select * from t_product where id = 3
select * from t_product where id = 5
select * from t_product where id = 7

select * from t_product where id in(1,3,5,7)

9.模糊查询

ike 模糊查询 一般和_或者%一起使用

• _ 占一位
• % 占0或者n位

name like '张%'  --查询姓张的用户, 名字的字数没有限制
name like '张_'  --查询姓张的用户 并且名字是两个的字的

10.多条件查询

where 条件1 and 条件2 and 条件3

11.or 任意条件满足

where 条件1 or 条件2 or 条件3

12.例子

• 查询商品价格>3000的商品
• 查询id=1的商品
• 查询id<>1的商品
• 查询价格在3000到6000之间的商品
• 查询id在1，5，7，15范围内的商品
• 查询商品名以iPho开头的商品(iPhone系列)
• 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and…)
• 查询id=1或者价格小于3000的商品

select * from product where price > 3000;
select * from product where pid = 1;
select * from product where pid <> 1;
select * from product where price between 3000 and 6000;
select * from product where id in (1,5,7,15);
select * from product where pname like 'iPho%';
select * from product where price > 3000 and num > 20;
select * from product where pid = 1 or price < 3000;

二、排序查询

1.单列排序

只按某一个字段进行排序

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];  //ASC: 升序，默认值; DESC: 降序

2.组合排序

同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

三、聚合函数

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

语法

SELECT 聚合函数(列名) FROM 表名 [where 条件];

• 求出学生表里面的最高分数
• 求出学生表里面的最低分数
• 求出学生表里面的分数的总和(忽略null值)
• 求出学生表里面的平均分
• 统计学生的总人数 (忽略null)

SELECT MAX(score) FROM student
SELECT MIN(score) FROM student
SELECT SUM(score) FROM student
SELECT AVG(score) FROM student
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。

select avg(ifnulll(score,0)) from student
//将score为unull的值，设置为0 ，这样就不会省略这行数据，能参与计算

四、分组查询

1.分组查询

分组查询是指使用 GROUP BY语句对查询信息进行分组，将分组字段结果中相同内容作为一组，如按性别将学生分成两组，GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处，单独分组只会返回第一条数据。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

语法

SELECT 字段1,字段2... 聚合函数 FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];
//where不能在group之后，只能在分组之前
//分组查询是先分组然后查询，将不同的值为成机组，然后计算，所以带上字段的值，可以明显读出数据。

例子：根据性别分组, 统计每一组学生的总人数

SELECT sex, count(*) FROM student GROUP BY sex

２.分组后筛选 having

例子:根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)
HAVING后面要跟聚合函数

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(*) > 5

注意事项

单独分组 没有意义, 返回每一组的第一条记录

分组的目的一般为了做统计使用, 所以经常和聚合函数一起使用

分组查询如果不查询出分组字段的值,就无法得知结果属于那组

在分组里面, 如果select后面的列没有出现在group by后面 ,展示这个组的这个列的第一个数据

3. where和having的区别

五、分页查询

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数. 经常用来做分页查询。
语法

select ... from .... limit 起始行数,查询的记录条数.

例子：分页查询学生, 每一页查询4条
b=4; a=(当前页码-1)*b;