Chapter II
In all the statements that relate to database, the schema keyword and the database synonym
1. Query the current reply, and the sql_mode of the global reply
Select @ @session. Sql_mode;
SELECT @ @global. sql_mode;
Set sql_mode = ' traditional '
Set GLOBAL Sql_mode = ' traditional '
2.4. Character Set support
Queries the current server supports those character sets, and collation
Show CharSet; # show Character set; SELECT * FROM INFORMATION_SCHEMA. Character_sets;
To view settings for a server
Show collation; #select * from INFORMATION_SCHEMA. collations;
Show variables; View variables for the current terminal
Show global variables; View Server Global Variables
2.5. Database operations
Create
Create database if not exists test1 character set UTF8 collate utf8_general_ci;
To view the build database statement
Show CREATE Database test1;
Delete
Drop database test1;
Modify Database
ALTER DATABASE test1 [character set UTF8] [collate utf8_general_ci];
2.6 Data Sheet operation
Create a table statement, set default characters, and sort rules
CREATE table if not exists tbl_name (...) character set character encoding collate collation engines = InnoDB;
Temporary table, when the current reply is returned, the table is automatically deleted, and if it is created, the existing table is temporarily hidden if it has the same name as the table in the current library.
Create temporary table Tbl_name.
Delete a table
drop table tbl_name;
Modify Table
ALTER TABLE Tbl_name ...
New Index
CREATE INDEX:
Delete Index
Drop Index ...
View the Build Table statement
Show CREATE table tbl_name;
View basic information for a table
Desc Tbl_name;
2.6.24 creating tables based on other tables or query results
Copy a table structure, including the indexes and the properties of each column, only a single table can be completely copied, columns cannot be selected, and so on.
Create [temporary] table tbl_name1 like tbl_name;
Insert data from a query,
INSERT INTO TBL_NAME1 select * from Tbl_name.
Directly save the query results to a new table, you can query multiple columns of multiple tables at the same time, generate a new table,
CREATE TABLE tbl_name1 Select ID from tbl_name;
CREATE TABLE T3 select T1.c t2.c as C2 from T1 inner join T2;
Explicitly defining a partial column, you need to provide explicitly defined columns in the query.
CREATE TABLE Tbl_name (I not unsigned, T time,d decimal (10,5))
Select
1 as I,
Cast (Curtime () as time) as T,
Cast (pi () as decimal (10,5) as D;
Forcing type conversions
Cast (Val as type), converting a value to a type
allowable casts of type, binary, char, date, DateTime, Time, signed, signed integer, unsigned, unsigned integer, decimal
View current server support for those storage engines
Show engines; # select * from Information_schema.engines;
MySQL Tech Insider Fifth edition