Rupeng net Study Notes (5) MySql basics and Study Notes mysql

Rupeng net Study Notes (5) MySql basics and Study Notes mysql

MySQL Basics

I. Database concepts
1. The equipment information, forum post information, QQ friend relationship information, and student information in the student status management system must be stored permanently in one place,
If I/O is used to write data to a file, it will be very troublesome, and it is not conducive to sharing data by many people.

2. We need to use a large number of library resources to develop most of the software and websites, and even to develop games and mobile apps. During the company's interview, the database is assessed at more than 1/3.

3. A DataBase is a warehouse that stores data, allowing you to easily put the data in and retrieve the data as needed.
Database Management SYSTEM (DBMS) is a software used to manage databases (such as adding, deleting, modifying, and querying databases,
Common DBMS types include MYSQL, Oracle, DB2, and MSSQLServer.
MYSQL is open-source and free, so it is the most widely used

4. The usage of different DBMS is similar. If you master one, you can basically master other DBMS.

2. Databases, tables, and columns

1. Table: the "shelf grid" for storing data"

2. Two names: column/Field)

Iii. Install MYSQL

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

2. Add system services
Run the command line as an administrator, cd to the bin folder of mysql, and execute "mysqlld-install"

Uninstall the service: mysql-remove


Iv. MYSQL management tools

1. MYSQL management tools are used to create databases and tables for DBMS management. Required by developers. Management tools are not DBMS, and DBMS is indispensable

2. There are many management tools: Navicat, Workbench (official), phpMyAdmin, SQLyog, and MySQL-Front. The usage is similar.
NaviCat lite is a free version. It is sufficient for functional learning.

3. After installation, go to "PremiumSoft" in the Start Menu.
Choose "file"> "New Connection"> "MYSQL" and "connection name" from the main menu. Set "host name or IP address" to the IP address or host name of MYSQLServer,

Enter 127.0.0.1 (local loopback address) in the local machine, and the username and password are all root (the complexity of changing the password in the production environment ).

4. If no error is reported during connection, MYSQLServer and management tools are installed normally.

5. Primary Key)

1. The primary key is the unique identifier of the Data row. Columns that are not repeated can be used as primary keys. A table can have no primary key, but it is very difficult to process,
Therefore, primary keys must be set for tables with no special reason.

2. There are two primary key selection policies:
Business primary key and logical primary key

A business primary key is a primary key that uses fields with business significance, such as ID card numbers and bank accounts.

The logical primary key uses fields that do not have the business significance of the task as the primary key. It is completely for the program, and the business personnel will not read the data.

It is difficult to ensure that the business primary key will not be repeated (the ID card number is repeated) and will not change (the account is upgraded). Therefore, we recommend that you use the logical primary key.


6. Table Association and foreign key (ForeignKey)

1. One field in one table points to the primary key of another table, so that the information of the two tables is connected together.

2. Multiple tables can be connected in series, or multiple to multiple tables can have a relational table between two tables.


7. create databases and tables

1, right-click the root node, new database, database name to make sense, such as "study1", character set is recommended to use UTF-8.

2. Right-click "create table" under the "table" node under study1, and "column" actually refers to columns.

Id (primary key, name of the potential rule, int, cannot be blank, right-click "primary key ")
Name (nvarchar, with a length of 10, cannot be blank)
Gender (bit, cannot be blank)
Save as "T_Persons"

3. Common table creation errors:

Do not use any possible keywords for the column name or table name. Do not have spaces (including front and back) or special characters.

4. There are many MYSQL storage engines. The most common ones are InnoDB and MyISAM. MyISAM is highly efficient, but does not support transactions, foreign key constraints, and other features,
Therefore, InnoDB is recommended. The new version is also InnoDB by default.

How to Set it: "option"> "engine" during table creation ". After the engine is created, right-click the table and choose "Object Information ".

5. Common Data Types
Text:
CHAR (*): a fixed-length string of up to 255 bytes. Its length must be specified at creation.
VARCHAR (*): a variable-length string of up to 255 bytes. Its length must be specified at creation.
TEXT: variable-length TEXT with a maximum length of 64 k characters
TINYTEXT: variable-length text up to 255 characters
MEDUIMTEXT: variable-length text with a maximum length of 16 k characters
LONGTEXT: variable-length text with a maximum length of 4 GB

 

 

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

 

 

Decimal point: (length and decimal point must be specified, that is, the display width and number of decimal places ):
Decimal: Precisely stores decimals. It is internally stored using strings. It is suitable for exact types such as amount. Alias: NUMERIC
Float: 4 bytes, single precision. Approximate storage (*), which is more efficient than decimal.
Double: 8 bytes, double precision. Approximate storage (*), which is more efficient than decimal.

Date and Time:
DATE: 4 bytes. Range: 1000-01-01--9999-12-31
TIME: 3 bytes. Range:-838: 59: 59--838: 59: 59
DATETIME: 8 bytes. Value Range: 1000-01-01 00:00:00 -- 9999-12-31 23:59:59

Binary big data:
TITYBLOB: the maximum length is 255 bytes.
BLOB: the maximum length is 64 KB.
MEDIUMBLOB: the maximum length is 16 MB.
LONGBLOB: the maximum length is 4 GB.

8. SQL statements

1. You cannot use the mouse to manage data manually. You must use the language to communicate with the database automatically. The SQL statement is a special statement used to "talk" with the database.

2. Strings in SQL statements are generally enclosed in single quotes.

3. SQL statements are case-insensitive.

4. In NavCat, find "query"-"New query" where the SQL statement is executed, compile the SQL statement, and click "run" to execute the SQL statement.

5. Simplest SQL:

View All data in a table: select * from T_Persons

6. Insert
Simple SQL statement for inserting data:
Insert into T_Persons (Id, Name, Age, Gender) values (5, 'Jim ', 20, 1)

The Insert statement can omit the column name after the table name, but it is strongly not recommended.

If the values of some fields in the inserted rows are uncertain, do not specify those columns during the Insert operation. The column "cannot be blank" cannot be omitted during insertion.

Auto Increment: Field Auto Increment can avoid concurrency and other problems. do not control Auto Increment by the programmer code. You do not need to specify a value when using an auto-increment field in Insert.

7. How to modify the table structure: click "design table"

1. To change the field "allow blank" to "do not allow blank", you must first set the default value of the old data (using the Update Statement ),
If the previous test data does not matter, you can delete all the old data (add the Height field)

2. to modify the column data type, make sure that the old data is compatible with the new data type. This is also true when you modify the Data Length.

8. Data Update

Update a column:
Update T_Persons Set Age = 30

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 data:
UPDATE T_Persons Set Age = 30 where Name = 'Tom '. Use the where statement to UPDATE only the rows whose Name is 'Tom,
Note that SQL is more suitable than single =, rather than =.

In the Where clause, you can also use complicated logic to determine the UPDATE T_Persons Set Age = 30 where Name = 'Tom 'or Age <25, or is equivalent to Java |

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

Other logical operators that can be used in Where: or, and, not, <,>,> =, <= ,! = (Or <>)

10. data deletion
Delete all data in the table:
DELETE from T_Persons

Delete part of table data
Delete from T_Persons where Age> 20

DELETE only deletes data, and the table is still,

11. delete a table
Drop table T_Persons

IX. Data Retrieval
1. Simple data retrieval: Select * from T_Persons

2. Retrieve only required columns:
SELECT Number FROM T_Employees,
SELECT Name, Age FROM T_Employees

3. Column alias
SELECT Number AS Number, Name AS Name, Age AS Age111 FROM T_Employees
Write or not write.

4. Calculate Columns
SELECT Number, Name, Age Age111, Age + 10 years later, 1 + 1, now () FROM T_Employees

5. Use where to retrieve data with compound conditions:
SELECT Name FROM T_Employees WHERE Salary <5000

6. You can also retrieve data not associated with any table:
Select 1 + 1;
Select now ();

10. Data Summary
1. SQL Aggregate functions:
MAX
MIN
AVG
SUM
COUNT
2. The highest salary for employees over 25 years old:

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 over 25 years old:

Select count (*) FROM T_Employees WHERE Age> 25

5. Total and average salaries of all employees:

Select sum (Salary), AVG (Salary) FROM T_Employees

11. Data Sorting

1. The Order by clause is located at the end of the Select statement. It allows execution to sort by one or more columns. You can also specify that the sorting method is ascending (ASC) or descending order (DESC)

2. Sort the information of all employees in ascending order of age:
SELECT * FROM T_Employees order by Age ASC

3. sort by age from large to small. If the age is the same, sort by salary from large to small:
SELECT * FROM T_Employees order by Age DESC, Salary DESC

4. The order by clause must be placed after the WHERE clause:
SELECT * FROM T_Employees WHERE Age> 23 order by Age DESC, Salary DESC

12. wildcard Filter

1. Use like for wildcard Filtering

2. The wildcard matching a single character is a half-width underline "_", which matches a single occurrence character.
Start with any character, and the rest is "erry ":
Select * from T_Employees where Name like '_ erry'

3. The wildcard for multi-character matching is "%", which matches any character that occurs at any number of times (0 or multiple.
K % matches strings starting with k and of any length
Search for employee information whose name contains letter n:
Select * from T_Employees where name like '% n %'

4. The like performance is poor, which can easily cause full table scanning. Use it with caution.
Database optimization (index, etc.) will be introduced later, and full-text index will be used for search in the project

XIII. null value processing

1. If no value is specified for a column in the database, the value is null. null in the database indicates "unknown", rather than "no.
Therefore, the result of select null + 1 is null, because the result of adding 1 is unknown"

2, Select * from T_Employees where name = null;
Select * from T_Employees where name! = Null;
There are no returned results, because the Database "does not know"
Select name + "a" from T_Employees

3. SQL uses is null and is not null to judge null values.
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 returned result set. limit is placed at the last position of the select statement,
Syntax: limit first line number, maximum number of result sets to be returned

For example, the following SQL statement will return a maximum of five records starting from the second row (the row number starts from 0) in descending order of wages with the Name not empty.
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.

Use Cases: many pages are used when developing websites and mobile apps.

15. group

1. Data groups are used to divide data into multiple logical groups, so that each group can be aggregated.
In SQL statements, the group by clause is used as the group by field ".
Groups are generally used together with collection functions. The group by clause is used to divide data into logical groups, while aggregate functions perform statistical calculation on each group.

2. Check the age groups of employees in the company:
Select Age from T_Employees group by Age

3. Place data rows with the same Age into a group. The grouped data can be considered as a temporary result set, while the SELECT Age statement extracts the value of the Age field in each group,
In this way, we can get the staff age table in the table above.

4. If the SELECT statement has a WHERE clause, the group by clause must be placed after the WHERE clause

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

16. group by and Aggregate functions

1. After grouping, you can use aggregate functions to collect statistics on the data in the group;

Calculate the average salary of employees in each group:
Select Age, avg (salary) from T_Employees
Group by Age

View the number of employees in each age group:
Select Age, count (*) from T_Employees
Group by Age

17. join
1. In a real business system, this association exists between tables, and few tables are not associated with other tables,
When implementing business functions, you often need to retrieve data from multiple tables. The most common technology for multi-Table search is table join.

2. If there is no table connection, It is troublesome and inefficient to query the customer name of each order first, and then query the customer table.

3. Use the JOIN keyword in SQL for table JOIN.
Table JOIN has many different types, including cross join, inner join, and outer JOIN supported by mainstream database systems)

Outer join is divided into left join and right join.

18. Foreign key constraints

1. if you delete/update a row of t_mers MERs records, there may be data with invalid CustomerId in T_Orders, making the program logic wrong.
Generally, the value of the primary key Id is not updated. Therefore, when talking about foreign key constraints, we only talk about "deleting t_mers MERs"

2. Foreign key constraint: how to deal with T_Orders and other rows that point to the t_mers MERs foreign key when deleting a data record in T_Customer. The foreign key constraint is used to create a table with the foreign key field *** Id.

3. Foreign key constraint creation method: "foreign key"> "add foreign key" when creating or modifying a table ".
Name: automatically named; column name: CustomerId; reference table: t_customers; field name: Id;
At the time of deletion and update: Generally, the default RESTRICT (CASCADE: delete T_Customers line also deletes its order;
Set null: When a row of t_mers Mers is deleted, SET its order CustomerId to NULL; no action/RESTRICT: delete is denied ).

19. extended learning materials
1. DDL:

2. Stored Procedures, triggers, constraints, subqueries, and processing of joins other than left join