Mysql Basics

=. = a small white one, only for learning records... Big guy light spray... Communicate together... Wow... Yingying~~

Chapter I overview of database

1.1 why use database

Persistence: save the data to a power-off storage device for later use. Data persistence means saving the data in memory to the hard disk for "solidification". The main application of persistence is to store the data in memory in a relational database. Of course, it can also be stored in disk files and XML data files.

1.2 related concepts of database

DB: Database
That is, the "warehouse" where data is stored. It holds a series of organized data.
DBMS: Database Management System
It is a large-scale software for manipulating and managing databases, such as establishing, using and maintaining databases.
SQL: Structure Query Language
A language designed to communicate with a database.

Chapter 2 MySQL database

2.1 MySQL overview

MySQL is an open source relational database management system developed by Swedish MySQL AB company. It was acquired by SUN on January 16, 2008. In 2009, SUN was acquired by Oracle. At present, MySQL is widely used in small and medium-sized websites on the Internet. Because of its small size, fast speed and low total cost of ownership, especially the characteristics of open source, many Internet companies choose MySQL as the website database

2.2MySQL database service start and stop

The server side of MySQL software must be started before the client can connect and use the database.

Mode 1: graphical mode

Computer (right click) ----- > management --------- > services and applications --------- > Services --------- > mysql57 -------- start or stop

Mode 2: command line mode

start-up MySQL Service command: net start MySQL57
 stop it MySQL Service command: net stop MySQL57

2.3MySQL database environment variable configuration

Environment variable name	operation	Environment variable value
MYSQL_HOME	newly build	C:\develop\MySQL\MySQL Server 5.7
path	edit	%MYSQL_HOME%\bin

To configure environment variables:

Computer (right click) ----- > advanced system settings --------- > system properties: Advanced --------- > environment variable --------- > new system variable MySQL_ Home -------- edit variable Path

2.4MySQL database client login

Method 1: MySQL built-in client

Start menu - > all programs - > MySQL 5.7 - > MySQL 5.7 command line client

Mode 2: command line

Specific operation mode:

Enter the command after C:\WINDOWS\system32: mysql -h localhost -P 3306 -u root -p

enter and prompt for password:root

mysql -h hostname - p port number - u username - p password

For example:

mysql -h localhost -P 3306 -u root -proot
//The password here is root

be careful:

-There can be no spaces between p and password, and there can be or no spaces between other parameter names and parameter values

mysql -hlocalhost -P3306 -uroot -proot

The password is recommended on the next line

mysql -h localhost -P 3306 -u root -p 
Enter password: ****

For example:

C:\WINDOWS\system32>mysql -h localhost -P 3306 -u root -p
Enter password: ****

If it is connected to the local computer: - hlocalhost can be omitted. If the port number is not modified: - P3306 can also be omitted, which is abbreviated as:
```
mysql -u root -p
Enter password:****
```

Method 3: visualization tool

For example, Navicat, SQLyog and other tools

2.5MySQL database coding configuration

View encoding commands

mysql> show variables like 'character_%';

2.6 common commands in MySQL

View the version of the database

select version();

Displays the database. How many databases are there in total

show databases;

Display tables in the database;

show tables;

show tables from library name;

Use / switch database

Database name;

Display the library currently in use;

select database();

Create a test library

create database test;

Create a table

create table table name(

Field name 1, field class line 1,

Field name 2 field class line 2,

...

Field name n field class row n

);

For example: create table student(id int,name varchar(25));

View table structure

desc table name;

View data in table

select * from table name;

select * from student;
Insert data into a table

insert into table name (field 1, field 2,..., field n) values (data value 1, data value 2,..., data value n);

insert into student(id,name)values(1,'wangzong');

insert into student(id,name)values(2,'zhuzong');
Modify data

update table name set field name = data value [where field name = data value]

update student set name='wozainihoumian' where id =2;
Delete data

delete from table name [where field name = data value]

delete from student where id =2;

Chapter 3 overview of SQL

3.1 what is SQL?

Structured query language: Structured Query Language
In fact, it defines the rules for operating all relational databases. There are different ways for each database operation
It is "dialect".

3.2SQL general syntax

SQL statements can be written in one or more lines, ending with semicolons.
Spaces and indents can be used to enhance the readability of statements.

The SQL statements of MySQL database are not case sensitive. It is recommended to use uppercase for keywords.

notes
- Single line comment: – comment content or # comment content (unique to mysql)
- Multiline comment: / * comment*/

3.3 SQL classification

DDL(Data Definition Language)
Used to define database objects: database, table, column, etc. Keywords: create, drop,alter, etc
DML(Data Manipulation Language)
It is used to add, delete and modify the data of tables in the database. Keywords: insert, delete, update, etc
DQL(Data Query Language)
Used to query the records (data) of tables in the database. Keywords: select, where, etc
DCL(Data Control Language)
It is used to define the access permission and security level of the database and create users. Keywords: GRANT, REVOKE, etc

Chapter IV DDL: operation database and table

4.1 operation database: CRUD

4.1.1C(Create): create

Create a database, judge that it does not exist, and then create:

create database if not exists;

Create a database and specify a character set

create database database name character set character set name;

Exercise: create a db4 database, determine whether it exists, and set the character set to gbk

  create database if not exists db4 character set gbk;

4.1.2R(Retrieve): query (English) / R ɪˈ tri ː v / retrieve; recover; retrieve)

Query the names of all databases:

show databases;
Query the character set of a database: query the creation statement of a database

show create database database name;

4.1.3U(Update): modify

Modify the character set of the database

alter database database name character set character set name;

4.1.4D(Delete): delete

Judge whether the database exists, and then delete it

drop database if exists database name;

4.1.5 using database

Query the name of the database currently in use

select database();
Use database

use database name;

4.2 operation table

4.2.1C(Create): create

Syntax:

create table table name(

Column data type 1 [(length) constraint], ---------- columns are separated by commas

Column data type 2 [(length) constraint],

Data type of column 3 [(length) constraint],

.........

Type of column n [(length) constraint]);

Note: comma (,) is not required for the last column

View table structure: desc table name

case

CREATE TABLE employee (
	id INT,
	NAME VARCHAR (25),
	age INT,
	salary DOUBLE
);

4.2.2R(Retrieve): query

Query all table names in a database

show tables

Structure of query table

desc table name;

4.2.3U(Update): modify

Syntax:

alter table name change / modify /add / drop column name [column type constraint];

Modification type:
- Modify column name: alter table name change column old column name new column name new type;
- Modify column type or constraint: alter table name modify column old column name new type [new constraint];
- Add a new column: alter table name add column name data type [first | after column name];
  
  ---- > (first means that the added new column is in the first column of the table, and after column name: means that the added new column is after the column name)
- Delete column: alter table name drop column name;
- Modify table name: alter table name rename [to] new table name;
- Modify the character set of the table: alter table name character set character set name

4.2.4D(Delete): delete

Syntax:

drop table if exists table name;
be careful:

if exists can only be used when creating and deleting libraries and tables. Column operations cannot be used.

4.3 common data types of MySQL

Numeric:

integer

tinyint, smallint, mediumint, int/integer, bigint (representing 1-byte, 2-byte, 3-byte, 4-byte and 8-byte integers respectively)

decimal

Floating point type

float(M,D) -- single precision (floating point value)

double(M,D) -- double precision (floating point value)

Fixed point type

dec(M，D)

decimal(M,D)
character:

Shorter text: char (variable length), varchar (variable length)

Long text: text, blob (long binary data)

Others:

binary and varbinary are used to hold shorter binaries
Enum: enum, enum (male, female)
Date type:

Date: date, only including yyyy mm DD

datetime: date, including yyyy MM DD HH: mm: SS

Timestamp: timestamp type includes yyyy mm ddhh: mm: SS

See the following table for details:

MySQL	data type	JAVA	length
TINYINT	integer	byte	1
SMALINT	integer	short	2
INT	integer	int	4
BIGINT	integer	long	8
nothing		boolean	In sql, 1 means true and 0 means false
FLOAT	Decimal a	float	Single-precision floating-point
DOUBLE	decimal	double	Double precision floating point number
VARCHAR	character string	string	You must specify a width, enclosed in single or double quotation marks
TIMESTAMP	Date type		Time type

be careful:

String type: char,varchar(M),text

char if no width is specified, the default value is 1 character

varchar(M). The width must be specified. Generally, the length is 25 varchar(25)

Date time types: year, date, datetime, timestamp, similar to the java.sql.Date class in Java

Chapter V DML: data in addition, deletion and modification table

insert,delete,update

5.1 adding data

grammar

 
 -- Mode 1:
 insert into Table name(Column name 1,Column name 2,...Listing n) values(Value 1,Value 2,...value n);
 
 -- Mode 2:
 insert into Table name set Column name 1=Value 1, Column name 2=Value 2;
 -- Note: except for numeric, the assignment of string should be enclosed in single quotation marks or double quotation marks
 
 insert into student set name = 'chinese rhubarb',math = 99; --  As shown in the following figure, the default values of fields without assignment are null

be careful

Column names and values should correspond to each other.

insert into salary(name,salary) values('Pan Zong',2000000000);-- Insert values in those fields first, and just write those fields
insert into salary(id,name,salary) values(null,'cocoa',19900);-- Namely: use null Value fill field id

If no column name is defined after the table name, values are added to all columns by default

insert into Table name values(Value 1,Value 2,...value n);

In addition to numeric types, other types (string varchar) need to be enclosed in quotation marks (either single or double)
```
insert into salary values(3,'Chu Zong',2000000000),(4,'Huang Zong',2000000000);
```

5.2 deleting data

grammar

delete from Table name [where Screening conditions] [limit] -- there limit Is to limit the number of deleted entries

-- Cases: deleting student Inside the watch, english Record of the last three students
delete from student order by english limit 3;

be careful
- If no condition is added, all records in the table will be deleted.
```
 delete from stu;
 delete from stu where id = 1;
```
- If you want to delete all records
  
  delete from table name; – Not recommended. How many records will be deleted
  
  truncate table name; – Recommended. It is more efficient. First delete the table, and then create the same table.
```
truncate table stu;       
```
Comparison of delete, truncate and drop

Differences:

① statement type: delete statement is data operation language (DML); truncate and drop are data definition language (DDL);

② syntax:

delete from table name where filter criteria;

truncate table name;

drop table if exists table name;

③ efficiency: drop > truncate > delete;

④ if there is self growth in the table to be deleted, if you use delete to delete and then insert data, the value of the self growth column starts from the breakpoint, and after truncate is deleted, insert data again, and the value of the self growth column starts from 1

⑤ truncate deletion cannot be rolled back. Delete deletion can be rolled back; Truncate has no return value for deletion, and delete has a return value (delete will return the number of affected rows, truncate does not)

5.3 modifying data

Syntax:

 update Table name set Column name 1 = Value 1, Column name 2 = Value 2,... [where condition];
 -- Case:
 update student set math = 62 where name like '_virtue%';-- In the name, the second word is'virtue'Student's math Change to 62

Note: if no conditions are added after where, all records in the table will be modified.

Chapter 6 data in DQL query table

Query summary (★★★★★★★★) --- the order is very important

-- The sequence number here represents the execution order of query statements

select Query list (7)

from Aliases for table 1    (1)  

[line type]join Table 2 aliases (2)            -- Connection type: inner join/left join/right join

on Connection conditions  (3)

[where Screening conditions]  (4)

[group by grouping]  (5)

[having Filter criteria after grouping]   (6)

[order by Sort list]  (8)

limit (start-1)*pageSize,pageSize; (9)     

--  limit Statement is placed at the end of the query statement★(Execution order and grammar writing are put at the end)

6.1 syntax of query statement

Syntax order:
  select Field list  from Table name list  where Condition list  group by Grouping field  having Conditions after grouping
  order by sort   limit Paging limit

6.2 data preparation

CREATE TABLE student ( -- new table student
	id INT,
	-- number 
	NAME VARCHAR (20),
	-- full name 
	age INT,
	-- Age 
	sex VARCHAR (5),
	-- Gender 
	address VARCHAR (100),
	-- address 
	math INT,
	-- mathematics 
	english INT -- English 
);

 -- insert data
INSERT INTO student (id,NAME,age,sex,address,math,english) VALUES
	(1,'Jack Ma',55,'male','Hangzhou',66,78),
	(2,'pony ',45,'female','Shenzhen',98,87),
	(3,'Ma Jingtao',55,'male','Hong Kong',56,77),
	(4,'Liuyan',20,'female','Hunan',76,65),
	(5,'Liu Qing',20,'male','Hunan',86,NULL),
	(6,'Lau Andy',57,'male','Hong Kong',99,99),
	(7,'Madder',22,'female','Hong Kong',99,99),
	(8,'Demacia',18,'male','Nanjing',56,65),
	(9,'k_ing',25,'male','Suzhou',100,100);

6.3 basic query

grammar
```
select Query list from Table name;
```
Similar to system.out.println (printed content);
characteristic:
- The query list can be: fields in the table, constant values, expressions, and functions
- The result of the query is a virtual table (knowledge points of the view)
Single field query

  select Listing from Table name;
  select name from student;

Query of multiple fields

select Field name 1, field name 2... from Table name;
select id,name,salary from student;

Query all fields

select * from Table name;
select * from student;

Note: the ` ` symbol can be used in mysql to remove the special meaning of the field

For example, name is a keyword in sql. If name is a field in a table, it needs to be cited with `

select `name` from student;

Query constant value

select 100;  -- 100
select 'john';-- john

Query expression

select 100%98;    -- 2

Query function

select VERSION();

There are two ways to view:

In the black window

In Navicat

Alias
- The first way is to use as
```
select 100%98 as result;
```
- Benefits of alias: easy to understand and distinguish
- The second way is to use spaces
```
select 100%98  result
```
  Note: in special cases, if the alias itself contains spaces, it needs to be enclosed in single quotation marks, such as
```
select 100%98 'result';
```

Remove duplicates: distinct

Single field

 select distinct Field name 1, field name 2... from Table name;

Multiple fields

select distinct ID,AA,BB from tName
-- When querying multiple fields, the fields are spliced first, and the spliced results are de duplicated

The above statement is to find a row composed of field ID+AA+BB that is not repeated in the whole table;

+Function of No
- The function of the + sign in mysql: there is only one function: operator
- select 100+90; If both operands are numeric, add and the output result is 190
- select ‘123’+90; As long as one of them is character type, try to convert character type numerical value to numerical value
```
If**If the conversion is successful, continue to add**Operation, the output result is 213
```
- select ‘john’+90; If the conversion fails, the character value is converted to 0 and the output result is 90
- select null+10; As long as one of them is null, the result must be null and the output result is null
- There are two meanings in java +
- Operator: both operands are numeric
- Connector: as long as one operand is a string

Note: in mysql, if you want to splice strings, you can use the function concat

 select concat(id,'_',name,'^'balance) from account;

6.4 query criteria

**Grammar**

 select  Query list from Table name where Screening conditions;  -- where keyword

classification

Filter by conditional expression

Conditional operator: > < =! = < > < = >=

  Case 1: query age>30 Year old student information
  select * from student where age >30；
  
  Case 2: query the name and address of students who do not live in Hangzhou
  select name,address from student where address != 'Hangzhou';

Filter by logical expression

Action: used to join conditional expressions

Logical operator: & & |! and or not

& & and: as long as two conditions are true, the result is true, and vice versa

|| and or: if one of the two conditions is true, the result is true, otherwise it is false

! And not: negative

  Case 1: query the names of students whose math scores are greater than 80 and less than 90,Age, and math scores
  select name,age,math from student where math>80 and math <90; Recommended use
  select name,age,math from student where math>80 && math <90;
  
  Case 2: Query age is not 20-30 Information about students between the ages of, or students whose math scores are greater than 90 points
  select * from student where age <20 or age >30 or math >90;
  select * from student where not (age >=20 and age <=30) or math >90;

Fuzzy query

Note: like is generally used with wildcards

Placeholder introduction

%: indicates any matching character (unlimited number, can be 0 characters)

_: Represents an arbitrary character (only one position)

  Case 1: query student information with characters in employee name
  select * from student where name like '%virtue%';

  Case 2: Query the student information of the employee whose second word is de
  select * from student where name like '_virtue%';

  Case 3: Query the student information whose second word is de and whose name length is 2
  select * from student where name like '_virtue';

  Case 4.Query the student information with name length of 3 in employees
  select * from student where name like '__';  -- There are three'_'

  exceptional case:Query name contains_Students,Need escape,Will have special consciousness_Convert symbol to normal symbol: underline_
  select * from student where name like '%\_%';

Case 2: query the student information of the employee whose second word is de ------ > the query results are as follows:

between and

① Using between and can improve the brevity of statements

② Including critical value ([a,b])

③ Do not change the order of the two critical values

  Case: query the information of students aged between 18 and 30
   select * from student where age >=18 and age <=30;
 --  Equivalent to:
   select * from student where age between 18 and 30;

Meaning: judge whether the value of a field belongs to an item in the in list

Features:

① use in to improve sentence conciseness

② the value types of in list must be consistent or compatible

③ wildcards are not supported in the in list

For example, it is wrong to write this: select *from student where name in('liu% ','uude%');

select * from student where address = 'Hangzhou' or address ='Hong Kong' or  address ='Shenzhen';
--  It is completely equivalent to the following statement:
select * from student where address in ('Hangzhou','Hong Kong','Shenzhen');

is null

= or < > cannot be used to determine null values

is null or is not null determines the null value

Case 1: Query English score is null Student's name and address
select name,address,english from student where english is null;
 
Case 2: Query English score is not null Student's name and address
select name ,address ,english from student where english is not null;

6.5 Sorting Query

Syntax:

  select  Query list from surface [where Screening conditions] order by Sort list [asc|desc]

Sorting method: ASC: ascending; default; can be omitted; DESC: descending.

be careful:
- If there are multiple sorting conditions after order by, and the first sorting condition is consistent, the secondary condition judgment will be performed
- The order by clause can support a single field, multiple fields, filter criteria, aliases, expressions, and functions
- The order by clause is at the end of the query statement, except for the limit clause

 Case 1:Query student information,It is required to rank from high to low in mathematics
 -- Sort by single field
 select * from student order by math desc ;
 
 -- Sort by multiple fields
 Case 2:Query student information, It is required to sort from high to low according to the math scores. If the math scores are the same, it shall be sorted in ascending order according to the English scores
 select * from student order by math desc,english asc;

 Case 3:Query the information of male students and sort them from high to low according to their math scores
 select * from student where sex ='male' order by math desc;


-- Sort by alias
 Case 4:Query student information, which is required to be sorted from high to low according to the total score
 select * ,math+english as Total score from student order by Total score desc;
 
 -- Sort by expression
 Case 5:Query student information, which is required to be sorted from high to low according to the total score
 select * ,math+english from student order by math+english desc;

Problems:

null value handling

ifnull function

Syntax: ifnull (field, value)

Explanation: when the result in the field is null, replace it with a value. If it is not null, keep the original field value
```
select ifnull(english,0) from student;

-- Optimized version:
select * ,math+ifnull(english,0) as Total score from student order by Total score desc;
```

6.6 common functions

Classification:

The first category: single line functions (character functions, mathematical functions, date functions, process control functions, and other functions)

The second category: grouping function (function: statistical use, also known as statistical function, aggregation function and group function)

6.6.1 single line function

Character function

length gets the number of bytes of the parameter value (the character set needs to be considered)

select length('john');
--     UTF-8 In the development environment of, a letter takes up 1 byte and a Chinese character takes up 3 bytes

concat splice string

select concat (name,'_','&',age) from student;

upper (to uppercase), lower (to lowercase)

select upper('john');
select lower('JOHN');

substr and substring intercept strings. Note: the index starts from 1

-- stay sql in,Index starts at 1 (focus)
Case 1: intercept all characters following the specified index
select substr('2020-08-08',6);  -- The result is 08-08

Case 2: intercept from specified index,Characters of specified length
select substr('Li Mochou fell in love with Lu Zhanyuan',7,3);-- The result is Lu Zhanyuan

 Case: capitalize last name,Name lowercase,Then splice
 select concat(upper('nihao'),lower('zaijian'));

instr: returns the index of the first occurrence of the substring. If it is not found, the return value is 0

select instr('I'm going to cook fried rice this noon,Take out for lunch yesterday','noon');  -- The result is 3
select instr('I'm going to cook fried rice this noon,Take out for lunch yesterday','Noon');  -- The result is 0

trim: the function removes the leading and trailing spaces (Note: the leading and trailing spaces)

select trim('    Zhang Cuishan   ');  -- The result is: Zhang Cuishan
select trim('aaab' from 'aaaaab Zhang aaaa Cuishan aaaaab');-- The result is: aaaaab Zhang aaaa Cuishan aa

lpad, rpad: fill left / right with the specified characters, and return the characters of the specified length

 select lpad('i am hungry',10,'*&');  --  *&*&*&*i am hungry
 select rpad('I am sleepy',10,'a^');  -- I am sleepy a^a^a^a

Replace replace

    select replace('Zhang Wuji falls in love with Zhou Zhiruo','Zhou Zhiruo','Zhao Min'); 
    -- Zhang Wuji fell in love with Zhao min

Mathematical function

Round round

select round(1.356); -- 1
select round(1.567,2);-- 1.57  
-- 2 here represents the number of decimal places to be reserved

ceil rounds up and returns > = the smallest integer of the parameter

 select ceil(1.1);  -- 2
 select ceil(2.0);  -- 2
 select ceil(2.02);  -- 3
 select ceil(-1.5);  -- The result is: -1

floor rounded down and returns < = the maximum integer of the parameter

select floor(1.9); -- The results are: 1
select floor(-1.9); -- The result is -2

truncate

SELECT TRUNCATE(1.4567,2);  -- 1.45
-- 2 here means that two digits are reserved after the decimal point after truncation
SELECT TRUNCATE(-1.48932,3);  -- The result is -1.489

SELECT TRUNCATE(34890.1,3);  -- The result is 34890.1
SELECT TRUNCATE(34890,3);  -- The result is 34890

mod remainder (the positive and negative of the remainder is consistent with the divisor)

 select a % b;  
 -- Equivalent to: 
 select mod(a,b);
 
 select mod(10,3); -- The result is 1
 select mod(-10,3);-- The result is -1
 select mod(10,-3);-- The result is 1
 select mod(-10,-3);-- The result is -1

Date function

now: returns the current system date + time

select now(); -- 2021-10-02 11:35:09

Current date: returns the current system date, excluding time

select curdate();-- 2021-10-02

Gets the specified part, year, month, day, hour, minute, and second
```
select year(now()); -- 2021
select month('2021-10-02'); -- 10
```
str_to_date: converts characters into dates in the specified format

Serial number	Formatter	function
1	%Y	4-digit year
2	%y	2-digit year
3	%m	Month (01,02,03...)
4	%c	Month (1, 2, 3,...)
5	%d	Day (01,02...)
6	%H	Hour (24-hour system)
7	%h	Hour (12 hour system)
8	%i	Minutes (00,01... 59)

select str_to_date('10-02-2021','%m-%d-%Y'); -- 2021-10-02
-- The expression means: according to the specified format'%m-%d-%Y'，To parse a string'10-02-2021'，Matching succeeded, output date form

select str_to_date('10-02-2021','%m/%d/%Y'); -- The result is empty

date_format: converts the date into a string in the specified format

Case 1:
select date_format('2021-10-02','%Y year-%m month-%d day');
-- The result: 2021-10 month-02 day
-- The expression means: according to the specified format:'%Y year-%m month-%d day',Convert date to string (no parsing process)

Case 2:
select date_format('2020/11/23','%Y&%m&%d');
-- The result: 2020&11&23

-- reflection: date_format(Date, specifying format)In the function, the format of the date seems to be fixed, that is, it can only be"specific date"In the form of,"Month day year"Etc. cannot be converted to string

Other functions
- select VERSION(); -----> View the version of the database
- select database();------> Query the name of the database currently in use

Process control function

The if function is similar to if else

  if(Conditional expression,true Execute this, false Execute this)
  select if(10>5,'greater than','less than');
  select if(english is null ,'I didn't take the English test','I took the English test') from student;

The case function is similar to switch case

  case Field or expression to judge
  when Constant 1 then Displayed value 1
  when Constant 2 then Displayed value 2
  else Value to display n
  end

  Case: multiply math scores by different coefficients according to different regions(Hangzhou:1.1;Shenzhen: 1.2; Hong Kong:1.3, Other unchanged)
    select address ,CASE address
    WHEN 'Hangzhou' THEN
        math * 1.1
    WHEN 'Shenzhen' THEN
        math * 1.2
    WHEN 'Hong Kong' THEN
        math * 1.3
    ELSE
        math
    END AS newMath  -- Here I give the whole structure: case....end Aliased newMath,Because from the overall position, he is in select The fields in the field list can be aliased!
    FROM
        student;

6.6.2 grouping function (aggregate function)

Grouping function (aggregation function) is used for statistics

count: calculate the number of (rows)

Since all aggregate functions are evaluated, null values are directly excluded.

However, at this time, it is necessary to calculate the number of all rows (including null rows)

What should I do?
Generally, non empty columns are selected: primary key
count(*)

select * from student;
select count(english) as number  from student; -- The result is 8, because Liu Qing's English score is null
select count(1) as number  from student;   --  The result was nine,

-- In this way, the effect of query is like in student A new column is added to the last column of the whole table count(1)，The field values of this column are all 1, and then-- Calculation column count(1)Number of rows
select count(*) as number  from student;  --  The result was nine,

max: calculate maximum

select max(math) from student;

min: calculate the minimum value

select min(math) from student;

sum: Calculation and

 select max(math) from student;

avg: calculate average

 select avg(math)  from student;

It can be used with distinct

  select sum(distinct math),sum(math) from student;
  select count(distinct math),count(math) from student

be careful:
- The calculation of all aggregate functions directly excludes null values.
  
  – solution:
  
  1. Select non empty columns for calculation
  
  2. IFNULL function
- sum(),avg() function only supports numeric values; min(),max(),count() values and characters are OK;
- There are restrictions on the fields queried together with the grouping function. Generally speaking, the fields after group by, that is, the parameters after group by must exist in the field list of select

6.7 group query

grammar

select Grouping function,Grouped fields 
from surface
[where Screening conditions]
group by Grouped fields 
[order by Sorted fields];

be careful:
After select, the field queried with the grouping function must be the field after group by
Grouping can be by a single field or by multiple fields. Multiple fields are separated by commas, and there is no order requirement
What's the difference between where and having?
- where is limited before grouping. If the conditions are not met, it will not participate in grouping. having is qualified after grouping. If the result is not satisfied, it will not be queried
- where cannot be followed by the aggregate function. having can judge the aggregate function.

Table for		Connected keywords	position
Original table A	Filtering before grouping	where clause	group by front
Result set after group by	Filtering after grouping	having clause	After group by

--  Group according to a single field
-- Case 1:Query the highest scores in mathematics and English of different genders
    select sex,max(math),max(english) from student group by sex;
    
-- Case 2: count the number of students in different regions    
	select address,count(*) from student group by address;

-- Case 3:Students from Hong Kong are counted and the average math score is calculated according to gender
    select sex,avg(math) from student where address ='Hong Kong' group by sex; 
    
    
    
--  Group by multiple fields    
-- Case 4:The average scores of students of different regions and genders were counted
    select address,sex,avg(math) from student group by address,sex;
    
    -- having Aggregate functions can be followed
-- Case 5:Count the average math scores of students in different regions and require the average score to be greater than 80
    select address,avg(math) as math_avg from student group by address having math_avg >80;
   
-- Case 6: according to statistics, students from Hong Kong are required to get an average score of Mathematics according to gender, and the average score is required to be greater than 80
    select  sex, avg(math) math_avg from student where address='Hong Kong' group by sex having math_avg >80;  

   
   
   -- group by You can follow the function
-- Case 7: group students according to the length of their names and count the number of students in each group
   select length(name),count(*)  from student group by length(name);
   
-- Case 8:Count the average math scores of students of different regions and genders, and order them from large to small according to the average math scores
   select address ,sex,avg(math) math_avg from student group by address,sex order by math_avg desc;

6.8 paging query ★ (widely used in daily life)

The client queries the data in the database table by passing two parameters: start (page number) and PageSize (number of items displayed per page). mysql database provides the paging function limit m,n, but the usage of this function is different from our requirements, so we need to rewrite our own paging statements according to the actual situation. The specific analysis is as follows:

For example:

The sql used to query the data from items 1 to 10 is: select * from table limit 0,10; ------ > Our requirement is to query the data on the first page: select * from table limit (1-1)*10,10;

The sql used to query the data from Articles 11 to 20 is: select * from table limit 10,10; ------ > Our requirement is to query the data on the second page: select * from table limit (2-1)*10,10;

Through the above analysis, it can be concluded that the paging sql format is:

select * from table limit (start-1)*pageSize,pageSize; 
-- among start Is the page number, pageSize Is the number of entries displayed per page.

6.8.1 limit clause

Can be used to force a select statement to return a specified number of records. limit accepts one or two numeric parameters. Parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first return record line, and the second parameter specifies the maximum number of return record lines. The offset of the initial record line is 0 (not 1)

6.8.2 limit function

limit y clause means: read y pieces of data (read y pieces of data from the first record, which is equivalent to limit (0,y)
limit x,y clause means: skip x pieces of data and read y pieces of data
limit y offset x clause means to skip x pieces of data and read y pieces of data

 -- Example-1
  limit 10  Indicates: read the first 10 records
--  Example-2
  limit 2,8  Indicates: starting from the third record, read 8 records, i.e. 3,4,5,6,7,8,9,10
 -- Example-3
  limit 8  offset 2  Indicates: starting from the third record, read 8 records, i.e. 3,4,5,6,7,8,9,10

6.9 multi table query (connection query)

Meaning: join query is used when the query fields come from multiple tables.

Features: there will be Cartesian product: Table 1 has m rows, table 2 has n rows, and the result set = m*n rows

Reason for Cartesian product phenomenon: when querying multiple tables, no effective connection conditions are added, resulting in complete connection of all rows of multiple tables

Prepare sql data:

# Create department table
CREATE TABLE dept (
	id INT,
	NAME VARCHAR (20),
	location_id INT
);

INSERT INTO dept (id, NAME, location_id)
VALUES
	(1, 'Development Department', 1),
	(2, 'Marketing Department', 2),
	(3, 'Finance Department', 3);
	
	
# Create location table
CREATE TABLE location (
	id INT,
	location_name VARCHAR (25)
);

INSERT INTO location (id, location_name)
VALUES
	(1, 'Beijing'),
	(2, 'Shenzhen'),
	(3, 'Shanghai');
	
	
	
# Create employee table
CREATE TABLE emp (
	id INT,
	NAME VARCHAR (10),
	gender CHAR (1),
	-- Gender
	salary DOUBLE,
	-- wages
	join_date DATE,
	-- Entry date
	dept_id INT
);

INSERT INTO emp (id,NAME,gender,salary,join_date,dept_id)
VALUES
(1,'Sun WuKong','male',7200,'2013-02-24',1),
(2,'Zhu Bajie','male',3600,'2010-12-02',2),
(3,'Tang Monk','male',9000,'2008-08-08',2),
(4,'Baigujing','female',5000,'2015-10-07',3),
(5,'spider goblin','female',4500,'2011-03-14',1);


#Create salary scale	
   create table salary_level(
     salary_level varchar(3),
     lowest_sal int,
     highest_sal int
   );
   insert into salary_level 
   values
   ('A',2000,4000),
   ('B',4000,6000),
   ('C',6000,8000),
   ('D',8000,10000);

6.9.1 sql99 standard

grammar

select query list

from table 1 alias [connection type] - > connection type: internal connection, external connection and cross connection

join table 2 alias

on connection condition

[where screening criteria]

[group by group]

[having filter criteria after grouping]

[order by sort list]

Note: if a table has been aliased, the query field cannot be qualified with the original table name

Classification by function
- Inner connection (taking intersection): equivalent connection and non equivalent connection
- External connection: left external connection, right external connection
- Cross connect (Union)

Six connection modes are graphically represented:

Internal connection (application scenario: query the intersection of multiple tables)

Syntax:

select Query list
from Aliases for table 1
inner join Aliases for table 2    -- inner Can be omitted
on Connection conditions;
[where Screening conditions]
[group by grouping]
[having Filter criteria after grouping]
[order by Sort list]

characteristic:

① The order of tables can be changed

② Result of inner join = intersection of multiple tables

③ N table connection requires at least n-1 connection conditions

Internal connection classification:

(1) Equivalent

(2) non equivalent

Equivalent (connection condition =)

case:The average salary of employees is counted, grouped by department name, and sorted from high to low by average salary

    SELECT
        d. NAME,
        avg(salary) avg_s -- Give the average wage an alias avg_s
    FROM
        emp e
    INNER JOIN dept d ON e.dept_id = d.id
    GROUP BY
        d. NAME
    ORDER BY
        avg_s DESC;

between... and

case:Ask for everyone's salary level

SELECT
	e.*, s.salary_level AS s_level
FROM
	emp e
INNER JOIN 
   salary_level s 
ON 
   e.salary 
BETWEEN 
    s.lowest_sal
AND 
    highest_sal;

External connection (★★★)
- Application scenario:
  
  It is generally used to query the remaining mismatched rows of multiple tables except the intersection part
- Syntax:
select query list

from table 1 alias

left|right join table 2 alias

on connection condition

[where filter criteria]

[group by group list]

[having filtering after grouping]

[order by sort list]

[limit clause;]
- characteristic:
  
  ① The query result of external connection is all records in the main table
  
  External connection query result = internal connection result + records in the master table but not in the slave table (★★★)
  
  If there is a matching from the table, the matching value is displayed
  
  If there is no matching from the table, null is displayed
  
  ② The left outer join is connected, and the main table is on the left of the left join.
  
  Whether the left table can match the conditions or not, it will be retained in the end: if it can match, the correct one will be retained; If there is no match, the fields in the right table are set to NULL. (therefore, the where clause often uses the right table. The primary key is null to further filter)
  
  ③ Right outer join. The main table is on the right of right join.
  
  Whether the right table can match the conditions or not, it will be retained in the end: if it can match, the correct one will be retained; If there is no match, the fields in the left table are set to NULL. (therefore, the where clause often uses the left table. Primary key is null to further filter)
  
  ④ Exchange the order of the two tables, switch the keywords left join and right join, and the output results are consistent;
  - Left outer connection
```
select e.*,d.NAME 
from 
  emp e 
left join     -- The main table is e
  dept d
on e.dept_id = d.id;
```
  - Right outer connection
```
SELECT
	d.*, e.*
FROM
	emp e     
RIGHT JOIN dept d ON e.dept_id = d.id;   -- The main table is d
```
  - Left outer connection * * (solve only the data in the main table)**
```
SELECT
	e.*, d. NAME
FROM
	emp e
LEFT JOIN dept d ON e.dept_id = d.id  --  The main table is e
where d.id is null; -- Further screening of left outer connection, using'where From table.Primary key is null' Statement to filter
```
  - Right external connection * * (solve only the data in the main table)**
```
SELECT
	d.*, e.*
FROM
	emp e
RIGHT JOIN dept d ON e.dept_id = d.id  -- The main table is d
where e.id is null; -- Further screening of right outer connection, using'where From table.Primary key is null' Statement to filter
```

6.9.2 sql92 standard (not introduced)

6.10 sub query (★★★)

meaning

select statements that appear in other statements are called subqueries or internal queries, and external query statements are called primary queries or external queries.
characteristic

① Subqueries are enclosed in parentheses

② Subqueries are generally placed on the right side of the filter criteria (where filter, having filter after grouping)

③ Scalar subquery (subquery returns only single row and single column), which is generally used with single row operator > < > = < = < >

Column subquery (subquery returns results: one column and multiple rows), which is generally used with the multi row operator in, etc

Subquery with in keyword: use the in keyword to compare the value of a specific column in the original table with the value in the result set (one column and multiple rows) returned by the subquery;

④ The execution order of the sub query takes precedence over the main query, that is, the conditions of the main query use the results of the sub query
classification
- Location of sub query: (★)
  - After select: the result set has only one row and one column
  - After from: the result set is generally multi row and multi column - > sub query can be used as a virtual table to participate in the query
  - After where or having:
  Scalar subquery (the result set has only one row and one column) ★
  
  Column subquery (the result set has multiple rows and one column) ★
  
  Row subquery (result set has one row and multiple columns)
- According to the number of rows and columns in the result set:
  - Scalar subquery (result set has only one row and one column)
  - Column subquery (result set has multiple rows and one column)
  - Row subquery (result set has one row and multiple columns)
  - Table sub query (the result set is generally multi row and multi column)

-- Case 1: query the employee whose salary is less than the average salary
select * from emp where salary <(select avg(salary)  from emp);  
-- The result set of subquery is single row and single column
-- It is generally used with single line operators >  <   >=   <=   =   <>


-- Case 2: query'Finance Department'and'Marketing Department'All employee information
-- The result set of a subquery is a column with multiple rows, which is generally matched with multiple row operators in Such use 
    -- Method 1:
    select * from emp  where dept_id in (select id from dept  where name in ('Marketing Department','Finance Department'));
    
     -- Method 2:
   select * from emp where dept_id in(select id from dept where name = 'Marketing Department' or 'Finance Department');
   
   -- Method 3: use inner join instead of sub query
   select e.*,d.name from emp e inner join dept d on e.dept_id = d.id
   where d.name in('Marketing Department','Finance Department');
   
   
  
   
-- Case 3: query employee employment date is 2011-11-11 Employee information and department information after
--  The result set of the sub query is multi row and multi column. At this time, the sub query can be used as a virtual table to participate in the query
  
select * from emp where join_date > 2011-11-11; -- The first step is to set the entry date as 2011-11-11 Employee information table after day, virtual table v Find out


select v.*,d.name from (select * from emp where join_date > 2011-11-11) v 
inner join dept d on v.dept_id = d.id;  -- The second step is to compare with the Department table dept Internal connection can be used here

select v.*,d.name from (select * from emp where join_date > 2011-11-11) v 
left join dept d on v.dept_id = d.id;  --  And department table dept For connection, the left outer connection can also be used here

Chapter 7 constraints

Meaning: a restriction used to restrict the data in a table. In order to ensure the accuracy and reliability of the data in the table, constraints will be added to some fields when creating a table
Classification: 5 major constraints
- not null: non null, used to ensure that the value of this field cannot be empty, such as name, student number, etc
- Default: default, used to ensure that the field has a default value, such as gender.
- Primary key: primary key, used to ensure that the value of this field is unique and non empty.
  
  A table can only have one primary key, which can be composed of one or more fields. The primary key is the unique identification of each row of data in the table
```
-- Automatic growth:If a column is numeric, use auto_increment Can be done automatically
-- Case: when creating a table, add a primary key constraint and complete the self growth of the primary key
	create table stu(
		id int primary key auto_increment, -- to id Add primary key constraint and self growth
		name varchar(20)
	);
		
-- Add auto growth (add after column)
	ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
	
-- Delete auto growth
	ALTER TABLE stu MODIFY id INT;
	
```
- Unique: unique. It is used to ensure that the value of this field is unique. It can be empty. The unique key can have multiple null values, such as seat number.
- foreigh key: foreign key. It is used to restrict the relationship between two tables and ensure that the value of this field must come from the value of the associated column of the main table. Add a foreign key constraint to the slave table so that the value of a field in the slave table references the value of a field in the master table.
Supplement to foreigh key foreign key knowledge:

① it is used to restrict the relationship between two tables. A field value of the main table is referenced from a field value of the table

② the foreign key column and the referenced column of the main table require the same data type, the same meaning, and no name

③ the referenced column of the main table must be a key (usually a primary key)

④ foreign key syntax:
```
  ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name);
```
Add or delete constraint summary when modifying table (★)

① not empty

Add non empty: alter table table name modify field name field type not null;

Delete non empty: alter table name modify field name field type;

② default

Add default: alter table name modify field name field type default value;

Delete default: alter table name modify field name field type;

③ primary key

Add PRIMARY KEY: column level add: alter table table name modify field name field type PRIMARY KEY

Table level addition: alter table name add primary key (field name);

Delete primary key: alter table name drop primary key;

④ unique

Add UNIQUE: column level add: alter table table name modify field name field type UNIQUE

Table level addition: alter table name add unique (field name);

Delete unique: alter table name drop index index name;

⑤ foreign key

Add foreign key: alter table name add [constraint constraint name] foreign key (slave table field name) references main table (field name referenced by the main table);

Delete foreign key: alter table name drop foreign key constraint name;

Chapter 8 affairs

8.1 basic introduction to transaction

For MySQL database, transaction refers to all SQL operations from the execution of the start transaction command to the execution of the commit or rollback command. If all these SQL operations are successfully executed, execute the commit command to commit the transaction, indicating that the transaction is successfully executed; If any of these SQL operations fails to execute, execute the rollback command to rollback the transaction, indicating that the transaction execution fails, and roll back the database to the state before executing the start transaction command.

8.2 four characteristics of affairs (key points: Interview)

Atomicity: atomicity means that a transaction is an inseparable work unit, and all operations in the transaction either occur or do not occur. Is the core feature of transaction, because transaction itself is defined by atomicity. If all SQL statements in the transaction are executed successfully, execute the commit command to commit the transaction, indicating that the transaction is executed successfully; If any SQL statement in the transaction fails to execute, execute the rollback command to roll back the transaction and roll back the database to the state before executing the start transaction command.
Persistence: persistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it.
Consistency: transactions must transition the database from one consistency state to another** It means that after the transaction is executed, the integrity constraint of the database is not destroyed, and the data state is legal before and after the transaction is executed** The integrity constraints of the database include but are not limited to: entity integrity (for example, the primary key of the row exists and is unique), column integrity (for example, the type, size and length of the field should meet the requirements), foreign key constraints, and user-defined integrity (for example, the sum of the two account balances should remain unchanged before and after transfer)
Isolation: transaction isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the internal operations and data used in a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, it will lead to various concurrency problems. Isolation pursues that transactions do not interfere with each other in the case of concurrency. For simplicity, we only consider the simplest read and write operations (special operations such as reading with lock are not considered temporarily). The discussion of isolation can be divided into two aspects:

Impact of (one transaction) write operation on (another transaction) write operation: lock mechanism ensures isolation

Impact of write operation (one transaction) on read operation (another transaction): MVCC guarantees isolation

First, let's look at the interaction between the write operations of two transactions. Isolation requires that only one transaction can write data at a time. InnoDB ensures this through the locking mechanism. The basic principle of lock mechanism can be summarized as follows: before modifying data, transactions need to obtain corresponding locks; After obtaining the lock, the transaction can modify the data; During the transaction operation, this part of the data is locked. If other transactions need to modify the data, they need to wait for the current transaction to commit or roll back and release the lock.

③ primary key

Add PRIMARY KEY: column level add: alter table table name modify field name field type PRIMARY KEY

Table level addition: alter table name add primary key (field name);

Delete primary key: alter table name drop primary key;

④ unique

Add UNIQUE: column level add: alter table table name modify field name field type UNIQUE

Table level addition: alter table name add unique (field name);

Delete unique: alter table name drop index index name;

⑤ foreign key

Add foreign key: alter table name add [constraint constraint name] foreign key (slave table field name) references main table (field name referenced by the main table);

Delete foreign key: alter table name drop foreign key constraint name;

Chapter 8 affairs

8.1 basic introduction to transaction

8.2 four characteristics of affairs (key points: Interview)

Atomicity: atomicity means that a transaction is an inseparable work unit, and all operations in the transaction either occur or do not occur. Is the core feature of transaction, because transaction itself is defined by atomicity. If all SQL statements in the transaction are executed successfully, execute the commit command to commit the transaction, indicating that the transaction is executed successfully; If any SQL statement in the transaction fails to execute, execute the rollback command to roll back the transaction and roll back the database to the state before executing the start transaction command.
Persistence: persistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it.
Consistency: transactions must transition the database from one consistency state to another** It means that after the transaction is executed, the integrity constraint of the database is not destroyed, and the data state is legal before and after the transaction is executed** The integrity constraints of the database include but are not limited to: entity integrity (for example, the primary key of the row exists and is unique), column integrity (for example, the type, size and length of the field should meet the requirements), foreign key constraints, and user-defined integrity (for example, the sum of the two account balances should remain unchanged before and after transfer)
Isolation: transaction isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the internal operations and data used in a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

Impact of (one transaction) write operation on (another transaction) write operation: lock mechanism ensures isolation

Impact of write operation (one transaction) on read operation (another transaction): MVCC guarantees isolation

Tags: Database MySQL

Posted on Sun, 03 Oct 2021 23:26:26 -0400 by Funk001

Programmer Help

Mysql Basics

Mysql Basics

Chapter I overview of database

1.1 why use database

1.2 related concepts of database

Chapter 2 MySQL database

2.1 MySQL overview

2.2MySQL database service start and stop

2.3MySQL database environment variable configuration

2.4MySQL database client login

2.5MySQL database coding configuration

2.6 common commands in MySQL

Chapter 3 overview of SQL

3.1 what is SQL?

3.2SQL general syntax

3.3 SQL classification

Chapter IV DDL: operation database and table

4.1 operation database: CRUD

4.1.1C(Create): create

4.1.2R(Retrieve): query (English) / R ɪˈ tri ː v / retrieve; recover; retrieve)

4.1.3U(Update): modify

4.1.4D(Delete): delete

4.1.5 using database

4.2 operation table

4.2.1C(Create): create

4.2.2R(Retrieve): query

4.2.3U(Update): modify

4.2.4D(Delete): delete

4.3 common data types of MySQL

Chapter V DML: data in addition, deletion and modification table

5.1 adding data

5.2 deleting data

5.3 modifying data

Chapter 6 data in DQL query table

6.1 syntax of query statement

6.2 data preparation

6.3 basic query

6.4 query criteria

6.5 Sorting Query

6.6 common functions

6.6.1 single line function

6.6.2 grouping function (aggregate function)

6.7 group query

6.8 paging query ★ (widely used in daily life)

6.8.1 limit clause

6.8.2 limit function

6.9 multi table query (connection query)

6.9.1 sql99 standard

6.9.2 sql92 standard (not introduced)

6.10 sub query (★★★)

Chapter 7 constraints

Chapter 8 affairs

8.1 basic introduction to transaction

8.2 four characteristics of affairs (key points: Interview)

Chapter 8 affairs

8.1 basic introduction to transaction

8.2 four characteristics of affairs (key points: Interview)

Hot Tags