文章目录
数据库简介
数据库就是存储数据的仓库,为了方便数据的存储和管理,将数据按照特定额规律存储在磁盘上,通过数据库管理系统,有效地组织和管理存储在数据库中的数据。
数据库系统和数据不是一个概念,数据库系统(DBS),比数据库大很多,由数据库、数据库管理系统、应用开打工具构成。
数据库管理系统(DataBase Management System,简称DBMS),用来定义数据、管理和维护数据的软件,他是数据库系统的一种重要的组成部分。常见的数据库系统:甲骨文Oracle数据库,IBM的DB2,微软的SQL Server、Access,PostgreSql,MySQL。
MySql数据库
MySql是开放源代码的数据库、MySql的跨平台性、开源免费、功能强大使用方便。
MySql5.6的安装即新特性:
安装:Windows版本,MySQL的二进制分发版(.MSI安装文件),MySQL官网下载:https://dev.mysql.com/downloads/mysql/,免安装版(.ZIP压缩文件)
新特性:
- 提高了性能和扩展能力,最多课扩展48个CPU线程,与MySQL5.5相比,性能提升高达230%。
- 改进了INNODB,提升了事务吞吐量和可用性。
- 改进了优化器,缩短了查询执行时间,增强了诊断,以便更好地进行查询调优和调试。
- 改进了复制,提高了性能、可用性和数据完整性
- 改进了PERFORMANCE_SCHEMA,提供了更好的监测
- 对INNODB进行NoSQL访问,完全符合ACID原则的快速键值访问,提高了开发人员灵活性。
- 子查询最佳化,通过优化子查询,可以提高执行效率
- 强化Optimizer Diagnostics功能
- 新增Index Condition Pushdown(ICP)和Batcha Key Access(BKA)功能
- 自我修复复制从表
- 高效能复制从表
- 时间延迟复制
SQL简介
Structured Query Language简称SQL,结构化查询语言,数据库管理系统通过SQL语言来管理数据库中的数据。
SQL语言的组成:
- DDL(Data Defination Language):数据定义语言,主要用于定义数据库、表、视图、索引和触发器等、像DROP、CREATE、ALTER等语句。
- DML(Data Manipulation Language):主要包括对数据的增删改。INSERT插入数据、UPADATE更新数据、DELETE删除数据。
- DQL(Data Query Language):数据检索语句,用来从表中获得数据,确定数据怎样在应用程序中给出,向SELECT查询数据。
- DCL(Data Control Language):数据控制语句,主要控制用户的访问权限。像GRANT、REVOKE、COMMIT、ROLLBACK等语句。
安装与配置
安装目录简介:
bin目录,存储可执行文件,
data目录,存储数据文件,
include目录,存储包含的头文件,
lib目录,存储库文件,
docs目录,文档;
share目录,错误消息和字符集文件;
my.ini文件,MySQL的配置文件,设置字符集,客户端字符集default-character-set= utf8、服务器端字符集character-set-server = utf8
注:安装时可以添加用户,也可以后期命令添加。官网的INSTALLER安装文件是32位,但可以在安装过程中选择安装64位数据库管理系统。安装完后记得要在环境变量中添加MySQL的bin文件目录
查看MySQL是否开启:
- 右键计算机管理,找到服务,找到MySQL,查看是否开启。
- 可以通过在cmd命令提示符窗口输入:mysql -u密码 -p密码,如果成功进入,也说明开启了。如果报错:Can’t connect to MySQ sercer on ‘localhost’,表明服务没开启。
第一次安装完,进入MySQL,输入\s,查询编码方式,发现客户端和连接点编码方式不是utf8,这时候容易出现乱码。exit退出后,在my.ini编辑编码方式,以ANSI方式存储在目录下。
然后以管理员身份运行,在cmd中输入以下命令
C:\Windows\system32>net stop mysql
MySQL 服务正在关闭 .
MySQL 服务已经关闭成功。
C:\Windows\system32>mysqld --remove
Service successfully removed
D:\MySQL\MySQL Server 8.0\bin>mysqld --install
Service successfully installed.
D:\MySQL\MySQL Server 8.0\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
当发现无法启动时,要在bin目录下使用mysqld --console查询错误日志,对照错误日志搜索原因。
补充:上述方法是一个错误的操作,因为前提没有查看错误日志导致上述方法的发生,上述方法会在服务列表增加一个本地服务MySQL,致使MySQL80无法启动,因此需要remove本地服务,MySQL5.6之后都只有网络服务,没有了本地服务。在中间查询如何启动服务时,还因无法登录,用了skip-grant-tables跳过密码阶段,修改密码,当修改密码提示无效时,刷新一下MySQL的权限即可: MySQL>flush privileges。
另外附上mysql配置文件的详细解析:https://blog.csdn.net/lienfeng6/article/details/78140404。
每次修改配置文件都要重新启动mysql
net stop mysql80
net start mysql80
登录/退出MySQL
登录常用参数:
-u (–uesername=name),用户名
-p (–password[=pwd]),密码;可以使用明文显示或密文显示
-h (–host=name),服务器名称,本地的话可以写localhost或者127.0.0.1(不输入默认)
-P (–port=#),端口号(不输入默认)
-D (–database=name),打开指定数据库
–prompt=name,设置命令提示符,只针对当前连接
–delimiter-name,指定分隔符
-V --version,输出本本信息并退出(一般放在明文密码后)
以上后面第二种使用方法,在5.6以后好像不再支持了
退出命令:
exit、quit、\q、Ctrl+c(5.6以后不再支持)
小技巧:在cmd命令提示符窗口输入cls可以清理屏幕历史显示。
修改MySQL命令提示符
- mysql -uroot -proot --prompt 提示符,连接客户端时通过参数指定
- prompt 提示符 连接客户端后,通过prompt命令修改
- \D:完整的日期
- \d:当前数据库
- \h:服务器名称
- \u:当前用户名
以上四个位常用的命令提示符参数,可以在登录的同时修改,也可以登录后修改
登陆后使用:
mysql> prompt \u
PROMPT set to '\u'
rootprompt mysql>
PROMPT set to 'mysql>'
mysql>prompt \h~\u~\D~
2019~prompt \h~\u~\D~\d'
PROMPT set to '\h~\u~\D~\d''
localhost~root~Thu Dec 26 22:55:18 2019~(none)'
当前没有打开数据库,因此显示none
登录时使用:
C:\Windows\system32>mysql -uroot -p --prompt=\h~\u
Enter password: ***
...
localhost~root
注:prompt后面不要加空格
MySQL常用命令
- SELECT VERSION();显示当前版本
- SELECT NOW();显示当前日期时间
- SELECT USER();显示当前用户
默认的SQL语句分隔符时;或者时\g
mysql>SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.18 |
+-----------+
1 row in set (0.01 sec)
mysql>SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2019-12-26 23:03:41 |
+---------------------+
1 row in set (0.00 sec)
MySQL语句的规范
- 关键字与 函数名称全部大写(虽然不缺分大小写)
- 数据库名称、表名称、字段名称等全部小写
- SQL语句必须以分隔符结尾
- SQL语句支持折行操作,只要不把单词、标记或引号字符串分割位两部分,可以在下一行继续写
- 数据库名称、表名称、字段名称等尽量不要使用MySQL的保留字,如果需要使用的时候需要使用反引号(``)将名字括起来。
mysql>SELECT
->
-> NOW()
->
-> ;
+---------------------+
| NOW() |
+---------------------+
| 2019-12-26 23:09:20 |
+---------------------+
1 row in set (0.00 sec)
mysql>SELECT NOW()'
'>
'>
'> ;
'> ;
'> ';
+---------------------+
| ;
;
|
+---------------------+
| 2019-12-26 23:10:37 |
+---------------------+
1 row in set, 1 warning (0.01 sec)
引号要成对出现。
mysql>SELECT NOW()\c #\c取消当前语句
mysql>DELIMITER // #更改分隔符
mysql>SELECT NOW();
-> //
+---------------------+
| NOW() |
+---------------------+
| 2019-12-26 23:12:53 |
+---------------------+
1 row in set (0.00 sec)
登录的时候同样也可以修改DELEMITER
C:\Windows\system32>mysql -uroot -p --delimiter=//
Enter password: ***
...
mysql> SELECT NOW()//
+---------------------+
| NOW() |
+---------------------+
| 2019-12-26 23:15:36 |
+---------------------+
1 row in set (0.00 sec)
可以通过键盘上下键,将之前打的命令调出来。
开启MySQL的日志,可以将命令和输出结果输入到文本中,如下代码:
mysql> DELIMITER ;
mysql> \T D:\mysqldemo\mysql1.txt #开启
Logging to file 'D:\mysqldemo\mysql1.txt'
mysql> \t #结束
Outfile disabled.
数据库操作(DDL)
创建数据库
CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] charaset_name];
{}代表必须出现,|代表选择,[]代表可选的。
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.03 sec)
在磁盘的…\MySQL Server 8.0\data创建了一个文件夹,也可以在这个目录下自己手动创建一个文件夹,但不能包含特殊字符。
mysql> CREATE DATABASE test1;
ERROR 1007 (HY000): Can't create database 'test1'; database exists
mysql> CREATE DATABASE IF NOT EXISTS test1;
Query OK, 1 row affected, 1 warning (0.01 sec)
通过下列语句查看警告信息。
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------+
| Note | 1007 | Can't create database 'test1'; database exists |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)
查看当前服务器下有哪些数据库
SHOW {DATABASES|SCHEMAS};
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.04 sec)
其中information_schema、mysql、performance_schema是不能被删掉的。
查看指定数据库的定义
SHOW CREATE {DATABASE|SCHEMA} db_name;
mysql> SHOW CREATE DATABASE test1
-> ;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
将上述的DATABASE修改成SCHEMA也可以。
mysql> CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
修改指定数据库的编码方式
ALTER {DATABASE|SCHEMA} db_name [DEFALUT] CHARACTER SET [=] charaset_name;
mysql> ALTER DATABASE test2 DEFAULT CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE test2;
+----------+--------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
打开指定的数据库
USE db_name;
mysql> USE test2;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test2 |
+------------+
1 row in set (0.00 sec)
可以通过SELECT {DATABASE()|SCHEMA()};获得当前打开的数据库名称。
删除指定的数据库
DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;
mysql> DROP DATABASE test2;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
MySQL中的数据类型
整数类型
数值类型 | 存储范围 | 字节数 |
---|---|---|
TINYINT | 无符号值:-128到127(- 2 2 2^2 22到 2 7 2^7 27-1 )无符号值:0到255(0到 2 8 2^8 28-1) | 1 |
SMALLINT | 无符号值:-32768到32768(- 2 1 5 2^15 215到 2 1 5 2^15 215-1 ) 无符号值:0到65535(0到 2 1 6 2^16 216-1) | 2 |
MEDIUMINT | 有符号值: -8388608到8388607(- 2 2 5 2^25 225到 2 2 5 2^25 225-1 )无符号值:0到16777215 (0到 2 1 7 2^17 217-1) | 3 |
INT | 有符号值:-2147683648到2147683647(- 2 5 1 2^51 251到 2 5 1 2^51 251-1 ) 无符号值:0到4294967295 (0到 2 5 2 2^52 252-1) | 4 |
BIGINT | 有符号值:-9223372036854775808到9223373036854775807(- 2 6 3 2^63 263到 2 6 3 2^63 263-1 )无符号值:0到18446744073709551615(0到 2 6 4 2^64 264-1) | 8 |
BOOL,BOOLEAN | 等价于TINYINT(1),0位false,其余位true | 1 |
可以通过帮助手册查看:
mysql> help tinyint
Name: 'TINYINT'
Description:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned
range is 0 to 255.
mysql> ? 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.
mysql> \h int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]
...
浮点类型
数值类型 | 存储范围 | 字节数 |
---|---|---|
FLOAT[(M,D)] | 负数取值范围位 -3.402823466E+38 到 -1.175494351E-38 M是数字总位数,D是小数点后面的位数。如果M和D被省略,根据硬件允许的限制来保存值。但精度浮点数精确到大约7位小数位 | 4 |
DOUBLE | -1.7976931348623157E+308 到-2.2250738585072014E-308、0、2.2250738585072014E-308 到 1.7976931348623157E+308 | 8 |
DECIMAL[(M,D)] | 和DOUBLE一样,内部以字符串形式存储数值 | M+2 |
字符串类型
数值类型 | 存储需求 |
---|---|
CHAR(M) | M个字节,0<=M<=255 |
VARCHAR(M) | L+1个字节,其中L<=M且0<=M<=65535 |
TINYTEXT | L+1个字节 其中L< 2 8 2^8 28 |
TEXT | L+2个字节 其中L< 2 1 6 2^16 216 |
MEDIUMTEXT | L+3个字节 其中L< 2 2 4 2^24 224 |
LONGTEXT | L+4个字节 其中L< 2 3 2 2^32 232 |
ENUM(‘value1’,‘value2’,…) | 1或2个字节,取决于美剧值的个数(最多65525个值) |
SET(‘value1’,‘value2’,…) | 1、2、3、4或者8个字节,取决于set成员的数目(最多64个成员) |
日期时间类型
列类型 | 存储范围 | 存储需求 |
---|---|---|
TIME | -838:59:59 - -838:59:59 | 3 |
DATE | 1000-01-01 ~ 9999-12-31 | 3 |
DATETIME | 1000-01-01 00:00:00 ~ 2038-01019 03:14:07 | 8 |
TIMESTAMP | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 | 4 |
YEAR | 1901-2155 | 1 |
除了以上内容意外,还可以存储二进制类型,如视频数据,xml等。
MySQL中的存储引擎
存储引擎指表的类型,数据库的存储类型决定了表在计算机中的存储方式,用户可以根据不同的存储方式,是否进行事务处理等来选择合适的存储引擎。
可以通过SHOW ENGINES;查看MySQL中的存储引擎。
mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
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: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
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: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
\G,是让显示更清晰。support表示mysql是否指出当前引擎,comment是指特点,transactions是指是否支持事务处理,xa表示是否是分布式,savepoints是否支持回滚点。
可以通过SHOW VARIABLES LIKE ‘have%’;查看显示支持的存储引擎详细信息。
mysql> SHOW VARIABLES LIKE 'have%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| have_compress | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | YES |
| have_profiling | YES |
| have_query_cache | NO |
| have_rtree_keys | YES |
| have_ssl | YES |
| have_statement_timeout | YES |
| have_symlink | DISABLED |
+------------------------+----------+
10 rows in set, 1 warning (0.03 sec)
查看默认的存储引擎SHOW VARIABLES LIKE ‘%storage_engine%’;。(只能模糊搜索)
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)
MySQL常用的搜索引擎LInnoDB存储引擎、MyISAM存储引擎、MEMORY存储引擎。
数据表的相关操作
数据博鳌是数据库最重要的组成部分之一,是其他对象的基础,数据表是存储数据的数据结构,包含了特定实体类型的数据,表有行(row)和列(column)构成的二维网络。
数据博鳌一定现有表结构,再有数据;数据表至少有一列,可以没有行或者多列,表名称要求唯一,而且不要包含特殊字符。
创建数据表
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名 字段类型[完整性约束条件]
…
)ENGINE=引擎名 CHARSET=‘编码方式’;
创建表的时候不建议直接写,最好在专门的编辑器写,粘贴复制过去。
MqSQL的注释需要添加#或者–
mysql> # SHOW DATABASES;
mysql> -- SHOW DATABASES;
创建表之前要先进入某个用户下:
mysql> \T D:\mysqldemo\mysql20191228.txt
Logging to file 'D:\mysqldemo\mysql20191228.txt'
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.05 sec)
mysql> USE test1;
Database changed
mysql> 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(8,2),
-> tel INT,
-> married TINYINT(1) COMMENT '0代表未结婚,非0代表已婚'
-> )ENGINE=INNODB CHARSET=UTF8;
Query OK, 0 rows affected, 4 warnings (0.07 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user |
+-----------------+
1 row in set (0.05 sec)
在使用关键字作为表名时,要用反引号。
在test1文件夹下有.frm文件存储表结构,表内容存储在.ibd文件,当创建表引擎时MyISAM时,数据存储在.MYD,索引在MYI文件中。
查看表结构
- DESC tbl_name
- DESCRIBE tbl_name
- SHOW COLUMNS FROM tbl_name
mysql> DESC user;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('ç”','å¥','保密') | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
10 rows in set (0.05 sec)
mysql> DESCRIBE user;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('ç”','å¥','保密') | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM user;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('ç”','å¥','保密') | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
每个类型后面的括号时显示长度。
测试数据类型
mysql> INSERT test1 VALUES(-128,-326=768,-8388608,-2147483648,-9223372036854775808);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM test1;
+------+------+----------+-------------+----------------------+
| num1 | num2 | num3 | num4 | num5 |
+------+------+----------+-------------+----------------------+
| -128 | 0 | -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.08 sec)
mysql> INSERT test2 VALUES(0,-12);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test2;
+------+------+
| num1 | num2 |
+------+------+
| 0 | -12 |
+------+------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test3(
-> num1 TINYINT ZEROFILL,
-> num2 TINYINT
-> );
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> INSERT test3 VALUES(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM test3;
+------+------+
| num1 | num2 |
+------+------+
| 001 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> DESC test3;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | tinyint(3) unsigned zerofill | YES | | NULL | |
| num2 | tinyint(4) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
2 rows in set (0.05 sec)
如果设置的显示长度比默认的小,但又不超过范围,是可以插入成功的:
mysql> CREATE TABLE test4(
-> num1 TINYINT(2) ZEROFILL,
-> num2 TINYINT
-> );
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> DESC test4;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | tinyint(2) unsigned zerofill | YES | | NULL | |
| num2 | tinyint(4) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT test4 VALUES(127,12);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test4;
+------+------+
| num1 | num2 |
+------+------+
| 127 | 12 |
+------+------+
1 row 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.07 sec)
mysql> INSERT test5 VALUES(3.1415,3.1415,3.1415);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> SELECT * FROM test5;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)
mysql> INSERT test5 VALUES(3.1495,3.1495,3.195);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> SELECT * FROM test5;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
| 3.15 | 3.15 | 3.20 |
+------+------+------+
2 rows 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)
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)
测试字符串:
char定长字符串,占用空间大,速度块
carchar变长字符串,占用空间小,速度慢
text是一种特殊的字符串类型。只能保存字符数据,而且不能有默认值
它们3个存储和检索数据的方式都不一样,数据检索的效率car>varchar>text
CHAR保存的时候,后面会用空格填充到指定长度,在检索时候后面的空格去掉。
VARCHAR在保存的时候,不进行填充。当值保存和检索时,尾部的空格仍保留。
mysql> CREATE TABLE test6(
-> num1 CHAR(5),
-> num2 VARCHAR(5)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT TEST6 VALUES('1 ','1 ');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT CONCAT(num1,'-'),CONCAT(num2,'-') FROM test6;
+------------------+------------------+
| CONCAT(num1,'-') | CONCAT(num2,'-') |
+------------------+------------------+
| 1- | 1 - |
+------------------+------------------+
1 row in set (0.00 sec)
mysql> INSERT TEST6 VALUES(' a',' a');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT CONCAT('-',num1),CONCAT('-',num2) FROM test6;
+------------------+------------------+
| CONCAT('-',num1) | CONCAT('-',num2) |
+------------------+------------------+
| -1 | -1 |
| - a | - a |
+------------------+------------------+
2 rows in set (0.00 sec)
在5.6以后的版本,不需要输入SET NAMES GBK;,默认的utf8就可以支持中文,输入后反倒容以出现乱码。
mysql> \s
--------------
mysql Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)
mysql> INSERT test6 VALUES('啊','啊');
Query OK, 1 row affected (0.05 sec)
Connection id: 17
Current database: test1
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter: ;
Server version: 8.0.18 MySQL Community Server - GPL
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 3 days 14 hours 24 min 50 sec
mysql> SELECT * FROM test6;
+------+------+
| num1 | num2 |
+------+------+
| 1 | 1 |
| a | a |
| 啊 | 啊 |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT LENGTH('啊');
+---------------+
| LENGTH('啊') |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT CHAR_LENGTH('啊');
+--------------------+
| CHAR_LENGTH('啊') |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
枚举类型
mysql> CREATE TABLE test7(
-> sex ENUM('男','女','保密')
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT test7 VALUES('男');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT test7 VALUES(2);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test7;
+------+
| sex |
+------+
| 男 |
| 女 |
+------+
2 rows in set (0.00 sec)
mysql> INSERT test7 VALUES(0);
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> INSERT test7 VALUES(NULL);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test7;
+------+
| sex |
+------+
| 男 |
| 女 |
| NULL |
+------+
3 rows in set (0.00 sec)
mysql> INSERT test7 VALUES('');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
测试集合类型
mysql> CREATE TABLE test8(
-> sex SET('A','B','C','D')
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT test8 VALUES('A,C,D');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test8;
+-------+
| sex |
+-------+
| A,C,D |
+-------+
1 row in set (0.00 sec)
mysql> INSERT test8 VALUES(3);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test8;
+-------+
| sex |
+-------+
| A,C,D |
| A,B |
+-------+
2 rows in set (0.00 sec)
数字中YEAR在插入时既可以是数字又可以是字符串,但最大只能是2155.
mysql> CREATE TABLE test10(
-> birth YEAR
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT test10 VALUES('2155');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT test10 VALUES(1966);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT test10 VALUES(2156);
ERROR 1264 (22003): Out of range value for column 'birth' at row 1
mysql> INSERT test10 VALUES(0);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
| 2155 |
| 1966 |
| 0000 |
+-------+
3 rows in set (0.00 sec)
mysql> INSERT test10 VALUES('0');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test10;
+-------+
| birth |
+-------+
| 2155 |
| 1966 |
| 0000 |
| 2000 |
+-------+
4 rows in set (0.00 sec)
TIME类型是天加小时
mysql> CREATE TABLE test11(
-> birth TIME
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT test11 VALUES('1 12:12:12');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test11;
+----------+
| birth |
+----------+
| 36:12:12 |
+----------+
1 row in set (0.00 sec)
mysql> INSERT test11 VALUES('11:11');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test11;
+----------+
| birth |
+----------+
| 36:12:12 |
| 11:11:00 |
+----------+
2 rows in set (0.05 sec)
mysql> INSERT test11 VALUES('1234');
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM test11;
+----------+
| birth |
+----------+
| 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.05 sec)
mysql> SELECT * FROM test11;
+----------+
| birth |
+----------+
| 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.01 sec)
mysql> SELECT * FROM test11;
+----------+
| birth |
+----------+
| 36:12:12 |
| 11:11:00 |
| 00:12:34 |
| 00:00:12 |
| 00:00:00 |
+----------+
5 rows in set (0.00 sec)
-- '0'和0一样的效果
DATE可以用是-或\作为分隔符,或者不加分隔符