Mysql-5-basic operations for data tables, mysql-5-basic operations

Mysql-5-basic operations for data tables, mysql-5-basic operations
1. create a table: Before using database database_name, create table name ();

For example, create an employee table tb_employee1 with the following structure:

Field name	Data Type	Remarks
Id	Int (11)	Employee ID
Name	Varchar (25)	Employee name
Depld	Int (11)	Department ID
Salary	Float	Salary

mysql> create database aa;mysql> use aa;mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float);

 

2. primary key constraint: The primary key, also known as the primary code, is a combination of one or more columns in the table. The primary key constraint requires that the data of the primary key column be unique and cannot be blank. A primary key uniquely identifies a record in a table. It can be used with foreign keys to define the relationship between different data tables and accelerate database query. There are two types of primary keys: Single-field primary keys and multi-field primary keys.

(1) Single-field primary key

mysql> create table tb_employee1(id int(11)primary key,name varchar(25),depld int(11),salary float);mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float,primary key(id));

(2) combining multiple fields with primary keys

mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float,primary key(id,name));

 

3. Foreign key constraint: A foreign key is used to establish a link between two tables. It can be one or multiple columns. A table can have one or more foreign keys. The foreign key corresponds to the integrity of the reference. The foreign key of a table can be null. If it is not null, each foreign key must be equal to a value of the primary key of another table.

The following describes several concepts. Is a field in the table. It can be not the primary key of the table, but corresponds to the primary key of another table. The foreign key is mainly used to ensure the integrity of data reference. After defining the foreign key, it is not allowed to delete the primary key with an association relationship in another table. For example, the primary key id of some tables. In table tb, a key deptld is associated with this id.

Master table (parent table): For two associated tables, the table where the primary key of the relevant field is located is the master table.

Slave table: For two associated tables, the table where the foreign key of the related field is located is the slave table.

For example, define the data table tb_employee5 and create a foreign key constraint in the table.

Create a department table tb_dept1. The table structure is as follows:

Field name	Data Type	Remarks
Id	Int (11)	Department ID
Name	Varchar (22)	Department name
Location	Varchar (50)	Department location

Mysql> create table tb_dept1 (id int (11) primary key, name varchar (22), location varchar (50); define the data table tb_employee5, associate its deptld field with the primary key id of tb_dept1 as the foreign key: mysql> create table tb_employee5 (id int (11) primary key, name varchar (25 ), deptld int (11), salary float, constraint fk_emp_dept1 foreign key (deptld) references tb_dept1 (id ));

 

4. non-null constraint (not null): The field value cannot be blank. For fields that use non-null constraints, if you do not specify a value when adding data, the database system reports an error. Syntax rule: field name data type not null

For example, define the data table tb_employee6. The name of the specified employee cannot be blank.

mysql> create table tb_employee6(id int(11)primary key,name varchar(25)not null,deptld int(11),salary float);

 

5. uniqueness constraint: This column must be unique and can be null, but only one null value can appear. The Uniqueness constraint ensures that duplicate values are not allowed for one or more columns.

(1) Specify the unique constraint after defining the column

Syntax Rules: field name data type unique

mysql> create table tb_dept2(id int(11)primary key,name varchar(22)unique,location varchar(50));

(2) Specify the unique constraint after defining all columns

Syntax rule: constraint name unique (field name)

mysql> create table tb_dept3(id int(11)primary key,name varchar(22),location varchar(50),constraint sth unique(name));

Difference between unique and primary key: A table can have multiple fields declared as unique, but only one primary key can be declared. Columns declared as primary key cannot have null values, however, fields declared as unique allow the existence of null values.

 

6. Default constraint: Specify the default value of a column. If there are many male students, the default gender is "male ". If this field is not assigned a value when a new record is inserted, the system automatically assigns a value to male.

Syntax rule: field name data type default Value

Example: mysql> create table tb_employee7 (id int (11) primary key, name varchar (25) not null, deptld int (11) default 1111, salary float );

 

7. Set the attribute value of the data table to automatically increase: In database applications, the system will automatically generate the primary key value of the field each time new data is inserted. You can add the auto_increment keyword to the table's primary key. In mysql, the initial value of auto_increment is 1 by default. Each new record is added, and 1 is automatically added to the field. only one field in a table can use the auto_increment constraint, and each field must be a part of the primary key. The fields bound by auto_increment can be any integer type (tinyint/samllint/int/bigint ).

Syntax Rules: field name data type auto_increment

For example, define the data table tb_employee8 and specify the employee ID to be automatically added.

mysql> create table tb_employee8(id int(11)primary key auto_increment,name varchar(25)not null,deptld int(11),salary float);

 

mysql> insert into tb_employee8(name,salary) values('lucy',1000),('lii',800),('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 |   1000 ||  2 | lii  |   NULL |    800 ||  3 | cai  |   NULL |  20000 |+----+------+--------+--------+3 rows in set (0.00 sec)

 

8. view the data table structure

View basic table structure: describe table name or desc table name

mysql> desc tb_employee8;+--------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          |+--------+-------------+------+-----+---------+----------------+| id     | int(11)     | NO   | PRI | NULL    | auto_increment || name   | varchar(25) | NO   |     | NULL    |                || deptld | int(11)     | 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. Pri is a part of the primary key of the column; uni indicates that the column is part of the unique index; mul indicates that a given value in the column can appear multiple times.

 

9. view the detailed table structure

Syntax rule: show create table name \ G

mysql> show create table tb_employee8\G*************************** 1. row ***************************       Table: tb_employee8Create Table: CREATE TABLE `tb_employee8` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(25) NOT NULL,  `deptld` int(11) 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 a data table: modify the structure of an existing data table in the database. Frequently Used operations to modify a table include modifying the table name, modifying the field data type or field name, adding or deleting fields, modifying the field arrangement location, and changing the table storage engine, delete the foreign key constraints of a table.

(1) modify the table name

Alter table old table name rename new table name example: mysql> alter table tb_dept3 rename tb_deptment3;

(2) modify the field data type

Alter table name modify Field name Data Type example: mysql> alter table tb_dept1 modify name varchar (30 );

(3) add fields

Alter table name add new field name data type Example 1: mysql> alter table tb_dept1 add managerld int (20); (fields without integrity constraints) Example 2: mysql> alter table tb_dept1 add column1 varchar (12) not null; (add constraints) Example 3: mysql> alter table tb_dept1 add column2 int (11) first; (Added to the first column) Example 4: mysql> alter table tb_dept1 add column3 int (11) after name; (Added to the last column of name)

(4) Delete A Field

Alter table Name drop field name example: mysql> alter table tb_dept1 drop column3;

(5) modify the field sorting

Alter table name modify Field 1 Data Type first after field 2 Example 1: mysql> alter table tb_dept1 modify id int (11) first; Example 2: mysql> alter table tb_dept1 modify column2 int (11) after column1;

(6) change the table Data Engine

Alter table name engine = changed storage engine example: mysql> alter table tb_dept1 engine = innodb;

(7) Delete the foreign key constraint of the table

Alter table Name drop foreign key constraint name

(8) modify the field name

mysql> alter table customers change c_contact c_phone varchar(50);

 

11. delete a data table

Drop table 1 table 2...

Delete a primary table associated with another table: delete the word table first, and then delete the primary table (or cancel the foreign key constraint and delete the primary table)