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