MySQL Tech Insider Fifth edition

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