Mysql DBA Advanced Operations Learning Note-mysql Building table statements and table knowledge

9.9 Table Operation

9.9.1 building a library with the default character set

In the default format, specify the character set to build the library

[email protected] 07:0205->create database wwn;Query OK, 1 row affected (0.00 sec)[email protected] 07:0339->SHOW CREATE DATABASE wwn\G;*************************** 1. row ***************************   Database: wwnCreate Database: CREATE DATABASE `wwn` /*!40100 DEFAULT CHARACTER SET latin1 */row in set (0.00 sec)

9.9.2 Establishing a table

(1) Basic command syntax for building tables

Create table< Table Name > (

< field name 1>< type 1>

............

< field name n>< type n>

)

(2) Statement of the building of the table

The following is a manual table of the wording, table name student

[email protected] 07:3231->create table student(   id int(4) not null,   name char(20) not null,   age tinyint(2) NOT NULL default ‘0‘,   dept varchar(16) default NULL   );Query OK, 0 rows affected (0.00 sec)

The second type of MySQL generated check Table statement Student table Example

Create Table: CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT ‘0‘,  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

(3) Visual display of student table

(4) Actual demo

Execute Student Table-building Table statement

[email protected] 07:5806->use wwnDatabase changed[email protected] 09:5115->create table student( id int(4) not null, name char(20) not null, age tinyint(2) NOT NULL default ‘0‘, dept varchar(16) default NULL );

View the Build Table statement

[email protected] 09:5115->show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT ‘0‘,  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

It is important to note that the engine in the default build statement for the mysql5.1 and mysql5.5 environments is different, and if you want to control the engine of the table, the specified engine will be displayed in the table statement.
Mysql5.1 and the previous default engine for innodb,mysql5.5.5 after the default engine is InnoDB.

View table Structure

[email protected] 09:5934->desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

9.9.3 MySQL table field type

We can refer to MySQL reference manual to learn

(1) Number type

(1) Date and Time type (date type: Support Range is 1000-01-01 to 9999-12-31. MySQL Displays the date value in YYYY-MM-DD format, but allows the value to be assigned to the date column using a string or a number)

(3) String type

The most important is the following three types

1.INT (M) Type: normal size integer type.

2.CHAR (M): fixed-length string type, when stored always fills the right specified length with a space.

Type 3.VARCHAR: variable-length string type.

For more information on MySQL field types, please refer to the MySQL manual.

9.9.5 view the structure of the build table

A. Querying table structure commands

DESC table name or show columns from table name, for example:

  [email protected] 12:1602->desc student;[ Email protected] 12:1602->show columns from student; [email protected] 12:1931->use wwndatabase changed[email protected] 12:1943->desc student;+-------+ -------------+------+-----+---------+-------+| Field | type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| Int (4) | NO | |   null| || name | char (20) | NO | |   null| || Age | tinyint (2) | NO | |   0 | || Dept | varchar (16) | YES | |   null| |+-------+-------------+------+-----+---------+-------+[email protected] 12:1945->show columns from student;+-------+-------------+------+-----+---------+-------+| Field | type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| Int (4) | NO | |   null| || name | char (20) | NO | |   null| || Age | tinyint (2) | NO | |   0 | || Dept | varchar (16) | YES | |   null| |4 rows in Set (0.00 sec)  

9.9.6 querying statements that have already been built

B. Check the statement of the built-in table (you can see the index and the information about creating the table)

show create table student\G[email protected] 12:2308->show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT ‘0‘,  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

Mysql DBA Advanced Operations Learning Note-mysql Building table statements and table knowledge