Summary of super detailed mysql knowledge points

Article Directory

Why use Mysql?

It is relatively used in Internet companies. The mysql database is free, compact and lightweight, and easy to install. Internet companies such as JD and Ali have a large amount of concurrency, and build multiple servers, and the cost of using free databases is low.

What is a database? What is a database management system? What is SQL? What is the relationship between them?

Database: Database, referred to as DB. A combination of some files stored in a certain format.

As the name implies: a warehouse that stores data is actually a bunch of files. These files store data in a specific format.

Database management system:

DataBaseManagement, referred to as DBMS. The database management system is specially used to manage the data in the database. The database management system can add, delete, modify and check the data in the database.

Common database management system:

Mysql, Oracle, MS sqlserver, DB2, sybase, etc...

SQL: Structured Query Language

Programmers need to learn SQL statements, by writing SQL statements, and then the DBMS is responsible for executing the SQL statements, and finally complete the operation of adding, deleting, modifying and checking data in the database.

SQL is a set of standards. This SQL statement can be used in any database management system.

The relationship between the three?

DBMS–Execute–>SQL–Operation–>DB

Check which databases are in mysql ?

mysql>show databases;

Insert picture description here

mysql comes with 4 databases by default.

How to choose to use a certain database ?

mysql>use test;

Create database

mysql>create school;

The most basic unit in the database is the table: table

View the tables in the database

mysql>show tables;

What is a table? Why use tables to store data?

Excel table data is a table, and the data stored in the database represents the data in the form of a table, because the table is more intuitive, and any table has rows and columns.

Row: called data/record.

Column: This is called a field.

Classification of sql statements

DQL: data query statement (all queries with the select keyword are query statements)

DML: Data Manipulation Language (all data in the table is added, deleted, modified and checked)

insert increase

delete

update

DDL: Data Definition Language (all with create, drop, alter)

DDL mainly operates on the structure of the table, not the data in the table

create: Increase

drop: delete

alter: modify

TCL: is the transaction control language

Including: transaction submission: commit;

​ Transaction rollback: rollback;

DCL: is a data control language.

For example: authorize grant, revoke permission revoke

View the data in the table

mysql>select * from school;

Only view the structure of the table

mysql>desc school;

Note: mysql does not execute if it does not see the semicolon!

Simple query

1. Query a field?

select field name from table name;

Note that: select and from are keywords

​ Both the field name and the table name are identifiers

2. Query two or more fields?

Use commas to separate

Query all the first way: write each field

​ The second way: select * from table name

3. Give the query column an ​​alias

mysql>select deptno,dname as deptname from dept;

Use the as keyword to alias

Note: Only the column name of the displayed query result is displayed as deptname, the original column name is still called: dname

Remember: the select statement will never be modified. (Because it is only responsible for query)

Condition query

select field 1, field 2, field 3 from table name where conditions;

between…and… Between two values, equivalent to >= and <=

select empno,ename,sal from emp where sal between 2450 and 3000;

Note: When using between and, you must follow the principle of small left and large right

Is null is null

Is not null is not null

And and or appear at the same time, there is a priority issue?

For example: query employees whose salary is greater than 2500 and whose department number is 10 or 20?

select * from emp where sal>2500 and deptno=10 or deptno=20;

Analyze the problem of the above sentence?

and has a higher priority than or.

The above statement will execute and first, and then execute or

select * from emp where sal>2500 and (deptno=10 or deptno=20);

And and or appear at the same time, and has a higher priority. If you want or to be executed first, you need to add "parentheses"

in contains, equivalent to multiple or

The query job is an employee of manager and salesman?

select empno, ename, job from emp where job ='manager' or job='salesman';

select empno, ename, job from emp where job in('manager','salesman');

Note: in is not an interval. In is followed by the specific value

like is called fuzzy query

Support% or underscore matching

% Matches any number of characters

Underscore: any character

Find those with o in the name?

select ename from emp where ename like'%o%';

Find the name ending in T?

select ename from emp where ename like'%T';

Find the name that starts with K?

select ename from emp where ename like'K%';

Find out where the second letter is A?

select ename from emp where ename like'_A%';

Sort

Query the salary of all employees, sort

select ename,sal from emp order by sal;//The default is ascending order

select ename,sal from emp order by sal desc; //specify descending order

select ename,sal from emp order by sal asc;//specify ascending order

Sort by salary first, same salary, sort by name

select ename,sal from emp order by sal asc, ename asc;

The order of keywords cannot be changed:

select...

from...

where...

order by...

The execution order of the above statements:

from

where

select

order by

Data processing function

Data processing function is also called single line processing function

Features of single-line processing functions: one input corresponds to one output

The opposite of a single-line processing function is a multi-line processing function. (Features of multi-line processing function: multiple inputs and one output)

What are the common one-line processing functions?

lower to lowercase

select lower (ename) as ename from emp;

upper case

substr takes a substring (substr (string to be intercepted, starting subscript, length of interception))

select substr (ename, 1, 1) as ename from emp;

length takes the length

trim to remove spaces

Grouping function (multi-line processing function)

The characteristics of multi-line processing function: input multiple lines, and finally output one line.

5:

count count

sum

avg average

max

min minimum

Note: The grouping function must be grouped before it can be used.

​ If there is no grouping, the whole table is a set of data by default.

Find the highest wage

select max(sal) from emp;

Grouping function

The first point: the grouping function automatically ignores null, and there is no need to deal with null in advance.

The second point: What is the difference between count (*) and count (specific fields) in the grouping function?

count (specific field): Indicates the total number of non-null elements in the field

count(*): The total number of rows in the statistics table. (As long as there is a row of data, count is ++)

​ Because it is impossible for each row of records to be null, and one column of a row of data is not null, then the row of data is valid

The third point: the grouping function cannot be used directly in the where clause

Fourth point: All grouping functions can be combined and used together

Group query

select…from…group by…

Combine all the previous keywords together

select...

from...

where...

group by...

order by...

Order of execution:

1. From...

2. Where...

3. Group by...

4. Select...

5. order by...

Why can't the grouping function be used directly behind where?

Because the grouping function must be grouped before it can be used when it is used.

When the where is executed, there is no grouping yet, so the grouping function cannot appear after the where.

select sum (sal) from emp;

There is no grouping, why can the sum() function be used?

Because select is executed after group by.

Key conclusions:

​ In a select statement, if there is a group by statement, select can only be followed by the fields participating in the grouping, and the grouping function, and the others cannot be followed.

Use having to further filter the data after grouping. Having cannot be used alone, having cannot replace where, having must be used in conjunction with group by.

Single table query summary

select...

from...

where...

group by...

having...

order by...

Order of execution:

1, from

2, where

3. Group by

4. having

5, select

6, order by

Query data from a table, first filter out the valuable data through where conditions, group these valuable data, and then use having to continue filtering after grouping. The select query comes out, and finally the output is sorted.

Find out the average salary of each position. It is required to show that the average salary is greater than 1500. Except for the manager position, it is required to sort in descending order of the average salary.

select job, avg(sal) as avgsal

from emp

where job<>'manager'

group by job

having avg(sal)>1500

order by avgsal desc;

Remove duplicate records from query results

Note: The original table data will not be modified, but the duplicate records of the query result will be removed.

A keyword is needed to remove duplicates: distinct

Distinct can only appear at the forefront of all fields.

Connect query

1. What is a connection query?

A single query from a table is called a single-table query.

For example: the emp table and the dept table are combined to query data, and the employee name is taken from the emp table, and the department name is taken from the dept table. This kind of cross-table query, where multiple tables are combined to query data, is called join query.

2. What is the classification of the connection query?

Classified according to the way the tables are connected:

Internal connection:

​ Equivalent connection

​ Non-equivalent connection

​ Self-connection

Outer connection:

​ Left outer connection (left connection)

​ Right outer connection (right connection)

3. When two tables are connected for query, there is no restriction on any conditions. The final query result is the product of the number of the two tables. This phenomenon is called: Cartesian product phenomenon. (This is a mathematical phenomenon discovered by Descartes.)

Equivalent connection of inner connection

Case: Query the department name of each employee, and display the employee name and department name?

sql92 syntax:

select e.ename,d.dname

from emp e,dept d

where e.deptno=d.deptno;

Disadvantages of sql92: the structure is not clear, the connection conditions of the table and the conditions for further filtering in the later stage are all placed after where.

sql99 syntax:

select e.ename,d.dname

from emp e

(inner)join dept d //inner can be omitted

on e.deptno=d.deptno;

Advantages of sql99: The conditions for table connection are independent. After the connection, if further filtering is required, continue to add where in the future

Non-equivalent connection

Case: Find out the salary grade of each employee, and ask to display the employee's name, salary, and salary grade?

select e.ename,e.sal,s.grade

from emp e

join salgrade s

on e.sal between s.losal and s.hisal;//The condition is not an equal relationship, which is called non-equivalent connection.

Self-connection

Case: Query the superior leader of an employee, request to display the employee's name and the corresponding leader's name?

Technique: Treat one table as two tables

select a.ename as'employee name', b.ename as'leader name'

from emp a

join emp b

on a.mgr=b.empno;

Outer join

Left join: It means that the table to the left of the join keyword is regarded as the main table, mainly to display all the data in this table, and to carry the table on the right of the associated query.

Right join: It means that the table on the right of the join keyword is regarded as the main table, which mainly displays all the data in the right table, and carries the table on the left of the associated query.

How to connect three tables or four tables?

select…

from a

join b

on the connection condition of a and b

join c

The connection condition of on a and c

join d

The connection condition of on a and d

Subquery

What is a subquery?

The select statement is nested in the select statement, and the nested select statement is called a subquery.

Subqueries in the where clause

Example: Find out the names and wages of employees who are higher than the minimum wage?

select ename, sal

from emp

where sal>min(sal);

This way of writing is wrong, and grouping functions cannot be used directly in the where clause.

Correct way:

The first step: query what is the minimum wage

​ select min(sal) from emp;

Step 2: Find out those >800

​ select ename,sal from emp where sal>800;

Step 3: Combine

select ename,sal from emp where sal>(select min(sal) from emp);

Subqueries in the from clause

Note: The subquery after from can use the query result of the subquery as a temporary table.

Case: Find the salary grade of the average salary for each position.

The first step: find out the average salary of each position (calculate the average according to the position grouping)

select job,avg(sal) from emp group by job;

Step 2: Treat the above query result as a real table.

select

​ t.*, s.grade

from

​ (select job,avg(sal) as avgsal from emp group by job) t

join

​ salgrade s

on

​ t.avgsal between s.losal and s.hisal;

union merge query result set

The efficiency of union is higher. For table joins, every time a new table is joined, the number of matches meets the Cartesian product, which is doubled. . .

But union can reduce the number of matches. In the case of reducing the number of matches, it can also complete the splicing of the two result sets.

a connect b connect c

a 10 records

b 10 records

c 10 records

Matches: 1000

a result of connecting b: 10*10–>100 times

a One result of connecting c: 10*10–>100 times

Use union: 100 times + 100 times = 200 times (union turns multiplication into addition operation)

limit

Limit is to take out part of the query result set, which is usually used in paging queries.

The role of paging: to improve the user experience.

Full usage: limit startIndex, length

startIndex start index

length taken length

Case: In descending order of salary, remove the top 5 employees?

select

​ ename,sal

from

​ emp

order by

​ sal desc

limit 5;

Note: limit in mysql is executed after order by

Pagination

3 records per page

The first page: limit 0,3 [0,1,2]

The second page: limit 3, 3 [3,4,5]

The third page: limit 5, 3 [6,7,8]

Display pagesize records per page

Page pageno: limit (pageNo-1) *pagesize, pagesize

Table creation

Syntax format of table building

(Building a table belongs to the DDL statement, DDL includes: create drop

alter)

create table table name (field name 1 data type, field name 2 data type, field name 3 data type);

Data types in mysql

varchar up to 255 (variable-length string, space will be dynamically allocated according to the actual data length)

​ Advantages: save space

​ Disadvantages: Need to allocate space dynamically, slow speed.

char up to 255 (fixed-length character string, no matter what the actual data length is, a fixed-length space is allocated to store the data. When it is used improperly, it may lead to a waste of space)

​ Advantages: no need to dynamically allocate space, fast

​ Disadvantages: Improper use may lead to waste of space.

How to choose varchar and char?

​ Gender field char

​ Name field varchar

int (longest 11) Integer type in numbers, equivalent to int in java

The long integer in bigint numbers, which is equivalent to long in Java

float Single-precision floating-point data

double double-precision floating-point data

date short date type

datetime long date type

clob character large object (can store up to 4G string) such as storing an article, storing a description

Blob Binary large objects (specially used to store streaming media data such as pictures, sounds, videos, etc.) are inserted using IO streams

Create a student table

Student ID, name, age, email address

create table t_student(

no int,name varchar(32),sex char(1),age int(3),email varchar(255));

Delete table

drop table if exists t_student ;//Delete if this table exists

Insert data insert (DML)

Syntax format: insert into table name (field name 1, field name 2, field name 3...) values ​​(value 1, value 2, value 3);

Note: The field name must correspond to the value, the quantity must correspond, and the data type must correspond.

insert into t_student(no,name,age,sex,email) values(01,'zhangsan',20,'f','[email protected]');

Note: If the insert statement is executed successfully, there will be one more record.

If no value is specified for other fields, the default value is null

Specify the default value default

Use str_to_date function for type conversion

The str_to_date function can convert a string into a date type date. Often used in insert

Syntax format:

​ str_to_date('string date','date format')

mysql date format:

​ %¥year

​ %m month

​ %d days

​ At %h

​ %i minutes

​ %s seconds

For example: insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990'));

If the provided date string is in this format %¥—%m—%d, mysql will automatically convert it

What is the difference between date and datetime?

date is a short date, only contains year, month, and day information

datetime is a long date, including year, month, day, hour, minute, and second information

The default format of mysql short date: %¥—%m—%d

The default format of mysql long date: %¥—%m—%d %h:%i:%s

Use now() in mysql to get the current time of the system

Modify update (DML)

Syntax format: update table name set field name 1=value 1, field name 2=value 2, field name 3=value 3... where condition;

update t_user set name='jack' where id=2;

Delete data delete (DML)

Syntax format: delete from table name where condition;

Note: If there is no condition, all data in the entire table will be deleted

Example: delete from t_user where id=2;

Quickly delete data in the table

The principle of delete statement to delete data:

The data in the table is deleted, but the real storage space of this data on the hard disk will not be released

The disadvantage of this deletion is that the deletion efficiency is relatively low

The advantage of this kind of deletion is: support rollback, you can restore data if you regret it!

The principle of truncate statement to delete data:

This kind of deletion efficiency is relatively high, the table is truncated once, and physically deleted

Disadvantages of this deletion: rollback is not supported

Advantages: fast

Delete table operation

drop table table name;//This is not to delete the data in the table, this is to delete the table

truncate is to delete the data in the table, the table is still there.

constraint

Constraint: constraint

When creating a table, we can add some constraints to the fields of the table to ensure the integrity and validity of the data in this table

The function of the constraint is to limit the data in the table and ensure that the data added to the data table is accurate and reliable

What are the constraints?

Non-empty constraint: not null

Uniqueness constraint: unique

Primary key constraint: primary key

Foreign key constraint: foreign key

Check constraints: check (mysql does not support, oracle support)

Non-empty constraint

not null

Non-null constraint not null constraint field cannot be null

drop table if exists t_vip;

create table t_vip(id int,`name varchar(255) not null);

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'lisi');

Uniqueness constraint

Unique constraint The field of the unique constraint cannot be repeated, but it can be null

drop table if exists t_vip;

create table t_vip(id int,`name varchar(255) unique, email varchar(255));//The constraint is added directly behind the column, this constraint is called column-level constraint

insert into t_vip(id,name,email)

​ values(1,'zhangsan','[email protected]');

insert into t_vip(id,name,email)

​ values(2,'lisi','[email protected]');

The combination of two fields is unique

idrop table if exists t_vip;

create table t_vip(id int,`name varchar(255), email varchar(255),unique(name,email));//The constraint is added directly after the column, this constraint is called table-level constraint

insert into t_vip(id,name,email) 

​		values(1,'zhangsan','[email protected]');

insert into t_vip(id,name,email)

​		 values(2,'zhangsan','[email protected]');

In mysql, if a field is constrained by not null and unique at the same time, the field automatically becomes the primary key field.

Primary key constraint (primary key)

Terms related to primary key constraints?

Primary key constraint: is a constraint

Primary key field: a primary key constraint has been added to this field

Primary key value: each value in the primary key field, the primary key value

What is the primary key? effect?

The primary key value is the unique identifier of each row of records.

The characteristics of the primary key: not null +unique

Only one primary key constraint can be added to a table.

The primary key value is recommended to use: int bigint char and other types

The primary key value is fixed-length, usually numbers.

The primary key value can be incremented by auto_increment, which starts from 1, and increments by 1.

Foreign key constraint

Foreign key constraint: a constraint

Foreign key field: foreign key constraints have been added to this field

Foreign key value: each value in the foreign key field.

Note: If t_class is the parent table

​ t_student is a child table

The order of deleting the table?

​ Delete the child first, and delete the father.

The order in which the tables are created?

​ Create the parent first, and create the child.

The order of deleting data?

​ Delete the child first, and delete the parent.

The order of inserting data?

​ Insert the father first, insert the son

create table t_class(

​				classno int primary key,

​				classname varchar(255));

create table t_student(

​				no int primary key auto_increment,

​				name varchar(255),

​				cno int,

​				foreign key(cno) references t_class(classno)

);

A certain field in the parent table referenced by the foreign key in the child table, must the referenced field be the primary key?

Not necessarily the primary key, but at least the unique constraint

The foreign key value can be null