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

I. CREATE TABLE option

1. Specify column options when defining columns

1) DEFAULT: defines the default value of the column

When a new row is inserted into the table and the column is not explicitly assigned, the default value is automatically obtained if the default value for the column is defined, or null if not.

Mysql > create table people-> (- > id int not null primary key,-> name varchar (20) not null,-> sex char (1) default'm'- >); mysql > insert into people (id,name) values (1); mysql > insert into people values (2) Li Si','f'); mysql > select * from people +-+ | id | name | sex | +-+ | 1 | Zhang San | m | | 2 | Li Si | f | +-+

Of course, you can also explicitly assign default values to columns using the DEFAULT keyword in INSERT and UPDATE statements:

Mysql > insert into people values (default); mysql > update people set sex=default where id=2;mysql > select * from people;+----+ | id | name | sex | +-- + | 1 | Zhang San | m | 2 | Li Si | m | 3 | Wang Wu | m | +-+

The function default (column) can get the default value of a column:

Mysql > select default (sex) from people;+-+ | default (sex) | +-+ | m | | m | m | +-+

2) comment: used to add comments to the column, up to 255characters, and the comments will be saved to the data dictionary.

Create a table stu _ comment with column comments

Mysql > create table stu_comment-> (- > id int not null primary key-> comment 'student ID',-> name varchar (20) not null-> comment 'name'->)

Query comment information from data dictionary

Mysql > select column_name,column_comment-> from information_schema.columns-> where table_name='stu_comment' +-+-+ | column_name | column_comment | +-+-+ | id | Student number | | name | name | +-+-+

2. Table options in the CREATE TABLE statement

1) engine: specify the storage engine used by the table

Storage engine: determines how data is stored and accessed, and how transactions are handled

MySQL allows you to use a different storage engine for each table, or the default storage engine if no storage engine is specified in the create table statement.

Mysql > show engines; # query all supported storage engines

Mysql > CREATE TABLE sexes (sex char (1) NOT NULL) ENGINE = INNODB

Note: the storage engine is a key point, which we will explain in more detail later.

2) auto_increment: determines what the first value of the incremented column is when the first row is inserted into the table

3) comment: add comments to the table

Mysql > create table mycomm (num int) comment 'test table'; mysql > select table_name,table_comment-> from information_schema.tables-> where table_name='mycomm' +-+ | table_name | table_comment | +-+-+ | mycomm | Test table | +-+-+

II. CREATE TABLE constraint

Purpose: you can define constraints for columns (constraint)

The main constraint is to prevent illegal data from entering the table and ensure the correctness and consistency of the data (collectively referred to as data integrity).

Constraints can also prevent a table from being deleted.

Note:

1) constraints in MySQL are saved in information_schema.table_constraints, and constraint information can be queried through this table.

2) time to define constraints: use create table statement, use alter table statement.

Commonly used types of constraints: 5

① not null: a non-null constraint that specifies that a column is not empty

② unique: a unique constraint that specifies that the data of a column and a combination of columns cannot be repeated

③ primary key: primary key constraint that specifies that the data of a column cannot be duplicated and unique

④ foreign key: foreign key that specifies that the column record belongs to one record in the main table and references another piece of data

⑤ check: check, specify an expression to validate the specified data

Syntax for constraint definition:

Column level: CREATE TABLE table_name (column_name data_type [[NOT NULL] | [UNIQUE [KEY] | PRIMARY KEY] | CHECK (expr)], …) Table level: CREATE TABLE table_name (column_name data_type [NOT NULL], column_name data_type [not null], … , [CONSTRAINT constraint_name] PRIMARY KEY (col_name,...) | [CONSTRAINT constraint_name] unique (col_name,...) | [CONSTRAINT constraint_name] foreign KEY (col_name) REFERENCES tbl_name (index_col_name) | check (expr)

Note:

1) NOT NULL constraints can only be defined at the column level, and constraints acting on multiple columns can only be defined at the table level, such as compound primary key constraints.

2) Foreign key constraints cannot be defined at the column level and cannot be named by MySQL (except NOT NULL)

3) constraints defined at the table level can be named (except for CHECK constraints)

1. Not null non-null constraint

Purpose: used to ensure that the value of the current column is not empty.

Mysql > create table temp_nn (id int not null); constraints directly affect DML operations mysql > insert into temp_nn values (1); Query OK, 1 row affected (0.00 sec) mysql > insert into temp_nn values (null); ERROR 1048 (23000): Column 'id' cannot be null columns with non-null constraints are not allowed to have null values

Note: non-null constraints can only appear on the columns of a table object.

2. Unique unique constraint

1. The only constraint is that the column or column combination of the specified table cannot be repeated to ensure the uniqueness of the data. The constrained column is not allowed to have duplicate values.

two。 Unique constraints do not allow duplicate values, but can be multiple null

3. The same table can have multiple unique constraints, multiple column combinations of constraints

Mysql > create table temp_uk (- > id int not null unique,-> name varchar (20) unique); mysql > insert into temp_uk values (1); mysql > insert into temp_uk values (2); ERROR 1062 (23000): Duplicate entry'a 'for key' name'mysql > insert into temp_uk values (2); mysql > insert into temp_uk values (3); mysql > select * from temp_uk +-+ | id | name | +-+-+ | 2 | NULL | | 3 | NULL | | 1 | a | +-+-+

It can be seen that columns with uniqueness constraints can have multiple null values because null null

4. When creating a unique constraint, if you do not give the unique constraint name, the default is the same as the column name

5. Unique constraints can be created not only in one table, but also in multiple tables at the same time.

Mysql > create table test (- > id int not null,-> name varchar (20),-> password varchar (16),-using table-level constraint syntax-> constraint uk_name_pwd unique (name,password)->) # indicates that the user name and password combination cannot be repeated in Query OK. 0 rows affected (0.08 sec) queries the data dictionary to view the information of unique key constraints: mysql > select * from information_schema.table_constraints-> where table_name='test'.

3. Primary key primary key constraint

Primary key = not null + unique

Primary key: used to uniquely identify each row in the table (usually of type integer or string)

Columns with primary key constraints are not allowed to have null values and duplicate values are not allowed

At most one primary key is allowed per table (a federated primary key can be defined), and the primary key name is always PRIMARY.

Mysql > create table temp_pk (- > id int primary key); mysql > insert into temp_pk values (1), (2); mysql > insert into temp_pk values (1); ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'mysql > update temp_pk set id=1 where id=2;ERROR 1062 (23000): Duplicate entry' 1' for key 'PRIMARY'mysql > insert into temp_pk values (null); ERROR 1048 (23000): Column' id' cannot be null

!! Give the primary key a new name, but in the data dictionary, the primary key name still shows primary

Union primary key (uniquely identifies a row with several columns)

Mysql > create table temp_pk (- > id int,-> name varchar (20),-> constraint pk_id_name primary key (id,name)->); Query OK, 0 rows affected (0.06 sec) mysql > desc temp_pk +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | NULL | | name | varchar (20) | NO | PRI | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > insert into temp_pk values (1) 'Zhang San') Query OK, 1 row affected (0.00 sec) mysql > insert into temp_pk values; Query OK, 1 row affected (0.01 sec) mysql > insert into temp_pk values (1 min Wang Wu); Query OK, 1 row affected (0.00 sec) mysql > insert into temp_pk values (1 min Zhang San); ERROR 1062 (23000): Duplicate entry'1-Zhang San 'for key' PRIMARY'mysql > select * from temp_pk +-+-- +-+ | id | name | +-+-+ | 1 | Zhang San | 1 | Wang Wu | | 2 | Li Si | +-+-+ 3 rows in set (0.00 sec)

4. Foreign key foreign key constraint

Foreign key constraints:

A referential integrity constraint that ensures referential integrity between one or two tables. A foreign key is a reference relationship between two fields built on a table or between two fields of two tables.

Note:

1) the value of a column with foreign key constraints cannot be given casually, but must satisfy the value of the primary key referenced by the foreign key.

2) multiple foreign keys can be defined in a table

3) Foreign key columns can be given null values by default.

By definition, a foreign key must refer to a primary key or unique key, which is usually in another table or can be the primary key of this table (the latter is called "self-reference").

Father-son table:

The table where the foreign key is located is called the child table and the slave table.

The table where the primary key referenced by the foreign key is called the parent table and the primary table.

Note: the parent-child table is relative. Table a can be a child of table b, but it can also be the parent of table c

Example: create a foreign key constraint

/ / create parent table mysql > create table dept (- > deptid int,-> dname varchar (20),-> constraint dept_deptid_pk primary key (deptid)->); mysql > insert into dept (deptid,dname) values (10 'Marketing Department'); mysql > insert into dept (deptid,dname) values (20 'sales Department') / / create a child table (create foreign key constraints at the table level) mysql > create table emp (- > id int,-> name varchar (20),-> deptid int,-> constraint emp_id_pk primary key (id),-> constraint emp_deptid_fk foreign key (deptid)-> references dept (deptid)->)

Query the data dictionary to view information about foreign key constraints:

The foreign key constraint is named emp_deptid_fk when the child table is created above

If you do not name the foreign key constraint, the default name is the table name _ ibfk_n, n is an integer, starting with 1

At this point, the deptid column (foreign key constraint) in the emp table is restricted by the dept primary table

Mysql > insert into emp (id,name,deptid) values (1 row affected (0.00 sec) mysql > insert into emp (id,name,deptid) values (2) Li Si, 10); Query OK, 1 row affected (0.00 sec) mysql > insert into emp (id,name,deptid) values (3) Wang Wu, 50) # insert main table ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_ fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`)) mysql > update emp set deptid=30 where id=1 # data not available in deptid column of # update master table ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_ fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`)) the update update operation rules for foreign keys are as follows... Mysql > delete from dept where deptid=10 # delete parent table ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`)) default deletion rule for foreign keys: when deleting rows in the parent table, if there are child rows in the child table that depend on the deleted parent row, then deletion is not allowed And throw an exception (default to use on delete restrict or on delete no action options for foreign keys)

Foreign key reference definition:

Reference_definition: REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION

When defining foreign key constraints, you can change the default deletion rules for foreign keys by using the on delete cascade or on delete set null options:

① ON DELETE CASCADE: cascading deletes. When deleting a row in a parent table, if there are child rows in the child table that depend on the deleted parent row, delete it along with the child row (dangerous! )

② ON DELETE SET NULL: when deleting a row in a parent table, if there are child rows in the child table that depend on the deleted parent row, then the foreign key column of the child row is set to null instead.

……

Mysql > create table emp (- > id int,-> name varchar (20),-> deptid int,-> constraint emp_id_pk primary key (id),-> constraint emp_deptid_fk foreign key (deptid)-> references dept (deptid)-> on delete cascade->);

5. Check constraint

MySQL can use check constraints, but check constraints have no effect on data validation.

Check constraints can be used in Oracle, which has a corresponding effect.

Mysql > create table test_ck (- > id int check (id > 0)->); mysql > insert into test_ck values (- 100); mysql > select * from test_ck;+-+ | id | +-+ |-100 | +-+

The CHECK clause is parsed but ignored by all storage engines .

When defining database columns, you can use ENUM (enumeration, enumeration) and SET (collection) types: flexible implementation of CHECK constraints

The difference between the two is:

With ENUM, only one value can be selected

With SET, you can select multiple values

Values in both ENUM and SET must be of type string.

1. Enum enumerated types

Note:

When storing ENUM values internally, MYSQL gives each value in ENUM a sequential number: the first value has a sequential number of 1, the second value has a sequential number of 2, and so on. Use these sequential numbers when sorting or comparing ENUM.

Mysql > select * from student order by sex;+----+ | id | name | sex | +-- + | 5 | Wang Wu | NULL | 1 | Zhang San | M | | 2 | Li Si | F | +-+

2. Set type: because multiple values can be enumerated, it is often used in table column constraints.

Mysql > create table team (- > teamno int not null,-> division set ('north','south','east','west')->); mysql > insert into team values (1 repartee west'); mysql > insert into team values (2 recorder west, South'); mysql > insert into team values (4 recordNull); mysql > insert into team values (3 recorder); ERROR 1265 (01000): Data truncated for column 'division' at row 1mysql > select * from team +-+-+ | teamno | division | +-+-+ | 1 | west | | 2 | south,west | | 4 | NULL | +-+-+

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.