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