MySql学习(一)…(⊙＿⊙；)…

1.数据库的介绍

数据存储的方法分为内存，文件和数据库

1.1什么是数据库

1. 存储数据的仓库
2. 本质上是一个文件系统，还是以文件的方式存在服务器的电脑上的。
3. 所有的关系型数据库都可以使用通用的 SQL 语句进行管理 DBMS DataBase Management System

常见数据库

MySql 数据库
MySQL 是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的 root 账号，使用安装时设置的密码即可登录。

mysql -u 用户名 -p 密码;

退出MySql

quit 或 exit

1.2 数据库的组成

⚫ 数据库管理系统：数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据 库 的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和 完整性，用户通过数据库管理系统访问数据库中表内的数据。
⚫ 所谓数据库服务器：是指在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开 发人员会针对每一个应用创建一个数据库。
⚫ 表：为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

2.数据库设计

2.1 建模过程
⚫ 需求分析阶段：分析客户的业务和数据处理需求 与该系统有关人员进行交流、座谈， 充分了解用户需求，理解数据库需要完成的任务
⚫ 概要设计阶段：设计数据库的 E-R 模型图，确认需求信息的正确和完整
✓ 标识实体 （Entity）
◼ 标识数据库要管理的关键对象或实体，实体一般是名词
✓ 标识每个实体的属性（Attribute）
✓ 标识实体之间的关系（Relationship）
⚫ 详细设计阶段：应用三大范式审核数据库结构
⚫ 代码编写阶段：物理实现数据库，编码实现应用

2.2 范式
仅有好的 RDBMS 并不足以避免数据冗余，必须在数据库的设计中创建好的表结构，Dr E.F.codd 最初定义了规 范化的三个级别，范式是具有最小冗余的表结构。 这些范式是：
• 第一范式(1st NF －First Normal Form) 第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小 数据单元（也称为最小的原子单元）， 则满足第一范式（1NF）。

• 第二范式(2nd NF－Second Normal Form) 如果一个关系满足 1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式（2NF） ，第二范
式要求每个表只描述一件事情。

• 第三范式(3rd NF－ Third Normal Form) 如果一个关系满足 2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF） 练习 假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：
⚫ 公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等
⚫ 公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等
⚫ 公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率 与工程 师不同） 绘制 ER 图，及设计表结构

2.3 Sql语句
1.什么是 SQL？ Structured Query Language：结构化查询语言 其实就是定义了操作所有关系型数据库的规则。每一种数据库操 作的方式存在不一样的地方，称为“方言”。
2. SQL 通用语法 1. SQL 语句可以单行或多行书写，以分号结尾。 2. 可使用空格和缩进来增强语句的可读性。 3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。 4. 3 种注释 * 单行注释: – 注释内容 或 # 注释内容(mysql 特有) * 多行注释: /* 注释 */
3. SQL 分类

1. DDL(Data Definition Language)数据定义语言 用来定义数据库对象：数据库， 表，列等。关键字：create, drop,alter 等
2. DML(Data Manipulation Language)数据操作语言 用来对数据库中表的数据 进行增删改。关键字：insert, delete, update 等
3. DQL(Data Query Language)数据查询语言 用来查询数据库中表 的记录(数据)。关键字：select, where 等
4. DCL(Data Control Language)数据控制语言(了解) 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

3.管理数据库

3.1 查看所有数据库

mysql> show databases; 

3.2 创建数据库
1）创建数据库，并指定默认字符集，指定默认的校验规则

mysql>create datebase 数据库名 default character set utf8 collate utf8_general_ci;

2）查看数据库的状态（默认字符串）

mysql>show create database 数据库名;

3.3 删除数据库

mysql>drop database 数据库名;

3.4 修改数据库
修改数据库默认字符集

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

3.5 使用数据库

mysql>use 数据库名;

显示正在使用的数据库

mysql>select DATABASE();

3.6 数据约束
用户操作表的数据进行约束，包括默认值，非空，唯一，主键，自增长，外键
⚫ 默认值：用户对使用默认值的字段不插入值的时候，就使用默认值

create table 表名(
	列名 数据类型 default 默认值
);

⚫ 非空： 限制字段必须赋值

create table 表名(
	列名 数据类型 not null
);

⚫ 唯一：对字段的值不能重复

create table 表名(
	列名 数据类型 unique
);

⚫ 主键：非空+唯 一 注意：
1）通常情况下，每值表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
2）建议不要选择表的包含业务含义的字段作为主键，建议给每值表独立设计一个非业务含义的 id 字段。

create table 表名(
	列名 数据类型 primary key
);

⚫ 自增长：自动递增
自增长，从0开始
zerofill 零填充

create table 表名(
	列名 int(4) zerofill primary key auto_increment
);

⚫ 外键：约束两种表的数据

-- 主表
create table 主表(
	列名1 数据类型 primary key,
	列名2 数据类型 varchar(20)
);

-- 副表
create table 副表(
	列名1 数据类型 primary key,
	列名2 数据类型 primary key,
	constraint 外键名字 foreign key(列名【外键】) references 主表(主键)
);

注意：
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！
2）主表的参考字段通用为主键！
3）添加数据： 先添加主表，再添加副表
4）修改数据： 先修改副表，再修改主表
5）删除数据： 先删除副表，再删除主表

⚫ 级联操作
问题： 当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表，但是，我们 希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现。
级联修改： on update cascade
级联删除： on delete cascade

4.管理表

4.1 查询所有表
查看数据库中的所有表

mysql>show tables;

4.2 创建表

mysql>create table 表名(
	字段名称1 字段类型 primary key,
	字段名称2 字段类型
);

mysql 主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、 MultiLineString、Polygon、GeometryCollection 等

4.3 查看表结构

mysql>desc 表;

4.4删除表

mysql>drop table 表名;

4.5 修改表
1）添加字段

mysql>alter table 表名 add column 字段名 类型;
mysql>alter table 表名 add 字段名1 类型,add 字段名2 类型;

2. 修改字段类型

mysql>alter table 表名 modify column 列名 类型;

3. 修改字段名称

mysql>alter table 表名 change column 列名 新列名 数据类型;

4)删除字段

mysql>alter table 表名 drop column 列名;

5)修改表名

mysql>alter table 表名 rename to 新表名;

5.数据操作

1）查看表的数据

mysql>select * from 表名;

2. 插入数据
   插入所有列（所有列的值都要按顺序依次插入）

mysql>insert into 表名 values(值1,值2);

3. 插入部分列（列的数据和值的顺序和数量一定保持一致）

mysql>insert into 表名(列名1,列名2,列名3)values(值1,值2,值3);

4. 修改数据

mysql>update 表名 set 列名=值,列名1=值1,列名2=值2;

5)删除数据

mysql>delete from 表名;

注意：
只能删除表中的数据，不能影响表中的自增长约束
使用 delete 删除的数据，通过事务可以回滚。
后面带条件

6)删除所有数据

mysql>truncate table 表名;

注意：
1）既能删除表的数据，也能够把表的子增长约束置为 0
2）使用 truncate 删除的数据，不能回滚！
3）后面不能带条件

6 查询数据

6.1 查询所有列

select * from 表名;

6.2 查询指定列

select 列1,列2 from 表名;

6.3 查询时指定别名

select 列名 as '别名',列名 as '别名' from 表名;
-- as 可以省略
select 列名  '别名',列名  '别名' from 表名;

6.4 查询去除重复数据

select distinct 列名 from 表名;

6.5 条件查询

1）逻辑条件：and 且		or 或
select * from 表名 where 列名1=值1 and 列名2=值2;
select * from 表名 where 列名1=值1 or 列名2=值2;
2)比较条件：>	<	>=	<=	=	between...and
select * from 表名 where 别名>值;
select * from 表名 where 别名<值;
select * from 表名 where 别名<值 or 别名=值;
select * from 表名 where 别名>=值 and 别名<=值;
select * from 表名 where 别名 between 值1 and 值2;	-- 包前包后
3)判空条件：=" is null <>" is not null
update 表名 set 列名 ="" where 列名=值;
update 表名 set 列名=null where 列名=值;
-- null  和 空字符串的区别： 
-- null： 没有数据。 判断 null： is null,判断不为 null： is not null
--  空字符： 有数据，数据就是空字符串。判断空字符： =''； 判断不为空字符串： <>'' 
select * from 表名 where 列 is null;
select * from 表名 where 列='';
4)模糊条件：like
-- like 后面跟上符合 
-- %:  表示任意多个字符（0-多个） 
-- _:  表示一个字符
select * from 表名 where 列名 like '值%';
select * from 表名 where 列名 like '%值%';
select * from 表名 where 列名 like '值_';

6.6 聚合查询

select sum(列) from 表名;
select avg(列) from 表名;
select max(列) from 表名;
select min(列) from 表名;
select count(*) from 表名;
select count(列) from 表名; -- 效率比count(*)稍高
-- 注意：聚合函数，会排出null值的数据

6.7 分组查询

select count(*) from 表名 group by 列名;

6.8 分组筛选

select count(*) from 表名 group by 列名 having count(*)>值;

6.9 多表查询

内连接查询：只有满足条件的结果才会显示
select 列名1,列名2
	from 表1,表2
	where 表1.列 = 表2.列	-- 表与表之间的连接条件
-- 内连接的另一种语法
select 列名1,列名2
	from 表1
	inner join 表2
	on 表1.列 = 表2.列
	
左连接查询：使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
-- （注意：左连接：左表数据一定会完全显示！）
select 列名1,列名2
	from 表1
	left join 表2
	on 表1.列 = 表2.列
右连接查询：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
-- （注意：右连接：右表数据一定会完全显示！）
select 列名1,列名2
	from 表1
	right join 表2
	on 表1.列 = 表2.列