mysql-database table Operations

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

This section focuses on:

1 Creating a Table

2 View Table Structure

3 Data types

4 Table Integrity constraints

5 Modifying a table

6 Copying tables

7 Deleting a table

Create a table

Syntax: CREATE TABLE table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]); Note: 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 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)              

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); 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 |+------+------+------+--------+ 
       

Mysql>Create DATABASE DB1 CharSet latin1;mysql>Use db1;mysql> CREATE TABLE T1 (name varchar (20));mysql> Show create table T1;#Look at the table and find that the table default is consistent with the character encoding of the data db1 mysql> insert into T1 values (‘Forest‘);#Insert error in Chinese because Latin1 does not support Chinese error 1366(HY000): mysql>#Workaround One: Delete library db1, rebuild DB1, character encoding specified as UTF8#Workaround two: Modify mysql> alter TABLE T1 charset UTF8;#Modify the encoding for table T1 mysql> insert into T1 values ( " forest # Although T1 's code is changed, the T1 field name is still the error 1366 created by Latin1 encoding  (HY000): mysql> ALTER TABLE T1 modify name varchar (20); # need to redefine the following field namemysql> insert into T1 values ( '  Lam  "from t1;+------+| name |+------+| Forest |+--- ---+ps: Do not forget to change the database encoding to UTF8, so that when you create a table under that database, UTF8 #< Span style= "COLOR: #008000" > configuration file: http://blog.csdn.net/yipiankongbai/article/details/16937815   

#1. Modify the configuration file[mysqld]default-character-set=utf8 [client] Default-character-set=utf8 [mysql]default-character-set= utf8# mysql5.5 above: Modification method changed  [mysqld] Character-set-server=utf8 Collation-server=utf8_general_ci [client] default-character-set=utf8 [MySQL] default-character-set=utf8#2. Restart service #3. View the results of the modification: \sshow Variables like  ' %char% "  

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

Two 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

Three data types

Http://www.cnblogs.com/liluning/p/7489710.html

Four-table integrity constraints

Http://www.cnblogs.com/liluning/p/7489852.html

Five modified table ALTER TABLE

Syntax: 1. Modify table name The name of the ALTER TABLE table RENAME the new table name; 2. Add field ALTER table name add field name data type [integrity constraint ...], add field name data type [integrity constraint ...    ];  ALTER table name ADD field name data type [integrity constraint ...]  first;  ALTER 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 service, ENGINE=INNODB;2.    Add Field mysql> ALTER TABLE student10, add name varchar (a) Not NULL, add age int (3) is not null default 22;                Mysql> ALTER TABLE student10, add stu_num varchar (TEN) not NULL after name; AddAfter adding the Name field mysql> ALTER TABLE student10, add sex enum (' Male ', ' female ') default ' male ' Fir          Qty Added to the front 3. Delete field mysql> ALTER TABLE student10, DROP sex;mysql> ALTER TABLE service, drop mac;4. Modify field type modifymysql> ALTER TABLE student10-Modify age int (3);mysql> ALTER TABLE STUDENT10-Modify ID    Int (one) NOT null primary key auto_increment; Modify the primary key 5. Add constraint (Increase auto_increment for existing primary key) mysql> ALTER TABLE student10 modify ID int (one) NOT null primary key auto_increment; ERROR 1068 (42000): Multiple PRIMARY key definedmysql> ALTER TABLE student10 modify ID int (one) not NULL auto_increment; Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:06.        Add a composite primary key to an existing table mysql> ALTER TABLE SERVICE2, add primary key (Host_ip,port); 7. Increase the primary key mysql> ALTER TABLE STUDENT1, modify name varchar (TEN) NOT null primary key;8. Increase primary KEY and autogrow mysql> ALTER TABLE STUDENT1-Modify ID int not NuLL primary key auto_increment;9. Delete primary key A. Remove the self-increment constraint mysql> ALTER TABLE student10 modify ID int (one-by-one) not null; B. Delete the primary key mysql> ALTER TABLE student10, drop primary key;

Six 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;

mysql-database table Operations