WEB：Mysql - 03

基本概念

数据库的概念

数据库，简而言之就是存储数据的仓库，可以按照一定的数据结构存储管理大量的数据及数据与数据之间的关系，它本质上是一种信息管理系统。数据库根据存储采用的数据结构的不同可以分为许多种，其中常见的有层次式数据库、网络式数据库、关系型数据库。其中关系型数据库占据着市场的主流。 

关系型数据库

关系型数据库是建立在关系模型基础上的数据库。这种定义听起来十分抽象，这里我们不深入讨论什么叫做“关系模型”--大学计算机专业专门有一门课叫“离散数学”专门讨论过关系模型 --，只是简单的表述为 利用表来存储数据，用表和表之间的关系保存数据之间的关系的数据库称为关系型数据库，这个定义不太严谨，但是更好理解。

常见的关系型数据库

商业数据库：

Oracle -- 甲骨文公司开发，市场占有率第一

SQLServer -- 微软公司开发，.Net技术中常用

DB2 -- IBM公司开发

Sybase -- Sybase公司开发

开源数据库：

MySql -- 瑞典MySql AB研发，非常受欢迎，已经被Oracle收购

SQLite -- 小型轻量级开源关系型数据库，常用作嵌入式设备数据库

MySql数据库的安装配置

MySQL安装配置

安装配置步骤见附加文档

​​​​​​​MySQL安装目录

MySQL的数据存储目录为data，data目录通常在\MySQL Server 5.1\data位置。在data下的每个目录都代表一个数据库。

MySQL的安装目录下：

bin目录中都是可执行文件；

my.ini文件是MySQL的配置文件；

基本命令

启动和关闭mysql服务器

启动：net start mysql；

关闭：net stop mysql；

在启动mysql服务后，打开windows任务管理器，会有一个名为mysqld.exe的进程运行，所以mysqld.exe才是MySQL服务器程序。

客户端登录退出mysql

在启动MySQL服务器后，我们需要使用管理员用户登录MySQL服务器，然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序：mysql.exe

登录：mysql -u root -p 123 -h localhost；

-u：后面的root是用户名，这里使用的是超级管理员root；

-p：后面的123是密码，这是在安装MySQL时就已经指定的密码；

-h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p 123；

退出：quit或exit；

在登录成功后，打开windows任务管理器，会有一个名为mysql.exe的进程运行，所以mysql.exe是客户端程序。

MySql常见概念

数据库服务器

指的就是一个数据库管理程序，这个程序可以管理多个数据库。

​​​​​​​数据库

一个数据库服务器可以管理多个数据库，可以认为是一个数据的“仓库”，一般一个web应用对应一个数据库。

​​​​​​​表

在保存应用实体数据时，通常会在数据库中创建多个表，以保存应用程序实体数据。一般来说应用程序中的一个javabean类可以对应一张数据库表。 

表记录

表中以一行记录保存一条信息，这样的一条记录称为表记录。可以认为一个javabean对象对应一条表记录。

SQL语言

基本概念

SQL

SQL--Structured Query Language, 结构化查询语言,是关系型数据库通用的操作语言。

是一种非过程性语言。

由美国国家标准局(ANSI）与国际标准化组织（ISO）制定SQL标准。各大数据库厂商都对其做了实现。所以我们只要学会了SQL语言，就可以操作各大关系型数据库了。

为加强SQL的语言能力，各厂商增强了过程性语言的特征,增加了一些非标准的SQL，这样的SQL称为该数据库的“方言”。

SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能

​​​​​​​操作数据库

​​​​​​​创建数据库

语法：

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

create_specification:

[DEFAULT] CHARACTER SET charset_name  |  [DEFAULT] COLLATE collation_name

其中charset_name是为数据库指定的默认字符集

Collate是为数据库指定的默认校对规则

校对规则是在字符集内用于比较字符的一套规则,可以控制select查询时where条件大小写是否敏感的规则。

练习：

~创建一个名称为mydb1的数据库。

    CREATE DATABASE mydb1;

~创建一个使用gbk字符集的mydb2数据库。

CREATE DATABASE mydb2 CHARACTER SET gbk;

~创建一个使用utf8字符集，并带校对规则的mydb3数据库。

CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_bin;

​​​​​​​查看数据库

语法：

显示所有数据库：SHOW DATABASES

显示数据库创建语句： SHOW CREATE DATABASE db_name

练习：

~查看当前数据库服务器中的所有数据库

SHOW DATABASES;

~查看前面创建的mydb2数据库的定义信息

SHOW CREATE DATABASE mydb2;

​​​​​​​修改数据库

语法：

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

alter_specification:    [DEFAULT] CHARACTER SET charset_name  | [DEFAULT] COLLATE collation_name

练习：

~查看服务器中的数据库，并把mydb2库的字符集修改为utf8

ALTER DATABASE mydb2 CHARACTER SET utf8;

​​​​​​​删除数据库

语法：

DROP DATABASE  [IF EXISTS]  db_name

练习：

~删除前面创建的mydb1数据库

DROP DATABASE mydb1;

​​​​​​​选择数据库

语法：

选择数据库:use db_name;

查询当前选择的数据: select database();

没有退出数据库的命令,如果想退出当前数据库进入另一个数据,直接use 另一个数据库 就可以了 

​​​​​​​操作表

​​​​​​​SQL中常见数据类型

1)字符串型

VARCHAR：

变长的字符串，需要在声明字段时指定能存储的最大字符数，真实占用的空间取决于存入的字符数，存入的越多占用空间越多。适合保存内容长度不定的字符类型数据。能包含数据的大小，mysql5.0以前0~255字节，mysql5.0以后0~65535字节

CHAR:

定长字符串，需要在声明字段时指定固定字符数。即使存入的字符数少于该长度，该字段也会占用该固定长度。适合存储长度不变的字符类型数据。能包含数据的大小，0~255字节

2)大数据类型

BLOB：

大二进制类型，可以存入二进制类型的数据，通过这个字段，可以将图片、音频、视频等数据以二进制的形式存入数据库。最大为4GB。

TEXT：

大文本，被声明为这种类型的字段，可以保存大量的字符数据，最大为4GB。

注意：text属于mysql的方言，在其他数据库中为clob类型

3)数值型

TINYINT:占用1个字节，相对于java中的byte

SMALLINT:占用2个字节，相对于java中的short

INT：占用4个字节，相对于java中的int

BIGINT：占用8个字节，相对于java中的long

FLOAT：4字节单精度浮点类型，相对于java中的float

DOUBLE：8字节双精度浮点类型，相对于java中的double

4)逻辑型

BIT：

位类型，可以存储指定位的值，可以指定位的个数，如果不指定则默认值为1位，即只能保存0或1，对应到java中可以是boolean型。

5)日期型

DATE：日期 

TIME：时间 

DATETIME：日期时间 

TIMESTAMP：时间戳 

**DATETIME和TIMESTAMP的区别？

* DATETIME和TIMESTAMP显示的结果是一样的，都是固定的"YYYY-MM-DD HH:MM:SS"的格式

* DATETIME支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。TIMESTAMP的显示范围是'1970-01-01 00:00:00'到2037年，且其实际的存储值为1970年到当前时间的毫秒数。

* 在建表时，列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不行。

* 在更新表时，可以设置TIMESTAMP类型的列自动更新时间为当前时间。

​​​​​​​字段约束

1)定义主键约束

通常每张表都会有一个字段或多个字段联合起来唯一标识表记录，这样的字段称为这张表的主键。我们可以为这样的字段增加主键约束。主键约束要求字段必须不能为空且值必须唯一。

增加主键约束：PRIMARY KEY

可以在定义主键时指定主键的自动增长auto_increment，这样主键就不需要人为的维护，在新增记录时不需要指定主键的值，数据库会自动维护一个计数器，每次计数器的值+1后作为主键，保证主键的唯一性。

2)定义唯一约束

如果需要指定某个字段的值不能重复，可以为该字段指定唯一约束

增加唯一约束：unique

3)定义非空约束

如果需要指定某个字段的值不能为空，可以为该字段指定非空约束

增加非空约束：not null

4)外键约束

见3.6

​​​​​​​新增表

语法：

CREATE TABLE table_name( field1  datatype, field2  datatype, field3  datatype)[character set 字符集] [collate 校对规则]field：指定列名　datatype：指定列类型

注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

创建表时，一般不需要指定字符集编码和校对规则，和数据库保持一致即可。

练习

~创建employee表

字段	属性
id	整型
name	字符型
gender	字符型
birthday	日期型
entry_date	日期型
job	字符型
salary	小数型
resume	大文本型

create table employee(

id int primary key auto_increment,

name varchar(20) unique,

gender bit not null,

birthday date,

entry_date date,

job varchar(100),

salary double,

resume text

);

​​​​​​​查看表

语法：

查看表结构：

desc tabName

查看当前所有表：

show tables

查看当前数据库表建表语句

show create table tabName;

​​​​​​​修改表

语法：

增加列：

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

修改列：

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

删除列：

ALTER TABLE tabname DROP (column);

修改表名：

ALTER TABLE old_tabname RENAME TO new_tabname;

或

RENAME TABLE old_tabname TO new_tabname;

修改列名称：

ALTER TABLE tabname CHANGE [column] old_col_name new_col_name datatype

修改列的顺序：

ALTER TABLE tabname MODIFY col_name1 datatype AFTER col_name2;

修改表的字符集：

ALTER TABLE tabname CHARACTER SET character_name;

练习：

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

alter table employee add image blob;

~修改job列，使其长度为60。

alter table employee modify job varchar(60);

~删除gender列。

alter table employee drop gender;

~表名改为user。

alter table employee rename to user;

    或

    rename table employee to user;

~列名name修改为username

alter table user change name username varchar(20);

~将image插入到gender列的后面

    alter table user modify image blob after gender;

~修改表的字符集为utf8

alter table user character set utf8;

​​​​​​​删除表

语法：

DROP TABLE tabname;

练习：

~删除user表

drop table user;

​​​​​​​操作表记录

​​​​​​​INSERT

语法：

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

注意：

插入的数据应与字段的数据类型相同

数据的大小应在列的规定范围内

在values中列出的数据位置必须与被加入的列的排列位置相对应

字符串和日期格式的数据要用单引号引起来

如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表

练习：

~向员工表中插入三条数据

insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)

values

(null,'张飞',1,'1990-09-09','2000-01-01','打手',998.0,'真的很能打...');

insert into employee

values

(null,'关羽',0,'1990-08-08','1990-08-09','财神',9999999.0,'公司挣钱就指着他了...');

insert into employee

values

(null,'刘备',1,'1990-06-06','1990-09-09','ceo',10.0,'公司的 ceo,基本没啥事干..'),

(null,'赵云',1,'2010-06-01','2010-06-01','保镖',400.0,'老板的 贴身护卫...');

​​​​​​​mysql乱码解决：

查看当前数据库中字符集配置：

show variables like'character%';

其中：

client是客户端使用的字符集。

connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。

database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。    

results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。       

server是服务器安装时指定的默认字符集设定。       

system是数据库系统使用的字符集设定。（utf-8不可修改）

set names xxx; 命令是客户端通知服务器和当前客户端交互时使用什么编码，但是这种方式每次新开客户端都需要通知服务器 很麻烦    

我们可以修改mysql/my.ini,将default-character-set=gbk,这是修改服务器默认认为的客户端的字符集编码

这样一来,大部分人都是gbk的客户端,不需要设置任何东西,连进来就没乱码.如果有个别的人客户端不是gbk,也可以通过set names xxx;的方式声明自己的编码集,也没有乱码.

​​​​​​​UPDATE

语法：

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

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

练习：

~将所有员工薪水修改为5000元。

update employee set salary=5000;

~将姓名为’张飞’的员工薪水修改为3000元。

update employee set salary=3000 where name='张飞';

~将姓名为’关羽’的员工薪水修改为4000元,job改为”耍大刀”。

update employee set salary=4000,job='耍大刀' where name='关羽';

~将刘备的薪水在原有基础上增加1000元。

update employee set salary=salary+1000 where name='刘备';

​​​​​​​DELETE

语法：

delete from tab_name [WHERE where_definition]

where用来筛选要删除的记录，如果不使用where子句，将删除表中所有数据。

delete语句不能删除某一列的值（可使用update）

delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

TRUNCATE TABLE tab_name语句也可以删除表中数据，它和delete有所不同。delete是一条条删除记录，truncate是摧毁整表再重建相同结构的表，truncate效率更高。

练习：

~删除表中名称为’张飞’的记录。

delete from employee where name='张飞';

~删除表中所有记录。

delete from employee;

~使用truncate删除表中记录。

truncate table employee;

​​​​​​​SELECT

(1)基本的查询

语法：

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

select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

from指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据

练习：

create table exam(

id int primary key auto_increment,

name varchar(20) not null,

chinese double,

math double,

english double

);

insert into exam values(null,'关羽',85,76,70);

insert into exam values(null,'张飞',70,75,70);

insert into exam values(null,'赵云',90,65,95);

insert into exam values(null,'张三丰',82,79,null);

~查询表中所有学生的信息。

select * from exam;

~查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

~过滤表中重复数据。

select distinct english from exam;

~在所有学生分数上加10分特长分显示。

select name,english+10 ,chinese+10 ,math+10 from exam;

~统计每个学生的总分。

select name,english+math+chinese from exam;

~使用别名表示学生总分。

select name as 姓名,english+math+chinese as 总分 from exam;

select name 姓名,english+math+chinese 总分 from exam;

select name english from exam;//~!这是错的

(2)使用where子句的查询

语法：

Select *|列名 from tablename [WHERE where_definition]

其中Where子句中支持： 

Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;

练习：

~查询姓名为关羽的学生成绩

select * from exam where name='关羽';

~查询英语成绩大于90分的同学

select * from exam where english > 90;

~查询总分大于230分的所有同学

select name 姓名,english+math+chinese 总分 from exam where english+math+chinese>230;

~查询语文分数在 80－100之间的同学。

select name,chinese from exam where chinese between 80 and 100;

~查询数学分数为75,76,77的同学。再查询分数不在这个范围内的同学

    select name,math from exam where math in (75,76,77);

    select name,math from exam where math not in (75,76,77);

~查询所有姓张的学生成绩。

select * from exam where name like '张%';

select * from exam where name like '张_';

select * from exam where name like '张__';

~查询数学分>70，语文分>80的同学。

select * from exam where math>70 and chinese>80;

(3)排序查询

语法：

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

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。

Asc 升序、Desc 降序

ORDER BY 子句应位于SELECT语句的结尾。

练习：

~对英语成绩排序后输出。

select name ,english from exam order by english desc;

~对总分排序按从高到低的顺序输出

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

~对姓张的学生成绩排序输出

select name 姓名, ifnull(english,0)+ifnull(math,0)+ifnull(chinese,0) 总分 from exam where name like '张%' order by 总分 desc;

1. 聚合函数

SQL提供的聚合函数有计数函数COUNT()、求和函数SUM()、平均值函数AVG()、最大值函数MAX()、最小值函数MIN()等。如下表：

函数名称	函数功能
COUNT()	返回选取结果集中行的数目
SUM()	返回选取结果集中所有值的总和
AVG()	返回选取结果集中所有值的平均值
MAX()	返回选取结果集中所有值的最大值
MIN()	返回选取结果集中所有值的最小值

语法：

求符合条件的记录中指定列的记录数

    select count(列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的和值

    Select sum(列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的平均值

    Select avg (列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的最大值

    Select max(列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的最小值

    Select min(列名)… from tablename [WHERE where_definition]

练习：

~统计一个班级共有多少学生？

select count(*) from exam;

~统计数学成绩大于75的学生有多少个？

select count(*) from exam where math>75;

~统计总分大于230的人数有多少？

select count(name) from exam

where ifnull(english,0)+ifnull(math,0)+ifnull(chinese,0) >230;

~统计一个班级数学总成绩？

select sum(math) from exam;

~统计一个班级语文、英语、数学各科的总成绩

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

~统计一个班级语文、英语、数学的成绩总和

select sum(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

~统计一个班级语文成绩平均分

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

~求一个班级数学平均分？

select avg(math) from exam;

~求一个班级总分平均分？

select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

~求班级最高分和最低分

select max(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

select min(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

(5)分组查询

语法：

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

练习：

create table orders(

id int,

product varchar(20),

price float

);

insert into orders(id,product,price) values(1,'电视',900);

insert into orders(id,product,price) values(2,'洗衣机',100);

insert into orders(id,product,price) values(3,'洗衣粉',90);

insert into orders(id,product,price) values(4,'桔子',9);

insert into orders(id,product,price) values(5,'洗衣粉',90);

~对订单表中商品归类后，显示每一类商品的总价

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

~查询购买了几类商品，并且每类总价大于100的商品

select product ,sum(price)

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

~查询单价小于100而总价大于100的商品的名称.

    select product

    from orders

    where price < 100 group by product having sum(price)>100;

**where子句和having子句都可以进行过滤器,但是使用场景有所不同:

* where子句对分组之前的数据进行过滤,不能使用聚合函数

* having子句对分组之后的数据进行过滤,可以使用聚合函数

* 使用where子句的地方都可以用having替代，但是使用having的地方一般不能用 where替