Mysql

Related concepts

DB database: a container that holds a set of organized data

DBMS database management system: used to manage data in DB

SQL structured query language: a language used to communicate with DBMS

# Enter exit
mysql -h localhost -P 3306 -u root -proot
exit

Common commands

# View database version
mysql --version
mysql -V
select version()(mysql Server)

# view the database
show databases;

# Enter database
Use Database name;

# View database tables
show tables;
show tables from Database name;

# View the current database
select database();

#Build table
create table info(
	id int,
	name varchar(20)
);

#View table structure
desc Table name;

# ``Single quotation marks distinguish between keywords and fields

grammatical norm

• Mysql is not case sensitive. It is recommended that keywords be capitalized and table names and column names be lowercase
• Commands can wrap and indent, ending with a semicolon
• notes
  • Single line comment: # or -- + space
  • Multiline comment:/**/

DQL(data query language)

Basic query

# Single table query field
select Field 1, field n from surface;

# Single quotation marks can be added when keywords, etc. appear in the alias
select field as alias from surface;
select Field alias from surface;

# duplicate removal
select distinct field from Table name;

# +Number
# +No. connects string and number. First convert the string to number. If it fails, assign it to zero;

# Splices and other splices
select concat(lastname,firstname)as full name from surface

# ifnull
ifnull(field a,value) Representative field a If the value of is blank, it will be displayed value

# isnull
 Judge whether a field or expression is null，If yes, return 1, otherwise return 0

Condition query

# Basic structure
select field from surface where Screening conditions

Filter criteria classification

• Conditional expression: >, <, =, not equal to (! =, < >) > =<=
  • Safety equals < = >: you can judge whether any value is satisfied
• Logical expressions: & &, | and,or,not
• Fuzzy query: like, between and, in, is null, is not null
  • like: use with wildcard;% Any number of characters, including 0 characters_ Any character
  • between and: contains two boundaries
  • In: judge whether the value is an item in the list
  • is null: = and= Cannot determine null

# Query the employee number and department number whose department number is not equal to 90
select employ_id,deparment_id from employee where deparment_id <>90

# Query the name and salary of employees whose salary is between 1w and 2w
select last_name,salary, from employee where salary>=10000 and salary<=20000;

# Query the information of Wang included in the employee name
select * from employee where last_name lile 'king%' or first_name like 'king%'

# Query information about employees with numbers between 100 and 200
select * from employee from where employ_id between 100 and 200;

# Query that the employee department number is it_ PROG,AD_ VP,IT_ Employee information of a certain in MVG
select * from employee from where deparment_id in('IT_PROG','AD_VP','IT_MVG');

# Query employee name and bonus rate without bonus
select last_name,comission_pct from employee where comission_pct is null;

# Safety equals
# Query employee information without bonus
select * from employee where commission_pcy <=> null;
# Query employee information with salary of 12000
select * from employee where salary <=> 12000;

Sort query

order by is usually placed at the back, except for limit

select * from surface [where condition] order by Sort list [desc Descending order||asc Ascending order,default]

# Query the information of employees whose department number is greater than or equal to 90, and sort by employment time
select * from employee where department >=90 order by hiredate asc;

# Display employee information and annual salary by annual salary [sort by expression], [sort by alias]
select *,(12*salary*(1+ifnull(commission,0)) as 'Annual salary' from employee order by 'Annual salary' desc;

# Display employee information in descending order of name length [sort by function]
select * from employee order by length(last_name) desc;

# Query employee information, first sort by employee salary in ascending order, and then sort by employee number in descending order [multi field sort]
select * from employee order by salary asc,employ_id desc;

Common functions

select Function table(Argument) [from surface]

Single-Row Functions

• Character function
• Length (string): get the number of string bytes
• concat(str1,str2): concatenate strings
  • upper(str): uppercase string
  • lower(str): make the string lowercase
  • substr[ing](str,index): intercept the string from index to the end; Index from the beginning
  • substr[ing](str,index,len): get the string with length len starting from index
  • instr(str1,str2): returns the index of the first occurrence of str2 in str1. If not, it returns 0
  • trim(str): remove the space before and after str
  • trim(str1 from str2): remove str1 before and after str2
  • lpad(str1,len,str2): fill str1 with str2 to the specified length; If str1 length is less than len, it is truncated from left to right
  • rpad(str1,len,str2): fill str1 with str2 to the specified length; If str1 length is less than len, it is truncated from right to left
  • replace(str1,str2,str3): replace all str2 appearing in str1 with str3
• Mathematical function
• round(val): round val; The result of round(1.567,2) is 1.57
• ceil(val): returns the smallest integer > = val
• floor(val): returns the maximum integer of < = val
  • truncate(val,len): reserved len bit val
    • mod(a,b): returns a%b; Essence of remainder a-a/b*b
      • rand(): randomly obtain decimals between 0 and 1
• Date function
  • now(): returns the current system date and time
  • Current date (): returns the current system date
    • curtime(): returns the current time, excluding the date
    • year(), month(), day(): interception date
    • monthname(): get the English version of the month
    • str_to_date(str, '% m-%d-%y'): converts the date format string to the specified format date
    • date_format(str, '% m-%d-%y'): converts the date to a string in the specified format
    • datediff(date1,date2): returns the number of days from date1 to date2
• Other functions
  • version(): view the database version
  • database(): current database
  • user(): current user
  • password(str): returns the value after encrypting str
  • md5(str): returns the value after encrypting str md5
• Process control function
  • If (expression, val1,val2): if the expression is true, val1 will be displayed; otherwise, val2 will be displayed
  • case expression when constant 1 then displayed value 1 when constant 2 then displayed value 2 esle constant n end;
  • case when condition 1 then displayed value 1 when condition 2 then displayed value 2... else displayed value n end

# Displays the employee's name, starting with uppercase letters and ending with lowercase letters_ Splicing
select concat(upper(subtring(last_name,1,1)),'_',lower(substring(last_naem,2))) from employee;

# Query the employee's salary. If the department number is 30, 1.1 times the salary will be displayed, if the department number is 40, 1.2 times the salary will be displayed, and other departments will be the original salary
select salary as Original salary 
case department_id when 30 then 1.1*salary 
when 40 then 1.2*salary 
else salary
end as New salary
from employee;

# Query the salary of employees. If the salary is > 20000, A will be displayed, > 15000, B will be displayed, > 10000, C will be displayed, otherwise D will be displayed
select salary,case when salary>=20000 then 'A',when salary>=15000
then 'B',when salary>10000 then 'C',else 'D' end as Wage scale from employee;

Grouping function

Commonly used in statistics, also known as statistical function, aggregate function and group function; The result returned by grouping query is 1; Grouping functions ignore null

• sum(): sum; Ignore null; Can be used with distinct
• avg(): average; Ignore null; Can be used with distinct
• max(): maximum value; Ignore null; Can be used with distinct
• min(): minimum value; Ignore null; Can be used with distinct
• count(): count the number of non empty values; Can be used with distinct

# Basic structure
select max(salary),min(salary) from employee;

# Count rows
select count(*)/count(1)/count(field) from employee;
myisam Under the engine, conut(*)Highest efficiency
innodb Under the engine, count(*)and count(1)Almost as efficient as count(field)kuai

# Query the difference days between the maximum enrollment time and the minimum enrollment time in the employee table
select datediff(max(hiberate)-min(hiberate)) from employee;

Grouping query

Having filters the results after grouping. where filters the original employ ment table; Grouping function in having

# Basic structure
select column,column(Grouping column) from surface [where Screening conditions] group by Grouping column [order by column];

# Query the maximum wage of each type of work
select max(salary),job_id from employee group by job_id;

# Query the average salary of each department with the letter a in the mailbox
select avg(salary),department_id from employee where email like'%a%' group by department_id

/* Screening conditions */
# The number of employees in that department is greater than 2
select count(*) as Number of people,department_id from employee group by department_id having Number of people>2 ;

# Query the job number and maximum salary of each type of work with bonus and maximum salary greater than 10000
select job_id,max(salary) from employee where commission_pct is not null group by job_id having max(salary)>10000;

# Query the minimum wage of each leader with leader number > 102, the leader number with leader number > 5000 and the minimum wage
select manage_id,min(salary) where manage_id>102 group by manage_id having min(salary)>5000 ;

/* Group by expression or function */
# Group employees by name length, query the number of employees in each group, and display the number of employees > 5
select count(*),length(last_name) from employee group by length(last_name) having count(*)>5;

/* Group by multiple fields */
# Query the average salary of employees in each department and type of work
select avg(salary),department_id,job_id from employee group by department_id,job_id;

/* Join sort */
# The average salary of employees in each department and type of work is arranged in descending order
select avg(salary),department_id,job_id from employee group by department_id,job_id order by desc;

join query

Classification by age:

- sqsl92 Standard: support internal connection
- sql99 Standard: support internal connection+External connection(Left outer, right outer)+Cross connect

Classification by function:

• Inner connection
  • Equivalent connection
    • Non equivalent connection
      • Self connection
• External connection
  • Left outer connection
    • Right outer connection
      • All external connections (not supported by mysql)
  • Cross connect

sql92

Equivalent connection: the result is the intersection of multiple tables

# Query the male name corresponding to the female student select boyname from boys,girls where girls.boyid= boy.id/* Alias the table; The original table name cannot be used after aliasing*/# Query the name of boys corresponding to girls select boyname from boys a,girls b where a.boyid= b.id/* Add screening conditions */# Query employee name and department name with bonus select last_name,department_name,commission_pct from emplpyee a,department b where a.department_id=b.deparment_id and commission_pct is not null;/* Plus grouping */# Query the number of departments in each city select conut(*) as number, city from location l,department d where d.location_id = l.location_id group by city/* Add sort */# Query the name of each type of work and the number of employees, and arrange them in descending order by the number of employees. select count(*) number of employees, job_name from job j,employee e where j.job_id=e.job_id group by job_name order by number desc;

Non equivalent connection

# Query employee's salary and salary level select salary, salary_ grade from employee e,job_ grade g where salary between min_ salary and max_ salary;

Self connection

# Query employee name and superior name select a.last_name,b.last_name from employee a,employee b where a.manage_id = b.employ_id ;

sql99

# Basic structure select query list from table 1 alias [connection type] join table 2 alias on connection condition [where filter condition] [group by] [having] [order by]

line type

• inner: the result is the intersection of multiple tables
  • Equivalent connection
    • Non equivalent connection
    • Self connection
• External connection: external connection result = internal connection + the master table has records that the slave table does not have
  • left outer join
  • right outer join
  • full outer join
  • Cross join: cross join

Internal connection: equivalent connection, non equivalent connection, self connection

# Query employee name and department name
select last_name,department_name from employee e inner join deparment d on e.department_id = d.department_id 

# Query the employee name and type of work included e in the name
select last_name,job_name from employee e inner join job j on j.job_id = e.job_id
where last_name like'%e%';

# Query the Department name and number of employees in that department whose number of employees is more than 3, in descending order
select department_name,count(*) from employee e inner join department d on e.departmenr_id = d.department_id group by d.department_name having count(*)>3 order by count(*) desc;

# When connecting multiple tables, the intermediate table needs to be connected with other tables
# Inner can be omitted

# Query the number of each salary level and descending by salary level
select salary_grade,count(*) from employee e inner join salary_grade s on salary between min_sal and max_sal group by salary_grade order by salary_grade desc;

# Query employee name and superior name
select a.last_name,b.last_name from employee a inner join employee b on a.manage_id = b.employee_id;

External connection: left outer, right outer, all outer; Query a table without a table

# External connection result = internal connection + the master table has records that the slave table does not have
# The left side of the left join is the main table
# right join the main table is on the right

# Query the girl name whose boyfriend is not in the boy list
select girl_name from girl g left out join boy b on g.boy_id = b.boy_id where b.boy_id is null; 

# Query the name of the Department without employees
select d.*,e.employee_id from department d left out join employee e on d.department_id = e.department_id where e.employee_id is null;

# Query the boyfriend information of girls with number > 3. If not, use null
select b.* from boy b right out join girl g on g.boy_id = b.boy_id where girl_id>3; 

# There is no department in that city
select city from location l left out join department d on l.location_id = d.location_id where d.department_id is null;

# Query the employee information of the department named SAL or IT
select e.* from employee e left out join department d on d.department_id = e.department_id where d.department_name='SAL' OR d.department_name='IT';

Cross connect: Cartesian product sets

Subquery

select statements that appear in other statements are called subqueries or intra queries; External query statements are called primary or external queries

characteristic

• Subqueries are enclosed in parentheses
• The subquery is placed to the right of the condition
• Scalar subqueries are generally used with single line operators; Column subqueries are generally used with multi row operators
• Execution sequence, from child to master

classification

• By location:
  • After select: only scalar subqueries are supported
  • After from: support table sub query
  • After where or having: except table subquery
  • After exists (related sub query): table sub query; Exists (expression). If the expression has a return value, it returns 1, and vice versa; Different from other sub queries, it executes the main query first and then the sub query
• According to the number of rows and columns of the result set:
  • Scalar subquery: there is only one row and one column in the result set
  • Column subquery: there are one column and multiple rows in the result set
  • Row sub query: there are one row and multiple columns in the result set
  • Table sub query: generally multi row and multi column

scalar subquery

# Whose salary is higher than abel
select * from employee where salary>(select salary from employee where last_name='abel');

# Employee information with the lowest salary in the company
select * from employee where salary=(select min(salary) from employee);

# Query the Department id and the minimum wage of the Department whose minimum wage is greater than the minimum wage of No. 50 department
select department_id,min(salary) from employee having min(salary)>(select min(salary) from employee where department_id=50)

Column subquery

With multiline operators:

• in()/not in(): equal to / not equal to any one in the list
• <, >, = any()/some(): compare with a value of the subquery; There is only one
• all(): compare with all values returned by subquery; Only when everything is satisfied

# Return location_ The ID is the name of all employees in the 1400 or 1700 department
select last_name from employee where department_id in (select department from department where location_id in (1400,1700);

# Return to jobs in other departments_ 'it' with ID_ Prog 'employee number, name and job of any employee with low salary in the Department_ ID and salary
select last_name,employee_id,job_id,salary from employee where salary>( select max(salary) from employee where job_id='IT_PROG' )and job_id <> 'IT_PROG';
select last_name,employee_id,job_id,salary from employee where salary < any(select salary from employee where job_id='IT_PROG') and job_id <> 'IT_PROG';

Row subquery

# Query the information of the employee with the lowest employee number and the highest salary [after where]
select * from employee where(employee_id,salary)=(select min(employee_id),max(salary) from employee) //Row subquery
select * from employee where employee_id=(select min(employee_id) from employee) and salary=(select max(salary) from employee) //Column subquery

# Query the number of employees in each department [after select]
select d.*,(select conut(*) from employee e where e.department_id=d.department_id) from department d;

# Query the salary grade of the average salary of each department [after from]
select avg.*,salary_grade from (select avg(salary) average wage,department_id from employee e group by department_id)avg,job_grade j where avg.average wage between min_sal and max_sal;

# Query the Department name with employees [after exists]
select department_name from department d where exists(select * from employee e where d.department = e.department);

# Query the information of boyfriend without girlfriend
select * from boy b where not exists(select * from girl g where b.boy_id=g.boy_id);

# Query the employee number, name and salary of employees in each department whose salary is higher than the average salary of the Department
select employee_id,last_name,salary from employee e,(select avg(salary) pinjun,department_id from employee group by department_id)avg where e.department_id =avg.department_id and salary>avg.pinjun

# Query the location of the Department_ Employee number of the employee working in the Department with ID 1700
select employee_id from employee where department in ( select distinct department_id from department d where location_id=1700 )

# Query the name of the employee with the highest salary and ask first_name and last_name displays a column, which is listed as last name first name
select concat(last_name,'.',first_name) surname.name where salary = (select max(salary) from employee)

Paging query

Application scenario: for the data to be displayed, one page is not fully displayed, and the sql request is submitted in pages

Syntax: select query list from table [...] limit offset (starting index, starting from 0), size (number of displayed entries)

Features: put at the end, and the execution order is also at the end

Formula: number of pages to display, number of entries per page, size; select list from table limit (page-1)*size,size;

# Query the first five employee information
select * from employee limit 0,5;select * from employee limit 5;

# Query the information of employees with bonus and display the top 10 employees with higher salary
select * from employee where commission_pct is not null order by salary desc limit 10;

# Query the information of the Department with the lowest average wage
select * from department where department in( select avg(salary),department_id from employee group by department_id order by avg(salary) limit 0,1 );

# Query the job information with the highest average salary select * from job where job_id in( select avg(salary),job_id from employee group by job_id order by avg(salary) desc limit 1);

# Query the departments whose average salary is higher than the average salary of the company
select department_id from employee group by department_id having avg(salary)>(select avg(salary) from employee);

# Query the details of all manager s in the company 
select * from employee where employee_id in(select distinct manage_id from employee);

Joint query

Union: combines the results of multiple query statements into one result

characteristic:

• The number of columns queried should be the same;
• The query type and order of multiple query statements should be consistent;
• Union will automatically remove duplicates, and union all can contain duplicates

# Query employee information with department number > 90 or email containing a
select * from employee where email like'%a%' or select * from employee where department_id >90;
select * from employee where email like'%a%' union select * from employee where department_id >90;

Execution sequence

DML(data managlation language)

Data operation language:

- insert insert- modify update- delete delete

insert

Syntax: insert into table (column name) values | insert into table set (column=value); The former supports inserting multiple rows; The former supports sub query;

Requirement: the type of inserted value is compatible with the type of column; The number of columns and the number of values should be one-to-one and equal

# Insert girl
insert into girl(id,name,sex,borndate,phone,boyfriend_id) value(13,'Force topology','female','1998-9-3','18988899888',4);

# Insert girl
insert into girl set id=14,name='wenzd',phone='999';

# Nullable columns may not write out their columns and values
insert into girl(id,name,sex) value(13,'Li Baoguo','female');

# Nullable columns write out column values and fill them with null s
insert into girl(id,name,sex,borndate,phone,boyfriend_id) value(13,'Force topology', 'female', null, null, 4);

Modify update

• Modification sheet
  • Syntax: update table set column = new value... where filter criteria;
• Modify multiple tables
  • Syntax: update table set column = new value... where connection condition and filter condition;

# Change the phone number of Tang in gril to 113
update gril set phone = 113 where name like'Tang%';

Delete drop

Method 1: delete from table where filter criteria (delete the entire table if there are no criteria) [limit n]

Method 2: truncate table (delete all data in the table without filter conditions) is more efficient than delete from table

The difference between delete and truncate

1. Filter criteria can be added to delete, but not to truncate
2. truncate high efficiency
3. delete after deleting the auto increment data, it will start from the breakpoint after inserting the data; truncate will start from the beginning
4. delete returns the number of affected rows, while truncate does not return a value
5. truncate cannot be rolled back. delete can be rolled back

# Delete the girl whose mobile number ends with 9
delete from gril where phone like '%9';# delete table from [table] where ;

DDL(data define language)

Data definition language; Creation, modification and deletion of library; Create, modify and delete tables; create,alter,drop

library

# Create Library create database if not exists Library name;# Modify the character set of the library alter database Library name character set gbk/utf-8;# Delete the drop database if exists library name;

surface

Syntax: alter table name add|drop|modify|change column name [column type constraint]

# Create table
create table Table name(
	Column name the type of the column[length,constraint],
	...
	Column name the type of the column[length,constraint]
);
/* Modify table */
# Modify column name
alter table Table name change column Original column name new column name type;

# Modify the type or constraint of the column
alter table Table name modify column New type of column name;

# Add a new column and put it after the first column or the specified column
alter table Table name add column Column name type[fisrt|after Listing];

# Delete column
alter table Table name drop column  Listing;

# rename table 
alter table Original table name rename to New table name;

# Delete table
drop table if exists Table name

# Replication of tables
# Copy table structure
create table New table name like Original table name

# Copy table structure + data
create table New table name select * from Original table name

# Copy partial structure
create table New table name select Listing... from Source table name where 0(bool Value is false that will do)

# Copy structure + partial data
create table New table name select * from Original table name where Screening conditions

Common data types

• Numerical type
  • integer
  • decimal
    • Fixed point number
    • Floating point number
• character
  • char: short text
  • text, blob: long binary
  • enum: enumeration: take one value at a time
  • set: set: multiple values can be taken at a time

Principle: the simpler the type, the better

integer

The scope is divided into signed and unsigned cases

characteristic:

- If the inserted value exceeds the range, an error will be reported out of range Exception, and then the nearest valid value is displayed
- Integer is unsigned by default. If you want to set the symbol, add unsigned- If the length is not set, there will be a default length; The set length represents the maximum width of the display. If the data length is not enough, it will be filled with 0 on the left, but it must be matched zerofill When used, the default is unsigned integer

tinyint  1 Byte range 8-bit binary
smallint  2 byte  mediumint  3 byte
int,Integer  4 byte
bigint  8 byte 

# How to set with and without symbols
create table testTable(	
t1 int, //Unsigned	
t2 int unsigned, //Signed	
t3 int(8) zeorfill);

decimal

• The precision of fixed-point number is higher than that of floating-point number
• D represents the number of digits after the decimal point, and M represents the sum of integer digits and decimal digits
• MD can be omitted. decimal defaults to 10,0

characteristic:

float(M,D)  4 byte double(M,D)  8 byte dec(M,D)  M+2 byte decimal(M,D)  M+2 byte

character

Differences between char and varchar

char(M): fixed length character. M can be omitted. The default value is 1. It is more space-consuming and efficient

varchar(M): variable length character. M cannot be omitted, which saves space and is inefficient

char,varchar Shorter text
text,blob Larger binary

# enumeration
create tabel test(
	t1 enum('female','male')
);
insert into test values('female');

# aggregate
create tabel test(
	t1 set('a','b','c')
);
insert into test values('a,b,c');

Date type

The difference between timestamp and datetime

• The timestamp range is small (sometime from 1970 010108 0001 to 2038); datetime range (1000-1-1 to 9999-12-31)

• timestamp is related to the actual time zone and can better reflect the actual time zone; datetime can only reflect the local time zone at the time of insertion

• The timestamp attribute is greatly affected by the mysql version and sqlmode

date  4 Byte date
datetime  8 Byte date+time
timestamp  4 Byte date+time
time  3 Byte time year  1 Byte year

Common constraints

A restriction used to restrict the data in a table, so as to ensure the accuracy and reliability of the data in the table

Classification:

- not null: Non empty, field cannot be empty
- default: Set default values for fields
- primary key: Primary key, which is used to ensure the uniqueness of fields
- unique: Unique. It is used to ensure the uniqueness of field values. It can be blank
- check: Check constraints,[mysql Not supported in]
- foreign key: Foreign key is used to restrict the relationship between two tables and ensure that the value of this field must come from the value of the main table; Add a foreign key constraint to the slave table to introduce the value of a column in the main table

When to add constraints

• When creating a table
• When modifying a table

Constraint classification

- Column level constraint: foreign key constraint has no effect; Constraint names are not supported- Table level constraints: all but non empty and default constraints are supported; Constraint names can be used

Primary key and unique difference

Ensure uniqueness and combination (not recommended); The unique key can be blank, and the primary key cannot be blank; A table can have multiple unique keys and only one primary key

Foreign key

• Foreign keys are required to be set in the slave table
• The foreign key column type of the slave table shall be consistent or compatible with the related columns of the master table, and the name is not required
• The associated columns of foreign keys are generally primary keys or unique columns
• When inserting data, insert the primary table first. When inserting the secondary table, the reverse is true when deleting

# Column level constraint
create teble stu(
	id int primary key,
	name varchar(20) not null unique, //Multiple constraints
	age int default 18	
);

# Table level constraint
create teble stu(
	id int ,
	name varchar(20) ,
	age int ,
    [constraint pk] primary key(id),
    [constraint notNull] not null(name),
    [constraint df] default(age) 10
);

# General writing method of table level constraints
create table stu(
	id int primary key,
	name varchar(20) not null,
	age int default 18,
	seat int union,
	major int,
	[constraint fk] foreign key(major_id) references major(id)
);

# Add constraints when modifying tables
create teble stu(
	id int ,
	name varchar(20),
	age int default 18,
	seat int union,
	majorid int
);
alter table stu modify column name varchar(20) not null //Add non empty constraint
alter table stu modify column id int primary key  //Add primary key 
alter table stu modify column major int foreign key(majorid) reference major(id);//Add foreign key
alter table stu drop column name varchar(20) [null] //Delete non empty constraints
alter table stu drop column age int; //Delete default constraint
alter table stu drop primary key; //Delete primary key
alter table stu drop unique

Identity column

It is also called self growing column. There is no need to insert values manually. The system provides default sequence values

characteristic:

• The identification column must be matched with the key column (unique or primary key)
• A table can only have one self incrementing column
• The type of identification column can only be numeric

# Specify self growing columns
create teble stu(	
id int primary key auto_increment);

# Specifies the auto increment step size
set auto_increment_increment=3

# Set identity column when modifying table
alter table surface modify column Column type auto_increment;

# Delete identity column when modifying table
alter table surface modify column Column type;

TCL(transaction control language)

thing

Object control language;

Things: one or a group of sql statements form an execution unit, which either executes all or does not execute all

Storage engine: the data in mysql is stored in files or memory with different technologies

Attributes of things (acid)

1. Atomicity: things are an inseparable unit. Things either happen or don't happen
2. Consistency: the data should be consistent before and after things happen
3. Isolation: the execution of things cannot be disturbed by other things; Concurrent things cannot interfere with each other
4. Persistence: once something is submitted, it will permanently change the data in the database

Conditions for Concurrency: when multiple things operate the same row of data in the same table of a database at the same time

Concurrency problem

1. Dirty reading: transaction A reads the data updated but not submitted by transaction B, and then B rolls back, so what A reads is dirty data
2. Non repeatable reading: thing A reads the data, and thing B updates and submits the data when A reads. When A reads the data again, the value changes
3. Unreal reading: thing A reads A field from the table, and B inserts A new row in the table. If A reads the same table again, there will be several more rows; It's like an illusion

Non repeatable reading focuses on modification, while phantom reading focuses on adding or deleting. To solve the problem of non repeatable reading, you only need to lock the rows that meet the conditions, and to solve the phantom reading, you need to lock the table

Isolation level: the degree of isolation between a thing and other things is called isolation level

classification

• Implicit things: things have no obvious marks of beginning and end; Such as insert, update and delete
• Show things: things have obvious open and end marks, and automatic submission is disabled first (set autocommit=0)

Creation of things

# Show things 
set autocommit=0; //Open things
[start transaction]
sql //Transaction logic code (insert, update and delete are supported in sql)
...
//End things
commit;//Submit things 𞓜 rollback// rollback transaction 

Database isolation level

Database transaction isolation level

1. Read uncommitted: allow things to read uncommitted tables of other things. Dirty reads, unrepeatable reads and unreal reads will appear
2. Read committed: only changes submitted by other things are allowed to be read, which can avoid dirty reading; Unreadable and unreadable
3. Repeatable read [mysql default]: ensure that a transaction can read the same value from a field multiple times. During the duration of the transaction, other transactions are prohibited from modifying the field; It can avoid dirty reading and non repeatable reading; Unreal reading is inevitable
4. Serializable: ensure that the same row of things can be read from a table, and prohibit other things from adding, deleting or modifying this field during the duration of things; It can avoid all concurrency problems and low efficiency

# View current isolation level
select @@tx_isolation;

# Set the isolation level of the current mysql connection
set transaction isolation level read committed;

# Set the global isolation level of the database system
set global transaction isolation level read commited;

savepoint

set autocommit=0;
start transaction;
sql1;
savepoint a; //Save node
sql2 ;
rollback a; //Rollback to savepoint
//After rollback, sql2 will not execute, but sql1 will execute 

view

Definition: a virtual table. The data in rows and columns comes from the tables used in the query of custom views, and is dynamically generated when using views. Only sql code is saved, and the results are not saved

Application scenario: the same query results are used in multiple places, and the sql used in the query results is complex

Views with the following characteristics are not allowed to be updated:

• sql statements containing the following keywords: grouping function, distinct, group by, having, union, union all
• Constant view
• select contains subqueries
• join
• from a view that cannot be updated
• The subquery of the where clause references the table in the from clause

Differences between tables and views

• Keywords created are different; create table，create view
• The actual space occupied by the table is large and the view space is small
• Generally, views can only be queried, and tables can be added, deleted or modified

# Create view
create view as select ...

# Modify view
create or replace view as select ...

# Delete view
drop view View 1,View 2...

# view a chart
desc View name;
show creare view View name;

# View update
// Adding, deleting and changing view data will affect the data in the original table
insert into View column values();
update View name set column=value where
drop from View name where

variable

classification

• System variable: provided by the system and belongs to the server level
  • Global variables:
  • Session variable: the scope is only valid for the current session (connection)
• User defined variables: variables are user-defined, declared, assigned, and used
  • User variable: valid for the current session
  • Local variable: the scope is only in the begin end where it is defined; Can only be used in the first sentence of begin end

# View system variables (default session)
show global||[session] variables;

# View system variables that meet the criteria
show  global||[session] variables like'%char%'

# View a specified system variable
select @@global||[session].System variable name;

# Modify system variable value
set global||[session].System variable name=value

# Declare and initialize user variables
set @Variable name=value; || set @Variable name:=value; || select set @Variable name:=value;

# Assign values to user variables
set @Variable name=:value; || select field into Variable name from surface; 

# Use user variables
select @Variable name;

# Declare local variables
declare Variable name type; || declare Variable name type default value;

# assignment
set Local variable name=value; || set Local variable name:=value || select set @Variable name:=value; || select field into Variable name from surface; 

# use
select Variable name;

Stored procedures and functions

Benefits: improve code reusability; Simplify operation; Reduce the times of compiling and connecting to the database, and improve the efficiency;

Stored procedure: a set of pre compiled sql statements to understand batch processing statements (suitable for batch insertion and update)

Function: there is only one return value (suitable for processing data and returning a result)

characteristic:

• If the stored procedure has only one sentence, begin end can be omitted; The end of the stored procedure can be reset using delimiter
• Each sql in the stored procedure must be semicoloned
• The parameter list in the stored procedure consists of three parts: parameter mode parameter name parameter type

Parameter mode

• In: this parameter can be used as input, that is, this parameter requires the caller to pass in a value
• out: this parameter can be used as output, that is, it can be used as return value
• inout: this parameter can be used as input or output

stored procedure

# establish
create procedure Stored procedure name(parameter list)
begin
	Process body
end

# call
call Stored procedure name(Argument);

# Empty parameter list
//establish
delimiter $
create procedure myp1()
begin
	insert into surface (column) values();
end $
//call
call myp1()$

# in parameter
//Query the corresponding boyfriend information according to the girl's name
create procedure myp2(in grilName varchar(20))
begin
	select b.* from boy b,gril g where b.gril_frinde_id=g.id;
end $
call myp2('nihao')$

//Create a stored procedure to judge whether the login is successful
create procedure myp3(in userName varchar(20),in passWord varchar(20))
begin
	declare result int default 0; //Declare local variables
	
	select conut(*) into result //Assign local variable
	from user where username=userName and password=passWord;
	
	select if(result>0,'success','Account error');
end $
call myp3('nihao','132456')$

# out parameter
//Query the corresponding boyfriend information according to the girl's name
set @bname;
create procedure myp2(in grilName varchar(20),out boyName varchar(20))
begin
	select b.name into boyName
    from boy b,gril g where b.gril_frinde_id=g.id;
end $

call myp2('nihao',@bname)$;
select @bname;

# Delete stored procedure
drop procedure Stored procedure name

function

characteristic:

• The parameter list includes parameter name and parameter type
• There must be a return statement in the function body. If there is no return statement, an error will be reported
• When the function has only one sentence, begin end can be omitted; Use the delimiter statement to set the end tag

# Create function
create function Function name(parameter list) returns Return type
begin
	Function body
end

# call
select Function name(parameter list)

# View function
show create function Function name;

# Delete function
drop function Function name;

Process control structure

Sequential structure: the program is executed from top to bottom

Branch structure: the program selects one of two or more paths to execute

Loop structure: the program repeatedly executes a piece of code when certain conditions are met

Branching structure

# if() function

Syntax: if(Expression 1,Statement 1,Statement 2);
Execution order: if expression 1 is true, the value of statement 1 is returned; otherwise, the value of statement 2 is returned

# case() function

Case 1: similar java of switch，It is generally used for equivalence judgment
 Syntax:	case expression
		when Value to be judged then Returned value 1 or statement 1;
		when Value to be judged then Returned value 2 or statement 2;
		...
		else  Returned value n Or statement n;
        end

Case 2: similar java Multiplicity of if Statement, which is generally used to realize interval judgment
 Syntax: case expression
		when Condition 1 then Returned value 1 or statement 1;
		when Condition 2 then Returned value 2 or statement 2;
		...
		else  Returned value n Or statement n;
        end
        
Execution order: if when If the value in meets the conditions, the corresponding then The following statement, and then end case
 If none is satisfied, execute else Statements in, if else Returns if omitted null

# Create A stored procedure to display the grade according to the passed in grade, (90-100 shows A, 80-90 shows B...)
create procedure pname(in score int)	
begin
    case
	when score>90 and score<=100 then select 'A';
	when score>80 and score<=90 then select 'B';
	when score>70 and score<=80 then select 'C';
	when score>60 and score<=70 then select 'D';
	else select 'E';
	end case;
end

# if structure

Function: realize multiple branches
 Syntax:
	if Condition 1 then Statement 1;
	else if Condition 2 then Statement 2;
	...
	[else sentence n];
Application: in begin end in

# The grade is displayed according to the incoming grade, (90-100 shows A, 80-90 shows B...)
create function fname(score int) returns char
begin
	if score>90 and score<=100 then select 'A';
	else if score>80 and score<=90 then select 'B';
	else if score>70 and score<=80 then select 'C';
	else if score>60 and score<=70 then select 'D';
	else select 'E';
end

Cyclic structure

• while
• loop
• repeat

characteristic:

• iterate is similar to continue, ending the current cycle and continuing the next cycle
• leave is similar to break, jumping out of the current cycle

# while
[label]while Cycle condition do
	Circulatory body;
end while[label];

# Loop (simulated dead loop)
[label]loop
	Circulatory body
end loop[label]

# repeat
[label]repeat
	Circulatory body
until Conditions for ending the cycle
end repeat[label];

# Batch insert data into admin table
create procedure while1(in count int) # Define the cyclic variable count stored procedure while1
begin
		declare i int default 1;
		while i<= count do # A: while I < = count do
			insert into admin(username, 'password') values(concat('tom', i), 'tom');
			set i = i + 1
		end while # end while a
		
# Stop when batch inserting data into admin table and the number of times is greater than 20
create procedure while2(in count int) # Define the loop variable count stored procedure while 2
begin
		declare i int default 1;
		a: while i<= count do
			insert into admin(username, 'password') values(concat('tom', i), 'tom');
			if i >= 20 then leave a;
			end if;
			set i = i + 1
		end while a


# Call stored procedure while 1
call while(30)