MySQL数据库
目录
MySQL数据库
1. 基本概念
- 数据（Data）
- 描述事物的符号记录
- 数字、文字、图形、声音、档案记录等
- 以"记录"形式按统一的格式进行存储
常用的数据类型：
int：整型 无符号[0,2^32-1]，有符号[-2^31,2^31-1]
float：单精度浮点 4字节32位
double：双精度浮点 8字节64位
char：固定长度的字符类型
varchar：可变长度的字符类型
text：文本
image：图片
decimal（5,2）：5个有效长度数字，小数点后面有2位
- 表
- 将不同的记录组织在一起
- 用来存储具体数据
- 数据库
- 表的集合，是存储数据的仓库
- 以一定的组织方式存储的相互有关的数据集合
- 是按照数据结构来组织、存储和管理数据的仓库
2. DBMS
- 数据库管理系统（DBMS）
- 是实现对数据库资源有效组织、管理和存取的系统软件
- 数据库的建立和维护功能、数据定义功能、数据操纵功能、数据库的运行管理功能、通信功能
- 是实现对数据库资源有效组织、管理和存取的系统软件
- 数据库系统
- 是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成
- 用户可以通过DBMS或应用程序操作数据库
应用程序 发送 数据请求 DBMS 低层指令 DB
DB 返回 数据 DBMS 数据处理结果 应用程序
3. 发展史
- 第一代数据库
- 层次模型与网状模型数据库系统
- 第二代数据库
- 关系数据库
- 第三代数据库
- 面向对象的数据库系统
4. 主流数据库介绍
- SQL Server（微软公司产品）
- 面向Windows操作系统
- 简单、易用
- Oracle（甲骨文公司产品）
- 面向所有主流平台
- 安全、完善、操作复杂
- DB2（IBM公司产品）
- 面向所有主流平台
- 大型、安全、完善
- MySQL（甲骨文公司收购）
- 免费、开源、体积小
5. 数据库分类
- 数据库分两大类：
- 关系型数据库
- 非关系型数据库
5.1 关系数据库
- 关系型数据库（英文名SQL）
- 操作命令：SQL语句
- 存储的数据结构：二维表格（行、列）
- 存储的数据：结构化数据
数据库->数据表->二维表格形式的结构化数据 列（字段）：用来描述对象的一个属性
行（记录）：用来描述一个对象的信息
- 典型代表：MySQL、MariaDB、PostgreSQL（pgsql）、Oracle、SQL Server、DB2
- 国产数据库：阿里云：RDB 华为：高斯 阿里：Oceanbase 腾讯：TDBA 人大金仓：达梦 greatSQL
5.2 非关系型数据库
- 非关系型数据库（英文NoSQL：Not Only SQL）
- 存储结构：健值对 文档 索引 时间序列
- 缓存型 Redis Memcached
- 文档型 MongoDB
- 索引型 ElasticSearch
- 时序型 Prometheus、InfluxDB
- 存储结构：健值对 文档 索引 时间序列
6. SQL语句
- SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能
6.1 SQL语句分类:
6.1.1 DDL:数据定义语言，用于管理数据库对象，如库、表、索引等
show databases;
use 库名;
show tables; show tables from 库名;
desc [库名.]表名;
create database 库名;
use 库名;
create table [库名.]表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key (字段)]);
drop table [库名.]表名;
drop database 库名;
修改表结构
改表名 alter table 旧表名 rename 新表名;
增加字段 alter table 表名 add 新字段 数据类型[字段属性]；
修改字段 alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
删除字段 alter table 表名 drop 字段;
6.1.2 DML:数据操纵语言，用于管理表数据
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);
insert into 表名 values (字段1的值, 字段2的值, ...); #要按照表结构的字段顺序设置值
update 表名 set 字段1=值, ... where 条件表达式;
delete from 表名 where 条件表达式;
6.1.3 DQL:数据查询语言，用于根据条件查询表数据
select * from 表名 [where 条件表达式];
select 字段1,字段2,... from 表名 [where 条件表达式];
select * from 表名\G #纵向查看每行记录字段的值
select * from 表名 limit N; #查看表的前N行记录
select * from 表名 limit N,M; #查看表的前N行之后的连续M行记录（不包含第N行）
6.1.4 DCL:数据控制语言，用于管理用户与权限
MySQL 的约束特性：
primary key #主键约束 字段的值不能重复，且不能为null，一个自建的表只能有一个主键
unique key #唯一性约束 字段的值不能重复，能为null，一个表可以有多个唯一键
not null #非空约束 字段的值不能为null
default #默认值约束 字段的值如果没有设置则使用默认值自动填充
auto_increment #自增约束 字段的值如果没有设置默认会从1开始每次自动递增1，要求自增字段必须设置主键
foreign key #外键约束
int(N) zerofill #零填充
克隆表
create table 新表 like 旧表; #克隆表结构
insert into 新表 (select * from 旧表); #克隆表数据：可实现表结构和表数据与旧表都一样
create table 新表 (select * from 旧表); #表数据与旧表一样，表结构与旧表可能不一样
清空表
delete from 表名; #一条一条的删除记录，效率较慢；自增字段仍然会按照清空前的最大记录继续自增
truncate table 表名; #直接重建表，清空表效率更快；自增字段会重新从1开始自增
#临时表
create temporary table 表名 (....); #临时表可以跟普通的表一样增删改查表中的数据，但是show tables是查看不到的，
#临时表只能在当前会话中有效，在其它会话中或者退出当前会话连接，临时都会失效
int(N) zerofill #零填充
7. 查看数据库结构
查看当前服务器中的数据库
SHOW DATABASES; #大小写不区分，分号“;”表示结束
mysql> show databases;
查看数据库中包含的表
USE 数据库名;
SHOW TABLES;
mysql> use mysql;
mysql> show tables;
mysql> show tables from mysql;
查看表的结构（字段）
USE 数据库名;
DESCRIBE [数据库名.]表名;
可缩写成：DESC 表名;
mysql> describe user;
mysql> desc mysql.user;
8. 创建及删除数据库和表
1.创建新的数据库
创建新的数据库
CREATE DATABASE 数据库名;
create database tb;
2.创建新的表
创建新的表
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
create table tb1 (id int,name char(4),age int,sex char(2),hobby varchar(20),primary key(id));
insert into tb1(id,name,age,sex,hobby) values(1,'cyw',20,'男','smoking');
insert into tb1 values(2,'cs',21,'男','CSGO');
select * from tb1;
insert into tb1 (id,name,hobby) values(3,'ct','singing');
insert into tb1 (id,age,name,hobby) values(3,'ct','23','singing');
update tb1 set age=22,sex='男';
insert into tb1(id,name,age,sex,hobby) values(4,'lyj',23,','男','tennis');
use tb;
show tables;
desc tb1; #查看表结构
3.删除指定的数据表
DROP TABLE [数据库名.]表名; #如不用USE进入库中，则需加上数据库名
drop table tb1;
delete from tb1 where id=1;
delete from tb1 where id=1 or id=3;
select * from tb1 where id=1 or id=3;
select * from tb1 limit 3;
select * from tb1 limit 2;
select * from tb1 limit 2,2;
4.删除指定的数据库
删除指定的数据库
DROP DATABASE 数据库名;
drop database tb;
9. 修改表名和表结构
1.修改表名
ALTER TABLE 旧表名 RENAME 新表名;
alter table tb1 rename group1;
show tables;
2.扩展表结构（增加字段）
ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用
alter table group1 add address varchar(50) default '地址不详';
3.修改字段（列）名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
desc group1;
select * from group1;
alter table group1 change name username varchar(10) unique key;
insert into group1(id,age) values(7,24);
4.删除字段
ALTER TABLE 表名 DROP 字段名;
alter table group1 drop address; #删除某一个字段
10. 管理表中的数据记录
向数据表中插入新的数据记录
INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);
查询数据记录
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
修改、更新数据表中的数据记录
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
在数据表中删除指定的数据记录
DELETE FROM 表名 [WHERE 条件表达式];
11. 案例扩展
use kgc;
create table if not exists info (
id int(4) zerofill primary key auto_increment, #指定主键的第二种方式
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));
#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL
12. 数据表高级操作
1．克隆表，将数据表的数据记录生成到新的表中
方法一：
create table test01 like KY08; #通过 LIKE 方法，复制 KY08 表结构生成 test01 表
insert into test01 select * from KY08;
#此方法能保证 新表的表结构、表数据 跟旧表都是一致的
方法二：
CREATE TABLE test02 (SELECT * from KY08);
#此方法创建的新表的表数据和旧表是一样的，但可能会出现新表的表结构和旧表的不一致
show create table test02\G #获取数据表的表结构、索引等信息
SELECT * from test02;
2．清空表，删除表内的所有数据
方法一：
delete from test01;
#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。
方法二：
truncate table test01;
#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。
3．创建临时表
临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。
CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
浙公网安备 33010602011771号