Table operations,

Table operations,

Reading directory

• Table introduction
• 1. Create a table
• 2. view the table structure
• Three Data Types
• Table 4 integrity constraints
• 5. ALTER TABLE
• 6. Copy a table
• 7. delete a table

 

 

Table introduction

 

A table is equivalent to a file. A record in a table is equivalent to a row of the file. The difference is that a record in a table has a corresponding title, which is called a table field.

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

This section focuses on:

1. Create a table

2. view the table structure

3. Data Type

4. Table integrity constraints

5. modify a table

6. Copy a table

7. delete a table

 

 

 

I. Create a table

 

Syntax: create table Name (field name 1 type [(width) Constraints], field name 2 type [(width) Constraints], field name 3 type [(width) constraints]); Note: 1. in the same table, the field names cannot be the same. the width and constraints are optional. 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 in the db1 database, MariaDB [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 from t1; empty set (0.00 sec) MariaDB [db1]> select * from t1; Empty set (0.00 sec) MariaDB [db1]> select id, name from t1; Empty set (0.00 sec)

Create a table

MariaDB [db1]> insert into t1 values    -> (1,'egon',18,'male'),    -> (2,'alex',81,'female')    -> ;MariaDB [db1]> select * from t1;+------+------+------+--------+| id   | name | age  | sex    |+------+------+------+--------+|    1 | egon |   18 | male   ||    2 | alex |   81 | female |+------+------+------+--------+MariaDB [db1]> insert into t1(id) values     -> (3),    -> (4);MariaDB [db1]> select * from t1;+------+------+------+--------+| id   | name | age  | sex    |+------+------+------+--------+|    1 | egon |   18 | male   ||    2 | alex |   81 | female ||    3 | NULL | NULL | NULL   ||    4 | NULL | NULL | NULL   |+------+------+------+--------+

Insert data to a table

Mysql> create database db1 charset latin1; mysql> use db1; mysql> create table t1 (name varchar (20); mysql> show create table t1; # View tables, it is found that the table is consistent with the character encoding of Data db1 by default. mysql> insert into t1 values ('line'); # An ERROR occurred while inserting Chinese characters, because latin1 does not support Chinese ERROR 1366 (HY000 ): mysql> # solution 1: delete database db1, recreate database db1, and specify utf8 as character encoding # solution 2: Modify mysql> alter table t1 charset utf8; # modify the table t1 encoding mysql> insert into t1 values ('line'); # although the t1 encoding is changed, however, the field name of t1 is still ERROR 1366 (HY000) created according to latin1 encoding: mysql> alter table t1 modify name varchar (20 ); # You need to redefine the namemysql> insert into t1 values ('line'); mysql> select * from t1; + ------ + | name | + ------ + | lin | + ------ + ps: do not forget to change the database encoding to utf8, so that when you create a table in the database, all default utf8 encoding # configuration file: http://blog.csdn.net/yipiankongbai/article/details/16937815

Garbled problem: Important !!!

#1. modify the configuration file [mysqld] default-character-set = utf8 [client] default-character-set = utf8 [mysql] default-character-set = utf8 # mysql5.5 or above: the modification method has been changed to [mysqld] character-set-server = utf8 collation-server = utf8_general_ci [client] default-character-set = utf8 [mysql] default-character-set = utf8 #2. restart the service #3. view the Modification result: \ sshow variables like '% char %'

Permanently solves coding problems

 

 

 

 

Ii. view the table structure

MariaDB [db1]> describe t1; # view the table structure, it can be abbreviated as desc table name + ------- + --------------------- + ------ + ----- + --------- + ------- + | 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]> show create table t1 \ G; # view the detailed table structure, add \ G

 

 

 

Iii. Data Types

Http://www.cnblogs.com/zhoujunhao/articles/7717763.html

 

Iv. Table integrity constraints

Http://www.cnblogs.com/zhoujunhao/articles/7718019.html

 

V. alter table

 

Syntax: 1. Modify the TABLE name alter table name RENAME new TABLE name; 2. ADD the field alter table name ADD field name data type [integrity constraints…], ADD field name data type [integrity constraints…]; Alter table name ADD field name data type [integrity constraints…] FIRST; alter table name ADD field name data type [integrity constraints…] AFTER field name; 3. Delete field alter table Name DROP field name; 4. MODIFY Field alter table name MODIFY Field Name Data Type [integrity constraints…]; Alter table name CHANGE old field name new field name old data type [integrity constraints…]; Alter table name CHANGE old field name new data type [integrity constraints…]; Example: 1. modify the storage engine mysql> alter table service-> engine = innodb; 2. add field mysql> alter table student10-> add name varchar (20) not null,-> add age int (3) not null default 22; mysql> alter table student10-> add stu_num varchar (10) not null after name; // mysql> alter table student10-> add sex enum ('male ', 'Female ') default 'male' first; // Add it to the top 3. delete the mysql field> 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) not null primary key auto_increment; // change to the primary key. add constraints (add auto_increment for existing primary keys) mysql> alter table student10 modify id int (11) not null primary key auto_increment; ERROR 1068 (42000 ): multiple primary key definedmysql> alter table student10 modify id int (11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 06. add a composite primary key for an existing table mysql> alter table service2-> add primary key (host_ip, port); 7. add the primary key mysql> alter table student1-> modify name varchar (10) not null primary key; 8. add primary keys and automatically increase mysql> alter table student1-> modify id int not null primary key auto_increment; 9. delete primary key. delete the auto-incrementing constraint mysql> alter table student10 modify id int (11) not null; B. delete the primary key mysql> alter table student10-> drop primary key;

 

 

6. Copy a table

Copy table structure + record (key won't copy: primary key, foreign key and index) mysql> create table new_service select * from service; only copy table structure (key won't copy ), do not copy the record mysql> select * from service where 1 = 2; // The condition is false and no record Empty set (0.00 sec) is found) mysql> create table newpartition service select * from service where 1 = 2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0. Copy the table structure (the key will also be copied). Do not copy the record mysql> create table t4 like employees;

 

 

 

7. delete a table

Drop table name;