MySQL database detailed self-taught 1

I. List of studies

1. Understanding the database and MySQL

2.mysql Connection

3. Introductory statement

4. Detailed column types

5. Adding and deleting changes

INSERT into table name (column 1,......  column n) VALUES (value 1,...... Value n);

* (column 1,...... Column N) allows no write, and if not written, all columns are inserted by default

INSERT into table name values (value 1,...... Value n);

*values the input order of the corresponding values is consistent with the order of the fields in the table

Change:

Update table name set column 1 = new value 1, column 2 = new value 2, where expr

* Which watch is changed? What columns of values do you need to change? What value should I change to? Which lines are in effect?

By deleting:

Delete from table name where expr

* What table data do you want to delete? Which lines do you want to delete?

Check:

Select column 1, column 2,.. Column n from table name where expr

* What table data is checked? Which columns do you want to select for queries? Which rows do you want to select?

6. Detailed explanation of 5 seed statements

7. Connection Query

8. Sub-query

9. Table engine and Character set

10. Index and index optimization

11. Triggers

12. Business

13. Backup and Recovery

Second, the entry statement

View all libraries

Create a library

Delete a library

Select Database

View Table

Create a table

CREATE TABLE table name (column 1 [column property default], column 2 [column property default],..... column n [column property default]) engine = Storage Engine CharSet = Character Set

Delete a table

Change table name

Iii. detailed column types 3 large column types

Numeric (integer column, floating-point column)

Character-type columns

Date/Time Type

A. Integer column storage range and occupied space

B. Optional properties for an integer column

tinyint (M) unsigned Zerofill

M: Width (only makes sense when the 0 is filled)

Unsigned: Unsigned type (non-negative)

zerofill:0 fill, (Default unsigned)

C. Floating-point and fixed-point

float: floating-point decimal: fixed-point decimal more precise

Float (m,d)

Decimal (M,D) 6,2

M: Accuracy (total number of digits, not including points)

D: Scale (decimal digit)

D. String type

E. Time-period type

F. Special type of NULL

Null is not false, it is not true, but "empty"

Any operator, the judge encounters NULL, all have NULL

Null can only be judged with is null,is NOT NULL

Null affects query speed, generally avoids making a value null

Iv. Introduction to the seed sentences of select 5

Where condition Query

GROUP BY group

Having screening

Order by sort

Limit number of result bars

Five, connection query

Left join connecting to the right connected inside

Function: Remove the associated data from 2 or more tables

Connection query syntax for ① connection query

The syntax for the left connection. Select ltable.*, rtable.* from ltable left joins rltable on ltable.colname = Rtable.colname

The syntax for inner joins. Select ltable.*, rtable.* from ltable inner join rltable on ltable.colname = Rtable.colname

The difference between the left and right connection of ② connection query and the inner connection

: Interchangeable left and right connections

: A LEFT Join B is equivalent to B right join A

The inner connection is the intersection of the left and right connections

: MySQL has no external connection

Six, sub-query

Sub-query is in the original query statement,

Embed a new query to get the result set we want.

Generally according to the sub-query embedded location divided into, where sub-query, from sub-query

①where Sub-query

The where subquery is the condition of the outer SQL query as the result of the query of the inner-layer SQL statement.

Typical syntax:

SELECT * from tableName where colname = (select ColName from Tbname where ...) {Where colname in (select ColName from Tbname where.)}

Typical cases:

1: Check the latest item

2: Find out all the items under a big column

②exists Sub-query

exists that is: the outer SQL query to find the row into the inner layer of SQL query, to make the inner query can be set up

Queries can be interchanged with in-type subqueries, but are much more efficient.

Typical syntax:

SELECT * FROM tablename where exists (SELECT * FROM tablename where ...)

Typical cases:

1: Find out all the items under a big column

③from Sub-query

From-type subquery: The result of an inner SQL statement query is used as a temporary table for the outer SQL statement to query again.

Typical syntax: SELECT * FROM (SELECT * from TableName where ...) where ....

Typical cases:

1: Check out the latest products under each column

2:bbs the latest posts in each column

Vii. storage engine and character set

Purpose of Study:

For speed and security requirements, choose a reasonable storage engine.

Considering the problem of multiple languages and porting, select a reasonable character set.

Viii. What is the concept of storage engine?

Databases have different ways of storing and managing the same data in MySQL, called the storage engine.

① storage engine with its features

② Storage Engine Selection

Articles, news and other security requirements are not high, choose MyISAM

Orders, funds, bills, train tickets and other high security requirements, you can choose InnoDB

For temporary transit tables, you can use the memory type, the fastest

Nine, Character set (charset)

One word Character set

A character set is a mapping table of characters <-> binary bytes

The character set is a set of symbols and encoding rules, whether in the Oracle database or in the MySQL database, there is a character set selection problem, and if in the database creation phase does not select the correct character set, you may need to replace the character set in the late, and the replacement of the character set is a relatively expensive operation, There is also a certain risk, so we recommend that at the beginning of the application, the right set of characters should be selected according to the requirements, avoiding unnecessary adjustments at a later stage.

Selection of the ① character set

1: Space-saving recommendations use a small character set as much as possible if you can fully satisfy your application. Because a smaller character set means that it saves space, reduces the number of bytes transmitted over the network, and improves the performance of the system due to the small amount of storage space indirectly. There are many character sets that can hold Chinese characters, such as UTF8, gb2312, GBK, GB18030, and so on, but gb2312 and GBK are commonly used. 2: Compatibility Because the gb2312 font is smaller than the GBK font, some remote words (for example: Minghe) can not be saved, so in the selection of character sets, must weigh these remote words in the application of the probability and the impact of the occurrence,

3: On the internet, the trend of internationalization is inevitable, and storage space has been more quantitative, it is recommended to use UTF8, if the development of intranet systems, such as internal OA, etc., you can consider GBK.

Ten, proofing rules collate (understand can)

In a word, the proofing rules are the "seating table" for the characters in the character set.

Please give a b b c c D d in ascending order

Xi. Character Set topic-completely garbled

Client Character Set clients

Server storage, query, comparison character set (connection)

Query result Character Set results

① Character Set topic-New and old website coexistence case

12. What is index and optimization index?

All the "directories" in front of the dictionary are missing please find out the word ' wah '

How to quickly find a word can be added to the dictionary directory for the database, the role of the index is to add the "data" directory

① index and Optimization index algorithm (understanding)

With n Random records, without an index, the average search for N/2 times, then after the index?

Btree (binary tree) index

Hash (hash) index

The advantages and disadvantages of ② Index and optimization index

Good outside:

Faster query speed (SELECT)

Harm:

Reduced increase, delete, change speed (Update/delete/insert) increase the file size of the table (the index file may even be larger than the data file)

The use principle of index and optimization of ③ index

But the degree index

Index criteria columns (where the most frequently-followed conditions are more appropriate for the index)

Indexed hash values, values that are too concentrated do not index for example: to the gender "male", "female" index, the meaning is not big

Index type of ④ index and optimization

Normal index (indexed)

Primary key index (primary key)

Unique index (unique)

Full-text index (fulltext)

Index creation syntax for ⑤ index and optimization (1)

Declare indexes directly when you build a table:

CREATE TABLE TableName (

Column 1 column type column properties,

....

Column n column type column properties,

Primary key (column name),

Index (column name),

Unique (column name),

Fulltext (column name)

) Engine xxxxx CharSet xxxx

Index creation syntax for ⑥ index and optimization (2)

Indexing by modifying tables

ALTER TABLE add index (column name);

ALTER TABLE add unique (column name);

ALTER TABLE Add primary key (column name);

ALTER TABLE ADD fulltext (column name);

⑦ index and optimization of index Delete syntax

To delete a primary key:

ALTER TABLE drop PRIMARY key

To delete other indexes:

ALTER TABLE DROP INDEX index name

Note: The index name is typically the column name, and if not, you can view the index by show index from TableName

13. Trigger

Learning Goals:

Definition OF triggers

Application of the Trigger

Mastering the creation syntax of triggers

A simple trigger is created

① Trigger Definition

In the development of database application software, we sometimes encounter some data changes in the table, hoping to cause the need of other related data changes, and use the trigger to meet the demand. It can automatically complete certain queries when some specific data in the table changes. Using triggers can not only simplify the program, but also increase the flexibility of the program.

A trigger is a special kind of transaction,

You can monitor a data operation (Insert/update/delete) and trigger related actions (Insert/update/delete)

② Trigger Applications

1. When you add or delete records to a table, you need to synchronize them in the related tables. For example, when an order is generated, the inventory of the goods purchased by the order is correspondingly reduced.

2. When the value of a column of data on a table is associated with data in another table. For example, when a customer makes a payment, a design trigger can be used to determine whether the customer's accumulated arrears exceed the maximum when the order is generated.

3. When a table needs to be tracked. For example, when a new order is generated, it is necessary to notify the relevant personnel in time for processing, you can design and add a trigger on the order form to implement

③ triggers create 4 elements of syntax

Watch location (table)

Monitoring Event Insert/update/delete

Trigger Time After/before

Triggering event Insert/update/delete

④ Trigger Creation syntax

Syntax for creating triggers

Create TRIGGER Trigger name After/befor (trigger time) Insert/update/delete (monitor event) on table name (monitor address) for each row begin SQL1; SQLN; End

⑤ trigger Reference row variable (1)

⑥ the deletion of triggers

Drop Trigger Triggername

14. The concept of business affairs

Thinking: I went to the bank to send a friend remittance, my card has 1000 yuan, a friend card 500 yuan, I transfer 50 Yuan to a friend (no handling fee), if, my money just buckle, and friends of money and no overtime, cable broken, how to do?

ACID Properties of ① transactions

Atomicity (atomicity): atoms mean the smallest particles, or things that cannot be divided. The principle of non-re-division of database transactions is atomicity. All queries that make up a transaction must either be executed or all canceled (as in the bank example above).

Consistency (consistency): a rule that refers to data, which should be consistent before/after a transaction

Isolation (Isolation): The simple point is that the operation of a transaction is not visible to other transactions.

Persistence (Durability): When a transaction is complete, its effect should be preserved and cannot be undone

The use of ② transactions

Open transaction (START transaction) Execute SQL operation (Normal SQL operation) commit/rollback (Commit/rollback)

Note: When building a table, select the InnoDB engine

XV, Backup and recovery

① Backup command

Methods for backing up all tables under a separate library

MYSQLDUMP-UUNAME-PPASSWD dbname >/dir/filename

Ways to back up a few tables under a library

MYSQLDUMP-UUNAME-PPASSWD dbname table1 table2. Tablen >/dir/filename

Ways to back up multiple libraries

Mysqldump-uname-ppasswd-b DB1 DB2 >/dir/filename

Ways to back up all libraries

Mysqldump-uname-ppasswd-a >/dir/filename

② Restore command

1: Operation at command line

MYSQL-UUNAME-PPASSWD [databasename]</dir/filename

2: Log in to MySQL after source operation

mysql> use dbname;

Mysql> Source/dir/filename;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The source of this article: http://www.cnblogs.com/suihui/p/3172049.html

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you for sharing the original author here.

MySQL database detailed self-taught 1