MySQL（一）

MySQL（一）

数据库概述

数据库(DataBase，DB)：指长期保存在计算机的存储设备(硬盘)上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合， 以文件的方式存在于服务器的电脑上

常见的关系型数据库

• MySql：开源免费的中小型的数据库，已经被Oracle收购了，MySql6.x版本也开始收费，后来Sun公司收购了MySql，而Sun公司又被Oracle收购

• Oracle：收费的大型数据库，Oracle公司的产品

• DB2：IBM公司的数据库产品，收费的，主要用于银行系统中

• SQLServer：Microsoft公司收费的中型的数据库

• SyBase：已经淡出历史舞台，提供了一个非常专业的数据建模工具PowerDesigner

• SQLite: 嵌入式的小型数据库，应用在手机端

---

数据库结构

数据库管理程序(DBMS)可以管理多个数据库，一般会对每一个实例创建一个数据库，为保存实例中实体的数据，就会在数据库创建多个表，以保存程序中实体的数据

---

数据库服务的启动与登录

Windows电脑通过命令提示符方式启动

# 启动服务
net start mysql
# 停止服务
net stop mysql

登录MySQL

mysql -u root -p
再输入密码

mysql -uroot -p密码

# 退出MySQL
quit
exit

---

SQL概述

概念

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

语法

• 每条语句以分号结尾(命令行里需要)，如果在图形化客户端如navicat，sqlyog，java代码中则不是必须加的
• SQL在window中不区分大小写

sql的分类

• Data Definition Language(DDL数据定义语言)
• Data Manipulation Language(DML数据操纵语言)
• Data Query Language(DQL 数据查询语言)
• Data Control Language(DCL 数据控制语言)

---

DDL操作数据库

-- 创建数据库
create database 数据库名 [character set 字符集][collate  校对规则];
-- 删除数据库
drop database 数据库名;
-- 查看所有的数据库
show databases;
-- 查看数据库的定义结构
show create database 数据库名;
-- 修改数据库
alter database 数据库名 character set 字符集;
-- 选中数据库
use 数据库名;
-- 查看当前选中(正在使用)的数据库
select database();

[ ]表示可选

字符集(charset)：是一套符号和编码

修改数据库不是修改数据库名，而是修改字符集

修改时是utf8，不是utf-8

---

DDL操作表

创建表

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

列: 字段；行: 记录

• 类型

  

• 约束

  约束	约束关键字
  主键	primary key
  唯一	unique
  非空	not null

  主键约束=非空+唯一

  auto_increment必须是设置了primary key之后才可以使用

  当设置了auto_increment后插入数据可以插入null，自动增长进行填充

---

查看表

-- 查看所有的表
show tables;
-- 查看表的定义结构
desc student;

---

修改表

-- 增加字段（列）
alter table 表名 add 字段 类型 约束;
-- 修改列的类型约束
alter table 表名 modify 列名 新类型 新约束;
-- 修改列的名称，类型，约束
alter table 表名 change 旧列名 新列名 类型 约束;
-- 删除列
alter table 表名 drop 列名;
-- 修改表名
rename table 旧表名 to 新表名;

---

删除表

drop table 表名;

---

DML操作表记录-增删改

插入记录

-- 插入指定的列
insert into 表名(列,列) values(值,值);
-- 插入所有的列
insert into 表名 values(值,值,值...);

注意：

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

---

更新记录

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

如果没有加where则更新整个表

---

删除记录

-- 使用delete删除
delete from 表 [where 条件];
-- 使用truncate删除
truncate table 表;

delete和truncate区别：

• DELETE是删除表中的数据，表结构还在，删除后的数据可以找回，并且delete删除可以一条一条的删除

• TRUNCATE删除是把表直接DROP掉，然后再创建一个同样的新表，删除的数据不能找回，但执行速度比DELETE快

---

DQL操作表记录-查询

单表查询

基本查询

-- 基本查询语法
select [*],[列名,列名],[列名 as 别名,...],[distinct 列名],[列(+,-...)] from 表名 [where 条件];
-- 查询所有的列
select * from 表名;
-- 查询某张表特定列
select 列名,列名,... from 表名;
-- 去重查询
select distinct 列名,列名,... from 表名;
-- 别名查询(as一般省略)
select 字段 as 别名,字段 as 别名,... from 表名;
select 字段 别名,字段 别名,... from 表名;
-- 对表取别名
select 表别名.字段名,... from 表 as 表别名;
-- 运算查询(+,-,*,/等)[null和其他数据进行运算得到是null]
select 字段 (+,-,*,/) 字段 as 别名 from 表名;
select 字段 (+,-,*,/) ifnull(字段,0) as 别名 from 表名;

• 去重针对某列，distinct前面不能先出现列名

• 运算查询字段，字段之间是可以运算的

• 字符串等类型可以做运算查询，但结果没有意义

• 运算查询时null和其他数据进行运算得到是null，若要完整统计数据，可使用ifnull(字段，0)，表示若值不等于null返回值本身，若值等于null则返回0

条件查询

-- 条件查询语法(取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回)
select ... from 表 where 条件;
-- between...and...   区间查询(相当于...<=字段<=...)
select ... from 表 where 字段 between...and...;
-- in(值,值..)
select ... from 表 where 字段 in(值,值..);
-- like模糊查询(一般和_或者%一起使用)
eg: select ... from 表 where 字段 like '张_';    -- 查询姓张的用户，并且名字是两个字的
    select ... from 表 where 字段 like '张%';    -- 查询姓张的用户，名字的字数没有限制
-- and多条件同时满足
select ... from 表 where 条件1 and 条件2 and 条件3;
-- or任意条件满足
select ... from 表 where 条件1 or 条件2 or 条件3;

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

---

排序查询

-- 只按某一个字段进行排序，单列排序
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];
-- 同时对多个字段进行排序(如果第1个字段相等，则按第2个字段排序，依次类推)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

ASC：升序，默认值；DESC：降序

排序可以根据一个字段排，也可以根据多个字段排序，排序只是对查询的结果集排序，并不会影响表中数据的顺序

---

聚合函数

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

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值

聚合函数会忽略空值NULL，对于NULL的记录不会统计，如果统计个数则尽量不要使用有可能为null的列，如果需要把NULL也统计进去的话，可以使用 IFNULL(列名，默认值) 函数来解决这个问题，如果列不为NULL，返回这列的值，如果为NULL，则返回默认值

---

分组查询

SELECT 字段1,字段2... FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];

根据某一列进行分组，将分组字段结果中相同内容作为一组；并且返回每组的第一条数据，有几组返回的记录就有几条

单独分组没有意义，分组的目的一般为了做统计使用，所以经常和聚合函数一起使用

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

• where和having的区别

  子名	作用
  where 子句	对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，先过滤再分组；where后面不可以使用聚合函数
  having字句	having子句的作用是筛选满足条件的组，即在分组之后过滤数据，先分组再过滤；having后面可以使用聚合函数

---

分页查询

select ... from .... limit a,b;

LIMIT a,b;
a：起始行数，也就是从哪里开始查询，从0开始计数，如果省略，默认就是0；a=(当前页码-1)*b ；
b：返回的行数，也就是一页查询的数量[固定的,自定义的]

---