15.数据库简介+MySQl使用+SQL语句

数据库简述

一.数据库简介DB

1.定义:

存储在计算机内的数据容器

指长期存储在计算机内的，有组织，可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储，具有较小的冗余，较高的数据独立性和易扩展性，并可为各种用户共享

2.DBMS数据库管理系统

纵和管理数据库的大型软件,用于建立、使用和维护数据库.

MySQL、Access、SQLite、SQL SERVER、Oracl…

3.数据库分类

1. 关系型数据库Relational Database

   Oracle, MySQL,SQLite用于手机的数据库

2. 非关系型数据库

   2.文档型数据库Document Database

   常见的有前端开发和爬虫的最爱MongoDB、Couchbase、Firebase、CouchDB

   3.键值数据库Key-value Database

   键值数据库常见的有Redis、Memcached

   4.搜索引擎Search Engine

   比如大厂必备的Elasticsearch、日志数据库Splunk

   *5.宽列数据库Wide Column

   6.图形数据库Graph

   常见的有Neo4j

   7.时序数据库Time Series

   常见的有InfluxDB

二.MySQL的安装

1.安装步骤

https://blog.csdn.net/weixin_44051608/article/details/85163823

2.MySQL数据库图形管理工具

1．SQLyog 2．phpMyAdmin 3．Navicat 4．MySQL GUI Tools

3.mysql程序常用命令

• 选定数据库：use dbname;
• 显示所有数据库：show databases;
• 显示使用的数据库中所有表：show tables;
• 显示表结构：desc tb_name;
• 放弃正在输入的命令：\c
• 显示命令清单：\h
• 退出mysql程序：\q
• 查看MySQL服务器状态信息：\s

4.MySQL字符集及字符序

show character set;

即可查看当前MySQL服务实例支持的字符集、字符集默认的字符序以及字符集占用的最大字节长度等信息

• latin1支持西欧字符、希腊字符等
• gbk支持中文简体字符
• big5支持中文繁体字符
• utf8几乎支持世界所有国家的字符。

5.Navicat快捷键操作

• Ctrl+Q: 弹出一个sql输入窗口
• Ctrl+Shift+R: 只运行选中的sql语句
• Ctrl+/ : 注释
• 鼠标三击选择当前行

三.MySQL数据库基本操作

SQL语言操作,不区分大小写,一句语句结束加;

-- 创建数据库, 如果本数据库存在则创建失败
create database db_name;
-- 使用数据库
use db_name;
-- 查看创建数据库信息
show create database db_name;
-- 删除数据库
drop database db_name;

…

表管理

一.SQL概述

SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL不区分大小写

1.SQL语法组成

![外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来

1)DDL

数据库定义语言(针对数据库结构的)

• create
• drop
• alter修改表
• show
• use

2)DML

数据库操作语言(针对表数据)

• select…from…查
• insert…into…
• update…set…更新
• delete…from…删

3)DCL

数据库控制语言

2.数据库的表

二维数组的集合(行+列).类似的表结构:

id	emp_name	age
1	数据1	23
2	数据2	…
3	…	…

3.数据类型

1)数值类型

• int | integer 整型

• bigint 8byte 大整型

• TINYINT

• DECIMAL(M,D) 浮点型

  M控制数值总位数,D控制浮点数

  decimal(6,2)两位小数共6位的数

2)字符串类型

• now() 当前时间
• DATETIME、DATE、TIMESTAMP、TIME和YEAR

3)日期和时间列类型

• char 和 varchar

  char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

  //二者区别:

  char(1)固定一个字符;

  varchar(10)数据字符长度不固定10以内就行

二.创建表

1.创建表结构

//创建表时;可以不加
//一行一个,分割,最后一行不需要
CREATE TABLE  employee(id bigint,emp_name VARCHAR(20) NOT NULL,address VARCHAR(60) NOT NULL,birthday DATE);

2.表, 字段命名规范(了解:通配的)

• 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)+下划线组成，命名简洁明确，多个单词用下划线’_'分隔
• 全部小写命名，禁止出现大写
• 禁止使用数据库关键字，如：name，time ，datetime，password等
• 表名称不应该取得太长（一般不超过三个英文单词）
• 表的名称一般使用名词或者动宾短语
• 用单数形式表示名称，例如，使用 employee，而不是 employees
• 明细表的名称为：主表的名称+字符dtl（detail缩写）
• 例如：采购定单的名称为：po_order，则采购定单的明细表为：po_orderdtl
• 表必须填写描述信息（使用SQL语句建表时

字段名命名示例:

• 使用下划线进行分隔，提升可读性。

  例如，emp_name，user_id，is_friend
  

• 使用完整名称，提高可读性。

  例如，uid修改为user_id，pid修改为person_id。
  

  • 表被索引列必须定义为not null，并设置default值。
  • 禁止使用float、double类型，建议使用decimal或者int替代。
  • 禁止使用blob、text类型保留大文本、文件、图片，建议使用其他方式存储，MySQL只保存指针信息。
  • 禁止使用varchar类型作为主键。

3.操作表table的语句

-- 创建表CREATE
create table employee(col1,col2......
);
-- 删除表DROP
drop table tb_name;
-- 显示表结构,常用desc
desc | describe tb_name;
-- 显示表所有信息
show create table tb_name;
-- ......................................................................
-- 修改table,下面举例说明:
alter table tb_name 
add | drop | change | modify col_name....;
-- 1.增加字段列ADD
alter table employee add age int;
alter table employee add gender char(1) after age;
alter table employee add gender char(1) first;
-- 2.修改字段列数据类型MODIFY
alter table employee modify [colmun] age bigint;
-- 修改列名CHANGE, 既可以改字段名也可改数据类型
alter table employee change [colmun] age new_age int;
-- 修改表名RENAME
alter table employee rename new_employee;
-- 3.删除列DROP
alter table employee drop gender;

详细参数可看：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

4.注释

• – 行注释

  desc empoyee;     -- This comment continues to the end of line
  

• desc empoyee;     # This comment continues to the end of line
  

• /* */

  mysql> desc  /* this is an in-line comment */ + empoyee;
  mysql> SELECT empoyee;
  /*
  this is a
  multiple-line comment
  */
  

…

数据操作

1.插入数据&更改数据&删除数据

#举例说明
create table dept(
dept_id int(0) primary key not null comment '编号',
dept_name varchar(32) comment '部门名称',
remark varchar(255) default('备注') comment '备注'
);
-- 1.插入数据insert... into....
INSERT INTO dept VALUES(1,'人事部','人事管理');#插入一行数据
INSERT INTO dept VALUES(2,'人事部','人事管理'),(3,'后勤部','日常管理');#插入多行row数据
INSERT INTO dept(dept_name,remark) value('财务部','管理财务');#根据字段插入数据
INSERT INTO dept VALUES|value(1,'人事部',default);
-- 2.更改数据update ......set...
UPDATE dept SET dept_name='策划部',remark='方法策划' WHERE dept_id=2;#加入where条件限制否则更改的时全部row数据,指定id=2的列
-- 3.删除数据delete ....from....where条件语句;
DELETE FROM dept WHERE dept_name='后勤部';
-- 清空表truncate [table] 表名
/*
# 建议保存数据接结构和内容,sql文件,下次直接运行sql文件恢复数据库;
truncate语句与“delete from 表名”语句区别: 
清空记录的表如果是父表，那么truncate命令将永远执行失败。如果使用truncate table成功清空表记录，那么会重新设置自增型字段的计数器。truncate table语句不支持事务的回滚，并且不会触发触发器程序的运行。
*/

…

数据完整性(重点)

一.数据完整性分类

1.实体完整性–主键

primary key(col_name,[col1_name])主键: 保证所有行有唯一标识符,可能是一列或多列组合(复合主键),主键这一列不能有重复值;

2.域完整性not null

not null是指一个列的输入有效性，是否允许为空值。强制域完整性的方法有：限制类型、格式或可能值的范围。

3.参照完整性foreign key

是指保证主关键字和外部关键字之间的参照关系。它涉及两个或两个以上表数据的一致性维护。

外键值将引用表中包含此外键的记录和被引用表中主键与外键相匹配的记录关联起来。在输入、更改或删除记录时，参照完整性保持表之间已定义的关系，确保键值在所有表中一致。

二.完整性约束设置

• PRIMARY KEY 主码约束(主键)

  一个表中只能有有一个主键约束:

  • 主键列值必须非空
  • 一个表中只能有一个主键
  • 单个列作为主键时，列的值必须唯一
  • 多个列作为主键时，允许某个列的值存在重复，但作为主键的列的组合的值必须唯一
  • 最少性，尽量选择单个键作为主键
  • 稳定性，尽量选择数值更新少的列作为主键

  CREATE TABLE employee(emp_id int(11) primary key;emp_name varchar(10)#primary key(emp_id,emp_name)复合主键
  )
  

• UNIQUE KEY 唯一性约束

  一个表中可以有多个unique约束

  CREATE TABLE employee(emp_id int(11) primary key;emp_name varchar(10),remark varchar(20),unique key(emp_name ),unique key( remark )}
  

• NOT NULL 非空值约束

  某数据单元的值为空表示该单元是以空值 (NULL) 作为其数据。NULL 不等于零或空白字符串：它表示没有输入任何内容，或提供了一个显式 NULL 值，通常表示该值未知或不适用 如果不允许该列的值为空，其定义的关键字为NOT NULL

• AUTO_INCREMENT自增-- 用于整数列默认自增1

  #一般设置主键
  -- 插入自增数据
  INSERT INTO person(id,username,age,info) VALUES (0[null],'feicy',33,'student')#0 | null替代自增数据,自增值也可以自己设置,不写就默认随后自增
  

• UNSIGNED 无符号整数

• DEFAULT default_value 默认值约束

  列设置默认值, 当列不提供值时,会默认赋值

  CREATE TABLE EMPLOYEE
  (id        CHAR(8) PRIMARY KEY,dep_id        CHAR(3),emp_name      VARCHAR(20) NOT NULL,address   VARCHAR(100) DEFAULT '地址不详',
  ……
  )
  insert into employee1 VALUES('s1001',10,'tom',DEFAULT);
  

• CHECK 约束

  check (age>=18)

• FOREIGN KEY 外键约束

  -- 单独一行放后面
  -- employ
  FOREIGN KEY(emp_id) references dept(dept_id);#dept是主表
  

  主表和从表

  主表是被引用的表，外表是引用其他表的表

  1、当主表中没有对应的记录时，不能将记录添加到子表:

  成绩表中不能出现在学员信息表中不存在的学号；

  2、不能更改主表中的值而导致子表中的记录孤立:

  把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；

  3、子表存在与主表对应的记录，不能从主表中删除该行:

  不能把有成绩的学员删除了

  4、删除主表前，先删子表:

  先删学员成绩表、后删除学员信息表

• DEFAULT CURRENT_TIMESTAMP 创建新记录时默认保存当前时间（仅适用timestamp数据列）

• ON UPDATE CURRENT_TIMESTAMP 修改记录时默认保存当前时间（仅适用timestamp数据列）

• CHARACTER SET name 指定字符集（仅适用字符串）

三.更改表约束(重点)

1.外键约束设置

• 创建表时，添加外键

  create table 表名(...外键列,constraint 外键名称自定义 foreign key (连接外键的列名称) references 主表(主表列)
  );
  

• 表创建完成后添加外键

  -- 添加和删除主键约束CONSTRAINT (约束)
  ALTER TABLE employee ADD CONSTRAINT fk_emp FOREIGN KEY(dept_id) REFERENCES dept(dept_id);#fk_emp外键名自定义
  ALTER TABLE employee ADD  FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
  ALTER TABLE employee DROP FOREIGN KEY fk_emp;
  

1. 添加外键
   ALTER TABLE 表名 ADD CONSTRAINT 外键名称自定义 FOREIGN KEY (连接外键的列名称) REFERENCES 主表(主表列);

   alter table employee add constraint emp_fk foreign key(emp_id) references dept(dept_id);
   

2. 删除外键

   ALTER TABLE employee DROP FOREIGN KEY emp_fk;
   

2.主键设置约束

#表创建完设置主键约束
-- 添加主键
ALTER TABLE employee ADD constraint pk PRIMARY KEY(emp_id);
-- 删除主键 
ALTER TABLE employee DROP PRIMARY KEY;

3. 删除被其他关联的父表

1. 先删除子表外键约束>>再删除父表;
   D CONSTRAINT 外键名称自定义 FOREIGN KEY (连接外键的列名称) REFERENCES 主表(主表列);

alter table employee add constraint emp_fk foreign key(emp_id) references dept(dept_id);

2. 删除外键

   ALTER TABLE employee DROP FOREIGN KEY emp_fk;
   

2.主键设置约束

#表创建完设置主键约束
-- 添加主键
ALTER TABLE employee ADD constraint pk PRIMARY KEY(emp_id);
-- 删除主键 
ALTER TABLE employee DROP PRIMARY KEY;

3. 删除被其他关联的父表

1. 先删除子表外键约束>>再删除父表;
2. 未删除外键情况下: 先删除从表>>再删除主表