数据库操作，数据类型表操作。

╰半夏微凉° 2024-03-27 13:01 90阅读 0赞

## **一、数据库操作** ##

> 数据库操作 在关系数据库中，sql语句或程序中
> 
> 1. 注释 - -为单行注释 /\* 多行注释 \*/
> 
> 2. 不区分大小写，但有习惯性将默认的关键字为大写
> 
> 3. 每个语句结尾建议有分号
> 
> 4. mysql中使用反引号为兼容性操作

**1. 查看库 show SHOW DATABASES \[LIKE '数据库名'\];**

-- 查看所有数据库
    show databases;
    -- 查看数据库名称为db开头
    show databases like 'db%';
    /*
    在数据库列表中
    information_schema
    mysql
    有一个表名称为 user 管理员相关的信息
    performance_schema
    sys
    都是mysql系统的数据库，一般不要操作
    */
    -- 查看当前登录用户转为使用的库
    select database(),user(),now(),version();
    -- 查看建立数据库的语句代码
    show create database d1;
    -- CREATE DATABASE `d1` /*!40100 DEFAULT
    CHARACTER SET utf8 */ /*!80016 DEFAULT
    ENCRYPTION='N' */
    -- CREATE DATABASE d1 DEFAULT CHARACTER SET utf8;
    show create database mysql;
    -- CREATE DATABASE `mysql` 
    /*!40100 DEFAULT
    CHARACTER SET utf8mb4 COLLATE
    utf8mb4_0900_ai_ci */ 
    
    /*!80016 DEFAULT
    ENCRYPTION='N' */

**2. 建立数据库**

-- 建立数据库
    create database d1;
    create database d2 default character set
    utf8;
    create database if not exists `d3` charset
    utf8;
    create database db default CHARACTER SET
    gbk;
    show create database db;
    -- 修改数据库文件的字符集
    alter database db character set utf8;

**3. 删除数据库 drop**

-- 删除数据库
    drop database d1;
    drop database if exists `d2`;
    drop database d3;

**4.查看字符集信息**

show charset like 'gb%';
    show charset like 'utf%';
    show VARIABLES like 'char%';
    show VARIABLES like '%dir%';

## **二、数据类型表操作** ##

### 1. 数据类型 ###

#### 1.1 numeric数字类型`` ####

> **整数： tinyint smallint mediumint int bigint** 
> 
> **小数：float double decimal(p,s) numeric(p,s)**
> 
> **DOUBLE PRECISION\[(M,D)\] \[UNSIGNED\]** 
> 
> **\[ZEROFILL\], REAL\[(M,D)\]** 
> 
> **无符号 ，填充0\[UNSIGNED\] \[ZEROFILL\]**

![fe1f082a85a044e593e4dc9676f53c53.png][]

-- 建立表(字段列使用数据类型)
    create table student(
    id int unsigned auto_increment,
    name varchar(30),
    /* age bigint , 不能这样写，bigint 是占用8字节
    */
    age tinyint, /* 整数只能存储整数 18*/
    money decimal(10,2),/* 99999999.99 最大数字
    如果没有数字则是 0.00 */
    primary key(id)
    );
    -- 插入数据
    insert into student
    values(null,
    'jack'
    ,18,200);
    -- 查询数据
    select * from student;

#### 1.2 string 字符串 ####

> **char(n) varchar(n) tinytext text mediumtext longtext** 
> 
> **char(0-255)** 
> 
> **varchar(0-21844) // 63533**

![a68a72ccf83748f5ab93b2ada57e5a10.png][]

**定长字符串 char()**

**变长字符串 varchar(n) tinytext text mediumtext longtext(4GB字符串)**

-- char varchar tinytext text mediumtext
    longtext
    -- 32767-1 21845-1 16383-1
    -- gbk varchar(32766) utf8 varchar(21844)
    utf8mb4 varchar(16383)
    select 65535/2,65535/3,65535/4;
    -- 演示
    create table t2(
    c1 char(5),/*定长字符串，功能是，如果设置5，
    则插入数据不到被空格也要占用5字节*/
    c2 varchar(5),/*变长字符串，如果设置5，最多
    插5个字符，如果插入2个字，则占用2个字符的字节数*/
    c3 tinytext,
    c4 mediumtext,
    c5 text,
    c6 longtext
    );
    insert into t2
    value('a'
    ,
    'b'
    ,
    'c'
    ,
    'd'
    ,
    'e'
    ,
    'f');
    select * from t2;
    -- 建立表
    create table user(
    sno char(5),/*定长*/
    name varchar(3)/*变长字符串 3个字符串不是3个
    字节*/
    );
    insert into user value('20211'
    ,
    '李四六');
    insert into user value('1'
    ,
    'jac');
    select name,char_length(name),length(name)
    from user;
    select sno,char_length(sno),length(sno)
    from user;
    -- 数据类型 字符型（串）
    show tables;
    select
    user(),database(),now(),3*3,rand(),version(
    ),sysdate();
    select CURRENT_DATE(),curdate();
    -- 删除指定几个表
    drop table if exists t1,t2,t3,t4,t5,t6;
    /* 多行注释 */
    -- 单行注释
    -- 字符串类型 的值需要添加单引号，数字类型值一般不加
    单引号
    -- char 定长字符串 varchar(30) '' 变长字符串
    -- uuid char(36) varchar(36)
    -- string char(n) n(max=255) varchar(n)
    TINYTEXT text MEDIUMTEXT LONGTEXT(4GB)
    create table t1(
    s1 char(6), /* 6个字符 */
    s2 char(200),
    s3 char(255),
    s4 varchar(10000),
    s5 varchar(11383)
    );
    drop table if exists t1;
    create table t2(
    s1 char(6), /* 6个字符 最多是255 */
    s2 char(3) /*a */
    );
    insert t2 value('javaab'
    ,
    '李si');
    select * from t2;
    select
    CHARACTER_LENGTH(s1),length(s1),CHARACTER_L
    ENGTH(s2),length(s2) from t2;
    -- 114688
    create table t3(
    c char(5)
    );
    insert t3 value('d2a');
    select * from t3;

#### 1.3 date、time、datetime日期相关类型 ####

> date yyyy-MM-dd 3字节 time hh:mm:ss 3字节
> 
> datetime yyyy-MM-dd hh:mm:ss 8字节 用来存储日期和时间。格式为"yyyy-mm-dd hh:mm:ss"，在mysq中占8个字节 存储范围： 1000-01-01 00:00:00 -- 9999-12-31 23:59:59
> 
> timestamp 时间戳 yyyy-MM-dd hh:mm:ss 4字节 用来存储时间戳。格式为“yyyymmddhhmmss”，显 示为"yyyy-mm-dd hh:mm:ss"。在msql中占4个字节 select CHARACTER\_LENGTH(s1),length(s1),CHARACTER\_L ENGTH(s2),length(s2) from t2; -- 114688 create table t3( c char(5) ); insert t3 value('d2a'); select \* from t3; 存储范围为: 1970-01-01 00:00:01 UTC -- 2038-01- 19 03:14:07
> 
> **注意：它存储的是由格林尼治时间1970年1月1日到 当前时间的秒数，但是显示为"yyyy-mm-dd hh:mm:ss"。**
> 
> year用来存储年份数据。格式为“yyyy”，在mysql中 占1个字节 存储范围为：1901 -- 2155

#### ![b045736f2467484db24a72f2dff95550.png][] ####

#### 1.4 enum set 复合类型 ####

> enum 枚举 set集合

-- null 男 女 a
    gender enum('男','女') default '男'
    age enum(10,20,30,40) default 20
    create table user(
    id int unsigned auto_increment,
    name varchar(30),
    gender enum('男','女','保密') default'男'
    , /*gender字段数据只能填写 男 女 保密 其中一
    个*/
    depart set('高等数学','英语','线性代数','离散数学'),
    /* 可以不填写，填写必须是集合中的元素，可
    是任意个 */
    primary key(id)
    )engine=innodb default charset=utf8;
    -- 插入正常
    insert into user value(null,'李四','女','英语,英语,线性代数');
    -- 插入数据错误
    insert into user value(null,'李丰','保密','英语,英语,线性代数,大学语文');
    select * from user;

#### 1.5 bit 布尔 ####

> bit 位类型，只能存储1 或 0 其实mysql底层使用的 是　tinyint unsigned

#### 1.6 json ####

> json是一种特殊字符串，轻量级的数据格式，不同程序 不同语言数据交换格式。 JSON：JavaScript Object Notation, JS 对象简谱)是一种 轻量级的数据交换格式。它基于ECMAScript (欧洲计算 机协会制定的js规范)的一个子集，采用完全独立于编程 语言的文本格式来存储和表示数据。简洁和清晰的层次 结构使得 JSON 成为理想的数据交换语言。 易于人阅读 和编写，同时也易于机器解析和生成，并有效地提升网 络传输效率。
> 
> mysql 从5.7开始增加了json新的数据类型，此类型非常 灵活。
> 
> **tinyint int bigint double(p,s) numeric(p,s) char(64) varchar(255) text longtext date datetime enum set bit json（mysql 5.7） 二进制类型（字节类型）用于存储文件**

#### 1.7 binary 二进制 ####

> binary(255)
> 
> varbinary()
> 
> blob 65k
> 
> TinyBlob 最大 255 相当于 binary(255) b
> 
> lob 最大 65K MediumBlob 最大 16M
> 
> LongBlob 最大 4G

### 2.表操作 ###

> 数据类型：**bit tinyint int bigint float double decimal(8,1) datetime date enum set char varchar json blob**

**1. 建立表 create table**

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]
    tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
    use d3;
    select
    version(),database(),user(),@@datadir,@@bas
    edir;
    -- 查看当前数据库有多少表
    show tables;
    show tables from d3;
    show tables from mysql;
    -- 建立表
    create table 表名(
    属性名（列名，字段名) 类型 约束
    default
    not null
    unsigned zerofill
    auto_increment
    unique,
    ...
    primary key(id)
    )engine=innodb default charset=utf8
    auto_increment=202001;
    use db;
    -- 列出查看当前数据库的所有表和视图
    show tables;
    show tables from mysql;
    -- 建立表
    create table if not exists t(t int);
    create table stu(
    sno int unsigned auto_increment comment
    '学生的学号'
    ,
    sname varchar(30) not null comment '姓
    名'
    ,
    score smallint unsigned default 0
    comment '成绩'
    ,
    saddress varchar(255) comment '学生家庭地
    址'
    ,
    primary key(sno) comment 'sno为PK'
    )engine = innodb auto_increment=20220101
    default charset utf8 comment '学生信息表';
    -- 查看建立表的语句
    show create table stu;
    -- 查看服务器所有存储引擎
    show engines;
    -- 字符集查看
    show charset like 'gb%';
    show charset like 'utf%';
    show variables like 'char%';

**2. 删除表 drop table**

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]
    -- 删除表
    use d1;
    show tables;
    create table t1(t int);
    create table t2(t int);
    create table t3(t int);
    drop table t1;
    -- 判断删除，如果存在就删除，不存在，报警告信息
    drop table if exists `t2`; -- Unknown
    table 'd1.t2'
    show warnings;
    -- 当前在d1数据库上，删除d3数据库中的表t
    drop table if exists d3.t;
    -- 删除一堆表
    drop table if exists t1,t2,t3,t4,t5;

**3. 修改表 alter table**

-- alter table 修改表结构（表名，列名，列的数据类
    型，相关约束）
    use d3;
    show tables;
    -- 查看表结构
    desc tuser;
    describe tuser;
    show columns from tuser;
    describe table tuser;
    -- 修改表名
    alter table tuser rename userinfo;
    rename table userinfo to tuser;
    -- 移动表表tt2从db2库移动当前数据库为tt3表
    rename table db2.tt2 to tt3;
    /*
    RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_*/
    drop table if exists
    t,t1,t3,s,stu,user,tuser,tteacher,dept,empl
    oyees,student;
    create table t1(t int);
    create table t2(t int);
    create table t3(t int);
    -- 修改多个表的名称
    rename table
    t1 to tt1,
    t2 to tt2,
    t3 to tt3;
    -- 查看表结构
    desc tt1;
    -- 查看建立数据库的语句
    show create database mysql;
    -- 查看建立表的语句
    show create table tt1;
    rename table tt1 to student;
    -- 增加字段类型
    alter table student add column id int
    unsigned auto_increment primary key;
    alter table student add sname varchar(15)
    not null;
    alter table student add age tinyint
    unsigned default 18;
    alter table student add gender
    enum('男'
    ,
    '女') default '男' after sname;
    alter table student add address
    varchar(255) first;
    -- 修改列的类型 及 位置
    alter table student modify address
    varchar(255) after age;
    -- 修改列名
    alter table student rename column address
    to saddr;
    -- 修改列名 类型 及 位置
    alter table student change saddr address
    varchar(100) after age;
    -- 复制表结构或建立一个空表
    create table stu like student;
    -- 根据查询的内容建立一个表，此表没有相关的约束，不
    推荐使用
    create table t1 as select 20,
    'jack';
    -- 删除字段
    alter table student drop column t;
    -- 修改表名 student cf_student cf_user
    cf_admin cf_car
    rename table w_student to cf_student;
    alter table cf_student rename st;
    -- 查看表结构
    describe st;
    desc st;
    -- 查看建立表语句
    show create table st;
    CREATE TABLE `st` (
    `id` int unsigned NOT NULL
    AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
    `gender` enum('男'
    ,
    '女') DEFAULT NULL,
    `course` set('计算机英语'
    ,
    '高数'
    ,
    '离散数
    学'
    ,
    '线性代数') DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT
    CHARSET=utf8mb3
    -- 增加一列（字段）
    -- 删除一列（字段）
    -- 修改列名
    -- 修改列的数据类型 int bigint varchar int
    varchar(30) varchar(5)
    select * from st;
    -- 增加一列
    alter table st add address varchar(255) not
    null default '郑州市';
    alter table st add age tinyint unsigned not
    null default 18 first;
    alter table st add age tinyint unsigned
    default 0 after name;
    -- 删除一列
    alter table st drop address;
    alter table st drop column age;
    -- 修改列名 name StudentName sname
    alter table st rename column name to sname;
    alter table st change sname name
    varchar(30) first;
    alter table st change name name varchar(30)
    after id;
    -- 修改列的数据类型及精度
    alter table st modify name varchar(100)
    after address;
    alter table st modify name varchar(50)
    after id;
    -- 复制表的结构
    -- create table t6(t int);
    create table st2 like st;
    show create table st2;
    select * from st2;
    -- 插入数据
    insert into st2 select * from st;
    show tables;
    -- 根据查询的结构，建立一个没有相关约束的表,可以直接
    插入数据
    create table st3 as select id,name from st;
    create table st4 as select id,name,address
    from st where name like '李%';
    select * from st3;
    show create table st3;
    drop table st3;
    select * from st4;

### 3.表约束 ###

**1.PK（主键约束）**

在关系数据库，一个表中，只能有一个主键(Primary Key)，有些数据库没有pk，系统报出错误。在myql数据 库，建立表时，可以有主键，也可以没有主键（推荐建 立表时必须有主键）。 主键列，在插入数据时，必须填写，不能为null，不能 重复。

drop table if exists
    t1,tt2,tt3,student,stu;
    -- 建立表 使用主键
    create table tt1(
    t int primary key
    );
    create table tt2(
    t int,
    primary key(t)
    );
    create table tt3(
    t int unsigned auto_increment primary
    key
    );
    create table tt4(
    t char(5)
    );
    insert tt4 values(1),(2),(2),(3),(2),(5);
    select * from tt4;
    -- 有重复数据，将不法增加主键约束
    alter table tt4 add primary key(t);
    -- 删除主键
    alter table tt4 drop primary key;
    -- 建立复合主键，此表还是只有一个主键，在系统数据库
    mysql.user表中的复合主键。
    create table tt5(
    id int unsigned,
    name varchar(30) not null,
    age tinyint unsigned default 18,
    primary key(id,name)
    );
    -- 表约束
    -- 主键PK约束（索引）唯一约束 非空 默认值
    show tables;
    create table wx_student(
    sno char(6) primary key,/*主键 PK 不能
    空，不能重复，一个表只有有一个主键*/
    sname varchar(30) not null,
    sphone char(11) unique ,/*唯一约束，不能重
    复*/
    sage tinyint unsigned default 18,
    saddr varchar(255),
    key(sname)
    );
    -- 定义uuid自动主键
    create table ws_goods(
    id char(36) default (uuid()) primary
    key,
    name varchar(50) not null,
    price decimal(10,1)
    );
    insert into ws_goods(name,price)
    values(uuid(),
    '笔记本'
    ,3000),('台式机'
    ,5500);
    insert into ws_goods(id,name,price)
    values(uuid(),
    '服装'
    ,260),(uuid(),
    '台式机
    2'
    ,3500);
    select * from ws_goods;
    select uuid_short(),uuid();
    create table t2(t bigint unsigned);
    drop table t2;
    -- 99657422712340480
    insert into t2 value(99657422712340480);
    select
    uuid_short(),uuid_short(),uuid_short(),uuid
    _short();

**从mysql v8.0.13开始，可以使用表达式作为字段的默认值：DEFAULT子句中指定的默认值可以是文字常量或表达式。除一个例外，将表达式默认值括在括号内，以将其与文字常量默认值区分开。**

**唯一的例外是，对于 TIMESTAMP和 DATETIME列， 您可以将CURRENT\_TIMESTAMP函数指定为默认函 数，而不用括号括起来。**

**2.FK（外键约束）**

FK foreign key 外键约束，一般涉及多个表，关联约束 形式，在mysql，表的存储引擎为innodb时才可以使用 外键。

**3.unique 唯一约束**

create table stu(
    id int unsigned auto_increment,
    name varchar(15) unique, /* 唯一约束 ，
    可以不填写，如果填写，不能为重复*/
    age tinyint unsigned default 18, /* 默认
    值 */
    gender enum('男'
    ,
    '女') default '男'
    ,
    address varchar(255) not null, /* 必须填
    写*/
    primary key(id)
    );

**4.not null**

**5.default**

##  ##

[fe1f082a85a044e593e4dc9676f53c53.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ab938b46d6d146f19a01ff50a0e8824b.png
[a68a72ccf83748f5ab93b2ada57e5a10.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e87f061fcc3d451d896c1935da6eb168.png
[b045736f2467484db24a72f2dff95550.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/917a97258a814ce69144818fa23cee05.png