MySQL Tech Insider Fifth edition

Source: Internet
Author: User
Tags create index

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.