Introduction to MySQL

Writing in the front

• This section describes the basics of MySQL. The core is DQL, and the rest is for easy reference.
• The table used in the case is as follows, and you can help yourself if necessary.
• Link: pan.baidu.com/s/1ss17w6BU… Extraction code: 2PFH
• Code are pro test effective, the error of the place please correct, or private chat I correct.

1. Database related concepts

• Benefits of databases:
  • Persist data locally
  • Can realize structured query, convenient management
• DB: database, a container that holds an organized set of data
• DBMS: Database management system, also known as database software (product), used to manage the data in the DATABASE
• SQL: Structured query Language, used to communicate with DBMS

Ii. Characteristics of database data storage

• You put data into tables, and tables into libraries
• A database can have multiple tables, and each table has a name that identifies it. Table names are unique.
• Tables have features that define how data is stored in them, similar to the design of “classes” in Java.
• A table consists of columns, also known as fields. All tables are composed of one or more columns, each of which is similar to the “properties” in Java
• The data in the table is stored in rows, each row similar to an object in Java.

Introduction and installation of the mysql product

3.1_Starting and Stopping the mysql service

• Method 1: Computer – right click Manage – Services
• Method 2: Run this command as an administrator
  • Net Start Service name (start service)
  • Net Stop Service name (Stop service)

3.2_Logging In and out of the mysql service

• Method 1: Use the mysql Client (only root user (Command Line Client))
• Method 2: Use the Windows client
  • Mysql [-h host name -p port number] -u user name -p password
  • Exit: Exit or CTRL +C

3.3_Common mysql commands

1. Check all databases show databases. 2. Open the specified database use database name. 3. 4. Check all tables in other libraries. 5. Create table create table name (column name column type, column name column type...) ; 6. Query table structure desc table name. 7. Check the server version. Method 1: Log in to the mysql server and select version(). Mysql --version or mysql --V is not logged in to mysql serverCopy the code

3.4_Syntax specification of mysql

• It is not case sensitive, but it is recommended that the keyword be uppercase and the table name and column name be lowercase
• It is best to end each command with a semicolon
• Each command can be indented or wrapped as required
• annotation
  • Single-line comment: # Comment text
  • Single line comment: — Comment text
  • Multi-line comments: /* Comments text */

Four _SQL language overall classification

• Data Query Language (DQL) : Data Query Language eg: select
• Data Manipulate Language (DML) : Data manipulation Language eg: Insert, Update, and DELETE
• Data Define Languge (DDL) : Data definition language eg: Create, DROP, alter
• TCL (Transaction Control Language) : Transaction Control Language eg: COMMIT, rollback

Five _DQL language

Step 1: Basic query

• SELECT * FROM table name;

System.out.println(something to print); Select * from table_name where table_name = 1; select * from table_name where table_name = 1; (1) : as (2) : space eg: select last_name as surname, first_name as name from employees; The keyword is: DISTINCT Example: Query the ids of all departments involved in the employee table. Eg: select distinct department_id from employees; Mysql select 100+90; mysql select 100+90; Select '123'+90; select '123'+90; Select 'John '+90; select' John '+90; 4: select null+10; As long as either party is NULL, the result is definitely NULLCopy the code

Step 2: Conditional query

• Conditional query: Filter the data of the original table according to the conditions to query the desired data
• Grammar: the select expression to query field | | | constant value function from table where conditions;

Salary >10000 conditional operator: > < >= <= =! = < > 2, logical expressions example: salary > 10000 && salary < 20000 logical operators: and (&) : if the two conditions at the same time, the result is true, otherwise is false or (| |) : Return true if either condition is true, false otherwise not(!) : False if the condition is true, otherwise true The query department number is not between 90 and 110, Select * from employees where not(department_id >= 90 and department_id<=110) or salary>15000; Use the following wildcard characters: ① % Any multiple characters, including 0 characters ② _ Any single character example: Select last_name from employees where last_name like '_$_%' $'; select last_name from employees where last_name like '_$_%' $'; Note: Include critical values and do not swap the order of the two critical values. Select * from employees where employee_id between 100 and 120; select * from employees where employee_id between 100 and 120; 3.3 IN Note: ① The value types in the IN list must be consistent or compatible. ② The in list does not support wildcards. Query the employee's job id is IT_PROG, AD_VP, Select last_name, job_id from employees where job_id in (IT_PROG,AD_VP,AD_PRES); select job_id from employees where job_id in (IT_PROG,AD_VP,AD_PRES); 3.4 is null = or <> cannot be used to check for null values is null or is not null Null invalid then we can use ifnull(x, value) to operate examples: Query the name and bonus rate of employees with bonuses, Select last_name,commission_pct from employees where the commission_pct is not null Select last_name from employees where commission_pct <=> null Is NULL Identifies only null values, which is highly readable. You are advised to use <=> to identify both NULL values and common values, which is less readableCopy the code

Step 3: Sort the query

• Grammar: select order to query the from table where conditions by sorting the field expression | | | function is an alias of the asc | desc.

  Select * from 'asC' where 'asC' indicates' ascending 'and' desc 'indicates' descending'. Select * from 'asC' where 'asC' indicates' ascending 'and' descending '. Example: 2.1 Single field add filter criteria in sort eg: Select * from employees where department_id >=90 order by employee_id desc; select * from employees where department_id >=90 order by employee_id desc; 2.2 Querying EG by Expression: Select *, SALARY *12*(1+ifnull(commission_pct,0)) as high salary from employees order by high salary desc; 2.3 Sort by function eg: Select length(last_name) L,last_name from employees order by L desc; 2.4 Sorting by Multiple Fields eg: Query employee information by descending order of salary and ascending order of Employee_id SELECT * from employees order by salary DESC,employee_id ASC;Copy the code

Advanced 4: Common functions

• Advantages: Java-like approach that hides implementation details and improves code reuse.
• Call: select function name (argument list) [from table];
• Classification: single line function and grouping function (used for statistics, also called statistical function, aggregate function, group function)

4.1 Single-line functions

• Character function

  • Concat stitching

    Select concat(last_name,'_','first_name') name from employees; If you simply concatenate two strings, do not use the intermediate concatenateCopy the code

  • Substr (substring) intercepts a substring

    Select substr(' outstr ',5) out_put; Select substr(' 1 ',3,4) from '1'; Note: the second parameter is a number, and if the number exceeds it, the result will be the last digitCopy the code

  • Upper is converted to uppercase and lower to lowercase

    Select concat(super(last_name),lower(first_name)) name from employees;Copy the code

  • Trim removes specified Spaces and characters, ltrim removes left Spaces, and rtrim removes right Spaces

    select trim("   le boy   ") out_put; # le boy 
    Copy the code

  • Replace, all of them

    Select replace(' You know what, yeah! ',',' bar ') as out_put; # You know, right!Copy the code

  • Lpad fills left, rpad fills right

    Select lpad(' why ',2,'@') out_put; Select lpad(' why ',5,'@') as out_put; Select rpad(' why ',5,'@') out_put; # Why @@Copy the code

  • Instr returns the index of the first occurrence of the substring

    Select instr(' I ',' I ') as outt_put; # 3Copy the code

  • Length Indicates the number of bytes

    select length('leboy');
    Copy the code

• The math functions are pretty much the same as Java

  • Round round

    SELECT ROUND (1.5); # 2 -Copy the code

  • Rand random number

    SELECT RAND(); # [0, 1)Copy the code

  • Floor is rounded down

  • Ceil rounded up

    The SELECT CEIL (1.02); # 1Copy the code

  • Mod mod(a,b) === a-a/b*b;

    SELECT MOD(10,-3); # 1
    Copy the code

  • Truncate truncation

    The select truncate (1.599999, 2); # 1.59Copy the code

• Date function

  • Now Current system date + time

    select now();  # yyyy-MM-dd HH:mm:ss
  Copy the code

  • Curdate Current system date excluding time

    select curdate(); # yyyy-MM-dd
    Copy the code

  • Curtime Indicates the current system time

  • Year () month() day() Gets the year, month and day

    select year(now()); # 2020
    Copy the code

  • Str_to_date converts characters to dates

    Select * from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y');Copy the code

    Date_format converts dates to characters

    Select last_name,date_format(hiredate,'%m month /%d day %y year ') from employees where last_name,date_format(hiredate,'%m month /%d day %y year ') from employees where last_name,date_format(hiredate,'%m month /%d day %y year ' commission_pct is not null;Copy the code

• Other functions

  • Version version
  • The database of the current library
  • User Indicates the current user

  select version();
  select database();
  select user();
  Copy the code

• Flow control function

  • If handles double branching, similar to Java’s ternary operator

    Select last_name,if(null,' commission_pct ',' commission_pct ') as last_name from employees;Copy the code

  • Case statements handle multiple branches

    • Case 1: Processing equivalence judgment switch case effect

      The syntax is as follows: case Field or expression when constant 2 then value 1 or statement 1 to display; When constant 2 then the value 2 or statement 2 to display; . Else The value n or statement n to display; End # eg: select * from department where id =30 where id = 1.1; Department number = 40 shows 1.2 times salary; Department number =50, showing 1.3 times salary; Other departments show wages as original wages. Select salary original salary,department_id department ID, Case department_id when 30 then salary*1.1 when 40 then salary*1.2 when 50 then salary*1.3 else salary end as new salary from employees;Copy the code

    • Case 2: The processing condition judgment is similar to multiple if else

      Syntax: case when condition 1 then value 1 or statement 1 when condition 2 then value 2 or statement 2... End # eg: query employee's salary if salary >20000 (level A), salary >15000 (level B), salary >10000 (level C), otherwise (level D) Select salary, Case when salary>20000 then 'A' when salary>15000 then 'B' when salary>10000 then 'C' else 'D' end as salary level from employees;Copy the code

4.2 Grouping Functions

• The sum sum
• Max maximum
• Min min
• Avg average
• Count count

Features: 1. Sum and AVG are generally used to handle numeric types, Max (dictionary order highest) min count(statistics exist) can handle any type. All of the above grouping functions ignore null values except count(*). Each parameter can be used with a distinct result after deduplication. 4. The parameter count can be a field, constant value (usually 1), and *. Count (*) is used to count the number of rows. 5. Select sum(salary) and round(avg(salary),2), Max (salary), min(salary),count(salary) from employees; Select count(distinct salary) count(salary) from employees; (3) : Count (*) = count(1) = count(*) = count(1) It's a little bit higher than count(*).Copy the code

Step 5: Group query

Select from table [where select criteria] group by [order by]; Features: 1. The fields queried together with the grouping function must be the fields that appear after the group by function. The filtering is classified into two types: pre-group filtering and post-group filtering. The filtering conditions are as follows: Keywords for the join of the table position (pre-group filtering) original table group by before WHERE (post-group filtering) Result set of group by having In general: If you can use pre-group filter, try to use pre-group filter, can improve efficiency if the group function is used for conditional filter must be post-group filter, put in the having sentence 3. Select * from 'group by'; select * from 'group by'; select * from 'group by'; select * from 'group by'; select * from 'group by'; Select avg(salary),job_id from employees group by job_id; 3.2 Querying the number of departments per location Select count(*),location_id from departments group by location_id; # note: Select Max (salary),department_id from employees where email like '%a%' group by department_id; 3.4 Querying the average salary of employees with bonuses Select AVg (salary),manager_id from employees where commission_pct is not null group by manager_id; Department_id from employees group by department_id having count(*)>5 select count(*),department_id from employees group by department_id having count(*)>5 Select job_id, Max (salary) from employees where commission_pct is not null Group by job_id having Max (salary)> 10000; Select min(salary),manager_id from employees where manager_id>102 select manager_id from employees where manager_id>102  group by manager_id having min(salary)>5000; # note: Add the order 3.8 maximum salary of bonus employee for each job >6000 job number and maximum salary, Select job_id, Max (salary) from employees where commission_pct is not null group by job_id having max(salary)>6000 order by max(salary) asc; # note: 3.9 Querying the minimum wage of each department for each type of work Select min(salary),department_id,job_id from employees group by department_id,job_id # order by min(salary) desc;Copy the code

Step 6: Join query

• Also known as a multi-table query, that is, the query fields from multiple tables.

Select name,boyName from boys,beauty; Cartesian product phenomenon: table 1 m row and table 2 n row query result m* N cause: no valid join condition avoid: Select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id;Copy the code

6.1 classification

• Chronologically:
  • Sql92 standard: mysql only supports internal connections, Oracle supports partial external connections
  • Sql99 standard: in mysql, except for full external connection, other support, therefore recommended
• By function:
  • In the connection
    • Contour connection
    • Unequal connection
    • Since the connection
  • Outer join
    • The left outer join
    • Right connection
    • Full outer join
  • Cross connection

6.2 the sql92 standard

Select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id; #eg: Select last_name,department_name from employees,departments where employees.department_id = departments.department_id; SQL > alter table aliases (' from #eg '); SQL > alter table aliases (' from #eg '); Select last_name, e.jo_id,job_title from employees e, jobs j # where e.jo_id = j.jo_id; select last_name, e.jo_id,job_title from employees e, jobs j # where e.jo_id = j.jo_id; And #eg: Select last_name,department_name,commission_pct from employees e, DEPARTMENTS D where E.department_id = d.department_id and e.commission_pct is not null; Select department_name,d.manager_id,min(salary) from employees select department_name,d.manager_id,min(salary) from employees select department_name,d.manager_id,min(salary) from employees e,departments d where e.department_id = d.department_id and commission_pct is not null group by department_name,d.manager_id; #1.4 Can add sort #eg: Query the name of each job and the number of employees, Select job_title,count(*) from employees e,jobs j where e. jo_id = j.jo_id group by job_title order by count(*) desc; #1 join multiple tables #eg: Query employee name, department name and city, city name starts with S, Select last_name,department_name,city from employees E,departments D,locations L where E.department_id = d.department_id and d.location_id = l.location_id and city like 's%' order by department_name desc; Summary of multi-table equivalent connection: ① equivalent join result = intersection of multiple tables ② n table join, at least N-1 join condition ③ multiple tables do not have primary or secondary requirements ④ Generally alias table, improve reading and performance ⑤ can be used with the above words and sentences. Such as screening group ordering # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 2. Non-equivalent connection #eg: Query the salary and salary level of an employee, Select salary,grade_level from employees E,job_grades G where salary between g.lowest_sal and g.haighest_sal and  g.grade_level = 'A'; Non-equivalent connection can be matched with all kinds of words before, refer to equivalent connection, not one example. # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 3. Since the connection # eg: Query the employee name and the superior name select E.lab_name,e.employee_id, m.lab_name, m.labyee_id from employees e,employees m where e.manager_id =  m.employee_id;Copy the code

6.3 sql99 standard

# Note: Inside join, outside join and cross join, the emphasis is on the three inside join and left outside, right outside. Grammar: Select queries list from the inner table 1 | left (outer) | right (outer) | cross 】 the join table 2 on connection conditions the inner | left (outer) | right (outer) | cross 】 Join table 3 on join condition 【where select condition 】 【group by select condition 】 【having select condition after select condition 】 【order by select condition 】 Inner join table 1 inner join table 2 alias on join condition; 1.1 Equivalent connection ① : Query employee name,department name select last_name,department_name from employees e inner join DEPARTMENTS D on E.department_id = d.department_id; (2) : Select last_name,job_title from employees e inner join jobs j on e.jo_id = j.jo_id WHERE (select last_name,job_title from employees e inner join jobs j on e.jo_id = j.jo_id where) select last_name,job_title from employees e inner join jobs j on e.jo_id = j.jo_id where last_name like '%e%'; (3) : Select count(*) city from departments D inner join locations l where D. location_id = query the city name and number of departments with departments greater than 3 (grouped and filtered) l.location_id group by city having count(*) > 3; (4) : Query the name and number of the department whose number of employees is greater than 3. Select count(*) number of employees,department_name FROM employees e inner join DEPARTMENTS D on E.department_id = d.department_id group by department_name having count(*) > 3 order by count(*) desc; (5) : Select last_name Employee name,department_name Department name,job_title Job name from employees e inner join select last_name employee name,department_name department name,job_title Job name from employees e inner join departments d on e.department_id = d.department_id inner join jobs j on e.job_id = j.job_id order by department_name desc; 1.2 Non-equivalent connection ① : Query employee salary levels select grade_level,salary from employees e inner join job_grades j on salary between lowest_sal and highest_sal; (2) : Select * from salary level where number of salary levels >20; Select count(*) Number of grades from job_grades J inner join employees e on E.salary between lowest_sal and  highest_sal group by grade_level having count(*) > 20 order by grade_level desc; 1.3 Self-Connection ① : Select e.last_name Employee name,m.last_name superior name from employees e inner join employees m on e.manager_id = m.employee_id; (2) : Query the employee name and superior name of the employee whose name contains k. Select e.last_name Employee name. From employees e inner join employees m on e.manager_id = m.employee_id where e.list_name like '%k%'; # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 2. External join application scenario: it is used for records in one table but not in another table. ① : The query result of external join is all records in the master table, and the matching value is displayed if the matching value is displayed in the table. Otherwise, null is displayed. External join query result = internal join result + records in the primary table but not in the secondary table, then filter as required ② : left external join: left is the primary table; Right outer join: right on the right is the main table (3) : left and right to exchange the order of the two tables, can achieve the same effect (4) : full outer join = connection results in table 1 and table 2 + no + table 2 and table 1, according to requirements after screening # eg: Select g.name from beauty g left outer join boys b on g.boyfriend_id = b.id where B.id is null; # note: It is best to use the primary key as the secondary table join condition, because it is non-empty and does not affect the result. Select g.name from boys b right outer join beauty g on b.id = g.boyfriend_id where B.id is null; # eg: Select department_name department name from DEPARTMENTS D left outer join employees e on D. department_id = e.department_id where e.employee_id is null; Select department_name from employees e right outer join departments D on E.department_id = D.department_id where e.employee_id is null; 3. Cross join: is a Cartesian product.Copy the code

• Sql92 and SQL99
  • Sql99 supports more
  • Readability: SQL99 implements the separation of connection conditions and filter conditions, which is more readable

Step 7: Subquery

• A query statement is nested with another complete SELECT statement. The nested SELECT statement is called a sub-query or an internal query outside the query statement, known as the primary query or external query.

• Classification:

  • Location by subquery:

    • Select: only standard quantum query is supported
    • Support table query, is a result set
    • Where or having after: scalar, column, row
    • A table that exists is a result set

  • Depending on the number of rows in the result set (function) :

    • Scalar quantum query (result set with only one column and one row)
    • Column subquery (result set with one column and multiple rows)
    • Row subquery (result set with one row and multiple columns)
    • Table subqueries (result sets usually have multiple rows and columns, the first three can also be called table subqueries (more special))

SQL > select * from Abel where Abel has a higher salary than Abel Select * from employees where last_name = 'Abel' select * from employees where last_name = 'Abel'  (select salary from employees where last_name = 'Abel'); Select * from job_id, salary, job_id, salary from salary where employee salary is the same as employee 141. Select job_id from employees where employee_id = 141; select job_id from employees where employee_id = 141; Select salary from employees where employee_id = 143; # Step 3: Select last_name name,job_id,salary from employees where job_id = (select job_id from employees where last_name = job_id employee_id = 141) and salary > (select salary from employees where employee_id = 143); Select last_name,job_id,salary from employees where salary = (select min(salary) from employees where salary = (select min(salary))  from employees ); Select min(salary) from employees where department_id = 50 #select department_id from employees where department_id = 50 #select department_id from employees where department_id = 50 Select min(salary),department_id DEPARTMENT ID from employees group by department_id having min(salary) > (select min(salary) From employees # select department_id from employees where department_id = 50 # select department_id from employees where department_id = 50 # # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 2. Select distinct department_id from department where the location_id is 1400 or 1700. Select distinct department_id from department where the location_id is 1400 or 1700 Departments where location_id in (1400,1700); Select last_name from employees where department_id in (select distinct department_id from departments where Location_id, in (1400170)); Select * from employees where job_id = 'IT_PROG' select * from employees where job_id = 'IT_PROG' select * from employees where job_id = 'IT_PROG' select * from employees where job_id = 'IT_PROG = 'IT_PROG'; Select employee_id,last_name name,job_id,salary from employees where salary < any(select distinct salary from employees employees where job_id = 'IT_PROG' ) and job_id <> 'IT_PROG'; Select Max (salary) from employees where job_id = 'IT_PROG'; select * from employees where salary = 'IT_PROG'; Select employee_id,last_name,job_id,salary from employees where salary<(select Max (salary) from employees employees where job_id = 'IT_PROG' ) and job_id <> 'IT_PROG'; Select * from salary where job_id = 'IT_PROG' and salary where job_id = 'IT_PROG' Select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from))))); The primary query condition not in (subquery result) is equal to the primary query condition <> all(subquery result) # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 3. 1 select min(employee_id) from employees; 2 Select employee (employee_id) from employees; Select Max (salary) from employees; Select * from employees where employee_id = (select min(employee_id) from employees) and salary = (select * from employees where employee_id = (select min(employee_id) from employees max(salary) from employees ); A row query can be used instead: Select * from employees where (employee_id,salary)=(select min(employee_id), Max (salary) from employees where (salary) =(select min(employee_id), Max (salary) from employees Employees); # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 7.2 behind the select (only support a scalar subquery) # 1) Querying the number of employees in each department select D.*,(select count(*) from employees e where e.partment_id = d.partment_id) Number of employees from departments d; # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 7.3 on # 1) query from behind the average salary for each department's wage level Select avg(salary),department_id from employees group by department_id Select AVg (salary) AG,department_id from employees group by department_id) ag_dep # Inner join job_grades G on ag_DEP. ag between lowest_sal and highest_sal; # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 7.4 behind the exists (correlated sub-query) # grammar: Exists (complete query statement) Select department_name from DEPARTMENTS where employees exist (select * from departments where employees exist employees e where d.department_id = e.department_id ); # the exists behind the subquery, unlike other sub queries, have the results after first query, it is the result of the first to find out the main query again according to the result of the subquery to do the corresponding filter (understand: every scan a record, judge whether the exists returns a result set, if that would keep, not the record is preserved) # in:  select department_name from departments d where d.department_id in( select department_id from employees ); #in: select b.* from boys b where B. ID not in (select boyfriend_id from beauty); #exists select b.* from boys b where not exists( select * from beauty g where g.boyfriend_id = b.id );Copy the code

• Small knot query features:
  • Subqueries are placed in parentheses
  • Subqueries can be placed after FROM, SELECT, WHERE, and HAVING, but are generally placed to the right of conditions
  • The subquery takes precedence over the primary query, which uses the results of the subquery
  • Subqueries are classified into the following two types based on the number of rows in the query result:
    • Single-row subquery: The result set has only one line, usually with the single-line operator: > <= <> >= <=
      • Illegal use: ① The subquery result is a set of values ② the subquery result is empty
    • Multi-row subqueryAny /some, all, in, not in;
      • In belongs to one of them, and any and all can often be replaced by other queries

Step 8: Paging query

• Application scenario: In Web development, the corresponding paging query SQL statement (how many pieces of data to display on each page) needs to be submitted according to user requirements.

Grammar: Select query list from table [join type join table 2] [ON join condition] [WHERE filter condition] [group by] [having filter after group] [ORDER by Limit [offset,] size; Select * from employees limit 0,5; select * from employees limit 0,5; select * from employees limit 5; Select * from employees limit 10,15; select * from employees limit 10,15; Select * from employees where commission_pct is not null order by salary desc limit 10;Copy the code

• Features:

Select * FROM table limit (page-1)*size,size; select * from table limit (page-1)*size,size; select * from table limit (page-1)*size,size;Copy the code

Step 9: Joint query

• Union: To combine the results of multiple query statements into one result.

Select * from employees where department_id>90 or email like '%a%'; select * from employees where department_id>90 or email like '%a%' Select * from employees where department_id>90 union select * from employees where email like '%a%'; The syntax is: Query statement 1 union Query statement 2 union... ; Application scenario: The query result is from multiple tables that are not directly connected, but the queried information is the same. Select id,name,sex from t_ca where sex = 'male' # union select t_id,t_name,t_sex from t_ua where t_sex = 'male'; * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Union is de-duplicated by default, and using union all can contain a representation of the entire duplicate item # : Select query list [from table] [WHERE condition] union [all] select query list [from table] [WHERE condition] union [all]..... Select * from 'select' where 'select' from 'select'Copy the code

Six _DML language

• Data manipulation language
  • Insert the insert
  • Modify the update
  • Delete the delete

insert

• grammar

Insert into table name (insert into table name) Values (1,...). ; # 1: Insert the same or compatible field type and value type, And one to one correspondence insert into beauty (id, name, sex, borndate, phone, photo, boyfriend_id) Values (13, 'Tang Yixin', 'woman', '1990-4-23', '18999999999', null, 2); If the photo type is blob, null will be used to fill the column. If the photo type is blob, null will be used to fill the column.  insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id) Values (13, 'Tang Yixin', 'woman', '1990-4-23', '18999999999', null, 2); # 表 2: insert into beauty(id, sex,phone) values(14,' beauty ',' female ','1899999999 '); Insert into beauty(id,phone,sex) values(' 189999990 ',15,' 189999990 ',' female '); Insert into beauty values(18,' Joe ',' female ',null,'13166666666',null,null); Insert into table name set column name = value, column name = value,... ; #eg: insert into beauty set id=19,name=' liu ',phone='110'; Insert into table values(),(), ()... ; #eg: insert into boys (boyName,userCP) values(' 1 ',100),(' 2 ',500); #eg: insert into beauty(id,name,phone) select 20,' 13798989898'; Insert the result set of the query into the beauty tableCopy the code

• summary

2. Features: 2.3. Insert values for fields that cannot be considered empty. The number of fields and values must be the same. The sequence is the same as that in the tableCopy the code

Modify the

• Modify single table syntax:

Update table name set name = new value, name = new value... # update all rows [where condition]; Update beauty set phone='13899889999' where name like '%'; Update boys set boyName = 'boyName ',userCP = 10 # where id = 2;Copy the code

• Modify multi-table method (cascade modification) :

# sql92 syntax: (only inside connection) update the alias table 1 1, table 2 alias 2 set field = new values, field = new values... Where join condition and filter condition; # sql99 grammar: update the alias table 1 1 inner | left | right alias 2 on the join table 2 connection conditions set field = new values,... Where filter criteria; You can also add on update CASCADE or on delete CASCADE to a foreign key constraint. Alter table drop foreign key foreign key name; Alter table add CONSTRAINT FOREIGN key name Foreign key references ON update CASCADE on delete cascade; # eg: Update boys B inner join beauty g on B.ID = g.boyfriend_id set g.hone = '114' where B.boyname Stickline (1, 0, 0), color0000ff; #eg: ② Change the boyfriend number of goddesses without boyfriends to 2. update boys b right join beauty g on b.id = g.boyfriend_id set g.boyfriend_id = 2 where b.id is null;Copy the code

delete

• Method 1: Delete statement

  Select * from table where select * from table where select * from table where select * from table where select * from table where select * from table where select * from table where select * Delete from beauty where phone like '%9'; delete from beauty where phone like '%9'; 2. Delete multiple tables (cascading deletes) : # syntax: # 1 sql92 syntax: delete the alias [delete write any table alias table, delete all write] from the alias table 1 1, table 2 alias 2 where the connection condition and the filter; # 2) sql99 syntax: delete [alias table 1 and table 2 alias] table from table 1 alias 1 inner | left | right alias 2 on the join table 2 connection condition where the filter; Delete g from beauty g inner join boys b on g.boyfriend_id = b.id where b.boyname = ' '; delete g from beauty g inner join boys b on g.boyfriend_id = b.id where b.boyname = ' '; Delete b,g from beauty g inner join boys b on B.id = g.boyfriend_id where b.boyname = 'boyfriend_id ';Copy the code

• Mode 2: truncate statement

Truncate table name truncate table name; truncate table boys; # Delete boys table data, high efficiency of clearing data.Copy the code

• The difference between the two approaches
  • Truncate cannot add a WHERE condition, while DELETE can add a WHERE condition
  • Truncate is slightly more efficient
  • Truncate After a table with self-growing columns is deleted and data is inserted, the data starts from 1. Delete After a table with self-growing columns is deleted and data is inserted, data starts from the last breakpoint
  • Truncate deletes with no return value, delete deletes with return value (eg: truncate returns 0 rows affected)
  • The TRUNCate deletion cannot be rolled back, while the DELETE deletion can be rolled back

Seven _DDL language

• Data definition language

Management of libraries and tables

• Create create, alter, drop drop

Create database [if not exists] # eg: Create database books; Create database if not exists books; 1.2 For the modification of the library, it is generally not recommended to code operation (easy to cause problems), and generally there is no modification of the library, if you want to change the name, generally change the file name on the server side to restart the service (this case is also rare). Alter database books character set GBK; Drop database [if exists] drop database name; #eg: drop database if exists books; Table 2.1 Create a table CREATE TABLE [if not exists] Table name (column name, column type [(length) constraint], column name, column type [(length) constraint],... Column name column type [(length) column constraint]) #eg: BName varchar(20), price double, # author varchar(20), If the author has written many books, you can create a table to store the author. Author create table author(id int, au_name varchar(20), Nation varchar (10)) of table 2.2 modify grammar: the alter table tablename add | drop | the modify | change column names [] column type, constraint; Alter table book change [column] publishDate pubDate datetime; Alter table book modify column pubDate timestamp; alter table book modify column pubDate timestamp; Alter table author add column annual double; Alter table author drop column annual; Alter table author rename to book_author; Drop table [if exists] book_author; If you want to refer to the library in your own way, use the following syntax, of course, if you want to use some old data. Drop database if exists Database name; Create database Database name; Drop table if exists Specifies the name of the table. Create table name (); Create table copy like author; Create table copy2 select * from author; Create table copy3 select id,au_name from author where nation = '中国'; Create table copy4 SELECT id,au_name from author where 0;Copy the code

​

Common data types

Numeric type: integer decimal number: fixed-point number Floating point number Character type: short text: char vARCHar Long text: text BLOB (long binary data) Date typeCopy the code

• The integer

type	Tinyint	Smallint	Mediumint	Int/Integer	Bigint
byte	1	2	3	4	8

drop table if exists tab_int; Create table tab_int(t1 int(7), t2 int unsigned #); insert into tab_int values(-321,-321); If the value is not set to a signed value, the value is set to 0. If the value is not set to a signed value, the value is set to 0. If the value is not set to a signed value, the value is set to 0. Note: Length does not represent the size of the numeric value. The length represents the width of the display. If it is not enough, it will be filled with 0 on the left, but it must be used with zerofill! Once zerofill is used, it is an unsigned value.Copy the code

• The decimal

  • The first two are floating point types and the last is fixed-point type

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

Create table tab_float(f1 float(5,2), f2 double(5,2), f3 dec(5,2)); Insert into tab_float values (789.21, 789.21, 789.21); Insert into tab_float values(789.217,789.217,789.217); Insert into tab_float values(789.2,789.2,789.2); #789.20 INSERT into tab_float values(7891.2,7891.2,7891.2); Summary: M: Integer part + decimal part D: Decimal part if out of range, insert threshold # ② : both M and D can be omitted, but: # If decimal, M defaults to 10 and D defaults to 0 # if float or double, the precision is determined by the precision of the data being inserted # ③ : Fixed point types have high precision. If you want to insert values with high precision (such as currency), consider using the DEC # principle: the simpler the type selected is the better, and the smaller the type that can hold values is the better (save space).Copy the code

• character

# Short text: char(M) varchar(M) Char (M) A fixed-length character. M can be omitted. The default value is 1. # enum create table tab_char(c1 enum('a','b','c') # #set create table tab_set( s1 set('a','b','c','d') ); insert into tab_set values('a'); insert into tab_set values('a,b'); insert into tab_set values('A,C,D'); # binary and varbinary are used to hold shorter binaries # enum is used to hold enumerations # set is used to hold collections # Longer text: text blobCopy the code

• The date of

  type	date	datetime	timestamp	time	year
  byte	4	8	4	3	1
  The sample	1000-01-01	1000-01-01 00:00:00	19700101080001	– 838:59:59	1901

  • Features:
    • (1) Date stores only the date. Time stores only the time. Year Stores only the year
    • Timestamp is affected by the time zone

Common constraints

• Constraint Meaning: Limits the data in a table to ensure accuracy and reliability (consistency).

Create table Table name (field name field type constraint); # Classification of constraints, six constraints: The value of this field is not null. For example, the value of this field is not null. For example, the value of this field is not null. The value of the primary key field is unique and not empty eg: student id, employee ID, etc. ⑥ Foreign key is used to restrict the relationship between two tables. Add a foreign key constraint to a secondary table, which is used to reference a column in the primary table eg: Select * from student table, select * from employee table, select * from employee table, select * from student table, select * from employee table, select * from employee table, select * from employee table, select * from employee table, select * from employee table, select * from employee table, select * from employee table SQL > create table name (table name); SQL > create table name; SQL > create table name (table name); Table level constraint); Add a constraint type to the column name and type of the column. Support only default non-empty primary key unique create database students; use students; Create table stuinfo(id int primary key, # stuName varchar(20) not null, Gender char(1) check(gender=' male 'or gender=' female '),# check constraint (gender=' male' or gender=' female ') seat int unique,# age int default 18,# default majorId Int references major(id) # int references major(id) # create table major( id int primary key, majorName varchar(20) ); # [constraint name] drop table if exists stuinfo; create table stuinfo( id int, stuname varchar(20), gender char(1), seat int, age int, majorid int, Constraint pk primary key(ID),# constraint Uq unique(seat),# constraint CK check(gender=' male 'or gender=' female '),# check Constraint fk_stuinfo_major Foreign key(majorID) References Major (id)# # eg: Create table if not exists stuinfo(id int primary key, stuname varchar(20) not null, gender char(1), age int default 18, seat int unique, majorid int, constraint fk_stuinfo_major foreign key(majorid) references major(id) ); # primary key and unique comparison primary key(ID,stuname) ensures uniqueness is allowed to be null how many combinations are allowed in a table!! Not recommended primary key √ × At most one √ unique √ √(only one NULL is allowed) Multiple √ # Foreign key relationships must be set in the secondary table. 2. The foreign key columns of the secondary table are the same or compatible with the associated columns of the primary table. 3. The associated column of the primary table must be a key (usually the primary key). 4. When inserting data, the primary table should be inserted before the secondary table. Alter table table_name modify column name alter table table_name alter table table_name modify column name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name Alter table table_name add [constraint constraint name] alter table table_name add [constraint name] Alter table table name modify column name column type null; Alter table table_name drop primary key; Alter table table_name drop index;Copy the code

Identity column

• Also known as self-growing columns: You don’t need to manually insert values, the system provides default sequence values.

Create table tab_identity(id int primary key auto_increment, name vARCHar (20)); insert into tab_identity values(null,'john'); Insert into tab_identity(name) values('luky'); Eg: set auto_increment_increment = 3; Insert into tab_identity values(10,' John '); insert into tab_identity values(10,' John '); insert into tab_identity values(10,' John '); Id columns do not have to be paired with a primary key, as long as they are a key. There can be only one identity column # 3 in a table. The identity column type can only be numeric # 4. The identity column can be set to a step by setting auto_increment_increment, Alter table tab_identity modify column ID int primary key auto_increment; Alter table tab_identity modify column ID int;Copy the code

Eight _TCL language

• Transaction Control Language Transaction Control Language
• Transaction: A unit of execution consisting of one or a group of SQL statements that either execute at all or not at all. Data is typically switched from one state to another through a set of logical operation units (a set of DML and SQL statements).
• Storage engine: Data in mysql is stored in files (or memory) using a variety of different techniques. Through the show engines; Check the storage engines supported by mysql. Innodb supports transactions (mysql5.5 default), while MyISam, Memory, etc do not.
• The ACID property of the transaction
  • Atomicity: Atomicity means that a transaction is indivisible, either executed or rolled back.
  • Consistency: change from one consistent state to another consistent state (eg: your two sums remain the same before and after the transfer).
  • Isolation: Transactions executed concurrently cannot interfere with each other (Isolation level is set accordingly).
  • Durability: Changes to data in the database are persistent once a transaction commits (as is deletion and modification) and should not be affected by other operations and database failures unless they are modified by other transactions.
• Transaction creation
  • Implicit transactions: No obvious opening and closing flags. Eg: insert update delete statement
  • Explicit transactions: Transactions have distinct opening and closing flags. Prerequisite: The automatic submission function must be disabled first.

Set autocommit = 0; Start transaction; SQL statement 1 (select INSERT, update, delete); [savePoint] # optional statement 2; . (3) The end of a transaction can be considered as a result of either committing or rolling back commit. Rollback [to breakpoint name]; Comparison between DELETE table names truncATE TABLE names Note: DELETE supports rollback. Truncate does not support rollbackCopy the code

• Multiple transactions running at the same time accessing the same data in the database can cause various concurrency problems if the necessary isolation mechanisms are not in place, such as two transactions T1 and T2:
  • Dirty reads: T1 reads a field that has been updated by T2 but has not yet been committed. If T2 rolls back, T1 reads temporary and invalid values.
  • Non-repeatable reads: T1 reads a field, T2 then updates the field, T1 reads the field again, and the value is different.
  • Phantom read: T1 reads a field from the table, T2 inserts new rows into the table, and T1 reads the table again with more rows.

Select @@tx_isolation; Set session Transaction Isolation level read uncommitted; #level # isolation level Repeatable read # Repeatable read can avoid dirty and unrepeatable reads serializable # Set Global Transaction Isolation level [read COMMITTED];Copy the code

Nine _ view

• View is a virtual table, mysql5.1 version of the new feature, is through the table dynamically generated data, only save SQL logic, do not save query results.

  Create view as query statement; 1 Eg: Query the employee name, department name, and job type whose name contains a.  create view myv1 as select last_name,department_name,job_title from employees e join departments d on e.department_id =  d.department_id join jobs j on j.job_id = e.job_id; Select * from myv1 where last_name like '%a%'; Create view myv2 as select AVG (salary) AG,department_id from employees group by department_id; Select myv2.ag, g.grad_level from myv2 join job_grades G on myv2.ag between g.lowest_sal and g.high_sal; Select * from myv2 order by ag limit 1; Create view myv3 as select * from myv2 order by ag limit 1; select d.*,m.ag from myv3 m join departments d on m.department_id = d.department_id; Create or replace view as query statement; create or replace view as query statement Method 2 alter VIEW View name as query statement; Drop view myv1,myv2,myv3; # 4 View desc myv3; Show create view myv3; Create or replace view myv1 as select last_name,email "annunl salary" from employees; #1 Insert into myv1 values(' [email protected]'); Update myv1 set last_name = 'last_name' WHERE last_name = 'last_fly '; Delete from myv1 where last_name = 'last_name '; Note: All of these operations will affect the original table. Here is a simple special view. SQL statements containing the following keywords: Select subquery join from a view that cannot be updated. A subquery of the WHERE clause applies to the table in the FROM clause Create view creates a table that contains only SQL logic. Create table creates a table that contains only SQL logicCopy the code

Ten _ variable

• System variables
  • The global variable
  • Session variable
• Custom variable
  • The user variables
  • A local variable

System variables

• It is provided by the system, not user-defined, and belongs to the server layer

  # a system variable # 1. To view the show [global | session] variables; # 2. Check the meet the conditions of a part of the system variables show [global | session] variables like '% char %'; Select @@system variable name; # the default session variable select @ @ global | session. System variable name; # 4. As a system variable assignment set [global | session] system variable name = value; A set @ @ # way global | session. The system variable name = value; Note: The default is sessionCopy the code

Custom variable

Set @user variable name = value; set @user variable name = value; Set @ user variable name := value; Select @username := value; eg: set @count:=1; Select * from table; select * from table; select * from table; eg: select count(*) into @count from employees; Select @username; Eg: the select @ count; # 2. Local variables # scope: defined only in begin end and is the first sentence #① Declare variable name type; Declare Variable name type Default value; Set local variable name = value; Set local variable name := value; Select @ local variable name := value; Select * from table select * from table select * from table Select local variable name; User variable current session The @ sign must be added anywhere in the session, and the type is not qualified. Begin End Local variables can only be in BEGIN end, and the first sentence is generally not required to be typedCopy the code

Xi _ Stored procedures and functions

The stored procedure

• Stored procedure: A collection of pre-compiled SQL statements, similar to methods used in Java.
• Advantages:
  • Improves the reuse of SQL statements and reduces the pressure on developers
  • Simplify operation and improve efficiency
  • Reduced compilation times and reduced connection times to the database server, improved efficiency

Begin The body of the stored procedure. End # Note # 1. The parameter list consists of three parts: Parameter mode Parameter Name Parameter Type eg: In Stuname Varchar (20) The in parameter can be the input the caller passes in and the out parameter can be the output the return value. The inout parameter can be either the input or the output. # 2 If the stored procedure body is just one sentence, Begin End You can omit the weight of the stored procedure. Each SQL statement must be marked with a semicolon (;). The end of the stored procedure can be reset using delimiter. # ① Create delimiter $CREATE procedure myp1() begin Insert into admin(username,word) values ('john1','0000'), ('john2','0001'), ('john3','0003'), ('john4','0004'), ('john5','0005'); End $#② call myp1()$# The end flag has been set to $to call # 2 in a black window. Create PROCEDURE myp2(in beautyName varchar(20)) begin select bo.* from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name=beautyName; Create PROCEDURE myp3(in username varchar(20),in password varchar(20)) begin end # 3. Create Procedure myp44(in beautyName varchar(20),out boyName varchar(20)) begin SELECT Bo.boyname into boyName from boys bo inner join beauty b on bo.id = B.boyfriend_id where b.name = beautyName; End $# Call myp44(' myrock ', @name)$# give a variable to receive select @name $# then view # 4. Create Procedure myp8(inout a int,inout b int) begin set A =a*2; create PROCEDURE myp8(inout a int,inout b int) set b=b*2; Set @m=10$set @n=20$call myp8(@m,@n)$select @m,@n$drop procedure p1; Show create procedure myp2;Copy the code

function

• Much the same as stored procedures
• The difference between
  • Stored procedure: can have no return, or multiple return, suitable for batch insert
  • Function: returns one and only (or none), returns a result after processing the data

Return type begin Function body end # Note: The parameter list contains two parts: If a return statement is not placed at the end of the function body, no error will be reported. # begin End can be omitted when there is only one line in the function body # Use the delimiter statement to set the end tag # two call the select function name (argument list) # Execute the function and display the return value # three simple demonstration #1 Return with no arguments # Return the number of employees in the company create function myf1() returns int begin declare c int default 0; Select count(*) into c from employees; Return c; End $select myf1()$# Create function myf2(empName varchar(20)) returns double begin set @sal=0; Select salary into @sal where last_name = empName; select salary from employees where last_name = empName; return @sal; End $select myf2('kochhar')$ Drop function myf3;Copy the code

Xii. Process control structure

• Sequential structure
• Branching structure
• Loop structure

Branching structure

If (expression 1, expression 2, expression 3) returns the value of expression 2 if expression 1 is true, otherwise returns the value of expression 3 2. Similar to a switch statement in Java, generally used to implement the equivalent judging grammar: case variable expression | | field the when to determine the value of the then returns the value of 1 or 1; When The value to be judged then returns the value 2 or statement 2; . Else The value n or statement n to return; end case; # case 2: similar to Java if-else statement, commonly used to implement interval judgment syntax: case when condition 1 then return value 1 or statement 1; When condition 2 then returns the value 2 or statement 2; . Else The value n or statement n to return; end case; # Case structure features: # can be used as an expression, nested in other statements, you can put it anywhere inside or outside of a begen end # can be used as a separate statement, you can only put it in a begin end # If the conditions are met then we end case # else can be omitted, if neither of those conditions are met, If: if condition 1 then statement 1; Elseif condition 2 then statement 2; . Else statements n; end if; # function: # create A function that returns A if >90, B if >80, and C if >60. Otherwise D create function test_if(score float) returns char begin Declare CH char default 'A'; if score>90 then set ch='A'; elseif score>80 then set ch='B'; elseif score>60 then set ch='C'; elseif ch='D'; end if; return ch; Select test_if(87)$Copy the code

Loop structure

• While loop repeat must be placed inside begin end

• Cycle control:

• Iterate is similar to continue;

• Leave is similar to break;

#1 While syntax: [mark] while loop condition do loop body; End while [mark]; #2 Loop syntax: [mark] loop body; End loop [mark]; Can be used to simulate a simple loop with leave to break out of the loop End repeat [tag]; Create PROCEDURE pro_while(in insertCount int) begin declare I int default 1; a: while i <=insertCount do insert into admin(username,word) values(concat('rose',i),'666'); set i = i+1; end while a; End $# Call pro_while(100)$# Loop Summary All three loops are in begin end. Loop is an infinite loop with no conditions Select varchar(20) from varchar(20) Insert a specified number of random strings into the table create table stringContent (id int primary key auto_increment, Content vARCHar (20)); insert a specified number of random strings into the table create table stringContent (id int primary key auto_increment, Content vARCHar (20)); delimiter $ create procedure test_randstr_insert(in insertCount int) begin declare i int default 1; # define loop variable I declare STR varchar (26) default 'abcdefghijklmnopqrstuvwxyz'; declare startIndex int default 1; # declare len int default 1; While I <= insertCount do set len =floor(rand()*20+1); Set startIndex=floor(rand()*26+1); Insert into stringContent (content) values(substr(STR,startIndex,len)); set i = i+1; end while; End $# calls call test_randSTR_INSERT (10)$# in the black windowCopy the code