Shulou(Shulou.com)06/01 Report--

First of all, we all know that only after successfully creating a database can we create a data table. A data table is a collection of fields in which data is stored in rows and columns.

create tables

MySQL uses CREATE TABLE to create tables. There are several options, mainly composed of table creation definition, table options definition and partition options.

table creation definition

It consists of the name of a table column, a null declaration of the definition set of the column, an integrity constraint or a table index item, which mainly defines the index, primary key, foreign key, etc. of the table.

syntax structure

CREATE[TEMPORARY]TABLE tbl_name( field name| Data Type [Column Level Integrity Constraints][Default] [, field name 2 data type [column-level integrity constraint][default]] [,....] [, table-level integrity constraints])[ENGINE= engine type]

Example:

Create a new customer profile

mysql> USE mysql_testDatabase changedmysql> CRATE TABLE customers ->( -> cust_id INT NOT NULL AUTO_INCREMENT, -> cust_name CHAR(50) NOT NULL, -> cust_sex CHAR(1) NOT NULL DEFAULT 0, -> cust_address CHAR(50) NULL -> cust_contact CHAR(50) NULL -> PRIMARY KEY(CUST_ID) ->)Query OK, 0 rows affected(0.11 sec) Temporary vs. persistent tables

TEMPORARY: indicates a temporary table, if not selected, a persistent table.

Persistent tables always exist, multiple users or applications can use persistent tables at the same time, if only temporary storage data can be added TEMPORARY keyword, temporary tables can only be visible to the user who created it, when the database connection is disconnected, the table will be automatically cleared.

data type

Data type refers to the type of data allowed in the system. Each column should have an appropriate data type to restrict or allow data for that column. When creating a table, you must specify the correct data type and data length for each column (CHAR(50)).

MySQL Main data types:

Numeric type: int, floating point double, Boolean bool

Date and time types: date type, timestamp type, time type

String type: fixed length character type char, variable length character type varchrar

Spatial data types: single geometric type GEOMETRY, etc.

Keyword AUTO_INCREMENT

AUTO_INCREMENT: Set the auto-increment attribute (++i) for columns with integer data type in the table. Starting from the current index or 1, there can only be one AUTO_INCREMENT in the table.

When a table column is specified as AUTO_INCREMENT, its value can be overridden, i.e., a value (which must be unique) is specified for the column in the table data insert statement, the value replaces the automatically generated value, and subsequent increments are based on the inserted value

specify a default value

DEFAULT: Used to specify MySQL default value if no value is given (DEFAULT 0)

If no default value is specified, a value is automatically assigned to it. If the column can take NULL, the default is NULL. If NOT NULL is defined, the default depends on the type of the column:

A column with no declaration AUTO_INCREMENT is numeric type, default 0

An AUTO_INCREMENT column defaults to the next value in the order

For date and time types other than TIMESTAMP, the default is the appropriate 'zero' value for that type

For the first TIMESTAMP column in the table, the default is the current date and time

NULL value

NULL: column with no value or missing value, NULL is allowed, the value of the column can not be given when inserting rows; column with NULL value is not allowed, the column must have data

NULL and '' are unequal NOT NULL columns allow '' NULL not allowed

primary key

PRIMARY KEY : Specify the primary key, the primary key must be unique and cannot be NULL, if it is a single column, the value must be unique, if it is a combination column, the combined value must be unique

update table

Modifying a database by using ALTER TABLE

ADD[COLUMN]: Add table columns, additional columns can be separated by commas

Example:

mysql> ALTER TABLE mysqle_test.customers -> ADD COLUMN cust_city char(10) NOT NULL DEFAULT'ShenZhen' AFTER cust_sex;Query OK,0 rows affected(0.61 sec)Records:0 Duplicates:0 Warning:0

AFTER: Add new column after cut_sexl column

FIRST: Adds the new column to the first column of the table

If you use the above keywords, add the new column to the end of the table

Similarly, ADDPRIMARY KEY, ADDFOREIGN KEY, ADD INDEX can be used to add the corresponding primary key, foreign key, index

CHANGE[COLUMN]: Modify the name or data type of a column in a table. Multiple columns can be modified using comma separation.

mysql> ALTER TABLE mysqle_test.customers -> CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT 'M'Query OK,0 rows affected(0.66 sec)Records:0 Duplicates:0 Warning:0

If the data type is changed, the original data of the column may be lost. If the data type changed by the view is incompatible with the original data type, the SQL command will not be executed and an error will be thrown.

In case of compatibility, the data of this column may be truncated, for example: if the data type of a column is varchart(10) and changed to char(1), the data 'ShenZhen' in this column will become'S'

ALTER [COLUMN]: Modifies or removes default values for specified columns

mysql> ALTER TABLE mysqle_test.customers -> ALTER COLUMN cust_city SET DEFAULT 'ShangHai'Query OK,0 rows affected(0.36 sec)Records:0 Duplicates:0 Warning:0

MODIFY [COLUMN]: modifies the data type of the specified column, modifies the column position via 'FIRST' or 'AFTER'

mysql> ALTER TABLE mysqle_test.customers -> MODIFY COLUMN cust_name char(30) FIRSTQuery OK,0 rows affected(0.20 sec)Records:0 Duplicates:0 Warning:0

DROP [COLUMN]: Delete a column, all data in the column is deleted

mysql> ALTER TABLE mysqle_test.customers -> DROP COLUMN cust_cityQuery OK,0 rows affected(0.42 sec)Records:0 Duplicates:0 Warning:0

DROP PRIMARY KEY, DROP FOREIGN KEY, DROP INDEX can also be used to delete the corresponding primary key, foreign key, index

RENAME[TO]: table renaming

mysql> ALTER TABLE mysqle_test.customers -> RENAME TOQuery OK,0 rows affected(0.42 sec) Rename table

In addition to RENAME TO in ALTER TABLE, you can also modify single and multiple tables (separated by commas) through RENAME TABLE

mysql> RENAME TABLE mysql_test.back.customers TO mysql_test.customers Delete table

DROP[TEMPORARY]TABLE[IF EXISTS] deletes an existing table, multiple tables can be deleted, provided that the operator must have permission, but the operator's permission on the table will not be deleted.

view table

SHOW [FULL] TABLES [{FROM| IN}db_name] [LIKE'pattern'| WHERE expr]: Displays all table names in the specified database

Example：

mysql> USE mysql_testDatabase changedmysql> SHOW TABLES: Tables_in_mysql_test customers 1 row in set

SHOW [FULL] COLUMNS {FROM| IN}tb_name[{FROM| IN}db_name] or {DESCRIBE| DESC} tbl_name[col_name| wild]: Displays the specified database table structure.

MySQL supports using DESCRIBE instead of SHOW COLUMNS FROM to view table structure

Example:

mysql> DESC mysql_test.custormesField Type Null key Default Extracust_id int NO PRI NULL auto_incrementcust_name char NO Nullcust_sex int NO 03 row in set

The above is the mysql table definition syntax details, more please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.