“This is the 24th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

preface

This time I finally put the basic use of SQL to do a summary, there is a general overview of a lot of content, the part of the problem, you can leave a message, there is time I will take out alone after all this guidance or I as the center, I think I can understand, most people can understand

SQL statements are classified into four types: DDL, DML, DQL, DCL. SQL statements are classified into four types: DDL, DML, DCL. So let’s talk about this operation around these guys.

To read the suggestions in this article, read them in the following order

Mariadb (SQL) Basic operations

SQL Injection Problems

Then read this article, of course, if there is a foundation just to review, then directly read ~

As for environment preparation, it is still the same advice. If you are a Windows user, you are not advised to install mysql directly, you can use an inherited development environment, such as PHPStudy or other database tools. If you are a Linux user, you are advised to install mysql directly. If not, install mysql directly and use VScode or idea to connect to mysql. Of course, other tools can also be used. PIP install mycli is recommended. If you are a Linux user, python3.x will be installed. So you can consider installing PIP or apt install.

I’ve been clamoring for a summary since summer, and now it’s here!

DDL

DDL is the database definition language that defines what the database, the table, looks like. This table is how the database is created drop.

Database creation

1. C (create) Create a database:

    create database yourdatabasename;
    
   Copy the code

   There is judgment

    create database if not exists yourdatabasename;
    
   Copy the code

   Create database, specify character set

     create database name character set utf8;
     
   Copy the code

   case

     create database if not exists Huterox character set utf8;
     
   Copy the code

2. R (Retrieve) query

   Query all

      show databases;
   Copy the code

   Query the creation statement of a database

      show create database name;
      
   Copy the code

3. U(updata) Modify Modifies the character set

   Alter database name character set;Copy the code

4. D(delete) Delete Delete

     drop database name;
     
   Copy the code

   judge

     drop database if exists name;
     
   Copy the code

5. Use Enter the database

    use databasename;
    
   Copy the code

   Query the name of the database in use

     select database();
     
   Copy the code

Manipulating database tables

1. create

   format

   Create table name (column name, column name, column name,...) ;Copy the code

   case

   Create table student(id int, name varchar(32), age int, score double(4,1), insert_time timestamp); create table student(id int, name varchar(32), age int, score double(4,1), insert_time timestamp);Copy the code

   Create a table that is as long as the existing table

   create table A like B;
   Copy the code

2. The query

   Query all data tables

    show tables;
   Copy the code

   See the description of this table

    desc tablename;
    
    
   Copy the code

3. Modify the

   Modify the name of the table

   Alter table name rename to alter table name;Copy the code

   Modify character set

   Alter table name character set;Copy the code

   Add a column (field name)

   Alter table table_name add column name;Copy the code

   Modify column names and types

   Alter table alter table name alter table name alter table name alter table name Alter table table name modify field name attributeCopy the code

   Delete the column

   Alter table drop alter table dropCopy the code

4. delete

   case

   Drop TABLE if EXISTS Indicates the name of a tableCopy the code

The table of CURD

In this case, it’s actually operating on a database table, but it’s splitting two things: one is DML, which is operating on a database table

CUD, add, change, delete. Queries are called DQL, and this one is a bit more complicated, so it’s broken down separately.

DML

Ok, now let’s talk about this DML

1. Add data

   Insert into table name Value, value... ; Insert into table name value (all values);Copy the code

2. Delete the data

   Delete from table name [condition]; delete from A where id=1;Copy the code

   Details if you want to delete everything

   The delete from the table nameCopy the code

   But it’s not good to do that, because he deletes it one by one, so

   Truncate table table nameCopy the code

   This one looks like this

      create table A like B;
      drop table B;
      
   Copy the code

3. Modify the data

   Update table_name set table_name = table_name, table_name = table_name;Copy the code

   Such as:

    update A set age=20 where name = Huterox;
    
   Copy the code

   Also if you don’t add where everything will be changed

DQL

So that’s what we’re going to focus on,

Let’s look at the format first

Select name, name,... From table name WHERE condition group by Having condition operations after a group ORDER by order BY limit Order by orderCopy the code

This is the complete operation, but of course let’s play with the simplest one first.

Single table operation

The simplest example

select * from A
Copy the code

Remove duplicate result sets

select distinct * from A
Copy the code

Simple operation

Simple direct operations are supported in SQL, such as direct summing, and aggregate functions are also available, which you'll know how to play if you've seen the basic use of Pandas. Pandas' DataFrame is similar to the structure of our tables. select a,b,c,a+b from ACopy the code

Suppose we have a table with a, B, and C. And of course there’s a little detail here, if one of a and B is null that adds up to null so we have to deal with the null value problem.

select IFNULL(a,0),IFNULL(b,0),c,a+b from A
Copy the code

If null is present, use 0 instead.

In addition, you can also give individual names

select IFNULL(a,0),IFNULL(b,0),c,a+b as sum from A
Copy the code

The AS can also be replaced with Spaces.

And then we have where, which I don’t think we need to talk too much about, but remember one detail here is that = is not ==, for example

select * from A where id>=20 and id=<30;
select * from A where id between 20 and 30;
Copy the code

Fuzzy query

We also have fuzzy queries

It is clear that _ is a placeholder and % means N _

Just look at the example

Select * from A where name like '_ %'; Select * from A where name like '%'; Select * from A where name like '__ %';Copy the code

The subquery

And of course there’s a subquery here

select name from student where id in (select id from score where math > 10);
Copy the code

It looks something like this.

Then there are our sorting, group query, of course, there are pagination, this part of the content in SQL injection problems have, here we supplement our aggregation function.

Aggregate function: Evaluates a column of data as a whole.

For example, statistics

select count(name) from A;
Copy the code

In addition we have these functions (remember these exclude NULL)

count
max
min
sum
avg
Copy the code

And then we’re going to look at this constraint. This is also important.

The constraint

First of all, our constraints fall into several categories.

1. Primary key constraint: Primary key

2. The non-null constraint is not null

3. Unique constraint

4. Foreign key Constraint Foreign key

There are two ways to add a constraint, one is at creation time

create table student( 
id int primary key auto_increment,
name varchar(32) not null, 
 )charset=utf8;
Copy the code

The previous three constraints are quite simple, but the foreign key operation should be noticed because it involves the whole multi-table operation, which is also the more complex part of the database operation.

A foreign key

First of all, there are two ways to create this foreign key: one is to write it directly, and the other is to add it later

create table student
(
    id int primary key auto_increment, 
    name varchar(32) not null, constrain foreign key nameforeignKey Specifies the foreign key column namereferencesAnother table name (column name)charset=utf8
Copy the code

So the first thing we have to do here is create two tables.

create table student(id int primary key auto_increment,name varchar(128),age int)charset=utf8;

create table class(id int primary key auto_increment,name varchar(128))charset=utf8;
Copy the code

Now that we have created the table, let’s talk about cascading. These two operations are one.

A cascade concept

First of all, in the actual operation process, according to the Development manual of Alibaba, it is forbidden to cascade tables, all relevant operations should be realized in the code logical cascade. In fact, this is also for security, of course, I also know now, WHICH White Hole I do not know how many cascades. The so-called cascade is actually very simple, take an old example. Student list and class list. The student table must have a field where the class ID is stored. Use this constraint to restrict the contents of the class Id field in the student table. For example, if there is no “A” in the class table ID, then obviously there is no “A” in the class table ID field!

Let’s demonstrate this operation.

Now let’s create a class_id as our foreign key

alter table student add class_id int;

Then it is upgraded to a foreign key

alter table student add constraint fg_class_id foreign key(class_id) references class(id);

Of course if you want to delete do so

alter table student drop constraint fg_class_id;
Copy the code

Again, we’re talking about table relationships, and now we’re actually implementing one-to-many relationships for a table and many-to-many, one-to-one relationships, all of which can be implemented through our foreign key cascade.

Table relationship

More than a pair of

Here we can use Djangos ORM as an example. In the SQL statement we directly set a foreign key is good. One of its models looks like this. That’s our example here.

Besides, we can do this directly in Django (much better than mybatis semi-automatic framework)

Look at the code:

class Grade(models.Model) :
	g_name = models.CharField(max_length=32)
	

class Student(models.Model) :
	s_name = models.CharField(max_length=16)
	s_grade = models.ForeignKey(Grade) Add a foreign key constraint
Copy the code

One to one

Now let’s use the metaphor of identity cards and people. So if you want to do this in our SQL, this is how you do it

alter table student add constraint fg_class_id foreign key(class_id) references class(id);
alter table student add constraint fg_class_id_un unique(fg_class_id );
Copy the code

This is the case with Django.

class Person(models.Model) :
	p_name = models.CharField(max_length=32)
	

class Person_id(models.Model) :
	p_id = models.CharField(max_length=32)
	p_person = models.OneToOne(Person)
Copy the code

Many to many

You have to be a little bit careful about this, because it doesn’t work quite the same. So how does that work? Well, let’s take an example of the relationship between a shopping cart and a person, and this is a classic many-to-many.

We need an intermediate table to do that. And then the foreign key binding.

I have an example of this in my SpringBoot + Vue, how that table is designed.

This time, I will use all of my technology, Java technology stack, Python technology stack, and then build a push system based on Flink. As for which distributed system is based on SpringCloud, or K8S, this will have to wait for me to make a good hole in the distributed system. I don’t believe that I can’t finish the project in the second semester of sophomore year. After that, the project should be OK, and then I will make a project focusing on artificial intelligence (in case that is not done, it will be used as a backup plan).

class Customer(model.Model) :
	c_name = models.CharField(max_length=16)

class Goods(models.Model) :
	g_name =  models.CharField(max_length=16)
	g_customer = models.MangToMangField(Customer)
Copy the code

Now that we’ve built the model, we’re going to manipulate it, and then we’re going to talk about how this thing works. The fetch query section is the same as before, but there is an addition relationship, such as adding an item to our shopping cart.

Phone = Goods()
Phone.g_name="iPhone12"
Phone.save()
Me = Customer.objects.get(c_name="xiaoming")
Me.goods_set.add(Phone)
			.remove(Phone)
			.set(Phone1,Phone2,...)
Copy the code

Of course, we could do the opposite, but the effect is the same, and you can see why in the picture below.The same is true if you implement it yourself, and it’s even more complicated.

Master-slave table

Speaking of which, the one in the mouth is the one in Django that comes from the table. Join table problems occur every time multiple tables are involved. In Django, any id table that has a constraint, such as the relationship between people and IDS, is a slave table. When the primary table is deleted, the delete on cascade is done by default. We should set it so that the main table can only be deleted if the corresponding data in the table is deleted. (In fact, this is the cascade operation)

class Person_id(models.Model) :
	p_id = models.CharField(max_length=32P_person = models.onetoone (Person,ondetele= models.protect) SETNULL SET_DEFAULT() SET()Copy the code

Cascade operation

This is the SQL representation of our master-slave table, which is actually our cascading operation. That is,

This is very easy to understand that is the class ID changed, so the corresponding student class ID should be changed, of course, here can be directly in the database, can also be through the code, but here recommended code implementation, that is, in Java to do

So just add one thing to the database

alter table student add constraint fg_class_id foreign key(class_id) references class(id) on delete cascade on update cascade;
Copy the code

Careful! Careful! Careful! The default is empty, that is, there will be no action.

League table query

In fact, this query is to integrate the contents of two tables. In fact, it is more graphic that is the relationship between the intersection of two sets of sets, if we treat the result of each query statement as a set. There are three main types of words: inline, left and right. In addition, it can be broken down, as shown in the picture below

Here we mainly say the first three, because the rest are just adding conditions to the end.

Here’s a formula

1. Specify the data (fields) to be queried 2. Specify which tables to query 3. Clear screening criteria

Here’s an example. Let’s say we have A class A and its Id is 1. Now let’s query the name and age of the student in class A.

select s.name,s.age  
from student as s 
inner join class as c 
on s.class_id = c.id
where s.id=1;
Copy the code

Left/right syndication table

This is actually quite simple, but the difference is actually the difference in the previous picture.

select s.name,s.age c.name 
from student as s 
left join class as c 
on s.class_id = c.id
where s.id=1;
Copy the code

Let’s say we have a student who is not in a class, who doesn’t have a class ID, and then we’ll see that one of the c. names is null and that’s itSo does the rightIt’s all very simple stuff, but very important!

Multi-table LianZha

This is the same thing, but let’s say we have another table

create table grade(id int primary key auto_increment 
grade float,
student_id int.foreign key(student_id) references student(id) )charset = utf8;
Copy the code

Now I also need to find out the student’s name, age and grade in Class A

select name,age,grade
from student as s inner join
class as c on s.class_id = c.id
inner join grade as g
where g.student_id = s.id and c.id=1;
Copy the code

See, the result of a pair of queries from left to right is a left set

So this is the end of the basic query. Mybatisplus multi-table paging query + blog display (now python, Java examples all have, next time I will see how to use Go to play)

You think this is the end of it? NO, NO, at this point you guess to learn the basic use, there is something behind, here I will say all.

Paradigm constraints

When designing a database table, suggest following constraints, requirements, suggestions.

There are six paradigms

1. First Normal Form (1NF)
2. Second normal Form (2NF)
3. Third normal Form (3NF)
4. Bush-codvan formula (BCNF)
5. Fourth normal Form (4NF)
6. The fifth normal Form (5NF, also known as perfect normal Form)

This one basically follows the first three completions.

So let’s talk about these paradigms

1. First Normal Form (1NF) : Each column is indivisible
2. Second normal form (2NF) : Eliminate partial function dependence on master code on the basis of 1NF
3. Third normal Form (3NF) : On the basis of 2NF, any non-primary attribute does not depend on any other primary attribute

case

The first paradigm

To look at this graph in the first place is not to satisfy the first normal form

This column has to be indestructible so I have to change it like this

The second paradigm

For example, A–> B student number can determine the name (student number, course name) can determine the grade is represented by the letter is A- > B partial function dependence (A,B)->C complete function dependence

So the code here is our (A), (A, B) attribute group

Now if we want to conform to the second normal form we have to remove the column that doesn’t conform to the complete function dependence and put it somewhere else. So this is what it looks like

Because of the name, department name, head of the department In (A, B) – > C as long as A, or B to satisfy is established, so must be broken up.

The third paradigm

On the basis of the second paradigm, we took apart the course selection list, but the student list actually had problems

Student ID –> Dean Department name –> Dean

B–>C (A,B) –>C (A,B) –>C

A->B A->C B,C is not related

So is the detached timepiece

So that satisfies the third normal form, which is actually a very complicated thing to say, but I think I should always think about it when I design, because for no other reason, I don’t want to have redundant information when I look up the user, and then I have to deal with it in the code.

Database backup restoration

Now talking about the database backup restore, this is also a tool or direct command this is the same as Redis backup is actually a backup statement.

mysqldump -U a username-P Password Of the database to be backed up>Path (Save)Copy the code

reduction

Source yourssqlfileCopy the code

Then this is the backup restore database, actually is very simple, then for the operation of the database and several, one is the transactions, and one is the view, and is a function that write SQL, the last is our DCL, this part of the content is not a lot, not more complicated than the DQL.

Transaction (trans action)

This transaction is atomic operations, is something that can’t break up, children about it, for example, you have to have a girlfriend, and then you can have a wife, and then you can have children (of course without a wife is theoretically possible but you have to have a woman, we according to the normal ethics) the children, the above link a cannot little, but less, Then this thing fails, then you have to start all over again, we call this rollback, so this transaction has two parts, operation, failed rollback. (It is worth mentioning that transactions in Redis are not rolled back.)

Here are four characteristics of transactions and what separates them from each other.

Strictly speaking, business is: a series of things will either all succeed, or all GG. Use format:

Statrt Transaction Starts a transactionrollbackThe rollbackcommitsubmitCopy the code

Now let’s take a small concrete example. Let’s say we have a banking system and we want to transfer money.

So we’ll leave it at that

starttransaction; Used herebegin; Same with update Accountset monery=monery- 500. where name='Joe';
update account set monery=monery+500 where name='bill';
commit; If no exception is submittedrollback; Otherwise the rollbackCopy the code

Let’s do it again in Python.

import pymysql

# establish a connection
conn=pymysql.connect(
    host='127.0.0.1',port=3306,user='Huterox',
    passwd='865989840',db='huterox',charset='utf8'
)
cursor = conn.cursor()

try:

    Sqlcomm = """insert into hello(name) value(%s)"""
    cursor.execute(Sqlcomm,("Xiao gang"))

    conn.commit()# submit
except pymysql.Error as err:
    conn.rollback()# the rollback
    print(err)
finally:
    conn.close()

Copy the code

This completes our basic transaction operations (Java is too much work, so I’ll show you in Python)

Transaction Commit Details

Note that MYSQL is automatically committing DML statements, and there is no default in Oracle.

You can set yourself to submit manually

set @@autocommit =0; 
Copy the code

Then follow each statement with a COMMIT.

Four characteristics of transactions

atomic

The smallest nonseparable unit of operation that either all succeed or all fail

persistence

Commit Rollback is persistent

Isolation,

Transactions are independent of each other

consistency

The total amount of data before and after transaction operation remains unchanged (A transfers money to B, the total amount of money remains unchanged)

The isolation level of the transaction

Multiple transactions are independent of each other, but problems can occur if multiple transactions operate on the same batch of data at the same time, and isolation levels between transactions need to be set to resolve the problems. Here comes the following question

1. Dirty read: a transaction that reads data not committed in another transaction (data not committed but cached)
2. Non-repeatable read (virtual read) : Different data is read twice in the same transaction.
3. Phantom read: all records in a DML table that were added by another transaction, causing the first query to fail to make the change (missed read)

In mysql, there are four levels of isolation (in fact, multiple transaction commit policy, the problem is also because we have multiple clients, can imagine the multithreading, in addition, the more problems solved, the slower the speed, for example, serializable table can be directly locked)

Isolation level:

1. Read Uncommitted A policy that can be committed for all three problems, but is most efficient
2. Read COMMITTED (Oracle default) 2 3 The problem cannot be resolved
3. Repeatable read repeatable read (mysql default
4. Serializable Serial call ALL can be ok

It’s all about efficiency, depending on what strategy you want to take.

Level operation

In this case, there are two instruction query levels

    select @@tx_isolation;
    
Copy the code

Set up the

Set Global transcation Isolation Level Level The value is a character string;Copy the code

I will not make a demonstration here. Considering the space, I can call and leave a message later. I will talk about this, otherwise I will leave it at that

So that’s the part about transactions

view

Save our query results so that we can use them next time, as well as as a privilege isolation function (DCL).

Create view View name as your DQL statement;Copy the code

Then show tables; When you create a view, you can simply create a view, which is actually a special table, to improve your code efficiency. But the thing is, if the data changes, then your visual chart changes, so it goes like this

When you change the view chart, the original linkage table will also be modified. If you delete it, you just

Drop View View chart nameCopy the code

The stored procedure

Stored Procedure is a kind of database object that stores complex programs in a database so that external programs can call them. Stored procedures are sets of SQL statements that accomplish specific functions, which are created by compilation and Stored in the database. The user then calls the name of the stored procedure to execute the call.

In plain English, this is equivalent to a function, but CURD programmers, this should not be used, I am not much use, maybe my small demo is too simple.

Before we play, let’s discuss why this SQL statement always; It only works at the end. I don’t want that; How about the end. The answer is yes.

delimiter ; Settings; Is the statement terminator (default)

Delimiter && Sets && to the statement terminator

I see. That’s easy to do.

Now let’s write a simple stored procedure.

There’s a table like this

Now I want to write a function that will directly help me view the information in this table.

So I do this

delimiter &&
create procedure showstu()
begin
select * from student;
end &&
delimiter ;

call showstd();
Copy the code

And the ones that were deleted later

drop procedure showstu;
Copy the code

Will do.

Finding stored procedures

This is easy

show procedure status like "show%";
Copy the code

This fuzzy query is also supported

In addition

show create procedure showstu;
Copy the code

You can see what’s inside

You can see that

The ginseng

If I want to get information about an individual student, I pass in an ID and I get a message. This is actually pretty easy

Let’s write a function called findById ()

DCL

Finally, this is our last one, this is our database management, permission statements, of course, and user creation, such as the initial mariadb (SQL) basic operation to create a user. This is intended primarily for DBA database administrators. This is actually what our Mariadb (SQL) basic operation user is doing

User creation

The format of the basic directive created is as follows:

create users 'usrname'@'Specify user login address' identified by'password'; # query userselect * from mysql.user;
Copy the code

Such as:

create users 'Hello'@'localhost' identified by'abc123'; Here,'localhost'Is the machine IPThe '%'Is any IP addressCopy the code

You can do this if you want to create a user that you can connect to remotely

create users 'Hello'@The '%' identified by'abc123';
Copy the code

Change a user’s password If you forget your password, you can log in to the database using another account that has sufficient permissions to change that account.

set password for 'Hello'@The '%' = password('abcd123');
Copy the code

The user to delete

drop usr 'Hello'@The '%';
Copy the code

User permissions

When we create a user we can pass

show grants for 'Hello'@'localhost';
Copy the code

View for example; Permission to modifyFirst of all, what are the permissions;SELECT INSERT CREATE DELECT DROP UPDATE CRANT OPITONThe same permissions given to other users (the right to be someone else’s father) now we give it the right except CREATEOPTION

grant all privileges on *.* to Hello@localhost;
Copy the code

revoke GRANT OPTION on *.* from Hello@localhost;
Copy the code

flush privileges; Make the configuration workCopy the code

I’ll just copy it over here.

conclusion

This is I spent an afternoon finally summed up the basic use of mysql, basically commonly used is these, things are more suitable for review, or take preview is also good.