3.3.5 Create or delete the database directly in the database directory, just create a directory with the same name as the database under the MySQL data directory, and delete the database by deleting the directory. So, you can do this directly, create or delete a database, or rename a database. This has some meaning for backup and recovery backups. 3.3.6 using the use of the database syntax: Use DB_NAME uses the Db_name statement tells MySQL using the Db_name database as the default database for subsequent queries. The database remains at the end of the session, or another use statement is issued: mysql> use DB1; Mysql> SELECT Count (*) from mytable; # selects from db1.mytable mysql> use DB2; Mysql> SELECT Count (*) from mytable; # selects from db2.mytable
If you are not using the USE statement, then the above example should be written as: mysql> SELECT count (*) from db1.mytable; Mysql> SELECT Count (*) from db2.mytable; Since use is also a command for a MySQL client program, you can end up with no semicolon at the command line, and the client program can get the results. 3.3.7 Summary This section describes SQL statements and utilities for database operations, including:? SQL statement: Create/drop database,show databases,use program mysqladmin? To create or delete a database directly directory 3.4 for data table operations with MySQL, currently (version 3.23) You can have three basic database tables Choose between formats. When you create a table, you can tell MySQL which table type it should use for the table. MySQL will always create a. frm file to save the table and column definitions. Depending on the type of table, indexes and data are stored in other files.
You can use the ALTER TABLE statement to transform between tables of different types. See 7.8 ALTER table syntax.? MyISAM in MySQL 3.23, MyISAM is the default table type, which is based on ISAM code and has a lot of useful extensions. The index is stored in a. MYI (myindex) extension file and the data is stored in a file that has a. MYD (MYData) extension. You can use the Myisamchk utility to check/repair the MyISAM table. ISAM You can also use the abandoned ISAM. This will disappear soon, because MyISAM is a better realization of the same thing. ISAM uses a B-tree index, which is stored in a. ISM extension file and the data is stored in a file with a. ISD extension, you can check/repair the ISAM table using the Isamchk utility. The ISAM tables are not binary portable across os/platforms.? HEAP
The heap table uses a hash (hashed) index and is stored in memory. This makes them faster, but if MySQL crashes, you will lose all the stored data. Heap is available as a temp table! 3.4.1 The information syntax of the data table with the Show/describe statement: Show TABLES [from db_name] [like wild] or show COLUMNS from Tbl_name [from db_name] [L IKE Wild] or show INDEX from Tbl_name (from db_name) or show TABLE STATUS [from db_name] [like wild]
{DESCRIBE | DESC} tbl_name {col_name | wild}
You can use Db_name.tbl_name as an alternative to the tbl_name from db_name syntax.? SHOW tables lists the tables in a given database. You can also use the Mysqlshow db_name command to get this form.
Note: If a user does not have any permissions on a table, the table will not appear in the output in show tables or Mysqlshow db_name. SHOW columns lists the columns in a given table. If the column type differs from what you expect from the CREATE TABLE statement, be aware that MySQL sometimes changes the column type. The describe statement provides information similar to show columns. Describe provides information about the columns of a table. Col_name can be a column name or a string that contains the "%" and "_" wildcard characters of SQL. This statement is provided in order to be compatible with Oracle. Show table status (introduced in version 3.23) runs like show STATUS, but provides more information for each table. You can also use the Mysqlshow--status db_name command to get this form. Show fields is a synonym for show columns, and show keys is a synonym for show index. You can also use Mysqlshow db_name tbl_name or mysqlshow-k db_name tbl_name columns The column or index of a table. SHOW index returns indexing information in a format that is very similar to the ODBC SQLStatistics call. 3.4.2 Using the Mysqlshow tool to get information below is a brief introduction to the usage of the Mysqlshow utility, which is very convenient to use in obtaining the information of the database and the table. Get a list of existing databases: shell> mysqlshow lists existing tables in a database db_name: shell> mysqlshow db_name lists structure information for a database table Db_name.tbl_name: Shell >mysqlshow db_name tbl_name Lists the index of a table: shell> mysqlshow–k db_name tbl_name 3.4.3 Creating a data table with the CREATE TABLE statement The CREATE TABLE statement creates tables. The complete syntax for this statement is quite complex, because there are so many optional clauses, but in practice the application of this statement is fairly straightforward. All the CREATE TABLE statements we used in chapter 1th are less complex. Interestingly, most of the complexities are clauses, and these clauses are thrown out of MySQL after analysis. See appendix1 can see these complex things. 1, CREATE TABLE statement basic syntax CREATE TABLE Tbl_name (Create_definition,...) [Type =table_type] Create_definition:col_name type [not NULL | NULL] [DEFAULT default_value] [auto_increment][primary KEY] in MySQL3.22 or later versions, the table name can be specified as Db_name.tbl_name, Whether or not the current database is available. For example, create a visitor message sheet: shell> mysql–u root–p mysql> CREATE DATABASE mytest; mysql> CREATE TABLE Guestbook (--Visitor VARCHAR (), Comments TEXT, entrydate DATETIME); nbsp
If everything is OK, congratulations, you've built your first table!
The table you created is named Guestbook, and you can use this table to store information about your site visitors. You are creating this table with the Reeate table statement, which has two parts: the first part specifies the name of the table, and the second part is the names and attributes of each field enclosed in parentheses, separated by commas.
Table Guestbook has three fields: Visitor,comments and EntryDate. The visitor field stores the visitor's name, the comments field stores the visitor's opinion of your site, and the EntryDate field stores the date and time that the visitor visited your site.
Note that each field name is followed by a specialized expression. For example, the field name comments followed by the expression text. This expression specifies the data type of the field. The data type determines what data a field can store. Because the field comments contains text information, its data type is defined as text type.
2. How to specify the type of a table you can also specify the type of the table when you create the table, and if you do not specify a table type, the default is the ISAM table in the 3.22 and previous versions, and the default is the MyISAM table in the 3.23 version. You should try to use the MyISAM table. Specifying the type of table is often used to create a HEAP table: mysql> CREATE TABLE fast (ID int,articles TEXT) type=heap;
3. Implied column description changes in some cases, MySQL implicitly changes a column description given in a CREATE TABLE statement. (This may also be in alter TABLE.) )? varchar with a length of less than 4 is changed to char.
? 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 that are greater than 3 characters are changed to VARCHAR columns. This does not affect how you use columns in any way; in MySQL, varchar is just a different way of storing characters. MySQL implements this change because it saves space and makes table operations faster.
? The display size of the timestamp must be even and within the range of 2 to 14. If you specify 0 display size or larger than 14, the dimension is forced to 14. The odd numeric dimensions from the 1~13 range are coerced to the next larger even number.
? You cannot store a literal in a timestamp column null; set it to null to the current date and time. Because the timestamp column behaves like this, the null and NOT NULL properties are not applied in a general way and are ignored if you specify them. DESCRIBE Tbl_name always reports that the timestamp column may have been given a null value.
If you want to know if MySQL is using a column type other than what you specified, issue a describe Tbl_name statement after you create or change your table. 3.4.4 an important concept of using the results of SELECT to create a table relational database is that any data is represented as a table of rows and columns, and the result of each SELECT statement is a table of rows and columns. In many cases, the "table" from select is just an image of the rows and columns that are scrolled along with your work on the display. Prior to MySQL 3.23, if you wanted to save the results of a SELECT in a table for future queries, you had to make a special arrangement: 1) Run the DESCRIBE or SHOW COLUMNS query to determine the column types in the table from which you want to get information. 2) Create a table that explicitly specifies the name and type of the column you just looked at. 3) After you create the table, publish an INSERT ... SELECT queries, retrieve the results, and insert them into the table you created. In MySQL 3.23, all of the changes were made. CREATE TABLE ... The SELECT statement eliminates these wasted time, making it possible to derive a new table directly from the results of a select query. You can complete a task in a single step without knowing or specifying the data type of the column you are retrieving. This makes it easy to create a table that is fully populated with the data you like and prepares for further queries.? If you specify a select,mysql after the Create statement, the new field will be created for all cells in the Select. For example: mysql> CREATE TABLE test-(a int not null Auto_increment,primary key (a), key (b))-SELECT b,c from Test2; This creates a table with 3 columns (A,B,C), where the data for the B,c column comes from the table test2. Note If any errors occur while copying the data into the table, the table is automatically deleted.
? You can copy a table by selecting the entire contents of a table (without a WHERE clause), or by using a WHERE clause that always fails to create an empty table, such as: mysql> CREATE TABLE Test SELECT * from Test2; mysql> CREATE TABLE Test SELECT * from test2 where 0; Creating an empty copy is useful if you want to use load data to load a data file into the original file and not be sure if you have the correct data format specified. You don't want to end up with a malformed record in the original table when you don't get the right option for the first time. An empty copy of the original table allows you to experiment with the option of LOAD data for specific column and row delimiters until you are satisfied with the interpretation of the input data. After you are satisfied, you can load the data into the original table. The Create temporary table and SELECT can be used in conjunction with creating a temporary table as its own copy, such as: This allows modifying the contents of the MY_TBL without affecting the original content. This is useful when you want to experiment with queries that modify the contents of a table without changing the contents of the original table. In order to use pre-written scripts that utilize the original table name, you do not need to edit these scripts to refer to different tables, just add the CREATE temporary TABLE statement at the beginning of the script. The corresponding script creates a temporary copy and operates on the copy, which is automatically deleted by the server at the end of the script. To create a table as an empty copy of itself, you can temporary with the create ... SELECT uses the WHERE 0 clause, for example: But there are a few things to note when creating an empty table. When you create a table that is populated by selecting data, its column name comes from the column name that you select. If a column is evaluated as the result of an expression, the name of the column is the text of the expression. The expression is not a valid column name, and you can run the following query in MySQL to understand this: to work properly, you can provide a legitimate nickname for the column: If you select a column with the same name from a different table, there will be some difficulty. Assume that both tables T1 and T2 have column C, and you want to create a table of all the combinations of rows from two tables. You can provide aliases that specify the unique column names in the new table, such as creating a table by selecting the data to populate and automatically copying the index of the original table. 3.4.5 Modifying the structure of a table with the ALTER TABLE statement sometimes you might need to change the structure of an existing table, so the ALTER TABLE statement will be the right choice for you. Add the row ALTER TABLE Tbl_nameAdd Col_name type For example, add a column to the table weight mysql>alter table pet Add weight int;? Delete column ALTER TABLE Tbl_name drop col_name
For example, delete the column Weight:mysql>alter table pet drop weight; Change column ALTER TABLE tbl_name modify Col_name type For example, change the types of weight: MYSQL&G T ALTER TABLE pet Modify weight samllint; Another method is: ALTER TABLE tbl_name change Old_col_name col_name type for example: mysql> ALTER TABLE pet change weight weight samllint Renaming the column Mysql>alter table pet change weight wei;? renaming table alter tables tbl_name rename NEW_TBL
For example, the Pet table is renamed Animal mysql>alter table Pet Rename animal; Change the type of table
In addition, attributes such as indexes can be added or removed for a column and are no longer detailed, see the appendix.
3.4.6 Delete data table with DROP TABLE statement drop table [IF EXISTS] tbl_name [, Tbl_name,...] Drop table deletes one or more database tables. The Data and table definitions in all tables are deleted, so use this command with caution! In MySQL 3.22 or later versions, you can use the keyword if exists class to avoid an error that does not exist on the table. For example: Mysql>use mytest; Mysql>drop TABLE Guestbook; Alternatively, you can specify both the database and the table: Mysql>drop table mytest. Guestbook; 3.4.7 Summary This section describes most of the operations of the table, and now summarizes the contents as follows:? What are the three types of tables in MySQL? How do I create a table, delete a table? How do I change the structure and name of a table? How to use the Mysqlshow utility 3.5 to insert a row record into a data table 3.5.1 insert a new data syntax using the INSERT statement: Insert [into] tbl_name [(c Ol_name,...)] VALUES (pression,...),... INSERT [into] tbl_name SET col_name=expression, ... Let's start by using the INSERT statement to increase the record, which is an SQL statement that specifies the table for which you want to insert rows of data or put values in rows. The INSERT statement has several forms:? You can specify values for all columns: For example: shell> mysql–u root–p mysql> use mytest; Mysql> INSERT into worker values ("Tom", "[email protected]"); The word "into" is optional since MySQL 3.22.5. (This is also true for other forms of INSERT statements.) The values table must contain the value of each column in the table and is given in the order in which the columns in the table are stored. (Typically, this is the order in which columns are defined when tables are created.) If you are not sure, you can use DESCRIBE tbl_name to view this order. )? Using multiple value tables, you can provide more than one row of data at a time. Mysql>insert into worker values (' Tom ', ' [email protected] '), (' Paul ', ' [email Protected] '); Insert with multiple Values Table ... The form of values is supported in MySQL 3.22.5 or later versions.
? You can give the column to which you want to assign a value, and then list the values. This is useful if you want to create a record that requires only a few columns for initial setup. For example: Mysql>insert into worker (name) VALUES (' Tom '); Since MySQL 3.22.5, this form of INSERT also allows multiple value tables: Mysql>insert into worker (name) VALUES (' Tom '), (' Paul '); Columns that are not given a name in the list of columns are assigned a default value. Since MySQL 3.22.10, it is possible to Col_name = value in the form of the dequeue and values. For example: Mysql>insert into worker set name= ' Tom '; Unnamed rows in the SET clause are assigned a default value. You cannot insert multiple rows using this form of an INSERT statement.
? An expression can reference any column that was previously set in a value table. For example, you can do this: mysql> INSERT into Tbl_name (col1,col2) VALUES (15,col1*2); But this is not possible: mysql> INSERT into Tbl_name (col1,col2) VALUES (col2*2,15); 3.5.2 Using Insert ... SELECT statement inserts a business from another table when we learned to create a table in the previous section, we knew that we could use Select to create tables directly from other tables, and even to replicate data records at the same time. If you already have a table, you can also benefit from the mate of the SELECT statement. Enter data from other tables, for example: Mysql>insert into Tbl_name1 (col1,col2) select Col3,col4 from Tbl_name2; You can also omit the column list for the destination table if you have data entry for each column. Mysql>insert to tbl_name1 select Col3,col4 from Tbl_name2;
INSERT into ... The SELECT statement satisfies the following conditions:
? A query cannot contain an ORDER BY clause. The destination table of the INSERT statement cannot appear in the FROM clause in the SELECT query section because this is forbidden in ANSI SQL to let you select from the table you are inserting. (The problem is that select will likely find records that were previously inserted within the same run time.) When using a sub-select clause, the situation can easily be confused) 3.5.3 using Replace, replace...select statement Insert Replace function is exactly the same as insert, except if an old record in the table has the same value as a new record on a unique index. The old record is deleted before the new record is inserted. In this case, the INSERT statement produces an error. The Replace statement can also be combined with a brown select, so the contents of the last two subsections are perfectly suited for repalce. It should be noted that the Replace statement may change the original record, so use caution. 3.5.4 using Load statements to enter data in bulk This chapter discusses how to use SQL to insert data into a table. However, if you need to add many records to a table, it is inconvenient to use SQL statements to enter data. Fortunately, MySQL provides some methods for bulk data entry, making it easy to add data to a table. These methods are described in this section and in the next section. This section describes the SQL language-level workaround. 1. Basic syntax Syntax: LOAD DATA [LOCAL] INFILE ' file_name.txt ' [REPLACE | IGNORE] into table tbl_name load DATA infile statements are read into a table at a high speed from a text file. If you specify the local keyword, read the file from the client host. If local is not specified, the file must be located on the server. (Local is available in MySQL3.22.6 or later versions.) For security reasons, when reading a text file located on the server, the file must be in the database directory or read by everyone. Also, in order to use the load DATA INFILE on the files on the server, you must have file permissions on the server host. See chapter Seventh Database security. The Replace and Ignore keywords control the repetitive processing of existing unique key records. If you specify replace, the new row replaces the existing rows that have the same unique key value. If you specify ignore, skip the input of the duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored.
If you use the local keyword to load data from a native file, the server has no way to stop the transfer of the file during the operation, so the default behavior is as if ignore were specified.
2. File search principle when looking for a file on a server host, the server uses the following rules:
? If an absolute pathname is given, the server uses that pathname.? If you give a relative pathname to one or more predecessor parts, the server searches for files relative to the server's data directory.
? If you give a file name without a predecessor, the server looks for the file in the database directory of the current database. Note that these rules imply that a file like "./myfile.txt" is read from the server's data directory, while a file given as "MyFile.txt" is read from the database directory of the current database. Also note that for the following statements, the DB1 file is read from the database directory, not the DB2:
mysql> use DB1; mysql> LOAD DATA INFILE "./data.txt" into TABLE db2.my_table; 3, fields and Lines clause syntax if you specify a fields clause, each of its clauses (TERMINATED by, [optionally] enclosed by and escaped by) is also optional, except that you must specify at least one of them.
If you do not specify a fields clause, the default value is the same as if you write: Fields TERMINATED by ' \ t ' enclosed by ' escaped ' if you do not specify a lines clause, the default value is the same as if you write: LI NES TERMINATED by ' \ n '
In other words, when the default value causes the input to be read, the LOAD DATA infile behaves as follows:? Look for line boundaries at line breaks? to divide rows into fields at a locator
? Do not expect the field to be encapsulated by any quotation mark characters? A locator, newline character, or "\" that begins with "\" is interpreted as part of the literal character of the field value LOAD DATA infile can be used to read files obtained from external sources. For example, a file in dBASE format will have a comma-delimited field surrounded by double quotation marks. If the lines in the file are terminated by a newline character, the command shown below describes the field and row processing options that you will use to mount the file:
mysql> LOAD DATA INFILE ' data.txt ' into TABLE tbl_name fields TERMINATED by ', ' enclosed by ' ' LINES TERMINATED by ' \ n ’;
Any field or row processing option can specify an empty string ('). If not empty, the fields [optionally] enclosed by and fields escaped by value must be a single character. Fields TERMINATED by and lines TERMINATED by value can be more than one character. For example, write a line terminated by a carriage return newline pair (CR+LF), or read a file containing such a line, specifying a lines TERMINATED by ' \ r \ n ' clause. field [optionally] enclosed by controls the bounding character of the fields. For output (SELECT ... Into OUTFILE), if you omit optionally, all fields are surrounded by the enclosed by character. An example of such an output (using a comma as the field delimiter) is shown below:
"1", "a string", "100.20" "2", "a string containing a, comma", "102.20" "3", "a string containing a \" quote "," 102.20 "" 4 "," a String containing a \ ", Quote and comma", "102.20"
If you specify that the optionally,enclosed by character is used only to enclose char and varchar fields:
1, "A string", 100.20 2, "a string containing a, comma", 102.20 3, "a string containing a \" quote ", 102.20 4," a string contain ing a \ ", Quote and comma", 102.20
Note that the appearance of the enclosed by character in a field value is escaped by using the escaped by character as its prefix. Also note that if you specify an empty escaped by value, it may produce output that cannot be read correctly by load DATA infile. For example, if the escape character is empty, the output shown above is shown below. Notice that the second field in line fourth contains a comma that follows the quotation marks, which (incorrectly) seems to terminate the field:
1, "A string", 100.20 2, "a string containing a, comma", 102.20 3, "a string containing a" quote ", 102.20 4," a string Containi ng a ", quote and comma", 102.20
The fields escaped by controls how special characters are written or read. If the fields escaped by character is not empty, it is used to prefix the following characters on the output: Fields escaped by character fields [optionally] enclosed by character fields TERMINATED by and line The first character of the S TERMINATED by value is ASCII 0 (actually writes subsequent escape characters to ASCII ' 0 ' instead of a 0-value byte) if the fields escaped by character is empty, no characters are escaped. Specifying a spin-off character may not be a good idea, especially if the field value in your data contains any of the characters from the table just given.
For input, if the field escaped by character is not empty, the occurrence of the character is stripped and subsequent characters are literally used as a part of the value of the fields. An exception is an escaped "0" or "n" (that is, \ s or \ n If the escape character is "\"). These sequences are interpreted as ASCII 0 (a 0-value byte) and null. See the following rules for null handling. Citation: http://hi.baidu.com/dofeil/item/d132d8177675e4721009b532
MySQL basic syntax
