Mysql table and constraint operations, mysql constraint operations

Mysql table and constraint operations, mysql constraint operations

By default, the Data Engine and character set are used to create a table. For example, to create a student table, the Code is as follows:

Create table students (id int unsigned zerofill auto_increment primary key, name varchar (20) not null, -- the field is not allowed to be null sex char (1 ));


--------------------------- Automatically add the Data Engine and Character Set ------------------------
Create table students (id int unsigned zerofill auto_increment primary key,
Name varchar (20) not null,
Sex char (1)
) Engines = innodb default charset = utf8;

• Insert and replace)

1. The insert command inserts a record directly into the table.

Insert into students (sname, sex) values ('orna ', 'male'); insert into students (sname, sex) values ('lisi', 'male '); insert into students (sname, sex) values ('wangw', 'male'); mysql> select * from students; + ------------ + -------- + ------ + | id | sname | sex | tid | + ------------ + -------- + ------ + | 0000000001 | orna | male | NULL | 0000000002 | lisi | male | NULL | 0000000003 | wangwu | male | NULL | + ------------ + -------- + ------ + 3 rows in set (0.00 sec)

2. When the replace command inserts a record, it determines whether the primary key is the same. If the primary key is the same, it is modified. Otherwise, a new record is inserted.

Replace into students (id, sname, sex) values (2, 'luscy', 'femal'); replace into students (id, sname, sex) values (20, 'hangsan', 'male'); mysql> select * from students; + -------------- + ---------- + ------ + | id | sname | sex | tid | + ------------ + ---------- + ------ + | 0000000001 | orna | male | NULL | 0000000002 | luscy | female | NULL | 0000000003 | wangwu | male | NULL | 0000000020 | zhangsan | male | NULL | + ------------ + -------- + ------ + 4 rows in set (0.00 sec)

　　

• Create a table using the select result set

Mysql> select * from students; + ------------ + ------- + ------ + | id | name | sex | + ------------ + ------- + ------ + | 0000000001 | luscy | female | 0000000002 | lisi | male | 0000000003 | orna | male | + ------------ + ------- + ------ + 3 rows in set (0.00 sec) mysql> create table stu select * from students; Query OK, 3 rows affected (0.33 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from stu; + ------------ + ------- + ------ + | id | name | sex | + ------------ + ------- + ------ + | 0000000001 | luscy | female | 0000000002 | lisi | male | 0000000003 | orna | male | + ------------ + ------- + ------ + 3 rows in set (0.00 sec)

• Modify Table Name

You can modify the table name in two ways.

mysql> rename table stu to st;Query OK, 0 rows affected (0.17 sec)mysql> alter table st rename to s;Query OK, 0 rows affected (0.23 sec)

• Delete table

Drop table name;

Drop table 1, table 2, table 3.

• Modify the column name of a table

Note the following when modifying fields:

Mysql> alter table s change name sname varchar (30); Query OK, 3 rows affected (1.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from s; + ------------ + ------- + ------ + | id | sname | sex | + ------------ + ------- + ------ + | 0000000001 | luscy | female | 0000000002 | lisi | male | 0000000003 | orna | male | + ------------ + ------- + ------ + 3 rows in set (0.01 sec)

　　

• Limit the value range of a field

Create table students (id int unsigned zerofill auto_increment primary key, name varchar (20) not null, sex enum ('male', 'female ') -- specifies the value range. Enum and set keyword agree); insert into students (name, sex) values ('orna', 'male '); -- data is successfully inserted -------------------------------- the data is inserted incorrectly. Why mysql> insert into students (name, sex) values ('orna', 'uncert'); ERROR 1265 (01000 ): data truncated for column 'sex' at row 1 mysql> select * from students; + ------------ + ------ + | id | name | sex | + ------------ + ------ + | 0000000001 | orna | male | + ------------ + ------ + 1 row in set (0.00 sec)

• Primary, external, and unique

A table can have only one primary key, but multiple fields can be used to create a primary key combination. There can be multiple foreign keys and unique constraints.

There are two ways to create a primary key:
-- Specify the primary key name alter table s add constraint pk primary key (id); -- do not specify the primary key name alter table s add constraint primary key (id );

2. Delete the primary key

Mysql> desc s; + ------- + fields + ------ + ----- + ------------ + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ----------------------- + ------ + ----- + ------------ + ------- + | id | int (10) unsigned zerofill | NO | PRI | 0000000000 | sname | varchar (30) | YES | NULL | sex | set ('male', 'female ') | YES | NULL | + ------- + ------------------------- + ------ + ----- + ------------ + ------- + 3 rows in set (0.00 sec) mysql> alter table s drop primary key; Query OK, 3 rows affected (0.82 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc s; + ------- + fields + ------ + ----- + ------------ + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ----------------------- + ------ + ----- + ------------ + ------- + | id | int (10) unsigned zerofill | NO | 0000000000 | sname | varchar (30) | YES | NULL | sex | set ('male', 'female ') | YES | NULL | + ------- + --------------------------- + ------ + ----- + ------------ + ------- + 3 rows in set (0.00 sec)

3. Add Fields

When adding a field, you can use the first and after keywords, but there is no before keyword.

Mysql> alter table students add address varchar (50) not null after sex; Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; + --------- + bytes + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + --------- + ----------------------- + ------ + ----- + ----------- + ------------------ | id | int (10) unsigned zerofill | NO | PRI | NULL | auto_increment | name | varchar (20) | NO | NULL | sex | set ('male', 'female ') | YES | NULL | address | varchar (50) | NO | NULL | + --------- + ------------------------- + ------ + ----- + --------- + ------------------ + 4 rows in set (0.00 sec)

4. delete a field

Mysql> select * from s; + ------------ + ------- + ------ + | id | sname | sex | + ------------ + ------- + ------ + | 0000000001 | luscy | female | 0000000002 | lisi | male | 0000000003 | orna | male | + ------------ + ------- + ------ + 3 rows in set (0.00 sec) mysql> alter table s drop column sex; Query OK, 0 rows affected (0.63 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc s; + ------- + fields + ------ + ----- + ------------ + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ----------------------- + ------ + ----- + ------------ + ------- + | id | int (10) unsigned zerofill | NO | 0000000000 | sname | varchar (30) | YES | NULL | + ------- + --------------------------- + ------ + ----- + ------------ + ------- + 2 rows in set (0.00 sec)

2. Add a unique constraint

-- Adds a unique constraint when creating a table. Create table students (id int unsigned zerofill auto_increment primary key, sname varchar (20) not null unique, sex set ('male', 'female '), tid int unsigned) engine = innodb default charset = utf8; -- adds a unique constraint after creating a table. Alter table students add constraint uk unique (sname); mysql> desc students; + ------- + upper + ------ + ----- + --------- + -------------- + | Field | Type | Null | Key | Default | Extra | + ------- + ----------------------- + ------ + ----- + --------- + -------------------- + | id | int (10) unsigned zerofill | NO | PRI | NULL | auto_increment | sname | varchar (20) | NO | UNI | NULL | sex | set ('male', 'female ') | YES | NULL | tid | int (10) unsigned | YES | NULL | + ------- + --------------------------- + ------ + ----- + --------- + ------------------ + 4 rows in set (0.01 sec)

-- Delete a unique constraint.

Mysql> alter table students drop index uk;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

3. added foreign key constraints

Alter table students add constraint fk foreign key (tid) references teachers (id );
-- Alter table students add constraint fk foreign key (tid) references teachers (id) on delete set null; indicates that the foreign key value of the current record is changed to null when the record referenced by the foreign key is deleted.
-- Alter table students add constraint fk foreign key (tid) references teachers (id) on delete cascade; delete the record referenced by the foreign key and delete the current record at the same time.
-- Alter table students add constraint fk foreign key (tid) references teachers (id) on delete set null on update cascade; indicates that the foreign key value of the current record is repaired when the record referenced by the foreign key is deleted.
-- Change to null, or modify the reference record and modify the foreign key reference field. Mysql> desc students; + ------- + upper + ------ + ----- + --------- + -------------- + | Field | Type | Null | Key | Default | Extra | + ------- + ----------------------- + ------ + ----- + --------- + -------------------- + | id | int (10) unsigned zerofill | NO | PRI | NULL | auto_increment | sname | varchar (20) | NO | NULL | sex | set ('male', 'female ') | YES | NULL | tid | int (10) unsigned | YES | MUL | NULL | + ------- + --------------------------- + ------ + ----- + --------- + ------------------ + 4 rows in set (0.00 sec)

4. Delete foreign key constraints

alter table students drop foreign key fk;alter table students drop index fk;

5. Create an index

mysql> create index sname on students(sname desc);Query OK, 0 rows affected (0.27 sec)Records: 0  Duplicates: 0  Warnings: 0
--alter table students add index(sname desc);

6. delete an index

alter table students drop index sname;
--drop index sname on students;