Reading guide

I haven't sorted out my articles for some time. It's not the summer vacation again. Feixue is going to come to Boda in the summer vacation. I also mentioned in the last article that I want to prepare for the postgraduate entrance examination. Therefore, this summer vacation may be my last big practice. I'm going nowhere in the summer vacation. I'll stay at home and write a few projects well, possibly in python and java, Look forward to the details! So today's summary is to pave the way for the later projects. It will be used later, but it will not hinder the basic learning of the project. Well, let's "not fat body, fat knowledge" to take a look at this issue of MySQL knowledge!!

Download MySQL

Note: it's best for beginners to use MySQL version 8:00. The stability of the higher version may not be very good. For Xiaobai, it's better to use version 5:00. Download link: Portal

After downloading, you can install the tutorial. You can search csdn for the installation. You must pay attention to ensure a successful installation, because it is troublesome to uninstall and reinstall the database. Pay attention to what problems you encounter during installation. You can communicate in the comment area
Maybe the download will be slow. After all, this is an external network. It seems that the latest version of Baidu online disk can be accelerated. If you want to download faster, you can chat privately and reply to me, mysql5 7. You can get resources. You can also find the "access to all resources" in my blog. In addition, you can find navicat, a visual and convenient database operation software for Amway

This is friendly to novices, but it is recommended that you operate with basic commands. This is not a professional or unprofessional problem. Basic commands are more friendly for you to understand MySQL operation and lay a foundation for you in future practice. Resources are obtained in the same way as above. Just reply to navicat.

Basic command

Tips before operation

MySQL statements look like English sentences, such as "create table tablename" and so on. It looks relatively simple, but we must not take it lightly. Behind the simple operation is complex logic. You will know after in-depth study in the future. These operations can be operated from the command prompt

You can also operate in the software navicat I just mentioned. I've given you how to download the software

Let's talk about the operation from the command prompt. If you learn this, you can basically complete the basic operation smoothly in navicat.

Login

• Start and shut down MySQL server

net start mysql;--start-up mysql The server
net stop mysql;--close mysql The server
# In fact, it's OK to enter directly

• Entry and exit

mysql -u root -p    Enter and enter the password mysql
quit;  and exit;           --sign out mysql

Example:

• Of course, you can also change the password

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

Operations on database commands

Try to knock it yourself

1.show databases; View all your databases currently
2.create database test; Create a file called test Database of
3.drop database test;Delete a named test Database of
4.use test;Select Library ,You must select a database before creating a table
5.show tables; View all tables in the selected database
6.show create database Library name;View the details of creating a library

Operations on tables

Create table:

create table class(
id int primary key auto_increment,#How does the primary key grow automatically
name varchar(32),
number int
);//Created a table with three columns named class. id is an integer, primary key and self growing

1.Modify field type alter table Table name modify Field type;
2.Add new field alter table Table name add Field type
3.Add fields and specify locations  alter table Table name add Field type   after field;
4.Delete table fields  alter table Table name drop Field name;
5.Modify the specified field  alter table Table name change Original field name new field name field type
6.desc Table name;View the fields of the table you are in
7.show create table Table name; View the details of creating a table

Delete table (this will be described separately)

Deleting is a big event. If you are not careful, the data will not come back, so you must be careful when deleting!!!

1 drop table table_name : Deleting all table data and table structures will immediately free disk space,

2 truncate table table_name : Delete all the data in the table, keep the table structure and free up disk space immediately. (you can use the desc tablename To see that the table structure still exists, but use select * from tablename You will find that the data in the table has been deleted.)

3 delete from table_name : Delete all data in the table, and the table structure remains unchanged, mysql Disk space will also be freed immediately.

4 delete from table_name where xxx : Conditional interpretation where The table structure remains unchanged and will not free up disk space.

5 delete After operation, use optimize table table_name Will immediately free up disk space.

6 Delete sure rollback revoke, truncate No.

Data manipulation

1.Insert add data(insert)3 Ways
    1.1 insert into tablename values(Value 1, value 2,...)(Rarely used)
    1.2 insert into tablename(Field 1, field 2...) values(Value 1, value 2,....);((more commonly used)
    1.3 insert into tablename(Field 1, field 2...) values(Value 1, value 2,....)，(Value 1, value 2,....)，(Value 1, value 2,....);
    
2.Delete data(delete) delete from tablename where Condition note: where Conditions must be added, otherwise all data will be deleted

3.Update data(update)  update tablename set Field 1 = Value 1, Field 2 = Value 2 where condition

4.Query data(select)
    4.1 Query all data in the table   select * from tablename
    4.2 Specify data query    select field from tablename
    Data queried by criteria  select field from tablename where condition 
    where Condition followed by condition
     Relationship:>,<,>=,<=,!=  
     Logic: or, and 
     section: id between 4 and 6 ;Closed interval, including boundary
     
5.sort
select field from tablename order by Field sorting keywords(desc | asc)
Sorting keywords desc Descending order asc Ascending order(default)
    5.1 Sort by field
    For example: select * from star orser by money desc, age asc;   
    5.2 Multi field sorting
    select field from tablename order by Field 1  desc |asc,...field n desc| asc;
    
6.Common statistical functions sum，avg，count，max,min
    Group only:select * from tablename group by field
    example: select count(sex) as re,sex from star group by sex having re > 3;
    Grouping statistics: select count(sex) from star group by sex;
    
7.grouping select * from tablename  limit Offset,quantity
    explain:
        7.1.If the offset is not written, the default value is 0
        7.2.The offset must be written when paging is implemented
        How to calculate the offset?:
        limit (n-1)*quantity ,quantity 
        
 8.group by grouping #In fact, this operation is an advanced operation. Here I will briefly say two details, which will be introduced in the following numbers
 such as
 /* View the number and average salary of different positions */
select job,count(empno),avg(sal) from emp group by job;

#You can experience two methods:
Not used
select count(*),order_code 
from lmt_flow_advance_check_apply
where order_code >=  'BJ20190104'
group by order_code;
use
select count(*),order_code 
from lmt_flow_advance_check_apply
group by order_code
having order_code >=  'BJ20190104';

Book delivery at the end

The above is a simple operation command for MySQL. If you want to know more, you can read MySQL must know. If you want to take an advanced look at high-performance mysql, if you can't find resources, you can reply to the corresponding name in the same way as navicat and MySQL above.

Finally, I wish you a happy summer vacation in advance. In the next issue, let's talk about the need of python web framework flash!!! If you have done your job, let's give it to three companies!!!