MySQL数据库基础(一) MySQL安装及数据类型

一、MySQL数据库简介

数据库服务概述
常见的数据库软件
主流操作系统 Unix、Linux、Windows
MySQL数据库基础(一) MySQL安装及数据类型_第1张图片

专业术语
DB (DataBase)
-数据库
-依照某种数据模型进行组织并存放到存储器的数据集合

DBMS(DataBase Mangement System)

  • 数据库管理系统
  • 用来操纵和管理数据库的服务软件

DBS(DataBase System)
-数据库系统:即 DB+ DBMS
-指带有数据库并整合了数据库管理软件的计算机系统

起源与发展
-应用最广泛的开源数据库软件
-一最早隶属于瑞典的MySQL AB公司
-2008年1月,MySQL AB被Sun收购
-2009年4月,SUN被Oracle收购

崭新的开源分支MariaDB
-为应付MySQL可能会闭源的风险而诞生
-由MySQL原作者 Widenius主导开发-与MySQL保持最大程度兼容
-MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。

特点及应用
主要特点
-适用于中小规模、关系型数据库系统
-支持Linux、Unix、Windows等多种操作系统
-支持Python、Java、Perl、PHP等编程语言

典型应用环境
-LAMP平台,与Apache HTTP Server组合
-LNMP平台,与Nginx组合

二、MySQL数据的安装

2.1、MySQL安装

准备环境 基本需求
1.创建CentOS系统虚拟机1台
2.配置IP地址192.168.4.150
3.关闭firewalld
4.禁用SELinux
5.配置yum源 安装mysql-server

1 ) 安装MySQL 配置官网的yum源

[root@mysql ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm    #安装官方yum源
[root@mysql ~]# yum clean all
[root@mysql ~]# yum repolist
[root@mysql ~]# yum -y install  mysql-server    #安装MySQL服务端

[root@mysql ~]# rpm -qa |grep mysql
mysql-community-client-8.0.22-1.el7.x86_64      #客户端应用程序
mysql80-community-release-el7-3.noarch          #之前下载的 yum安装包
mysql-community-common-8.0.22-1.el7.x86_64      #数据库和客户端库共享文件
mysql-community-libs-8.0.22-1.el7.x86_64        #数据库客户端应用程序的共享库
mysql-community-client-plugins-8.0.22-1.el7.x86_64
mysql-community-server-8.0.22-1.el7.x86_64      #数据库服务端

[root@mysql ~]# systemctl start  mysqld         #开启服务
[root@mysql ~]# systemctl enable mysqld         #设置开机启动
[root@mysql ~]# systemctl status  mysqld        #查看服务状态
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 二 2020-11-24 14:07:31 CST; 4min 21s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2919 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 2991 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─2991 /usr/sbin/mysqld

11月 24 14:07:09 mysql systemd[1]: Starting MySQL Server...
11月 24 14:07:31 mysql systemd[1]: Started MySQL Server.


[root@mysql ~]# ss -anput|grep 3306         #查看服务占用端口
tcp    LISTEN     0      70       :::33060                :::*                   users:(("mysqld",pid=2991,fd=32))
tcp    LISTEN     0      128      :::3306                 :::*                   users:(("mysqld",pid=2991,fd=34))

软件安装后自动创建相关目录文件
主配置文件
-/etc/my.cnf
数据库目录
-/var/lib/mysql
默认端口号: 3306
进程名 mysqld
传输协议 TCP
进程所有者 mysql
进程所属组 mysql
错误日志文件
-/var/log/mysqld.log

2 ) 初始密码登录
数据库管理员名为root

  • 默认仅允许root本机连接
  • 首次登录密码在安装软件时随机生成
  • 随机密码存储在日志文件/var/log/mysqld.log里
  • 连接命令 ]# mysql -h数据库地址 -u用户 -p密码
  • 数据库命令输入默认不支持 Tab键命令补齐,这也进一步增加了数据管理员的难度,在日常的使用管理中,管理员一般会借助第三方的数据库管理软件

常用的数据库管理软件有:
-Workbench
-Navicat

2.2、修改MySQL密码登录策略

案例1:
要求:登陆数据库修改密码策略 设置简单密码

[root@mysql ~]# grep -i 'password' /var/log/mysqld.log      //查看随机密码
2020-11-24T06:07:17.500550Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: R-SsjKcfZ4a7
[root@mysql ~]# mysql -u root -p'R-SsjKcfZ4a7'              //登陆mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.22

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>                                               //登录成功后,进入SQL操作环境
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.    #提示需要修改默认密码

修改root密码
-具体操作如下
-使用alter user命令修改登录密码―新密码必须满足密码策略
修改登陆密码方法如下:

mysql> alter user root@"localhost " identified by“密码";

连接MySQL服务器时,最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码
可以写在命令行(如果不写,则出现交互,要求用户输入),当然基于安全考虑一般
不推荐这么做:所以以下明文密码会有警告!
[Warning] Using a password on the command line interface can be insecure.

mysql> alter user root@"localhost" identified by "123AAA...b";     //修改密码
Query OK, 0 rows affected (0.03 sec)
mysql> exit               //退出数据库 或使用 quit
Bye

[root@mysql ~]# mysql -u root -p'123AAA...b'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

3 ) 修改密码策略
为了方便后面的演示,输入密码不用太复杂设置一个简单的密码 默认数据库是有密码复杂度要求的

临时生效 服务重启后失效

mysql> set global validate_password.policy=0;      //只验证长度
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=6;     //修改密码长度为6位,默认为8位
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@"localhost" identified by "123456";    //修改密码为123456
Query OK, 0 rows affected (0.04 sec)

mysql> exit
Bye

修改配置文伯 永久性生效

[root@mysql ~]# vim /etc/my.cnf    
[mysqld]                         //在mysqld后面添加这两行
validate_password.policy=0 
validate_password.length=6
[root@mysql ~]# systemctl restart mysqld

[root@mysql ~]# mysql -u root -p123456          //登陆MySQL
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show  databases;             //显示已有的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.06 sec)
mysql> 

三、数据库基础管理

3.1、连接方式及数据储存流程

客户端连接MySQL服务的方法
1.命令行
2.web页面
3.安装图形软件
4.编写脚本( php、Java、python.....)

-使用 mysql 命令登录
mysql -h服务器IP -u用户名 -p密码「数据库名]
-quit或exit退出

数据存储流程
客户端把数据存储到数据库服务器上的步骤
1.连接数据库服务器
2.建库 //类似于文件夹
3.建表 //类似于文件
4.插入记录 //类似于文件内容
5.断开连接

SQL命令使用规则

  • SQL命令不区分字母大小写(密码、变量值除外)
  • 每条SQL命令以 ; 结束
  • 默认命令不支持Tab键自动补齐
  • \c 终止sql命令

MySQL管理环境(续1)
常用的SQL命令分类
管理数据库使用SQL(结构化查询语言)
1.DDL数据定义语言 如:create、alter、drop
2.DML数据操作语言 如:insert、update、delete
3.DCL数据控制语言 如:grant、revoke
4.DTL数据事物语言 如:commit、rollback、savepoint

3.2、库管理命令

库管理命令

  • 库类似于文件夹,用来存储表
  • 可以创建多个库,通过库名区分
  • show databases; #显示已有的库
  • select user( ); #显示连接用户
  • use 库名; #切换库
  • select database( ); #显示当前所在的库
  • create database 库名; #创建新库
  • show tables; #显示已有的表
  • drop database 库名; #删除库

库管理命令(续1)
库名命名规则

  • 仅可以使用数字、字母、下划线、不能纯数字
  • 区分字母大小写,具有唯一性
  • 不可使用指令关键字、特殊字符

    mysql> create database DB1;
    Query OK, 1 row affected (0.03 sec)
    
    mysql> CREATE DATABASE db1;
    Query OK, 1 row affected (0.04 sec)

    3.3、表管理命令

    ·建表
    -表存储数据的文件。
    Mysql> create table 库名.表名(

     字段名1  类型(宽度),
     字段名2  类型(宽度),
     ......

    )DEFAULT CHARSET=utf8; #指定中文字符集,可以给字段赋值中文

mysql> create table db1.stuinfo(
name char(15),
homeaddr char(20)

表管理命令(续1)

  • 表类似于文件,
  • desc库名.表名; #查看表结构
  • drop table库名.表名; #删除表

3.4、记录管理命令

  • 记录类似于文件里的行
  • select * from库名.表名; #查看表记录
  • insert into库名.表名 values(值列表); #插入表记录
  • update 库名.表名 set字段=值; #修改表记录
  • delete from表名; #删除表记录

    mysq> insert into db1.stuinfo values("jim" ;" usa"),("lilei" , "china");
    mysql> select * from db1.stuinfo;
    Mysql> update db1.stuinfo set homeaddr= "beijing" ;

    案例2∶数据库基本管理
    1.使用mysql命令连接数据库 新建如下表格
    2.练习库管理命令(查看、删除、创建库、切换)
    3.练习表管理命令(查看、删除、创建表)
    4.练习记录管理命令(插入、查看、修改、删除)

1 ) 使用mysql命令连接数据库,练习查看现在的库

[root@mysql ~]# mysql -u root -p123456
mysql> show  databases;
+--------------------+
| Database           |
+--------------------+
| DB1                |
| db1                |
| information_schema |           //信息概要库
| mysql              |           //授权库
| performance_schema |           //性能结构库
| sys                |           //系统元数据库
+--------------------+
6 rows in set (0.06 sec)

2)切换/使用指定的库

mysql> use sys;    //切换到sys库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();    //确认当前所在的库
+------------+
| database() |
+------------+
| sys        |
+------------+
1 row in set (0.00 sec)

mysql> use mysql;   //切换到mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();   //确认当前所在的库
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

3)新建名为newdb的库,确认结果

mysql> create database newdb;     //新建名为newdb的库
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DB1                |
| db1                |
| information_schema |
| mysql              |   
| newdb              |   //新建的newdb库
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.01 sec)

4)删除指定的库

mysql> drop database newdb;   //删除名为newdb的库
Query OK, 0 rows affected (0.02 sec)
 
mysql> show databases;    //确认删除结果,已无newdb库
+--------------------+
| Database           |
+--------------------+
| DB1                |
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

5)查看指定的库里有哪些表

mysql> use mysql
Database changed
mysql> use mysql;
Database changed
mysql> show tables;
+----------------------------------------------+
| Tables_in_mysql                              |
+----------------------------------------------+
| columns_priv                                 |
| component                                    |
| db                                           |
| default_roles                                |
| engine_cost                                  |
| func                                         |
| general_log                                  |
| global_grants                                |
| gtid_executed                                |
| help_category                                |
| help_keyword                                 |
| help_relation                                |
| help_topic                                   |
| innodb_index_stats                           |
| innodb_table_stats                           |
| password_history                             |
| plugin                                       |
| procs_priv                                   |
| proxies_priv                                 |
| replication_asynchronous_connection_failover |
| role_edges                                   |
| server_cost                                  |
| servers                                      |
| slave_master_info                            |
| slave_relay_log_info                         |
| slave_worker_info                            |
| slow_log                                     |
| tables_priv                                  |
| time_zone                                    |
| time_zone_leap_second                        |
| time_zone_name                               |
| time_zone_transition                         |
| time_zone_transition_type                    |
| user                                         |     //存放数据库用户的表
+----------------------------------------------+
34 rows in set (0.00 sec)

6)查看指定表的字段结构
当前库为mysql,查看columns_priv表的结构,以列表形式展现

mysql> desc columns_priv\G
*************************** 1. row ***************************
  Field: Host
   Type: char(255)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 2. row ***************************
  Field: Db
   Type: char(64)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 3. row ***************************
  Field: User
   Type: char(32)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 4. row ***************************
  Field: Table_name
   Type: char(64)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 5. row ***************************
  Field: Column_name
   Type: char(64)
   Null: NO
    Key: PRI
Default: 
  Extra: 
*************************** 6. row ***************************
  Field: Timestamp
   Type: timestamp
   Null: NO
    Key: 
Default: CURRENT_TIMESTAMP
  Extra: DEFAULT_GENERATED on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key: 
Default: 
  Extra: 
7 rows in set (0.00 sec)

查看columns_priv表的结构,以表格形式展现

mysql> desc columns_priv;     //查看表结构,以表格形式展现末尾需要有分号
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                                         |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host        | char(255)                                    | NO   | PRI |                   |                                               |
| Db          | char(64)                                     | NO   | PRI |                   |                                               |
| User        | char(32)                                     | NO   | PRI |                   |                                               |
| Table_name  | char(64)                                     | NO   | PRI |                   |                                               |
| Column_name | char(64)                                     | NO   | PRI |                   |                                               |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                                               |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)

上述操作中,当引用非当前库中的表时,可以用“库名.表名”的形式。比如,切换
为mysql库再执行“desc columns_priv;”,与以下操作的效果是相同的:

mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                                         |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host        | char(255)                                    | NO   | PRI |                   |                                               |
| Db          | char(64)                                     | NO   | PRI |                   |                                               |
| User        | char(32)                                     | NO   | PRI |                   |                                               |
| Table_name  | char(64)                                     | NO   | PRI |                   |                                               |
| Column_name | char(64)                                     | NO   | PRI |                   |                                               |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                                               |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)

在mydb库中创建一个名为pwlist的表

mysql> create database mydb;
Query OK, 1 row affected (0.28 sec)

mysql> use mydb
Database changed

mysql> create table pwlist(                 
    -> name char(16) not null,
    -> password char(48)default'',
    -> primary key(name)
    -> );
Query OK, 0 rows affected (0.39 sec)

mysql> show tables;     //查看新建的表单
+----------------+
| Tables_in_mydb |
+----------------+
| pwlist         |
+----------------+
1 row in set (0.01 sec)

mysql> desc pwlist;     //查看pwlist表的字段结构
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(16) | NO   | PRI | NULL    |       |
| password | char(48) | YES  |     |         |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> drop table pwlist;   //删除当前库中的pwlist表
Query OK, 0 rows affected (0.11 sec)

案例3:
在mydb库中创建一个学员表
在MySQL表内存储中文数据时,需要更改字符集(默认为latin1不支持中文),以便
MySQL支持存储中文数据记录;比如,可以在创建库或表的时候,手动添加“DEFAULT
CHARSET=utf8”来更改字符集。
根据上述表格结构,创建支持中文的student表:
MySQL数据库基础(一) MySQL安装及数据类型_第2张图片

mysql> create table mydb.student( 
    -> 学号 char(9) NOT NULL,
    -> 姓名 varchar(4) NOT NULL,
    -> 性别 enum('男','女') NOT NULL,
    -> 手机号 char(11) DEFAULT'',
    -> 通信地址 varchar(64),
    -> PRIMARY KEY(学号)
    -> )DEFAULT CHARSET=utf8;   //手工指定字符集,采用utf8
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> DESC mydb.student;   //查看student表的字段结构
+--------------+-------------------+------+-----+---------+-------+
| Field        | Type              | Null | Key | Default | Extra |
+--------------+-------------------+------+-----+---------+-------+
| 学号         | char(9)           | NO   | PRI | NULL    |       |
| 姓名         | varchar(4)        | NO   |     | NULL    |       |
| 性别         | enum('男','女')   | NO   |     | NULL    |       |
| 手机号       | char(11)          | YES  |     |         |       |
| 通信地址     | varchar(64)       | YES  |     | NULL    |       |
+--------------+-------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show create table mydb.student;   //查看student表的实际创建指令:
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `学号` char(9) NOT NULL,
  `姓名` varchar(4) NOT NULL,
  `性别` enum('男','女') NOT NULL,
  `手机号` char(11) DEFAULT '',
  `通信地址` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                  |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit

注意:若要修改MySQL服务的默认字符集,可以更改服务器的my.cnf配置文件,添加
character_set_server=utf8 配置,然后重启数据库服务。

[root@mysql ~]# cat /etc/my.cnf
[mysqld]
......
character_set_server=utf8    //添加默认字符集

[root@mysql ~]# systemctl restart mysqld    //重启服务
[root@mysql ~]# mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8                           |          //修改成功
| character_set_system     | utf8                           | 
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

四、MySQL数据类型

4.1、常见信息种类

  • 数值型∶体重、身高、成绩、工资
  • 字符型:姓名、工作单位、通信住址
  • 枚举型:兴趣爱好、性别、专业
  • 日期时间型:出生日期、注册时间

4.2、字符型

定长char**

  • 定长:char(字符个数)
  • 最大字符个数255
  • 不够指定字符个数时在右边用空格补全
  • 字符个数超出时,无法写入数据。
mysql> create table db1.t1(
    -> name char(5),
    -> homedir char(50)
    -> );
Query OK, 0 rows affected (0.42 sec)
mysql> insert into db1.t1 values("bob","USA");

变长varchar

  • 变长: varchar(字符个数)
  • 按数据实际大小分配存储空间
  • 字符个数超出时,无法写入数据。·大文本类型: text/blob
  • 字符数大于65535存储时使用
mysql> create table db1.t7(
    -> name char(5),
    -> email varchar(30)
    -> );
Query OK, 0 rows affected (0.44 sec)
mysql> insert into db1.t7 values("lucy","luck@163.com");

案例4:
按照如下结构建表:

mysql> desc db1.t3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | char(5)     | YES  |     | NULL    |       |
| mail    | varchar(10) | YES  |     | NULL    |       |
| homedie | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

1)新建db1库,并切换到db1库

mysql> create database db1;
Query OK, 1 row affected (0.02 sec)

mysql> use db1;
Database changed

2)新建t3表

mysql> create table db1.t3(
    -> name char(5),
    -> mail varchar(10),
    -> homedie varchar(50)
    -> );
Query OK, 0 rows affected (0.11 sec)

3) 查看a3表结构

mysql> desc db1.t3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | char(5)     | YES  |     | NULL    |       |
| mail    | varchar(10) | YES  |     | NULL    |       |
| homedie | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.3、数值型

整数型
只能存储整数
MySQL数据库基础(一) MySQL安装及数据类型_第3张图片

浮点型·浮点型
-格式1∶字段名类型;
-格式2∶字段名类型(总宽度,小数位数)
MySQL数据库基础(一) MySQL安装及数据类型_第4张图片

案例5
要求:按照 下图所示建表

mysql> desc db1.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int        | YES  |     | NULL    |       |
| name    | char(5)    | YES  |     | NULL    |       |
| age     | tinyint    | YES  |     | NULL    |       |
| pay     | float      | YES  |     | NULL    |       |
| money   | float(5,2) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+

1)创建t2表

mysql> use db1;
Database changed
mysql> create table db1.t2(
    -> stu_num int,
    -> name char(5),
    -> age tinyint,
    -> pay float,
    -> money float(5,2)
    -> );
Query OK, 0 rows affected, 1 warning (0.07 sec)

2) 查看t2表结构

mysql> desc db1.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int        | YES  |     | NULL    |       |
| name    | char(5)    | YES  |     | NULL    |       |
| age     | tinyint    | YES  |     | NULL    |       |
| pay     | float      | YES  |     | NULL    |       |
| money   | float(5,2) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

4.4、日期时间型

类型
日期时间 datetime

  • 范围:1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 格式:yyyymmddhhmmss

日期时间 timestamp

  • 范围:1970-01-01 00:00:00 ~ 2038-01-19 00:00:00
  • 格式:yyyymmddhhmmss

**类型(续1)
日期date**
-范围:0001-01-01 ~ 9999-12-31
-格式:yyyymmdd

年year
-范围:1901~2155
-格式:yyyy

时间time
-格式:HH:MM:SS

类型(续2)
关于日期时间字段
-当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime值为NULL(空)

year类型
-要求使用4位数赋值
-当使用2位数赋值时:

  • 01~69视为 2001~2069
  • 70~99视为1970~1999

时间函数
· MySQL服务内置命令
一可以使用时间函数给字段赋值
MySQL数据库基础(一) MySQL安装及数据类型_第5张图片

案例6:
要求:
练习如下时间函数的使用:
now( ) year( ) month( ) day( ) date( ) time( )
curtime( ) curdate( )
按照如下图所示建表

mysql> desc db1.t4;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| name       | char(10) | YES  |     | NULL    |       |
| your_start | year     | YES  |     | NULL    |       |
| up_time    | time     | YES  |     | NULL    |       |
| birthday   | date     | YES  |     | NULL    |       |
| party      | datetime | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+

1)练习如下命令的使用 now( ) year( ) month( ) day( ) date( ) time( )

mysql> select now();   //使用now()查看当前的日期和时间
+---------------------+
| now()               |
+---------------------+
| 2020-11-25 15:18:08 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();  //使用curdate()获得当前的日期
+------------+
| curdate()  |
+------------+
| 2020-11-25 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();  //使用curtime()获得当前的时间
+-----------+
| curtime() |
+-----------+
| 15:18:41  |
+-----------+
1 row in set (0.00 sec)

mysql> select year(now()),month(now()),day(now());  //分别获取当前日期时间中的年份、月份、日
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
|        2020 |           11 |         25 |
+-------------+--------------+------------+
1 row in set (0.00 sec)

mysql> select date(now()) ;  //获取系统日期
+-------------+
| date(now()) |
+-------------+
| 2020-11-25  |
+-------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2020-11-25  |
+-------------+
1 row in set (0.01 sec)

2)创建t4表 建表

mysql> create table db1.t4(
    -> name char(10),
    -> your_start year,
    -> up_time time,
    -> birthday date,
    -> party datetime
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc db1.t4;    //查看表结构
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| name       | char(10) | YES  |     | NULL    |       |
| your_start | year     | YES  |     | NULL    |       |
| up_time    | time     | YES  |     | NULL    |       |
| birthday   | date     | YES  |     | NULL    |       |
| party      | datetime | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert into db1.t4 values("bob",1990,083000,20191120,20190828200000);    //插入记录
Query OK, 1 row affected (0.29 sec)

mysql> insert into db1.t4 values("tom",1991,090000,20191120,now());
Query OK, 1 row affected (0.26 sec)

mysql> select * from db1.t4;   //查看表所有列名的集合
+------+------------+----------+------------+---------------------+
| name | your_start | up_time  | birthday   | party               |
+------+------------+----------+------------+---------------------+
| bob  |       1990 | 08:30:00 | 2019-11-20 | 2019-08-28 20:00:00 |
| tom  |       1991 | 09:00:00 | 2019-11-20 | 2020-11-25 15:37:26 |
+------+------------+----------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> select name  from db1.t4;  //查看表name列记录
+------+
| name |
+------+
| bob  |
| tom  |
+------+
2 rows in set (0.00 sec)

mysql> 

4.5、枚举型

enum

  • enum单选︰
  • 格式∶字段名enum(值1,值2,值N)
  • 仅能选择一个值
  • 字段值必须在列表里选择

set 多选:
-格式:字段名set(值1,值2,值N)
-选择一个或多个值
-字段值必须在列表里选择

案例7:
要示 按照如下图所示建表

mysql> desc db1.t5;
+-------+----------------------------------+------+-----+---------+-------+
| Field | Type                             | Null | Key | Default | Extra |
+-------+----------------------------------+------+-----+---------+-------+
| name  | char(5)                          | YES  |     | NULL    |       |
| likes | set('eat','game','film','music') | YES  |     | NULL    |       |
| sex   | enum('boy','girl','no')          | YES  |     | NULL    |       |
+-------+----------------------------------+------+-----+---------+-------+

1)创建t5表

mysql> create table db1.t5(
    -> name char(5),
    -> likes set("eat","game","film","music"),
    -> sex enum("boy","girl","no")
    -> );
Query OK, 0 rows affected (0.30 sec)

2)查看表结构

mysql> desc db1.t5;
+-------+----------------------------------+------+-----+---------+-------+
| Field | Type                             | Null | Key | Default | Extra |
+-------+----------------------------------+------+-----+---------+-------+
| name  | char(5)                          | YES  |     | NULL    |       |
| likes | set('eat','game','film','music') | YES  |     | NULL    |       |
| sex   | enum('boy','girl','no')          | YES  |     | NULL    |       |
+-------+----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3)插入表记录

mysql> insert into db1.t5 values("bob","eat,film,game","boy");
Query OK, 1 row affected (0.02 sec)

mysql> select * from db1.t5;
+------+---------------+------+
| name | likes         | sex  |
+------+---------------+------+
| bob  | eat,game,film | boy  |
+------+---------------+------+
1 row in set (0.00 sec)

你可能感兴趣的