MySQL table creation

When creating a database table, it is a good idea to write the code that creates the table in the editor and paste it into the command line, which is convenient if the error is modified.

Now to create a user table:

--Open the database,--there must be a space behind it to indicate the comment Usemydb3;--CREATE TABLE (user)--ID : ID--User name: Username--Age: Ages--Gender: Sex--Email: Email--Address: Addr--Birthday: birth--Salary: Salary--Tel: Tel--whether to marry: married--When you have Chinese, you need to temporarily convert the encoding of the client, SET NAMES UTF8, valid for the current connection--field comments, adding comments to fields by comment--use anti-quotation marks to prevent table names from being duplicate keywords--You can specify the storage engine and encoding of the tableCREATE TABLE`User' (IDSMALLINT, usernameVARCHAR( -), ageTINYINT, Sex ENUM ('male','female','Confidentiality'), emailVARCHAR( -), addrVARCHAR( $), birth Year, SalaryFLOAT(8,2), TelINT, marriedTINYINT(1) COMMENT'0 means unmarried, not 0 for marriage') ENGINE=INNODB CHARSET=UTF8;

To view all data tables in the database:

Mysql>SHOW TABLES;+-----------------+|Tables_in_mydb3|+-----------------+|Cms_cate||Cms_news||Course|| User            |+-----------------+4Rowsinch Set(0.01Sec

To view the table structure:

Three ways: DESC table_name;  DESCRIBE table_name; SHOW COLUMNS from table_name;

Mysql> DESC User;+----------+----------------------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+----------+----------------------------+------+-----+---------+-------+|Id| smallint(6)|YES|     | NULL    |       ||Username| varchar( -)|YES|     | NULL    |       ||Age| tinyint(4)|YES|     | NULL    |       ||Sex|Enum'male','female','Confidentiality')|YES|     | NULL    |       ||Email| varchar( -)|YES|     | NULL    |       ||Addr| varchar( $)|YES|     | NULL    |       ||Birth|  Year(4)|YES|     | NULL    |       ||Salary| float(8,2)|YES|     | NULL    |       ||Tel| int( One)|YES|     | NULL    |       ||Married| tinyint(1)|YES|     | NULL    |       |+----------+----------------------------+------+-----+---------+-------+TenRowsinch Set(0.01Sec

Create an integer table Test1 and test the field type:

CREATE TABLE  TINYINTSMALLINT INTBIGINT) ;

Insert a record into the table:

-- inserting data into a table INSERT VALUES (-32768,-8388608,-2147483648, - 9223372036854775808);

Check to see if the insert succeeds: '

Mysql> Select *  fromtest1;+------+--------+----------+-------------+----------------------+|Num1|Num2|Num3|Num4|Num5|+------+--------+----------+-------------+----------------------+| - - | -32768 | -8388608 | -2147483648 | -9223372036854775808 |+------+--------+----------+-------------+----------------------+1Rowinch Set(0.00Sec

Modify it to let the num1 out of range to see if it can be inserted successfully:

Mysql> INSERTTest1VALUES(-129,-32768,-8388608,-2147483648,-9223372036854775808); ERROR1264(22003): Out ofRange value for column 'NUM1'At row1

To create a table with an unsigned field:

-- No sign CREATE TABLE  TINYINTTINYINT);

Inserting data

-- Insert INSERT VALUES (0,-);

MySQL>INSERTVALUES(-ten,-1264 (22003of forcolumn'num1'1 

0 Filling Zerofill

-- 0 Fill Zerofill, plus zerofill automatically become unsigned -- If the display length is not specified, the default is the maximum length CREATE TABLE  TINYINT(3INT zerofill); -- Inserting Data INSERT VALUES (1,1);

Look at the fill effect:

Mysql> Select *  fromtest3;+------+------------+|Num1|Num2|+------+------------+|  001 | 0000000001 |+------+------------+1Rowinch Set(0.01Sec

MySQL table creation