catalogue

1, Advantages

2, Create databases and tables

3, Basic usage syntax (addition, deletion, modification and query)  

1. Increase

2. Delete

3. Change

4. Check

1, Advantages

1. Simple: compared with other large databases, MySQL is a relatively simple database system, which is easier to use than Xiaobai
2. Economy: compared with some paid databases, MySQL can be used for free, which is very sweet
3. Memory: MySQL database only occupies tens of mb of memory, and low configuration computers can run smoothly
4. Security: MySQL database is completely networked and can be accessed anywhere on the Internet, so we can share the same database with anyone anywhere. Moreover, MySQL can also perform access control, which can control who can't see their own data.
5. Speed: MySQL runs very fast and can beat more than 90% of the databases on the market in terms of speed

2, Create databases and tables

1. The syntax is: create database database name. The following is to create a database named book

   create database book;

2. Use database

   use book;

3. Create a data table, for example:

   -- Create a file named book Table of
   create table book(
   id int primary key, -- The first field, the name of the book ID Field, data type is int 
   name varchar(20), -- The second field is the name of the book(name)Field, data type is String,
   price double(6,2) -- The third field is the price of the book(price)Field, data type is double
   )

   Attributes in table (part):
   a. Data type: select according to your own needs, generally (int,string,double), and the corresponding sql types are (int, varchar (field length), double), Boolean and so on
   b. primary key: sets the primary key. It is generally used as an id and is a unique value. When the id of the primary key in the same table is the same, an error will be reported
   c. auto_increment: automatic growth. The default starts from 1 and does not apply to the varchar() field
   d.default: the default field. The syntax is (name varchar(20) default 'Zhang San'). In this way, the content of the name field of this table is Zhang San by default. Zhang San will be overwritten when adding data

3, Basic usage syntax (addition, deletion, modification and query)  

1. Increase

1. Normal add data

   -- Do not set the field of the table as the primary key for this addition
   insert into book values(1,'Fierce sabre in the snow',39);

2. Addition of specified fields
    

   -- to name Add data
   insert into book(name) values('Fierce sabre in the snow');
   
   -- to name，price Add data
   insert into book(name,price) values('Fierce sabre in the snow',39.99);

3. Setting the addition of primary keys is the same as that of specified fields

   -- 0 if the primary key is not automatically added, or null，1, 2, 3 if auto add is set...
   insert into book(name,price) values('Fierce sabre in the snow',39.99);

2. Delete

1. Delete database

   -- Delete named book Database of
   drop database book;

2. Delete table

   -- Delete named book Table of
   drop table book;

3. Delete data in table

   -- delete book All data in table
   delete from book;
   -- Delete a single piece of data, such as id=1 A piece of data
   delete from book where id = 1;
   -- Delete multiple pieces of data, such as delete id=1，id=2，id=3 Three pieces of data
   delete from book where id in(1,2,3);

3. Change

1. All data for a single field

   -- Modify all data of a single field so that all data in the table name The fields are all Zhang San
   update book set name='Zhang San';

2. Modify specified data

   -- modify id=1 of name data
   update book set name='Snow 1' where id = 1;
   -- modify id=1 of name，price data
   update book set name='Snow 1',price=59.99 where id = 1;

4. Check

1. Query all data

   -- query book Table all data
   select * from book;

2. Exact query name

   -- according to name Make an accurate query, so that only name='Fierce sabre in the snow'Data
   select * from book where name = 'Fierce sabre in the snow';

3. Fuzzy query name

   -- according to name Fuzzy query, so that only name Field contains'snow'Word data
   select * from book where name like '%snow%';
   -- query'snow'Data beginning with a word
   select * from book where name like 'snow%';
   -- query'knife'End of word data
   select * from book where name like '%knife';
   -- query'snow'Data that begins with a word and results in only two words
   select * from book where name like 'snow_';
   -- query'in'Data that ends in a word and results in only two words
   select * from book where name like '_in';

4. Query data by field

   -- query name field
   select name from book;
   -- query name，price field
   select name,price from book;

5. Query data between xx and xx

   -- query id=1 Data to 7
   select * from where id between 1 and 7;

6. join query

   -- Connect multiple tables for query. The following two examples are used. You can continue to add if necessary
   
   -- establish book Table (book table)
   create table book(
   bid int primary key, -- book Tabular id，Set as primary key
   name varchar(20) -- book Tabular name
   )
   
   -- establish client Table (customer table)
   create table client(
   cid int primary key, -- climent Primary Key 
   cname varchar(20), -- climent Table name
   bid int -- book Table in climent The foreign key of the table, so binding the foreign key is easy to delete the table
   )
   
   -- Connect and query two tables, as For alias, it can be written or not
   select * from book as b
   inner join climent as c
   on c.bid = b.bid;
   -- You can also not write as
   select * from book b
   inner join climent c
   on c.bid = b.bid;
   
   -- according to book Tabular name Query data
   select * from book b
   inner join climent c
   on c.bid = b.bid
   where b.name = 'Fierce sabre in the snow';
   
   -- Query some fields of two tables, such as query book Tabular name and climent Tabular cname field
   select b.name,c.cname from book b
   inner join climent c
   on c.bid = b.bid;

The above are some basic MySQL operations, which are also the most commonly used operations. I wrote it first. My eyes are a little sour