MySql01：介绍|数据类型|表库操作|CRUD|约束

数据库

一：数据库介绍

1.1：为什么使用数据库

存储数据用文件就可以了，为什么还要弄个数据库?

文件保存数据有以下几个缺点：

1. 文件的安全性问题
2. 文件不利于数据查询和管理
3. 文件不利于存储海量数据
4. 文件在程序中控制不方便

数据库存储介质：

1. 磁盘
2. 内存

为了解决上述问题，专家们设计出更加利于管理数据的软件——数据库，它能更有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。

1.2：数据库分类

关系型数据库 和 非关系型数据库

关系型数据库（RDBMS）：

是指采用了关系模型来组织数据的数据库。 简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。基于标准的SQL，只是内部一些实现有区别。常用的关系型数据库如：

1. Oracle：甲骨文产品，适合大型项目，适用于做复杂的业务逻辑，如ERP、OA等企业信息系统。收费。

2. MySQL：属于甲骨文，不适合做复杂的业务。开源免费。

3. SQL Server：微软的产品，安装部署在windows server上，适用于中大型项目。收费。

非关系型数据库：

（了解）不规定基于SQL实现。现在更多是指NoSQL数据库，如：

1. 基于键值对（Key-Value）：如 memcached、redis

2. 基于文档型：如 mongodb

3. 基于列族：如 hbase

4. 基于图型：如 neo4j

	关系型数据库	非关系型数据库
使用SQL	是	一般不基于SQL
实务支持	支持	不支持
复杂操作	支持	不支持
海量读写	效率低	效率高
基本结构	基于表和列，结构固定	灵活性比较高
使用场景	业务方面的OLTP系统	用于数据的缓存、或基于统计分析的OLAP系统

注：OLTP（On-Line Transaction Processing）是指联机事务处理，OLAP（On-Line AnalyticalProcessing）是指联机分析处理。

1.3：SQL分类

1. DDL数据定义语言，用来维护存储数据的结构，代表指令: create, drop, alter
2. DML数据操纵语言，用来对数据进行操作，代表指令： insert，delete，update
3. DML中又单独分了一个DQL，数据查询语言，代表指令： select
4. DCL数据控制语言，主要负责权限管理和事务，代表指令： grant，revoke，commit

二：数据库操作

进入MySql服务：

C:\Windows\system32>mysql -u root -p
Enter password: ****

2.1：显示数据库

show databases;

2.2：创建数据库

 create database if not exists 数据库名称 character set utf8mb4;

说明：MySQL的utf8编码不是真正的utf8，没有包含某些复杂的中文字符。MySQL真正的utf8是使用utf8mb4utf8mb4

2.3：使用数据库

use 数据库名;

2.4：删除数据库

drop database if exists 数据库名;

三：数据类型

3.1：数值类型

整形/浮点型

数据类型	大小	说明	对应Java类型
bit(n)	n指定位数，默认为1位	二进制数字，n的范围是1-64，数值范围为0-2^(m-1)	常用Boolean对应BIT，此时默认是1位，即只能存0和1
tinyint	1字节		Byte
smallint	2字节		Short
int	4字节		Integer
bigint	8字节		Long
float(M,D)	4字节	单精度，M为指定长度，D为指定小数位数。会发生精度丢失。	Float
double(M,D)	8字节		Double
decimal(M,D)	M/D最大值+2	双精度，M为指定长度，D为指定小数位数。精确数值。	BigDecimal
numeric(M,D)	M/D最大值+2	双精度，M为指定长度，D为指定小数位数。精确数值。	BigDecimal

3.2：字符类型

数据类型	大小	说明	对应Java类型
varchar(size)	0-65535字节	可变长度字符串	String
text	0-65535字节	长文本数据	String
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据	Stirng
BLOB	0-65,535字节	二进制形式的长文本数据	byte[]

3.3：日期类型

数据类型	大小	说明	对应Java类型
datetime	8字节	1000-9999年，不进行时区的检索和转换	Java.util.Date/java.sql.Timestamp
Timestamp	4字节	1970-2038，自动检索当前时区并转换	Java.util.Date/java.sql.Timestamp

四：表的操作

4.1：显示数据表

show tables;

4.2：查看表结构

desc 表名;

4.3：创建表

CREATE TABLE 表名 (
 field1 datatype comment '字段说明',
 field2 datatype,
 field3 datatype,
 `如果关键字和字段名字相同时` datatype 
) comment '表的说明';

4.4：删除表

drop table  if exists 表名 ;

五：CRUD

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

5.1：插入

全列插入，指定列插入，单行数据，多行数据

--全列插入,values插入的顺序和建表的顺序相同
insert into 表名 values (字段值,字段值...);
--指定列插入 多行数据
insert into 表名 (字段1,字段2) values
	(字段值,字段值)
	(字段值,字段值);

--datetime 插入 当作字符串，但有指定格式 '2001-03-28 时分秒可省略'

5.2：查询

全列查询：

– 通常情况下不建议使用 * 进行全列查询

– 1. 查询的列越多，意味着需要传输的数据量越大。

– 2. 可能会影响到索引的使用。

select * from 表名;

指定列查询：

select 	字段1,字段2 form 表名 ;

查询字段为表达式：

select id , concat(name,':',password) as '姓名：密码' , age*2 from stu_test;
select year(birthday) as '年',month(birthday) as '月',day(birthday) as '日'  from  stu_test;

concat：字符串拼接

year,month,day ：获取日期的年月日

别名：

select 字段名 as '别名';
select 字段名 '别名';

select 表别名.字段名 '字段别名' form 表名 '表别名'
select 学生.id  '学号' from stu_test as 学生;

去重：

使用DISTINCT关键字对某列数据进行去重。

select distinct * from 表名

排序：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] 
 ORDER BY column [ASC|DESC], [...];
 
 select distinct age from stu_test order by age asc/desc;

null为最小的数据，表达式/别名排序，多字段排序：

-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;

条件查询：

运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，Null不安全，null=null返回null
<=>	等于，null安全，null<=>null返回true
!=,<>	不等于
Between x and y	[x,y]
in(…)	是(…)任意一个
is null	是null
is not null	不是null
like	%任意个字符 _一个字符 模糊查询
AND	多条件为true才返回true
OR	任意一个为true返回true
NOT	相反

1. WHERE条件可以使用表达式，但不能使用别名。

2. AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

分页查询：

limit:

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

5.3：修改

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] 

-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english ;
-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

update不支持limit

5.4：删除

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

-- 删除整张表数据
DELETE FROM for_delete;
--删除部分数据
delete from user order by age desc limit 3;

六：约束

6.1：约束类型

1. NOT NULL - 指示某列不能存储 NULL 值。
2. UNIQUE - 保证某列的每行必须有唯一的值。
3. DEFAULT - 规定没有给列赋值时的默认值。
4. PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
5. FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
6. CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

6.2：Not Null

创建表时，可以指定某列不为空。

--not null  非空
create table 表名 (
 id int not null comment 'id不为空'
);

6.3：Unique

指定列为唯一的，不可以重复

--unique 不重复
create table 表名(
 id int unique comment 'id不重复'
);

6.4：Default

默认值

--default 默认
create table 表名(
 id int primary key comment '主键',
 name varchar(20) default 'user'
);

6.5：Primary Key

--primary key 主键
create table if not exists 表名(
 id int primary key auto_increment comment'自增长主键',
 name varchar(20) default 'user'
);

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。

id INT PRIMARY KEY auto_increment,

6.6：Foreign Key

外键用于关联其它表的主键或者唯一键。

foreign key (字段名) references 表(列)

--foreign key 外键
create table if not exists class (
 c_id int primary key comment'班级主键' auto_increment,
 c_name varchar(20) not null default 'classXXXX'
);

create table if not exists student(
 s_id int primary key comment '学生主键' auto_increment,
 s_class int not null,
 foreign key (s_class) references class (c_id)
);

6.7：Check (了解)

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);