Summary of MySQL Basics

Time:2021-10-15

Basic syntax of MySQL:

MySQL data is available at the end of the article

Execution sequence:

from -> join -> on -> where -> groupby -> avg max .. -> having -> select ->order by

SQL language classification:

  • DQL: Data Query Language
  • DML: Data Manipulation Language
  • DDL: Data Definition Language
  • DCL: data control language

DQL

data query language

SELECT:

  • It can be understood as the output of Java system. Out. Println();
  • The output content can be
    • fieldSelect field name from table name
    • constantselect 90
    • functionselect concat('a', 'bbb');
    • expressionselect 100/1234;
    • Aliasselect last_ Name as' name
    • duplicate removalselect DISTINCT department_id

About ‘+’ in select:

  • Select value + value; Direct operation
  • Select character + value; First, try to convert characters into numerical values. If the conversion is successful, continue the operation; Otherwise, it is converted to 0 and then calculated
    • select ‘aa’+70 #70
    • select 70 +’30’ #100
  • Select null + value; The results are null

Description table structure:

DESC departments;

Condition query

/*
 Conditional operator: > < >! = > =<=
 Logical operator: & & |! and or not 
 Fuzzy query: like, between, and, in, is null 
 		Like '%%' does not match null value
 	Wildcard: 
        %Any number of characters, including 0 characters 
        _  Any single character
        Default Escape\
        Custom escape: escape 'escape character'
 */
 
#1. Query the name and salary of employees whose salary is 12000-17000
SELECT 
last_ Name as' name ', salary as' salary'
FROM 
employees
WHERE 
salary BETWEEN 12000 and  17000
;
 #2 query the name, department number and annual salary of the employee with employee number 176
SELECT 
employee_ ID as' number ', last_ Name as' name ', job_ ID as' department number ', salary * 12 as' annual salary'
FROM 
employees
WHERE 
employee_id =176 
;
 #3 select the name and salary of employees whose salary is not between 5000 and 12000
SELECT 
last_ Name as' name ', salary as' salary'
FROM 
employees
WHERE 
salary not BETWEEN 5000 and  17000
;
#4 select the name and department number of the employee working in department 20 or 50
SELECT 
last_ Name as' name ', Department_ ID as' department number '
FROM 
employees
WHERE 
-- department_id =20 or department_id =50
department_id in(20,50)
;
#5
#If the attribute is blank, select the employee name and job without manager in the company_ id
SELECT 
last_ Name as' name ', job_ ID as' job no. '
FROM 
employees
WHERE 
ISNULL(manager_id)
;
#6. Select the name, salary and bonus level of employees with bonus in the company
SELECT 
last_ Name as' name ', commission_ PCT as' bonus', salary as "salary"
FROM 
employees
WHERE 
not ISNULL(commission_pct)
;
#7 select the employee name whose third letter is a
SELECT 
last_ Name as' name '
FROM 
employees
WHERE 
last_name LIKE '__a%'
;

#8 select the employee name with letters A and E in the name
SELECT 
last_ Name as' name '
FROM 
employees
WHERE 
last_name LIKE '%a%e%' or last_name LIKE '%e%a%';
;

#9 shows the first in the employees table_ Name employee information ending with 'e'
SELECT 
last_ Name as' name '
FROM 
employees
WHERE 
last_name LIKE '%e';
;

#Table 10 shows the name and position of employees with department number between 80-100
SELECT 
last_ Name as' name ',department_id '部门号'
FROM 
employees
WHERE 
department_id BETWEEN 80 AND 100;
;

#11 shows the manager of the table employees_ Employee name and position with ID of 100101110
SELECT 
last_ Name as' name ',job_id as '职位',manager_id
FROM 
employees
WHERE 
manager_id in(100,101,110)
;

sort

#1. Query the employee's name, department number and annual salary, in descending order of annual salary and ascending order of name
SELECT last_name as name,department_id as dID ,salary*12 as yearsal
FROM employees
ORDER BY yearsal DESC,last_name ASC

#2. Select the name and salary of employees whose salary is not between 8000 and 17000, in descending order of salary
SELECT last_name as name,salary as sal
FROM employees
WHERE salary not BETWEEN 8000 and 17000
ORDER BY salary DESC

#3. Query the employee information contained E in the mailbox, first in descending order according to the number of bytes in the mailbox, and then in ascending order according to the department number
SELECT department_ ID as did, email, length (email) as' mailbox length '
FROM employees
ORDER BY LENGTH(email) DESC,department_id ASC

Common functions:

Character function

  • LOWER(str)
  • UPPER(str)
  • CONCAT(str1,str2,…)
  • Substr (STR, POS, len) PS: there are many overloaded functions
  • LENGTH(str)
  • Instr (STR, substr) returns the index of the first occurrence of a substring
  • Trim ([remstr from] STR) removes leading and trailing spaces by default
    • Select length (trim ('a 'from "aaa123aaa")) as' length' => 123
  • REPLACE(str,from_str,to_str)
  • Lpad (STR, len, padstr), rpad (STR, len, padstr) fills the specified length with the specified characters
    • SELECT LPAD('aaaaa','10','#') => #####aaaaa

Mathematical function

  • Round (x, d)
    • ROUND(45.926,2) => 45.93
  • Truncate
    • TRUNCATE(45.926,2) =>45.92
  • Mod: remainder

Date function

  • Now: current time

  • STR_ TO_ Date (STR, format): set the date format tocharacterConvert to specified formatdate

    • STR_TO_DATE(‘2017-01-06 10:20:30′,’%Y-%m-%d %H:%i:%s’)
    • 2017-01-06 10:20:30
  • DATE_ Format (date, format): Setdateconvert tocharacter

    • DATE_FORMAT(‘2021223 ‘,’% y year% m month% d ‘)

Process control function

Branch:

CREATE PROCEDURE casse(IN score INT)
BEGIN
	CASE 
	WHEN scorce>=90 THEN
		SELECT 'A';
	WHEN scorce>=90 THEN
		SELECT 'B';
	ELSE
		SELECT 'C';	
END CASE;
END;
#---------------------------------

CREATE PROCEDURE casse(IN score INT)
BEGIN
 IF search_condition THEN
	statement_list
	ELSEIF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;
END;

#-------------Circulation-------------------
CREATE PROCEDURE casse(IN cnt INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<=cnt DO
	INSERT into admin(username,`password`) VALUES(xxx,xxx)
END WHILE;

END;

#-------------IF---------------
IF(condition, value_if_true, value_if_false)

-Practice-

#1 display system time (Note: date + time)
SELECT NOW()

#2. Calculate annual salary
SELECT last_name as name,salary,salary*1.2 as newsal
FROM employees



#3. Sort the names of employees by their initials and write the length of the names
SELECT last_ Name as name, length (last_name) as' length '
FROM employees
ORDER BY SUBSTR(name,1,1) ASC,LENGTH(name) DESC

#4 output < last_ name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earn ',ROUND(salary),' monthly but wants ',ROUND(salary*3)) as 'Dream Salary'
FROM employees

#5
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
Last_name  Job_id   Grade
king       AD_PRES  A
*/
SELECT last_ name,job_ ID and #case are also fields separated by commas
CASE job_id
	WHEN 'AD_PRES' THEN
		'A'
	WHEN 'ST_MAN' THEN
	'B'
	WHEN 'IT_PROG' THEN
		'C'
	WHEN 'SA_REP' THEN
	'D'
	WHEN 'ST_CLERK' THEN
	'E'
	ELSE
		'null'
END as 'grade'
FROM employees
ORDER BY grade ASC

Grouping function:

features:

  1. Ignore null values and do not count
  2. With distinct de duplication
  • AVG () numeric
  • Sum () numeric
  • Count() any data type
  • Max() any data type
  • Min() any data type
COUNT(*):

​ Why count (*) is recommended to count the total records? According to the characteristics of grouping function, null value is not included. When a field is null, omission may occur

From the official website:

InnoDB handles select count (*) and select count (1) operations in the same way. No performance differences.

Group query:

1. Single sub segment grouping:

Select grouping function, field after grouping
From table
[where filter criteria] - filter criteria before grouping
Group by grouped fields
[filtering after grouping] - a grouping function to filter groups that meet the conditions
[order by sort list]

2. Group by function

After group by, you can also use the function,

​ Example: group by employee name length, query the number of employees in each group, and filter the number of employees > 5;

SELECT LENGTH(last_name) as 'length', COUNT(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) >3

3. Multi field grouping

Example: query the average salary of employees in each department and type of work

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE not ISNULL(department_id)
GROUP BY department_id,job_id
ORDER BY AVG(salary) ASC

φ(゜▽゜*)♪

-Exercise one-

#1. Query each job_ The maximum value, minimum value, average value and sum of employee wages of ID are calculated by job_ ID ascending order
SELECT job_id, MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC
#2. Query the minimum wage of employees under each manager. The minimum wage cannot be less than 6000, and employees without managers are not included
SELECT manager_id, MIN(salary)
FROM employees
Where not isnull (manager_id) -- filter criteria before grouping
GROUP BY manager_id
Having Min (salary) > = 6000 -- filter qualified groups
ORDER BY manager_id ASC 
#3. Query the difference days between the maximum enrollment time and the minimum enrollment time in the employee table
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))
FROM employees

Joint query:

UNION

when use:The query results come from multiple tables, and multiple tables have no direct connection relationship, but the query information is consistent

characteristic:

  1. The number of columns to be queried is consistent, and the order should be consistent
  2. Union is de duplicated by default. If union all is used, duplicate items can be included

Connection query:

Cartesian product phenomenon:

SELECT b.*,g.*
FROM boys b,beauty g

Table 1 has m rows, table 2 has n rows, and the result = m * n;

reason: no valid connection condition

avoid: you can use where to add a valid connection condition

Internal connection:

Returns the portion of both tables that meet the criteria

  • Equivalent connection: the equal sign (=) operator is used in the connection condition for comparison
  • Non equivalent connection: the equal sign (=) operator is not used for comparison in connection conditions
  • Self join: equivalent to using one table as two tables
#(equivalent connection)
    #Query the number of departments in each city
    SELECT city,COUNT(*)
    FROM locations l,departments d
    WHERE l.location_id = d.location_id
    GROUP BY city
    #Query the Department name, department leader number and minimum wage of each department with bonus
    SELECT	d.department_name,d.manager_id ,MIN(e.salary) 
    FROM employees e,departments d
    WHERE e.department_id =d.department_id AND  NOT ISNULL(e.commission_pct)
    GROUP BY e.department_id
    #Query the country number with more than 2 departments in each country
    SELECT l.country_id
    FROM locations l,departments d
    WHERE l.location_id = d.location_id
    GROUP BY country_id
    HAVING COUNT(*)>2

#Unequal value connection
    #According to job_ Grades grades each employee's salary
    SELECT e.salary ,jg.grade_level
    FROM employees e,job_grades jg
    WHERE e.salary BETWEEN jg.lowest_sal AND jg.highest_sal
    ORDER BY e.salary ASC

	
#Self connection 
    #Select the name and employee number of the specified employee, as well as the name and employee number of his manager. The result is similar to the following format
    # employees Emp manager Mgr
    SELECT e.last_name as 'employees' ,e.employee_id as 'Emp' ,m.last_name as' manager',m.employee_id as'Mgr'
    FROM employees e,employees m
    WHERE e.manager_id =m.employee_id

External connection:

Query result of external link = all records in the master table + matching value in the slave table (if NULL, null will be displayed)

  • The left outer connection is the left join, and the main table is on the left

  • The right outer join is the main table on the right

  • Full join(MySQL does not support)

    • select <select_list>
        from A full join B
        on A.key=B.key
        where A.key is null or b.key is null
Sql99 syntax
Select -- query list
From table 1 -- [connection type]
Join table 2
On connection condition--
Where filter criteria
#Query boyfriend in beauty
SELECT g.`name` ,b.id,b.boyName
FROM beauty g LEFT JOIN boys b
on g.boyfriend_id =b.id
#Query which department has no employees
SELECT d.* ,e.employee_id,e.manager_id
FROM departments d LEFT JOIN employees e
on d.department_id = e.department_id
WHERE e.manager_id is null
#Query the employee information of the department named Sal and it
SELECT e.*,d.department_name
FROM departments d RIGHT JOIN employees e
on d.department_id = e.department_id
WHERE d.department_name in('SAL','IT')

Cross connect:

Cartesian product result


Subquery:

Classification:

  1. Scalar subquery – the result set has only one row and one column
  2. Column subquery – one column, multiple rows (single field)
  3. Row subquery – one row with multiple columns(Multiple rows and columns are allowed, multi field)
  4. Table subquery – multiple rows and columns

Location:

  1. Select + scalar subquery
  2. From + table subquery
  3. Where / having + (scalar + column query + row)
  4. exists

where /having :

  • scalar subquery
  • Column subquery
  • Row subquery

characteristic:

  • Scalar query with single line operator> < <> >= <=
  • Column subquery with multiline operatorin ,any ,some ,all
Scalar subquery:
#Return job_ The ID is the same as that of employee 141. Salary is more than that of employee 143_ ID, and salary
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141
) AND salary >(
	SELECT salary
	FROM employees
	WHERE employee_id=143
)
#Return the information of the employee with the lowest salary, last_ name,job_ id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary =(
	SELECT MIN(salary)
	FROM employees
)
Column subquery (multiple rows):

anyA comparison and a subquery return:

In/not in: equal to any one in the list

#Return location_ The ID is the name of all employees in the 1400 or 1700 department
SELECT last_name
FROM employees
WHERE department_id in (
	SELECT department_id
	FROM departments
	WHERE location_id in (1400,1700)
)
#Return other jobs_ ID is' it '_ Prog 'employee number, name and job of any employee with low salary in the type of work_ ID, and salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <>'IT_PROG';
Row subquery:
#Return the information of the lowest employee number and the highest salary
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
)

select:

scalar subquery
#Query the number of employees in each department and display the departments table
SELECT d.* ,(
	SELECT COUNT(*)
	FROM employees e
	WHERE d.department_id=e.department_id
)Statistics
FROM departments d   -- 27
/*
Error code why?
SELECT d.* ,(
	SELECT COUNT(*)
	FROM employees e
	WHERE d.department_id=e.department_id
) 'ANS'
FROM employees e,departments d
2889
*/

from:

#Query the salary grade of the average salary of each department
SELECT ag_dep.*,g.grade_level
FROM (
	SELECT AVG(salary) as ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_ Grades G -- alias directly. If as' g ', an error will be reported
ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal

Exists:

/*
Syntax:
Exists (complete query statement)
return:
0 or 1
*/
#Query boys without girlfriends
SELECT bo.* 
FROM boys bo
WHERE NOT EXISTS (
	SELECT boyfriend_id
	FROM beauty g
	WHERE bo.id =g.boyfriend_id
)
#Connection query
SELECT b.*
FROM boys b LEFT JOIN beauty g
on b.id= g.boyfriend_id
WHERE g.name is NULL

-Practice-

--1. Query the name and salary of employees in the same department as zlotkey
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=(
		SELECT department_id
		FROM employees
		WHERE last_name ='Zlotkey'
)#34
--2. Query the employee number, name and salary of employees whose salary is higher than the average salary of the company
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
)#51
--3. 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 (
	SELECT AVG(e.salary) ag_sal,e.department_id eid
	FROM employees e
	GROUP BY department_id
) tab1 INNER JOIN employees e1
on e1.department_id = tab1.eid
WHERE e1.salary >tab1.ag_sal
ORDER BY e1.employee_id #38

--4. Query the employee number and name of the employee whose name contains the letter U in the same department
SELECT employee_id,last_name
FROM employees
WHERE department_id in(
	SELECT DISTINCT department_id
	FROM employees 
	WHERE last_name LIKE '%u%'
)#96

--5. Query the location of the Department_ Employee number of the employee working in the Department with ID 1700
SELECT employee_id
FROM employees
WHERE department_id in(
	SELECT department_id
	FROM departments
	WHERE location_id=1700
)#18


--6. Query the name and salary of employees whose manager is king

SELECT last_name,salary
FROM employees e
WHERE manager_id in(
	SELECT employee_id
	FROM employees
	WHERE last_name ='K_ing'
)#14

--7. Query the name of the employee with the highest salary and ask first_ Name and last_ Name is displayed as a column, and the column name is last name
Select concat (first_name, last_name) as "name"
FROM employees
WHERE salary =(
	SELECT MAX(salary)
	FROM employees
)

Paging query:

Select -- query list
From table 1 
[type] join table 2 -- [connection type] 
On connection condition--
Where filter criteria
Group by group field
Filtering after grouping
Order by sort field
limit offset ,size;
offset = (page-1)*size

DML:

data manipulation language

Insert:

Insert into table name (column name,...) values (value 1) -- one
Insert into table name set field = value, field = value,... -- two
#1. The inserted value type is consistent with the column type, and the order can be changed. Non nullable values must be inserted
#2. Method 1 supports inserting multiple rows + subqueries at one time

modify:

Update table name: set field = value, field = value [where filter criteria]-- one
--2 multi table update
Update table 1 alias 
Left|right|inner join table 2 alias 
On connection condition  
Set field = value, field = value 
[where screening criteria];

Delete:

Delete from table name [where filter criteria] [limit entries]

Truncate table name

#Multi table delete instance
delete g
from beauty g
inner join boys b 
on g.boyfriends_id = b.id
where b.boyName='xxx';

The difference between delete and truncate:

  1. After truncate is deleted, if it is inserted again, the identification column starts from 1
    After delete, if you insert again, the identification column starts from the breakpoint
  2. Delete can add filter criteria
    Truncate cannot add filter criteria
  3. Truncate is more efficient
  4. Truncate has no return value
    Delete can return the number of rows affected
  5. Truncate cannot be rolled back
    Delete can be rolled back

DDL:

data defination language

Library:

#Create
CREATE DATABASE if NOT EXISTS boos;
#Rename
RENAME DATABASE boos TO books;
#Set character set
ALTER DATABASE boos CHARACTER SET gbk;
#Delete Library
DROP DATABASE boos;

Table:

Drop table if table name exists
Create table [if not exists] table name(
	Field name field type (length) [constraint],
	Field name field type [constraint],
	....
	Field name field type [constraint] 
)

create table if not exists books(
	id INT(2) AUTO_INCREMENT,
	bname VARCHAR(20),
  	privce DOUBLE ,
	PRIMARY KEY (id)
)

1.Add column
Alter table name add column column name type [first after field name];

ALTER TABLE books ADD COLUMN author INT;

2.Modify the type or constraint of the column
Alter table name modify column name new type [new constraint];

alter table books modify column name DOUBLE UNIQUE;

3.Modify column name
Alter table name change column old column name new column name type;

At the same time, the type can be modified

alter table books change bname name varchar(30);

4 .Delete column
Alter table name DROP column name;

ALTER TABLE books DROP COLUMN NAME

5.Modify table name
Alter table name rename [to] new table name;

alter table books rename to new_books;

6. replication of tables

#1. Copy table structure
Create table name like old table;
CREATE TABLE copyed LIKE new_books

#2. Copy table structure + data
Create table table name 
Select query list from old table [where filter];

CREATE TABLE copy_books
SELECT * FROM new_books WHERE 1=1;

Data type:

Integer:tinyint、smallint、mediumint、int/integer、bigint

  • ① Both unsigned and signed can be set. They are signed by default. Unsigned (no sign number) can be set through unsigned
  • length
    • The size of the stored data has been determined according to the data type, and the length represents the maximum width of the display. If it is not enough, fill it with 0 on the left, but it needs to be matched with zerofill, andThe default is unsigned integer

Floating point:

  • Number of fixed points: decimal (m, d)
  • Floating point numbers: float (m, d), double (m, d)

M = integer digits + decimal digits

D = number of decimal places

In mysql8: if d = 3 and 12.456 is inserted, it will be rounded to 12.5

character:char、varchar、binary、varbinary、enum、set、text、blob

char: char (m), the maximum length cannot exceed m, where M can be omitted,The default is 1

varchar: variable length character, written as varchar (m), and the maximum length cannot exceed M,Where m cannot be omitted

Date type:Year, date, time, datetime

6ZNBuT.png

Constraints:

  • NOT NULL: non empty, the value of this field is required
  • UNIQUE: unique, the value of this field cannot be repeated
  • DEFAULT: by default, the value of this field does not need to be manually inserted. There is a default value
  • CHECK: check, MySQL does not support
  • PRIMARY KEY: primary key. The value of this field cannot be repeated and is not empty. Unique + not null
  • FOREIGN KEY: foreign key. It is used to restrict the relationship between two tables. When adding foreign key constraints from the table, it is used to reference the value of a column in the main table

Primary key, foreign key:

Primary key: a table can have at most one primary key, but can have multiple unique keys

Foreign key:

  1. Setting foreign key relationships from tables
  2. The foreign key column type of the slave table and the associated column type of the master table are required to be consistent or compatible
  3. The associated column of the main table must be a key (primary key, unique)
  4. To insert data, insert the primary table first, and then delete the secondary table
    • When deleting data, delete the secondary table first, and then the primary table
Support type Can I use constraint name
Column level constraint Foreign key constraints have no effect may not
Table level constraints Except non empty and default Yes, but not valid for the primary key
Create table table name(
    #Column level constraint
	Field name field type is not null, # not null
	Field name field type primary key, # primary key
	Field name field type unique, # unique
	Field name field type default value, # default
    #Table level constraints
	Constraint constraint name foreign key references main table (referenced column),
    constraint fk_stu_major foreign key(majorid) references major(id),
    
    Constraint constraint name [constraint] (field), -- constraint name = alias, variable
    constraint pk  primary key(id)
)
Show index from table name

Identification column:

Self growth column:

  • A table can have at most one self growing column
  • Self growing columns can only support numeric types
  • Self growing column must be a key

TCL

Transaction control language

Transaction:One or more SQL statements form an execution unit, and a group of SQL statements are either executed or not executed

Transaction characteristics:

  • A: Atomicity a transaction is an indivisible whole, either all or none
  • C: Consistency a transaction can switch data from one consistent state to another
  • 1: Isolation a transaction is not disturbed by other transactions, and multiple transactions are isolated from each other
  • D: Durability once a transaction is committed, it will be permanently persisted locally
set autocommit=0
start transaction 
delete from account where id =25
Savepoint a # set savepoint
delete from account where id =28
rollback to a; # Rollback to a

Focus on introducing transaction operations in spring

other

View:

what: virtual table. Data generated dynamically through the table. Only SQL logic is saved, and results are not saved

where:

  • The same query results are used in multiple places
  • The SQL statements used are complex
#Create
create VIEW myview1 
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 myview1 where last_name like '%a%'

#Modification
Create or replace view [view name]
as
[query statement]


#Delete drop view [view name]

Variable:

System variables:

  • global variable: at the server level, you must have super permission to assign values to system variables. The scope is the entire server, that is, it is valid for all connections (sessions)

  • Session variable: the server provides system variables for each connected client, and the scope is the current connection (session)

  • show 【global|session 】variables like '';
      Set [global session] variable name = value

Custom variables:

  • User variables:Valid for current connection (session)

  • Local variables:Only valid in begin end where it is defined

    • Declare variable name type [default value];

Stored procedure:

what: a set of precompiled SQL statements

why use:

  • Improve code reusability
  • Simplify operation
  • Reduce compilation times and database server connection times
Create procedure stored procedure name (parameter list)
begin
[SQL statement]
end
Parameter list:
Parameter mode parameter name parameter type
In    stuname  varchar(20)
Parameter mode:
  • in: this parameter can be used as input
  • out: this parameter can be used as a return value
  • inout: enter + return value

call: call stored procedure name (argument list)

Delimiter end tag:

Delimiter $-- arbitrary

Case:

IN:

CREATE DEFINER=`root`@`localhost` PROCEDURE `logincheck`(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
	#Routine body goes here...
	DECLARE result VARCHAR(20) DEFAULT '';
	SELECT COUNT(*) INTO result
	FROM admin
	WHERE admin.username =username AND
	admin.`password` =password;
	Select if (result > 0, 'success',' failure ');
END

CALL logincheck('admin1','123')
#In JDBC
db.connect( "db_host" ).execute_sql( "CALL logincheck('admin1','123')" );

OUT:

CREATE DEFINER=`root`@`localhost` PROCEDURE `findboy`(In butyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	#Routine body goes here...
	SELECT bo.boyName INTO boyName
	FROM boys bo
	JOIN beauty g on bo.id = g.boyfriend_id
	WHERE g.name = butyName;
END

Call findboy ('xiaozhao ', @ boyname);
SELECT @boyName

Function:

difference:

  • Stored procedure: there can be 0 returns or multiple returns. Batch operation
  • Function: there is one and only one, which is suitable for processing data and returning results
Create function ` function name '(parameter list)
	Returns return type
BEGIN
	#Routine body goes here...
	
	RETURN 0;
END;
--------------------
CREATE DEFINER = CURRENT_USER FUNCTION ``()
	RETURNS integer
BEGIN
	#Routine body goes here...
	
	RETURN 0;
END;

Select function name (parameter list)

DCL:

data control language
User management:

create user zhang3 identified by '123123';

update mysql.user set password=password('123456') where user='li4';
flush privileges;

update mysql.user set user='li4' where user='wang5';flush privileges;

drop user li4 ;

Permission management:

grant all privileges on *.* to [email protected]'%'  identified by '123';

REVOKE ALL PRIVILEGES ON mysql.* FROM  [email protected] # Revoke all permissions of the whole database and table

REVOKE select,insert,update,delete ON mysql.* FROM  [email protected] # Withdraw the insert, delete, modify and query permissions of all tables under the MySQL database

Link:https://pan.baidu.com/s/1EeBnOhgMSJjvn35RZtz5sw
Extraction code: 3mji
After copying this content, open Baidu online disk mobile app, which is more convenient to operate

Recommended Today

SQL exercise 20 – Modeling & Reporting

This blog is used to review and sort out the common topic modeling architecture, analysis oriented architecture and integration topic reports in data warehouse. I have uploaded these reports to GitHub. If you are interested, you can have a lookAddress:https://github.com/nino-laiqiu/TiTanI recorded a relatively complete development process in my hexo blog deployed on GitHub. You can […]