三、数据库和表管理

作者: 楠有枝 | 来源:发表于2019-07-13 16:29 被阅读0次

一、数据库管理

创建库时指定字符集(utf-8, gb2312)

-- 查看当前数据库
SELECT DATABASE();

-- 显示当前时间、用户名、数据库版本
SELECT now(), user(), version();

-- 创建库
CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
    数据库选项：

        DEFAULT CHARACTER SET charset_name
        COLLATE collation_name

-- 查看已有库
SHOW DATABASES[ LIKE 'PATTERN']

-- 查看当前库信息
SHOW CREATE DATABASE 数据库名

-- 修改库的选项信息
ALTER DATABASE 库名 选项信息

-- 删除库,同时删除该数据库相关的目录及其目录内容
DROP DATABASE[ IF EXISTS] 数据库名

charset_name 就是字符集，比如：

gbk

utf8

utf8mb4

collation_name 排序规则，比如:

gbk_chinese_ci

utf8_general_ci

utf8mb4_general_ci

# utf-8
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# gbk
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

Example

mysql> create database teacher default charset utf8mb4 collate utf8mb4_general_ci;

使用数据库

USE 数据库名字;

Example

use teacher;

查看当前库

select database();
/*或*/
status;

查看库的默认字符集

mysql> show  create database teacher\G

删除数据库

drop database  数据库名称;

Example

drop database teacher;

二、表的管理

1. MySQL 表的基本概念

MySQL 的表存放了实际的数据。

表大体可以分为表头和表体，一张表只有一个表头，也就是一行数据，
这行数据的每一列的值成为字段。

表体可以有多行数据组成，每一行代表了一个要表现的实际对象。

每个字段代表了一条数据都有那些属性，比如存一个学生的信息，需要有姓名、年龄、学号等。

对于这些属性，你会发现它们是不同类型的数据，比如姓名是普通的文本，就是字符串，而年龄就是数字，也就是整型。

为了保证这些数据存放到数据库时，不会出现类型的错误而导致以后影响使用，在创建表时，必须对每个字段指定要存放数据的数据类型。

学生表

id	name	age	phone
1	王麻子	32	13141098899
2	九筒	32	13721096541
3	六子	18	13139192657
4	马邦德	37	15855996688

2. MySQL 中的数据类型

int(M)[UNSIGNED][ZEROFILL]
             UNSIGNED 表示符号
             ZEROFILL   表示不够的位使用 0 填充
          number  int(2)  UNSIGNED  ZEROFILL 
         number 的值假如你存1，显示的时候不够 2 位，就会使用 0 填充，变成 02
            整数，数据类型用于保存一些范围的整数数值范围：
                有符号：
                    -2147483648 ～ 2147483647
                无符号：
                    0 ～ 4294967295

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
            单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
                无符号：
                    -3.402823466E+38 to -1.175494351E-38,
                    0
                    1.175494351E-38 to 3.402823466E+38
                有符号：
                    0
                    1.175494351E-38 to 3.402823466E+38

            **** 数值越大，越不准确 ****

char (m)
            char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
            PS: 即使数据小于m长度，也会占用m长度
varchar(m)
            varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

            注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

text
            text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

        mediumtext
            A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

        longtext
            A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

/* 定义数据库列时，可以使用Enum（Enumeration，枚举）和SET（集合）类型：变通的实现CHECK约束

两者的区别是：

　　使用Enum，只能选一个值；

　　使用SET，可以选多个值；

Enum和SET中的值都必须是字符串类型。

注意：

　　在内部存储Enum值时，MYSQL给Enum中的每个值一个顺序号码：第一个值的顺序号码是1，第二个值的顺序号码是2，以此类推。当排序或比较Enum的时候，使用这些顺序号码进行。
*/

Enum
            枚举类型，
            An Enum column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
            示例：
                CREATE TABLE shirts (
                    name VARCHAR(40),
                    size Enum('x-small', 'small', 'medium', 'large', 'x-large')
                );
                INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

set
            集合类型
            A SET column can have a maximum of 64 distinct members.
            示例：
                CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
                INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

关于日期和时间类型的字段

字段类型: DATE
表现形式: YYYY-MM-DD
取值范围: 1000-01-01/9999-12-31
创建方法: 字段名 DATE
添加数据: values("20191010")
查询到的结果: 2019-10-10

DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

YEAR
            YYYY（1901/2155）

DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

3. MySQL 表的操作 DDL

DDL（Data Definition Language 数据定义语言）

用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DDL不需要commit（DDL都是被隐式提交，不能对DDL语句使用ROLLBACK命令）。

CREATE     创建表,在数据库创建对象
ALTER      修改表,修改数据库结构(扩展自修)
DROP       删除表,从数据库中删除对象
TRUNCATE   删除表中所有行（无法回退）
COMMENT    注释（为数据字典添加备注）
RENAME     重命名表(扩展自修)

关于存储引擎

  -- 存储引擎
  ENGINE = engine_name
  表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同
  常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
  不同的引擎在保存表的结构和数据时采用不同的方式
  MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
  InnoDB表文件含义：.frm表定义，表空间数据和日志文件
  SHOW ENGINES -- 显示存储引擎的状态信息
  SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息
    -- 自增起始数
        AUTO_INCREMENT = 行数
    -- 数据文件目录
        DATA DIRECTORY = '目录'
    -- 索引文件目录
        INDEX DIRECTORY = '目录'
    -- 表注释
        COMMENT = 'string'
    -- 分区选项
        PARTITION BY ... (详细见手册)

a. 创建表

create table 表名[ IF NOT EXISTS](
    列名  类型  [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'],
    列名  类型
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表时可以单独指定其使用的存储引擎和默认字符编码。
ENGINE=InnoDB DEFAULT CHARSET=utf8

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
    id int not null ,                                                                                
    age int not null default 20
)

是否可空，null表示空，并不是字符串类型
not null    - 不可空，在添加数据时必须给值
null        - 可空(这是非主键的默认属性)

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
create table tb1(
    id int   primary key,
    age int null
)
或
create table tb1(
    id int not null,
    age int not null,
    primary key(id,age)
)

自增，如果为某列设置自增列，插入数据时无需设置此列的值，默认将自增（表中只能有一个自增列）
create table 表名(
    id int auto_increment primary key,
    age int not null
)

Example

create table student(
    id int  auto_increment primary key,
    name varchar(12) not null,
    age int not null,
    phone char(11)
)

创建日期和时间类型和存值

创建

mysql> create table tb_datas(
    -> date1 date,
    -> time2 time,
    -> yyyy year,
    -> yyyy_mm_ddThh datetime,
    -> yyyymmddhh  timestamp);

插入数据

insert into tb_datas(date1,time2,yyyy,yyyy_mm_ddThh,yyyymmddhh) values( 20191010,"20:00:00",2019,"2019-10-10 20:00:00","2019-10-11 20:00:00");

查询到的数据

mysql> select * from tb_datas\G
*************************** 1\. row ***************************
        date1: 2019-10-10
        time2: 20:00:00
         yyyy: 2019
yyyy_mm_ddThh: 2019-10-10 20:00:00
   yyyymmddhh: 2019-10-11 20:00:00
1 row in set (0.00 sec)

创建表时，定义外键,一对多关系

首先创建主表，主表就是，表中的一条数据会对应到从表中的多条数据，比如班级表中的一条数据就代表了一个班级，一个班级中可以有多个学生，所以班级表就是主表，学生表就是从表。

那需要先创建一个主表： class

# 首先切换到一个库中再创建表
mysql> use  db1;

# 接着创建表
create table class(
id  int auto_increment primary key,
name varchar(12)
)

接下来可以创建从表了，并在创建从表的时候设置外键

create table student2(
    id int auto_increment primary key,
    class_id int,
    foreign key(class_id) REFERENCES class(id)
);

创建表时，定义多个外键，多对多关系(扩展)

create table class2teacher(
    id int auto_increment primary key, 
    class_id int, teacher_id int, 
    foreign key(class_id) REFERENCES class(id),
    foreign key(teacher_id) references teacher(id)
);

b. 查看当前数据库中所有表

mysql> show tables;
+--------------------+
| Tables_in_shark_db |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

c. 查看表结构

/*查看当前库中指定表的表结构*/
mysql> desc 表名;

/*查看指定库中指定表的表结构*/
mysql> desc 库名.表名;

d. 清空表的内容，表本身还在。

delete from 表名    /*自增列的值继续递增，可以加 where 子句*/
truncate table 表名  /*自增列的值重新从 1 开始*/

/*删除表中的所有数据，自增列的值继续递增*/
delete from tb1;

/*删除表中的某些数据，被删除的数据的自增列的值将不会再次出现，自增列的值继续递增*/
delete  from  tb1   where id < 20;

e. 删除表，表将消失

drop table  表名     /*整个表将不复存在*/

三、数据库和表管理

一、数据库管理

创建库时指定字符集(utf-8, gb2312)

使用数据库

查看当前库

查看库的默认字符集

删除数据库

二、表的管理

1. MySQL 表的基本概念

2. MySQL 中的数据类型

关于日期和时间类型的字段

3. MySQL 表的操作 DDL

关于存储引擎

a. 创建表

接下来可以创建从表了，并在创建从表的时候设置外键

b. 查看当前数据库中所有表

c. 查看表结构

d. 清空表的内容，表本身还在。

e. 删除表，表将消失

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

三、数据库和表管理

一、数据库管理

创建库时指定字符集(utf-8, gb2312)

使用数据库

查看当前库

查看库的默认字符集

删除数据库

二、 表的管理

1. MySQL 表的基本概念

2. MySQL 中的数据类型

关于日期和时间类型的字段

3. MySQL 表的操作 DDL

关于存储引擎

a. 创建表

接下来可以创建从表了，并在创建从表的时候设置外键

b. 查看当前数据库中所有表

c. 查看表结构

d. 清空表的内容，表本身还在。

e. 删除表，表将消失

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

二、表的管理