MySQL database

1. MySQL built-in functions

1.1 string function

1.1.1 concat splicing string function

• concat (parameter 1, parameter 2, parameter 3, parameter n)
  • Parameters can be numbers or strings
  • Concatenate all parameters into a complete string

Example 1: put 12334,'ab'Splice into a string'12334ab'
select concat(12, 334, 'ab');

1.1.2 length returns the number of string characters

• For a Chinese character in utf8 format, length returns 3

-- Example 1: calculation string'abc'Length of
select length('abc');
-- Example 2: calculation string'you and me'Length of
select length('you and me');
-- Example 3: calculating strings'Me and you'Length of
select length('Me and you');
-- Example 4: query table students in name Length equal to 9
--(Three utf8 Student information in Chinese characters)

1.1.3 mysql built-in functions can be used after the where condition;

select * from students 
where length(name) = 9;

1.1.4 left truncates a specified number of characters from the left side of the string

• Left (string, n)
  • N represents n characters intercepted on the left side of the string

Example 1: intercepting strings'you and me abc'Left 3 characters
select left('you and me abc', 3);
Example 2: intercepting strings'you and me abc'Left 4 characters
select left('you and me abc', 4);
Example 3: intercepting strings'abc you and me'Left 4 characters
selectt left('abc you and me', 4);

1.1.5 left truncates a specified number of characters from the right side of the string

• Right (string, n)
  • N represents n characters intercepted on the right side of the string

Example 1: intercepting strings'you and me abc'3 characters at the right end
select right('you and me abc', 3);
Example 2: intercepting strings'you and me abc'4 characters at the right end
select right('you and me abc', 4);
Example 3: intercepting strings'abc you and me'4 characters at the right end
selectt right('abc you and me', 4);

1.1.6 substring intercepts a specified number of characters from the specified position of the string

• Substring (string, starting position, n)
  • Start at 1
  • n represents the number of interceptions

Example 1: intercepting strings'you and me abc'3 characters from the second character
select right('you and me abc',2, 3);
Example 2: intercepting strings'you and me abc'3 characters from the left
select right('you and me abc', 1, 3);
Example 3: intercepting strings'abc you and me'1 character from the 4th character
selectt right('abc you and me', 4, 1);

1.1.7 built in functions can be used in the field names displayed in select

Example 1: interception studets Last names of all students in the table
select left(name, 1) from students;
select substring(name. 1, 1) from studets;
Example 2: query students Tabular card Field, intercepting the date of birth,
Show Li Bai's birthday
select substring(card, 7, 8) 
from students
where name = 'Li Bai'; 
Example 3: query students Form all information about students,
Arrange birthdays from large to small
select * from studetns 
order by substring(card, 7, 8);

1.1.8 ltrim removes the space to the left of the string

• Ltrim (string with spaces)

Example 1: remove string' abcd'Left space
select ltrim('    abcd');

1.1.9 rtrim removes the space to the right of the string

• Rtrim (string with spaces)

Example 1: remove string'abcd      'Right space
select rtrim('abcd      ');
select concat(rtrim('abcd      '), 'Test character')

1.1.10 trim removes spaces on both sides of the string

• Trim (string with spaces)

Example 1: remove string'      abcd      'Right space
select rtrim('      abcd      ');
Example 2:
select concat(123, trim('  abcd  '), 'sdsds');

1.2 mathematical functions

1.2.1 round

• Round (number, d)
  • d represents the decimal places to be reserved. If d is omitted, it is 0 by default

Example 1:1.653 Round to the nearest whole number
select round(1.653);
Example 2:1.653 Round to 2 decimal places
select round(1.653, 2);
Example 3: query students The average age of middle school students, rounded,
Keep two decimal places
select round(avg(age), 2) from students;

1.2.3 rand random number

• rand()
  • Each run produces a floating-point number from 0 to 1
• rand() is often used to sort a table randomly
  • order by rand()

select rand();
-- Tip: randomly select a student from the student list
select * from students order by rand() limit 1;

1.3 date time function

1.3.1 current_date returns the system date

• current_data()

1.3.2 current_time returns the system time

• current_time()

1.3.3 return to system date and time

• now()

Example: date and time function
DROP TABLE IF EXISTS a;
CREATE TABLE a (
	id INT PRIMARY KEY NOT NULL,
	indate datetime
);

insert into a VALUES(1, '2021-6-25 14:16:01');
INSERT INTO a VALUES(3,now());

SELECT * FROM a;
id	indate
1	2021-06-25 00:33:08
2	2021-06-25 00:33:18
3	2021-06-25 00:33:26

2. Storage process

2.1 definitions

• In short, it is a set of SQL statements with powerful functions, which can realize some complex logic functions, similar to the methods in JAVA language;
  ps: stored procedures are somewhat similar to triggers. They are a set of SQL sets, but stored procedures are called actively, and their functions are more powerful than triggers. Triggers are called automatically after something is triggered;

2.2 create storage process

- Example 1: creating a stored procedure stu(),query students Table all students
create procedure stu()
begin
	select * from students;
end

2.3 use and storage process

• Call stored procedure:

call stu()

2.4 delete storage process

• When deleting a stored procedure, do not write () after the name

drop procedure stu;
drop procedure if exists stu;

3. View

3.1 definitions

• A view is an encapsulation of a select statement
• A view can be understood as a read-only table. For a view, you can only use select, not delete and update

3.2 creating views

CREATE VIEW stu_nan AS
SELECT * from students WHERE sex = 'male';

3.3 use view

SELECT * from stu_nan;
SELECT * FROM stu_nan WHERE age > 30;

SELECT * FROM stu_nan INNER JOIN scores ON 
stu_nan.studentNo = scores.studentNo;

3.4 deleting views

DROP VIEW stu_nan;
DROP VIEW if EXISTS stu_nan;

4. Services

4.1 why are there transactions

• Why use transactions?
  For example, when a user buys a book online, the number of books is 10. Each book is 10 yuan, and the user account is only 90 yuan in total. When the user wants to buy 10 books, the number is enough, but the account balance is not enough. If there is no transaction in the background, the number will be reduced by 10, but the account balance will remain unchanged. Obviously, this is not in line with the reality, Therefore, to synchronize the changes in the number of books and account balance, transactions need to be configured to ensure that they succeed and fail at the same time.

4.2 what is a transaction

• The so-called transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable work unit.
• A transaction is a collection of sql statements that change data operations.
• begin - start transaction
• rollback - rolls back the transaction and discards the modification of the table
• Commit - commit the transaction to make the changes to the table effective
• No write begin means that there is no transaction, and table operations without transaction are effective in real time
• If only begin is written without rollback or commit, the system will launch and the result will be rollback

4.3 transaction command

• Rolling back

-- affair
-- delete students The record in the table is 001;
-- Delete at the same time scores The record in the table is 001;
-- Roll back the transaction and discard the two deletions at the same time;

BEGIN;
DELETE FROM students WHERE studentNo = 1;
DELETE FROM scores WHERE studentNo = 1;
-- Rollback transaction, discard deletion
ROLLBACK;
-- Query whether rollback is successful
SELECT * FROM students;
SELECT * FROM scores;

5. Index

5.1 thinking:

• Read a book, how to quickly know how many pages to view?
  -Create a catalogue of books;
  -Quickly find the page corresponding to the content through the index of the directory.
• When the amount of data in the table is large, searching for data will become very slow;
• You can create a directory for the table similar to that in books, so as to speed up the efficiency of data query, which is called index in the database;

5.2 index creation

• Statement create index index name on table name (field (length));
• If the field is a string, you need to specify the length of the string when creating the table field

-- Indexes
-- Example 1: for students Tabular age Field creates an index named age_index

CREATE INDEX age_index ON students(age);

-- Example 2: for students Tabular name Field creates an index named name_index
CREATE INDEX name_index ON students(name(10));

5.3 view index

-- WHERE The database system will automatically find out whether there is an index in the field after the condition
SELECT * from students WHERE age = 30;
SELECT * from students WHERE `name` = 'Li Bai';
-- No index, no call
SELECT * FROM students WHERE sex = 'female';

5.4 delete index

DROP INDEX age_index ON students;
DROP INDEX name_index ON students;
-- query students Index in table
show index from students;

5.5 advantages and disadvantages of index

• advantage:
  -The query speed of select statement is greatly improved;
• Disadvantages:
  -Reduce the execution speed of update, delete and insert statements;
• More than 80% of items are select ed, so index is required;
• In practice, if a large number of data modification operations are involved, the index can be deleted before modification, and then the index can be established after modification.

6. MySQL command line

6.1 windows/linux cmd command window connect to mysql and exit mysql

6.2 introduction to MySQL command line

7. Database management operation based on command line

7.1 display existing database

7.2 creating database

• create database database name default charset utf8;
  

7.3 delete database

7.4 adding new users

• grant all on database name Table name to @ 'Login Host' identified by 'password' with grant option;
  -grant all on: represents empowerment of users;
  -Database name: can be *, representing all databases;
  -Table name: can be *, representing all tables, such as database The table name is written as The representative can operate on all databases and all tables;
  -To user name: Specifies the name of the user to be created;
  -@ 'Login Host': @ 'localhost': means that you can only log in locally, @ '%': means that you can log in remotely;
  -with grant option: whether the user can assign permissions to other users.

7.5 modifying user password

• Modify the local login password (if the user exists, modify the password; if the user does not exist, create the user)
  -grant all on database name Table name user name to @ 'Login Host' identified by '111111' with grant option;
• Remote login password modification
  -grant all on database name Table name user name to @ '%' identified by '111111' with grant option;

7.6 deleting users

• Step 1: log in to mysql as root
  - mysql -u root -p
• Part 2: select mysql database
  - use mysql;
• Step 3: Reclaim user test permissions
  - revoke all on . from test@'localhost';
  - revoke all on . from test@'%';
• Step 4: delete user test
  - delete from user where user = 'test';
• Step 5: refresh permissions
  - flush privilleges;
• The content of this chapter is summarized as follows: