MySQL database basic instruction (full)

Database Basic Types

Char fixed-length string char (10) memory two-bit 10-bit query has advantages
varchar variable-length string varchar (10) Two-bit two-bit storage advantage

Enumeration type can only take one
The set type can take multiple

Binary type Common blobs

First, avoid creating database and table in Chinese garbled and view encoding method

#vim/etc/mysql/my.cnf. (after 5.5 system) the following modifications:
[Client]
Default-character-set=utf8
[Mysqld]
Default-storage-engine=innodb
Character-set-server=utf8
Collation-server=utf8_general_ci

1, when creating the database:
CREATE DATABASE ' Test '
CHARACTER SET ' UTF8 '
COLLATE ' Utf8_general_ci ';

2. Create table ' Database_user ' (
' ID ' varchar (+) not NULL default ' ',
' UserID ' varchar (+) not NULL default ' ',
) Engine=innodb DEFAULT Charset=utf8;

CREATE TABLE ' T_student ' (
' ID ' double,
' Stuname ' varchar (60),
' Age ' double,
' Sex ' varchar (30),
' Gradename ' varchar (60)
);
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 1 ', ' Zhang San ', ' 23 ', ' Male ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 2 ', ' Zhang Sanfeng ', ' 25 ', ' Male ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 3 ', ' John Doe ', ' 23 ', ' Male ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 4 ', ' Harry ', ' 22 ', ' Male ', ' third grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 5 ', ' Jenny ', ' 21 ', ' Female ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 6 ', ' Li Na ', ' 26 ', ' Female ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 7 ', ' Wang Feng ', ' 20 ', ' Male ', ' third grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 8 ', ' Mona ', ' 21 ', ' Female ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 9 ', ' small black ', ' 22 ', ' Male ', ' first grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 10 ', ' herd ', ' 25 ', ' Male ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 11 ', ' Little Zhang San ', ' + ', NULL, ' second grade ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 12 ', ' small Zhang San ', ' 23 ', ' Male ', ' sophomore ');
Insert INTO ' t_student ' (' IDs ', ' stuname ', ' age ', ' sex ', ' gradename ') VALUES (' 13 ', ' Zhang Sanfeng ', ' "'", ' ", ') ', ' the ' second Grade ');

Inquire

Single-Table Query
In statement
SELECT * from T_student WHERE age in (21,23);

Not in statement
SELECT * from T_student WHERE age is not in (21,23);

Between and
SELECT * from T_student WHERE age between and 24;

Not between and
SELECT * from T_student WHERE age not between and 24;

Like fuzzy query
% stands for any character
_ Represents a single character
SELECT * from t_student WHERE stuname like ' Zhang% ';
SELECT * from t_student WHERE stuname like ' Zhang _ ';
SELECT * from t_student WHERE stuname like ' Chang __ ';(two underscores for two characters)

Null value query
SELECT * from t_student WHERE sex is NULL;
SELECT * from t_student WHERE sex is not NULL;

Conditional query with and
SELECT * from T_student WHERE gradename= ' first grade ' and age=23;

Conditional query with OR
SELECT * from T_student WHERE age=21 or age=23;

Distinct to repeat the query
SELECT DISTINCT gradename from T_student;

Sort the results of a query
SELECT * from T_student order by age DESC; (descending)
SELECT * from T_student ORDER by age ASC; (ascending)

GROUP BY group Query
Group BY property [having conditional expression][with ROLLUP]
1. No meaning for use alone
SELECT * from T_student GROUP by Gradename;
1 323 men and one grade
4 Kings, 522 men, third grade.
2 three-Feng 25 men and two-year

2. Working with the Group_concat () function
Group_concat () calculates which rows belong to the same group and displays the columns that belong to the same group.
Which columns to return are determined by the function parameter (that is, the field name).
The grouping must have a standard, which is to group by the columns specified by the group by.

SELECT Gradename,group_concat (stuname) from T_student GROUP by Gradename;
11 Grade Zhang San, little Black, John Doe, Jenny
73 grade Wang Feng, Harry
62 Grade Li Na, Mona, Zhang Sanfeng, herd, small Zhang San, small Zhang San, Zhang Sanfeng Small

3. Working with aggregation functions
11 Grade 4
43 Grade 2
22 Grade 7

4. Use with having (limit the result of the output)
SELECT
Id
Gradename,
COUNT (Stuname)
From
T_student
GROUP by
Gradename
Having
COUNT (Stuname) > 3;

11 Grade 4
22 Grade 7

5. Use with the WITH rollup (last add a sum line)
SELECT Gradename,count (stuname) from T_student GROUP by Gradename with ROLLUP

11 Grade 4
73 Grade 2
62 Grade 7
613

SELECT Id,gradename,group_concat (stuname) from T_student GROUP by Gradename with ROLLUP

11 Grade Zhang San, little Black, John Doe, Jenny
73 grade Wang Feng, Harry
62 Grade Li Na, Mona, Zhang Sanfeng, herd, small Zhang San, small Zhang San, Zhang Sanfeng Small
6 Zhang Three, Xiao Hei, John Doe, Jenny, Wang Feng, Harry, Li Na, Meng na, Zhang Sanfeng, herd, small Zhang San, small Zhang San, Zhang Sanfeng Small

6. LIMIT Paging Query
Select field 1, Field 2 from table name limit initial position, per page size;
SELECT * from T_student LIMIT 0, 5;
SELECT * from T_student LIMIT 5, 5;
SELECT * from T_student LIMIT 10, 5;
1 323 men and one grade
2 three-Feng 25 men and two-year
3 Li 423 male first grade
4 Kings, 522 men, third grade.
5 Jenny 21 Female first Grade

6 Li Na 26 female sophomore
7 Wang Feng 20 male third grade
8 Mona 21 Female sophomore
9 Small Black 22 male first grade
10 herd 25 Men second Grade

11 Little 3,212 Grade
12 small Zhang 323 men and two grades
13 Zhang Sanfeng Small 242 grade

Querying using Aggregate functions

CREATE table ' T_grade ' (
' id ' int,
' stuname ' varchar,
' course ' varchar,
' score ' int
) ENGINE =innodb DEFAULT Charset=utf8;
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 1 ', ' Zhang San ', ' language ', ' 91 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 2 ', ' Zhang San ', ' math ', ' 90 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 3 ', ' Zhang San ', ' English ', ' 87 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 4 ', ' John Doe ', ' language ', ' 79 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 5 ', ' John Doe ', ' math ', ' 95 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 6 ', ' John Doe ', ' English ', ' 80 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 7 ', ' Harry ', ' language ', ' 77 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 8 ', ' Harry ', ' Math ', ' 81 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 9 ', ' Harry ', ' English ', ' "");

1, COUNT () function
1) Statistics records the number of bars
SELECT count (*) as total from T_grade;
2) used with the group by function.
SELECT Stuname,count (*) from T_grade GROUP by Stuname;
33
Li 43
King 53

2, sum () function
1) sum
SELECT stuname,sum (score) from T_grade WHERE stuname= "Zhang San";
2) Use the
SELECT stuname,sum (score) from T_grade Group by Stuname with the group by function;
3,268
Li 4,254
King 5,247

3, AVG () function
1) averaging
SELECT Stuname,avg (score) from T_grade WHERE stuname= "Zhang San";
2) Use the
SELECT Stuname,avg (score) from T_grade Group by Stuname with the group by function;
The
4, Max ()/min () function
1) asks for the maximum value of
Select Stuname,course,max (Score) from T_grade WHERE stuname= "Zhang San";
2) used with the group by function, course cannot be added here because there is stuname
SELECT Stuname,max (score) from T_grade GROUP by Stuname;

Connection query: Connect two or more tables, and then select the data you want, by connecting them to a condition.
Cartesian product
SELECT * from T_book,t_booktype;

1, INNER join query
The connection query is one of the most common connection queries. An inner JOIN query can query two or more than two tables.
SELECT * from T_book,t_booktype WHERE t_book.booktypeid=t_booktype.id;
Alias:
SELECT tb.bookname,tb.author,tby.booktypename from T_book tb,t_booktype tby WHERE tb.booktypeid=tby.id;
2, outside the link query
External link can find out all the information of a table
SELECT property name list from table name 1 left/right join table name 2 on table Name 1. property name 1= table Name 2. Property names 2
2.1 left JOIN query
can query out "table name 1 , and in Table name 2, only matching records can be queried. Mismatched with null
SELECT * from T_book left JOIN t_booktype on t_book.booktypeid=t_booktype.id;
Alias:
SELECT tb.bookname,tb.author,tby.booktypename from T_book TB left JOIN t_booktype tby on tb.booktypeid=tby.id;
2.2 The right connection query
can query out all the records for Table name 2, and in table name 1, you can only query for matching records.
SELECT * from T_book right joins T_booktype on T_book.booktypeid=t_booktype.id;
SELECT tb.bookname,tb.author,tby.booktypename from T_book TB right joins T_booktype Tby on Tb.booktypeid=tby.id;
3, multi-conditional connection query
SELECT tb.bookname,tb.author,tby.booktypename from T_book tb,t_booktype tby WHERE tb.booktypeid= Tby.id and tb.price>70;

Sub-query

CREATE TABLE ' T_pricelevel ' (
' ID ' int,
' PriceLevel ' int,
' Price ' float,
' Description ' varchar (300)
);
Insert INTO ' t_pricelevel ' (' IDs ', ' pricelevel ', ' price ', ' description ') VALUES (' 1 ', ' 1 ', ' 80.00 ', ' expensive book ');
Insert INTO ' t_pricelevel ' (' IDs ', ' pricelevel ', ' price ', ' description ') VALUES (' 2 ', ' 2 ', ' 60.00 ', ' affordable books ');
Insert INTO ' t_pricelevel ' (' IDs ', ' pricelevel ', ' price ', ' description ') VALUES (' 3 ', ' 3 ', ' 40.00 ', ' cheap book ');

1. Query with in keyword
The condition of one query statement may fall into the query results of another SELECT statement.
SELECT * from T_book where Booktypeid in (select ID from T_booktype);
SELECT * from T_book where Booktypeid not in (select ID from T_booktype);

2. Query with comparison operator (single)
A subquery can use a comparison operator, and the result of a query cannot be a collection if it can be compared.
SELECT * from T_book where price>= (select Price from T_pricelevel where pricelevel=1);

3, query with exists keyword
If the subquery queries to the record, the outer query is executed, otherwise the outer query is not executed.
Select * from T_book where exists (SELECT * from T_booktype);
Select * from T_book where NOT EXISTS (SELECT * from T_booktype);

4, subquery with any keyword (collection)
Any keyword means that any of these conditions are met
Select * from T_book where price >=any (select price from T_ PriceLevel);

5, the query with the ALL keyword
All keyword indicates that all conditions are met
Select * from T_book where price >=all (select Price from T_pricelevel); The

Merge query
1, UNION
Database system merges all query results together and then removes the same record.
Select ID from t_book Union Select from ID from T_booktype; The
2, UNION all
Database system merges all query results together and does not eliminate the same records.
Select ID from t_book UNION ALL select from ID from T_booktyp

to alias table and field:
1, alias for table
Format: alias of table Name table
Select * FR Om T_book TB where tb.id=1;
Alias Field
Format: property name [as] alias
Select Tb.bookname bname from T_book TB where tb.id=1;
Select Tb.bookname as bname from T_book TB where tb.id=1;

Inserting, updating, and deleting data

1. Insert data
To insert data into all fields of the table
Format: INSERT into table name VALUES (v1,v2,v3,..., Vn);
INSERT into t_book values (NULL, ' database ', 23, Wind Neumann, 1);
INSERT into T_book (Id,bookname,price,author,booktypeid)
values (NULL, ' database ', 23, Wind Neumann, 1);

Specify fields in the table to insert data
Format: INSERT into table name (attribute 1, property 2,..., attribute N) VALUES (v1,v2,..., Vn);
INSERT into T_book (bookname,author) VALUES (' Database ', wind Neumann);

inserting multiple records simultaneously
INSERT into T_book (Id,bookname,price,author,booktypeid)
values (NULL, ' database ', 23, Wind Neumann, 1),
(NULL, ' Database 2 ', 23, Wind Neumann, 1)
(NULL, ' Database 3 ', 23, Wind Neumann, 1); How

Inserts a value into a column of a specified row in sql: Insert does not support where update

updates data
Update t_book set bookname= ' Java programming idea ', price=120 where id = 1;
Update T_book set bookname= ' Data original ' where bookname like '% i love my home% ';

Delete data
Delete from T_book where id = 5;

Index

Index Introduction
Index definition: The index is composed of one or more columns in a database table, which is used to
improve the query speed of the data in the table; a book-like directory that facilitates quick positioning, searching for
Find the specified content.

Advantages and disadvantages of indexes
Pros: Improve query data speed
Cons: Increased time to create and maintain indexes

2016/1/6 Review here
Index classification
1. General Index
Can be created in any data type
2. Uniqueness Index
You can use the unique parameter to set the limit that the value of the index must be when creating a unique index
is unique, the primary key is the unique index by default
3. Full-Text Indexing
Use the fulltext parameter setting to create only the Char,varchar,text type
On the field. The main function is to improve the speed of querying large string types, Mysql
The default engine does not support full-text indexing
4. Single-Column indexing
You can create an index for a single field in a table
5. Multi-column index
Index of tables created on multiple fields
6. Spatial index
Spatial indexes can be set using the spatial parameter, which can only be built in space
Data types, which improves the efficiency of the system's access to spatial data. MySQL default engine does not support

Create an index

1. Create an index when creating a table
CREATE Table Table name (
Property name data type [integrity constraint],
Property name data type [integrity constraint],
....
Property name Data type
[UNIQUE | Fulltext | SPATIAL] Index| KEY
Alias (Property name 1 [(length)] [ASC | DESC])
);
CREATE TABLE T_user1 (
ID INT (11),
UserName VARCHAR (20),
PASSWORD VARCHAR (20),
INDEX (UserName)
);
Uniqueness Index
CREATE TABLE T_user2 (
ID INT (11),
UserName VARCHAR (20),
PASSWORD VARCHAR (20),
UNIQUE INDEX (UserName)
);
Take aliases
CREATE TABLE T_user2 (
ID INT,
UserName VARCHAR (20),
PASSWORD VARCHAR (20),
INDEX Index_username (UserName)
);
Multi-column Index
CREATE TABLE T_user3 (
ID INT,
UserName VARCHAR (20),
PASSWORD VARCHAR (20),
INDEX Index_username_password (userName, password)
);
To create an index on a table that already exists
Create an index on username on T_user3
Create INDEX Index_username on T_user3 (userName); To have an index name
Create a unique index on username on T_user3
Create unique index index_username on T_user3 (userName);
Creating multi-column indexes in T_user3
Create INDEX Index_username_password on T_user3 (Username,password);

To create an index with an ALTER TABLE statement
ALTER TABLE T_USER3 Add index Index_username (userName);
ALTER TABLE T_user3 add unique index index_username (userName);
ALTER TABLE T_USER3 Add index Index_username_password (Username,password);

Delete Index
Drop index index_username on T_user3;

View
1, a view is a virtual table that is a table from one or more tables in a database.
2, only the definition of the view is stored in the database, and the data in the view is not stored, which is stored in the original table.
3, when querying data using a view, the database system will fetch the corresponding data from the original table.

The role of the view
1, make the operation simple;
2, increase the security of data;
3, improve the logical independence of the table;

1. Create a View
CREATE [Algorithm ={undefiend | MERGE | TempTable}]
View view name [(property list)]
As SELECT statement
[With [cascaded | LOCAL] CHECK OPTION];

2. Single table Create a view
CREATE VIEW v1 as SELECT * from T_book;

CREATE VIEW v2 as SELECT bookname,price from T_book;
SELECT * from v1;//can only check the data in the view, the other is not found, to ensure the security

CREATE VIEW v2 (b,p) as SELECT bookname,price from T_book; Replace the name on the View field

3. Create a view with multiple tables
CREATE VIEW v4 As select Tb.bookname,tby.booktypename from T_book TB, T_booktype Tby where tb.booktypeid=tby.id;

4. View View
DESC v2; View basic information about a view
Show TABLE STATUS like ' v2 '; View basic information, different from the above
Show TABLE STATUS like ' T_book ';

Show CREATE VIEW v2; View detail Information

5. Modify the View
Create or Replace view statement modify views
CREATE OR REPLACE [algorithm ={UNDEFINED | MERGE | TempTable}]
View view name [(property list)]
As SELECT statement
[With [cascaded | LOCAL] CHECK OPTION];

CREATE
OR REPLACE VIEW v1 (bookname, price) as SELECT
BookName,
Price
From
T_book;

Alter statement Modify view
ALTER [Algorithm ={UNDEFINED | MERGE | TempTable}]
View view name [(property list)]
As SELECT statement
[With [cascaded | LOCAL] CHECK OPTION];

ALTER VIEW v1 as SELECT * from T_book;

6. Update the View
Update view refers to inserting (insert), Updating (update), and deleting (delete) tables with data from the viewport. Because the view is a virtual
The table in which no data is intended. When you update through a view, you are converting the base table to update. When you update a view, only the data within the permission range is updated.
is out of range and cannot be updated.
Inserting data
Insert INTO V1 values (null, ' Java good ', +, ' Fei ', 1);
Update data
Update v1 set bookname= ' java very good ', price=200 where id=5;
Delete data
Delete from v1 where id=5;

7. Delete a view
Deleting a view means deleting a view that already exists in the database. When you delete a view, you can only delete the definition of the view and not delete the data;
Drop view [IF EXISTS] View name list [RESTRICT | CASCADE]
Drop view if exists v2;

Trigger
A trigger (TRIGGER) is an action triggered by an event. These events include INSERT statements, UPDATE statements, and DELETE statements.
When the database system executes these events, the trigger is activated to perform the appropriate action.
Example: Inserting data in a table, triggering B table inserting data

2. Creating and Using triggers

Create a trigger that has only one execution statement
CREATE TRIGGER Trig_book after INSERT
On T_book for each ROW

CREATE TRIGGER Trig_book after INSERT
On T_book for each ROW
UPDATE t_booktype SET booknum=booknum+1 WHERE new.booktypeid=t_booktype.id;
New is a transition variable that represents the newly inserted table

To create a trigger with more than one execution statement
CREATE TRIGGER trigger name before | After Trigger event
On table name for each ROW
BEGIN
Execute statement List
END

CREATE TRIGGER Trig_book2 after DELETE
On T_book for each ROW
BEGIN
UPDATE t_booktype SET booknum=booknum-1 WHERE new.booktypeid=t_booktype.id;
INSERT into T_log VALUES (null, ' delete a piece of data in the T_book table ');
DELETE from T_test WHERE old.booktypeid=t_test.id;
END

The following statement executes an error in the database and has not been modified correctly
Delimiter |
CREATE TRIGGER Trig_book2 after DELETE
On T_book for each ROW
BEGIN
UPDATE t_booktype SET booknum=booknum-1 WHERE old.booktypeid=t_booktype.id;
INSERT into T_log VALUES (Null,now (), ' delete a piece of data in the T_book table ');
DELETE from T_test WHERE old.booktypeid=t_test.id;
END
|
Delimiter

3. View triggers
Show TRIGGERS statement View trigger information ==> all triggers

4. Delete a trigger
DROP TRIGGER trigger name;

MySQL database basic instruction (full)