MySQL 2-create databases and tables using the command line Method

1. Use the create database or create schema command to create a database. Create database name create database if not exists database Name, if the ifnot exists clause is used, the following error information is displayed: showdatabases; www.2cto.com: show create dabasese database name 2. after creating a database, 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 the database is created using the create database statement, the database will not automatically become the current database and must be specified using this USE statement. Note: in MySQL, each SQL statement uses ";" as the end mark. MySQL syntax is case-insensitive. You must be a system administrator or have the user-level create permission to create a database. You have created a system administrator named root during MySQL installation. Assume that the password is root. 3. specify character sets when creating a library, checking rules: create database mydb character set utf8 collate utf8-general_ci; character set: Specify the database character set (Charset), collate: Specify the collation of the character set 4. to modify the database, you can only modify the parameters of the database after it is created. to modify the database parameters, run the alter database command. Syntax format: alter {database | schema} [db_name] alter_specification [, alter_specification]... alter_specification: www.2cto.com [default] character set charset_name | [default] collate collation_name Description: alter database is used to change the global features of the database. These features are stored in the database directory db. in the opt file. You must have the permission to modify the database before using alterdatabase. The options for modifying a database are the same as those for creating a database. If the database name in the statement is ignored, modify the current (default) database. 5. Delete the database drop database name drop database if exists database Name (avoid MySQL error messages when deleting a database that does not exist .) Note: This command must be used with caution because it will delete the specified entire database, and all tables (including the data) of the database will also be permanently deleted. 6. Creating a table to create a table is to create the table structure: which fields (attributes), field names, and field types are included in the create table name (field name 1, field name 2, etc ,.... ); Example: create a student table use Database Name (to determine which database your table is created and change the database to 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]; Note: ● temporary: This keyword indicates that the table created with the create Command is a temporary table. A table created without this keyword is usually called a persistent table. A persistent table exists after it is created in the database. A persistent table can be used by multiple users or applications at the same time. Sometimes you need to temporarily store data, for example, temporarily store the results of complex select statements. Later, you may need to use this result repeatedly, but it does not need to be permanently saved. In this case, you can use a temporary table. You can operate temporary tables like a persistent table. However, temporary tables have a short life cycle and can only be seen to users who have created them. When the database is disconnected, MySQL automatically deletes them. ● When creating a table, you can use the if not exists statement to determine whether the created table already exists and avoid errors. ● The table name (tbl_name) of the table to be created must comply with the tag rules. MySQL reserved words must be enclosed in single quotation marks. ● Column_definition: column definition, including the column name and data type. It may also have a null value declaration and an Integrity Constraint. ● Index_definition: Table index item definition, which mainly defines the index, primary key, and foreign key of the table. The specific definition will be discussed later. Www.2cto.com ● table_option: The option used to describe the table. ● Select_statement: you can add a select statement at the end of the create table statement and create a table 7 Based on the table. modify table Structure: add field modify old field Delete field alter table name add field name type (width) // Add the field modify old field type // modify the old field drop field name // Delete the field change old field name new field content // change the column name order by col_name // sort convert to character set charset_name [character set Name] // convert the character set to binary [default] character set charset_name [character set Name] // example of modifying the default character set: add the birthday field, modify the name field, and modify the name of the field in the student table; 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 [(M, D)] [unsigned] [zerofill] double [(M, D)] [unsigned] [zerofill] bit type. M specifies the number of digits. The default value is 1. The value range is 1-64. The signed value range is-128 to 127. The value ranges from 0 to 255. Www.2cto.com uses 0 or 1 to indicate true or false 2's 16 power 2's 32 power 2's 64 power M to specify the display length, d. specify the number of decimal places to indicate fractional text with a higher precision than float, binary char (size) char (20) varchar (size) varchar (20) blob longblob text (clob) longtext (longclob) Fixed Length string variable length string binary data big text time date/datetime/TimeStamp date type (YYYY-MM-DD) (YYYY-MM-DD HH: MM: SS), TimeStamp represents TimeStamp, it can be used to automatically record the insert and update operations. Note: After the character data type and numeric data type, MySQL allows you to specify a data type option to change the attributes and functions of the data type. For character data types, MySQL supports two data types: characterset and collate. To distinguish between uppercase and lowercase 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 smaller than the length set by the field, fill the remaining part with 0. Int-related types correspond to bit, short, int, and long in java. The varchar, bolb, and text classes are variable-length type. The actual length of each type of storage depends on the column value. 9. the format of column definition column_definition 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. The column name must comply with the flag rules. It cannot exceed 64 characters and must be unique in the table. MySQL reserved words must be enclosed in single quotes. ● Type: the data type of a column. Some data types must specify the length of n and enclose them in parentheses. MySQL supports the data types described in appendix C. ● Auto_increment: Set the auto-increment attribute. This attribute can be set only for integer columns. When a null value or 0 is inserted into an auto_increment column, the column is set to value + 1. Here value is the maximum value of this column in the previous table. The auto_increment sequence starts from 1. Each table can have only one auto_increment column, and it must be indexed. ● Not null | null: Specifies whether the column can be empty. If this parameter is not specified, the default value is null. ● Default default_value: Specifies the default value for the column. The default value must be a constant. The BLOB and TEXT Columns cannot be assigned the default values. If no default value is specified for the column, MySQL automatically assigns one. If the column can be NULL, 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 without the auto_increment attribute declared, the default value is 0. For an auto_increment column, the default value is the next value in the sequence. (2) For date and time types other than timestamp, the default value is the appropriate "zero" value for this 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 a null string. For enum, the default value is the first enumerated value. ● Unique key | primary key: both indicate that the values in the field are unique. Primary key indicates that a table can only define one primary key. The primary key must be notnull. ● Comment 'string': string indicates the description of a column. ● Reference_definition: Specify the referenced table and column. 10. (1 ). copy table: create table name like table name 1 syntax format: create [temporary] table [if not exists] tbl_name [() like old _ name [] | [as (select_statement)]; Note: Use the LIKE keyword to create a new table with the same structure as the old _ name table, the column name, data type, null designation, and index will also be copied, but the table content will not be copied, so the new table created is an empty table. You can use the as keyword to copy the table content, but the index and integrity constraints are not copied. Select_statement indicates an expression. For example, it can be a select statement. (2 ). delete table: drop table Name (this command deletes all table descriptions, table integrity constraints, indexes, and table-related permissions) www.2cto.com (3 ). modify the table name: rename clause. Syntax: rename table old_name to new_name new_tbl_name is the new table name. For example, change table a to B: alter table a rename to B. In addition to the alter table command above, you can directly use the renametable statement to change the table name. ● Order by clause (introduced later): used to sort the rows in a certain order when creating a new table. Note: After insertion and deletion, the table will not remain in this order. You can use this option to improve query efficiency after making major changes to the table. In some cases, sorting by column may be easier for MySQL. ● Table_options: Modify the table options. The specific definitions are the same as those in the create table statement. Multiple add, alter, drop, and chang clauses can be written in an alter table statement, separated by commas. This is an extension between MySQL and standard SQL. In standard SQL, each clause in each alter table statement can be used only once. 11. Note: most of the options in a table involve how table data is stored and where it is stored. In most cases, you do not need to specify the table option. The engine option is the storage engine that defines tables. For details, see Appendix E. 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" indicates that the "student ID" field is defined as the primary KEY. "Default 1" indicates that the default value of "gender" is 1. "Engine = InnoDB" indicates that the storage engine used is InnoDB, and InnoDB is the default storage engine of MySQL on the Windows platform. Therefore, "engine = InnoDB" can be omitted. 12. Note: ● ignore: it is an extension of MySQL compared with standard SQL. If duplicate keywords exist in the new table after modification and ignore is not specified, the operation fails when the duplicate keyword error occurs. If ignore is specified, only the first row is used for rows with duplicate keywords, and other conflicting rows are deleted. ● Column_definition: defines the data type and attributes of a column. The specific content is described in the create table syntax. ● First | after col_name: before or after a column. If this parameter is not specified, the column is added to the end. Note: If the Data Type of the column in the table conflicts with that of the column to be modified, an error occurs. For example, if a char-type column needs to be changed to the int type, but the original column value contains the character-type data "a", it cannot be modified. 13. 1). NULL (NULL) the concept of NULL usually indicates unknown, unavailable, or data that will be added later. If a column is allowed to be null, a specific value is not provided for the column when the record value is input to the table. If a column is not allowed to be null, then, the specific value of this column must be provided during input. Note: The table keyword cannot be null. A null value cannot be confused with a numeric value 0 or a character-type null character. Any two null values are not equal. Www.2cto.com 2 ). the IDENTITY attribute of a column can create a flag column for any table that contains the sequence number value generated by the system. A column of the unique sequence number can be used as a key value. Each table can only have one column set as a flag attribute. The column can only be of the decimal, int, numeric, smallint, bigint, or tinyint data type. When defining a flag property, you can specify its seed (I .e. starting) value and increment value. The default values of both are 1. The system automatically updates the flag column value. The flag column does not allow null values. In the following cases, MySQL implicitly changes the column type given by a create table statement (this may also occur in the alter table statement ). 14. MySQL implicitly changes the column type: (1) varchar with a length less than 4 is changed to char. (2) If any column in a table has a variable length, the entire row will become longer. Therefore, if a table contains any variable-length columns (varchar, text, or Blob), all char columns larger than 3 characters will be changed to varchar columns. This does not affect how users use columns in any way. In MySQL, this change can save space and make table operations faster. (3) The display size of timestamp must be an even number and be in the range of 2 ~ Within the range of 14. If 0 is specified or the display size is larger than 14, the display size is forced to 14. From 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number. (4) You cannot store a NULL value in a timestamp column. Setting it to NULL is the current date and time by default. If you want to know whether MySQL uses a column type other than the specified one, use a DESCRIBE statement after creating the table. DESCRIBE statements are described in section 3.1.4. 15 type is defined as follows: Description: After the character data type and numeric data type, MySQL allows you to specify a data type option to change the attributes and functions of the data type. For character data types, MySQL supports two data types: CHARACTERSET and COLLATE. To distinguish between uppercase and lowercase 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 smaller than the length set by the field, fill the remaining part with 0. Spatial_type is spatial data. 16. table option table_option is defined as follows: {engine | type} = engine_name // storage engine | auto_increment = value // Initial value | auto_increment = value // average table row length | [default] charcter set charset_name [collatecollation_name]/ /default Character Set and verification | checksum = {0 | 1} // if it is set to 1, the checksum is obtained. | comment = 'string' // note | connection = 'connect _ string '// connection string | MAX_ROWS = value // The maximum number of rows | MIN_ROWS = value // the minimum number of columns | PACK_KEYS = {0 | 1 | DEFAULT} | password = 'string '// pair. frm file encryption | delay_key_write = {0 | 1} // keyword update | row_format = {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} // defines how each row should be stored 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' // the path of the data file | index directory = 'absolute path todirectory' // index path author tianyazaiheruan