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

Time:2021-1-23

preface

Why studyMySQLWhat about it? becauseMySQLIt’s the most popularRelational database management systemOne, inwebIn terms of application,MySQLIt’s the best software.MySQLUsedsqlLanguage is the most commonly used standardized language for accessing databases.

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

1. Introduction to MySQL

What is a database?

  1. Database, which is based ondata structurecomeOrganization, storage and managementData warehouse.
  2. Database management system, index database system for data management software system.

Let me organize a mind map:
Issue 17 - what is MySQL database? It's enough to read this dry article!

Details:

  • Installation configuration, common commands, operation database;
  • 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 NULL constraint, primary key constraint, unique constraint, default constraint and foreign key constraint;
  • Management tools:MySQL Workbench,SQLyog
  • Insert and auto 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 by limit statement;
  • MySQL operator, numeric function, character function, date time function, aggregate function, information function and encryption function;
  • Use the subquery raised by the comparison operator, and the subquery used when inserting records
  • Multi table join, inner join, outer join, self join, multi table update, multi table delete
  • Create, use custom functions
  • Create a stored procedure and use it

MySQL official website:

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

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

Installation package download: (installation operation)

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

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

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

Click Install:

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

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

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

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

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

Operation of product configuration:

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

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

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

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

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

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

Open the service boxwin+r, inputservices.msc

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

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

2. MySQL directory structure

  1. binDirectory: used to store some executable files
  2. includeDirectory: used to store some included 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.iniFiles: configuration files for databases

Start and stop:

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

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

mysqlParameters:

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

3. Common commands

Command to change user password:

The mysqladmin command is used to modify the user password

Mysqladmin command format:

Mysqladmin - U user name - P old password new password

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

Command to display the database

show databases;

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

Command to use database

The name of the database

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

Displays information about the current connection

  1. Show currentConnected databaseselect database();
  2. Show currentServer versionselect version();
  3. Show currentDate timeselect now();
  4. Show currentuserselect user();

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

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

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

4. Operate database (create, modify, delete)

Create databaseSQL

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

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

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

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

Modify the syntax format of the database:

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

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

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

Delete database syntax format:

drop database [if exitsts] db_name;

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

5. Database – data type

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

Book category table:

Category number_ ID) category name (category) parent_ id)
1 computer 0
2 medicine 0

Book information table:

Book number_ ID) class number (Book)_ category_ ID) book_ Name) author price press publish date store

Borrowing information form:

Book number_ ID) ID number (card_) ID) loan date (borrow)_ Return date_ Date) return (status)

Reader information sheet

ID number (card_) ID) name (name) sex (sex) age (age) Tel balance (balance)

Data type:

Integer:TINYINT-1 byteSMALLINT-2 bytesMEDIUMINT-3 bytesINT-4 bytesBIGINT-8 bytes

Floating point type and fixed point type:

Float - 4 bytes
Double - 8 bytes

decimal

Date time type:

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

character:

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

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 constraint] [default],
 Column name 2 data type [column level constraint] [default],
 ...
 [table level constraints]
);

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

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

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

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

Describe < table name > / desc < table name >

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

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

show create table tbl_name;

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

  • Modify database table

Add column:

Alter table < table name >
 Add < new column name > < data type >
  [constraint] [first | after column name already exists];

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

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

Modify column name:

Alter table < table name >
 Change < old column name > < new column name > < new data type >;

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

Modify the data type of the column:

Alter table < table name > Modify < column name > < data type >

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

Modify the arrangement of columns

Alter table < table name >
Modify < column 1 > < data type > first | after < column 2 >

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

Delete column:

Alter table < table name > drop < column name >;

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

Modify table name:

Alter table < old table name > rename [to] < new table name >;

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

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

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

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

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

View table partition

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

Creating table partitions: Usingpartition byType (field)

usevalues less thanOperator defined partition

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

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

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

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

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

What is a subquery?

It refers to nesting in other partssqlA query statement within a statement.

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),
(4534, 2545, 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 ');

Query the borrowing information table to display the borrowing records of the book XX

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

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

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

Query the book information table to display all the book information that the book category is not in the database

select * from bookinfo where book_ category_ id<>(select category_ ID from bookcategory where category ='Database ');

Query the book information table to display all the 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 to display all the book information of the book category ‘2’

The subquery after "in" returns a data column, which is equal to any value in the data column

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 selectStatement copies data from a table and inserts it into an existing table.

insert into table2 select * from table1;

You need to create a penalty record information table, which contains the following information:Book number, ID number, date of repayment, actual date of repayment, the amount of penalty.

The records come from the readers who have not returned the books beyond the time of returning the books in the borrowing information table

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;

The reader who has an ID number of 5461xxxxxxx will return the overrun book 20201101, and achieve the following requirements according to the description:

  • Update the borrowing information table and update the borrowing status to “yes”.
  • Update the penalty record information form, update the actual return date and penalty amount, and the penalty amount is 0.2 yuan per day.
Update borowinfo 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, by restricting the data of the row or column of the table, to ensure the stability of the data of the tableIntegrity, uniqueness.

Table structure:

Book (book number)book_id, category numberbook_category_id, titlebook_name, authorauthor)

staymysqlThere are several constraint types commonly used in

Constraint type Nonempty constraint Primary key constraint Unique constraint Default constraint Foreign key constraints
keyword not null primary key unique default foreign key

Book information table:

(book number)book_id, category numberbook_category_id, titlebook_name, authorauthor, priceprice, presspress, publication timepubdate, inventorystore)

Book category table:

(class number)category_id-Primary key, category namecategory-Unique, parent categoryparent_id-(not empty)

Reader information sheet:

(ID number)card_id, namename, gendersex, ageage, contact numbertel, balancebalance)

Borrowing information form:

(book number)book_idID numbercard_id, lending dateborrow_date, return datereturn_date, return or notstatus)

Nonempty constraint

nullField value can be empty

not nullField value cannot be empty

Nonempty constraint

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

Column name data type not null

Add a non empty constraint when creating a table

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

Delete non empty constraint

alter table bookinfo modify book_name varchar(20);

Adding non null constraints by modifying tables

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

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

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

Primary key constraint

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

Type of primary key:

The primary key is divided intoSingle field primary keyandMulti field union primary key

Single field primary key: it 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 (column name);

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;

Adding primary key constraints by modifying tables

ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);

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

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

Multi field union primary key

A primary key is a combination 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)
);

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

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

Unique constraint

The unique constraint requires that the column is unique and can be null. The unique constraint can ensure that there are no duplicate values in one or several columns.

rule of grammar:

Column name data type unique

[constraint < constraint name >] unique (< column name >)

Add a unique constraint when creating a table

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 a unique constraint 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);

Delete unique constraint

ALTER TABLE book_info DROP  KEY  uk_bname;

ALTER TABLE book_info DROP  INDEX  uk_bname;

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

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

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

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

The difference between unique constraint and primary key constraint

  1. There can be more than one in a tableuniqueStatement, but only oneprimary keystatement
  2. Declare asprimary keyNull values are not allowed for columns of
  3. Declare asuniqueNull values are allowed for columns of

Default constraint

A default constraint is the default value of a column

Column name data type default default

Add default constraints when creating tables

CREATE TABLE bookinfo(
    book_id INT PRIMARY KEY,
    Press varchar (20) default 'press'
);

Add the default constraint by modifying the table

ALTER TABLE bookinfo 
Alter column press set default 'Publishing House';

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 constraints

Foreign keys are used to establish links between the data of two tables, which can be one or more columns. A table can have one or more foreign keys.

Foreign keys correspond to referential integrity. Foreign keys in one table can be null. If not, each foreign key must be equal to a value of the primary key in another table.

Function: keep the consistency and integrity of data.

Add foreign key constraint when creating table

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

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

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

Reference operation of foreign key constraint

cascadeTo delete or update from the parent table and automatically delete or update 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);

Creating library management system table

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

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

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

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

);

Reader information sheet

create table readerinfo(

card_id char(18) primary key,
name varchar(20) not null,
Sex enum ('male ',' female ',' confidential ') default' confidential ',
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 record

Insertion of single table data record

Syntax format:

insert into table_name(column_list) values(value_list);

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

Inserts data for the specified column of the table

insert into readerinfo
(card_ ID, name, TEL) values ('4562135465 ',' Zhang Fei ',' 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);

Inserts the query results into the table of the

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

Auto increase

Set the property value of the table to increase automatically

Column name data type auto_ increment

Add auto increment columns when creating tables

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 the autoincrement column

alter table bookcategory_tmp auto_increment = 15;

insert into bookcategory_ tmp(category,parent_ ID) values ('literature ', 0);

Deleting foreign key of book information table

alter table bookinfo drop foreign key fk_bcid;

The function of adding automatic number to 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 borrowing message into the borrowing information table

insert into borrowinfo(book_ id,card_ id,borrow_ date,return_ Date, status) values (202020104616874, '2020-11-29','2020-12-29 ','No');

Update the balance in the reader information table

Check the price of the book 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

To delete all records in a table, you can use thetruncate tablesentence,truncateThe original table will be deleted directly and a new table will be created

truncate table table_name

Search 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 the specified column

select category from bookcategory;

select category_id,category from bookcategory;

Query records with specified conditions

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 table by gender

select sex from readerinfo group by sex;

The records in the reader information table are grouped by gender, and the number of people of each gender is counted

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

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

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

sort

The order by clause is used to sort the query results

Order by column name [ASC | desc]

Sort direction:

  1. Sorting is divided into ascending and descending, the default is ascending
  2. Ascending orderasc
  3. Descending orderdesc

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

The first three lines of records

select * from bookinfo limit 3;

The last two records 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, 'Publishing House', '2020-04-01', 10),
(452454542,4, '456', 'xxx', 35.5, 'Publishing House', '2020-08-01', 20),
(4578754,4, '789', 'xxx', 46.6, 'Publishing House', '2020-05-01', 8));

Group the book information according to the inventory, count the number of each group of inventory, and 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 operators
  4. Bitwise Operators

Arithmetic operator

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

Comparison operator

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

Logical operators

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

Bitwise Operators

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

Operator priority

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

Reader’s ID number, name, telephone number, balance.

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

Check the information of readers whose balance is more than 200 in the reader information table.

select * from readerinfo where balance>200;

Check the information of readers whose balance is not equal to 200 in the reader information table.

select * from readerinfo where balance <> 200;

Check the information of readers 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 a 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 '_ ';

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

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

Select * from bookinfo where price > 80 or press ='publishing house ';

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

Modulo returns the remainder of X 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');

Case conversion

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

Find the 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

Replacement 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 the 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 date
select date_add('2020-01-01', interval 5 month); // 2020-06-01

Calculates 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 (group 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;

Book information table, the total price of all books.

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;

How many kinds of books are there 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' book type ', sum (store) as' total stock' from bookinfo group by book_ category_ id;

Information function and encryption function

System information function

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

View the number of times the MySQL 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 join query

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

From the book information table: (book number)book_id, category numberbook_category_id, titlebook_name

Book category table: (category number)category_id, category namecategory, parent categoryparent_id)

Access table: (book number)book_id, titlebook_name, category namecategory)

Syntax structure of multi table join:

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, book category

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

Internal connection

Query and select data from multiple tables according to the connection conditions, display the data rows that match the connection conditions in these tables, and combine them into new records. (inner connection isBoth have in common

The grammatical structure of inner join is as follows

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

Because of the need for book borrowing statistics, we want to inquire whether the books number, book name, ID number, name, telephone number, and return date of the books are returned.

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

Outer join queries the associated rows in multiple tables.

External connection is divided into: left external connectionleft outer join; right outer connectionright outer join

According to business needs, we need to check which books are 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: all the records in the left table are displayed, and the records that meet the connection conditions in the right table are displayed.

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

Grammatical structure:

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

Self connect

If two tables involved in a join query are the same table, this kind of 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 category name of book 'from book category 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]

The ID number 432xxxxxx is the reader who returns the book 86154, which is based on the description.

  1. Update the borrowing information table and update the borrowing status to “yes”.
  2. Update the penalty record information form, update the actual return date and penalty amount, and the penalty amount is 0.2 yuan per day.
  3. At the same time, update the balance of the reader information table. (deduct the fine 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 needs, 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;

It is necessary 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.

According to the connection conditions used by the operator: equal connection, 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 functions

SHOW FUNCTION STATUS;

Delete function

DROP FUNCTION IF EXISTS function_name;

Function: need to have a return value, you can specify 0 ~ n parameters

To create a custom function:

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

CharacteristicsSpecify the properties of the storage function. For example:

SQL security {definer | invoker} indicates who has permission to execute.

Definer means that only the definer can execute.

Invoker means that only the caller with permission can execute. By default, the system specifies it as definer.

Comment 'string': comment information, which can be used to describe the storage function.

The function body is composed of SQL code, which can be used as a simple SQL statement. If it is a composite structure, it needs to be usedbegin...endStatement, compound structure can contain declaration, 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 ;

Using (calling) custom functions
select ym_date(pubdate) from bookinfo;

Example analysis function:

Create a function
Delimiter $$-- delimiter
---Start creating function
create function user_main_fn(v_id int)
returns varchar(50)
begin
--Defining 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;
--Returns the result of function processing
return v_userName;
End $$-- function creation delimiter
delimiter;

Two necessary conditions of 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 properties of the storage function
  5. routine_ Body: function body

To create a parameterless custom function:

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

Delete custom function

DROP FUNCTION [IF EXISTS] func_name;

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

SELECT DATE_ Format (now(),% y year% m month% d% H point% I minute% s second ')

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_ Format (now(),% y year% m month% d% H point% I minute% s second ');

SELECT f1();

Functions of composite structures

--Change the statement terminator to $$, in order to prevent the following function from regarding; as the end of the 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 statement – if
  2. Case conditional statement – Case
  3. While loop statement – while
  4. Loop loop statement – loop
  5. Repeat loop statement repeat

13. Stored procedure

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

Local variables are represented by keywordsDECLAREstatement

DECLARE var_name [, varname2, varname3 …] date_type [DEFAULT value];
For example: decare num int default 10;

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

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

insideBEGIN…ENDVariables defined in a block are only valid within that block

The scope of the session variable is the whole program

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

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

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 ;

Calling stored procedure
call selectproc1();
Create a stored procedure to query book number, title and 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, 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;

The difference between stored procedure and function

Stored procedures, stored procedures to achieve the function of replication, powerful, can perform a series of database operations, including modifying the table.

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

Difference in return value

Stored procedure: can return multiple values, or can not return values, just to achieve an effect or action.

Storage function: must have a return value, and can only have one return value.

The parameters are different

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 a function, you need to specify the value according to the parameter type.

Grammatical structure

Stored procedure, when a stored procedure is declaredThere is no need to specify the return type.

Storage function, function declaration needsSpecifies the return type, and must contain a valid return statement in the function body.

Call mode

Stored procedure, usingcallStatement

Storage function, embedded insqlCan be used inselectCall in

14. Business

There are four conditions that a transaction must satisfy

Atomicity
Consistency consistency
Isolation
Durability persistence

Control transaction processing

rollbackRollback ends the user’s transaction and undoes all uncommitted changes in progress

commit, commit the transaction, and make all changes that have been made to the database permanent

savepoint identifier, allows you to create a savepoint in a transaction, and there can be multiple savepoints in a transaction

rollback to identifierTo roll back the transaction to the marked point

There are two main methods of transaction processing

usebegin, rollback, commitTo achieve

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

Issue 17 - what is MySQL database? It's enough to read this dry article!
Directly use set to change the automatic submission mode of MySQL

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

InnoDB uses transactions

Starting from mysql5.5, InnoDB is the default table storage engine.

innodbIt is the preferred engine of transactional database and supports transaction security table.

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

The definition of the delimiter in MySQL

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

Issue 17 - what is MySQL database? It's enough to read this dry article!
(tell the MySQL interpreter whether the command has ended and whether MySQL can be executed. )

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

mysqlThe core of the storage engine is the storage engine.

innodbStorage engine

  • It ismysqlA transaction secure storage engine with commit, rollback and crash recovery capabilities is provided.
  • It has good performance for processing huge amount of data
  • innodbStorage engine supports foreign key integrity constraints
  • innodbIt is used in many large database sites that need high performance

Set up the storage engine:

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

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

Storage engine for modifying tables

alter table tablename engine = engineName

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

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

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

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

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

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

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

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

15. Management and maintenance

Manage users

USE mysql;

select user from user;

Permission table: the permission information table of the storage account:user,db,host,tables_priv,columns_privandprocs_priv

Function of each permission table

tables_privThe table is used to set operation permissions on the table;columns_privTable is used to set permissions on a column of the table;procs_privTable can set operation permissions on stored procedures and stored functions.

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

Use the create user statement to create a new user

Syntax format:

CREATE USER “user”@“host” [IDENTIFIED BY “password”];

Using drop user statement to delete user

Syntax format:

DROP USER user[, user];

Example: using drop user to 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, don’t get lost

OK, everyone, the above is the whole content of this article, you can see that the people here are talents. I will continue to update the technology related articles, if you feel that the article is useful to you, welcome to give a “like”, also welcome to share, thank you!!