新手的MySQL学习笔记,数据表的基本操作

  

  

MySQL学习笔记,mysql笔记

  

  数据库是叁个得以存放数据库对象的器皿,数据库对象包罗:表、视图、存款和储蓄进程、函数、触发器、事件。个中,表是数据库最基本的因素,是其余数据库对象的前提条件。

  表中的1列称为1个字段,一行称为一条记下。

 

  1.数据表的始建、查看数据表、查看数据表结构

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> USE test1;
Database changed

mysql> CREATE TABLE table1(
    -> id SMALLINT UNSIGNED,
    -> username VARCHAR(20),
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

  二.数据表的删除 DROP TABLE table_name;

mysql> USE test1;
Database changed

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| tb2             |
+-----------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE tb2;
Query OK, 0 rows affected (0.20 sec)

mysql> DESCRIBE tb2;
ERROR 1146 (42S02): Table 'test1.tb2' doesn't exist

 

  叁.插入记录、查看记录  INSERT table_name [(col_name,
…)] VALUES(…);

mysql> SHOW COLUMNS FROM table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT table1 VALUES(5,'Tom',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table1 (id,username) VALUES(5,'Tom');
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM table1;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    5 | Tom      |   22 |
|    5 | Tom      | NULL |
+------+----------+------+
2 rows in set (0.00 sec)

  

  4.字段的空值与非空  NULL,NOT NULL

mysql> CREATE TABLE table2(
    -> username VARCHAR(20) NOT NULL,
    -> #NULL可加可不加,不加默认可以为空#
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW COLUMNS FROM table2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| age      | tinyint(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT table2 VALUES(NULL,25);
ERROR 1048 (23000): Column 'username' cannot be null

  

  5.主键约束与机关编号  P福特ExplorerIMA奥迪Q5Y KEY,AUTO_INCREMENT

    ※ 一张数据表只可以存在两个主键

    ※ 主键能担保记录的唯壹性

    ※ 主键自动为NOT NULL

    ※ AUTO_INCREMENT只可以合营P悍马H2IMALANDY KEY使用,不可能独立选取。PPAJEROIMA揽胜极光Y
KEY能够独立选取。

mysql> CREATE TABLE table3(
    -> id SMALLINT PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> DESCRIBE table3;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT table3 (username) VALUES('XingyaZhao');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table3 (username) VALUES('XuebiBaby');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT table3 VALUES(4,'David');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table3 (username) VALUES('Somebody');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM table3;
+----+------------+
| id | username   |
+----+------------+
|  1 | XingyaZhao |
|  2 | XuebiBaby  |
|  4 | David      |
|  5 | Somebody   |
+----+------------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE table4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> SHOW COLUMNS FROM table4;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT table4 VALUES(6,'ChuanDao');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table4 VALUES(3,'Pigiu');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT table4 VALUES(3,'York');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> SELECT * FROM table4;
+----+----------+
| id | username |
+----+----------+
|  3 | Pigiu    |
|  6 | ChuanDao |
+----+----------+
2 rows in set (0.00 sec)

   

  陆.唯1约束 UNIQUE KEY

    ※ 唯1约束能够确认保障有个别字段中每一个记录的唯一性

    ※ 唯一约束的字段可以为空值

    ※ 每张数据表能够存在五个唯1约束

mysql> CREATE TABLE table5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.60 sec)

mysql> SHOW COLUMNS FROM table5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table5 (username,age) VALUES('XuebiBaby',21);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',18);
ERROR 1062 (23000): Duplicate entry 'XingyaZhao' for key 'username'

mysql> SELECT * FROM table5;
+----+------------+-----+
| id | username   | age |
+----+------------+-----+
|  1 | XingyaZhao |  22 |
|  2 | XuebiBaby  |  21 |
+----+------------+-----+
3 rows in set (0.00 sec)

    

  7.暗许约束 DEFAULT

mysql> CREATE TABLE table6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED DEFAULT 20
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> DESCRIBE table6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | 20      |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT table6 (username) VALUES('Xingya');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT table6 (username) VALUES('Jude');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table6 (username,age) VALUES('Tom',21);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM table6;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Xingya   |   20 |
|  2 | Jude     |   20 |
|  3 | Tom      |   21 |
+----+----------+------+
3 rows in set (0.00 sec)

 

数据库是二个得以存放数据库对象的器皿,数据库对象包罗:表、视图、存款和储蓄进程、函数、触发器、事件。个中,…

3-1约束

  数据库是一个得以存放数据库对象的容器,数据库对象包含:表、视图、存款和储蓄进度、函数、触发器、事件。当中,表是数据库最宗旨的要素,是其它数据库对象的前提条件。

  数据库是一个得以存放数据库对象的器皿,数据库对象包涵:表、视图、存款和储蓄进程、函数、触发器、事件。个中,表是数据库最中央的要素,是别的数据库对象的前提条件。

一、约束保证数据的完整性和一致性;

  表中的一列称为贰个字段,一行称为一条记下。

  表中的壹列称为三个字段,一行称为一条记下。

二、约束分表级约束(多少个或多少个个上述字段的自律)和列级约束(二个字段约束);

 

 

新手的MySQL学习笔记,数据表的基本操作。3、NOT NULL      
非空;

  一.数据表的创制、查看数据表、查看数据表结构

  壹.数据表的成立、查看数据表、查看数据表结构

P奥迪Q3IMA昂CoraY KEY      
主键约束;

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> USE test1;
Database changed

mysql> CREATE TABLE table1(
    -> id SMALLINT UNSIGNED,
    -> username VARCHAR(20),
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> USE test1;
Database changed

mysql> CREATE TABLE table1(
    -> id SMALLINT UNSIGNED,
    -> username VARCHAR(20),
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

UNIQUE KEY        
唯1约束;

 

 

DEFAULT              
默许约束;

  贰.数据表的删除 DROP TABLE table_name;

  2.数据表的删减 DROP TABLE table_name;

FOREIGN KEY      
 外键约束。

mysql> USE test1;
Database changed

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| tb2             |
+-----------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE tb2;
Query OK, 0 rows affected (0.20 sec)

mysql> DESCRIBE tb2;
ERROR 1146 (42S02): Table 'test1.tb2' doesn't exist
mysql> USE test1;
Database changed

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| tb2             |
+-----------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE tb2;
Query OK, 0 rows affected (0.20 sec)

mysql> DESCRIBE tb2;
ERROR 1146 (42S02): Table 'test1.tb2' doesn't exist

空值与非空值:

 

 

NULL 与 NOT NULL 

  三.插入记录、查看记录  INSERT table_name [(col_name,
…)] VALUES(…);

  三.插入记录、查看记录  INSERT table_name [(col_name,
…)] VALUES(…);

自动编号:

mysql> SHOW COLUMNS FROM table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT table1 VALUES(5,'Tom',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table1 (id,username) VALUES(5,'Tom');
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM table1;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    5 | Tom      |   22 |
|    5 | Tom      | NULL |
+------+----------+------+
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT table1 VALUES(5,'Tom',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table1 (id,username) VALUES(5,'Tom');
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM table1;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    5 | Tom      |   22 |
|    5 | Tom      | NULL |
+------+----------+------+
2 rows in set (0.00 sec)

AUTO_INCREMENT
自动编号,必须与主键组合使用,暗中同意景况下初阶值为一,每一回增量为一;

  

  

主键:

  4.字段的空值与非空  NULL,NOT NULL

  四.字段的空值与非空  NULL,NOT NULL

PCRUISERIMAKoleosY KEY
 主键约束,每张鼠标只设有贰个主键,主键保障记录的唯壹性,主键自动为NOT
NULL;

mysql> CREATE TABLE table2(
    -> username VARCHAR(20) NOT NULL,
    -> #NULL可加可不加,不加默认可以为空#
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW COLUMNS FROM table2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| age      | tinyint(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT table2 VALUES(NULL,25);
ERROR 1048 (23000): Column 'username' cannot be null
mysql> CREATE TABLE table2(
    -> username VARCHAR(20) NOT NULL,
    -> #NULL可加可不加,不加默认可以为空#
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW COLUMNS FROM table2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| age      | tinyint(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT table2 VALUES(NULL,25);
ERROR 1048 (23000): Column 'username' cannot be null

#CREATE
TABLE tb3(

997755.com澳门葡京 ,  

  

id
SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,       #安装主键
和机关编号

  伍.主键约束与活动编号  P大切诺基IMA揽胜极光Y KEY,AUTO_INCREMENT

  5.主键约束与活动编号  P冠道IMA昂科拉Y KEY,AUTO_INCREMENT

username
VACHAR(20) NOT NULL);

    ※ 一张数据表只好存在3个主键

    ※ 一张数据表只好存在一个主键

唯1约束:

    ※ 主键能保障记录的唯1性

    ※ 主键能确认保证笔录的唯一性

UNIQUE KEY
唯1约束,能够确认保障记录的唯1性,可以为空值(只有三个NULL值),每张数据表可以存在几个唯1约束

    ※ 主键自动为NOT NULL

    ※ 主键自动为NOT NULL

#CREATE
TABLE tb4(

    ※ AUTO_INCREMENT只可以同盟PKoleosIMA奥德赛Y KEY使用,不可能独立行使。PRAV4IMAMuranoY
KEY能够单独选择。

    ※ AUTO_INCREMENT只好合作PPAJEROIMA陆风X八Y KEY使用,无法独立选拔。PCRUISERIMALX570Y
KEY能够独立选择。

id
SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

mysql> CREATE TABLE table3(
    -> id SMALLINT PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> DESCRIBE table3;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT table3 (username) VALUES('XingyaZhao');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table3 (username) VALUES('XuebiBaby');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT table3 VALUES(4,'David');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table3 (username) VALUES('Somebody');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM table3;
+----+------------+
| id | username   |
+----+------------+
|  1 | XingyaZhao |
|  2 | XuebiBaby  |
|  4 | David      |
|  5 | Somebody   |
+----+------------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE table4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> SHOW COLUMNS FROM table4;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT table4 VALUES(6,'ChuanDao');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table4 VALUES(3,'Pigiu');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT table4 VALUES(3,'York');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> SELECT * FROM table4;
+----+----------+
| id | username |
+----+----------+
|  3 | Pigiu    |
|  6 | ChuanDao |
+----+----------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE table3(
    -> id SMALLINT PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> DESCRIBE table3;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT table3 (username) VALUES('XingyaZhao');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table3 (username) VALUES('XuebiBaby');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT table3 VALUES(4,'David');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table3 (username) VALUES('Somebody');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM table3;
+----+------------+
| id | username   |
+----+------------+
|  1 | XingyaZhao |
|  2 | XuebiBaby  |
|  4 | David      |
|  5 | Somebody   |
+----+------------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE table4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> SHOW COLUMNS FROM table4;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT table4 VALUES(6,'ChuanDao');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table4 VALUES(3,'Pigiu');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT table4 VALUES(3,'York');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> SELECT * FROM table4;
+----+----------+
| id | username |
+----+----------+
|  3 | Pigiu    |
|  6 | ChuanDao |
+----+----------+
2 rows in set (0.00 sec)

username
VACHAR(20) NOT NULL UNIQUE KEY,     #安装姓名唯1约束 

   

   

age
TINYINT UNSIGNED);

  陆.唯一羁绊 UNIQUE KEY

  6.唯一约束 UNIQUE KEY

暗中同意约束:

    ※ 唯一约束能够确定保证有个别字段中各种记录的唯一性

    ※ 唯1约束能够有限支撑有个别字段中每种记录的唯一性

DEFAULT
 当插入记录时,要是未有明显性为字段赋值,则自动赋予暗中同意值。

    ※ 唯一约束的字段可感到空值

    ※ 唯一约束的字段可以为空值

#CREATE
TABLE tb5(

    ※ 每张数据表能够存在多少个唯一约束

    ※ 每张数据表能够存在七个唯一约束

id
SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

mysql> CREATE TABLE table5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.60 sec)

mysql> SHOW COLUMNS FROM table5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table5 (username,age) VALUES('XuebiBaby',21);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',18);
ERROR 1062 (23000): Duplicate entry 'XingyaZhao' for key 'username'

mysql> SELECT * FROM table5;
+----+------------+-----+
| id | username   | age |
+----+------------+-----+
|  1 | XingyaZhao |  22 |
|  2 | XuebiBaby  |  21 |
+----+------------+-----+
3 rows in set (0.00 sec)
mysql> CREATE TABLE table5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.60 sec)

mysql> SHOW COLUMNS FROM table5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table5 (username,age) VALUES('XuebiBaby',21);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',18);
ERROR 1062 (23000): Duplicate entry 'XingyaZhao' for key 'username'

mysql> SELECT * FROM table5;
+----+------------+-----+
| id | username   | age |
+----+------------+-----+
|  1 | XingyaZhao |  22 |
|  2 | XuebiBaby  |  21 |
+----+------------+-----+
3 rows in set (0.00 sec)

username
VACHAR(20) NOT NULL UNIQUE KEY,

    

    

sex
EMUI(‘1′,’2′,’3’) DEFAULT ‘3’);       #设置暗中认可性别字段为三

  7.默许约束 DEFAULT

  七.私下认可约束 DEFAULT

外键约束:

mysql> CREATE TABLE table6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED DEFAULT 20
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> DESCRIBE table6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | 20      |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT table6 (username) VALUES('Xingya');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT table6 (username) VALUES('Jude');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table6 (username,age) VALUES('Tom',21);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM table6;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Xingya   |   20 |
|  2 | Jude     |   20 |
|  3 | Tom      |   21 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE table6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED DEFAULT 20
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> DESCRIBE table6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | 20      |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT table6 (username) VALUES('Xingya');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT table6 (username) VALUES('Jude');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table6 (username,age) VALUES('Tom',21);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM table6;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Xingya   |   20 |
|  2 | Jude     |   20 |
|  3 | Tom      |   21 |
+----+----------+------+
3 rows in set (0.00 sec)

渴求:父表和子表必须运用同壹的蕴藏引擎,只好为InnoDB,且不准行使权且表;外键列和参照列必须怀有相似的数据类型,当中数字的长短或然是或不是有暗号位必须1律;而字符的尺寸则足以区别;外键列和参照列必须成立索引。如外键列不存在索引,mysql将自动制造索引。

 

 

#暗许存款和储蓄引擎:bin目录下
my.ini文件中  修改成INNODB;

  8.外键约束 FOREIGN KEY

  八.外键约束 FOREIGN KEY

本着外键约束的参考操作:

    ※ 父表和子表必须运用同样的存款和储蓄引擎 InnoDB

    ※ 父表和子表必须利用同样的贮存引擎 InnoDB

1、CASCADE:从父表删除或更新且活动删除或更新子表中相称的行;

    ※
外键列和参照列必须怀有相似的数据类型。个中数字的长短和是还是不是有标识位必须1律;而字符的长度能够分化。

    ※
外键列和参照列必须有所相似的数据类型。个中数字的长度和是不是有暗记位必须一律;而字符的尺寸能够分化。

二、SET
NULL:从父表删除或更新行,并安装子表中的外键列;

    ※
外键列和参照列必须创设索引。借使外键列不设有索引的话,MySQL将活动创设索引。

    ※
外键列和参照列必须创造索引。假使外键列不存在索引的话,MySQL将自行创制索引。

三、REST冠道ICT:拒绝父表的删除或更新操作;

mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW INDEXES FROM provinces\G
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM users\G
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW INDEXES FROM provinces\G
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM users\G
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

四、NO
ACTION:标准SQL的要害字,再mysql中与REST奥迪Q5ICT相同。

 

 

表级约束和列级约束:对贰个数据列建立的自律
为列级约束;对多少个数据列建立的约束称为表级约束。

表级约束只好再列定义后声称,列级约束能够再列定义时宣称,也足以再列定义后声称。

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website