Mysql 2 -- create a data table

Mysql learning 2 -- CREATE a data TABLE in Mysql, CREATE a data TABLE using the SQL statement CREATE TABLE: CREATE TABLE Name (attribute name data type [integrity constraints], attribute name data type [integrity constraints], and attribute name data type [integrity constraints],); "table name" indicates the name of the data table to be created, the "property name" parameter indicates the name of the field in the table. The "Data Type" parameter specifies the Data Type of the field. Note: before creating a data TABLE, you must USE the USE statement to select a database in the format of; USE Database name instance 1: [SQL] CREATE TABLE example (id INT, name VARCHAR (20 ), sex BOOLEAN); set the table's primary key 1. When a single-field primary key is composed of a field, you can directly add the primary key after this field to set the primary key: attribute name data type primary key instance 2: [SQL] CREATE TABLE example1 (stu_id INT PRIMARY KEY, stu_name VARCHAR (20), stu_sex BOOLEAN ); 2. A multi-field primary key is composed of multiple attributes. After the attribute is defined, the primary key is set as follows: instance 3: [SQL] CREATE TABLE example2 (stu_id INT, course_id INT, grade FLOAT, Primary key (stu_id, course_id); 3. Set the foreign KEY of the table to set the foreign KEY. The foreign KEY must depend on the existing primary key of the parent table in the database, and the foreign KEY can be null. The foreign key is used to establish the association between the table and its parent table. When deleting a message in the parent table, the corresponding information in the subtable must also be changed. The basic syntax for setting FOREIGN keys is: constraint foreign key alias foreign key (attribute 1.1, attribute 1.2, and attribute 1.n) REFERENCES table name (attribute 2.1, attribute 2.2,, and attribute 2.n), the "foreign key alias" parameter is the foreign key code, and the "property 1" parameter list is the foreign key set in the subtable, the "table name" parameter refers to the name of the parent table, and the "attribute 2" parameter list is the primary key of the parent table. Example 4: [SQL] CREATE TABLE example3 (id INT PRIMARY KEY, stu_id INT, course_id INT, CONSTRAINT c_fk FOREIGN KEY (stu_id, course_id) REFERENCES example2 (stu_id, course_id )); explanation: The id field is the primary key, the stu_id and course_id fields are the foreign key, c_fk is the foreign key alias, and example2 is the parent table of example3, the stu_id and course_id fields in example3 depend on the corresponding fields in example2. 4. Set non-NULL constraints for a table. When creating a table, add the not null constraint condition to some special fields in the table. The basic syntax is as follows: attribute name data type not null instance 5: [SQL] CREATE TABLE example4 (id INT NOT NULL PRIMARY KEY, name VARCHAR (20) NOT NULL, stu_id INT, CONSTRAINT d_fk foreign key (stu_id) REFERENCES example1 (stu_id); 5. Set the uniqueness CONSTRAINT of the table. uniqueness means that the values of this field in all records cannot appear repeatedly. When creating a table, you must add a UNIQUE constraint to some special fields in the table. Basic Syntax: attribute name data type UNIQUE instance 6: [SQL] CREATE TABLE example5 (id INT PRIMARY KEY, stu_id INT UNIQUE, name VARCHAR (20) NOT NULL ); 6. Setting the attribute value of a table to automatically add AUTO_INCREMENT is a special constraint in the Mysql database. It is mainly used to automatically generate a unique ID for the new records inserted in the table. Only one field in a table can use the AUTO_INCREMENT constraint, and the field must be a part of the primary key. By default, the field value starts from 1. Basic Syntax: property name data type AUTO_INCREMENT instance 7: [SQL] CREATE TABLE example6 (id INT PRIMARY KEY AUTO_INCREMENT, stu_id INT UNIQUE, name VARCHAR (20) NOT NULL ); 7. Set the DEFAULT value of the table attribute through the DEFAULT keyword. Basic Syntax: property name data type DEFAULT instance 8: [SQL] CREATE TABLE example7 (id INT PRIMARY KEY AUTO_INCREMENT, stu_id INT UNIQUE, name VARCHAR (20) NOT NULL, english VARCHAR (20) DEFAULT 'zero ', Math float default 0, Computer float default 0); Author: junjieguo