Day10-mysql table Operation

Introduction to a storage engine

The storage engine is a table type, and MySQL has different processing mechanisms depending on the table type

Http://www.cnblogs.com/guoyunlong666/p/8491702.html

Two table Introduction

Table is equivalent to a file, a record in the table is equivalent to a row of the file, but a record in the table has a corresponding title, called the table field

Id,name,qq,age is called a field, the rest, a row of content is called a record

Three Create a table

#语法: Create TABLE table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]); #注意: 1. In the same table, the field names cannot be the same as 2. Width and constraints are optional 3. Field names and types are required

MariaDB [(None)]>CREATE Database db1 charset UTF8; MariaDB [(none)]>Use db1; MariaDB [DB1]>CREATE table T1 (-ID int,Name varchar (50),    -Sex enum ('male','female'),    -Age Int (3)    - ); MariaDB [DB1]> Show tables;#View all table names under the DB1 libraryMariaDB [DB1]>desc T1;+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | || name | varchar (50) |     YES | |       NULL | || sex | Enum'male','female') |     YES | |       NULL | || Age | Int (3) |     YES | |       NULL | |+-------+-----------------------+------+-----+---------+-------+MariaDB [DB1]> select Id,name,sex,age fromT1; Empty Set (0.00sec) MariaDB [DB1]> select * fromT1; Empty Set (0.00sec) MariaDB [DB1]> select Id,name fromT1; Empty Set (0.00 sec)

View Code

MariaDB [db1]>INSERT INTO T1 values(1,'Egon', 18,'male'),    (2,'Alex', 81,'female')    - ; MariaDB [DB1]> select * fromT1;+------+------+------+--------+| ID | name | Age |    Sex |+------+------+------+--------+| 1 |   Egon | 18 |    Male | | 2 |   Alex | 81 | Female |+------+------+------+--------+MariaDB [DB1]>INSERT into T1 (ID) Values(3),    (4); MariaDB [DB1]> select * fromT1;+------+------+------+--------+| ID | name | Age |    Sex |+------+------+------+--------+| 1 |   Egon | 18 |    Male | | 2 |   Alex | 81 |    Female | | 3 | NULL | NULL |    NULL | | 4 | NULL | NULL | NULL |+------+------+------+--------+

inserting data into a table

Note Note: Do not add commas to the last field in the table

Four view table structure

MariaDB [db1]> describe T1; #查看表结构, can be abbreviated as DESC table name +-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| ID    | int               | YES  |     | NULL    |       | | name  | varchar           | YES  |     | NULL    | |       | | sex   | enum (' Male ', ' female ') | YES  |     | NULL    |       | | age   | INT (3)                | YES  |     | NULL    |       | +-------+-----------------------+------+-----+---------+-------+mariadb [db1]> show create TABLE t1\g; #查看表详细结构, can add \g

Five data types

Http://www.cnblogs.com/guoyunlong666/p/8491773.html

Six-table integrity constraints

Http://www.cnblogs.com/guoyunlong666/p/8491875.html

Seven Modified tables ALTER TABLE

Syntax: 1. Modify the table name                           RENAME A new table name for the table name; 2. add Field      ALTER TABLE name                          add field name  data type [integrity constraint ...],                          add field name  data type [Integrity constraints ...];      ALTER Table name                          ADD field name  data type [integrity constraint ...]  First;     ALTER Table Table name                          ADD field name  data type [integrity constraint ...]  After field name;                            3. Delete field      ALTER TABLE name                           drop field name; 4. modify field      ALTER TABLE name                           MODIFY  field name data type [integrity constraint ...];      ALTER Table table name change                           old field name new field name old data type [integrity constraint condition ...];      ALTER table name change                           old field name new data type [integrity constraint ...];

Example:1. Modify the storage engine MySQL>ALTER TABLE serviceEngine=InnoDB;2. add Field MySQL>ALTER TABLE student10Add name varchar (20) notNULL,-Add age int (3) notNull default 22; MySQL>ALTER TABLE student10Add Stu_num varchar (10) notNull after name; //after adding the name field MySQL>ALTER TABLE student10Add Sex enum ('male','female') Default'male'First //add to the front3. Delete field MySQL>ALTER TABLE student10-Drop Sex;mysql>ALTER TABLE service-drop mac;4. Modify the field type Modifymysql>ALTER TABLE student10-Modify Age Int (3); MySQL>ALTER TABLE student10-Modify ID int (11) notNULL primary key auto_increment; //Modify the primary key5add constraints (increase auto_increment for existing primary keys) MySQL> ALTER TABLE student10 modify ID int (11) notNULL primary key auto_increment; ERROR1068 (42000): Multiple primary key Definedmysql> ALTER TABLE student10 modify ID int (11) notNULL auto_increment; Query OK, 0 rows affected (0.01sec) records:0 duplicates:0 warnings:06Add a composite primary key to a table that already exists MySQL>ALTER TABLE Service2-Add primary key (Host_ip,port); 7. Increase the primary key MySQL>ALTER TABLE Student1-Modify name varchar (10) notNULL primary key;8add primary keys and auto-grow MySQL>ALTER TABLE Student1-Modify ID int notNULL primary key auto_increment;9Remove the primary key A. Remove the self-increment constraint MySQL> ALTER TABLE student10 modify ID int (11) notnull; b. Delete primary key MySQL>ALTER TABLE student10--drop primary key;

ExampleEight copy table

Copy table structure + record (key does not replicate: primary key, foreign key and index) mysql> CREATE TABLE New_service select * from service; Copy table structure only mysql> select * from service where 1=2;        Condition is false, no record is found for empty set (0.00 sec) mysql> CREATE TABLE New1_service SELECT * from service where 1=2;  Query OK, 0 rows Affected (0.00 sec) records:0  duplicates:0  warnings:0mysql> create table t4 like employees;

Nine Delete a table

DROP table name;

Day10-mysql table Operation