Section 1 overview of database management system
You can see the ranking of databases in www.db-engines.com/en/ website.
The database is divided into:
Relational database:
mysql——mariaDB
oracle
Non relational database
nosql
Data management system: a more efficient data management system on disk storage mechanism.
For relational databases: Store data in tables, each table may have a correlation
Its core components: libraries, tables, indexes, views, sql, storage engines, storage functions, triggers, event schedulers
Constraints:
The primary key constraint is unique, not empty, and only one table can have one.
The only constraint is the only existence, not empty, but there are multiple.
Outsourcing: reference to constraints from another table.
Check constraint: check
mysqlOfficial website: www.mysql.com mariadb.org
mysqlRelated tools:
mysql utilities Management tools provide a set of command line tools for maintaining and managing MySQL servers.
mysql workbench Performance evaluation
mysql connectors Connector
mysqlThere are 3 ways of installation.
1 Source package installation
2 The binary package is installed and can be used directly after decompression.
3rpmPackage installation
2 Using binary to install
Uninstall the original mariadb-server MariaDB
Confirm the existence of MySQL users
Decompression MySQL compression package to /usr/local/ decompression can be directly used without installation.
Make a soft connection.
ln -sn mysql-5.7.18-linux-glibc2.5-x86_64/ mysql
Change the MySQL directory to root:mysql
Create a directory to store data changes.
Edit environment variable:
vim /etc/profile.d/mysql.sh
export PATH= /usr/lcoal/mysql/bin:$PATH
. /etc/profile.d/mysql.sh
Do MySQL initialization:
mysql –verbose –help See MySQL’s detailed help.
# my_print_defaults ；Displays the configuration file path that is read by default.
Create a configuration file: /usr/local/mysql/etc/my.cnf
1 Create directory: MKDIR -pv /usr/local/mysql/etc
2 Copy /etc/my.cnf to the directory and modify it.
vim my.cnf
Change the above pid-file to
Copy command path /etc/ini.d/mysqld so that you can start with the service command.
You need to create error.log and mysql.log. This file belongs to MySQL. Otherwise, the report will be wrong.
Startup service:
# service mysqld start
mysqlClient program: interactive mode and command mode two kinds.
mysql Command MySQL -uroot -hlocalhost -p
Common options:
–host=host_name, -h host_name：Server address;
-user=user_name, -u user_name：User name;
–password[=password], -p[password]：User password;
–port=port_num, -P port_num：Server port;
–protocol={TCP|SOCKET|PIPE|MEMORY} Agreement
Local communication: requests based on local loopback addresses will be based on local communication protocols; logins based on socket files
Non local communication: request by using non local loopback address; TCP protocol;
socket=path, -S path
–database=db_name, -D db_name： Set default library
–compress, -C：Data compression and transmission
–execute=statement, -e statement：Execute SQL statement in non interactive mode.
–vertical, -E：The query results are displayed longitudinally.
After login to MySQL interactive interface
You can enter help to view help.
View parameters:
There are two kinds of parameters, one is global GLOBAL, one is session session.
The session is temporary, effective only for the current session and effective immediately.
GLOBAL It is valid for the new session to be modified, requiring users to have administrative privileges.
mysql> show GLOBAL VARIABLES Display parameters
Modify with set (some can be changed, some can not be changed).
SER [SESSION|GLOBAL] system_var_name = expr Use like or where clause
mysqlState variables
show [GLOBAL|SESSION] status [LIKE | WHERE]
statusThe value can be zero.
mySQLData type: HELP DATA TYPE view data type
Character:
CHAR(#)， BINARY(#)：Fixed length; CHAR does not distinguish character case, but BINARY distinguishes.
VARCHAR(#)， VARBINARY(#)：Elongated type
TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
Numerical model:
Floating point: approximation
FLOAT
DOUBLE
REAL
BIT
Integer: precision
Date time type:
Date: DATE
Time: TIME
Date J time: DATETIME
Timestamp: TIMESTAMP
Year: YEAR (2), YEAR (4)
Internal construction type
ENUM：enumeration
ENUM(‘Sun’,’Mon’,’Tue’,’Wed’)
SET：aggregate
Type modifier:
Character types: NOT NULL, NULL, DEFALUT’STRING’, CHARACET SET’CHARSET’, COLLATION’collocation’.
SHOW CHARACTER SET; View character set
Integer: NOT NULL, NULL, DEFALUT value, AUTO_INCREMENT, UNSIGNED
Date time type: NOT NULL, NULL, DEFAULT
SQL MODE：Define mysqld’s setting of response behaviors such as constraint violation.
SHOW VARIABLES LIKE ‘sql_mode’ View the SQL model
2 ways of modification
mysql> SET GLOBAL sql_mode=’MODE’;
mysql> SET @@global.sql_mode=’MODE’;
basic operation
Database:
Create, change, delete
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name COLLATE [=] collation_name
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
Table creation, modification, deletion
(1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
View all supported storage engines:
mysql> SHOW ENGINES;
View the storage engine of the specified table:
mysql> SHOW TABLE STATUS LIKE clause;
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] delete
ALTER TABLE tbl_name[alter_specification [, alter_specification] …] modify
Contents can be modified:
(1) table_options
(2) Add definition: ADD (field, field set, index, constraint)
(3) Modify fields:
(4) Delete operation: DROP
View table structure definition: DESC tbl_name;
View table definition: SHOW CREATE TABLE tbl_name;
View table attribute information: SHOW TABLE STATUS [{FROM IN} db_name] [LIKE’pattern’WHERE expr]
Index: Operation creation, modification and deletion
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,…)
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
DROP INDEX index_name ON tbl_name
Index type:
1 Clustered index and non clustered index: whether index exists with data;
2 Primary key index and auxiliary index
3 Dense index and sparse index: whether each item is indexed;
View:
Virtual table: stored SELECT statement;
Establish:
CREATE VIEW view_name [(column_list)] AS select_statement；
Modification:
ALTER VIEW view_name [(column_list)] AS select_statement；
Delete:
DROP VIEW [IF EXISTS] view_name [, view_name] … ；
Query execution path:
Request – & gt; Query cache – & gt; Parser – & gt; Preprocessor – & gt; Optimizer – & gt; Query execution engine – & gt; Storage engine – & gt; Cache – & gt; Response
SELECTThe execution flow of the statement:
FROM –> WHERE –> Group By –> Having –> Order BY –> SELECT –> Limit
DISTINCT：Data removal;
SQL_CACHE：Explicitly specifies the result of the cache query statement;
SQL_NO_CACHE：Explicitly specifies the result of not caching the query;
query_cache_typeThe server variable has three values:
ON：Enable;
SQL_NO_CACHE：No caching; cache is cached by default.
OFF：Close;
DEMAND：Cache on demand;
SQL_CACHE：Cache; default no caching.
Fields can use aliases:
col1 AS alias1, col2 AS alias2, …
WHEREClause: Specifies the filtering conditions to achieve the “selection” function.
Filter condition: Boolean expression;
[WHERE where_condition]
Arithmetic operators: +, -, *, /%
Comparison operator: =, < >; = =; < =>; > > = = < < = =
IS NULL， IS NOT NULL
Interval: BETWEEN min AND Max
IN：List;
LIKE：Fuzzy comparison,% and sum;
RLIKEOr REGEXP
Logical operators:
AND， OR， NOT
GROUP BY：The query results are grouped according to the specified fields to be used for “aggregate” operations.
avg(), max(), min(), sum(), count()
HAVING：The result of group aggregation is filtered.
ORDER BY：Sort the results of the query according to the specified fields.
Ascending order: ASC
Descending order: DESC
LIMIT：Quantitative restrictions on output results
Three, multi table query and sub query
Multi table query:
Connection operation:
Cross connection: Cartesian product;
Internal connection:
Equivalents: let the fields between tables be set up in an equal way.
Unequal value connection:
Natural connection
Self connection: connect yourself to yourself.
External connection:
Left external connection:
FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col
Right outside connection:
FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col
Example:
Download a SQL script hellodb.sql
]# mysql -uroot -hlocalhost < hellodb.sql
Multi table equivalent query
Self connection:
Subquery: nested query in query;
SELECT * FROM (SELECT * FROM students where age > 20) AS S WHERE S.Gender=’M’;
For subqueries in the WHERE clause;
(1) For subqueries in comparison expressions: subqueries can only return a single value;
(2) For subqueries in IN: subqueries can return a list value;
SELECT * FROM STUDENTS WHERE ClassID IN (SELECT DISTINCT ClassID FROM students WHERE Gender = ‘F’) AND Gender=‘M’；
(3) Subqueries for EXISTS
For subqueries in the FROM clause;
SELECT tb_alias.col1, … FROM (SELECT clause) AS tb_alias WHERE clause;
Joint query: merges the execution results of multiple query statements; UNION
SELECT clause UNION SELECT cluase；
Practice:
(1) In the students table, the names and ages of students who are over 25 years old and who are male are inquired.
SELECT Name,Age FROM students WHERE Age > 25 AND Gender=’M’;
(2) Based on ClassID, the average age of each group was displayed.
SELECT ClassID,avg(age) FROM students GROUP BY ClassID;
(3) The average age and age of the second subjects in the 30 subjects were shown.
SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30;
(4) Displays the information of the student who begins with the name of L;
SELECT * FROM students WHERE Name LIKE ‘L%’;
(5) Shows the relevant information of TeacherID students who are not empty.
SELECT * FROM students WHERE TeacherID IS NOT NULL;
(6) After sorting by age, the information of the oldest 10 students is displayed.
SELECT * FROM students ORDER BY Age DESC LIMIT 10;
Importing hellodb.sql, the following operations are performed on the students table.
1、Group ClassID to show the number of students in each class.
SELECT ClassID,count(StuID) FROM students GROUP BY ClassID;
2、The age of Gender is shown by grouping.
SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
3、The class with ClassID average age greater than 25 was grouped.
SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(Age) > 25;