Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL uses command line to build libraries and tables". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "MySQL builds libraries and tables on the command line".

1. Create

Use the create database or create schema command to create a database.

Create database library name

Create database if not exists library name (create the library and verify whether the created library exists. If it does not exist, it will not be built.)

MySQL does not allow two databases to use the same name, and ifnot exists clauses can be used without displaying error messages

Show all databases: showdatabases; www.2cto.com

Displays the definition information for creating the database: show create dabasese library name

two。 After the database is created, use the USE command to specify the current database.

Syntax format: use db_name

Note: this statement can also be used to "jump" from one database to another. After creating a database with a create database statement, the database will not automatically become the current database and needs to be specified with this USE statement.

Note: in MySQL, every SQL statement ends with ";".

Case sensitivity is not present in mySQL syntax. The person who created the database must be a system administrator or have user-level create privileges. During the installation of MySQL, a system administrator has been created, named root, assuming the password is root.

3. When creating the library, specify the character set and proofread the rules:

Create database mydb character set utf8

Collate utf8-general_ci

Character set: specify the database character set (Charset), collate: the proofreading rules for the specified character set

4. Modify the database, you can only change the parameters

After the database is created, if you need to modify the parameters of the database, you can use the alter database command.

Syntax format:

Alter {database | schema} [db_name]

Alter_specification [, alter_specification]...

Where alter_specification: www.2cto.com

[default] character set charset_name

| | [default] collate collation_name |

Description:

Alter database is used to change the global properties of the database, which are stored in the db.opt file in the database directory. Users must have permission to modify the database before they can use alterdatabase. The options for modifying the database are the same as creating the database, and the function is not repeated. If the database name is ignored in the statement, the current (default) database is modified.

5. Delete database

Drop database library name

Drop database if exists library name (avoid MySQL error messages when deleting databases that do not exist.)

Note: this command must be used with care because it will delete the entire specified database and all tables in that database (including the data in it) will be permanently deleted.

6. Create a tabl

To create a table is to create the structure of the table: which fields (attributes), field names, field types

Create table table name

(field name 1 type

Field name 2 Typ

.

);

Example: create a student table

Use library name (determine which library your table is built in and make it the current database)

Create table xs

Www.2cto.com

(

Id int

Name char (10)

Sex char (2)

);

Create [temporary] table [if not exists] tbl_name

[([column_definition],... | [index_definition])]

[table_option] [select_statement]

Description:

● temporary: this keyword indicates that the table created with the create command is a temporary table. Tables created without this keyword are often called persistent tables. Persistent tables will always exist in the database once created, and multiple users or applications can use persistent tables at the same time. Sometimes you need to store data temporarily, for example, to temporarily store the results of complex select statements. After that, you may want to use this result repeatedly, but the result does not need to be saved permanently. At this point, temporary tables can be used. Users can manipulate temporary tables in the same way as persistent tables. It's just that temporary tables have a short life cycle and are visible only to the users who created them, and MySQL automatically deletes them when you disconnect from the database.

When ● creates a table, you can also use the if not exists statement to determine whether the created table already exists to avoid errors.

The table name (tbl_name) of the table to be created by ● must conform to the marker rules and must be enclosed in single quotation marks if there are MySQL reserved words.

● column_definition: column definitions, including column names, data types, and possibly a null declaration and an integrity constraint.

● index_definition: table index item definition, which mainly defines the table index, primary key, foreign key, and so on. The specific definition will be discussed later. Www.2cto.com

● table_option: options for describing tables.

● select_statement: you can add a select statement to the end of the create table statement to create a table based on a table

7. Modify table structure: add fields, modify old fields, delete fields

Alter table table name

Add field name type (width) / / add field

Modify Old Field Type / / modify Old Field

Drop Field name / / Delete Field

Change old field name new field content / / rename

Order by col_name / / sort

Convert to character set charset_name [character set name] / / convert character set to binary

[default] character set charset_name [character set name] / / modify the default character set

Example: add the birthday field to the student table, modify the name field, and modify the name of the field name

Alter table xs

Add birthday date

Www.2cto.com

Modify name varchar (20)

Change name sname varchar (20)

8. Modify table name

Rename table old name to new name

classification

Data type

Description

Numerical type

Bit (M)

Tinint [unsigned] [zerofill]

Bool,boolean

Smallint [unsigned] [zerofill]

Int [unsigned] [zerofill]

Bigint [unsigned] [zerofill]

Float [(MMagar D)] [unsigned] [zerofill]

Double [(MMagar D)] [unsigned] [zerofill]

Bit type. M specifies the number of digits, default value 1, range 1-64

The signed range is-128 to 127. Unsigned 0 to 255.

Www.2cto.com

Use 0 or 1 to indicate true or false

2 to the power of 16

2 to the power of 32

2 to the power of 64

M specifies the display length, d specifies the number of decimal places

Represents a decimal with greater precision than float

Text, binary type

Char (size) char (20)

Varchar (size) varchar (20)

Blob longblob

Text (clob) longtext (longclob)

Fixed length string

Variable length string

Binary data

Large text

Time date

Date/datetime/TimeStamp

Date type (YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS). TimeStamp represents a timestamp, which can be used to automatically record the time of insert and update operations.

Note: after character data types and numeric data types, MySQL allows you to specify a data type option to change the properties and functions of the data type.

For character data types, MySQL supports two data type options: characterset and collate. If you want to distinguish the case of characters, you can add bingary after the character type.

For numeric data types other than bit, MySQL allows you to add one or more data type options. Unsigned: negative values are not allowed. Zerofill: when the length of the inserted value is less than the length set by the field, the rest is filled with 0.

The types related to int correspond to bit, short, int and long in java, respectively.

The varchar, bolb, and text classes are variable length types. The storage requirements for each type depend on the actual length of the column values.

9. The column definition column_definition format is as follows:

Col_name type [not null | null] [default default_value]

[auto_increment] [unique [key] | [primary] key]

[comment 'string'] [reference_definition]

Description: www.2cto.com

● col_name: name of the column in the table. Column names must conform to marker rules, must be no more than 64 characters long, and must be unique in the table. If there are MySQL reserved words, they must be enclosed in single quotation marks.

● type: the data type of the column, some of which need to be specified in length n and enclosed in parentheses. The data types supported by MySQL are described in Appendix C.

● auto_increment: sets the self-increment property, which can only be set by integer columns. When a null value or 0 is inserted into an auto_increment column, the column is set to value+1, where value is the maximum value of the column in the previous table. The auto_increment sequence starts at 1. Each table can have only one auto_increment column, and it must be indexed.

● not null | null: specifies whether the column is allowed to be empty. If not specified, the default is null.

● default default_value: specifies the default value for the column, which must be a constant. Where BLOB and TEXT columns cannot be assigned default values. If no default value is specified for the column, MySQL automatically assigns one. If the column can take a null value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:

(1) for numeric types that do not declare the auto_increment attribute, the default value is 0. For an auto_increment column, the default value is the next value in the order.

(2) for date and time types other than timestamp, the default value is the appropriate zero value for that type. For the first timestamp column in the table, the default value is the current date and time.

(3) for string types except enum, the default value is an empty string. For enum, the default value is the first enumerated value.

● unique key | primary key: both indicate that the value in the field is unique. Primary key means set to primary key. Only one primary key can be defined in a table. The primary key must be notnull.

● comment 'string': describes the column, and string is the content of the description.

● reference_definition: specifies the referenced tables and columns.

10. (1) Copy table: create table table name like table name 1

Syntax format:

Create [temporary] table [if not exists] tbl_name

[() like old_ name []]

| | [as (select_statement)] |

Description:

Use the LIKE keyword to create a new table with the same structure as the old _ name table. The column name, data type, null assignment, and index are also replicated, but the contents of the table are not replicated, so the new table created is an empty table. The contents of a table can be copied using the as keyword, but indexes and integrity constraints are not replicated. Select_statement represents an expression, for example, it can be a select statement.

(2)。 Delete table: drop table table name (this command removes all table descriptions, table integrity constraints, indexes, table-related permissions, etc.) www.2cto.com

(3)。 Modify the table name: rename clause, syntax format:

Rename table old_name to new_name

New_tbl_name is the new table name. For example, rename table a to b:alter table a rename to b; in addition to the alter table command above, you can change the name of the table directly with the renametable statement.

● order by clause (described later): used to have the rows arranged in a certain order when creating a new table. Note that the tables do not remain in this order after insertions and deletions. After making major changes to the table, you can improve query efficiency by using this option. In some cases, sorting may be easier for MySQL if the table is sorted by column.

● table_options: modify the table option as defined in the create table statement.

You can write multiple add, alter, drop, and chang clauses in an alter table statement, separated by commas. This is an extension of MySQL over the standard SQL. In standard SQL, each clause in each alter table statement is allowed only once.

11. Description: most of the options in the table relate to how and where the table data is stored. In most cases, it is not necessary to specify table options. The engine option is the storage engine that defines the table, as described in Appendix E.

For example:

USE mydb1

CREATE TABLE XS

(

Student ID char (6) NOTNULL primary KEY

Name char (8) NOTNULL

Professional name char (10) NOT NULL

Gender tinyint (1) NOT NULL default 1

) engine=InnoDB

Note: "primary KEY" means to define the "student number" field as the primary key. "default 1" means that the default value for "gender" is 1. "engine=InnoDB" means that the storage engine used is InnoDB,InnoDB, which is the default storage engine for MySQL on the Windows platform, so "engine=InnoDB" can be omitted.

twelve。 Description:

● ignore: is an extension of MySQL over standard SQL. If there is a duplicate keyword in the modified new table, if ignore is not specified, the operation fails when a duplicate keyword error occurs. If ignore is specified, only the first line is used for rows with duplicate keywords, and other conflicting lines are deleted.

● column_definition: defines the data types and properties of the column, as described in the syntax of create table.

● first | after col_name: it is added before or after a column. If it is not specified, it is added to the last.

Note: if the data type of the data stored in this column in the table conflicts with the type of the column to be modified, an error occurs. For example, a column of the original char type needs to be changed to an int type, but the character data "a" in the original column value cannot be modified.

13. 1). Null value (NULL) concept

Null values usually represent data that is unknown, unavailable, or will be added later. If a column is allowed to be null, the record value may not be given to the column when entering a record value into the table, while if a column is not allowed to be null, the specific value of the column must be given when entering.

Note: table keywords are not allowed to be null. Null values cannot be confused with numeric data 0 or null characters of character types. No two null values are equal. Www.2cto.com

2)。 IDENTITY (flag) property of the column

For any table, you can create a flag column that contains the system-generated ordinal value, which uniquely identifies a column in the table and can be used as a key value. Only one column per table can be set as a flag property, and the column can only be of the decimal, int, numeric, smallint, bigint, or tinyint data type. When you define a flag attribute, you can specify its seed (that is, start) value and incremental value, both of which have a default value of 1. The system automatically updates the flag column value, and the flag column does not allow null values.

In the following cases, MySQL implicitly changes a column type given in a CREATE TABLE statement (this may also occur on an ALTER TABLE statement).

14.MySQL implicitly changes the column type:

(1) varchar whose length is less than 4 is changed to char.

(2) if any column in a table has a variable length, the result is that the entire row is longer.

Therefore, if a table contains any variable-length columns (varchar, text, or Blob), all char columns greater than 3 characters are changed to varchar columns. This does not affect in any way how the user uses the column. This change in MySQL saves space and makes table operations faster.

(3) the display size of timestamp must be even and within the range of 2 to 14. If you specify a 0 display size or greater than 14, the size is forced to 14. The odd size from 1 to 13 is forced to the next larger even number.

(4) you cannot store a NULL in a timestamp column. Set it to null and defaults to the current date and time.

If you want to know whether MySQL uses a column type other than the one specified, use a DESCRIBE statement after creating the table. The DESCRIBE statement is introduced in Section 3.1.4.

15 type is defined as follows:

Description: after character data types and numeric data types, MySQL allows you to specify a data type option to change the properties and functions of the data type.

For character data types, MySQL supports two data type options: CHARACTERSET and COLLATE. If you want to distinguish the case of characters, you can add BINGARY after the character type.

For numeric data types other than BIT, MySQL allows you to add one or more data type options. UNSIGNED: negative values are not allowed. ZEROFILL: when the length of the inserted value is less than the length set by the field, the rest is filled with 0.

Spatial_type is spatial type data.

16. The table option table_option is defined as follows:

{engine | type} = engine_name / / Storage engine

| | auto_increment = value / / initial value |

| | auto_increment = average row length of value / / table |

| | [default] charcter set charset_name [collatecollation_name] / / default character set and proofreading |

| | checksum = {0 | 1} / / set to 1 means a checksum is obtained |

| | comment= 'string' / / comments |

| | connection = 'connect_string' / / connection string |

| | MAX_ROWS = the maximum number of value / / lines |

| | MIN_ROWS = the minimum number of value / / columns |

| | PACK_KEYS = {0 | 1 | DEFAULT} |

| | password = 'string' / / encrypt a pair of .frm files |

| | delay_key_write = {0 | 1} / / update a pair of keywords |

| | row_format= {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} / / defines how each line should store www.2cto.com |

| | union = (tbl_name [, tbl_name]...) / / indicates which table should be merged |

| | insert_method = {NO | FIRST | LAST} / / whether to execute the INSERT statement |

| | data directory = 'absolute path todirectory' / / path to the data file |

| | index directory = 'absolute path todirectory' / / path of the index |

At this point, I believe you have a deeper understanding of "MySQL uses the command line to build libraries and tables". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.