1. MySQL数据库|库操作|表操作
1、初识数据库
我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个
专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，
总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。数据（Data）：描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机；在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容。
数据库（Databases，简称DB）：数据库库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的；数据库是长期存放在计算机内、有组织、可共享的数据即可。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享。
数据库管理系统（DataBase Management System 简称DBMS）在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键
这就用到了一个系统软件---数据库管理系统如MySQL、Oracle、SQLite、Access、MS SQL Server
常见的数据库模型分为关系型数据库（MySQL、Oracle、SQL Server....）和非关系型数据库（文档存储数据库MongoDB；键值存储数据库Redis、Memcached、列存储数据库HBase、图形数据库Neo4J）
mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。
数据库服务器-：运行数据库管理软件 数据库管理软件：管理-数据库 数据库：即文件夹，用来组织文件/表 表：即文件，用来存放多行内容/多条记录
1.1mysql的安装
mysql就是一个基于socket编写的C/S架构的软件
客户端软件
mysql自带：如mysql命令，mysqldump命令等
python模块：如pymysql
数据库管理软件分类--->>>
分两大类：
关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型：mongodb，redis，memcache
可以简单的理解为：
关系型数据库需要有表结构
非关系型数据库是key-value存储的，没有表结构
mysql windows7的安装：https://jingyan.baidu.com/article/f3ad7d0ffc061a09c3345bf0.html?qq-pf-to=pcqq.c2c
关于如何清除mysql，很不好清除，一系列的注册表什么的：参考 https://blog.csdn.net/renwudao24/article/details/51860752
存储数据的仓库
mysql就是套接字服务端、客户端而已；
win+R 输入servers.msc 找到服务里边去启动mysql
select user(); #查看当前登录的账号 mysql> select user(); +----------------+ | user() | +----------------+ | ODBC@localhost | #代表本地账号 +----------------+ 1 row in set (0.10 sec)
C:\Users\Administrator>mysql -uroot -p #uroot就是管理员账号 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 .... mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
C:\Users\Administrator>mysqladmin -uroot -p password '123' #修改默认密码 Enter password: Warning: Using a password on the command line interface can be insecure. Warning: single quotes were not trimmed from the password by your command line client, as you might have expected.
C:\Windows\System32>tasklist | findstr mysql mysqld.exe 2032 Services 0 5,324 K C:\Windows\System32>taskkill /F /PID 2032 成功: 已终止 PID 为 2032 的进程。 C:\Windows\System32>net start MySQL MySQL 服务正在启动 .. MySQL 服务已经启动成功。
1.2修改mysql字符编码
统一字符编码
关于win7修改默认字符编码：https://blog.csdn.net/u013474104/article/details/52486880
建个my.ini配置文件
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] character-set-server=utf8 collation-server=utf8_general_ci # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. basedir = C:\mysql-5.6.39-winx64 #这两个需要自行修改配置下path datadir = C:\mysql-5.6.39-winx64\data # port = ..... # server_id = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] default-character-set = utf8 [mysql.server] default-character-set = utf8 [mysqld_safe] default-character-set = utf8 [client] default-character-set = utf8
C:\Windows\system32>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.6.39, for Win64 (x86_64) Connection id: 1 Current database: Current user: ODBC@localhost SSL: Not in use Using delimiter: ; Server version: 5.6.39 MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 18 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tabl es: 60 Queries per second avg: 0.277 -------------- mysql> show variables like 'character%'; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\mysql-5.6.39-winx64\share\charsets\ | +--------------------------+----------------------------------------+ 8 rows in set (0.00 sec) mysql> exit Bye C:\Windows\system32>net stop MySQL MySQL 服务正在停止. MySQL 服务已成功停止。 C:\Windows\system32>net start MySQL MySQL 服务正在启动 . MySQL 服务已经启动成功。 C:\Windows\system32>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.6.39, for Win64 (x86_64) Connection id: 1 Current database: Current user: ODBC@localhost SSL: Not in use Using delimiter: ; Server version: 5.6.39 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: 13 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tabl es: 60 Queries per second avg: 0.384 -------------- mysql> show variables like 'character%'; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\mysql-5.6.39-winx64\share\charsets\ | +--------------------------+----------------------------------------+ 8 rows in set (0.00 sec)
1.3初识sql语句
跟数据有关的文件都放在data目录下
SQL语句：
操作文件夹（库）
操作数据库就是操作文件夹
增
create database db1 charset utf8;
查
show create database db1;
show databases; #查看所有的库
改
alter database db1 charset gbk;
删
drop database db1;
C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database db1 charset utf8; Query OK, 1 row affected (0.06 sec) mysql> show create database db1; +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.06 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.14 sec) mysql> alter database db1 charset gbk; Query OK, 1 row affected (0.00 sec) mysql> show create database db1; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> drop database db1; Query OK, 0 rows affected (0.26 sec)
操作文件（表）
造表就是在硬盘上操作文件；.frm就是表标题之类的、.db就是表数据；
切换文件夹：use db1;
查看当前所在文件夹：select database();
增
create table t1(id int,name char);
查
show create table t1;
show tables;
desc t1;
改
alter table t1 modify name char(6);
alter table t1 change name NAME char(7);
删
drop table t1;
C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database db1 charset utf8; Query OK, 1 row affected (0.00 sec) mysql> use db1; Database changed mysql> select database(); +------------+ | database() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec) mysql> create table t1(id int,name char); Query OK, 0 rows affected (1.65 sec) mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------- | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(6) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------- 1 row in set (0.12 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(6) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> alter table t1 change name NAME char(7); Query OK, 0 rows affected (1.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | NAME | char(7) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.05 sec) mysql> drop table t1; Query OK, 0 rows affected (0.23 sec) mysql> show tables; Empty set (0.00 sec)
操作文件内容（记录）
在表里边操作
增
insert t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3');
查
select id,name from db1.t1;
select * from db1.t1;（不推荐使用，测试的时候可以用）
改
update db1.t1 set name='SB';
update db1.t1 set name='ALEX' where id=2;
删
delete from t1;
delete from t1 where id=2;
mysql> use db1; Database changed mysql> insert t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3'); Query OK, 3 rows affected (0.53 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select id,name from db1.t1; +------+-------+ | id | name | +------+-------+ | 1 | egon1 | | 2 | egon2 | | 3 | egon3 | +------+-------+ 3 rows in set (0.00 sec) mysql> select id from db1.t1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> select * from db1.t1; +------+-------+ | id | name | +------+-------+ | 1 | egon1 | | 2 | egon2 | | 3 | egon3 | +------+-------+ 3 rows in set (0.00 sec) mysql> update db1.t1 set name='SB'; Query OK, 3 rows affected (0.10 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> update db1.t1 set name='alex' where id=2; Query OK, 1 row affected (0.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | SB | | 2 | alex | | 3 | SB | +------+------+ 3 rows in set (0.00 sec) mysql> delete from t1 where id=2; Query OK, 1 row affected (0.12 sec)
2、库操作
系统数据库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
创建数据库
CREATE DATABASE 数据库名 charset utf8;命名规则：可以由字母、数字、下划线、＠、＃、＄；区分大小写；唯一性；不能使用关键字如 create select；不能单独使用数字；最长128位查看数据库
show databases; ##所有的库
show create database db1;
select database(); ##当前选择的库
选择数据库
USE 数据库名;
删除数据库
DROP DATABASE 数据库名;
修改数据库
alter database db1 charset utf8;3、表操作
可以用 help create help create database
3.1存储引擎
1、什么是存储引擎？
存储引擎就是表的类型
2、查看MySQL支持的存储引擎
show engines;
show engines\G #查看所有支持的存储引擎
show variables like 'storage_engine%'; #查看正在使用的存储引擎
3、指定表类型/存储引擎
create table t1(id int)engine=innodb；
create table t2(id int)engine=memory; #数据丢进去存到内存里边 #需要把mysql关了内存就清除了
create table t3(id int)engine=blackhole; #黑洞，数据丢进去就没了
create table t4(id int)engine=myisam; #.frm表结构；.MYD是它的表data文件；.MYI索引文件；
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1)
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.08 sec) mysql> show engines\G *************************** 1. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 9. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO 9 rows in set (0.00 sec)
mysql> create database db2; Query OK, 1 row affected (0.01 sec) mysql> use db2; Database changed mysql> create table t1(id int)engine=innodb; Query OK, 0 rows affected (0.64 sec) mysql> create table t2(id int)engine=memory; Query OK, 0 rows affected (0.21 sec) mysql> create table t3(id int)engine=blackhole; Query OK, 0 rows affected (0.26 sec) mysql> create table t4(id int)engine=myisam; Query OK, 0 rows affected (0.17 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.31 sec) mysql> insert into t2 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into t3 values(1); Query OK, 1 row affected (0.02 sec) mysql> insert into t4 values(1); Query OK, 1 row affected (0.10 sec) mysql> select * from t1; #innodb +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select * from t4; #myisam +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select * from t3; #blackhole Empty set (0.00 sec) mysql> select * from t2; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> exit Bye C:\Windows\system32>net stop MySQL MySQL 服务正在停止. MySQL 服务已成功停止。 C:\Windows\system32>net start MySQL MySQL 服务正在启动 .. MySQL 服务已经启动成功。 C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.39 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from db2.t2; #数据丢到内存里边了，重新启动就没了 Empty set (0.00 sec)
3.2、表的增删改查
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段；
id，name，age，sex 称为字段，其余的，一行内容称为一条记录
语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的
查看表：
use db2
desc t4; #查看表结构、字段
show create table t4; #查看表的详细信息
show create table t4\G
show create table mysql.user\G
修改表结构：
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表：
复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;
只复制表结构（两种方法：1.是在条件为假的情况下；2.用like ）
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table t4 like employees;
mysql> select * from mysql.user; +-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-- | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete _priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | F ile_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_pri............................
.........................................##太多了此处省略
4 rows in set (0.00 sec) mysql> select * from mysql.user\G *************************** 1. row *************************** Host: localhost User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N *************************** 2. row *************************** Host: 127.0.0.1 User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N *************************** 3. row *************************** Host: ::1 User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N *************************** 4. row *************************** Host: localhost User: Password: Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: NULL password_expired: N 4 rows in set (0.00 sec)
mysql> select host,user from mysql.user; +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | root | +-----------+------+ 4 rows in set (0.00 sec)
mysql> create database db3 charset utf8; Query OK, 1 row affected (0.00 sec) mysql> use db3; Database changed mysql> select host,user from mysql.user; +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | root | +-----------+------+ 4 rows in set (0.00 sec) mysql> create table t1 select host,user from mysql.user; #别往屏幕上丢了，丢给t1，这就是复制表 Query OK, 4 rows affected (0.78 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc t1; #表结构只有host和user +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | host | char(60) | NO | | | | | user | char(16) | NO | | | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> select * from t1; #查看下它的记录 +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | root | +-----------+------+ 4 rows in set (0.00 sec) #########只要表结构，不要记录 mysql> select host,user from mysql.user; +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | root | +-----------+------+ 4 rows in set (0.00 sec) mysql> select host,user from mysql.user where 1>5; #在条件为假的情况下实现， 只要表结构，不要记录。 Empty set (0.07 sec) mysql> create table t2 select host,user from mysql.user where 1>5; Query OK, 0 rows affected (0.57 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | host | char(60) | NO | | | | | user | char(16) | NO | | | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> select * from t2; Empty set (0.00 sec) ####只拷贝表结构用like 就可以了，没有数据 mysql> create table t3 like mysql.user; Query OK, 0 rows affected (0.66 sec) mysql> desc t3; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | mysq l_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 43 rows in set (0.03 sec) mysql> desc mysql.user; ##跟上边操作一样 +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | mysq l_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 43 rows in set (0.01 sec) mysql> select * from t3; Empty set (0.06 sec)
3.3、数据类型
#1. 数字：
整型：tinyinit int bigint
小数：
float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
0.000001230123123123
存成：0.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存
#2. 字符串：
char（10）：简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
#3. 时间类型：
最常用：datetime
#4. 枚举类型与集合类型
验证表的数值类型 tinyint
========================================
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127
无符号：
0 ～ 255
PS： MySQL中无布尔值，使用tinyint(1)构造。
========================================
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
========================================
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
zerofill 使用说明，例如 int(5)表示当数值宽度小于 5 位的时候在数字前面加’0’填满宽度,如果不显示指定宽度则默认为 int(11)，zerofill默认为int(10)。注:当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0~256。
mysql> create database db4; Query OK, 1 row affected (0.00 sec) mysql> use db4; Database changed mysql> create table t1(x tinyint); ##默认是有符号zerofill -128~127 Query OK, 0 rows affected (0.52 sec) mysql> insert into t1 values(-1); Query OK, 1 row affected (0.12 sec) mysql> select * from t1; +------+ | x | +------+ | -1 | +------+ 1 row in set (0.00 sec) mysql> insert into t1 values(-129),(128); #超过范围win7系统报错了 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> create table t2(x tinyint unsigned); #创建无符号的，默认都是有符号的；有符号即数字前有正、负号 Query OK, 0 rows affected (0.57 sec) #0-255 mysql> insert into t2 values(-1),(256); ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t2 values(-1),(255); ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t2 values(0),(255); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t2; +------+ | x | +------+ | 0 | | 255 | +------+ 2 rows in set (0.00 sec) #宽度 字节 int（1）后边加了个宽度，这是个坑；整型后边那个宽度并不是它的存储宽度，而是它的显示宽度；存储宽度是在指定类型之后就已经默认定了；显示宽度也没有必要指定，它会给你默认添加的，
足够在范围以内；除此之前，其他数据类型的宽度就是它的存储宽度
精度
======================================
#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
有符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
无符号：
1.175494351E-38 to 3.402823466E+38
精确度：
**** 随着小数的增多，精度变得不准确 ****
======================================
#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
有符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
无符号：
2.2250738585072014E-308 to 1.7976931348623157E+308
精确度：
****随着小数的增多，精度比float要高，但也会变得不准确 ****
======================================
decimal[(m[,d])] [unsigned] [zerofill]
定义：
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
精确度：
**** 随着小数的增多，精度始终准确 ****
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。mysql> create table t8(x float(255,30)); Query OK, 0 rows affected (0.59 sec) mysql> create table t9(x double(255,30)); Query OK, 0 rows affected (0.53 sec) mysql> create table t10(x decimal(65,30)); Query OK, 0 rows affected (1.38 sec) mysql> insert into t8 values(1.111111111111111111111111111111);##随着小数位数增多，精度开始不准确 Query OK, 1 row affected (0.26 sec) mysql> insert into t9 values(1.111111111111111111111111111111);#精度要比float准确点，但随着小数的增多，同样变得不准确； Query OK, 1 row affected (0.16 sec) mysql> insert into t10 values(1.111111111111111111111111111111); ##精度始终准确，d为30，只保留30位小数 Query OK, 1 row affected (0.08 sec) mysql> select *from t8; +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t9; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t10; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+ 1 row in set (0.00 sec)
日期类型
create table student(
id int,
name char(6), # 表最大存储6个字符
born_year year,#年
birth_date date,#年月日
class_time time, #时分秒
reg_time datetime #年月日时分秒
)；
insert into student values
(1,'egon',now(),now(),now(),now() );
insert into student values
(2,'alex',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12");
mysql> create table student( -> id int, -> name char(6), -> born_year year, #年 -> birth_date date, #年月日 -> class_time time, #时分秒 -> reg_time datetime #年月日时分秒 -> ); Query OK, 0 rows affected (0.56 sec) mysql> insert into student values -> (1,'kris',now(),now(),now(),now()); Query OK, 1 row affected, 1 warning (0.25 sec) mysql> select * from student; +------+------+-----------+------------+------------+---------------------+ | id | name | born_year | birth_date | class_time | reg_time | +------+------+-----------+------------+------------+---------------------+ | 1 | kris | 2018 | 2018-04-26 | 13:57:00 | 2018-04-26 13:57:00 | +------+------+-----------+------------+------------+---------------------+ 1 row in set (0.00 sec) mysql> insert into student values -> (2,'alex',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"); Query OK, 1 row affected (0.12 sec) mysql> select * from student; +------+------+-----------+------------+------------+---------------------+ | id | name | born_year | birth_date | class_time | reg_time | +------+------+-----------+------------+------------+---------------------+ | 1 | kris | 2018 | 2018-04-26 | 13:57:00 | 2018-04-26 13:57:00 | | 2 | alex | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 | +------+------+-----------+------------+------------+---------------------+ 2 rows in set (0.00 sec) ->\c ##为终止运行
字符类型
char： 定长 不够的给你补上
varchar： 变长 传几个给你写几个，不要超过字符个数
#宽度指的是字符的个数
create table t13(name char(5));
create table t14(name varchar(5));
insert into t13 values('李杰 '); #'李杰 ' 后边有3个空格
insert into t14 values('李杰 '); #'李杰 ' 后边有1个空格
select char_length(name) from t13; #5 把name传给这个函数，统计查过结果的字符的长度
select char_length(name) from t14; #3
打开 pad_char_to_full_length SQL 模式 SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';这样在检索或者查询时，查出的结果会自动删除尾部的空格。
select name from t13 where name='李杰';
select name from t13 where name like '李杰';
mysql> create table t13(name char(5)); Query OK, 0 rows affected (0.64 sec) mysql> create table t14(name varchar(5)); Query OK, 0 rows affected (0.62 sec) mysql> insert into t13 values('alex'); Query OK, 1 row affected (0.12 sec) mysql> insert into t14 values('alex'); Query OK, 1 row affected (0.12 sec) mysql> mysql> select char_length(name) from t13; # char_length是查看字符数 #你取的时候它把后边的0给你省掉了；写入的时候不会省；存的时候是种存储机制，
#对于取可以加where条件，满足条件了再查出来；不管怎么存，在取的时候，mysql只会按照值进行匹造并不会管末尾有几个值；所以说在取的时候后边有几个空格没有用。 +-------------------+ | char_length(name) | +-------------------+ | 4 | +-------------------+ 1 row in set (0.14 sec) mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; Query OK, 0 rows affected (0.15 sec) #让它显出原型来 mysql> select char_length(name) from t13; #5 +-------------------+ | char_length(name) | +-------------------+ | 5 | +-------------------+ 1 row in set (0.00 sec) #######末尾去空格###（使用 where或者like两种方法 ） mysql> select name from t13 where name='alex'; +-------+ | name | +-------+ | alex | +-------+ 1 row in set (0.14 sec) mysql> select name from t13 where name='alex '; #不管后边有几个空格，都可以忽略，但是前面有空格就查不到了。 +-------+ | name | +-------+ | alex | +-------+ 1 row in set (0.00 sec) mysql> select name from t13 where name=' alex'; Empty set (0.00 sec) mysql> select name from t13 where name like'alex'; Empty set (0.02 sec) mysql> select name from t13 where name like'alex '; Empty set (0.00 sec) mysql> select name from t13 where name like'alex'; Empty set (0.00 sec) mysql> select name from t13 where name like'alex '; #like必须保证它的完整性，不然查不到 +-------+ | name | +-------+ | alex | +-------+ 1 row in set (0.00 sec)
char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形
虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比 较其值,忽略 CHAR 值存在的右填充,即使将 SQL _MODE 设置为 PAD_CHAR_TO_FULL_ LENGTH 也一样,,但这不适用于like；
name char(5) #char类型优点是简单粗暴，不管你有多少个数据，存5个取五个，存储效率非常快；缺点是浪费空间；（大部分场景用char）
egon |alex |wxx |
name varchar(5) #varchar类型优点是更加节省空间，存数据更加精准，但存的时候速度慢，先存头再存数据，取的时候也是先读头才知道取多少个数据
1bytes+egon|1bytes+alex|1bytes+wxx|
4+egon|4+alex|3+wxx|
建表的时候把定长的数据往前放，尽量不要混着用。
枚举类型与集合类型
字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
create table consumer(
id int,
name char(16),
sex enum('male','female','other'),
level enum('vip1','vip2','vip3'),
hobbies set('play','music','read','run')
);
insert into consumer values
(1,'egon','male','vip2','music,read');
insert into consumer values
(1,'egon','xxxxx','vip2','music,read');
mysql> create table consumer( -> id int, -> name char(16), -> sex enum('male','female','other'), -> level enum('vip1','vip2','vip3'), -> hobbies set('play','music','read','run') -> ); Query OK, 0 rows affected (0.62 sec) mysql> mysql> mysql> insert into consumer values -> (1,'egon','male','vip2','music,read'); Query OK, 1 row affected (0.14 sec) mysql> select * from consumer; +----------+------------------+-----------+-------------+---------------+ | id | name | sex | level | hobbies | +----------+------------------+-----------+-------------+---------------+ | 1 | egon | male | vip2 | music,read | +----------+------------------+-----------+-------------+---------------+ 1 row in set (0.00 sec) mysql> insert into consumer values -> (1,'egon','xxxxx','vip2','music,read'); #不在我范围之内的进来就是空的了 Query OK, 1 row affected, 1 warning (0.18 sec) mysql> select * from consumer; +----------+------------------+-----------+-------------+---------------+ | id | name | sex | level | hobbies | +----------+------------------+-----------+-------------+---------------+ | 1 | egon | male | vip2 | music,read | | 1 | egon | | vip2 | music,read | +----------+------------------+-----------+-------------+---------------+ 2 rows in set (0.00 sec)
3.4、完整型约束
作用：用于保证数据的完整性和一致性
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20 3. 是否是key 主键 primary key 外键 foreign key 索引 (index,unique...)
约束条件not null与default
create table t15(
id int(11) unsigned zerofill
);
create table t16(
id int,
name char(6),
sex enum('male','female') not null default 'male' #不能为空，如果传空就用默认的值男
);
insert into t16(id,name) values(1,'egon');
mysql> create table t15( -> id int(11) unsigned zerofill -> ); Query OK, 0 rows affected (0.55 sec) mysql> desc t15; #Null表上显示可以传空值，如果不允许，你还得给它个Default默认值 +-------+---------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------------+------+-----+---------+-------+ | id | int(11) unsigned zerofill | YES | | NULL | | +-------+---------------------------+------+-----+---------+-------+ 1 row in set (0.03 sec) mysql> create table t16( -> id int, -> name char(6), -> sex enum('male','female') not null default 'male' -> ); Query OK, 0 rows affected (0.61 sec) mysql> desc t16; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(6) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into t16(id,name) values(1,'egon'); Query OK, 1 row affected (0.13 sec) mysql> select * from t16; +------+--------+------+ | id | name | sex | +------+--------+------+ | 1 | egon | male | +------+--------+------+ 1 row in set (0.00 sec)
约束条件unique key
unique key
单列唯一
#方式一 #在谁的字段后边加unique，指的是这个字段的记录是唯一的不能重复；
create table department(
id int unique,
name char(10) unique
);
mysql> create table department( -> id int, -> name char(10) -> ); Query OK, 0 rows affected (0.51 sec) mysql> desc department; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into department values -> (1,'IT'), -> (2,'IT'); Query OK, 2 rows affected (0.10 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from department; +------+------------+ | id | name | +------+------------+ | 1 | IT | | 2 | IT | +------+------------+ 2 rows in set (0.00 sec) mysql> drop table department; Query OK, 0 rows affected (0.39 sec) mysql> create table department( -> id int unique, -> name char(10) unique -> ); Query OK, 0 rows affected (0.99 sec) mysql> desc department; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(10) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into department values -> (1,'IT'), -> (2,'IT'); ERROR 1062 (23000): Duplicate entry 'IT ' for key 'name' #报错了说重复了
#方式二：
create table department(
id int,
name char(10)，
unique(id),
unique(name)
);
insert into department values
(1,'IT'),
(2,'Sale');
mysql> drop table department; Query OK, 0 rows affected (0.33 sec) mysql> create table department( -> id int unique, -> name char(10) unique -> ); Query OK, 0 rows affected (0.73 sec) mysql> desc department; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(10) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.06 sec) mysql> insert into department values -> (1,'IT'), -> (2,'Sale'); Query OK, 2 rows affected (0.12 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from department; +------+------------+ | id | name | +------+------------+ | 1 | IT | | 2 | Sale | +------+------------+ 2 rows in set (0.00 sec)
联合唯一
create table services(
id int,
ip char(15),
port int,
unique(id),
unique(ip,port) #ip和端口是唯一的，这个叫联合唯一
);
insert into services values
(1,'192.168.11.10',80),
(2,'192.168.11.10',81),
(3,'192.168.11.13',80);
insert into services values
(4,'192.168.11.10',80);
mysql> create table services( -> id int, -> ip char(15), -> port int, -> unique(id), -> unique(ip,port) -> ); Query OK, 0 rows affected (0.75 sec) mysql> desc services; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.06 sec) mysql> insert into services values -> (1,'192.168.11.10',80), -> (2,'192.168.11.10',81), -> (3,'192.168.11.13',80); Query OK, 3 rows affected (0.27 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from services; +------+-----------------+------+ | id | ip | port | +------+-----------------+------+ | 1 | 192.168.11.10 | 80 | | 2 | 192.168.11.10 | 81 | | 3 | 192.168.11.13 | 80 | +------+-----------------+------+ 3 rows in set (0.00 sec) mysql> insert into services values -> (4,'192.168.11.10',80); ERROR 1062 (23000): Duplicate entry '192.168.11.10 -80' for key 'ip'
约束条件primary key
primary key是指约束：not null unique #不为空且唯一
一个表中可以：
单列做主键
多列做主键（复合主键）
但一个表内只能有一个主键primary key
存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须有一个主键；#innodb的独特特性不为空且唯一。
# 单列主键
create table t17(
id int primary key,
name char(16)
);
insert into t17 values
(1,'egon'),
(2,'alex');
insert into t17 values
(2,'wxx');
insert into t17(name) values
('wxx');
create table t18(
id int not null unique,
name char(16)
);
mysql> create table t17( -> id int primary key, -> name char(16) -> ); Query OK, 0 rows affected (0.71 sec) mysql> insert into t17 values -> (1,'egon'), -> (2,'alex'); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t17; +----------+------------------+ | id | name | +----------+------------------+ | 1 | egon | | 2 | alex | +----------+------------------+ 2 rows in set (0.00 sec) mysql> select * from t17; +----------+------------------+ | id | name | +----------+------------------+ | 1 | egon | | 2 | alex | +----------+------------------+ 2 rows in set (0.00 sec) mysql> mysql> mysql> create table t18( ######如果没有主键就回去自动寻找一个不为空且唯一的字段作为主键； -> id int not null unique, -> name char(16) -> ); Query OK, 0 rows affected (0.84 sec) mysql> desc t18; ###通常一个表中，都有一个ip字段用来表示它的标号，通常这个ip字段就应该设置为它的主键 +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
# 复合主键
create table t19(
ip char(15),
port int,
primary key(ip,port) #它俩联合到一起
);
insert into t19 values
('1.1.1.1',80),
('1.1.1.1',81);
mysql> create table t19( -> ip char(15), -> port int, -> primary key(ip,port) -> ); Query OK, 0 rows affected (0.61 sec) mysql> desc t19; +-------+----------+------+-----+-----------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+-----------------+-------+ | ip | char(15) | NO | PRI | | | | port | int(11) | NO | PRI | 0 | | +-------+----------+------+-----+-----------------+-------+ 2 rows in set (0.01 sec) mysql> insert into t19 values -> ('1.1.1.1',80), -> ('1.1.1.1',81); Query OK, 2 rows affected (0.16 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t19; +-----------------+------+ | ip | port | +-----------------+------+ | 1.1.1.1 | 80 | | 1.1.1.1 | 81 | +-----------------+------+ 2 rows in set (0.00 sec)
约束条件auto_increment
###auto_increment create table t20( id int primary key auto_increment, name char(16) ); insert into t20(name) values ('egon'), ('alex'), ('wxx');
insert into t20(id,name) values (7,'yuanhao'); insert into t20(name) values ('egon1'), ('egon2'), ('egon3'); #了解 show variables like 'auto_inc%'; ####模糊匹配 #步长： auto_increment_increment默认为1 #起始偏移量 auto_increment_offset默认1 #设置步长 set session auto_increment_increment=5; ##只在本次链接有效 set global auto_increment_increment=5; ##设置成全局了，全部有效了 #设置起始偏移量 set global auto_increment_offset=3; 强调：起始偏移量<=步长 create table t21( id int primary key auto_increment, name char(16) ); insert into t21(name) values ('egon'), ('alex'), ('wxx'), ('yxx'); 清空表： delete from t20; ##全部都删了；但是自增长字段的那个值没有为1 delete from t20 where id = 3; ##它用在删除固定范的记录，一般跟where连用； insert into t20(name) values ('xxx'); truncate t20; #应该用它来清空表
mysql> create table t20( -> id int primary key auto_increment, -> name char(16) -> ); Query OK, 0 rows affected (0.77 sec) mysql> desc t20; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.08 sec) mysql> insert into t20(name)values -> ('egon'), -> ('alex'), -> ('kris'); Query OK, 3 rows affected (0.18 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t20; ##自增长 +----+------------------+ | id | name | +----+------------------+ | 1 | egon | | 2 | alex | | 3 | kris | +----+------------------+ 3 rows in set (0.00 sec) mysql> insert into t20(id,name) values #我不自增长，非要插入个7也可以；接着下面的就会按照7自增 -> (7,'yuanhao'); Query OK, 1 row affected (0.14 sec) mysql> select * from t20; +----+------------------+ | id | name | +----+------------------+ | 1 | egon | | 2 | alex | | 3 | kris | | 7 | yuanhao | +----+------------------+ 4 rows in set (0.00 sec) mysql> insert into t20(name) values -> ('egon1'), -> ('egon2'), -> ('egon3'); Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t20; +----+------------------+ | id | name | +----+------------------+ | 1 | egon | | 2 | alex | | 3 | kris | | 7 | yuanhao | | 8 | egon1 | | 9 | egon2 | | 10 | egon3 | +----+------------------+ 7 rows in set (0.00 sec)
mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.13 sec) ##设置起始偏移量和步长。要退出exit然后重新登录下才生效
mysql> desc t20; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> select * from t20; +----+------------------+ | id | name | +----+------------------+ | 1 | egon | | 2 | alex | | 3 | kris | | 7 | yuanhao | | 8 | egon1 | | 9 | egon2 | | 10 | egon3 | +----+------------------+ 7 rows in set (0.00 sec) mysql> delete from t20; #删除记录，但id值没有清除掉； Query OK, 7 rows affected (0.15 sec) mysql> insert into t20(name)values -> ('xxx'); Query OK, 1 row affected (0.08 sec) mysql> select * from t20; +----+------------------+ | id | name | +----+------------------+ | 11 | xxx | +----+------------------+ 1 row in set (0.00 sec) mysql> truncate t20; #id值也就被清除了。 Query OK, 0 rows affected (0.59 sec) mysql> insert into t20(name)values -> ('xxx'); Query OK, 1 row affected (0.12 sec) mysql> select * from t20; +----+------------------+ | id | name | +----+------------------+ | 1 | xxx | +----+------------------+ 1 row in set (0.00 sec)
约束条件之foreign key
foreign key:建立表之间的关系
#1、建立表关系：
#先建被关联的表,并且保证被关联的字段唯一
create table dep(
id int primary key, ##应该保证它的唯一性，设置为主键
name char(16),
comment char(50)
);
#再建立关联的表
create table emp(
id int primary key,
name char(10),
sex enum('male','female'),
dep_id int,
foreign key(dep_id) references dep(id) #关联另外一张表的部门列
on delete cascade ########删除重复，只要被关联的表移动，关联的表也跟着移动
on update cascade ######更新重复，
);
mysql> create table dep( -> id int primary key, -> name char(16), -> comment char(50) -> ); Query OK, 0 rows affected (0.65 sec) mysql> desc dep; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | | comment | char(50) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 3 rows in set (0.02 sec) mysql> mysql> create table emp( -> id int primary key, -> name char(10), -> sex enum('male','female'), -> dep_id int, -> foreign key(dep_id) references dep(id)); Query OK, 0 rows affected (1.59 sec) mysql> desc emp; ##已经关联了，dep_id +--------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(10) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | dep_id | int(11) | YES | MUL | NULL | | +--------+-----------------------+------+-----+---------+-------+ 4 rows in set (0.08 sec) mysql> insert into dep values -> (1,"IT","技术能力有限部门"), -> (2,"销售","销售能力不足部门"), -> (3,"财务","花钱特别多部门"); Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into emp values -> (1,'egon','male',1); Query OK, 1 row affected (0.19 sec) mysql> insert into emp values -> (2,'alex','male',1), -> (3,'wupeiqi','female',2), -> (4,'yuanhao','male',3), -> (5,'jinximn','male',2); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from emp; +----+------------+--------+--------+ | id | name | sex | dep_id | +----+------------+--------+--------+ | 1 | egon | male | 1 | | 2 | alex | male | 1 | | 3 | wupeiqi | female | 2 | | 4 | yuanhao | male | 3 | | 5 | jinximn | male | 2 | +----+------------+--------+--------+ 5 rows in set (0.00 sec) mysql> select * from dep; +----+----------------------+--------------------------------------------------------------------+ | id | name | comment | +----+----------------------+--------------------------------------------------------------------+ | 1 | IT | 技术能力有限部门| | 2 | 销售 | 销售能力不足部门| | 3 | 财务 | 花钱特别多部门| +----+----------------------+--------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> mysql> delete from emp where dep_id=1; Query OK, 2 rows affected (0.23 sec) mysql> delete from dep where id=1; Query OK, 1 row affected (0.08 sec) mysql> select * from emp; +----+------------+--------+--------+ | id | name | sex | dep_id | +----+------------+--------+--------+ | 3 | wupeiqi | female | 2 | | 4 | yuanhao | male | 3 | | 5 | jinximn | male | 2 | +----+------------+--------+--------+ 3 rows in set (0.00 sec) mysql> select * from dep; +----+----------------------+--------------------------------------------------------------------+ | id | name | comment| +----+----------------------+--------------------------------------------------------------------+ | 2 | 销售 | 销售能力不足部门| | 3 | 财务 | 花钱特别多部门| +----+----------------------+--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> delete from dep where id=3; #删不掉，因为它们有关联关系 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai nt fails (`db4`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");
#再往关联表插入记录
insert into emp values
(1,'egon','male',1);
insert into emp values
(2,'alex','male',1),
(3,'wupeiqi','female',2),
(4,'yuanhao','male',3),
(5,'jinximn','male',2);
delete from emp where dep_id=1;
delete from dep where id=1;
delete from dep where id=3;
mysql> drop table emp; Query OK, 0 rows affected (0.32 sec) mysql> drop table dep; Query OK, 0 rows affected (0.25 sec) mysql> create table dep( -> id int primary key, -> name char(16), -> comment char(50) -> ); Query OK, 0 rows affected (0.66 sec) mysql> create table emp( -> id int primary key, -> name char(10), -> sex enum('male','female'), -> dep_id int, -> foreign key(dep_id) references dep(id) -> on delete cascade -> on update cascade -> ); Query OK, 0 rows affected (0.74 sec) mysql> insert into dep values -> (1,"IT","技术能力有限部门"), -> (2,"销售","销售能力不足部门"), -> (3,"财务","花钱特别多部门"); Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into emp values -> (1,'egon','male',1); Query OK, 1 row affected (0.17 sec) mysql> mysql> insert into emp values -> (2,'alex','male',1), -> (3,'wupeiqi','female',2), -> (4,'yuanhao','male',3), -> (5,'jinximn','male',2); Query OK, 4 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dep; +----+----------------------+--------------------------------------------------------------------+ | id | name | comment| +----+----------------------+--------------------------------------------------------------------+ | 1 | IT | 技术能力有限部门| | 2 | 销售 | 销售能力不足部门| | 3 | 财务 | 花钱特别多部门| +----+----------------------+--------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+------------+--------+--------+ | id | name | sex | dep_id | +----+------------+--------+--------+ | 1 | egon | male | 1 | | 2 | alex | male | 1 | | 3 | wupeiqi | female | 2 | | 4 | yuanhao | male | 3 | | 5 | jinximn | male | 2 | +----+------------+--------+--------+ 5 rows in set (0.00 sec) mysql> delete from dep where id=1; Query OK, 1 row affected (0.11 sec) mysql> select * from dep; +----+----------------------+--------------------------------------------------------------------+ | id | name | comment| +----+----------------------+--------------------------------------------------------------------+ | 2 | 销售 | 销售能力不足部门| | 3 | 财务 | 花钱特别多部门| +----+----------------------+--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------+--------+--------+ | id | name | sex | dep_id | +----+------------+--------+--------+ | 3 | wupeiqi | female | 2 | | 4 | yuanhao | male | 3 | | 5 | jinximn | male | 2 | +----+------------+--------+--------+ 3 rows in set (0.00 sec) mysql> update dep set id=202 where id=2; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +-----+----------------------+--------------------------------------------------------------------+ | id | name | comment| +-----+----------------------+--------------------------------------------------------------------+ | 3 | 财务 | 花钱特别多部门| | 202 | 销售 | 销售能力不足部门| +-----+----------------------+--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------+--------+--------+ | id | name | sex | dep_id | +----+------------+--------+--------+ | 3 | wupeiqi | female | 202 | | 4 | yuanhao | male | 3 | | 5 | jinximn | male | 202 | +----+------------+--------+--------+ 3 rows in set (0.00 sec)
最好不要直接把表建好硬性关系；先从逻辑上实现两张表的关系；从应用程序层面去搞，不要把两张表耦合到一起；少扩散数据库，在应用程序层面
4. 多表查询
两张表之间的关系： 1、多对一 出版社 书（foreign key(press_id) references press(id)） 2、多对多 作者 书 egon: 九阳神功 九阴真经 alex: 九阳神功 葵花宝典 yuanhao: 独孤九剑 降龙十巴掌 葵花宝典 wpq: 九阳神功 insert into author2book(author_id,book_id) values (1,1), (1,2), (2,1), (2,6); 3、一对一 customer表 student表
多对一
窍门：先站左表找右边多对一，再站右表找多对一左表的关系；
mysql> use db4; Database changed mysql> create table press( -> id int primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (1.23 sec) mysql> create table book( -> id int primary key auto_increment, -> name varchar(20), -> press_id int not null, -> foreign key(press_id) references press(id) -> on delete cascade -> on update cascade -> ); Query OK, 0 rows affected (0.76 sec) mysql> insert into press(name) values -> ('北京工业地雷出版社'), -> ('人民音乐不好听出版社'), -> ('知识产权没有用出版社') -> ; Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into book(name,press_id) values -> ('九阳神功',1), -> ('九阴真经',2), -> ('九阴白骨爪',2), -> ('独孤九剑',3), -> ('降龙十巴掌',2), -> ('葵花宝典',3) -> ; Query OK, 6 rows affected (0.09 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from press; +----+--------------------------------+ | id | name | +----+--------------------------------+ | 1 | 北京工业地雷出版社 | | 2 | 人民音乐不好听出版社 | | 3 | 知识产权没有用出版社 | +----+--------------------------------+ 3 rows in set (0.08 sec) mysql> select * from book; +----+-----------------+----------+ | id | name | press_id | +----+-----------------+----------+ | 1 | 九阳神功 | 1 | | 2 | 九阴真经 | 2 | | 3 | 九阴白骨爪 | 2 | | 4 | 独孤九剑 | 3 | | 5 | 降龙十巴掌 | 2 | | 6 | 葵花宝典 | 3 | +----+-----------------+----------+ 6 rows in set (0.00 sec)
多对多
站在左表多对一，站在右表多对一；把关系独立出来单独列一个表，有一个字段关联左表，又有一个字段关联右表；
egon:
九阳神功
九阴真经
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(2,1),
(2,6);
mysql> create table author( -> id int primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.91 sec) mysql> create table author2book( -> id int not null unique auto_increment, -> author_id int not null, -> book_id int not null, -> constraint fk_author foreign key(author_id) references author(id) -> on delete cascade -> on update cascade, -> constraint fk_book foreign key(book_id) references book(id) -> on delete cascade -> on update cascade, -> primary key(author_id,book_id) -> ); Query OK, 0 rows affected (1.19 sec) mysql> insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq'); Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from author; +----+---------+ | id | name | +----+---------+ | 1 | egon | | 2 | alex | | 3 | yuanhao | | 4 | wpq | +----+---------+ 4 rows in set (0.00 sec) mysql> select * from book; +----+-----------------+----------+ | id | name | press_id | +----+-----------------+----------+ | 1 | 九阳神功 | 1 | | 2 | 九阴真经 | 2 | | 3 | 九阴白骨爪 | 2 | | 4 | 独孤九剑 | 3 | | 5 | 降龙十巴掌 | 2 | | 6 | 葵花宝典 | 3 | +----+-----------------+----------+ 6 rows in set (0.00 sec) mysql> insert into author2book(author_id,book_id) values -> (1,1), -> (1,2), -> (2,1), -> (2,6); Query OK, 4 rows affected (0.18 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from author2book; +----+-----------+---------+ | id | author_id | book_id | +----+-----------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 1 | | 4 | 2 | 6 | +----+-----------+---------+ 4 rows in set (0.00 sec)
一对一
在后有记录的那个字段加个foreign key
mysql> create database db5; Query OK, 1 row affected (0.22 sec) mysql> use db5; Database changed mysql> create table customer( -> id int primary key auto_increment, -> name varchar(20) not null, -> qq varchar(10) not null, -> phone char(16) not null -> ); Query OK, 0 rows affected (0.88 sec) mysql> create table student( -> id int primary key auto_increment, -> class_name varchar(20) not null, -> customer_id int unique, #该字段一定要是唯一的 -> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique -> on delete cascade -> on update cascade -> ); Query OK, 0 rows affected (1.40 sec) mysql> insert into customer(name,qq,phone) values -> ('李飞机','31811231',13811341220), -> ('王大炮','123123123',15213146809), -> ('守榴弹','283818181',1867141331), -> ('吴坦克','283818181',1851143312), -> ('赢火箭','888818181',1861243314), -> ('战地雷','112312312',18811431230) -> ; Query OK, 6 rows affected (0.17 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into student(class_name,customer_id) values -> ('脱产3班',3), -> ('周末19期',4), -> ('周末19期',5) -> ; Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from customer; +----+-----------+-----------+-------------+ | id | name | qq | phone | +----+-----------+-----------+-------------+ | 1 | 李飞机 | 31811231 | 13811341220 | | 2 | 王大炮 | 123123123 | 15213146809 | | 3 | 守榴弹 | 283818181 | 1867141331 | | 4 | 吴坦克 | 283818181 | 1851143312 | | 5 | 赢火箭 | 888818181 | 1861243314 | | 6 | 战地雷 | 112312312 | 18811431230 | +----+-----------+-----------+-------------+ 6 rows in set (0.00 sec) mysql> select * from student; +----+-------------+-------------+ | id | class_name | customer_id | +----+-------------+-------------+ | 1 | 脱产3班 | 3 | | 2 | 周末19期 | 4 | | 3 | 周末19期 | 5 | +----+-------------+-------------+ 3 rows in set (0.00 sec)
