一、搭建Mysql服务器
下载软件mysql-*****.tar
关闭防火墙（如果有的话）
关闭SELinux（如果有的话）
步骤一：准备工作
1）如果之前有mariadb，则需要先卸载，并删除对应的配置与数据：
1 [root@localhost ~]# systemctl stop mariadb
2）删除/etc/my.cnf配置文件
此配置文件由RHEL自带的mariadb-libs库提供：
1 [root@localhost ~]# rm -rf /etc/my.cnf
3）删除数据
1 [root@localhost ~]# rm -rf /var/lib/mysql/*
4）卸载软件包（没有会显示未安装软件包）
1 [root@localhost ~]# rpm -e --nodeps mariadb-server mariadb 2 警告：/var/log/mariadb/mariadb.log 已另存为/var/log/mariadb/mariadb.log.rpmsave
步骤二：安装mysql
1）解压mysql-***.tar 软件包
1 [root@host50 ~]# tar -xvf mysql-5.7.17.tar //解压mysql整合包 2 ./mysql-community-client-5.7.17-1.el7.x86_64.rpm 3 ./mysql-community-common-5.7.17-1.el7.x86_64.rpm 4 ./mysql-community-devel-5.7.17-1.el7.x86_64.rpm 5 ./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm 6 ./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm 7 ./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm 8 ./mysql-community-libs-5.7.17-1.el7.x86_64.rpm 9 ./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm 10 ./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm 11 ./mysql-community-server-5.7.17-1.el7.x86_64.rpm 12 ./mysql-community-test-5.7.17-1.el7.x86_64.rpm
2）安装MySQL软件包
1 [root@host50 ~]# yum -y install mysql-community-*.rpm //yum安装自动解决依赖 2 ./mysql-community-client-5.7.17-1.el7.x86_64.rpm 3 ./mysql-community-common-5.7.17-1.el7.x86_64.rpm 4 ./mysql-community-devel-5.7.17-1.el7.x86_64.rpm 5 ./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm 6 ./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm 7 ./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm 8 ./mysql-community-libs-5.7.17-1.el7.x86_64.rpm 9 ./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm 10 ./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm 11 ./mysql-community-server-5.7.17-1.el7.x86_64.rpm 12 ./mysql-community-test-5.7.17-1.el7.x86_64.rpm
3）启动MySQL数据库服务并设置开机自启
1 [root@host50 ~]# systemctl start mysqld //启动mysql服务 2 [root@host50 ~]# systemctl enable mysqld //设置开机自启 3 [root@host50 ~]# systemctl status mysqld //查看mysql服务状态 4 ● mysqld.service - MySQL Server 5 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) 6 Active: active (running) since 二 2018-08-28 10:03:24 CST; 8min ago 7 Docs: man:mysqld(8) 8 http://dev.mysql.com/doc/refman/en/using-systemd.html 9 Main PID: 4284 (mysqld) 10 CGroup: /system.slice/mysqld.service 11 └─4284 /usr/sbin/mysqld --daemonize --pid-file=/var/r... 12 8月 28 10:02:56 localhost.localdomain systemd[1]: Starting MySQ... 13 8月 28 10:03:24 localhost.localdomain systemd[1]: Started MySQL... 14 Hint: Some lines were ellipsized, use -l to show in full.
步骤三：连接MySQL服务器，修改初始密码
1）查看初始密码
1 [root@host50 ~]#grep –i 'password' /var/log/mysqld.log 2 2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for root@localhost: mtoa>Av<p6Yk //随机生成的管理密码为mtoa>Av<p6Yk
2）使用初始密码连接mysql服务
1 [root@host50 ~]# mysql -u root -p'mtoa>Av<p6Yk' //初始密码登录， 2 mysql: [Warning] Using a password on the command line interface can be insecure. 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 11 5 Server version: 5.7.17 6 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 7 Oracle is a registered trademark of Oracle Corporation and/or its 8 affiliates. Other names may be trademarks of their respective 9 owners. 10 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 11 mysql> //登录成功后，进入SQL操作环境
3）重置数据库管理员roo本机登录密码
1 mysql> show databases; 2 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement //提示必须修改密码 3 mysql> alter user root@”localhost” identified by "123qqq…A"; //修改登陆密码 4 Query OK, 0 rows affected (0.00 sec) 5 mysql> exit //断开连接 6 [root@host50 ~]#
4）修改密码策略
1 [root@host50 ~]# mysql -uroot –p123qqq…A 2 mysql> 3 mysql>set global validate_password_policy=0; //只验证长度 4 Query OK, 0 rows affected (0.00 sec) 5 mysql>set global validate_password_length=6； //修改密码长度,默认值是8个字符 6 Query OK, 0 rows affected (0.00 sec) 7 mysql> alter user root@”localhost” identified by "tarena"; //修改登陆密码 8 Query OK, 0 rows affected (0.00 sec) 9 mysql>exit
5）使用修改后的密码登录
1 [root@host50 ~]# mysql -uroot -ptarena //登录 2 Welcome to the MySQL monitor. Commands end with ; or \g. 3 Your MySQL connection id is 15 4 Server version: 5.7.17 MySQL Community Server (GPL) 5 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 6 Oracle is a registered trademark of Oracle Corporation and/or its 7 affiliates. Other names may be trademarks of their respective 8 owners. 9 mysql> show databases; //查看数据库 10 +--------------------+ 11 | Database | 12 +--------------------+ 13 | information_schema | 14 | mysql | 15 | performance_schema | 16 | sys | 17 +--------------------+ 18 4 rows in set (0.00 sec) 19 mysql>
mysql -u root -p Enter Password > '密码' 错误：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 或者：错误：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
解决办法 修改my.cnf配置文件 在[mysqld]下添加skip-grant-tables，保存。 重启mysql： 1、systemctl stop mysql
2、systemctl start mysql 进入mysql，登录 mysql -u root -p 不用输入密码，直接回车（出现Enter Password 也一样直接回车，即可登陆成功） 输入use mysql，修改root的密码： update user set authentication_string=password('新密码') where user='root'; flush privileges; 退出： quit;
修改my.cnf配置文件 注释或删除skip-grant-tables。
再次重启mysql：
1、net stop mysql
2、net start mysql
二、数据库基本管理
步骤一：使用mysql命令连接数据库
连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。
1 [root@dbsvr1 ~]# mysql -uroot -p123456 //紧挨着选项，不要空格 2 mysql: [Warning] Using a password on the command line interface can be insecure. 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 16 5 Server version: 5.7.17 MySQL Community Server (GPL) 6 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 7 Oracle is a registered trademark of Oracle Corporation and/or its 8 affiliates. Other names may be trademarks of their respective 9 owners. 10 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 11 mysql> exit //退出已登录的mysql> 环境 12 Bye
默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；
1 [root@dbsvr1 ~]# mysql -h 127.0.0.1 –u root –p 2 Enter password: 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 17 5 Server version: 5.7.17 MySQL Community Server (GPL) 6 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 7 Oracle is a registered trademark of Oracle Corporation and/or its 8 affiliates. Other names may be trademarks of their respective 9 owners. 10 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 11 mysql> exit //退出已登录的mysql环境 12 Bye
步骤二：练习查看/删除/创建库的相关操作
以root用户登入“mysql> ”环境后，可以执行各种MySQL指令、SQL指令。基本的用法事项如下：
- 操作指令不区分大小写（库名/表名、密码、变量值等除外）。
- 每条SQL指令以 ; 结束或分隔。
- 不支持 Tab 键自动补齐。
- \c 可废弃当前编写错的操作指令。
1）查看自带的库
1 mysql> show databases; //查看现有的库 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | //信息概要库 6 | mysql | //授权库 7 | performance_schema | //性能结构库 8 | sys | //系统元数据库 9 +--------------------+ 10 4 rows in set (0.15 sec)
2）切换/使用指定的库
1 mysql> use sys; //切换到sys库 2 Database changed 3 mysql> select database(); //确认当前所在的库 4 +------------+ 5 | DATABASE() | 6 +------------+ 7 | sys | 8 +------------+ 9 1 row in set (0.00 sec)
切换到mysql库：
1 mysql> use mysql; //切换到mysql库 2 Reading table information for completion of table and column names 3 You can turn off this feature to get a quicker startup with -A 4 Database changed 5 mysql> select database(); //确认当前所在的库 6 +------------+ 7 | DATABASE() | 8 +------------+ 9 | mysql | 10 +------------+ 11 1 row in set (0.00 sec) 12 5 rows in set (0.00 sec)
3）新建名为newdb的库，确认结果：
1 mysql> create database newdb; //新建名为newdb的库 2 Query OK, 1 row affected (0.00 sec) 3 mysql> show databases; 4 +--------------------+ 5 | Database | 6 +--------------------+ 7 | information_schema | 8 | mydb | //新建的mydb库 9 | mysql | 10 | newdb | //新建的newdb库 11 | performance_schema | 12 | sys | 13 +--------------------+ 14 6 rows in set (0.00 sec)
4）删除指定的库
1 mysql> drop database newdb; //删除名为newdb的库 2 Query OK, 0 rows affected (0.01 sec) 3 mysql> show databases; //确认删除结果，已无newdb库 4 +--------------------+ 5 | Database | 6 +--------------------+ 7 | information_schema | 8 | mydb | 9 | mysql | 10 | performance_schema | 11 | sys | 12 +--------------------+ 13 5 rows in set (0.00 sec)
步骤三：练习查看/删除/创建表的相关操作
1）查看指定的库里有哪些表
查看mysql库里有哪些表：
1 mysql> use mysql; 2 Reading table information for completion of table and column names 3 You can turn off this feature to get a quicker startup with -A 4 Database changed 5 mysql> show tables; 6 +---------------------------+ 7 | Tables_in_mysql | 8 +---------------------------+ 9 | columns_priv | 10 | db | 11 | engine_cost | 12 | event | 13 | func | 14 | general_log | 15 | gtid_executed | 16 | help_category | 17 | help_keyword | 18 | help_relation | 19 | help_topic | 20 | innodb_index_stats | 21 | innodb_table_stats | 22 | ndb_binlog_index | 23 | plugin | 24 | proc | 25 | procs_priv | 26 | proxies_priv | 27 | server_cost | 28 | servers | 29 | slave_master_info | 30 | slave_relay_log_info | 31 | slave_worker_info | 32 | slow_log | 33 | tables_priv | 34 | time_zone | 35 | time_zone_leap_second | 36 | time_zone_name | 37 | time_zone_transition | 38 | time_zone_transition_type | 39 | user | //存放数据库用户的表 40 +---------------------------+ 41 31 rows in set (0.00 sec)
2）查看指定表的字段结构
当前库为mysql，查看columns_priv表的结构，以列表形式展现：
1 mysql> desc columns_priv\G //查看表结构，以列表形式展现，末尾不用分号 2 *************************** 1. row *************************** 3 Field: Host 4 Type: char(60) 5 Null: NO 6 Key: PRI 7 Default: 8 Extra: 9 *************************** 2. row *************************** 10 Field: Db 11 Type: char(64) 12 Null: NO 13 Key: PRI 14 Default: 15 Extra: 16 *************************** 3. row *************************** 17 Field: User 18 Type: char(32) 19 Null: NO 20 Key: PRI 21 Default: 22 Extra: 23 *************************** 4. row *************************** 24 Field: Table_name 25 Type: char(64) 26 Null: NO 27 Key: PRI 28 Default: 29 Extra: 30 *************************** 5. row *************************** 31 Field: Column_name 32 Type: char(64) 33 Null: NO 34 Key: PRI 35 Default: 36 Extra: 37 *************************** 6. row *************************** 38 Field: Timestamp 39 Type: timestamp 40 Null: NO 41 Key: 42 Default: CURRENT_TIMESTAMP 43 Extra: on update CURRENT_TIMESTAMP 44 *************************** 7. row *************************** 45 Field: Column_priv 46 Type: set('Select','Insert','Update','References') 47 Null: NO 48 Key: 49 Default: 50 Extra: 51 7 rows in set (0.01 sec)
查看columns_priv表的结构，以表格形式展现：
1 mysql> desc columns_priv; //查看表结构，以表格形式展现末尾需要有分号 2 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 5 | Host | char(60) | NO | PRI | | | 6 | Db | char(64) | NO | PRI | | | 7 | User | char(32) | NO | PRI | | | 8 | Table_name | char(64) | NO | PRI | | | 9 | Column_name | char(64) | NO | PRI | | | 10 | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 11 | Column_priv | set('Select','Insert','Update','References') | NO | | | | 12 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 13 7 rows in set (0.00 sec)
当引用非当前库中的表时，可以用“库名.表名”的形式。比如，切换为mysql库再执行“desc columns_priv;”，与以下操作的效果是相同的：
1 mysql> desc mysql.columns_priv; 2 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 5 | Host | char(60) | NO | PRI | | | 6 | Db | char(64) | NO | PRI | | | 7 | User | char(16) | NO | PRI | | | 8 | Table_name | char(64) | NO | PRI | | | 9 | Column_name | char(64) | NO | PRI | | | 10 | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 11 | Column_priv | set('Select','Insert','Update','References') | NO | | | | 12 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 13 7 rows in set (0.00 sec)
3）在test库中创建一个名为pwlist的表
包括name、password两列，其中name列作为主键。两个字段值均不允许为空，其中密码列赋予默认空值，相关操作如下所述。
切换到mydb库：
1 mysql> use mydb; 2 Database changed
新建pwlist表：
1 mysql> create table pwlist( 2 -> name char(16) not null, 3 -> password char(48)default '', 4 -> primary key(name) 5 -> ); 6 Query OK, 0 rows affected (0.38 sec)
确认新创建的表：
1 mysql> show tables; 2 +----------------+ 3 | Tables_in_mydb | 4 +----------------+ 5 | pwlist | //新建的pwlist表 6 +----------------+ 7 1 rows in set (0.01 sec)
查看pwlist表的字段结构：
1 mysql> desc pwlist; 2 +----------+----------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +----------+----------+------+-----+---------+-------+ 5 | name | char(16) | NO | PRI | NULL | | 6 | password | char(48) | YES | | | | 7 +----------+----------+------+-----+---------+-------+ 8 2 rows in set (0.01 sec)
4）删除指定的表
删除当前库中的pwlist表：
1 mysql> drop table pwlist; 2 Query OK, 0 rows affected (0.01 sec)
确认删除结果：
1 mysql> show tables; 2 Empty set (0.00 sec)
5）在mydb库中创建一个学员表
表格结构及数据内容如表-1所示。
在MySQL表内存储中文数据时，需要更改字符集（默认为latin1不支持中文），以便MySQL支持存储中文数据记录；比如，可以在创建库或表的时候，手动添加“DEFAULT CHARSET=utf8”来更改字符集。
根据上述表格结构，创建支持中文的student表：
1 mysql> CREATE TABLE mydb.student( 2 -> 学号 char(9) NOT NULL, 3 -> 姓名 varchar(4) NOT NULL, 4 -> 性别 enum('男','女') NOT NULL, 5 -> 手机号 char(11) DEFAULT '', 6 -> 通信地址 varchar(64), 7 -> PRIMARY KEY(学号) 8 -> ) DEFAULT CHARSET=utf8; //手工指定字符集，采用utf8 9 Query OK, 0 rows affected (0.31sec)
查看student表的字段结构：
1 mysql> DESC mydb.student; 2 +--------------+-------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +--------------+-------------------+------+-----+---------+-------+ 5 | 学号 | char(9) | NO | PRI | NULL | | 6 | 姓名 | varchar(4) | NO | | NULL | | 7 | 性别 | enum('男','女') | NO | | NULL | | 8 | 手机号 | char(11) | YES | | | | 9 | 通信地址 | varchar(64) | YES | | NULL | | 10 +--------------+-------------------+------+-----+---------+-------+ 11 5 rows in set (0.00 sec)
查看student表的实际创建指令：
1 mysql> SHOW CREATE TABLE mydb.student; 2 +---------+----------------------------------+ 3 |Table |Create Table | 4 +---------+----------------------------------+ 5 | student | CREATE TABLE `student` ( 6 `学号` char(9) NOT NULL, 7 `姓名` varchar(4) NOT NULL, 8 `性别` enum('男','女') NOT NULL, 9 `手机号` char(11) DEFAULT '', 10 `通信地址` varchar(64) DEFAULT NULL, 11 PRIMARY KEY (`学号`) 12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 13 +---------+----------------------------------+ 14 1 row in set (0.00 sec)
注意：若要修改MySQL服务的默认字符集，可以更改服务器的my.cnf配置文件，添加character_set_server=utf8 配置，然后重启数据库服务。
1 [root@dbsvr1 ~]# vim /etc/my.cnf //修改运行服务配置 2 [mysqld] 3 .. .. 4 character_set_server=utf8 5 [root@dbsvr1 ~]# systemctl restart mysqld //重启服务 6 .. .. 7 [root@dbsvr1 ~]# mysql –u root -p 8 Enter password: 9 .. .. 10 mysql> SHOW VARIABLES LIKE 'character%'; //确认更改结果 11 +--------------------------+----------------------------+ 12 | Variable_name | Value | 13 +--------------------------+----------------------------+ 14 | character_set_client | utf8 | 15 | character_set_connection | utf8 | 16 | character_set_database | utf8 | 17 | character_set_filesystem | binary | 18 | character_set_results | utf8 | 19 | character_set_server | utf8 | 20 | character_set_system | utf8 | 21 | character_sets_dir | /usr/share/mysql/charsets/ | 22 +--------------------------+----------------------------+ 23 8 rows in set (0.03 sec)
三、字符类型
步骤一：创建a3表
1）新建db1库，并切换到db1库
1 mysql> CREATE DATABASE db1; 2 Query OK, 1 row affected (0.00 sec) 3 mysql> USE db1; 4 Database changed
2）新建t3表
1 mysql> CREATE TABLE db1.t3 ( 2 -> name char(5) , 3 -> mail varchar(10), 4 -> homedir varchar(50) 5 -> ); 6 Query OK, 0 rows affected (0.61sec)
3) 查看a3表结构
1 mysql> DESC db1.a3; 2 +----------+----------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +----------+----------------------+------+-----+---------+-------+ 5 | name | char(5) | YES | | NULL | | 6 | mail | varchar(10) | YES | | NULL | | 7 | homedir | varchar(50) | YES | | NULL | | 8 +----------+----------------------+------+-----+---------+-------+ 9 3 rows in set (0.00 sec)
四、数值类型
步骤一：创建t2表
1）切换到db1库
1 mysql> USE db1; 2 Database changed
2）新建t2表
1 mysql> create table db1.t2( 2 -> stu_num int, 3 -> name char(5), 4 -> age tinyint, 5 -> pay float, 6 -> money float(5,2) 7 -> ); 8 Query OK, 0 rows affected (0.03 sec)
3) 查看t2表结构
1 mysql> desc db1.t2; 2 +---------+------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +---------+------------+------+-----+---------+-------+ 5 | stu_num | int(11) | YES | | NULL | | 6 | name | char(5) | YES | | NULL | | 7 | age | tinyint(4) | YES | | NULL | | 8 | pay | float | YES | | NULL | | 9 | money | float(5,2) | YES | | NULL | | 10 +---------+------------+------+-----+---------+-------+ 11 5 rows in set (0.00 sec) 12 mysql>
五、日期时间类型
步骤一：时间函数的使用
1）使用now()查看当前的日期和时间
1 mysql> SELECT now(); 2 +---------------------+ 3 | now() | 4 +---------------------+ 5 | 2019-07-03 05:00:15 | 6 +---------------------+ 7 1 row in set (0.00 sec) 8 mysql>
2）使用curdate()获得当前的日期
1 mysql> SELECT curdate(); 2 +------------+ 3 | curdate() | 4 +------------+ 5 | 2019-07-03 | 6 1 row in set (0.00 sec) 7 mysql>
3）使用curtime()获得当前的时间
1 mysql> SELECT curtime(); 2 +-----------+ 3 | curtime() | 4 +-----------+ 5 | 04:04:55 | 6 +-----------+ 7 1 row in set (0.00 sec)
4）分别获取当前日期时间中的年份、月份、日
1 mysql> SELECT year(now()) , month(now()) , day(now()); 2 +-------------+--------------+------------+ 3 | year(now()) | month(now()) | day(now()) | 4 +-------------+--------------+------------+ 5 | 2019 | 7 | 3 | 6 +-------------+--------------+------------+ 7 1 row in set (0.00 sec) 8 mysql>
5）获取系统日期
1 mysql> select date(now()); 2 +-------------+ 3 | date(now()) | 4 +-------------+ 5 | 2019-07-03 | 6 +-------------+ 7 1 row in set (0.00 sec)1 row in set (0.00 sec) 8 Mysql>
步骤二：创建t4表
1）建表
1 mysql> create table db1.t4( 2 -> name char(10), 3 -> your_start year, 4 -> up_time time, 5 -> birthday date, 6 -> party datetime 7 -> ); 8 Query OK, 0 rows affected (0.04 sec) 9 mysql>
2) 查看表结构
1 mysql> desc db1.t4; 2 +------------+----------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +------------+----------+------+-----+---------+-------+ 5 | name | char(10) | YES | | NULL | | 6 | your_start | year(4) | YES | | NULL | | 7 | up_time | time | YES | | NULL | | 8 | birthday | date | YES | | NULL | | 9 | party | datetime | YES | | NULL | | 10 +------------+----------+------+-----+---------+-------+ 11 5 rows in set (0.00 sec)
3）插入记录
1 mysql> insert into db1.t4 values("bob",1990,083000,20191120,2019082820000); 2 Query OK, 1 row affected, 1 warning (0.01 sec) 3 mysql> insert into db1.t4 values("tom",1991,090000,20191120,now()); 4 Query OK, 1 row affected (0.02 sec)
4）查看表记录
1 mysql> select * from db1.t4; 2 +-----+------------+----------+------------+---------------------+ 3 | name | your_start | up_time | birthday | party | 4 +------+------------+----------+------------+---------------------+ 5 | bob | 1990 | 08:30:00 | 2019-11-20 | 0000-00-00 00:00:00 | 6 | tom | 1991 | 09:00:00 | 2019-11-20 | 2019-07-03 05:12:41 | 7 +------+------------+----------+------------+---------------------+ 8 2 rows in set (0.00 sec)
六、枚举类型
步骤一：创建t5表
1）建表
1 mysql> create table db1.t5 ( 2 -> name char(5), 3 -> likes set("eat","game","film","music"), 4 -> sex enum("boy","girl","no") 5 -> ); 6 Query OK, 0 rows affected (0.04 sec)
2）查看表结构
1 mysql> desc db1.t5; 2 +-------+----------------------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------+----------------------------------+------+-----+---------+-------+ 5 | name | char(5) | YES | | NULL | | 6 | likes | set('eat','game','film','music') | YES | | NULL | | 7 | sex | enum('boy','girl','no') | YES | | NULL | | 8 +-------+----------------------------------+------+-----+---------+-------+ 9 3 rows in set (0.00 sec)
3）插入表记录
1 mysql> insert into db1.t5 values ("bob","eat,film,game","boy"); 2 Query OK, 1 row affected (0.03 sec)
4）查看表记录
1 mysql> select * from db1.t5; 2 +------+---------------+------+ 3 | name | likes | sex | 4 +------+---------------+------+ 5 | bob | eat,game,film | boy | 6 +------+---------------+------+ 7 1 rows in set (0.00 sec)
浙公网安备 33010602011771号