Issue 17 - what is MySQL database? It's enough to read this dry article!

preface

Why study MySQL? Because MySQL is one of the most popular relational database management systems, MySQL is the best software in web applications. The sql language used by MySQL is the most commonly used standardized language for accessing databases.

Rest assured, friends who read this issue are suitable for: website development, software development or enthusiasts.

1. Introduction to MySQL

What is a database?

  1. Database is a warehouse that organizes, stores and manages data according to data structure.
  2. Database management system is a software system that manages data in index database system.

Let me organize a mind map:

Details:

  • Installation and configuration, common commands and database operation;
  • Integer type and floating point type, date time type and character type;
  • Create and view database tables, modify database tables, and delete database tables;
  • Non empty constraint, primary key constraint, unique constraint, default constraint, foreign key constraint;
  • Management tools: MySQL Workbench, SQLyog;
  • Insert and automatically number single table data records, update single table data records, delete single table data records, query single table data records, group query results, sort query results, and limit the number of query records through limit statement;
  • mysql operator, numeric function, character function, date time function, aggregation function, information function and encryption function;
  • The subquery raised by the comparison operator and used when inserting records
  • Multi table connection, internal connection, external connection, self connection, multi table update, multi table deletion
  • Create, use custom functions
  • Create a stored procedure and use the stored procedure

mysql official website:

Installation package download: (installation operation)

Click Install:

Operation of product configuration:

Open the service box, use win+r and enter services msc

2. mysql directory structure

  1. bin directory: used to store some executable files
  2. include directory: used to store some header files included
  3. lib Directory: used to store some library files
  4. share Directory: used to store error information, character set files, etc
  5. data directory: used to place some log files and databases
  6. my.ini file: the configuration file of the database

Start and stop:

mysql parameters:

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

3. Common commands

Command to modify user password:

mysqladmin The command is used to modify the user password

mysqladmin Command format:

mysqladmin -u user name -p Old password password New password

Commands for displaying databases

show databases;

Commands using database

use Name of the database

Displays information about the current connection

  1. Display the currently connected database: select database();
  2. Display the current server version: select version();
  3. Display current date and time: select now();
  4. Display the current user: select user();

4. Operation database (create, modify, delete)

Create SQL database:

create database [if not exists] db_name
[default] character set [=] charset_name
create database database_name;

Modify the syntax format of the database:

alter database db_name 
[default] character set [=] charset_name

Delete database syntax format:

drop database [if exitsts] db_name;

5. Database - data type

Understand data types: (with the help of library management system)

Book category table:

Category number( category_id) Category name(category) Parent category(parent_id)
1 Computer 0
2 Medical 0

Book information sheet:

Book number(book_id) Category number(book_category_id) Title( book_name) author(author) Price(price) Publishing House( press) Publication time( pubdate) Stock( store)

Borrowing information form:

Book number( book_id) ID number(card_id) Lending date( borrow_date) Return date (return_date) Return(status)

Reader information table

ID number( card_id) full name(name) Gender(sex) Age(age) contact number(tel) balance(balance)

Data type:

Integer: TINYINT-1 byte SMALLINT-2 byte mediamint-3 byte INT-4 byte BIGINT-8 byte

Floating point number type and fixed point number type:

float-4 Bytes
double-8 Bytes

decimal

Date time type:

character:

6. Operation of database table structure

  • Create and view data tables
Create data table:create table

create table <Table name>
( 
 Column name 1 data type[Column level constraints][Default value],
 Column name 2 data type[Column level constraints][Default value],
 ...
 [Table level constraints]
);

  • View database tables:
show tables [from db_name];
  • View the basic structure of the data table:
show columns from tbl_name;

describe <Table name> /DESC<Table name>

show create table tbl_name;

  • Modify database table

Add column:

alter table <Table name>
 add <New column name> <data type>
  [ constraint condition ] [first | after Column name already exists];

Modify column name:

alter table <Table name>
 change <Old column name> <New column name> <New data type>;

Modify the data type of the column:

alter table <Table name> MODIFY <Listing> <data type>

Modify the arrangement position of columns

alter table<Table name>
MODIFY <Column 1> <data type> FIRST|AFTER<Column 2>

Delete column:

alter table <Table name> drop <Listing>;

Modify table name:

alter table <Old table name> RENAME [TO] <New table name>;

  • Delete database table
drop table [if exists] Table 1,Table 2,...surface n;

View table partitions

Create table partition: use partition by type (field)

Define partitions using the values less than operator

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
);

7. Sub query

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';

What is a subquery?

It refers to query statements nested within other sql statements.

select * from table1 where col1 = (select col2 from table2);
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', '1,2,3 etc.', 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');

The inquiry form of xx shows the borrowing information of this book

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

Query the book information table to display all book information whose book price is less than the average price of books

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

Query the book information table to display all book information whose book category is not 'database'

select * from bookinfo where book_category_id<>(select category_id from bookcategory where category = 'database');

Query the book information table and display all book information with the book category of '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); 

Query the book information table and display all book information with book category '2'

in The following sub query returns a data column, which means that any value in the data column meets the conditions

select * from bookinfo where book_category_id in (select category_id from bookcategory where parent_id = 2);

select * from bookinfo where book_category_id = any (select category_id from bookcategory where parent_id = 2);

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');

insert into select statement copies data from a table and then inserts the data into an existing table.

insert into table2 select * from table1;

A penalty record information table needs to be created, including the following information: book number, ID number, due date, actual return date, and penalty amount

The record comes from the readers who have not returned the books beyond the return time in the borrowing information form

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;

Readers with a ID number of 5461xxxxxx will return the out of gauge books 20201101. According to the description, the following requirements are realized:

  • Update the borrowing information table and update the borrowing status to 'yes'.
  • Update the penalty record information table, and update the actual return date and penalty amount. The penalty amount is 0.2 yuan deducted for each exceeding day.
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;

8.mysql constraints

It is a kind of restriction, which ensures the integrity and uniqueness of the data of the table by restricting the data of the rows or columns of the table.

Table structure:

Book (Book ID, category ID, title, author)

Several constraint types commonly used in mysql:

Constraint type Non NULL constraint Primary key constraint Unique constraint Default constraint Foreign key constraint
keyword not null primary key unique default foreign key

Book information sheet:

(Book ID, category ID, title, author, price, press, publication date, stock store)

Book category table:

(category number category_id - primary key, category name category - unique, parent category_id - non empty)

Reader information sheet:

(ID number_id, name, sex, age, tel, balance)

Borrowing information form:

(book\u ID, ID number\u ID, lending date, return date, return status)

Non NULL constraint

null field value can be empty

The value of the not null field cannot be null

Non NULL constraint

Non empty constraint means that the value of the field cannot be empty. For the field with non NULL constraint, if the user does not specify a value when adding data, the database system will report an error.

Column name of data type not null

Add non empty constraint when creating table

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

Delete non empty constraints

alter table bookinfo modify book_name varchar(20);

Add a non empty constraint by modifying the table

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

Primary key constraint

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

Type of primary key:

Primary keys are divided into single field primary keys and multi field joint primary keys

Single field PK: consists of one field

Specify the primary key while defining the column
 Column name data type primary key;

Specify the primary key after the column definition
[constraint<Constraint name>] primary key(Listing);

Add primary key constraint when creating table

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

Delete primary key constraint

ALTER TABLE bookinfo DROP PRIMARY KEY;

Add primary key constraints by modifying tables

ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);

Multi field joint primary key

A primary key consists of multiple fields. Primary key (field 1, field 2,... Field n);

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

Adding a primary key to a column by modifying a 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);

Unique constraint

The unique constraint requires that the column be unique and can be null. The unique constraint can ensure that one or more columns do not have duplicate values.

rule of grammar:

Column name data type unique

[constraint <Constraint name>] unique(<Listing>)

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)
);

Add unique constraints by modifying tables

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);

Delete unique constraint

ALTER TABLE book_info DROP  KEY  uk_bname;

ALTER TABLE book_info DROP  INDEX  uk_bname;

The difference between unique constraint and primary key constraint

  1. A table can have multiple unique declarations, but only one primary key declaration
  2. Columns declared as primary key cannot have null values
  3. Null values are allowed for columns declared unique

Default constraint

A default constraint is the default value of a column

Column name data type default Default value

Add default constraints when creating tables

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

Add default constraints by modifying tables

ALTER TABLE bookinfo 
ALTER COLUMN press SET DEFAULT 'press';

alter table bookinfo 
modify press varchar(10) default 'press';

Delete default constraint

alter table bookinfo modify press varchar(20);

ALTER TABLE bookinfo 
ALTER COLUMN press DROP DEFAULT;

Foreign key constraint

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

The foreign key corresponds to referential integrity. The foreign key of a table can be null. If it is not null, each foreign key must be equal to a value of the primary key in another table.

Function: maintain data consistency and integrity.

Add foreign key constraints when creating tables

Book category table(Parent table)
CREATE TABLE bookcategory(
    category_id INT PRIMARY KEY,
    category VARCHAR(20),
    parent_id INT
);

Book information table(Sub 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)
);

Add foreign key constraints by modifying tables

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

Delete foreign key constraint

ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;

Reference operations for foreign key constraints

cascade, which deletes or updates from the parent table and automatically deletes or updates the matching 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);

Create library management system table

Book category table

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

Book information table

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 'Machinery Industry Press',
pubdate date not null,
store int not null,
constraint fk_bcid foreign key(book_category_id) references bookcategory(category_id)

);

Reader information table

create table readerinfo(

card_id char(18) primary key,
name varchar(20) not null,
sex enum('male','female','secrecy') default 'secrecy',
age tinyint,
tel char(11) not null,
balance decimal(7,3) default 200

);

Borrowing information form

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)

);

9. Operation of database table records

Insertion of single table data record

Syntax format:

insert into table_name(column_list) values(value_list);

Insert 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);

Inserts data for the specified column of the table

insert into readerinfo
(card_id,name,tel)values('4562135465','Fei Zhang','4651354651');

Insert multiple records at the same time

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

Insert results from query table

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

Automatic increase

Set the attribute value of the table to increase automatically:

Column name data type auto_increment

Add auto increment column when creating 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;

Test auto increment

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

Remove auto increment column

alter table bookcategory_tmp modify category_id int;

Add auto increment column

alter table bookcategory_tmp modify category_id int auto_increment;

Modify the starting value of auto increment column

alter table bookcategory_tmp auto_increment = 15;

insert into bookcategory_tmp(category,parent_id)values('literature',0);

Delete foreign key of book information table

alter table bookinfo drop foreign key fk_bcid;

Add automatic numbering function for book category table

alter table bookcategory modify category_id int auto_increment;

Restore Association

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

Update of single table data record

Insert a piece of borrowing information into the borrowing information table

insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20202010,46516874,'2020-11-29','2020-12-29','no');

Update the balance in the reader information table

Check the price of books 79.80
select price from bookinfo where book_id = 20202010;

Update balance

update readerinfo set balance = balance - 79.80*0.05 where card_id = '46516874';
select * from readerinfo;

Update the inventory of book information table

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

Deletion of single table data record

Delete records with specified conditions

delete from readerinfo where card_id = '46461265464565';

Delete all records in the table

delete from readerinfo;

truncate table readerinfo;fast

To delete all records in the table, you can use the truncate table statement. Truncate will directly delete the original table and recreate a table. Its syntax structure is as follows:

truncate table table_name

Query the category number of Pediatrics

select category_id from bookcategory where category='Pediatrics';

Delete book information with book number 5

delete from bookinfo where book_category_id = 5;

Delete the category of Pediatrics in the book category table

delete from bookcategory where category = 'Pediatrics';

Query of single table data record

Query all columns

select * from bookcategory;

select category_id,category,parent_id from bookcategory;

Query specified column

select category from bookcategory;

select category_id,category from bookcategory;

Query records with specified criteria

select book_id,book_name,price from bookinfo where press='press';

Records with non duplicate query results

select distinct press from bookinfo;

View null values

select * from readerinfo where age is null;

grouping

Count the number of male readers in the reader information table

select count(*) from readerinfo where sex='male';

Group the records in the reader information form by gender

select sex from readerinfo group by sex;

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

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

The records in the reader information table are grouped by gender, and the number of people after grouping is greater than the gender of

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

sort

Sort the results of the query through the order by clause

order by Listing [asc|desc]

Sorting direction:

  1. Sorting is divided into ascending and descending. Ascending is the default
  2. Ascending asc
  3. Descending desc

Single column sorting

select * from bookinfo order by price;

Multi column sorting

select * from bookinfo order by price,store;

Specify sort direction

select * from bookinfo order by price,store desc;

The limit statement limits the number of query records

First 3 lines of records

select * from bookinfo limit 3;

The last two records starting from the third record

select * from bookinfo limit 2,2;

select * from bookinfo limit 2 offset 2;
insert into bookinfo(book_id,book_category_id,book_name,author,price,press,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, 'press', '2020-05-01',8);

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

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

10. Operators and functions

MySQL mainly has the following operators:

  1. Arithmetic operator
  2. Comparison operator
  3. Logical operator
  4. Bitwise Operators

Arithmetic operator

Comparison operator

Logical operator

Bitwise Operators

Operator priority

The reader's ID number, name, telephone number and balance.

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

View the reader information with balance greater than 200 in the reader information table.

select * from readerinfo where balance>200;

Check the reader information with balance not equal to 200 in the reader information table.

select * from readerinfo where balance <> 200;

View the reader information whose age is not empty in the reader information table.

select * from readerinfo where age is not null;

Check the reader information in the reader information table with the balance between 350 and 450.

select * from readerinfo where balance between 350 and 450;
select * from readerinfo where name in('dada','dada1','dada2');

select * from readerinfo where name like 'Zhang_';

select * from readerinfo where tel like '135%';

select * from bookinfo where price>50 and store<5;

select * from bookinfo where price>80 or press = 'press';

select * from bookinfo where price not between 50 and 100;

Numerical function

ceil returns the smallest integer value greater than x

select ceil(28.55); // 29

floor returns the maximum integer value less than x

select floor(28.55); // 28

rounding
round returns the integer closest to parameter x and rounds parameter X

select round(28.55); // 29
select round(28.55,1),round(28.55,0),round(28.55,-1);
// 28.6 29 30

Truncation Functions

select truncate(28.55,1),truncate(28.55,0),truncate(28.55,-1);
// 28.5 28 20

Take modulus and return the remainder after x is divided by y

select mod(11,2); // 1
select book_id,book_name,price, round(price) from bookinfo;

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

Character function

String connection

select concat('hello','world');
select concat_ws('-','hello','world');

Convert letters to case

select lower('Hello World');
select upper('Hello World');

Find length

select length(' hello ');

Remove Spaces

select ltrim(' hello '),length(ltrim(' hello '));
select rtrim(' hello '),length(rtrim(' hello '));
select trim(' hello '),length(trim(' hello '));

Intercept string

select substring('hello world',1,5);

select substring('hello world',-5,2);

Gets a string of the specified length

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

Substitution function

select replace('hello world','world','mysql'); // hello mysql

Formatting Functions

select format(1234.5678,2),format(1234.5,2),format(1234.5678,0);
//1234.57 1234.50 12345

select book_id,book_name,format(price,2)from bookinfo;

Date and time functions

View current system date

select curdate();
// 2020-02-02
select curdate()+0;

select curtime()+0;

View the current system date and time

select now(); // 2020-10-10 12:12:12

select sysdate(); // 2020-10-10 12:12:12

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

Addition of dates
select date_add('2020-01-01', interval 5 month); // 2020-06-01

Calculate the number of days between two dates
select datediff('2020-02-10','2020-02-01');

Date formatting
select date_format('2020-02-01', '%Y%m');

Aggregate function (grouping function)

|Name | description|
|avg() | returns the average value of a column|
|count() | returns the number of rows in a column|
|max() | returns the maximum value of a column|
|min() | returns the minimum value of a column|
|sum() | returns the sum of the values of a column|

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

select avg(price) from bookinfo;

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

select sum(price) from bookinfo;

Find the maximum inventory in the book information table.

select max(store) from bookinfo;

Find the minimum inventory in the book information table.

select min(store) from bookinfo;

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

select count(*) from bookinfo;

Group by category, query the number of books in each category and the total inventory of books in each category.

select book_category_id as 'Book category',count(book_id) as 'Types of books', sum(store) as 'Total inventory' from bookinfo group by book_category_id;

Information function and encryption function

System information function

View current MySQL The version number of the server version
select version();

see MySQL The number of times the server is currently connected
select connection_id();

View the current database name
select schema();

View the currently logged in user name
select user();

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;

11. Multi table connection query

Multi table join query is to obtain data from multiple tables.

Book ID: (book name, book ID)

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

Acquisition table: (Book ID, book name, category name)

Syntax structure of multi table connection:

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

Multi table connection
By viewing the book information table and book category table
To get the book number, book name and book category

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

Inner connection

Query and select data from multiple tables according to the connection conditions, display the data rows matching the connection conditions in these tables, and combine them into new records. (inner connection is both)

Syntax structure of inner connection:

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

Due to the need of book borrowing statistics, I want to query the book number, book name, ID number, name, phone number, return date and whether to return the unreturned books.

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';

External connection

An outer join queries the associated rows in multiple tables.

External connections are divided into: left outer join; right outer join

According to business needs, we need to check the books 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;

Left outer connection: displays all records in the left table and records that meet the connection conditions in the right table.

Right external connection: displays all records in the right table and records that meet the connection conditions in the left table.

Syntax structure:

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

Self connection

If two tables involved in a join query are the same table, this query is called self join

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

select * from bookcategory;

select s.category_id as'Book category number', s.category as 'Book category name', p.category as'Superior classification name of books' from bookcategory s
inner join bookcategory p
on s.parent_id = p.category_id;

Multi table update

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

Readers with ID card No. 432xxxxxx will return the overtime book 86154. According to the description, the following requirements are realized:

  1. Update the borrowing information table and update the borrowing status to 'yes'.
  2. Update the penalty record information table, and update the actual return date and penalty amount. The penalty amount is 0.2 yuan deducted for each exceeding day.
  3. At the same time, update the balance of the reader information table. (deduct the penalty amount 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;

Multi table deletion

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

Book category table, book information table:

Due to business requirements, it is necessary to delete the categories in the book category table that have no 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;

You need to delete the category of programming language in the book category table and the book record about 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; 

Multi table connection

According to the results returned by the connection query: inner join, outer join and cross join.

Operators used according to connection conditions: equal connection and unequal connection.

12. User defined function

Create function

CREATE FUNCTION Function name(parameter list) RETURNS Return type
BEGIN
    Function body
END

Call function

SELECT Function name(parameter list)

View function

SHOW FUNCTION STATUS;

Delete function

DROP FUNCTION IF EXISTS function_name;

Function: return value is required. 0~n parameters can be specified

To create a custom function:

create function function_name([func_parameter])
returns type
[characteristics..] routine_body

Characteristics specifies the characteristics of the storage function. Examples of values:

sql security{definer|invoker}Indicate who has permission to execute.

definer Indicates that only the definer can execute.

invoker It means that only the caller with permission can execute. By default, it is specified as definer. 

comment 'string':Annotation information can be used to describe storage functions.

The function body is composed of sql code, which can be a simple sql statement. If you need to use begin for a composite structure End statement. The composite structure can contain declaration and process 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;

To create a custom function:

Syntax format:

create function function_name([func_parameter])
returns type
[characteristics...] routine_body
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 ;

Use (call) custom functions
select ym_date(pubdate) from bookinfo;

Instance analysis function:

Create a function
delimiter $$ --Delimiter
--- Start creating function
create function user_main_fn(v_id int)
returns varchar(50)
begin
--Define variables
declare v_userName varchar(50);
--Assign values to defined variables
select f_userName info v_userName from t_user_main
where f_userId = v_id;
--Return function processing result
return v_userName;
end $$ --Function create delimiter
delimiter;

There are two necessary conditions for a user-defined function: parameter and return value

Create custom function

create function function_name
returns
{string|integer|real|decimal}
routine_body

Syntax format:

CREATE FUNCTION function_name([func_parameter])
RETURNS type
[characteristics ... ] routine_body
  1. function_name: function name
  2. func_parameter: the parameter list of the function
  3. RETURNS type: Specifies the type of return value
  4. Characteristics: Specifies the characteristics of the storage function
  5. routine_body: function body

To create a custom function without parameters:

Delete custom function

DROP FUNCTION [IF EXISTS] func_name;

SELECT DATE_FORMAT(NOW(), '%Y year%m month%d day %H Point:%i Score:%s second')

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y year%m month%d day %H Point:%i Score:%s second');

SELECT f1();

Function of composite structure

-- Change the statement terminator to $$,To prevent the following functions from;As the end of a statement
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 separator back
DELIMITER ;

Use of process control

Common process control statements:

  1. if conditional judgment statement - if
  2. case conditional judgment statement - case
  3. while loop statement - while
  4. LOOP loop statement
  5. repeat loop statement repeat

13. Stored procedure

Local variables are declared with the keyword DECLARE

DECLARE var_name [, varname2, varname3 ...] date_type [DEFAULT value];
Example: DECARE num INT DEFAULE 10;

Variables defined in the internal BEGIN... END block are valid only within the block

The scope of session variables is the whole program

Grammatical results

create procedure proc_name([proc_parameter])
[characteristics...] routine_body
delimiter // 
create procedure selectproc1()
begin
select book_id, book_name, price, store from bookinfo;
end //
delimiter;

call selectproc();

Delete stored procedure:

drop procedure [if exists] proc_name;
Create a stored procedure to query the number, title, price and inventory of books.
delimiter //
create procedure selectproc1()
begin
select book_id,book_name,price,store from bookinfo;
end//
delimiter ;

Call stored procedure
call selectproc1();
Create a stored procedure to query book number, book name and book category
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 output 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;

Delete stored procedure
drop procedure proc1;
drop procedure  if exists proc2;

Differences between stored procedures and functions

Stored procedure. The functions realized by stored procedure are more replication and powerful. It can perform a series of database operations, including modifying tables.

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

Difference in return value

Stored procedure: it can return multiple values or no values, but only achieve some effect or action.

Storage function: there must be a return value and only one return value.

Different parameters

Stored procedure: there are three parameter types of stored procedure: in, out and inout.

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

Grammatical structure

Stored procedure. The return type does not need to be specified when the stored procedure is declared.

When storing a function, the return type needs to be specified when declaring the function, and a valid return statement must be included in the function body.

Call mode

Stored procedure, called with call statement

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

14. Services

There are four conditions that a transaction must meet:

atomicity Atomicity
consistency uniformity
lsolation Isolation
durability persistence

Control transactions

Rollback: rollback will end the user's transaction and undo all uncommitted modifications in progress

commit, which commits the transaction and makes all changes made to the database permanent

savepoint identifier, which allows you to create a savepoint in a transaction. There can be multiple savepoints in a transaction

rollback to identifier: rollback the transaction to the marked point

There are two main methods of transaction processing

It is implemented with begin, rollback and commit

  1. begin,start transaction
  2. Rollback transaction rollback
  3. commit transaction confirmation


Directly use set to change the automatic submission mode of mysql

  1. set autocommit = 0 disable autocommit
  2. set autocommit = 1 start autocommit

innodb uses transactions

From mysql5 Since version 5, InnoDB is the default table storage engine.

innodb is the preferred engine for transactional databases and supports transactional security tables.

delimiter in MySql

By default, delimiter is a semicolon. In the command-line client, if a command ends with a semicolon, mysql will execute the command after entering.


(tell the mysql interpreter whether the command has ended and whether mysql can be executed.)

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

The core of mysql is the storage engine.

innodb storage engine

  • It provides mysql with a transaction secure storage engine with the ability of commit, rollback and crash recovery.
  • It has good performance for processing huge amount of data
  • innodb storage engine supports foreign key integrity constraints
  • innodb is used in many large database sites that need high performance

Set up the storage engine:

  1. Set the storage engine of the server
  2. In the configuration file my Under mysqld in ini, set the required storage engine
  3. default-storage-engine=InnoDB
  4. Restart mysql server
Create a table (single) and set up the storage engine
create table mytest(
 id int primary key,
 name varchar(10)
) engine = innodb default charset = utf8;

Modify the storage engine of the table

alter table tablename engine = engineName

15. Management and maintenance

Manage users

USE mysql;

select user from user;

Permission table: the permission information table of the stored account: user,db,host,tables_priv,columns_priv and procs_priv

Function of each permission table

tables_priv table is used to set operation permissions on the table; columns_priv table is used to set permissions on a column of the table; procs_priv table can set operation permissions for stored procedures and stored functions.

Use the CREATE USER statement to create a new user

Syntax format:

CREATE USER "user"@"host" [IDENTIFIED BY "password"];

Use the DROP USER statement to delete a user

Syntax format:

DROP USER user[, user];

Example: use DROP USER Delete account"rose"@"localhost":
DROP USER "rose"@"localhost";

Example:

View the path of the log file
show variables like 'log_error';

Create a new log information table
flush logs;

Create a new log information table
mysqladmin -uroot -p flush-logs

Pay attention and don't get lost

Well, ladies and gentlemen, the above is the whole content of this article. You can see that the people here are talents. I will constantly update the articles related to technology later. If I think the articles are useful to you, please give me a "like" and welcome to share. Thank you!!

Tags: PHP Front-end MySQL Back-end

Posted by keigowei on Mon, 16 May 2022 11:38:44 +0300