MySQL Basics-Summary

/************************************************mysql Database View related Information statement *****************************************/

SELECT version ();--View the database revision number

SELECT user (), current_user ();--Production view current user

SELECT database ();--View the currently in use databases

SELECT curdate (), current_date ();--View current date

SELECT curtime (), Current_time ();--View current time

SELECT now (), Current_timestamp ();--View the current timestamp

Show charset;--View all the character encodings supported by MySQL

SHOW CHARSET like ' s% '; --view character encodings that begin with S

Show VARIABLES like ' char% ';--view variable named Char at the beginning of the variable

Show engines;--View all engines supported by the database

Show databases;--View all databases

Show tables;--See all the tables

Show CREATE Database aa;--view standard build database statements

Show CREATE table aa;--view standard built-in statements

Show full COLUMNS from student;--View all field information for a table

Desc student;--View table structure [describe abbreviation: DESC

/*************************************************** creating a database statement **************************************************** **/

CREATE DATABASE AA;

Create database ' AA ';--creates a db with a name that can be a keyword. Main role: Enhanced compatibility

CREATE DATABASE IF not EXISTS bb DEFAULT Charset=utf8;

CREATE DATABASE ' AA '/*!40100 DEFAULT CHARACTER SET UTF8 */;

CREATE DATABASE ' AA '/*!40100 DEFAULT CHARACTER SET GBK */;

CREATE DATABASE ' AA '/*!40100 DEFAULT CHARACTER SET gb2312 */;

Use aa;--Select a database

Drop database IF EXISTS aa;--Delete databases

/********************************************************** data Table related statements ********************************************/

CREATE TABLE IF not EXISTS student (

Column name, type, decoration, constraint

Sid INT (Ten) UNSIGNED Zerofill PRIMARY KEY auto_increment,

Sname VARCHAR (Ten) is not NULL,

Sgender ENUM (' Male ', ' female ', ' confidential ') DEFAULT ' male '

) Engine=myisam DEFAULT charset=utf8;--standard Build Table statement

Create TABLE bb like student;--fast tables, with Student table as template to create the same structure of the same constraint table, no data

CREATE TABLE AA SELECT * from student;--Quick build tables Create a new table with student queried data without constraints

drop table IF EXISTS aa;--Delete one of the tables

drop table IF EXISTS aa,bb,cc;--Delete multiple tables

RENAME table AA to bb;--modifying the tables name

ALTER TABLE aa RENAME BB; --Modify Table name

ALTER TABLE AA RENAME to bb;--modify the tables name

ALTER TABLE student Change sname snames VARCHAR (20);--Modify the column name and type of the table

ALTER TABLE student Change Sid Sid INT (10);

ALTER TABLE Student Drop PRIMARY key;--Delete primary KEY (if the current primary key does not have a self-increment field, if there is a self-increment field first executes the above statement, clear the Auto_increment field)

ALTER TABLE Student Add CONSTRAINT PK PRIMARY key (SID);--Add primary key

ALTER table Student Add column sage;--adds a row at the end of the table

ALTER TABLE Student Add column saddress after sanme;--adds a row after a field

ALTER TABLE Student Add column Sscore first;--at the beginning

ALTER TABLE student Drop column score;--delete a row

ALTER TABLE stu Add CONSTRAINT FK FOREIGN key (Stid) REFERENCES tec (TID);--Add a FOREIGN KEY constraint (the field must be present with the same type and decoration)

ALTER TABLE Stu ADD CONSTRAINT FK FOREIGN Key (Stid) REFERENCES Tec (TID) on delete set null;--Delete Foreign Key association, the related content is set to null

ALTER TABLE Stu ADD CONSTRAINT FK FOREIGN Key (Stid) REFERENCES Tec (TID) on delete cascade;--Delete related data when foreign Key association is deleted

ALTER TABLE Stu ADD CONSTRAINT FK FOREIGN Key (Stid) REFERENCES Tec (TID) on update cascade;--Update related content when foreign key associations are updated

Alter TABLE Stu ADD CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Tec (TID) on delete set NULL on UPDATE cascade;--delete set NULL, modify update

/*

Above on DELETE set NULL: FOREIGN key set to null

Above on Delete/update CASCADE: FOREIGN KEY Cascade

Note: When using FOREIGN KEY constraints, the data table's storage engine is InnoDB

*/

ALTER TABLE stu Drop FOREIGN key fk;--Delete foreign key

/*********************************************************** Index Operation *********************************************** ****/

--In general, if you create a table when the PK (primary key), FK (foreign Key), UK (UNIQUE constraint key) database system will automatically index, the role of the index: the large amount of data can improve the retrieval speed

Create index Myindex on Stu (Sscore DESC);--Creating indexes

ALTER TABLE Stu ADD index Myindex (sscore DESC);--Create an index

Drop index myindex on stu;--Delete indexes

ALTER TABLE stu Drop INDEX myindex;--Delete indexes

Data type of/******************************************************mysql ********************************************** **/

--Numeric type

Bit--bits type (only 0 and 1 are saved)

TINYINT--short integer range is signed: -128~127, unsigned number: 0~255

SMALLINT--Small integer range is signed number: -32768~32767, unsigned number: 0~65535

Mediumint--middle integer range is signed: -8388608~8388607, unsigned number: 0~16777215

INT--integer range is signed number: -2147483648~2147483647, unsigned number: 0~4294967295

BIGINT--Large integer range is signed number: -9223372036854775808~9223372036854775807, unsigned number: 0~18446744073709551615

--Precision decimal digit type

NUMERIC (P,s)--p represents the total length of the value, s represents the reserved decimal place, and if p=s, only small trees smaller than 1

Decimal (P,s)--p represents the total length of the value, s represents the reserved decimal place, and if p=s, only small trees smaller than 1

--Character type

CHAR (n)--Fixed length string, n<=255, can hold n characters

VARCHAR (n)--variable length string, n<=65535

Text--literal type, maximum storage of 65,535 characters

Mediumtext--Medium text type, can store 16,777,215 characters

Longtext--long text type maximum can be saved 4GB

Enum (' Male ', ' female ', ' secret ')--enum type take one of them

--Binary file types

Longblob--The main storage binary file, the maximum can be 4G

--Date type

Date--dates can only be kept for the month and day illegal for: 0000-00-00

DateTime-Date and Time type save month day seconds

Time--Save 18:30:35 seconds

TIMESTAMP--Timestamp type

CREATE TABLE T1 (

Sname VARCHAR (10),

Stime TIMESTAMP Default Current_timestamp ()--Update data when no default time is set, time modified to modify data

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE T1 (

Sname VARCHAR (10),

Stime DATETIME DEFAULT Current_timestamp ()--time is always set when data is inserted or when data is inserted

) Engine=myisam DEFAULT Charset=utf8;

/************************************************* constraint-Related keywords ****************************************************/

--not null non-null constraint

-The default default value constraint, which is no longer used if a value is specified

--enum enumeration, can only go inside the value or NULL, cannot make other values

--PRIMARY Key Primary key has a primary key in a table and can have only one and cannot be duplicated

CREATE TABLE T1 (

Tid INT UNSIGNED not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (20)

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE T2 (

Tid INT UNSIGNED not NULL auto_increment,

Tname VARCHAR (20),

PRIMARY KEY (TID)

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE T3 (

Tid INT UNSIGNED not NULL auto_increment,

Tname VARCHAR (20),

PRIMARY Key (Tid,tname)--Create composite primary key, one primary key management two tables

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE T4 (

Tid INT UNSIGNED not NULL,

Tname VARCHAR (20)

) Engine=myisam DEFAULT Charset=utf8;

ALTER TABLE T4 ADD CONSTRAINT pk PRIMARY KEY (TID);

--The above can be used to establish a primary key, but the last one is not self-increment primary key

--FOREIGN key FOREIGN KEY constraint, two table storage engine with InnoDB, and a foreign key in one table is the primary KEY or UNIQUE constraint key of another table

--building teachers ' tables

CREATE TABLE Teacher (

Tid SMALLINT UNSIGNED not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (20)

) Engine=innodb DEFAULT Charset=utf8;

--Establish student table pk[primary key], fk[foreign key], a table's foreign key points to another table's primary KEY or UNIQUE constraint key, in a table inside and outside the key can not have, must have the correct

CREATE TABLE Student (

Sid INT UNSIGNED Zerofill not NULL PRIMARY KEY auto_increment,

Sname VARCHAR () not NULL,

Sscore INT,

Stid SMALLINT UNSIGNED,

--Constraint FK foreign key (Stid) References teacher (TID)

--CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (tid) on delete set null

--CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (TID) on DELETE Cascade

--CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (tid) on update CASCADE

CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (tid) on DELETE SET NULL on UPDATE CASCADE

) Engine=innodb DEFAULT Charset=utf8;

INSERT into teacher VALUES (NULL, ' teacher Zhang ');

INSERT into teacher VALUES (NULL, ' Miss Liu ');

INSERT into teacher VALUES (NULL, ' Miss Li ');

INSERT into teacher VALUES (NULL, ' Miss Huang ');

SELECT * from teacher;

INSERT into student VALUES (NULL, ' Zhuge Liang ', 70, 3);

INSERT into student VALUES (NULL, ' Big Joe ', 75,4);

INSERT into student VALUES (NULL, ' Zhang Fei ', 80, 1);

INSERT into student VALUES (NULL, ' Guan Yu ', 95,2);

INSERT into student VALUES (NULL, ' Liu Bei ', 56,2);

INSERT into student VALUES (NULL, ' Caocao ', 37, 3);

INSERT into student VALUES (NULL, ' Liao Gai ', 76, 1);

INSERT into student VALUES (NULL, ' Sun Quan ', 89,4);

SELECT * from student;

--The associated teacher table, when the primary key is not set (on delete set Null,on delete Cascade,on UPDATE CASCADE), the following statements perform an error, violating the FOREIGN KEY constraint:

INSERT into student VALUES (NULL, ' Little Joe ', ' 25 ', 10);

DELETE from teacher WHERE tid = 3;

UPDATE teacher SET tid = 5 WHERE tid = 2;

DROP TABLE teacher;

--The associated teacher table, when the primary key is set (on delete set Null,on delete Cascade,on UPDATE CASCADE), the table is more cascading:

CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (tid) on delete set null--When deleting an association, the related content is set to null

CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (tid) on delete CASCADE--Delete related content when deleting an association

CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (tid) on update CASCADE--Update the relevant content when you modify the association

CONSTRAINT FK FOREIGN KEY (Stid) REFERENCES Teacher (TID) on DELETE set NULL on UPDATE CASCADE--delete set empty, modify update

DROP TABLE student;

DROP TABLE teacher;

TRUNCATE student;--truncate the data in the table, reinsert the primary key from the beginning of the self-increment

TRUNCATE teacher;

-Unique key only constraint

CREATE TABLE T5 (

Tid INT UNSIGNED zerofill not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (20),

UNIQUE KEY (Tname)

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE T6 (

Tid INT UNSIGNED zerofill not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (a) UNIQUE KEY

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE T7 (

Tid INT UNSIGNED zerofill not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (20)

) Engine=myisam DEFAULT Charset=utf8;

ALTER TABLE T7 ADD UNIQUE KEY (tname);

--You can add a unique constraint above

This article is from the "Night Wind" blog, please be sure to keep this source http://jiangkun08.blog.51cto.com/6266992/1547241

MySQL Basics-Summary