database
The database is similar to a folder with many excel tables, but the database is a software specially used to manage data, so the efficiency is much higher than that of Excel or text storage
- Database management system consists of a collection of interrelated data and a set of programs to access these data. This data set is often called a database
- classification
- Relational database: Based on the relational model, relational database uses a series of tables to express data and the relationship between these data. MySQL is a relational database. Relational model has become the main data type today. It simplifies the work of programmers than the previous network model and hierarchical model. Now the popular NoSQL database generally refers to non relational database
- Non relational databases: independent lists
MySQL database
Linux system is used
1. MySQL profile
The mysql configuration file level directory installed by Ubuntu through apt is / etc / mysql / mysql conf.d
Above Windowns is my. In the installation directory Ini. If you need to change some MySQL settings
2. View MySQL services
On Linux, through: service mysql status/start stop
You can view it through the task manager under Windowns
3. Log in to MySQL
- Local connection:
mysql -u username - p
Input password
- Remote connection:
mysql -hIP address - p port - u user - p
Input password
4. View database
MySQL statement specification
- Keywords and function names are all capitalized
- The database name, table name and field name are all lowercase and enclosed in backquotes
- SQL statements must end with a semicolon
Check which databases are available: SHOW DATABASES;
Enter a database: USE mysql;
Judge whether it is in which database: SELECT DATABASE();
View current user: SELECT user();
5. Create database
CREATE{DATABASE|SCHEMA}[IF NOT EXISTS] `name`;
The part with braces is to choose one more
The part in brackets is optional
In MySQL, the database name is enclosed in backquotes
DATABASE in MySQL DATABASE is equivalent to SCHEMA, which may be different in other databases
6. Delete database
DROP {DATABASE|SCHEMA}[IF EXISTS] `name`;
The part with braces is to choose one more
The part in brackets is optional
7. Table operation
increase
-
Create table
CREATE TABLE [IF NOT EXISTS] table_name( column_name data_type );
column_name is the field name, that is, the column name
data_type is the field type. The common field types are integer (INT) and string (varchar)
The part in brackets is optional
-
Add field
ALTER TABLE table_name ADD ( column_name data_type );
Delete
-
Delete table
DROP TABLE table_name;
change
-
Modify table
- Delete field
ALTER TABLE table_name DROP ( column_name );
- Modify field type
ALTER TABLE table_name MODIFY column_name data_type;
- Modify field type and name
ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;
- Modify table name
ALTER TABLE table_name RENAME new_table_name;
check
- View which tables are in the database
SHOW TABLES[FROM `Library name`];
-
See what information is in the table
- View table creation information
SHOW CREATE TABLE `name`;
- View table field information
DESC table_name; SHOW COLUMNS FROM table_name;
constraint
Non NULL constraint
Whether a value of a database field can be NULL, NULL field value can be NULL, and NOT NULL field value cannot be NULL. When the field is set to non empty, the value must be inserted to insert the value, otherwise an error will be reported
Add a non empty constraint
ALTER TABLE `table_name` MODIFY column_name type NOT NULL;
NOT NULL can be added at the end for non null constraints
Delete non empty constraints
ALTER TABLE `table_name` MODIFY column_name type NULL;
Unique constraint
After a unique constraint is added to a field, the value of the field does not duplicate, that is, the value of the field is the only unique key in the table
Add unique constraint
ALTER TABLE `table_name` ADD CONSTRAINT `index_name` UNIQUE(column_name);
index_name is an alias for easy deletion
Delete unique constraint
ALTER TABLE `table_name` DROP INDEX `index_name`;
Primary key constraint
The primary key ensures the uniqueness of records. The primary key is saved as NOT NULL. Each data table can only have one primary key (non empty and unique)
Add primary key constraint
ALTER TABLE `table_name` ADD PRIMARY KEY(column_name);
Delete primary key constraint
ALTER TABLE `table_name` DROP PRIMARY KEY;
Self increasing constraint
AUTO_INCREMENT is numbered automatically and must be used in combination with the primary key. By default, the starting value is 1 and each increment is 1
Add self increasing constraint
ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT NOT NULL AUTO_INCREMENT;
Delete auto increment constraint
ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT NOT NULL;
Default constraint
The setting of the initial value of default constraint. When inserting a record, if there is no explicit assignment for the field, the default value will be automatically assigned
DEFAULT TABLE `table_name` ALTER `cloumn_name`{SET DEFAULT `default_name`|DROP DEFAULT}
Foreign key constraint
Constraint object
The constraints mentioned earlier are intra table constraints themselves, while foreign key constraints constrain other tables
Create condition
The storage engine of the data table can only be InnoDB. The data types of the foreign key column and the reference column are the same. The foreign key must be associated with the key. The foreign key name must be unique in the database
Create foreign key constraints
ALTER TABLE `table_name` ADD CONSTRANT `index_name` FOREIGN KEY (`column_name`) RRENCES `Foreign key associated table`(`column_name`);
Set when creating a table, as long as the content after ADD
Delete foreign key constraint
ALTER TABLE `table_name` DROP FOREIGN KEY `index_name`;
Combined use cases
CREATE TABLE stu ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, sex VARCHAR(10) DAFAULT 'nan', phone VARCHAR(11) UNIQUE, d_id INT, CONSTRAINT fk_id FOREIGN KEY (d_id) REFERENCES department(d_id) )AUTO_INCREMENT=100;
Table relation
One to one relationship refers to the relationship between one data of one entity and one data of another entity
One to many relationship, one to many and many to one is a concept, which means that one data of one entity is associated with multiple data of another entity
In the many to many relationship, the data of one entity corresponds to multiple data of another entity, and the data of another entity also corresponds to multiple data of the current entity
Three data paradigms
The role of the paradigm is to avoid data redundancy and insert / delete / update exceptions as much as possible
-
The first normal form (1NF): each attribute in the relationship conforming to 1NF is not separable. 1NF is the most basic requirement of all relational databases
Simply put, multiple headers cannot appear -
Second normal form (2NF): Based on 1NF, 2NF eliminates the partial functional dependence of non primary attributes on codes
In short, only one object (primary key) can be described, and other column names (secondary keys) are completely dependent on the object -
The third normal form (3NF): Based on 2NF, 3NF eliminates the transfer function dependence of non primary attributes on codes
In short, all non primary attributes only appear once in the whole database. There can be no dependency between secondary keys
Database transaction
A sequence of database operations that accesses and may operate various data items. These operations are either all performed or not performed. It is an inseparable work unit
In short: several operations are bound together, either all or none
view
The three paradigms make the table query complex. For common data queries, it is very inconvenient to write complex query statements repeatedly. Therefore, a virtual table (without data) can be created. The data of this virtual table comes from other tables in the database, and the data source of the virtual table is given at the time of definition
Create view
CREATE VIEW `view_name`[(column_name_list)] AS select_statement;
Delete view
DROP VIEW `view_name`;
8. Table data operation
increase
INSERT INTO table_name [(column_name,···)]{VALUES|VALUE}(···)
When the inserted field name is not specified, insert all fields
() the inside shall be separated by
If VALUES is inserted (··), (···)
Delete
DELETE FROM table_name [WHERE condition];
If no condition is added, all contents of the table will be deleted
where can be used as is null or is not null
< > it is not equal to
change
- Update data
UPDATA table_name SET column_detail_name=value [WHERE condition];
If there is no WHERE, modify all data
check
Single table query
SELECT {*|column_name} FROM table_name [WHERE Conditions to add];
It can be matched using and and or
Alias
SELECT column_name AS new_column_name FROM table_name AS new_table_name;
The function is to prevent conflicts of the same name between tables
Where AS can be replaced by spaces
You can use table_name.column_name to query table contents
multi-table query
- Inner connection
Cartesian coordinate system
Imagine the two tables as each point on the x-axis and y-axis, which is the set of rows in the two tables
SELECT * FROM `table_name1` INNER JOIN `table_name2`; SELECT * FROM `table_name1`, `table_name2`; SELECT * FROM `table_name1`CROSS JOIN `table_name2`; SELECT * FROM `table_name1` JOIN `table_name2`;
The results of the above four SQL are the same, which are Cartesian products, also known as unconditional connection / cross connection, etc
The number of columns is the addition of two table columns, and the number of rows is the product of two table rows
Add condition query
SELECT * FROM `table_name1` INNER JOIN `table_name2` ON condition; SELECT * FROM `table_name1`, `table_name2` WHERE condition; SELECT * FROM `table_name1`CROSS JOIN `table_name2`; SELECT * FROM `table_name1` JOIN `table_name2`;
Multi table connection
SELECT * FROM `table_name1` INNER JOIN `table_name2` ON condition INNER JOIN `table_name3` ON condition; SELECT * FROM `table_name1`, `table_name2`, `table_name3` WHERE condition;
- External connection
Right connection (left connection is similar to right connection, but the effect is opposite)
SELECT * FROM `table_name1` RIGHT JOIN `table_name2`ON condition;
Left connection
SELECT * FROM `table_name1` LEFT JOIN `table_name2`ON condition;
It mainly retains the data connected on the left, deletes more data on the right, and fills in less data with null
Full connection
UNION is used to merge the result sets of two or more SELECT statements and eliminate any duplicate rows in the table
SELECT s.`name`,c.`name` FROM `students` s LEFT JOIN `select` se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id` UNION SELECT s.`name`,c.`name` FROM `students` s LEFT JOIN `select` se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`;
Keep two tables and fill with null if none
Sub table query
Subqueries can be placed not only after JOIN, but also after WHERE
SELECT * FROM `students` s WHERE s.`dept_id` = (SELECT `id` FROM `department` d WHERE d.`name` = 'College of foreign languages');
sort
ORDER BY sorts the query results, ASC ascending (default) DESC descending
SELECT * FROM `table1_name` AS s JOIN `table2_name` sd ON s.`id`=sd.`stu_id`ORDER BY s.`id` DESC;
Limit the number of rows displayed
LIMIT limits the number of rows displayed for the query results
SELECT * FROM `table1_name` AS s JOIN `table2_name` sd ON s.`id`=sd.`stu_id`ORDER BY s.`id` DESC LIMIT 2,3;
Where 2,3 indicates that 2 to 3 lines are displayed
Grouping query
Grouping is a common operation, which is often used for grouping statistics. After using GROUP BY, groups will be grouped according to the fields after GROUP BY, and must be clear fields, not *, so it cannot be * after SELECT. Secondly, you can use HAVING to filter the results after grouping. Note: the fields after HAVING must appear after SELECT
How many statistics are there
SELECT COUNT(*) FROM `table1_name`;
Number of occurrences of Statistics
SELECT `column_name`,count(*) FROM `table_name` GROUP BY `column_name` HAVING Selection conditions;
MySQL built-in functions
- Field processing
SELECT s.`name`,IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`; # Handle NULL SELECT DISTINCT `name` FROM `students`; # Field de duplication
- String interception
LEFT is to intercept from the LEFT, RIGHT is to intercept from the RIGHT, and SUBSTRING can specify the interception range
SELECT LEFT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`; SELECT RIGHT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`; SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
- String splicing
SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
- Type conversion
CAST CONVERT
SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`; SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;
- Time function
SELECT DAY('2017-08-18')-DAY('2017-08-01'); SELECT NOW();
There are many MySQL functions, many of which are the same as those we usually use. Aggregate functions such as ABS/MAX/MIN/ROUND/AVG/SUM are also used in the same way. If you need to use them, please query by yourself
SELECT MAX(sd_age) FROM `table_name`; SELECT sd_age,MAX(sd_age) FROM `table_name` GROUP BY sd_age;
MySQL execution order
-
FORM: calculate the Cartesian product of the left table and the right table of FROM. Generate virtual table VT1
-
ON: ON filter virtual table VT1. Only those rows that meet < join condition > will be recorded in virtual table VT2
-
Join: if OUTER JOIN (such as left join and right join) is specified, unmatched rows in the reserved table will be added to virtual table VT2 as external rows to generate virtual table VT3. If the rug from clause contains more than two tables, steps 1-3 will be repeated for the result VT3 generated by the previous join connection and the next table, Until all tables are processed
-
WHERE: perform WHERE condition filtering on virtual table VT3. Only records conforming to < WHERE condition > will be inserted into virtual table VT4
-
group by: group the records in VT4 according to the columns in the group by clause to generate VT5
-
CUBE |ROLLUP: perform cube or rollup operations on table VT5 to generate table VT6
-
Having: apply having filter to virtual table VT6. Only records conforming to < having condition > will be inserted into virtual table VT7
-
Select: execute the select operation, select the specified column and insert it into the virtual table VT8
-
DISTINCT: de duplicate the records in VT8. Generate virtual table VT9
-
ORDER BY: record in virtual table VT9 according to < order_ by_ List > sort to generate virtual table VT10
-
LIMIT: fetch the record of the specified row, generate the virtual table VT11, and return the result
proposal
-
Try to avoid whole table scanning, such as SELECT*
-
Establish appropriate index
-
Use the appropriate storage engine
-
In the JOIN, try to use the small table LEFT JOIN large table
-
Unless necessary, try not to use ORDER BY,GROUP BY and distinct, and try to use index instead