Like Peng Web Study notes (v) MySQL Foundation

MySQL Basics

First, the database concept
1, the user equipment information, forum post information, QQ friend relationship information, student information management system, etc. to "persist" to save to a place,
If you write to a file via Io, it can be very cumbersome and not conducive to multiple people sharing data

2, we develop most of the software, Web sites to use a large number of stacks, and even develop games, mobile apps to use the database, the company interview when the database assessment at more than One-third

3, the database is the storage of data storage, you can easily put the data in, and the data according to various needs to be taken out.
Database Management System,dbms is the software that manages the database (such as pruning and checking).
Common DBMS are MySQL, Oracle, DB2, MSSQLServer and so on.
MySQL is open source, free, so the most widely used

4, different DBMS usage is similar, mastering one is basically equal to mastering the other DBMS

II. databases, tables, columns

1,table (table): "Bookshelf lattice" for storing data

2, two kinds of names: Columns/fields (field)

Third, MySQL environment installation

1, download
Http://www.mysql.com/downloads/MySQL Enterprise Edition (commercial), MySQL Cluster CGE (commercial) are paid editions; MySQL Community Edition is an open source free version.

2, adding system services
Run command line as Administrator, CD to MySQL Bin folder, execute "Mysqlld-install"

Uninstall Service: Mysql-remove


Iv. MySQL Management tools

The 1,mysql management tool is used to manage DBMS database creation, table creation, and so on. Developer must install. Management tool is not Dbms,dbms

2, there are many management tools: Navicat, Workbench (official), PhpMyAdmin, SQLyog, Mysql-front, the usage is similar.
NaviCat Lite is the free version, the function of learning enough.

3, after installation in the "Premiumsoft" in the Start menu.
Main Menu "file" → "New Connection" → "MYSQL", "Connection name" arbitrarily, "hostname or IP address" to fill MySQLServer IP address or host name,

127.0.0.1 (local loopback address), the user name password is root (the password in the production environment to change a bit more complex).

4, if the connection does not error, instructions MySQLServer and management tools are installed no problem

V. PRIMARY KEY (PrimaryKey)

1, the primary key is the unique identifier of the data row. Columns are not duplicated to be primary keys. A table can have no primary key, but it is very difficult to handle,
So there's no special reason to set the primary key for the table

2, the primary key has two selection strategies:
Business primary key and logical primary key

Business primary key is the use of business-meaningful field key, such as a social Security number, bank account, etc.

A logical primary key is a field that uses no task business meaning, a key that is purely for the program to see and that the business person does not look at.

The logical primary key is recommended because it is difficult to ensure that the business primary key is not duplicated (the ID number is duplicated) and does not change (account rise)


Vi. Association between tables, foreign keys (ForeignKey)

1, one of the fields in a table points to the primary key of the other table, which connects the information of the two tables together.

2, can be multiple tables "in series", can also "many-to-many" between the two tables exist a relationship table


Vii. construction of the building of the library table

1, the root node right-click, create a new database, the database name made sense, such as "Study1", the character set is recommended with UTF-8.

2, under the "Table" node under Study1 right-click "New Table", "field" is actually refers to the column.

Id (primary key, name of the unspoken rule, int, not allowed to be empty, right-click "PRIMARY Key")
Name (nvarchar, length 10, not allowed to empty)
Gender (bit, not allowed to empty)
Save As "T_persons"

3. Common errors in building a table:

Column name/table name do not use possible keywords, do not have spaces (including front and back), do not have special characters

4,mysql has a lot of storage engines, most commonly InnoDB and myisam,myisam are more efficient, but do not support transactions, foreign key constraints and other features,
Therefore, it is generally recommended to use InnoDB, and the new version defaults to InnoDB.

How to set up: "Options" → "engine" when building a table. Built how to look at the engine: right-click on the table on the "Object Info" button.

5, Common data types
Text:
CHAR (*): A fixed-length string of up to 255 bytes, whose length must be specified at creation time
VARCHAR (*): A variable-length string of up to 255 bytes, whose length must be specified at creation time
Text: variable length text with maximum 64K characters
Tinytext: Long text with a maximum length of 255 characters
Meduimtext: variable length text with maximum 16K characters
Longtext: variable length text with maximum 4GB characters

Integer: (Choose the smallest possible type after considering the value of the data)
Tinyint:1 bytes. Signed values: 128 to 127; unsigned values: 0 to 255
Smallint:1 bytes. Signed values: 32768 to 32767; unsigned values: 0 to 65535
Mediumint:3 bytes.
Int:4 bytes
Bigint:8 bytes

Decimal: (You need to specify the length and decimal point, which is the display width and scale):
Decimal: Precisely stored decimals, stored internally with strings, suitable for exact types such as amounts. Alias: NUMERIC
Float:4 bytes, single precision. Approximate storage (*), higher efficiency than decimal.
Double:8 bytes, double precision. Approximate storage (*), higher efficiency than decimal.

Date Time:
Date:4 bytes. Range: 1000-01-01--9999-12-31
Time:3 bytes. Range: -838:59:59--838:59:59
Datetime:8 bytes. Range: 1000-01-01 00:00:00--9999-12-31 23:59:59

Binary Big Data:
Tityblob: Maximum length of 255 bytes
BLOB: Maximum length is 64KB
Mediumblob: Maximum length is 16MB
Longblob: Maximum length is 4GB

Viii. Getting started with SQL statements

1, can not always use the mouse point to point to manually manage the data, to be able to communicate through the language and the database automatically, SQL statement is the database "chat" dedicated statement

Strings in 2,sql statements are generally enclosed in single quotes

The 3,SQL statement is case insensitive

Find the "query"--"new query" that executes SQL statements in 4,navcat, write SQL and click "Run" to execute SQL statement

5, the simplest sql:

View all data for a table: SELECT * FROM T_persons

6,insert
Simple SQL statement to insert the data:
Insert into t_persons (Id,name,age,gender) VALUES (5, ' Jim ', 20, 1)

The INSERT statement can save the column name after the token name, but strongly does not recommend

If the values of some fields in the inserted row are indeterminate, then insert does not specify those columns. Columns that are "not allowed to be empty" cannot be omitted when inserting

Auto Increment: Field self-increment avoids problems such as concurrency, and does not allow programmer code to control self-increment. Use the self-increment field to insert without specifying a value.

7. Ways to modify Table structure: Point "design Table"

1, the "Allow null" field is modified to "not allowed to empty", you need to first give the old data default (with the UPDATE statement),
If the previous test data does not matter, you can delete the old data (increase the Height field)

2. Modify the data type of the column to be aware that the old data is compatible with the new type, as well as when modifying the length of the data.

8, Data Update

To update a column:
Update T_persons Set age=30

To update multiple columns:
Update t_persons s Set age=30,name= ' Tom '

Expression: UPDATE t_persons Set age=age+1

9, Conditional Data Update

Update part of the data:
Update t_persons Set age=30 where name= ' Tom ', using the WHERE statement to update only the row that Name is ' Tom ',
Note that SQL equals judgment with a single =, not = =.

In where you can also use complex logic to determine the update t_persons Set age=30 where name= ' Tom ' or age<25,or is equivalent to the Java | |

Where (age>20 and age<30) or (age=80)

Other logical operators that can be used in where: or, and, not, <, >, >=, <=,! = (or <>), etc.

10, Data deletion
To delete all the data in the table:
DELETE from T_persons

Delete some of the data in a table
Delete from T_persons where age>20

Delete just deletes the data, the table is still there,

11, Delete table
Drop Table T_persons

IX. Data retrieval
1, simple data retrieval: Select * from T_persons

2, retrieve only the required columns:
SELECT number from T_employees,
SELECT Name,age from T_employees

3, Column Aliases
SELECT number as name, name as Age111 from T_employees
Write not write as all line

4, computed column
SELECT number, name name, age age111,age+10 ten years later, 1+1,now () from T_employees

5, use where to retrieve the data for the compound condition:
SELECT Name from T_employees WHERE salary<5000

6, you can also retrieve data that is not associated with any table:
Select;
Select Now ();

X. SUMMARY of data
1,sql Aggregation functions:
MAX
MIN
Avg
SUM
COUNT
2, maximum wage for employees older than 25 years:

SELECT MAX (Salary) from T_employees WHERE age>25

3, minimum wage and maximum wage:

SELECT MIN (Salary), MAX (Salary) from T_employees

4, number of employees older than 25:

SELECT COUNT (*) from T_employees WHERE age>25

5, the total wage of all employees:

SELECT SUM (Salary), AVG (Salary) from T_employees

XI. Data sequencing

The 1,order by clause at the end of the SELECT statement allows you to sort by one or more columns, and you can specify whether the sort is ascending (ASC) or descending (DESC)

2, sort the list of all employee information in ascending order of age:
SELECT * from T_employees ORDER by age ASC

3, according to the age from the big to the small sort, if the same age is the salary from the big to the small sort:
SELECT * from T_employees ORDER by age desc,salary DESC

The 4,order by clause is placed after the WHERE clause:
SELECT * from T_employees WHERE age>23 ORDER by age desc,salary DESC

12. Wildcard Filter

1, wildcards are filtered using a like

2, a single-character matching wildcard is a half-width underscore "_", which matches an individual occurrence of a character.
Start with any character and the remainder is "Erry":
Select * from t_employees where Name like ' _erry '

3, the wildcard character matches the half-width percent "%", which matches any character that appears any number of times (0 or more).
k% matches a string of any length starting with K
Retrieve employee information with the letter N in the name:
SELECT * from t_employees where name like '%n% '

4,like performance is poor, it is easy to cause full table scan, use with caution.
Database optimizations (indexes, etc.) are followed, and full-text indexing is done for searches in the project

13. Null Value Processing

1, in a database, a column if no value is specified, the value is NULL, and NULL in the database is "not known", not a representation.
So the select null+1 result is null because "I don't know" adds 1 to the result or "Don't know"

2,select * from t_employees where name = NULL;
SELECT * from T_employees where name!=null;
None of the results are returned because the database "doesn't know"
Select Name+ "A" from t_employees

Null value in 3,sql using is NULL, is not NULL
SELECT * from t_employees WHERE NAME is null;
SELECT * from T_employees WHERE NAME is not null;

14. Limit

The Limit keyword is used to limit the result set returned, and the limit is placed at the last position of the SELECT statement.
The syntax is: the first line of the limit line number, the maximum number of result sets to return

For example, the following SQL statement will return a maximum of five records from the second row (the line number starts at 0) that are not empty by name, in descending order of wages
Select 8 from T_employees
Where Name is not null
ORDER BY Salary Desc
Limit 2,5

Note: Limit must be placed at the end of all statements

Usage scenario: A lot of paging is used when developing websites and developing mobile programs

XV, GROUP by

1, data grouping is used to divide the data into logical groups so that each group can be aggregated.
The GROUP BY clause is used in SQL statements to group by, using the Group by Group field.
Grouping is generally used with aggregate functions, and the GROUP BY clause is responsible for dividing the data into logical groups, while the aggregation function calculates each group statistically

2. Check the age of the company's employees:
Select age from T_employees GROUP by age

3, put the same age data row into a group, the data after grouping can be considered as a temporary result set, and the Select Age statement takes out the value of the age field for each group.
So we get the Employee age chart for the table.

4, if the SELECT statement has a WHERE clause, the GROUP BY clause must be placed after the where statement

The 5,group by clause divides the results of the search into groups, and each group is a subset of all records.

16. Group BY and aggregation functions

1, after grouping, the data in the group can be counted by aggregation function;

Calculate the average salary for employees in each group:
Select Age,avg (Salary) from T_employees
GROUP BY age

See the number of employees for each age group:
Select Age,count (*) from T_employees
GROUP BY age

17. Join
1, really. In a business system, this connection exists between tables, and there are few tables that do not have an association with other tables.
In the realization of business functions, it is often necessary to retrieve data from multiple tables, while the most common technique for multi-table retrieval is table connection

2, if there is no table connection, then query each order's name of the customer must first inquire about the order, then to query the Customer table, trouble and inefficient

The JOIN keyword is used in 3,sql for table joins.
Table joins are available in a number of different types, with cross joins, Inner joins (INNER joins), and outer joins (Outter joins) supported by the mainstream database system

The outer joins are divided into: Left JOIN, right join.

18, FOREIGN KEY constraints

1, if the deletion/update T_customers row of records, then it may cause the existence of CustomerID in T_orders data is illegal, making the program logic error.
Generally does not update the value of the primary key ID, so when talking about foreign key constraints only talk about "delete T_customers"

2, FOREIGN KEY constraint: When deleting a piece of data in T_customer, how to handle t_orders and other rows that point to the t_customers foreign key. FOREIGN KEY constraint establishes a table on the ***id field

3, the method of constructing foreign KEY constraint: "foreign key" → "add foreign key" when new or modified table.
Name: automatic name; field name: CustomerId; reference table: t_customers; outer field name: Id;
On Delete, update: General default restrict (CASCADE: Delete the t_customers line when the order is deleted;
Set NULL: When deleting a t_customers line, set its order CustomerID to Null;no action/restrict: Reject delete).

19. Expand Learning Materials
1,DDL:

2, stored procedures, triggers, constraints, subqueries, handles other joins outside the left join

Like Peng Web Study notes (v) MySQL Foundation