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 | #代表本地账号
+----------------+
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
.... mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
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 Services , K C:\Windows\System32>taskkill /F /PID
成功: 已终止 PID 为 的进程。 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
Server version: 5.6. MySQL Community Server (GPL) Copyright (c) , , 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, row affected (0.06 sec) mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
row in set (0.06 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.14 sec) mysql> alter database db1 charset gbk;
Query OK, row affected (0.00 sec) mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
row in set (0.00 sec) mysql> drop database db1;
Query OK, 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
Server version: 5.6. MySQL Community Server (GPL) Copyright (c) , , 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, row affected (0.00 sec) mysql> use db1;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
row in set (0.00 sec) mysql> create table t1(id int,name char);
Query OK, rows affected (1.65 sec) mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
`id` int() DEFAULT NULL,
`name` char() DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
row in set (0.12 sec) mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
row in set (0.00 sec) mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int() | YES | | NULL | |
| name | char() | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
rows in set (0.01 sec) mysql> alter table t1 change name NAME char(7);
Query OK, rows affected (1.31 sec)
Records: Duplicates: Warnings: mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int() | YES | | NULL | |
| NAME | char() | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
rows in set (0.05 sec) mysql> drop table t1;
Query OK, 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, rows affected (0.53 sec)
Records: Duplicates: Warnings: mysql> select id,name from db1.t1;
+------+-------+
| id | name |
+------+-------+
| | egon1 |
| | egon2 |
| | egon3 |
+------+-------+
rows in set (0.00 sec) mysql> select id from db1.t1;
+------+
| id |
+------+
| |
| |
| |
+------+
rows in set (0.00 sec) mysql> select * from db1.t1;
+------+-------+
| id | name |
+------+-------+
| | egon1 |
| | egon2 |
| | egon3 |
+------+-------+
rows in set (0.00 sec) mysql> update db1.t1 set name='SB';
Query OK, rows affected (0.10 sec)
Rows matched: Changed: Warnings: mysql> update db1.t1 set name='alex' where id=2;
Query OK, row affected (0.16 sec)
Rows matched: Changed: Warnings: mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| | SB |
| | alex |
| | SB |
+------+------+
rows in set (0.00 sec) mysql> delete from t1 where id=;
Query OK, 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, row affected (0.01 sec) mysql> use db2;
Database changed
mysql> create table t1(id int)engine=innodb;
Query OK, rows affected (0.64 sec) mysql> create table t2(id int)engine=memory;
Query OK, rows affected (0.21 sec) mysql> create table t3(id int)engine=blackhole;
Query OK, rows affected (0.26 sec) mysql> create table t4(id int)engine=myisam;
Query OK, rows affected (0.17 sec) mysql> insert into t1 values();
Query OK, row affected (0.31 sec) mysql> insert into t2 values();
Query OK, row affected (0.00 sec) mysql> insert into t3 values();
Query OK, row affected (0.02 sec) mysql> insert into t4 values();
Query OK, row affected (0.10 sec) mysql> select * from t1; #innodb
+------+
| id |
+------+
| |
+------+
row in set (0.00 sec) mysql> select * from t4; #myisam
+------+
| id |
+------+
| |
+------+
row in set (0.00 sec) mysql> select * from t3; #blackhole
Empty set (0.00 sec) mysql> select * from t2;
+------+
| id |
+------+
| |
+------+
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
Server version: 5.6. MySQL Community Server (GPL) Copyright (c) , , 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 类型[(宽度) 约束条件]
); #注意：
. 在同一张表中，字段名是不能相同
. 宽度和约束条件可选
. 字段名和类型是必须的
查看表：
use db2
desc t4; #查看表结构、字段
show create table t4; #查看表的详细信息
show create table t4\G
show create table mysql.user\G
修改表结构：
语法：
. 修改表名
ALTER TABLE 表名
RENAME 新表名; . 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; . 删除字段
ALTER TABLE 表名
DROP 字段名; . 修改字段
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............................
.........................................##太多了此处省略
rows in set (0.00 sec) mysql> select * from mysql.user\G
*************************** . 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:
max_updates:
max_connections:
max_user_connections:
plugin: mysql_native_password
authentication_string:
password_expired: N
*************************** . 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:
max_updates:
max_connections:
max_user_connections:
plugin: mysql_native_password
authentication_string:
password_expired: N
*************************** . row ***************************
Host: ::
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:
max_updates:
max_connections:
max_user_connections:
plugin: mysql_native_password
authentication_string:
password_expired: N
*************************** . 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:
max_updates:
max_connections:
max_user_connections:
plugin: mysql_native_password
authentication_string: NULL
password_expired: N
rows in set (0.00 sec)
mysql> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| :: | root |
| localhost | |
| localhost | root |
+-----------+------+
rows in set (0.00 sec) mysql> create database db3 charset utf8;
Query OK, row affected (0.00 sec) mysql> use db3;
Database changed
mysql> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| :: | root |
| localhost | |
| localhost | root |
+-----------+------+
rows in set (0.00 sec) mysql> create table t1 select host,user from mysql.user; #别往屏幕上丢了，丢给t1，这就是复制表
Query OK, rows affected (0.78 sec)
Records: Duplicates: Warnings: mysql> desc t1; #表结构只有host和user
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| host | char() | NO | | | |
| user | char() | NO | | | |
+-------+----------+------+-----+---------+-------+
rows in set (0.01 sec) mysql> select * from t1; #查看下它的记录
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| :: | root |
| localhost | |
| localhost | root |
+-----------+------+
rows in set (0.00 sec) #########只要表结构，不要记录
mysql> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| :: | root |
| localhost | |
| localhost | root |
+-----------+------+
rows in set (0.00 sec) mysql> select host,user from mysql.user where >; #在条件为假的情况下实现， 只要表结构，不要记录。
Empty set (0.07 sec) mysql> create table t2 select host,user from mysql.user where >;
Query OK, rows affected (0.57 sec)
Records: Duplicates: Warnings: mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| host | char() | NO | | | |
| user | char() | NO | | | |
+-------+----------+------+-----+---------+-------+
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, rows affected (0.66 sec) mysql> desc t3;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char() | NO | PRI |
| |
| User | char() | NO | PRI |
| |
| Password | char() | 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() unsigned | NO | |
| |
| max_updates | int() unsigned | NO | |
| |
| max_connections | int() unsigned | NO | |
| |
| max_user_connections | int() unsigned | NO | |
| |
| plugin | char() | YES | | mysq
l_native_password | |
| authentication_string | text | YES | | NULL
| |
| password_expired | enum('N','Y') | NO | | N
| |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
rows in set (0.03 sec) mysql> desc mysql.user; ##跟上边操作一样
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char() | NO | PRI |
| |
| User | char() | NO | PRI |
| |
| Password | char() | 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() unsigned | NO | |
| |
| max_updates | int() unsigned | NO | |
| |
| max_connections | int() unsigned | NO | |
| |
| max_user_connections | int() unsigned | NO | |
| |
| plugin | char() | YES | | mysq
l_native_password | |
| authentication_string | text | YES | | NULL
| |
| password_expired | enum('N','Y') | NO | | N
| |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
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, row affected (0.00 sec) mysql> use db4;
Database changed
mysql> create table t1(x tinyint); ##默认是有符号zerofill -128~127
Query OK, rows affected (0.52 sec) mysql> insert into t1 values(-1);
Query OK, row affected (0.12 sec) mysql> select * from t1;
+------+
| x |
+------+
| - |
+------+
row in set (0.00 sec) mysql> insert into t1 values(-),(); #超过范围win7系统报错了 ERROR (): Out of range value for column 'x' at row mysql> create table t2(x tinyint unsigned); #创建无符号的，默认都是有符号的；有符号即数字前有正、负号
Query OK, rows affected (0.57 sec) #0-255 mysql> insert into t2 values(-),();
ERROR (): Out of range value for column 'x' at row
mysql> insert into t2 values(-),();
ERROR (): Out of range value for column 'x' at row
mysql> insert into t2 values(),();
Query OK, rows affected (0.13 sec)
Records: Duplicates: Warnings:
mysql> select * from t2;
+------+
| x |
+------+
| |
| |
+------+
rows in set (0.00 sec) #宽度 字节 int（）后边加了个宽度，这是个坑；整型后边那个宽度并不是它的存储宽度，而是它的显示宽度；存储宽度是在指定类型之后就已经默认定了；显示宽度也没有必要指定，它会给你默认添加的，
足够在范围以内；除此之前，其他数据类型的宽度就是它的存储宽度
精度
======================================
#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(,));
Query OK, rows affected (0.59 sec) mysql> create table t9(x double(,));
Query OK, rows affected (0.53 sec) mysql> create table t10(x decimal(,));
Query OK, rows affected (1.38 sec) mysql> insert into t8 values(1.111111111111111111111111111111);##随着小数位数增多，精度开始不准确
Query OK, row affected (0.26 sec) mysql> insert into t9 values(1.111111111111111111111111111111);#精度要比float准确点，但随着小数的增多，同样变得不准确；
Query OK, row affected (0.16 sec) mysql> insert into t10 values(1.111111111111111111111111111111); ##精度始终准确，d为30，只保留30位小数
Query OK, row affected (0.08 sec) mysql> select *from t8;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
row in set (0.00 sec) mysql> select * from t9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
row in set (0.00 sec) mysql> select * from t10;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
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(),
-> born_year year, #年
-> birth_date date, #年月日
-> class_time time, #时分秒
-> reg_time datetime #年月日时分秒
-> );
Query OK, rows affected (0.56 sec) mysql> insert into student values
-> (,'kris',now(),now(),now(),now());
Query OK, row affected, warning (0.25 sec) mysql> select * from student;
+------+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth_date | class_time | reg_time |
+------+------+-----------+------------+------------+---------------------+
| | kris | | -- | :: | -- :: |
+------+------+-----------+------------+------------+---------------------+
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, row affected (0.12 sec) mysql> select * from student;
+------+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth_date | class_time | reg_time |
+------+------+-----------+------------+------------+---------------------+
| | kris | | -- | :: | -- :: |
| | alex | | -- | :: | -- :: |
+------+------+-----------+------------+------------+---------------------+
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());
Query OK, rows affected (0.64 sec) mysql> create table t14(name varchar());
Query OK, rows affected (0.62 sec) mysql> insert into t13 values('alex');
Query OK, row affected (0.12 sec) mysql> insert into t14 values('alex');
Query OK, row affected (0.12 sec) mysql>
mysql> select char_length(name) from t13; # char_length是查看字符数 #你取的时候它把后边的0给你省掉了；写入的时候不会省；存的时候是种存储机制，
#对于取可以加where条件，满足条件了再查出来；不管怎么存，在取的时候，mysql只会按照值进行匹造并不会管末尾有几个值；所以说在取的时候后边有几个空格没有用。
+-------------------+
| char_length(name) |
+-------------------+
| |
+-------------------+
row in set (0.14 sec) mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, rows affected (0.15 sec) #让它显出原型来 mysql> select char_length(name) from t13; #
+-------------------+
| char_length(name) |
+-------------------+
| |
+-------------------+
row in set (0.00 sec) #######末尾去空格###（使用 where或者like两种方法 ）
mysql> select name from t13 where name='alex';
+-------+
| name |
+-------+
| alex |
+-------+
row in set (0.14 sec) mysql> select name from t13 where name='alex '; #不管后边有几个空格，都可以忽略，但是前面有空格就查不到了。
+-------+
| name |
+-------+
| alex |
+-------+
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 |
+-------+
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(),
-> sex enum('male','female','other'),
-> level enum('vip1','vip2','vip3'),
-> hobbies set('play','music','read','run')
-> );
Query OK, rows affected (0.62 sec) mysql>
mysql>
mysql> insert into consumer values
-> (,'egon','male','vip2','music,read');
Query OK, row affected (0.14 sec) mysql> select * from consumer;
+----------+------------------+-----------+-------------+---------------+
| id | name | sex | level | hobbies |
+----------+------------------+-----------+-------------+---------------+
| | egon | male | vip2 | music,read |
+----------+------------------+-----------+-------------+---------------+
row in set (0.00 sec) mysql> insert into consumer values
-> (,'egon','xxxxx','vip2','music,read'); #不在我范围之内的进来就是空的了
Query OK, row affected, warning (0.18 sec) mysql> select * from consumer;
+----------+------------------+-----------+-------------+---------------+
| id | name | sex | level | hobbies |
+----------+------------------+-----------+-------------+---------------+
| | egon | male | vip2 | music,read |
| | egon | | vip2 | music,read |
+----------+------------------+-----------+-------------+---------------+
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填充
. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 必须为正值（无符号） 不允许为空 默认是20
. 是否是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() unsigned zerofill
-> );
Query OK, rows affected (0.55 sec) mysql> desc t15; #Null表上显示可以传空值，如果不允许，你还得给它个Default默认值
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id | int() unsigned zerofill | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
row in set (0.03 sec) mysql> create table t16(
-> id int,
-> name char(),
-> sex enum('male','female') not null default 'male'
-> );
Query OK, rows affected (0.61 sec) mysql> desc t16;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int() | YES | | NULL | |
| name | char() | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00 sec) mysql> insert into t16(id,name) values(,'egon');
Query OK, row affected (0.13 sec) mysql> select * from t16;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| | egon | male |
+------+--------+------+
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()
-> );
Query OK, rows affected (0.51 sec) mysql> desc department;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | YES | | NULL | |
| name | char() | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.01 sec) mysql> insert into department values
-> (,'IT'),
-> (,'IT');
Query OK, rows affected (0.10 sec)
Records: Duplicates: Warnings: mysql> select * from department;
+------+------------+
| id | name |
+------+------------+
| | IT |
| | IT |
+------+------------+
rows in set (0.00 sec) mysql> drop table department;
Query OK, rows affected (0.39 sec) mysql> create table department(
-> id int unique,
-> name char() unique
-> );
Query OK, rows affected (0.99 sec) mysql> desc department;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | YES | UNI | NULL | |
| name | char() | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.01 sec) mysql> insert into department values
-> (,'IT'),
-> (,'IT');
ERROR (): 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, rows affected (0.33 sec) mysql> create table department(
-> id int unique,
-> name char() unique
-> );
Query OK, rows affected (0.73 sec) mysql> desc department;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | YES | UNI | NULL | |
| name | char() | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.06 sec) mysql> insert into department values
-> (,'IT'),
-> (,'Sale');
Query OK, rows affected (0.12 sec)
Records: Duplicates: Warnings: mysql> select * from department;
+------+------------+
| id | name |
+------+------------+
| | IT |
| | Sale |
+------+------------+
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(),
-> port int,
-> unique(id),
-> unique(ip,port)
-> );
Query OK, rows affected (0.75 sec) mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | YES | UNI | NULL | |
| ip | char() | YES | MUL | NULL | |
| port | int() | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.06 sec) mysql> insert into services values
-> (,'192.168.11.10',),
-> (,'192.168.11.10',),
-> (,'192.168.11.13',);
Query OK, rows affected (0.27 sec)
Records: Duplicates: Warnings: mysql> select * from services;
+------+-----------------+------+
| id | ip | port |
+------+-----------------+------+
| | 192.168.11.10 | |
| | 192.168.11.10 | |
| | 192.168.11.13 | |
+------+-----------------+------+
rows in set (0.00 sec) mysql> insert into services values
-> (,'192.168.11.10',);
ERROR (): 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()
-> );
Query OK, rows affected (0.71 sec) mysql> insert into t17 values
-> (,'egon'),
-> (,'alex');
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings: mysql> select * from t17;
+----------+------------------+
| id | name |
+----------+------------------+
| | egon |
| | alex |
+----------+------------------+
rows in set (0.00 sec) mysql> select * from t17;
+----------+------------------+
| id | name |
+----------+------------------+
| | egon |
| | alex |
+----------+------------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> create table t18( ######如果没有主键就回去自动寻找一个不为空且唯一的字段作为主键；
-> id int not null unique,
-> name char()
-> );
Query OK, rows affected (0.84 sec) mysql> desc t18; ###通常一个表中，都有一个ip字段用来表示它的标号，通常这个ip字段就应该设置为它的主键
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | NO | PRI | NULL | |
| name | char() | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
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(),
-> port int,
-> primary key(ip,port)
-> );
Query OK, rows affected (0.61 sec) mysql> desc t19;
+-------+----------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+-----------------+-------+
| ip | char() | NO | PRI | | |
| port | int() | NO | PRI | | |
+-------+----------+------+-----+-----------------+-------+
rows in set (0.01 sec) mysql> insert into t19 values
-> ('1.1.1.1',),
-> ('1.1.1.1',);
Query OK, rows affected (0.16 sec)
Records: Duplicates: Warnings: mysql> select * from t19;
+-----------------+------+
| ip | port |
+-----------------+------+
| 1.1.1.1 | |
| 1.1.1.1 | |
+-----------------+------+
rows in set (0.00 sec)
约束条件auto_increment
###auto_increment create table t20(
id int primary key auto_increment,
name char()
); insert into t20(name) values
('egon'),
('alex'),
('wxx'); insert into t20(id,name) values
(,'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=; ##只在本次链接有效
set global auto_increment_increment=; ##设置成全局了，全部有效了 #设置起始偏移量
set global auto_increment_offset=;
强调：起始偏移量<=步长 create table t21(
id int primary key auto_increment,
name char()
); insert into t21(name) values
('egon'),
('alex'),
('wxx'),
('yxx'); 清空表：
delete from t20; ##全部都删了；但是自增长字段的那个值没有为1
delete from t20 where id = ; ##它用在删除固定范的记录，一般跟where连用；
insert into t20(name) values
('xxx'); truncate t20; #应该用它来清空表
mysql> create table t20(
-> id int primary key auto_increment,
-> name char()
-> );
Query OK, rows affected (0.77 sec) mysql> desc t20;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| name | char() | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
rows in set (0.08 sec) mysql> insert into t20(name)values
-> ('egon'),
-> ('alex'),
-> ('kris');
Query OK, rows affected (0.18 sec)
Records: Duplicates: Warnings: mysql> select * from t20; ##自增长
+----+------------------+
| id | name |
+----+------------------+
| | egon |
| | alex |
| | kris |
+----+------------------+
rows in set (0.00 sec) mysql> insert into t20(id,name) values #我不自增长，非要插入个7也可以；接着下面的就会按照7自增
-> (,'yuanhao');
Query OK, row affected (0.14 sec) mysql> select * from t20;
+----+------------------+
| id | name |
+----+------------------+
| | egon |
| | alex |
| | kris |
| | yuanhao |
+----+------------------+
rows in set (0.00 sec) mysql> insert into t20(name) values
-> ('egon1'),
-> ('egon2'),
-> ('egon3');
Query OK, rows affected (0.11 sec)
Records: Duplicates: Warnings: mysql> select * from t20;
+----+------------------+
| id | name |
+----+------------------+
| | egon |
| | alex |
| | kris |
| | yuanhao |
| | egon1 |
| | egon2 |
| | egon3 |
+----+------------------+
rows in set (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | |
| auto_increment_offset | |
+--------------------------+-------+
rows in set (0.13 sec) ##设置起始偏移量和步长。要退出exit然后重新登录下才生效
mysql> desc t20;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| name | char() | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> select * from t20;
+----+------------------+
| id | name |
+----+------------------+
| | egon |
| | alex |
| | kris |
| | yuanhao |
| | egon1 |
| | egon2 |
| | egon3 |
+----+------------------+
rows in set (0.00 sec) mysql> delete from t20; #删除记录，但id值没有清除掉；
Query OK, rows affected (0.15 sec) mysql> insert into t20(name)values
-> ('xxx');
Query OK, row affected (0.08 sec) mysql> select * from t20;
+----+------------------+
| id | name |
+----+------------------+
| | xxx |
+----+------------------+
row in set (0.00 sec) mysql> truncate t20; #id值也就被清除了。
Query OK, rows affected (0.59 sec) mysql> insert into t20(name)values
-> ('xxx');
Query OK, row affected (0.12 sec) mysql> select * from t20;
+----+------------------+
| id | name |
+----+------------------+
| | xxx |
+----+------------------+
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(),
-> comment char()
-> );
Query OK, rows affected (0.65 sec) mysql> desc dep;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int() | NO | PRI | NULL | |
| name | char() | YES | | NULL | |
| comment | char() | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
rows in set (0.02 sec) mysql>
mysql> create table emp(
-> id int primary key,
-> name char(),
-> sex enum('male','female'),
-> dep_id int,
-> foreign key(dep_id) references dep(id));
Query OK, rows affected (1.59 sec) mysql> desc emp; ##已经关联了，dep_id
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int() | NO | PRI | NULL | |
| name | char() | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| dep_id | int(11) | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
rows in set (0.08 sec) mysql> insert into dep values
-> (,"IT","技术能力有限部门"),
-> (,"销售","销售能力不足部门"),
-> (,"财务","花钱特别多部门");
Query OK, rows affected (0.13 sec)
Records: Duplicates: Warnings: mysql> insert into emp values
-> (,'egon','male',);
Query OK, row affected (0.19 sec) mysql> insert into emp values
-> (,'alex','male',),
-> (,'wupeiqi','female',),
-> (,'yuanhao','male',),
-> (,'jinximn','male',);
Query OK, rows affected (0.06 sec)
Records: Duplicates: Warnings: mysql> select * from emp;
+----+------------+--------+--------+
| id | name | sex | dep_id |
+----+------------+--------+--------+
| | egon | male | |
| | alex | male | |
| | wupeiqi | female | |
| | yuanhao | male | |
| | jinximn | male | |
+----+------------+--------+--------+
rows in set (0.00 sec) mysql> select * from dep;
+----+----------------------+--------------------------------------------------------------------+
| id | name | comment |
+----+----------------------+--------------------------------------------------------------------+
| | IT | 技术能力有限部门|
| | 销售 | 销售能力不足部门|
| | 财务 | 花钱特别多部门|
+----+----------------------+--------------------------------------------------------------------+
rows in set (0.00 sec) mysql>
mysql> delete from emp where dep_id=1;
Query OK, rows affected (0.23 sec) mysql> delete from dep where id=1;
Query OK, row affected (0.08 sec) mysql> select * from emp;
+----+------------+--------+--------+
| id | name | sex | dep_id |
+----+------------+--------+--------+
| | wupeiqi | female | |
| | yuanhao | male | |
| | jinximn | male | |
+----+------------+--------+--------+
rows in set (0.00 sec) mysql> select * from dep;
+----+----------------------+--------------------------------------------------------------------+
| id | name | comment|
+----+----------------------+--------------------------------------------------------------------+
| | 销售 | 销售能力不足部门|
| | 财务 | 花钱特别多部门|
+----+----------------------+--------------------------------------------------------------------+
rows in set (0.00 sec) mysql> delete from dep where id=3; #删不掉，因为它们有关联关系
ERROR (): 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, rows affected (0.32 sec) mysql> drop table dep;
Query OK, rows affected (0.25 sec) mysql> create table dep(
-> id int primary key,
-> name char(),
-> comment char()
-> );
Query OK, rows affected (0.66 sec) mysql> create table emp(
-> id int primary key,
-> name char(),
-> sex enum('male','female'),
-> dep_id int,
-> foreign key(dep_id) references dep(id)
-> on delete cascade
-> on update cascade
-> );
Query OK, rows affected (0.74 sec) mysql> insert into dep values
-> (,"IT","技术能力有限部门"),
-> (,"销售","销售能力不足部门"),
-> (,"财务","花钱特别多部门");
Query OK, rows affected (0.14 sec)
Records: Duplicates: Warnings: mysql> insert into emp values
-> (,'egon','male',);
Query OK, row affected (0.17 sec) mysql>
mysql> insert into emp values
-> (,'alex','male',),
-> (,'wupeiqi','female',),
-> (,'yuanhao','male',),
-> (,'jinximn','male',);
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings: mysql> select * from dep;
+----+----------------------+--------------------------------------------------------------------+
| id | name | comment|
+----+----------------------+--------------------------------------------------------------------+
| | IT | 技术能力有限部门|
| | 销售 | 销售能力不足部门|
| | 财务 | 花钱特别多部门|
+----+----------------------+--------------------------------------------------------------------+
rows in set (0.00 sec) mysql> select * from emp;
+----+------------+--------+--------+
| id | name | sex | dep_id |
+----+------------+--------+--------+
| | egon | male | |
| | alex | male | |
| | wupeiqi | female | |
| | yuanhao | male | |
| | jinximn | male | |
+----+------------+--------+--------+
rows in set (0.00 sec) mysql> delete from dep where id=;
Query OK, row affected (0.11 sec) mysql> select * from dep;
+----+----------------------+--------------------------------------------------------------------+
| id | name | comment|
+----+----------------------+--------------------------------------------------------------------+
| | 销售 | 销售能力不足部门|
| | 财务 | 花钱特别多部门|
+----+----------------------+--------------------------------------------------------------------+
rows in set (0.00 sec) mysql> select * from emp;
+----+------------+--------+--------+
| id | name | sex | dep_id |
+----+------------+--------+--------+
| | wupeiqi | female | |
| | yuanhao | male | |
| | jinximn | male | |
+----+------------+--------+--------+
rows in set (0.00 sec) mysql> update dep set id=202 where id=2;
Query OK, row affected (0.10 sec)
Rows matched: Changed: Warnings: mysql> select * from dep;
+-----+----------------------+--------------------------------------------------------------------+
| id | name | comment|
+-----+----------------------+--------------------------------------------------------------------+
| | 财务 | 花钱特别多部门|
| | 销售 | 销售能力不足部门|
+-----+----------------------+--------------------------------------------------------------------+
rows in set (0.00 sec) mysql> select * from emp;
+----+------------+--------+--------+
| id | name | sex | dep_id |
+----+------------+--------+--------+
| | wupeiqi | female | |
| | yuanhao | male | |
| | jinximn | male | |
+----+------------+--------+--------+
rows in set (0.00 sec)
最好不要直接把表建好硬性关系；先从逻辑上实现两张表的关系；从应用程序层面去搞，不要把两张表耦合到一起；少扩散数据库，在应用程序层面
4. 多表查询
两张表之间的关系：
、多对一
出版社 书（foreign key(press_id) references press(id)）
、多对多
作者 书
egon:
九阳神功
九阴真经
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功 insert into author2book(author_id,book_id) values
(,),
(,),
(,),
(,); 、一对一
customer表 student表
多对一
窍门：先站左表找右边多对一，再站右表找多对一左表的关系；
mysql> use db4;
Database changed
mysql> create table press(
-> id int primary key auto_increment,
-> name varchar()
-> );
Query OK, rows affected (1.23 sec) mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(),
-> press_id int not null,
-> foreign key(press_id) references press(id)
-> on delete cascade
-> on update cascade
-> );
Query OK, rows affected (0.76 sec) mysql> insert into press(name) values
-> ('北京工业地雷出版社'),
-> ('人民音乐不好听出版社'),
-> ('知识产权没有用出版社')
-> ;
Query OK, rows affected (0.13 sec)
Records: Duplicates: Warnings: mysql> insert into book(name,press_id) values
-> ('九阳神功',),
-> ('九阴真经',),
-> ('九阴白骨爪',),
-> ('独孤九剑',),
-> ('降龙十巴掌',),
-> ('葵花宝典',)
-> ;
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings: mysql> select * from press;
+----+--------------------------------+
| id | name |
+----+--------------------------------+
| | 北京工业地雷出版社 |
| | 人民音乐不好听出版社 |
| | 知识产权没有用出版社 |
+----+--------------------------------+
rows in set (0.08 sec) mysql> select * from book;
+----+-----------------+----------+
| id | name | press_id |
+----+-----------------+----------+
| | 九阳神功 | |
| | 九阴真经 | |
| | 九阴白骨爪 | |
| | 独孤九剑 | |
| | 降龙十巴掌 | |
| | 葵花宝典 | |
+----+-----------------+----------+
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()
-> );
Query OK, 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, rows affected (1.19 sec) mysql> insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
Query OK, rows affected (0.22 sec)
Records: Duplicates: Warnings: mysql> select * from author;
+----+---------+
| id | name |
+----+---------+
| | egon |
| | alex |
| | yuanhao |
| | wpq |
+----+---------+
rows in set (0.00 sec) mysql> select * from book;
+----+-----------------+----------+
| id | name | press_id |
+----+-----------------+----------+
| | 九阳神功 | |
| | 九阴真经 | |
| | 九阴白骨爪 | |
| | 独孤九剑 | |
| | 降龙十巴掌 | |
| | 葵花宝典 | |
+----+-----------------+----------+
rows in set (0.00 sec) mysql> insert into author2book(author_id,book_id) values
-> (,),
-> (,),
-> (,),
-> (,);
Query OK, rows affected (0.18 sec)
Records: Duplicates: Warnings: mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| | | |
| | | |
| | | |
| | | |
+----+-----------+---------+
rows in set (0.00 sec)
一对一
在后有记录的那个字段加个foreign key
mysql> create database db5;
Query OK, row affected (0.22 sec) mysql> use db5;
Database changed
mysql> create table customer(
-> id int primary key auto_increment,
-> name varchar() not null,
-> qq varchar() not null,
-> phone char() not null
-> );
Query OK, rows affected (0.88 sec) mysql> create table student(
-> id int primary key auto_increment,
-> class_name varchar() not null,
-> customer_id int unique, #该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );
Query OK, rows affected (1.40 sec) mysql> insert into customer(name,qq,phone) values
-> ('李飞机','',),
-> ('王大炮','',),
-> ('守榴弹','',),
-> ('吴坦克','',),
-> ('赢火箭','',),
-> ('战地雷','',)
-> ;
Query OK, rows affected (0.17 sec)
Records: Duplicates: Warnings: mysql> insert into student(class_name,customer_id) values
-> ('脱产3班',),
-> ('周末19期',),
-> ('周末19期',)
-> ;
Query OK, rows affected (0.14 sec)
Records: Duplicates: Warnings: mysql> select * from customer;
+----+-----------+-----------+-------------+
| id | name | qq | phone |
+----+-----------+-----------+-------------+
| | 李飞机 | | |
| | 王大炮 | | |
| | 守榴弹 | | |
| | 吴坦克 | | |
| | 赢火箭 | | |
| | 战地雷 | | |
+----+-----------+-----------+-------------+
rows in set (0.00 sec) mysql> select * from student;
+----+-------------+-------------+
| id | class_name | customer_id |
+----+-------------+-------------+
| | 脱产3班 | |
| | 周末19期 | |
| | 周末19期 | |
+----+-------------+-------------+
rows in set (0.00 sec)