Learning notes for MySQL

good understanding of SQL statements: columns: Understanding the variables that can be calculated Where: Understood as an expression, put in a row to see if it's true Check out the results can be as a table to understand, select to apply select Comprehensive query;

Five types of queries

1. where
2. Group
3. Havding
4. ORDER BY
5. Limit

It must be used in the order of the five query methods, otherwise the syntax does not pass; that is, where group has an order by limit

The parsing order of standard SQL is: (1). FROM clause, assemble data from different data sources (2). WHERE clause to filter records based on specified criteria (3). GROUP BY clause, dividing data into multiple groupings (4). Use aggregate functions for calculations (max avg min ...) (5). Use the HAVING clause to filter the grouping (6). Calculate all expressions (limit) Select name from score where name is not in (select name from score where score<80) group by name; To illustrate: In the Student scores table (Tb_grade), the "Examinee name" content is not empty records according to "examinee name" group, and filter the results of the group, select "Total Score" greater than 600 points. Select Candidate Name, Max (total) From Tb_grade Where name is NOT NULL GROUP BY S_name Having a total of >600 ORDER by Max (from score) In the Student score table, the "examinee name" content is not empty records according to "examinee name" group, and filter the results of the group, select "Total Score" greater than 600 points. Analyze the sentence: Divide the name into the overall condition: the name is not empty Max is greater than 600 The standard sequential SQL statements are: Select Candidate Name, Max (total) as Max Total From Tb_grade Where examinee name is not NULL Group BY candidate name Having Max (total) > 600 ORDER BY Max Total In the example above, the SQL statements are executed in the following order: (1). First execute the FROM clause to assemble data from the Tb_grade table from the data source (2). Execute a WHERE clause to filter data that is not NULL for all data in the Tb_grade table (3). Execute the GROUP BY clause to group the Tb_grade table by the Student Name column (4). Calculate the max () aggregate function and the maximum number of the total scores by "total" (5). The HAVING clause is executed, and the total score of the screening course is greater than 600. (7). Execute the ORDER BY clause to sort the final results by "Max Score" (7). Use order by to sort the result set

When inserting the MySQL data under the Command Line window, if the Chinese garbled characters cannot be inserted, use the command set names GBK; Insert multiple rows of data can be inserted into Stu (Stuid,stuname,stuage) VALUES (3, ' John Doe ', (4, ' Wang four ', 23), separated by commas. MySQL Common commands:

1. Login: MySQL start MySQL service cmd--net start MySQL (this is the service name)
2. Mysql-h localhost-u root-p (password)
3. Hit the wrong command with \c back to the edit screen
4. See how many libraries a database has: show databases;
5. Creating a library: Create Database kuname;
6. Using a library: use TableName; (Do not show, do it directly, if you are familiar with the database)
7. Show table: show tables; (Note is multiple tables: tables)
8. Delete a library: Dorp database kuname;\\\\\\\\\\\mysql does not allow the names of its own databases to be modified
9. 1064 in MySQL is a syntax error;
10. Modify table Name: Rename table Oldtablename to Newtableneme (library name cannot be changed)
11. Delete a table: you guess how to delete it?
12. View table structure: DESC tablename
13. Add a field to a table: ALTER TABLE tablename add field name type; for example: ALTER TABLE admin add age tinyint;
14. How do I set the field type to be unsigned? : When declaring, add unsigned after the field type; (unsigned)
15. tinyint (M) unsigned Zerofill
16. Copy a table structure that already exists: CREATE TABLE tablename like the name of a table that already exists so that the created table has no data and the knowledge structure is the same.

where m stands for width, but only when Zerofill is available,

Unsigned: unsigned

Zerofill:0 fills for example: 0001 0002 0003,

the difference between char and varchar :

char (m) indicates a fixed-length string, if the actual storage is not enough M, then add a space after the time, take out, when the space is removed, (so you really want to save the space when it is not saved)

varchar (M) variable length string,

Speed: Char fixed length faster some selection principles: 1, Space utilization 2. Speed

Text:??

Year Type

The year type is a single-byte type used to represent years.

MySQL retrieves and displays the year value in yyyy format. The range is 1901 to 2155.

You can specify year values in various formats:

· A four-bit string with a range of ' 1901 ' to ' 2155 '.

· A four-digit number, ranging from 1901 to 2155.

· A two-bit string with a range of ' 00 ' to ' 99 '. Values of ' 00 ' to ' 69 ' and ' 70 ' to ' 99 ' range are converted to a year value of 2000 to 2069 and 1970 to 1999 ranges.

· A two-bit integer that ranges from 1 to 99.

1 to 69 are converted to 2001 to 2069

70 to 99 is converted to a year value of 1970 to 1999 range.

Note that the two-bit integer range differs slightly from the two-bit string range because you cannot specify 0 directly as a number and interpret it as 2000. You must specify it as a string ' 0 ' or ' 00 ' or it is interpreted as 0000.

· The result returned by the function, whose value is appropriate for the year context, such as now ().

The illegal year value is converted to 0000.

in real development, such as the registration time, product release date and so very useful datetime type, because datetime convenient to view but inconvenient to calculate, the time stamp should be used to identify the date in development . Timestamp : From 1970-01-01 00:00:00 to the current number of seconds, Insert data from one library into another table: INSERT INTO TableName SELECT * * * * FROM;Query a column field: Not all columns are queried select Sutid,stuname from Stu; MySQL data type integer is divided into: tinyint\ smallint\ mediumint \ int \ bigint such as declaring a tinyint, the default is a signed, that is (-128 to 127)

Type	Bytes	Minimum value	Maximum Value
		(Signed/unsigned)	(Signed/unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
Mediumint	3	-8388608	8388607
		0	16777215
Int	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

CREATE TABLE Admin (
ID int PRIMARY KEY,
Username varchar (20),
Password varchar (20)
)

How to understand where: the Where condition as an expression and the comparison of each row to see if it is true, where only the table on the hard disk function, the query out in the buffer does not work.

How to understand the columns behind select: To understand the column name as a variable, the variable can be calculated,

Check the backlog of goods under each column (Commodity stock * price)

Select goods_id, sum (goods_num* Goods_pirce) from goods group by goods_id;

Query each column backlog of money, and the payment of more than 20,000 of the column;

Select goods_id, sum (goods_pirce* goods_number) as Huokuan from goods group by GOODS_ID have Huokuan > 20000;

Fuzzy query: Like

Like after plus conditional% pass with any character _ pass a single character

The GROUP BY

Group rows by field (keyword: max min sum count ...)

1. Check the price of the most expensive item select MAX (Goods_price) from goods;
2. Select Max (goods_id) from goods for the item with the maximum product number;
3. Check the price of the cheapest commodity select min (goods_id) from goods;
4. Query total inventory for all goods select SUM (goods_number) from goods;
5. Check the number of most expensive items under each column select CAT_ID, max (Shop_price) from goods group by CAT_ID;
6. Product categories under each column Select cat_id count (*) from goods group by CAT_ID;

Use of Limit

On the last side of the statement, the function of the line limit entry

Limit [Offset]n

Offset: Offsets (optional parameter, if not written, defaults to 0. From the first selection)

N: Remove entry (required parameter)

Eg: Take out the most expensive item in the database:

Select goods_id, goods_name from goods order BY goods_price desc LIMIT 1;

Difficulty point: To go out of each column of the most expensive products

Select Goods_id,goods_name from goods group by goods_id ORDER by DESC LIMIT 1;

Sub-query:

Where sub-query: The query result of the inner layer as the comparison condition of the outer query

From sub-query: The query results of the inner layer as a temporary table, a common layer of SQL query again

Exists sub-query: The outer query results to the inner layer, to see if the inner layer of the query condition is established

EG: Check the latest product:

Select Goods_id,goods_name from goods where goods_id = (select Max (goods_id) from goods);???? The legendary sub-query;

Find the most expensive items in each column:

First step: First to find out the most expensive incidentally: select Max (goods_price) from goods group by goods_id;

Step Two: Query select goods_id, Goods_name,goods_price where Goods_price in (select Max (goods_price) from goods Group B Y goods_id);

Union query: Union

A federated query is two or more different tables.

The query condition is: The two tables query out the same number of columns. The organization of the table can be inconsistent.

If two tables are combined to query the data with the same rows and values, the condition of not specifying union will automatically repeat, if you do not need to go to the weight plus all

Eg:select * from a union SELECT * from B (de-weight) SELECT * From a UNION ALL SELECT * from B (no weight)

Take two tables and add the values of the same fields:

Select ID, sum (number) from (SELECT * From a UNION ALL SELECT * from B) as flag Group by ID;

If the sentence is followed by an order by or limit, the two words must be expanded.

The order by with limit is only meaningful if not configured Limit,order by will be removed by the parser. Just think where 1 constant set up without affecting performance;

Add Columns:

Add columns, default to the next column in the table, which column the new columns can be declared with after class; The first Class

ALTER TABLE tablename ADD column name int *******;

Delete Column

ALTER TABLE tablename drop column name;

Modify column: ALTER TABLE tname change modified column name declaration NEW column: ALTER TABLE m changed m mmm int;

Views: What is a view??? If

A view is a virtual table generated by the query result;

CREATE view as SELECT statement.

Modify a view: As with Create, change create to alter, essentially overwriting the old view;

1. View-to-table relationships:
   1. If the table's data changes, then the view's data must have changed.
   2. View modifications affect tables, but views are not always modifiable.
      1. When the view data corresponds to the table data one by one, modifying the view at this time will have an impact on the table. Also, for INSERT, the view should also contain columns that do not have a default value.

Why you have a view:

1. Easy to simplify the query, if you always need a query results to do complex queries.
2. Do permission control because there are tables that are inconvenient for others to see. This makes it possible to create a view for others to see.
3. Big data tables can be used: for example, when the number of rows in a table exceeds a lot: More than 2 million is more obvious, this time you can

Transactions: Four features ACDI features

1. Refers to a set of operations that either executes or does not perform--------atomicity (non-splitting of atoms) (atomicity)
2. The total amount of data before and after a transaction must be consistent-------consistency (consistenty)
3. Other sessions cannot see the process of transactions-----------Isolation (lsolation) before all operations are completed
4. The impact of a transaction cannot be undone------------persistence (durability)

If an error occurs, the transaction is not allowed to be revoked, only through compensatory transactions.

Database backup

1 Incremental backup

2. Overall backup

Common Backup commands:

1. Export a library (in library): Mysqldump-u root-p-B kuname1 kuname2 kuname3 > D:\\kuname.sql
2. Export all libraries: Mysqldump-u root-p-A > Path/backup name. sql
3. Export all tables (in tables) under a library: mysqldump-u root-p kuname >d:\\kuname.sql
4. Export a table under the library: Mysqldump-u root-p kuname t_name1 t_name2 t_name3 >d:\\t_name.sql

Restore command:

1. Restore the:mysql> source D:\\***.sql to the library unit; A word ok (already logged in case)
2. A table for unit recovery:mysql> use Ku_name

Mysql> source d:\\***.sq; (two steps)

Index: The directory created for the data

Benefits: Faster Query speed

Disadvantage: Reduce the speed of adding and deleting

Principles of Index creation:

1. Do not over-index

2. Index where the most frequent places are

3. Try to index the hash value, too concentrated value index is not very meaningful

Normal index: Index speeds up query

Unique index: Values on a unique row cannot be duplicated

Primary KEY index: Primary key cannot be duplicated

Full-text index: Fulltext (internal index of the article, not fuzzy query, fuzzy query is a row of the search, efficiency is very low)

To view the index of a table:

Show index from T_name;

To build an index:

ALTER TABLE name add primary key (column name)

ALTER TABLE indicates that the Add Index/unique/fulltext index name (column name) does not write the index name by default and the column name is the same

Delete Index

ALTER TABLE name DROP INDEX name;

Learning notes for MySQL