Basic operation of MySQL data table

I haven't combed the MySQL basic command for a long time. Today the time to tidy up, though very simple ... But there's still a need to solidify the base drip.

1. Create a table: Use the database database_name before creating the name table ();

Example: Create an Employee table Tb_employee1, as shown in the following table

Field name	Data type	remarks
ID	Int (one)	Employee number
name	varchar	Employee name
Depld	Int (one)	Department number
Salary	float	wages

Mysql> CREATE DATABASE aa;mysql> use aa;mysql> CREATE TABLE tb_employee1 (ID int (one), name varchar), DEPLD int (1 1), salary float);

2. PRIMARY KEY constraint: The primary key is also called the main code, which is a combination of one or more columns in a table. A PRIMARY key constraint requires that the data for the primary key column be unique and cannot be empty. A primary key uniquely identifies a record in a table, can be combined with a foreign key to define relationships between different data tables, and can speed up database queries. There are two types of primary keys: Single Chini and multi-field federated primary keys.

(1) Single field primary key

Mysql> CREATE TABLE tb_employee1 (ID int (one) primary key,name varchar), DEPLD Int (one), salary float);mysql> Create Table tb_employee1 (id int (one), name varchar (+), Depld Int (one), Salary float,primary key (ID));

(2) Multi-field federated primary key

Mysql> CREATE TABLE tb_employee1 (ID int (one), name varchar (+), Depld Int (one), Salary float,primary key (Id,name));

3. FOREIGN KEY constraint: A foreign key is used to establish a connection between two tables, which can be one or more columns. A table can have one or more foreign keys. Foreign keys correspond to referential integrity, a table's foreign keys can be null, and if not NULL, each foreign key must be equal to a value of the primary key in the other table.

Here are a few concepts. is a field in a table that may not be the primary key for this table, but one that corresponds to the primary key of another table. The primary function of the foreign key is to guarantee the integrity of the data reference, and after the foreign key is defined, it is not allowed to delete the primary key that has an association relationship in another table. For example: The primary key ID for some tables, with a key deptld in table TB associated with this ID.

Primary table (parent table): For two tables with associated relationships, the table that contains the primary key in the related field is the primary table.

From table (from table): For two tables with associated relationships, the table in which the foreign key in the related field is located is from the table.

Example: Define a data table tb_employee5, and create a foreign key constraint in the table

Create a departmental table TB_DEPT1, table structure as shown in the following table

Field name	Data type	remarks
ID	Int (one)	Department number
name	varchar	Department name
location	" varchar "(+)	Department location

Mysql> CREATE TABLE tb_dept1 (ID int (one) primary key,name varchar (), location varchar (50)), define data table Tb_employee5, Have its deptld field associated with the primary key of the TB_DEPT1 as a foreign key id:mysql> CREATE TABLE tb_employee5 (ID int (one) primary key,name varchar (+), Deptld int (one), salary float,constraint fk_emp_dept1 foreign key (DEPTLD) references tb_dept1 (ID));

4. Non-null constraint (NOT NULL): means that the value of the field cannot be empty. For fields that use a non-null constraint, the database system will error if the user does not specify a value when adding data. Syntax rule: Field name data type NOT NULL

Example: Defining a data table tb_employee6, specifying that the employee's name cannot be empty

Mysql> CREATE TABLE tb_employee6 (ID int (one) primary key,name varchar (+) not null,deptld int (one), salary float);

5. Uniqueness constraint: This column is required to be unique, allowed to be empty, but only one null value can appear. Uniqueness constraints ensure that duplicate values cannot be present in one or more columns.

(1) Specify a unique constraint directly after the column is defined

Syntax rules: Field name data type unique

Mysql> CREATE TABLE tb_dept2 (ID int (one) primary key,name varchar () unique,location varchar (50));

(2) Specify a UNIQUE constraint after all columns have been defined

Syntax rules: Constraint constraint name unique (field name)

Mysql> CREATE TABLE TB_DEPT3 (ID int (one) primary key,name varchar (), location varchar (+), constraint sth unique (name) );

Unique and primary key differences: A table can have more than one field declared as unique, but only one primary key declaration; A column declared as primary key does not allow null values, but a field declared as unique allows the existence of a null value.

6. Default constraint: Specifies the default value for a column. such as male classmates more, sex can default to "male". If you do not assign a value to this field when inserting a new record, the system automatically assigns the field a value of male.

Syntax rules: Field name data type default defaults

Example:mysql> CREATE TABLE tb_employee7 (ID int (one) primary key,name varchar) not null,deptld int (one) default 1111,salary float);

7. Set the property value of the data table to increase automatically: in a database application, you often want to automatically generate a field's primary key value each time you insert new data. You can add the Auto_increment keyword to the table primary key to implement it. In MySQL, by default auto_increment has an initial value of 1, and a field automatically adds 1 for each new record. A table can have only one field with auto_increment constraints, and each field must be part of the primary key. The Auto_increment constrained field can be any integer type (tinyint/samllint/int/bigint).

Syntax rules: Field name data type Auto_increment

Example: Defining a data table Tb_employee8, specifying an automatic increase in employee labeling

Mysql> CREATE TABLE Tb_employee8 (ID int (one) primary key auto_increment,name varchar (+) not null,deptld int (one), salary float);

mysql> INSERT INTO Tb_employee8 (name,salary) VALUES (' Lucy ', ' N '), (' lii ', ' + '), (' Cai ', 20000); Query OK, 3 Rows Affected (0.00 sec) Records:3  duplicates:0  warnings:0mysql> select * FROM tb_employee8;+---- +------+--------+--------+| ID | name | Deptld | Salary |+----+------+--------+--------+|  1 | Lucy |   NULL |   | |  2 | LII  |   NULL |    | |  3 | Cai  |   NULL |  20000 |+----+------+--------+--------+3 rows in Set (0.00 sec)

8. View the structure of the data table

View Table basic structure: Describe table name or desc table name

Mysql> desc tb_employee8;+--------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          |+--------+-------------+------+-----+---------+----------------+| ID     | int     | NO   | PRI | NULL    | auto_increment | | name   | varchar (25) | NO   |     | NULL    |                | | deptld | int (one)     | YES  |     | NULL    |                | | | salary | float       | YES  |     | NULL    |                | +--------+-------------+------+-----+---------+----------------+

(1) Null: Indicates whether the column can store NULL values

(2) Key: Indicates whether the column has been indexed. The PRI is part of the primary key of the column; uni indicates that the column is part of a unique index; Mul indicates that a given value in a column is allowed to occur more than once.

9. View Table Detail Structure

Syntax rules: Show CREATE table name \g

Mysql> Show CREATE TABLE tb_employee8\g*************************** 1. Row ***************************       table:tb_employee8create table:create Table ' tb_employee8 ' (  ' id ' int ') not Null auto_increment,  ' name ' varchar (+) NOT NULL,  ' deptld ' int (one) default null,  ' salary ' float default null ,  PRIMARY KEY (' id ')) engine=myisam auto_increment=4 DEFAULT charset=latin11 row in Set (0.00 sec)

10. Modify the data table: Modify the data table structure that already exists in the database. Common modifications to the table are: Modify the table name, modify the field data type or field name, add and remove fields, modify the position of the field, change the table's storage engine, delete the table's foreign key constraints, and so on.

(1) Modify table name

ALTER TABLE old table name rename new table name example:mysql> ALTER TABLE TB_DEPT3 rename Tb_deptment3;

(2) Modifying field data types

ALTER TABLE table name modify field name data type example:mysql> ALTER TABLE TB_DEPT1 modify name varchar (30);

(3) Add a field

ALTER TABLE name add new field name data type example 1:mysql> ALTER TABLE TB_DEPT1 add Managerld Int (20) (Field without integrity constraint) example 2:mysql> ALTER TABLE TB_DEPT1 add column1 varchar (n) not null; (add constraint) example 3:mysql> ALTER TABLE TB_DEPT1 add Column2 int (one) first; (Add to column) Example 4 :mysql> ALTER TABLE TB_DEPT1 add Column3 int (one) after name; (added to column after name)

(4) Delete a field

ALTER TABLE name drop FIELD name Example:mysql> ALTER TABLE TB_DEPT1 drop COLUMN3;

(5) Modifying field sorting

ALTER TABLE name modify field 1 data type first after Field 2 example 1:mysql> ALTER TABLE TB_DEPT1 modify ID int (one) first; example 2:mysql> alter Table TB_DEPT1 Modify Column2 int (one) after Column1;

(6) Changing the data engine of a table

ALTER TABLE name engine= changed storage engine example:mysql> ALTER TABLE TB_DEPT1 ENGINE=INNODB;

(7) Delete a foreign key constraint for a table

ALTER TABLE name drop FOREIGN key FOREIGN KEY constraint name

(8) Modifying field names

Mysql> ALTER TABLE Customers change c_contact c_phone varchar (50);

11. Delete Data Sheet

Table 1 of DROP table 2 ...

Delete the main table associated with another table: delete the Word table first, then delete the primary table (or cancel the foreign key constraint, delete the main table)

Basic operation of MySQL data table