Issue 17 – What is a MySQL database? This one dry article is enough!

preface

Why learn MySQL? Because MySQL is one of the most popular relational database management systems, it is the best software for Web applications. The SQL language used by MySQL is the most commonly used standardized language for accessing databases.

Rest assured, this is for all of you: web developers, software developers and hobbyists.

1. Introduction to MySQL

What is a database?

1. Database, it is according to the data structure to organize, store and manage data warehouse.
2. Database management system refers to the software system that manages data in a database system.

Let me put together a mind map:

Details:

• Installation and configuration, common commands, operation database;
• Integer and floating point, date and time and character;
• Create and view the database table, modify the database table, delete the database table;
• Non-null constraint, primary key constraint, Unique constraint, default constraint, foreign key constraint;
• Management tools:MySQL Workbench, SQLyog;
• Insertion and automatic numbering of single table data records, update of single table data records, delete single table data records, query of single table data records, group the query results, sort the query results, limit the number of query records through the limit statement;
• Mysql operators, numeric functions, character functions, date and time functions, aggregate functions, information functions and encryption functions;
• The subquery that is used when the record is inserted, using the subquery raised by the comparison operator
• Multi-table join, inner join, outer join, self-join, multi-table update, multi-table delete
• Create and use custom functions
• Create a stored procedure and use the stored procedure

Mysql’s official website:

Installation package download :(installation operation)

Click Install:

Product configuration operations:

Open the services box with Win + R and type services.msc

2. Mysql directory structure

1. binDirectory: Used to store some executable files
2. includeDirectory: Used to store the contained header files
3. libDirectory: Used to store some library files
4. shareDirectory: Used to store error messages, character set files, etc
5. dataDirectory: Used to place some log files and databases
6. my.iniFile: configuration file of the database

Start and Stop:

Mysql parameters:

parameter	describe
-u	The user name
-p	password
-V	Output the version information and exit
-h	The host address

3. Common commands

Commands for changing user passwords:

The mysqladmin command is used to change the password of a user. The mysqladmin command format is mysqladmin -u user name -p old password password new passwordCopy the code

Command to display the database

show databases;
Copy the code

Use database commands

Use Specifies the name of the databaseCopy the code

Displays information about the current connection

1. Show the currentConnected database:select database();
2. Show the currentServer Version:select version();
3. Show the currentDate/time:select now();
4. Show the currentThe user:select user();

4. Operate database (create, modify, delete)

SQL > create database;

create database [if not exists] db_name
[default] character set [=] charset_name
Copy the code

create database database_name;
Copy the code

Alter database syntax format:

alter database db_name 
[default] character set [=] charset_name
Copy the code

Delete database syntax format:

drop database [if exitsts] db_name;
Copy the code

5. Database – Data type

Understand data types :(with library management)

List of Book Categories:

Category Number (category) Category Name (category) Parent category (PARENt_ID) 1 Computer 0 2 Medical 0Copy the code

Book Information Table:

Book Number (book_id) Category Number (book_category_id) Title (book_name) Author (Price) Publisher (Press) Pubdate (Store)Copy the code

Loan Information Sheet:

Book NUMBER (book_id) Card number (card_id) Loan Date (Borrow_date) Return date (return_date) Return statusCopy the code

Reader information sheet

Id Card number (card_id) Name (name) Sex (Age) Telephone Number (Tel)Copy the code

Data type:

Integer type: tinyint-1 bytes Smallint-2 bytes Mediumint-3 bytes int-4 bytes Bigint-8 bytes

Floating-point and fixed-point types:

Float -4 bytes double-8 bytes decimalCopy the code

Date and time type:

Character:

6. Operation of database table structure

• Create and view data tables

Create table create table < table name > (column name 1 data type [column level constraint][default], column name 2 data type [column level constraint][default],... [table level constraint]);Copy the code

• View database table:

show tables [from db_name];
Copy the code

• View the basic structure of the data table:

show columns from tbl_name; Describe < table name > /DESC< table name >Copy the code

show create table tbl_name;
Copy the code

• Modifying a database table

Add columns:

Alter table < table name > add new column name < > < > data types [constraints] [first | after existing column names].Copy the code

Modify column name:

Alter table < table name > change < old column name > < new column name > < new data type >;Copy the code

Alter column data type:

Alter table < table name > MODIFY < column name > < data type >Copy the code

Changes the column placement

The alter table < table name > MODIFY < 1 > > < data type FIRST | AFTER < 2 > columnCopy the code

Delete the columns:

Alter table < table name > drop < table name >;Copy the code

Alter table name:

Alter table < old table name > RENAME [TO] < new table name >;Copy the code

• Drop a database table

Drop table [if exists] table 1, table 2... Table n;Copy the code

View table partition

Create table partition (partition by);

Use the values less than operator to define partitions

create table bookinfo(
 book_id int,
 book_name varchar(20)
)
partition by range(book_id)(
 partition p1 values less than(20101010),
 partition p3 values less than MAXVALUE
);
Copy the code

7. The subquery

select price from bookinfo where book_id = 20101010;

select * from readerinfo;

update readerinfo set balance = balance-(select price from bookinfo where book_id = 20101010) * 0.05 where card_id = '2323232342sxxxxx';
Copy the code

What is a subquery?

It is a query statement nested within other SQL statements.

select * from table1 where col1 = (select col2 from table2);
Copy the code

insert into bookcategory(category,parent_id)values('x',2),('y',2); insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store) values (45245244, 6, 'x', 'such as 1, 2, 3', 115, 'press', '2020-06-01', 10), (45342545, 6, 'y', '1, 2', 27.8, 'press', '2020-07-01', 5); update readerinfo set balance = 500 where card_id = '683246'; insert into borrowinfo(book_id,card_id,borrow_date,return_date,status) values (35452455, '5724154', '2020-10-10', '2020-11-10', 'no');Copy the code

Query the loan information sheet, display the loan record of the book borrowed xx

select * from borrowinfo where book_id = (select book_id from bookinfo where book_name = 'xx');
Copy the code

Query a book information table that displays information about all books whose prices are less than the average book price

select * from bookinfo where price < (select round(avg(price),2) from bookinfo);
Copy the code

Query a book information table that displays information about all books whose category is not ‘database’

Select * from bookinfo WHERE book_category_id<>(select category_id from bookcategory WHERE category = 'c002 ');Copy the code

Query book information table to display information about all books whose book category is’ computer ‘

select * from bookcategory;

select * from bookinfo where book_category_id = ANY(select category_id from bookcategory where parent_id = 1);


select * from bookinfo where price > ANY (select price from bookinfo where book_category_id =4);  

select * from bookinfo where price > ALL (select price from bookinfo where book_category_id =4); 
Copy the code

Query book information table to display information about all books whose book category is’ 2 ‘

The subquery following in returns a column of data, Select * from bookinfo where book_category_id in (select category_id from bookcategory WHERE category_id = 'c002' and category_id = 'c002' parent_id = 2); select * from bookinfo where book_category_id = any (select category_id from bookcategory where parent_id = 2);Copy the code

Check whether there is a category of ‘y’ in the book category table. If so, check the book information table

select * from bookinfo where exists (select category_id from bookcategory where category='y');

select * from bookinfo where exists (select category_id from bookcategory where category='x');
Copy the code

The INSERT into SELECT statement copies data from a table and then inserts the data into an existing table.

insert into table2 select * from table1;
Copy the code

You need to create a fine record information sheet with the following information: book number, ID number, due date, actual return date, and the amount of the fine

The records come from readers who have not returned their books beyond the due date on the loan information sheet

create table readerfee( book_id int, card_id char(18), return_date date, actual_return_date date, Book_fee decimal(7,3), primary key(book_id,card_id)); Select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = 'no '; insert into readerfee(book_id,card_id,return_date) select book_id,card_id,return_date from borrowinfo where Datediff (sysdate(),return_date)>0 and status = 'no '; select * from readerfee;Copy the code

The reader whose ID number is 5461XXXXXXX will return the book 20201101 over the limit, and fulfill the following requirements according to the description:

• Update the Lending information sheet to update the lending status to ‘Yes’.
• Update the fine record information form, update the actual return date and the fine amount, the fine amount will be deducted 0.2 yuan for each day beyond the date.

Update borrowinfo set status = 'yes' WHERE book_id = 20201101 and card_id =' 5461XXXXxxx '; select * from borrowinfo; update readerfee set actual_return_date=sysdate(), Book_fee =datediff(sysdate(),return_date)*0.2 WHERE book_id = 20201101 and card_id = '5461XXXXxxx '; select * from readerfee;Copy the code

8. Mysql constraints

It is a restriction that ensures the data integrity and uniqueness of a table by limiting the data in the rows or columns of the table.

Table structure:

Books (Book_id, category book_category_id, title book_name, author)

There are several constraint types commonly used in mysql:

Constraint type	Not null constraint	Primary key constraint	The only constraints	The default constraints	Foreign key constraints
The keyword	not null	primary key	unique	default	foreign key

Book Information Table:

(Book_id, book_category_id, book_name, author, Author, Price, Press, PubDate, Stock Store)

List of Book Categories:

(Category number category_id – Primary key, Category Name Category – Unique, Parent category PARENt_id – Non-empty)

Reader Information Sheet:

(ID card no. Card_id, name, Sex, Age, age, tel, balance)

Loan Information Sheet:

(Book_id, ID card number card_id, Loan date Borrow_date, return date return_date, return status)

Not null constraint

The null field value can be null

The not NULL field value cannot be null

Not null constraint

A non-empty constraint means that the value of a field cannot be empty. If the user does not specify a value when adding data to a field that uses a non-null constraint, the database system will report an error.

Column name data type not nullCopy the code

Add a non-null constraint when creating a table

create table bookinfo(
	book_id int,
	book_name varchar(20) not null
);
Copy the code

Delete a non-null constraint

alter table bookinfo modify book_name varchar(20);
Copy the code

Add a non-null constraint by modifying the table

alter table bookinfo modify book_name varchar(20) not null;
Copy the code

Primary key constraint

Primary key constraint: The data in the primary key column is required to be unique and cannot be empty. The primary key can uniquely identify a record in the table.

Primary key types:

Primary keys are classified into single-field primary keys and multi-field combined primary keys

Single-field primary key: consists of a single field

Specify the primary key column name data type primary key; [Constraint < constraint name >] Primary key(column name);Copy the code

Add primary key constraints when creating tables

create table bookinfo(
	book_id int primary key,
	book_name varchar(20) not null
);
Copy the code

create table bookinfo(
	book_id int,
	book_name varchar(20) not null,
	constraint pk_id primary key(book_id)
);
Copy the code

Delete the primary key constraint

ALTER TABLE bookinfo DROP PRIMARY KEY;
Copy the code

Add primary key constraints by modifying the table

ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);
Copy the code

Multi-field union primary key, compound primary key

A primary key is composed of multiple fields. Primary key(1, 2,… Field n);

create table borrowinfo(
book_id int,
card_id char(18),
primary key(book_id,card_id)
);
Copy the code

Add a primary key to a column by modifying the table

create table bookinfo(
 book_id int,
 book_name varchar(20) not null
);

alter table bookinfo modify book_id int primary key;
alter table bookinfo add primary key(book_id);
alter table bookinfo add constraint pk_id primary key(book_id);
Copy the code

The only constraints

A unique constraint requires that the column be unique and allowed to be null. A unique constraint ensures that no duplicate values occur in one or more columns.

Grammar rules:

[constraint < constraint name >] UNIQUE (< column name >)Copy the code

Add unique constraints when creating tables

CREATE TABLE bookinfo( book_id INT PRIMARY KEY, book_name VARCHAR(20) NOT NULL UNIQUE ); Or:  create table bookinfo( book_id int primary key, book_name varchar(20) not null, constraint uk_bname unique(book_name) );Copy the code

Add unique constraints by modifying the table

alter table bookinfo modify book_name varchar(20) unique;

ALTER TABLE bookinfo ADD UNIQUE(book_name);

alter table bookinfo 
add constraint uk_bname unique(book_name);
Copy the code

Delete unique constraints

ALTER TABLE book_info DROP  KEY  uk_bname;

ALTER TABLE book_info DROP  INDEX  uk_bname;
Copy the code

The difference between unique and primary key constraints

1. There can be more than one tableuniqueDeclaration, but only oneprimary keyThe statement
2. Declared asprimary keyColumns of the
3. Declared asuniqueAllows null values for columns of

The default constraints

The default constraint is the default value for a column

Column name Data type default Default valueCopy the code

Add default constraints when creating tables

CREATE TABLE bookinfo(book_id INT PRIMARY KEY, press VARCHAR(20) DEFAULT 'publisher');Copy the code

Add default constraints by modifying the table

ALTER TABLE bookinfo ALTER COLUMN press SET DEFAULT ' '; Alter table bookinfo modify press varchar(10) default 'publisher ';Copy the code

Delete default Constraints

alter table bookinfo modify press varchar(20);

ALTER TABLE bookinfo 
ALTER COLUMN press DROP DEFAULT;
Copy the code

Foreign key constraints

A foreign key is used to establish a link between the data of two tables. It can be one or more columns, and a table can have one or more foreign keys.

Foreign keys correspond to referential integrity. A foreign key of a table can be null; otherwise, each foreign key must be equal to some value of the primary key in the other table.

Function: Maintain data consistency and integrity.

Add foreign key constraints when creating tables

CREATE TABLE bookcategory(category_id INT PRIMARY KEY, category VARCHAR(20), PARENt_id INT); CREATE TABLE bookinfo(book_id INT PRIMARY KEY, book_category_id INT, CONSTRAINT fk_cid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id) );Copy the code

Add foreign key constraints by modifying the table

ALTER  TABLE  bookinfo  
ADD  FOREIGN KEY(book_category_id) REFERENCES  bookcategory(category_id);
Copy the code

Delete the foreign key constraint

ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;
Copy the code

Reference operations for foreign key constraints

Cascade, deletes or updates rows from the parent table and automatically deletes or updates matched rows in the child table

create table bookinfo(
 book_id int primary key,
 book_category_id int,
 constraint fk_cid foreign key (book_category_id) references bookcategory(category_id) on delete cascade);
Copy the code

Create a library management system table

List of book categories

create table bookcategory(
category_id int primary key,
category varchar(20) not null unique,
parent_id int not null
);
Copy the code

Book information sheet

create table bookinfo( book_id int primary key, book_category_id int, book_name varchar(20) not null unique, Author varchar(20) not null, price float(5,2) not null, press varchar(20) default 'machine press ', pubdate date not null, store int not null, constraint fk_bcid foreign key(book_category_id) references bookcategory(category_id) );Copy the code

Reader information sheet

Create table readerInfo (card_id char(18) primary key, name vARCHar (20) not null, sex enum(' male ',' female ',' secret ') default 'secret ', Age tinyint, tel char(11) not null, balance decimal(7,3) default 200);Copy the code

Loan information sheet

create table borrowinfo(

book_id int,
card_id char(18),
borrow_date date not null,
return_date date not null,
status char(11) not null,
primary key(book_id,card_id)

);
Copy the code

9. Operations recorded in database tables

Insertion of single table data records

Syntax format:

insert into table_name(column_list) values(value_list);
Copy the code

Inserts data for all columns of the table

insert into bookcategory
(category_id,category,parent_id)values
(1,'x',0);

insert into bookcategory values(2,'y',0);
Copy the code

Inserts data for the specified column of the table

Insert into readerinfo (card_id,name,tel) VALUES ('4562135465',' 4651354651');Copy the code

Insert multiple records simultaneously

insert into bookcategory(category_id,category,parent_id)values(3,'x',1),(4,'y',1),(5,'z',2);
Copy the code

Inserts the query results into the table

insert into bookcategory select * from test where id>5;
Copy the code

Automatically add

The property value of the set table is automatically increased:

Column name Data type AUTO_INCREMENTCopy the code

Add increment columns when creating a table

create table bookcategory_tmp(
	category_id int primary key auto_increment,
	category varchar(20) not null unique,
	parent_id int not null
)auto_increment=5;
Copy the code

Test the increment column

insert into bookcategory_tmp(category,parent_id)values('dadaqianduan',0);
Copy the code

Remove the increment column

alter table bookcategory_tmp modify category_id int;
Copy the code

Add a self-increment column

alter table bookcategory_tmp modify category_id int auto_increment;
Copy the code

Modify the start value of the increment column

alter table bookcategory_tmp auto_increment = 15; Insert into bookcategory_tmp(category,parent_id) VALUES (' category_',0);Copy the code

Delete the foreign key of the book information table

alter table bookinfo drop foreign key fk_bcid;
Copy the code

Add automatic numbering to the book category table

alter table bookcategory modify category_id int auto_increment;
Copy the code

Recovery associated

alter table bookinfo add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);
Copy the code

Update of single table data records

Inserts a lending message into the lending information table

insert into Borrowinfo (book_id card_id, borrow_date return_date, status) values (20202010465687, 4, '2020-11-29', '2020-12-29', 'no');Copy the code

Update the balance in the reader information sheet

Select price from bookinfo where book_id = 20202010;Copy the code

Update the balance

Update readerinfo set balance = balance-79.80 *0.05 WHERE card_id = '46516874'; select * from readerinfo;Copy the code

Update the inventory of the book information sheet

update bookinfo set store = store -1 where book_id = 20150201;
select * from bookinfo;
Copy the code

Delete a single table data record

Deletes the record for the specified condition

delete from readerinfo where card_id = '46461265464565';
Copy the code

Delete all records from a table

delete from readerinfo; truncate table readerinfo; fastCopy the code

To delete all records in a table, use the TRUNCate table statement. Truncate deletes the original table and creates a new table with the syntax structure:

truncate table table_name
Copy the code

Query pediatrics category number

Select category_id from ccid where cCID =' ccid ';Copy the code

Delete the information about book 5

delete from bookinfo where book_category_id = 5;
Copy the code

Delete the pediatrics category from the book category table

Delete from bookcategory where category = 'pediatrics ';Copy the code

Query a single table data record

Query all columns

select * from bookcategory;

select category_id,category,parent_id from bookcategory;
Copy the code

Query a specified column

select category from bookcategory;

select category_id,category from bookcategory;
Copy the code

Query the records of a specified condition

Select book_id,book_name,price from bookinfo where press=' book_id ';Copy the code

The query result must be unique

select distinct press from bookinfo;
Copy the code

Check the null value

select * from readerinfo where age is null;
Copy the code

grouping

Count the number of male readers in the readership information sheet

Select count(*) from readerinfo where sex=' male ';Copy the code

Group the records in the reader information table by gender

select sex from readerinfo group by sex;
Copy the code

Group the records in the reader information table by gender and count the number of people in each gender

select sex,count(*) from readerinfo group by sex;
Copy the code

Group the records in the reader information table by gender

select sex from readerinfo group by sex having count(sex)>2;
Copy the code

The sorting

Sort the results of the query through the order by clause

The order by column names [asc | desc]Copy the code

Sorting directions:

1. The sort can be ascending or descending, and the default is ascending
2. ascendingasc
3. Descending orderdesc

A single order

select * from bookinfo order by price;
Copy the code

Multi-column sorting

select * from bookinfo order by price,store;
Copy the code

Specify sort direction

select * from bookinfo order by price,store desc;
Copy the code

The limit statement limits the number of records to be queried

The first three lines record

select * from bookinfo limit 3;
Copy the code

The last two records starting with record 3

Select * from bookinfo limit 2,2; select * from bookinfo limit 2 offset 2;Copy the code

Insert into bookinfo (book_id, book_category_id book_name, author, price, the press, for its pubdate, store) values (454235424, 4, '123', 'XXX', 85.8 'press', '2020-04-01', 10), (452454542, 4, '456', 'XXX', 35.5 'press', '2020-08-01', 20), (454578754, 4, '789', 'XXX ', 46.6,' Publishing House ', '2020-05-01',8);Copy the code

Group book information according to inventory, count the number of inventory in each group, and then sort by inventory in descending order, and view the first four records in the result

select store,count(*)from bookinfo 
group by store 
order by store desc 
limit 4;
Copy the code

Operators and functions

MySQL has the following operators:

1. Arithmetic operator
2. Comparison operator
3. Logical operator
4. An operator

Arithmetic operator

Comparison operator

Logical operator

An operator

Operator priority

Reader’S ID number, name, phone number, balance.

select card_id, name, tel, balance from readerinfo where balance-200<=0;
Copy the code

View the reader information table, the balance of more than 200 readers information.

select * from readerinfo where balance>200;
Copy the code

View the reader information table, the balance is not equal to 200 reader information.

select * from readerinfo where balance <> 200;
Copy the code

View information about readers whose ages are not empty in the reader information table.

select * from readerinfo where age is not null;
Copy the code

Check the reader information sheet for any reader with a balance between 350 and 450.

select * from readerinfo where balance between 350 and 450;
Copy the code

select * from readerinfo where name in('dada','dada1','dada2'); Select * from readerinfo where name like '_'; select * from readerinfo where tel like '135%'; select * from bookinfo where price>50 and store<5; Select * from bookinfo where price>80 or press = 'bookinfo '; select * from bookinfo where price not between 50 and 100;Copy the code

Numerical function

Ceil returns the minimum integer value greater than x

The select ceil (28.55); / / 29Copy the code

Floor returns the maximum integer value less than x

The select floor (28.55); / / 28Copy the code

Round Returns the integer closest to the argument x, which is rounded off

Select round (28.55); / / and select round (28.55, 1), round (28.55, 0), round (28.55, 1); / / 28.6 29 and 30Copy the code

The truncation function

The select truncate (28.55, 1), truncate (28.55, 0), truncate (28.55, 1); / / 28.5 and 20Copy the code

Modulo, return the remainder of x divided by y

Select the mod (11); / / 1Copy the code

select book_id,book_name,price, round(price) from bookinfo;

select * from bookinfo where mod(book_id,2)=0;
Copy the code

Character function

String conjunction

select concat('hello','world');
select concat_ws('-','hello','world');
Copy the code

Letter conversion case

select lower('Hello World');
select upper('Hello World');
Copy the code

For the length of the

select length(' hello ');
Copy the code

Delete the blank space

select ltrim(' hello '),length(ltrim(' hello '));
select rtrim(' hello '),length(rtrim(' hello '));
select trim(' hello '),length(trim(' hello '));
Copy the code

Intercept string

Select substring (' hello world ', 1, 5); Select substring (' hello world ', 5, 2);Copy the code

Gets a string of the specified length

select left('hello world', 5); // hello
select right('hello world', 5); // world
Copy the code

Replace function

select replace('hello world','world','mysql'); // hello mysql
Copy the code

Formatting function

Select the format (1234.5678, 2), and the format (1234.5, 2), the format (1234.5678, 0); //1234.57 1234.50 12345 select book_id,book_name,format(price,2)from bookinfo;Copy the code

Date and time functions

View the current system date

select curdate(); / / 2020-02-02Copy the code

select curdate()+0;

select curtime()+0;
Copy the code

View the current system date and time

select now(); // 2020-10-10 12:12:12 select sysdate(); / / the 2020-10-10 12:12:12Copy the code

date_add(date,interval expr type): year,month,day,week,hour

Select date_add('2020-01-01', interval 5 month); Select datediff('2020-02-10','2020-02-01'); Select date_format('2020-02-01', '%Y%m');Copy the code

Aggregate function (grouping function)

| | | names describe | | avg () returns a list of average | | the count () | | returns a column lines () | | Max back to a maximum of one column | | min () | returns a list of the minimum | | the sum () and | | to return to a particular column value

Find the average price of all books in the book information table.

select avg(price) from bookinfo;
Copy the code

Find the total price of all books in the book information table.

select sum(price) from bookinfo;
Copy the code

Find the maximum inventory in the book information table.

select max(store) from bookinfo;
Copy the code

Find the minimum inventory in the book information table.

select min(store) from bookinfo;
Copy the code

Find out how many kinds of books are in the book information table.

select count(*) from bookinfo;
Copy the code

Group by category, query how many kinds of books under each category and the total inventory of books in each category.

Select book_category_id as' book_id ',count(book_id) as' book_id ', Sum (store) as' total inventory 'from bookinfo group by book_category_id;Copy the code

Information function and encryption function

System information function

Select version(); Select connection_id(); Select schema(); Select user();Copy the code

Encryption function

select md5('test');

create table myuser(
	username varchar(10),
	password varchar(35)
);

insert into myuser values('user1',md5('pwd1'));

select * from myuser;

select * from myuser where username = 'user1' and password = md5('pwd1');

select password('rootpwd');

set password = password('rootpwd');

select user,authentication_string from mysql.user;
Copy the code

11. Join multiple tables

Multi-table join queries fetch data from multiple tables.

From the book information table :(book number book_id, category number book_category_id, title book_name)

From the book category table :(category number category_id, category name category, parent category parent_id)

Get table :(book_id, book_name, category name)

Syntax for joining multiple tables:

table_reference
[INNER] JOIN | {LEFT|RIGHT} [OUTER] JOIN
table_reference
on conditional_expr
Copy the code

Multi-table connection obtains the book number, book name and book category by viewing the book information table and book category table

select book_id,book_name,category from bookinfo inner join bookcategory  on bookinfo.book_category_id = bookcategory.category_id;
Copy the code

In the connection

Select data from multiple tables according to join conditions, display the data rows matching the join conditions in these tables, and combine them into new records. (Inner join is common to both)

The syntax structure of an inner join:

select column_list from t1 [INNER] JOIN t2 ON join_condition1 [INNER] JOIN t3 ON join_condition2 ... ]  where where_conditions;Copy the code

Due to the need of books lending statistics, want to query not returned books book number, book name, ID number, name, telephone, return date, whether to return.

select borrowinfo.book_id,book_name,borrowinfo.card_id, name, tel, return_date, status from borrowinfo inner join bookinfo on borrowinfo.book_id = bookinfo.book_id inner join readerinfo on Borrowinfo. Card_id = readerinfo.card_id where borrowinfo. Status = 'no '; select t1.book_id,book_name,t1.card_id, name, tel, return_date, status from borrowinfo t1 join bookinfo t2 on t1.book_id = t2.book_id join readerinfo t3 on t1.card_id = t3.card_id Where t1.status = 'no ';Copy the code

Outer join

An outer join queries rows associated with multiple tables.

Left outer join left outer join; Right outer join right outer join

For business purposes, we need to see what books are available under all categories in the book category table.

select book_id, book_name, category from bookcategory
left join bookinfo on bookcategory.category_id = bookinfo.book_category_id
where parent_id<>0;

select book_id, book_name, category from bookinfo a
right join bookcategory b on b.category_id = a.book_category_id;

select * from bookcategory;
Copy the code

Left outer join: Displays all records in the left table and those in the right table that meet the join conditions.

Right-outer join: displays all records in the right table and those in the left table that meet the join conditions.

Grammatical structure:

select column_list
from t1
left | right [outer] join t2 on join_condition1;
Copy the code

Since the connection

If both tables involved in a join query are the same table, the query is called self-join

Query the book category number, category name, superior category name of all book categories.

select * from bookcategory; Select s.category_id as' category ', s.category as' category ', P.ategory as' from bookcategory s inner join bookcategory p on s.parent_id = p.ategory_id;Copy the code

More table updates

update 
table1 {[inner] join | {left|right} [outer] join} table2
on conditional_expr
set col1 = {expr1|default}
[,col2 = {expr2|default}]...
[where where_condition]
Copy the code

The reader whose ID number is 432XXXXXX will return the book 86154 that has timed out, and fulfill the following requirements according to the description:

1. Update the Lending information sheet to update the lending status to ‘Yes’.
2. Update the fine record information form, update the actual return date and the fine amount, the fine amount will be deducted 0.2 yuan for each day beyond the date.
3. Also update the balance of the reader information sheet. (Deduct the penalty from the balance)

update readerfee t1 join readerinfo t2 on t1.card_id = t2.card_id set actual_return_date = Sysdate (),book_fee=datediff(sysdate(),return_date)*0.2,balance = balance-book_fee WHERE t1.book_id = 86154 and t1.card_id = '432xxxxxx'; select * from readerinfo;Copy the code

More table delete

delete table1[.*], table2[.*]
from table1 {[inner]join|{left|right}[outer]join} table2
on conditional_expr
[where where_condition]
Copy the code

Book category table, book information table:

Due to business requirements, you need to delete categories in the book category table that do not have book records in the book information table.

select book_id,book_name,category from bookcategory_bak t1
left join bookinfo_bak t2 
on t1.category_id = t2.book_category_id
where parent_id<>0;

delete t1 from bookcategory_bak t1
left join bookinfo_bak t2 
on t1.category_id = t2.book_category_id
where parent_id<>0 and book_id is null;

select * from bookcategory_bak;
Copy the code

The category of the programming language that needs to be deleted from the book category table, and the book record of the programming language in the book information table.

select book_id,book_name,category_id,category from bookcategory_bak t1
inner join bookinfo_bak t2
on t1.category_id = t2.book_category_id;

delete t1,t2 from bookcategory_bak t1
inner join bookinfo_bak t2
on t1.category_id = t2.book_category_id
where t1.category_id = 3; 
Copy the code

Multiple table joins

The results returned by the join query are inner join, outer join, and cross join.

Operators used according to join conditions: equal join, unequal join.

12. Custom functions

Create a function

CREATE FUNCTION FUNCTION name (parameter list) RETURNS Return type BEGIN FUNCTION body ENDCopy the code

Call a function

SELECT function name (parameter list)Copy the code

Check the function

SHOW FUNCTION STATUS;
Copy the code

Delete function

DROP FUNCTION IF EXISTS function_name;
Copy the code

Function: Requires a return value, can specify 0 to n parameters

Create a custom function:

create function function_name([func_parameter]) returns type [characteristics..]  routine_bodyCopy the code

Characteristics Specifies the Characteristics of the storage function. The value is as follows:

SQL security {definer | invoker} specify who has permission to execute. Definer means that only the definer can execute. Invoker means that only callers who have permissions can execute it, which is specified definer by default. Comment 'string': Comment information that can be used to describe storage functions.Copy the code

The function body is composed of SQL code, which can be simple SQL statements. If you need to use begin for composite structures… End statement, compound structure can contain declaration, flow control.

select length('hello');
select date_format(pubdate,'%Y-%m') from bookinfo;

delimiter // 
create function ym_date(mydate date)
returns varchar(15)
begin
return date_format(mydate,'%Y-%m');
end//

delimiter;
select ym_date(pubdate) from bookinfo;
Copy the code

Create a custom function:

Syntax format:

create function function_name([func_parameter]) returns type [characteristics...]  routine_bodyCopy the code

select length('hello');

select date_format(pubdate,'%Y-%m') from bookinfo;
Copy the code

delimiter // create function ym_date(mydate date) returns varchar(15) begin return date_format(mydate,'%Y-%m'); end// delimiter ; Select YM_date (pubdate) from bookinfo;Copy the code

Example analysis function:

Delimiter $$-- delimiter -- Start creating a function CREATE function user_MAIN_fn (v_id int) returns VARCHAR (50) BEGIN -- Define variable declare v_userName varchar(50); Select f_userName info v_userName from t_user_main where f_userId = v_id; Return v_userName; End $$-- The function creates the delimiter;Copy the code

A custom function requires two conditions: parameters and return values

Create custom functions

The create function function_name returns {string | integer | real | decimal} routine_body syntax format: CREATE FUNCTION function_name([func_parameter]) RETURNS type [characteristics...  routine_bodyCopy the code

1. Function_name: function name
2. Func_parameter: A list of parameters of a function
3. RETURNS Type: Specifies the type of the returned value
4. Characteristics: Specifies the Characteristics of the storage function
5. Routine_body: Function body

Create a custom function with no parameters:

Delete a custom function

DROP FUNCTION [IF EXISTS] func_name;
Copy the code

SELECT DATE_FORMAT(NOW(), '%Y year %m month %d day %H dot: % I 分 : RETURN DATE_FORMAT(NOW(), '%Y year %m month %d day %H: % I minute: %s seconds '); SELECT f1();Copy the code

A function of a composite structure

-- Change the statement terminator to $$to prevent the following function from changing; DELIMITER $$CREATE FUNCTION addUser (username VARCHAR(20)) RETURNS INT UNSIGNED RETURN BEGIN INSERT INTO table_1(username) VALUES(username); LAST_INSERT_ID(); END; Change the DELIMITER back to DELIMITER;Copy the code

Use of process control

Common flow control statements:

1. IF condition statement – IF
2. CASE Condition statement – CASE
3. WHILE loop statement – WHILE
4. LOOP The LOOP statement – LOOP
5. REPEAT Loop statement – REPEAT

13. Stored procedures

Local variables are declared with the keyword DECLARE

DECLARE VAR_name [, VARname2, varname3...  date_type [DEFAULT value]; DECARE num INT DEFAULE 10;Copy the code

Inside the BEGIN… Variables defined in the END block are valid only within that block

Session variables are scoped to the entire program

Grammar results

create procedure proc_name([proc_parameter]) [characteristics...]  routine_bodyCopy the code

delimiter // 
create procedure selectproc1()
begin
select book_id, book_name, price, store from bookinfo;
end //
delimiter;

call selectproc();
Copy the code

Delete a stored procedure:

drop procedure [if exists] proc_name;
Copy the code

Create a stored procedure that queries a book's number, title, price, and inventory. delimiter // create procedure selectproc1() begin select book_id,book_name,price,store from bookinfo; end// delimiter ; Call the stored procedure Call selectProc1 ();Copy the code

Delimiter // CREATE Procedure proc1() BEGIN Select book_id,book_name,category from Bookinfo T1 join bookcategory t2 on t1.book_category_id = t2.category_id; end// delimiter ; call proc1(); Design a stored procedure to delete a reader and print the number of remaining readers. delimiter // create procedure proc2(in cid char(18), out num int) begin delete from readerinfo where card_id = cid; select count(card_id) into num from readerinfo; end// delimiter ; select * from readerinfo; call proc2('6545xx', @num); select @num; Design a stored procedure to exchange two numbers. delimiter // create procedure proc3(inout num1 int, inout num2 int) begin declare t int default 0; set t = num1; set num1 = num2; set num2 = t; end// delimiter ; set @n1 = 3, @n2 = 5; call proc3(@n1,@n2); select @n1,@n2; Drop procedure proc1; drop procedure if exists proc2;Copy the code

The difference between stored procedures and functions

Stored procedure, the function of the stored procedure is relatively replication, powerful, can perform a series of database operations including modifying tables.

Storage function, the realization of the function is more targeted.

Difference in return value

Stored procedures: They may return multiple values, or they may not return a value, but simply implement an effect or action.

Storage functions: Must have a return value, and only one return value.

Different parameters

Stored procedure: There are three types of stored procedure parameters: in,out, and inout.

Storage function: There is only one parameter type, similar to the in parameter. When calling a function, you need to specify a value according to the parameter type.

Grammatical structure

Stored procedures that do not need to specify a return type when they are declared.

Store functions that must be declared with a return type and contain a valid return statement in the function body.

Call way

Stored procedures called with the call statement

Storage functions, embedded in SQL, can be called in select

14. The transaction

A transaction must meet four conditions:

Atomicity Atomic Consistency Lsolation Isolation DurabilityCopy the code

Control transaction processing

Rollback will end the user’s transaction and undo any uncommitted changes that are being made

Commit commits the transaction and makes any changes that have been made to the database permanent

Savepoint Identifier, which allows you to create a savepoint in a transaction. You can have multiple SavePoints in a transaction

Rollback to identifier to rollback the transaction to the marker point

There are two main approaches to transaction processing

Use begin, rollback, and commit

1. begin,start transactionStart a transaction
2. rollbackTransaction rollback
3. commitTransaction confirmation

Alter mysql autocommit mode with set

1. set autocommit = 0Disable automatic submission
2. set autocommit = 1Start automatic submission

Innodb uses transactions

Starting with Mysql5.5,InnoDB is the default table storage engine.

Innodb is the engine of choice for transactional databases and supports transaction security tables.

In the MySql delimiter

By default, delimiter is a semicolon. On the command line client, if a command ends with a semicolon, mysql will execute the command when you press enter.

(Tell the mysql interpreter whether the command is finished and whether mysql can execute it.)

What is a storage engine: A database storage engine is the underlying software component of a database. The database management system uses a data engine to create, query, update, and delete data.

At the heart of mysql is the storage engine.

Innodb storage engine

• It ismysqlProvides a transaction secure storage engine with commit, rollback, and crash recovery capabilities.
• It has good performance for processing large amounts of data
• innodbStorage engines support foreign key integrity constraints
• innodbIt is used in many large database sites that require high performance

Setting the storage engine:

1. Set the storage engine of the server
2. Set the desired storage engine under mysqld in the configuration file my.ini
3. default-storage-engine=InnoDB
4. Restarting the mysql server

Create table mytest(id int primary key, name vARCHar (10)) engine = innodb default charset = utf8;Copy the code

Modify the storage engine of the table

alter table tablename engine = engineName
Copy the code

15. Management and maintenance

Manage users

USE mysql;

select user from user;
Copy the code

Permission table: Permission information table of storage accounts: user, DB,host,tables_priv,columns_priv, and procs_priv

The role of each permission table

Tables_priv Table Used to set operation rights for a table. Columns_priv is used to set permissions on a column of the table. The procs_priv table can set operation permissions on stored procedures and stored functions.

Use the CREATE USER statement to CREATE a new USER

Syntax format:

CREATE USER "USER" @ "host" [IDENTIFIED BY "password"];Copy the code

Use the DROP USER statement to DROP a USER

Syntax format:

DROP USER user[, user]; Example DROP USER "rose"@"localhost": DROP USER "rose"@"localhost";Copy the code

Example:

Show variables like 'log_error'; Create a new log table flush logs; Create a new log table mysqladmin -uroot -p flush-logsCopy the code

Pay attention and don’t get lost

Well folks, that’s all for this article, and the people here are talented. I will continue to update the technology related articles, if you find the article useful, welcome to give a “like”, also welcome to share, thank you!!