MySQL Learning notes

Last Update:2016-01-25 Source: Internet

Author: User

Tags float double

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

/** Creating a database **/
Create Database testdata;
/**
return to query OK; Indicates that the creation was successful.
**/

/** Deleting a database **/
Drop database testdata;
/**
return to query OK; Indicates a successful creation
**/

/** shows the engines supported by MySQL **/
Show engines;
/**
If the number of columns is too much, the end is not too beautiful, so you can end with \g.
Support for default means that the database defaults to the InnoDB engine.
**/

/** View the default engine **/
Show variables like ' storage_engine% ';
/**
To modify the default engine, modify the Default-storage-engine in the configuration file to the name of the engine you want.
**/

/**
MyISAM default support for table locks
InnoDB supports row locks by default, but sometimes locks the table
**/

/**
Where to note floating point storage:
A float double stores an approximate value, such as 123131309307461630.010 with a float double storing a value of 123131309307461630.000
A string is stored inside a decimal, so a high precision of 123131309307461630.010 stores a value of 123131309307461630.010
**/

/**
char varchar nvarchar

Char
Char is fixed-length, that is, when you enter a character that is less than the number you specify, char (8), the character you enter is less than 8 o'clock, and it will then fill the empty value back. When you enter a character that is larger than the specified number, it intercepts the characters that are out of the bounds.

nvarchar (n)
A variable-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice times the number of characters entered. The input data character length can be zero.

varchar[(N)]
Variable-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. Storage size is the actual length of bytes of input data, not n bytes. The input data character length can be zero.

varchar suitable for keeping some numbers and letters nvarchar suitable for the survival of the man
**/

/** creating a MySQL data table statement **/
Create TABLE Test (
ID INT (one) auto_increment,
Name varchar (55),
Year char (4)
) engine= ' MyISAM ' default Charset=utf8;

/**
After the field type, you can increase the constraint:
The NOT NULL field cannot be empty
Detault default value To field setting default value
**/

Describe table_name;/** view table unit structure, can be simplified to desc table_name**/
Show CREATE TABLE test;/** view SQL statements when creating tables **/
drop table test;/** Delete data table **/
ALTER TABLE Old_tablename Rename new_tablename;/** Modify the name of the tables **/
ALTER TABLE test1 Add age char (4);/** add field, if you want to add the first keyword to the field at the end, if you want to add after the field, you can add the After field name at the end **/

ALTER TABLE test1 drop field name;/** Delete the specified field **/
ALTER TABLE test1 modify age int (2)/** modify the specified field to adjust the field order with first or after **/
ALTER TABLE TEST1 Change AGF address char (4)/** modify field name char (4) is the data type of the old field if it is changed to varchar (4) Then the data type of the new field becomes the latter You can use first or after to adjust the order of the fields **/

/**
Note:
Change to rename a column or alter the type of a column, given the old column name and the new column name, the current type
MODIFY can change the type of a column without renaming it (no new column name is required) and you can modify the order of the columns
**/

/** Index note test4 is the table name **/
InnoDB MyISAM Engine supports Btree index, memory supports hash storage

/** Index Operation **/
ALTER TABLE TEST1 add primary key (ID)/** Add primary key index parentheses can be multiple columns **/
ALTER TABLE test1 Drop primary key;/** Delete primary key index **/
ALTER TABLE test1 add ' index type ' index name ' (field)/* Add non-primary key index, field can be multiple, separated by commas */
ALTER TABLE test1 DROP index name ';/* Delete non-primary key index, index name is the name specified at creation */
Show index from table name;/* View index */

/**
View whether the statement is used for indexing
EXPLAIN SQL statement If Possible_key is not empty, the index is used
**/

/* Normal index start */
/** creating a normal index when creating a table **/
CREATE TABLE Test4 (
ID Int (one) primary key auto_increment,
Name varchar (in) not NULL DEFAULT ' ',
Index name (property name)
);

/** Create a normal index to an existing table **/
Create index index name on test4 (attribute name);
/**alter statement to create a normal index **/
ALTER TABLE TEST4 Add index index name (attribute name);
/**alter Statement Delete Normal index **/
Alert table test4 drop index name;
/* Normal index END */

/** Unique index Start **/
/** Create a unique index when creating a table **/
CREATE TABLE Test4 (
ID Int (one) primary key auto_increment,
Name varchar (in) not NULL DEFAULT ' ',
UNIQUE index index name (attribute name);
);
/** Create a unique index to an existing table **/
Create unique index index name on test4 (property name)
/**alter statement to create a normal index **/
ALTER TABLE TEST4 add unique index name (attribute name);
/** Delete method with normal index **/
/** Unique index End **/

/** Full-text indexing changing the uniqueness in a unique index to Fulltext is the way to create a full-text index **/

/** Delete Index Start **/
/** Delete The syntax form of an index **/
Drop index index name on test4;/* Delete indexes */
/**alter Statement Delete Normal index **/
Alert table test4 drop index name;
/** Delete primary key because the primary key will only have one, so you don't have to specify a name **/
Alert table test4 drop primary key;
/** Delete Index End **/

/** View **/
/** CREATE VIEW syntax in the SQL statement specification, the view naming prefix is typically named view_xxx or v_xxx **/
CREATE VIEW name as SQL statement;

/** using Views **/
SELECT * FROM view name
/** view statements When creating a view **/
Show create view attempted name;
/** View View Structure **/
DESC View Name

/** Deleting a view **/
Drop view name 1, view name 2

/** Modifying a View **/
Create or Replace view view name as SQL statement

/**alter Create modified View method **/
Alter VIEW view name as SQL statement;

/**
A view is a virtual table that is placed into a view virtual table by specifying the fields and values in the table, and the view and base tables are affected by each other, which is equivalent to a snapshot of the contents of the base table if the view is from more than one base table, adding the delete is not effective
**/

/* Trigger */
/*
Three actions to be able to use a trigger:
Insert
Delete
Update
*/

/* CREATE TRIGGER syntax */
--Create Trigger Insert1 before insert on the user for each row insert into nickname (name) values (' Rocky ');

--Basic syntax: CREATE TRIGGER Trigger Name trigger time (After/before) trigger event (insert/delete/update) on table name the statement executed when each row is triggered;
/*
For insert only new
For delete only old
For update old New is common
*/
/*delimiter &&
Create trigger Insert2 after insert
On the user for each row
Begin
Insert into nickname (nickname) values (NEW.UID);
Insert into nickname (nickname) values (NEW.UID);
End
&& delimiter;*/

--The new.uid here is the primary key generated after insertion
/* Trigger multiple statements delimiter end symbol Modify END symbol */

/* View Trigger */
Show triggers;

/* Delete trigger */
Drop trigger trigger name;