Shulou(Shulou.com)06/01 Report--

1. Create a data table

Stored as rows and columns, each row has a unique record, and each column represents a field or field in the record.

Format: the name of the table is case-insensitive and cannot use the SQL keyword; multiple columns are separated by commas.

Create table (field 1, data type [column constraint], field 2, data type [column constraint], field 3, data type [column constraint],. [table level constraints])

Test: table name test01

| | Field name | data type | |

| | name | varchar (30) |

| | id | int (11) | |

(1) Primary key constraint

Single field

Mysql > create table test02-> (id int (11) primary key,-> name varchar (30); Query OK, 0 rows affected (0.08 sec) mysql > create table test03-> (id int (11),-> name varchar (30),-> primary key (id)); Query OK, 0 rows affected (0.10 sec) mysql > desc test02 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | NULL | name | varchar (30) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > desc test03 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | 0 | name | varchar (30) | YES | | NULL | | +-+-+ 2 rows in set (0.01 sec)

Column implies a rowid field

The table will explicitly require id to be a recognizable flag.

Multiple field

Mysql > create table test05 (id int (11), name varchar (30), primary key (id,name)); Query OK, 0 rows affected (0.11 sec)

Delete primary key constraint

Mysql > alter table test0004 drop primary key; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > show create table test0004\ G * * 1. Row * * Table: test0004 Create Table: CREATE TABLE 'test0004` (`username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, `id`smallint (5) unsigned NOT NULL DEFAULT '0mm, `age` tinyint (3) unsigned NOT NULL UNIQUE KEY `username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql > desc test0004 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | username | varchar (10) | NO | PRI | NULL | pid | smallint (5) unsigned | YES | | NULL | id | smallint (5) unsigned | NO | | 0 | age | tinyint (3) unsigned | NO | | NULL | | +-+-- + 4 rows in set (0.00 sec)

How to add a primary key to an existing table?

For example:

Create a notesheet, and then add a field

Mysql > create table test0004 (username varchar (10) not null, pid smallint unsigned); Query OK, 0 rows affected (0.13 sec) mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | | NULL | | pid | smallint (5) unsigned | YES | | NULL | | +-+-- + -+ 2 rows in set (0.00 sec) mysql > alter table test0004 add id smallint unsigned Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0

Add a primary key and verify

Mysql > alter table test0004 add constraint PK_test0004_id primary key (id); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | | NULL | | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | +-+- -+ 3 rows in set (0.00 sec) mysql > show create table test0004\ G * * 1. Row * * Table: test0004Create Table: CREATE TABLE `test0004` (`username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, `id` smallint (5) unsigned NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Let's add a unique constraint to take a look.

Mysql > alter table test0004 add unique (username); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table test0004\ G * * 1. Row * * Table: test0004Create Table: CREATE TABLE `test0004` (`username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, `id`smallint (5) unsigned NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Next, we add a field to him and modify and delete the default value operation.

Mysql > alter table test0004 add age tinyint unsigned not null;Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | UNI | NULL | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (3) unsigned | NO | NULL | | | +-+-+ 4 rows in set (0.00 sec) mysql > alter table test0004 alter age set default 15 | Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | UNI | NULL | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (3) unsigned | NO | 15 | | | +-+-+ 4 rows in set (0.00 sec) mysql > alter table test0004 alter age drop default | Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

(2) Foreign bond bondage

A table can have one or more foreign keys; ensure the consistency and integrity of the data; after defining foreign keys, no

Allows you to delete records that have associated relationships in another table.

Primary table: a table with a primary key that is associated with two tables

Slave table: a table with a foreign key that is associated with two tables

Constraint foreign key references primary key column

Mysql > create table test06-> (id int (11) primary key,-> name varchar (30) not null); Query OK, 0 rows affected (0.16 sec) mysql > create table test07 (id int (11) primary key, name varchar (30), constraint test0607 foreign key (id) references test06 (id)); Query OK, 0 rows affected (0.19 sec)

Deepen:

First create a provin1 table

Mysql > create table provin1 (id smallint unsigned primary key auto_increment, pnaame varchar (20) not null); Query OK, 0 rows affected (0.09 sec) mysql > show create table provin1\ G * * 1. Row * * Table: provin1Create Table: CREATE TABLE `provin1` (`id` smallint (5) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar (20) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Create another test0003 table

Mysql > create table test0003 (id smallint unsigned primary key auto_increment, ussername varchar (10) not null, pid smallint unsigned, foreign key (pid) referencess provin1 (id) on delete cascade); Query OK, 0 rows affected (0.08 sec) mysql > show create table test0003\ G * * 1. Row * * Table: test0003Create Table: CREATE TABLE `test0003` (`id`smallint (5) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`) CONSTRAINT `test0003_ibfk_ 1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)

Insert data into provin1

Mysql > insert provin1 (pname) values ('A'); Query OK, 1 row affected (0.00 sec) mysql > insert provin1 (pname) values ('b'); Query OK, 1 row affected (0.00 sec) mysql > insert provin1 (pname) values ('C'); Query OK, 1 row affected (0.00 sec) mysql > select * from provin1 +-+ | id | pname | +-+-+ | 1 | A | 3 | b | | 5 | C | +-+-+ 3 rows in set (0.00 sec)

Insert data into test0003

Mysql > insert test0003 (username,pid) values ('tom',3); Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values (' lichao',5); Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values ('chenchen',1); Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values (' cat',3) Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values ('nihao',7); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`chenchen`.`test0003`, CONSTRAINT`test0003 _ ibfk_ 1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE)

We see that there is an error when inserting pid = 7. The reason for the error is that there is no 7 id in the provin1 table corresponding to pid, so he will report an error. Let's take a look at the data in the test0003 table.

Mysql > select * from test0003;+----+ | id | username | pid | +-- + | 1 | tom | 3 | 3 | lichao | 5 | 5 | chenchen | 1 | 7 | cat | 3 | +-+ 4 rows in set (0.01sec)

So let's remove the field with an id of 3 in provin1 to see the changes in the test0003 table.

Mysql > delete from provin1 where id = 3bot query OK, 1 row affected (0.00 sec) mysql > select * from provin1;+----+-+ | id | pname | +-- +-+ | 1 | A | 5 | C | +-+-+ 2 rows in set (0.00 sec) mysql > select * from test0003 +-+ | id | username | pid | +-+ | 3 | lichao | 5 | 5 | chenchen | 1 | +-+ 2 rows in set (0.00 sec)

We see that the corresponding field with id 3 in the test0003 table disappears after removing the data with id 3 in provin1, which verifies the cascade function in the foreign key bondage.

Cascade: rows that are deleted or updated from the parent table and automatically deleted or matched to the new child table.

Below I will sort out other constraints and mysql table operation documents, one after another update, this article is forbidden to reprint, personal summary is not easy, please understand

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.