MySQL_01_数据类型测试

连接服务器

打开服务器命令: mysql -u root -p
打开日志:\T 路径/文件名如果没有该文件名可以自动创建
关闭日志:\t

数据库相关命令

创建数据库

基本命令

CREATE DATABASE DatabaseName;

创建不同与默认的编码方式的数据库

CREATE DATABASE IF NOT EXISTS DatabaseName DEFAULT CHARACTER SET = 'GBK' 

默认编码方式是'GBK'
我们可以看到编码变成了GBK:

+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                   |
+----------+---------------------------------------------------------------------------------------------------+
| ZZUGSH   | CREATE DATABASE `ZZUGSH` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改已有数据库的编码方式

ALTER DATABASE ZZUGSH DEFAULT CHARACTER SET 'UTF8';

我们可以看到编码已经被修改:

mysql> SHOW CREATE DATABASE ZZUGSH;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                    |
+----------+----------------------------------------------------------------------------------------------------+
| ZZUGSH   | CREATE DATABASE `ZZUGSH` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+

数据库常用命令

查看上一步产生的警告
SHOW WARNINGS;
查看已有数据库
SHOW DATABASES;

输出:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| guosihan           |
| information_schema |
| KITGSH             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
查看已有数据库的定义
SHOW CREATE DATABASE ZZUGSH;

输出:

mysql> SHOW CREATE DATABASE ZZUGSH;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                    |
+----------+----------------------------------------------------------------------------------------------------+
| ZZUGSH   | CREATE DATABASE `ZZUGSH` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+
打开数据库
USE db_name

输出:

mysql> USE ZZUGSH;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| zzugsh     |
+------------+
1 row in set (0.00 sec)
展示现在所有数据库名称
SHOW DATABASE;

输出所有数据库:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| guosihan           |
| information_schema |
| KITGSH             |
| mysql              |
| performance_schema |
| sys                |
| ZZUGSH             |
+--------------------+

删除指定数据表

mysql> DROP DATABASE IF EXISTS ZZUGSH;
Query OK, 0 rows affected, 1 warning (0.00 sec)

数据表的相关命令

MySQL中的数据结构

  • 整数类型
    • TINYINT
    • SMALLINT
    • MEDIUMINT
    • INT
    • BIGINT
    • BOOL,BOOLEN:TINYINT(1),0 - TRUE,其他 - FALSE
  • 浮点类型
    • FLOAT[(M,D)]:M是数字总位数,D是小数后面的位数总数
  • 字符串类型
    • CHAR(M):定长字符串,M个字节
    • VARCHAR(M)
    • TINYTEXT
    • TEXT:可以输入长篇字符串
    • MEDIUMTEXT
    • LONGTEXT
    • ENUM(‘value1’,‘value2’,…):当值可以列举的时候,从列举之中选择一个
    • SET(‘value1’,‘value2’,…),可以选择多个值
  • 日期时间类型
    • TIME
    • DATE
    • DATETIME
    • TIMESTAMP
    • TEAR

不用具体的硬背,这种方式可以临时查询数据类型的具体信息。

mysql> help INT
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html

MySQL储存引擎

什么是储存引擎

存储引擎就是储存时指表的类型。
数据库的存储类型决定了表在计算机中的存储方式。用户可以根据不同的存储方式、是否进行事务处理等来选择合适的存储引擎。

关于引擎的相关代码

展示所有的引擎

mysql> show engines
    -> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show engines\G;
*************************** 1. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

查看显示支持的存储引擎信息:SHOW VARIABLES LIKE 'have%
查看默认的存储引擎:SHOW VARIABLES LIKE 'storage_ engine

mysql> show variables like 'storage engine';
Empty set (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
MySQL常用存储引擎及特点
  • InnoDB
    • 优点: 提供了良好的事务管理 崩溃修复能力和并发控制。
    • 缺点: 读写效率差 占用效率空间数据相对比较大。
    • 适用场景: InnoDB存储引擎特别适合处理多重并发的更新请求。
  • MyISAM
    • MyISAM存储引擎的优势在于占用空间小,处理速度快,缺点是不支持事务和并发性。
  • Memory
    • 受大小限制,因其存在内存中的特性,这类表的处理速度非常快。但是其数据容易丢失,生命周期短。
    • 场景: 目标数据只是临时需要,在其生命周期中必须立即可用。

数据表的具体相关命令

建立数据表相关代码

-- 创建一个数据表

CREATE DATABASE IF NOT EXISTS `maizi` DEFAULT CHARACTER SET 'UTF8';

USE `maizi`;
-- 创建学员 user表
-- 编号 id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birthday
-- 薪水 salary
-- 电话 tel
-- 是否结婚 married
-- 当有中文的时候 需要临时转换客户端的编码方式
-- SET NAMES GBK;
-- 字段注释 通过comment 注释内容 给字段添加注释内容

CREATE TABLE IF NOT EXISTS `user`(
		
	id SMALLINT,
	username VARCHAR(20),
	age TINYINT,
	sex ENUM('男','女','保密'),
	email VARCHAR(50),
	addr VARCHAR(200),
	birth YEAR,
	salary FLOAT,
	tel INT,
	married TINYINT(1) COMMENT '0代表未结婚,非0代表结婚'

)ENGINE=INNODB CHARSET=UTF8;

-- 现在要创建一个课程表 course
-- 编号 cid
-- 课程名称 courseName
-- 课程描述 courseDesc

CREATE TABLE IF NOT EXISTS course(

	cid TINYINT,
	courseName VARCHAR(50),
	courseDesc VARCHAR(200)

);

-- 创建新闻分类表cms_cate
-- 编号 分类名称 分类描述

CREATE TABLE IF NOT EXISTS cms_cate(
	
	id TINYINT,
	cateNAME VARCHAR(50),
	cateDesc VARCHAR(200)

)ENGINE=MyISAM CHARSET=UTF8;

-- 新闻表 cms_news
-- 编号 新闻标题 新闻内容 新闻发布时间 点击量 是否置顶 新闻所属分类 发布人

CREATE TABLE IF NOT EXISTS cms_news(
	
	id INT,
	title VARCHAR(50),
	content TEXT,
	pubtime INT,
	clickNum INT,
	istop TINYINT(1) COMMENT '0代表不置顶,1代表置顶'
	-- cID TINYINT,
	-- aID 
);

查看指定表结构

mysql> DESC cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubtime  | int         | YES  |     | NULL    |       |
| clickNum | int         | YES  |     | NULL    |       |
| istop    | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> DESCRIBE cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubtime  | int         | YES  |     | NULL    |       |
| clickNum | int         | YES  |     | NULL    |       |
| istop    | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubtime  | int         | YES  |     | NULL    |       |
| clickNum | int         | YES  |     | NULL    |       |
| istop    | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

表中插入记录

向表中插入记录

INSERT tbl_name VALUE|VALUES(值,...);

查询表中所有记录

SELECT * FROM tbl_name;
-- 举例
SELECT * FROM test;

测试各种数据类型

测试整型

所有整型类型测试:

-- 创建数据表 测试整型
```sql
mysql> CREATE TABLE test1(
    -> 
    -> num1 TINYINT,
    -> num2 SMALLINT,
    -> num3 MEDIUMINT,
    -> num4 INT,
    -> num5 BIGINT
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| user            |
+-----------------+
5 rows in set (0.00 sec)

mysql> DESC test1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| num1  | tinyint   | YES  |     | NULL    |       |
| num2  | smallint  | YES  |     | NULL    |       |
| num3  | mediumint | YES  |     | NULL    |       |
| num4  | int       | YES  |     | NULL    |       |
| num5  | bigint    | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> ? BIGINT
Name: 'BIGINT'
Description:
BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to
9223372036854775807. The unsigned range is 0 to 18446744073709551615.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

URL: https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html

mysql> INSERT test1 VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+--------+----------+-------------+----------------------+
| num1 | num2   | num3     | num4        | num5                 |
+------+--------+----------+-------------+----------------------+
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+------+--------+----------+-------------+----------------------+
1 row in set (0.00 sec)
测试添加无符号的数据类型
mysql> CREATE TABLE test2(
    -> 
    -> num1 TINYINT UNSIGNED,
    -> num2 TINYINT
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test2;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| num1  | tinyint unsigned | YES  |     | NULL    |       |
| num2  | tinyint          | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT test2 VALUES(0,-2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test2;
+------+------+
| num1 | num2 |
+------+------+
|    0 |   -2 |
+------+------+
1 row in set (0.00 sec)

mysql> INSERT test2 VALUES(-2,-2);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> SELECT * FROM test2;
+------+------+
| num1 | num2 |
+------+------+
|    0 |   -2 |
+------+------+
1 row in set (0.00 sec)

零填充ZEROFILL
mysql> INSERT test4 VALUES(1,1,1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test4;
+------+-------+----------+------------+----------------------+
| num1 | num2  | num3     | num4       | num5                 |
+------+-------+----------+------------+----------------------+
|  001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
+------+-------+----------+------------+----------------------+
1 row in set (0.00 sec)

mysql> INSERT test4 VALUES(123,1,1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test4;
+------+-------+----------+------------+----------------------+
| num1 | num2  | num3     | num4       | num5                 |
+------+-------+----------+------------+----------------------+
|  001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
|  123 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
+------+-------+----------+------------+----------------------+
2 rows in set (0.00 sec)
-- 用零填充默认用零填充,使得位数达到最大值,作用是显示美观,长度一致
测试浮点型
mysql> CREATE TABLE test5(
    -> 
    -> num1 FLOAT(6,2),
    -> num2 DOUBLE(6,2),
    -> num3 DECIMAL(6,2)
    -> 
    -> );
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> DESC test5;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num1  | float(6,2)   | YES  |     | NULL    |       |
| num2  | double(6,2)  | YES  |     | NULL    |       |
| num3  | decimal(6,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT test5 VALUES(3.1415,3.1415,3.1415);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num3' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT test5 VALUES(3.2495,3.2495,3.2495);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM test5;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
| 3.25 | 3.25 | 3.25 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test5 WHERE num1=3.14;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test5 WHERE num2=3.14;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test5 WHERE num3=3.14;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.01 sec)

mysql> SELECT * FROM test5 WHERE num1='3.14';
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test5 WHERE num3='3.14';
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)
-- DECIMAL的精度更高
测试字符串

CHAR定长字符串,占用空间大,速度快。
VARCHAR变长字符串,占用空间小,速度慢。

mysql> CREATE TABLE test6(
    -> 
    -> str1 CHAR(5),
    -> str2 VARCHAR(5)
    -> 
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| user            |
+-----------------+
10 rows in set (0.00 sec)

mysql> DESC test6;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| str1  | char(5)    | YES  |     | NULL    |       |
| str2  | varchar(5) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT test6 VALUES('1','1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test6;
+------+------+
| str1 | str2 |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> INSERT test6 VALUES('1  ','1'  );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test6;
+------+------+
| str1 | str2 |
+------+------+
| 1    | 1    |
| 1    | 1    |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT CONCAT(str1,'-'),CONCAT(str2,'-') FROM test6;
+------------------+------------------+
| CONCAT(str1,'-') | CONCAT(str2,'-') |
+------------------+------------------+
| 1-               | 1-               |
| 1-               | 1-               |
+------------------+------------------+
2 rows in set (0.00 sec)

mysql> INSERT test6 VALUES('1  ','1  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT(str1,'-'),CONCAT(str2,'-') FROM test6;
+------------------+------------------+
| CONCAT(str1,'-') | CONCAT(str2,'-') |
+------------------+------------------+
| 1-               | 1-               |
| 1-               | 1-               |
| 1-               | 1  -             |
+------------------+------------------+
3 rows in set (0.00 sec)

mysql> INSERT test6 VALUES('  1','  1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('-',str1),CONCAT('-',str2) FROM test6;
+------------------+------------------+
| CONCAT('-',str1) | CONCAT('-',str2) |
+------------------+------------------+
| -1               | -1               |
| -1               | -1               |
| -1               | -1               |
| -  1             | -  1             |
+------------------+------------------+
4 rows in set (0.00 sec)

mysql> INSERT test6 VALUES('你','好');
ERROR 1406 (22001): Data too long for column 'str1' at row 1
mysql> INSERT test6 VALUES('啊','啊');
ERROR 1406 (22001): Data too long for column 'str1' at row 1
mysql> SELECT LENGTH('啊');
+--------------+
| LENGTH('��?)   |
+--------------+
|            3 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT CHAR_LENGTH('');
+-------------------+
| CHAR_LENGTH('��?)   |
+-------------------+
|                 2 |
+-------------------+
1 row in set, 1 warning (0.01 sec)

mysql> DESC test6;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| str1  | char(5)    | YES  |     | NULL    |       |
| str2  | varchar(5) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SET NAMES UTF8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT test6 VALUES('啊','啊');
Query OK, 1 row affected (0.00 sec)

mysql> DESC test6;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| str1  | char(5)    | YES  |     | NULL    |       |
| str2  | varchar(5) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test6;
+------+------+
| str1 | str2 |
+------+------+
| 1    | 1    |
| 1    | 1    |
| 1    | 1    |
|   1  |   1  |
|||
+------+------+
5 rows in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('啊');
+--------------------+
| CHAR_LENGTH('啊')  |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('啊');
+--------------------+
| CHAR_LENGTH('啊')  |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('啊');
+---------------+
| LENGTH('啊')  |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
测试文本类型
mysql> CREATE TABLE test7(
    -> 
    -> str1 TEXT
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES
    -> ;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| user            |
+-----------------+
11 rows in set (0.00 sec)

mysql> INSERT test7 VALUES('ニューラルネットワーク    ー研究の歴史と本書の構成')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test7;
+---------------------------------------------------------------------------+
| str1                                                                      |
+---------------------------------------------------------------------------+
| ニューラルネットワーク    ー研究の歴史と本書の構成                        |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
测试枚举类型
mysql> CREATE TABLE test8(
    -> 
    -> sex ENUM('男','女','保密')
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT test8 VALUES('男')
    -> INSERT test8 VALUES('女')
    -> INSERT test8 VALUES('保密')
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT test8 VALUES('')
INSERT test8 VALUES('保密')' at line 2
mysql> INSERT test8 VALUES('男');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT test8 VALUES('女');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT test8 VALUES('保密');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test8;
+--------+
| sex    |
+--------+
||
||
| 保密   |
+--------+
3 rows in set (0.00 sec)

mysql> INSERT test8 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test8;
+--------+
| sex    |
+--------+
||
||
| 保密   |
||
+--------+
4 rows in set (0.00 sec)

mysql> INSERT test8 VALUES(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test8;
+--------+
| sex    |
+--------+
||
||
| 保密   |
||
| NULL   |
+--------+
5 rows in set (0.00 sec)
测试集合类型
mysql> CREATE TABLE test9(
    -> 
    -> Fav SET('A','B','C','D')
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT test9 VALUES('A,C,D');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT test9 VALUES('D,B,A');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test9;
+-------+
| Fav   |
+-------+
| A,C,D |
| A,B,D |
+-------+
2 rows in set (0.00 sec)

mysql> INSERT test9 VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test9;
+-------+
| Fav   |
+-------+
| A,C,D |
| A,B,D |
| A,B   |
+-------+
3 rows in set (0.00 sec)

mysql> INSERT test9 VALUES(10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test9;
+-------+
| Fav   |
+-------+
| A,C,D |
| A,B,D |
| A,B   |
| B,D   |
+-------+
4 rows in set (0.00 sec)

mysql> INSERT test9 VALUES(15);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test9;
+---------+
| Fav     |
+---------+
| A,C,D   |
| A,B,D   |
| A,B     |
| B,D     |
| A,B,C,D |
+---------+
5 rows in set (0.00 sec)

mysql> INSERT test9 VALUES(11);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test9;
+---------+
| Fav     |
+---------+
| A,C,D   |
| A,B,D   |
| A,B     |
| B,D     |
| A,B,C,D |
| A,B,D   |
+---------+
6 rows in set (0.00 sec)
测试YEAR
mysql> CREATE TABLE test10(
    -> 
    -> birth YEAR
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT test10 VALUES(1901);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
|  1901 |
+-------+
1 row in set (0.00 sec)

mysql> INSERT test10 VALUES(2155);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
|  1901 |
|  2155 |
+-------+
2 rows in set (0.00 sec)

mysql> INSERT test10 VALUES(12);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
|  1901 |
|  2155 |
|  2012 |
+-------+
3 rows in set (0.00 sec)

mysql> INSERT test10 VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
|  1901 |
|  2155 |
|  2012 |
|  0000 |
+-------+
4 rows in set (0.00 sec)

mysql> INSERT test10 VALUES('0');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
|  1901 |
|  2155 |
|  2012 |
|  0000 |
|  2000 |
+-------+
5 rows in set (0.00 sec)
-- 年份不能插入超过范围的数字
-- 0-69,输入这个范围内的数字或者字符串,会转化为2000-2099年之间,70-99会自动转化成90年代的。
-- 插入一个数字0和一个字符串0的时候,结果是截然不同的。
测试TIME时间
mysql> CREATE TABLE test11(
    -> 
    -> test TIME
    -> 
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT test10 VALUES('1 12:12:12')
    -> 
    -> ;
ERROR 1265 (01000): Data truncated for column 'birth' at row 1
mysql> INSERT test11 VALUES('1 12:12:12')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test11;
+----------+
| test     |
+----------+
| 36:12:12 |
+----------+
1 row in set (0.00 sec)

mysql> INSERT test11 VALUES('11:11')
    ->-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';' at line 2
mysql> INSERT test11 VALUES('11:11');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test11;
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
+----------+
2 rows in set (0.00 sec)

mysql> INSERT test11 VALUES('1234');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test11;
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
| 00:12:34 |
+----------+
3 rows in set (0.00 sec)

mysql> INSERT test11 VALUES('12');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test11;
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
| 00:12:34 |
| 00:00:12 |
+----------+
4 rows in set (0.00 sec)

mysql> INSERT test11 VALUES('0');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test11;
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
| 00:12:34 |
| 00:00:12 |
| 00:00:00 |
+----------+
5 rows in set (0.00 sec)

mysql> INSERT test11 VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test11;
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
| 00:12:34 |
| 00:00:12 |
| 00:00:00 |
| 00:00:00 |
+----------+
6 rows in set (0.00 sec)
测试日期时间DATE
mysql> CREATE TABLE test12(
    -> 
    -> test DATE
    -> 
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT test11 VALUES('12-6-7')
    -> ;
ERROR 1292 (22007): Incorrect time value: '12-6-7' for column 'test' at row 1
mysql> INSERT test12 VALUES('12-6-7')
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test12;
+------------+
| test       |
+------------+
| 2012-06-07 |
+------------+
1 row in set (0.00 sec)

mysql> INSERT test12 VALUES('12/6/7')
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test12;
+------------+
| test       |
+------------+
| 2012-06-07 |
| 2012-06-07 |
+------------+
2 rows in set (0.00 sec)

mysql> INSERT test12 VALUES('120607');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test12;
+------------+
| test       |
+------------+
| 2012-06-07 |
| 2012-06-07 |
| 2012-06-07 |
+------------+
3 rows in set (0.00 sec)

mysql> INSERT test12 VALUES('12@6-7');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test12;
+------------+
| test       |
+------------+
| 2012-06-07 |
| 2012-06-07 |
| 2012-06-07 |
| 2012-06-07 |
+------------+
4 rows in set (0.01 sec)

mysql> \t

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值