Day08-mySQL

计算机存储数据的方式
1）内存，优点：速度快
缺点：容积小，断电数据全部清空。
2）硬盘，优点：容积大
缺点：查询速度特别慢，需要一个一个文件夹去遍历
3）数据库（关系型数据库）：
优点：查询数据快，数据关系明确
缺点：电影等文件存在数据库是二进制存放的，所以针对体积大的文件将其存放在硬盘中，然后将其路径存在数据库中。

---

一,数据库概述

---

1.什么是数据库?
- 数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合.
- 文件系统,数据的仓库

2.数据库管理系统
- 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
- 软件，数据库管理系统本质上就是软件。
- 数据库=数据的集合+数据库软件

注意：通常情况下，经常会用数据库来表示他们使用的数据库软件，这经常会引起混淆，确切的说，数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

3.常见的关系型数据库

• MYSQL：（本质上是一个软件）开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费.

• Oracle：收费的大型数据库.Oracle公司的产品.Oracle收购SUN公司，收购MYSQL；按照电脑台数收费。

• DB2：IBM公司的数据库产品,收费的.银行系统中. eclipse就是IBM开发的。安全性非常高的一个数据库。

• SQLServer：微软公司.收费的中型的数据库.

• SyBase：已经淡出历史舞台.提供了一个非常专业数据建模的工具PowerDesigner. PowerDesigner主要用于做数据库设计。

• SQLite: 嵌入式的小型数据库,应用在手机端. 最典型的就是安卓手机里面。

和java相关的数据库: mysql,oracle

二,数据库的安装和卸载(要求：对照着文档会安装)

• 具体参考文档
  1.安装需要注意的地方
• 安装路径不要有空格和中文
  本机安装目录：C:\Program Files (x86)\MySQL
  安装后查看有没有运行服务：计算机-右键管理-服务和应用程序-服务
  校验MySQL：cmd中 ： mysql -u root -p
  然后输入密码。之后可以通过show dababases；来查看内置的四个数据库。里面内置的四个数据库不要删除，不然有可能出现未知的错误
  

2.卸载需要注意的地方
- 去360或者控制面板卸载
- 一定要删除两个文件夹（数据库安装路径，数据存放路径，这两个文件夹在配置文件里面my.ini有写，my.ini的路径在：C:\Program Files (x86)\MySQL\MySQL Server 5.5）

具体步骤
1到控制面板或者360中卸载MySQL
2到安装目录删除MySQL
即删除：C:\Documents and Settings\All Users\Application Data\MySQL 数据库安装目录
C:\ProgramData\MySQL 数据存放目录
3查看注册表：
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services
搜索mysql，找到一律干掉！

数据库中：
一个软件对应一个数据库
一个实体(类)对应一张表

数据库的默认端口号3306
服务器的默认端口号8080

---

三,sql概述（sql语句）

---

sql：structure query language
结构化查询语言。客户端通过sql语句去操作数据库服务器

1.什么是sql?
- SQL：Structure Query Language。（结构化查询语言）,通过sql语法操作数据库 ；功能有结构化和查询：结构化其实就是创建、删除等。
- SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

• 各数据库厂商(mysql,oracle,Sql Server)都支持ISO的SQL标准。 普通话
• 各数据库厂商在标准的基础上做了自己的扩展。 数据库的方言

2.sql的分类
- DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；（和数据的操作没有半毛钱关系，用来操作数据库和数据库表）
​ CREATE、 ALTER、DROP

• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）； （用于操作数据）
  ​ INSERT、 UPDATE、 DELETE

• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别； （和我们没有关系）
  ​ grant

• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。—重点（查询数据库表中的数据，重中之中）
  ​ SELECT

还有一类关键字：show

---

四,对数据库的CRUD（增删改查）

---

补充：修改用户名，不建议修改！！！
* 修改用户名：
* use mysql;
* update user set user=”leevi” where user=”root”;
* flush privileges;

连接数据库服务器的步骤
* 在cmd中输入mysql -u root -p然后回车
* 输入密码然后回车
** 离开服务器
exit；

---

1,创建数据库

---

1.1语法
create database 数据库名 [character set 字符集][collate 校对规则]
(中括号表示可以写可以不写）
字符集(charset)：是一套符号和编码。
校对规则(collation)：是在字符集内用于比较字符的一套规则,

1.2练习
- 创建一个day08_1的数据库（默认字符集)
create database day08_1；

• 创建一个day08_2的数据库,指定字符集为gbk
  create database day08_2 character set gbk；

• 创建一个day08_3的数据库,指定字符集为gbk,校对规则为gbk_chinese_ci
  create database day08_3 character set gbk collate gbk_chinese_ci;

可以在参考手册中找到字符集和参考手册。

---

2.查看数据库

2.1查看所有的数据库
show databases;
分号不能够省略。show只用于查看数据库和查看数据库的表；

2.2查看数据库的定义，查看数据库的定义，主要用于查看数据库的字符集。
show create database 数据库名
主要是用于查看数据库的字符集，无法查看校对规则。

---

3.删除数据库

drop database 数据库名;
drop这个关键字只会出现在删除数据库和删除数据库中的表中，删除数据使用delete

---

4.修改数据库

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

注意：
- 是utf8，不是utf-8
- 不是修改数据库名

---

5.其它操作

• use 数据库名; 切换数据库、
  注意，切换数据库的时候，直接use，没有use database，这点比较特殊。
  use day08_2;

• select database(); 查看正在使用的数据库。当你不知道当前使用的是哪个数据库的时候就用这句话查看。
  select database();

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

---

五,对表的CRUD

1.创建表

1.1语法
create table 表名(
列名 类型 约束，
列名 类型 约束，
列名 类型 约束
);

1.2 数据类型
整型 int：2的32次方

浮点型: float, 可以更精确的指定显示float（m,d）：m指定显示长度，d指定小数位数。
float(5,2)–> 999.99, -999.99 一般不需要这样指定长度。
double，double（m,d）;

BOOLEAN /BOOL：使用0或者1表示真假，可以自行规定。

char（size）：存放固定长度的字符，固定长度字符串
char（10）：表示这列要填放字符类型，最大长度为10个字符，如果存入了‘ab’,会自动在后面补上8个空格‘ab ’。
缺点：不利于内存优化；
优点：速度快；
什么时候用char（）：长度固定的时候用，比如存放手机号。

varchar（size）：表示该列填放字符串类型；可变长度字符串
varchar（10）：最大长度为10，如果存入‘ab’,那就申请两个字符的内存空间
优点：节省内存空间

BLOB 文件: 二进制 数据
注意: 在公司,存文件是把文件放在服务器(电脑)上,将放文件的路径存到数据库.虽然具有良好的保密性，但是体积占用大。

TEXT 存放大文本

• 时间类型
  ​ date: yyyy-MM-dd;
  datetime:日期和时间;年月日时分秒
  timeStamp: 时间戳（一般使用）
  

1.3 约束,
- 即规则,类型集合里面的泛型 List list ;
- 作用：保证用户输入的数据保存到数据库中是正确的

约束种类:
- not null; 非空；存放数据的时候该列不能为空
- unique；唯一约束, 后面的数据不能和前面重复
- primary key；主键约束(非空+唯一); id:作为数据的唯一标识,通常给id int类型设置主键约束,auto_increment null
- auto_increment;自动增长列 ，一定是和主键一起使用的。

id类型
通常给id int类型设置主键约束,auto_increment

1.4练习
- 创建一张学生表(含有id字段,姓名字段,性别字段为int类型. id为主键自动增长)
create table student（
id int primary key auto_increment,
name varchar(10）,
sex int);

2.查看表

2.1查看所有的表
show tables;

2.2查看表的定义结构(查看表的字段)
desc 表名；

3.修改表

修改表的所有语句，除了改表名，都是以 alter table+表名 开头

3.1语法
- 增加一列；
alter table 表名 add 字段 类型 约束；

• 修改列的类型约束；
  只能修改类型和约束，不能够修改字段名
  alter table 表名 modify 字段 类型 约束 ；

• 修改列的名称，类型，约束；
  alter table 表名 change 旧列 新列 类型 约束;
  可以修改列的名称和类型约束，新列名后面必须加类型，有约束也要加上。当然旧列名和新列名可以一样。一样的情况就相当于modify。

• 删除一列；
  alter table 表名 drop 列名；

• 修改表名 ;
  特殊的，唯一一个修改表而不用alter table 表名开头的
  rename table 旧表名 to 新表名；

-修改表的字符集：
修改表的字符集为utf8
alter table user character set utf8;

3.2练习
- 给学生表增加一个grade字段
alter table student add grade int not null；

• 给学生表的sex字段改成字符串类型
  alter table student modify sex varchar(10);

• 给学生表的grade字段修改成class字段
  alter table student change grade class int not null；

• 把class字段删除
  alter table student drop class;

• 把学生表修改成老师表
  rename table student to teacher;

4.删除表

drop table 表名；

六,对表里面数据的CRUD（重点）

准备工作:
​ 创建一张明星表(明星id,明星名字,明星年龄,明星身价,明星类型.备注:明星id是主键,自动增长)
create table star(
id int primary key auto_increment,
name varchar(10) not null,
age int ,
price int,
type int ,
);

修改本机cmd编码：
chcp 65001
也可以改本机的my.ini文件中的编码内容，正常情况下是修改不了的，没有合适的权限。复制到桌面后修改后再黏贴回去。

查询
select * from 表名;

1.插入数据

两种方式：
第一种方式
insert into 表 (列，列..) values(值，值..)；

insert into star（name,age,price,type） values(‘霆锋’,38,1000,1);
这里没有管id哦。也不会报错，顺序按照我们写的标签名的顺序写value值。

第二种方式：
insert into 表 values(值，值….)
有多少列values后面就要写多少个值，哪怕你要为空你也得写null。
要求，值的顺序跟表中字段的顺序是一致的，而且每个都要写，没有值也要写null；

注意：
- 没有赋值的列,系统自动赋为null
- 列名与列值的类型、个数、顺序要一一对应。
- 值不要超出列定义的长度。
- 如果插入空值，请使用null
- 插入的日期和字符串，使用引号括起来。

命令行插入中文数据报错:
- 关闭mysql服务
- 修改配置文件 my.ini中客户端的编码为gbk
- 重新开启服务

2.更新记录

2.1语法
update 表名 set 字段 = 值 ,字段 = 值 [where 条件]
条件用来控制行。

2.2练习
- 将所有明星的性别改成女性
update star set sex=’female’;

• 将宝宝的性别改成男性
  update star set sex=’male’ where name=’宝宝’;

• 将蓉蓉的价格改成-250
  update star set price=-200 where name=’蓉蓉’;

• 将房祖名的价格在原价格的基础上增加2000
  update star set price=price+2000 where name=’小白’;

3.删除记录(表还在)

3.1语法
delete from 表名 [where条件];
如果没加条件相当于将表中的数据全部删除。基本上不要用！小心删除了所有条件。

truncate table 表名;

速度更快，直接删除表，然后再建立一张一样的新的空表。

区别：
- DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
- TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

删除数据后，再插入数据，如果不指定id，那么会默认在已经有的基础上的id在增加。

3.2练习

• 删除表中名称为’蓉蓉’的记录
  delete from star where name=’蓉蓉’;

• 删除表中的所有记录
  delete from star;
  truncate table star;

4.查询记录（重点中的重点）

4.1语法

select [列名,列名] [*] [聚合函数][distinct 字段] from 表名 [WHERE –> group by –>having–> order by]

4.2练习

4.2.1 查询所有的列的记录
select * from 表名;
*代表所有列、所有字段。后面没加条件，就可以查出所有的东西。
select后，from前的东西是用来控制查询出来的数据的列。而where条件是用于控制查询出来的行。
- 查询明星表的所有记录

4.2.2查询某张表特定列的记录，多个字段。
select 字段名1,字段名2 from 表名;
select name,price from star;
- 查询明星表的姓名和年龄字段

4.2.3 去重查询 (distinct 字段)
select distinct 字段名 from 表名;
​ 去除重复记录（两行或两行以上记录中系列的上的数据都相同）
- 把年龄重复的给筛选
注意:
要查询的字段的数据一模一样才能去重
查询多列数据的时候，要每一列都相同才能够去重。

4.2.4 别名查询 (字段 as 别名, as可以省略) 只是为了展示得更容易看懂一点，其实没啥作用
select 字段名 as 别名 from 表名
select name as ‘姓名’ ,age ‘年龄’，type ‘ 类型’ from star;
- 查询明星名称和价格，明星价格通过别名‘身价’来显示

4.2.5运算查询(+,-,*,/等)
select name，age+10 from star
数据库表中的信息并没有改变。
这种方法是针对数据类型而言的。
如果该字段是字符串就是没意义的。
- 把明星名称，和明星年龄+10查询出来

注意：
- 运算查询字段字段之间是可以的
- 字符串时间这些做运算查询，结果是没有意义的

4.2.6 条件查询
select * from star where price>3000；

条件查询常见条件：

注意:

1.between…and…: 包含临界值 eg: between 3000 and 6000相当于: 3000<=price<=6000

2.模糊查询,like和通配符一起使用:
主要是针对字符串？
​ 占位符
% ;多位（0~n）
​ _ ;一位

1.  in   一个集合。
2. and    or
3. not非

• 查询价格>3000的明星
  select name from star where price>3000;

• 查询价格在3000到6000之间的明星
  select name from star where price between 3000 and 6000;

• 查询id在1，5，10，15范围内的明星
  select name from star where id in(1,5,10,15);

• 查询明星名称以’柯’开头的明星
  select name from star where name like(‘羽%’);

• 查询价格大于3000并且年龄大于30的明星的信息
  select name from star where price>3000 and age>30;

4.2.7排序; order by 列名
asc升序(默认) desc降序
排序语句放在查询语句的最后面！

• 查找所有明星的记录,按照价格升序
  select * from star order by price asc；

• 查找所有明星的记录,按照价格升序,如果价格一样,按照年龄降序
  select * from star order by price asc,age desc;

应用场景:

1.qq,微信附近的人. 距离

2.京东,淘宝,价格,

4.2.8聚合函数
聚合函数对一组值执行计算并返回单一的值。除了 COUNT 以外，聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
（聚合函数的前面不能有字段名（列名）,如果一定要有，那么该字段名只能是用来分组的字段名）
聚合函数是用来做纵向运算的函数
select count（*） from star；
聚合函数不能够写在where条件中。

select [列名,列名] [*] [聚合函数][distinct 字段] from 表名 [WHERE –> group by –>having–> order by]

​ count(|字段)；统计指定列不为NULL的记录行数, 最好以不能够为空的为准，因此count里面最好写id，也可以直接写
select count（*） from star；

​ 如果要统计表中的总记录数，那么count( *|不能为空的字段名)

​ sum();计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0

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

​ min()；计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算

​ avg();计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

• 统计明星表的总记录数
  select count(id) from star;

• 求明星价格的总和
  select sum(price) from star;

• 查询明星的最大价格
  select max(price) from star;

• 查询明星的平均价格
  select avg(price) from star;

4.2.9分组 (group by 列)
分组查询select后的第一个字段名一定是用于分组的字段名。
分组查询，单独使用group by,只显示各个组的第一条数据，因此单独使用group by是没有意义的
- group by结合group_concat()使用，可以展示每一组的数据集合，group_concat()中不能写*

• select type,group_concat(name) from star group by type;
  分组查询select后面一定要跟上分组的字段名

• group by结合聚合函数使用(聚合函数前写得字段名必须是用于分组的字段名)（最常见的）

  • 统计每一组的数据个数
    select type,count(*) from product group by type;

  • 统计每一组的数据的总价格
    select type,sum(price) from product group by type;

  • 统计每一组的数据中价格最小的那个
    select type,min(price) from product group by type;

• 根据明星类别分组，统计不同明星类别的个数
  select type,count(*) from star group by type;

• 根据明星类别分组，统计不同明星类别的总价格
  select type,sum(price) from star group by type;

4.2.10 分组后筛选(having)

• 根据明星类别分组，统计不同明星类别的个数,并且该明星类别数量大于2的
  select type,count() from star group by type having count()>2;

-统计每类的明星个数，根据type分组，并且明星的年龄>30
select type,count(*) from star group by type having age>30;
?????

注意：
- having是分组后筛选

select [列名,列名] [*] [聚合函数][distinct 字段] from 表名 [WHERE –> group by –>having–> order by]
从上述公式也可以看出，where是group by 之前执行，写在前面，而having 是在分组后执行，写在后面。

where和having的区别
- 1.having是在分组后对数据进行过滤.where是在分组前对数据进行过滤
- 2.having后面可以使用聚合函数(统计函数)where后面不可以使用聚合函数
- 3.having后面加的条件一定要与分组有关。
- 4.WHERE是分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

顺序: select…from…where…group by…having…order by

练习：
统计每个学生的总分。
select *，math+chinese+english from student ;
这样会显示所有的资料，外加一列总分

统计数学成绩大于或等于90的学生有多少个？
select count(math) from student where math>90;
错误写法： select count(math>=90) from student;
where表示分组前进行筛选；

统计总分大于250的人数有多少？
select count(id) from exam where chinese+math+english>250;
错误写法：select count(math+chinese+english>250)as ‘总分’ from student;

查看建表语句：
show create table student；

求一个班级总分平均分
因为班级里面有一个人的分为null
select avg(ifnull(chinese,0))+avg(ifnull(math,0))+avg(ifnull(english,0)) from student;
错误写法：select avg(math+english+chinese) from student;

ifnull：
IFNULL(expr1,expr2)
假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

求班级最高分和最低分（数值范围在统计中特别有用）
数据中有null；
select max(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;
select min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;
错误写法：
select max(math+english+chinese) from student;
select min(math+english+chinese) from student;

查询购买了几类商品，并且每类总价大于100的商品
select product,price from orders group by product having sum(price)>100;