Mysql

1, Operation database: CRUD

1. C (Create): Create

   • Create database:

     • create database Database name;

   • Create a database. When it is judged that it does not exist, create it again:

     • create datsbase if not exists Database name;

   • Create the database and specify the character set:

     • create database Database name character set Character set name;    

   • Exercise: create a db4 database, determine whether it exists, and specify the character set as gbk:

     • create database if not exists db4 character set gbk;

2. R (Retrieve): query

   • Query the names of all databases:

     • show databases;

   • Query the subset of a database: query the creation statement of a database:

     • show create database Database name;

3. U (Update): modifying

   • Modify the character set of the database:

     • alter database Database name character set Character set name;

4. D (Delete): Delete

   • Delete database:

     • drop database Database name;

   • Judge whether the database exists, and then delete it

     • drop database if exists Database name

5. Use database

   • Query the name of the database currently in use

     • select database();

   • Use database:

     • use Database name

2, Operation table: CRUD

1. C (Create): Create

   1. Syntax:

      create table Table name(
          Column name 1 data type 1,
          Column name 2 data type 2,
          Column name 3 data type 3,
          .......
          Listing n data type n   --Note: the last line does not contain a comma
      );

   2. Database type:

      • Int (integer type): age int

      • Double (decimal type): score double(5,2)

        • (5,2) indicates that there are 5 decimal places, and two decimal places are reserved

      • Date (date): only include year, month and day, yyyy mm DD

      • datetime (date): including month, day, hour, minute and second, yyyy MM DD HH mm SS

      • timestamp (time error type): including yyyy MM DD HH mm SS

        • If this field is not assigned a value in the future, or the value is null, the current system time will be used by default and the value will be assigned automatically

      • Varchar (string type): name varchar(20) indicates the maximum 20 characters of the name

2. R (Retrieve): query

   • Query the names of all tables in a database:

     • show tables;

   • Query the character set of a table:

     • show create table Table name;

   • Query table structure:

     • desc Table name;

3. U (Update): modifying

   • Modify table name:

     • alter table Table name rename to New table name;

   • Modify the character set of the table:

     • alter table Table name character set Character set name;
       

   • Add a column:

     • alter table Table name add Column name data type;

   • Delete a column:

     • alter table Table name drop Listing;
       

   • Modify column name and data type:

     • alter table Table name modify Column name new data type; --Modify data type only

     • alter table Table name change Column name new column name new data type; --Modify both column names and data types

4. D (Delete): Delete

   • drop table Table name;

   • drop table if exists Table name;

3, DML: data in addition, deletion and modification table

1. Add data:

   • insert into Table name(Column name 1,Column name 2,....,Listing n) values(Value 1,Value 2,....,value n);

2. Delete data:

   • delete from Table name [where condition];

   • be careful:

     • To delete all records:

       • delete from table name-- Not recommended. You can delete as many records as you have

       • truncate table name-- Recommended as like as two peas, first delete the sheet and create a blank table.

3. Modify data:

   • update table Table name set Column name 1=Value 1,Column name 2=Value 2,.....[where condition]; 

   • be careful:

     • If no conditions are added, all the data in the table will be modified

4, DQL: query records in a table

1. Syntax:

   select    Field list
   from      Table name list
   while     Condition list
   group by  Group list
   having    Conditions after grouping
   order by  sort
   limit     Paging limit

2. Basic query:

   1. Query of multiple fields:

      • select Field name 1,Field name 2.... from Table name;

   2. Remove duplicates:

      • select distinct Field name from Table name;

   3. Calculation column: generally, four operations can be used to calculate the values of some columns (generally, only numerical operations will be performed)

      • select name,math,english,ifnull(math,0)+ifnull(english,0) from Table name;

      • be careful:

        • Null participates in the operation, and the calculation results are null

        • Solution: ifnull(english,0): it means that if the value of english is null, its value is 0

   4. Alias: as (can also be omitted, replaced by a space)

      • select name,math (as) mathematics,english (as) English,math+eglish (as) Total score from Table name;

3. Condition query:

   1. Syntax: where condition

   2. Operator:

      • between.......and......

      • in (set)

      • is (not) null

      • and or & & or or not or!

      • like: fuzzy query

        • Placeholder:: Single arbitrary character%: zero or more arbitrary characters

        • select * from stu where like "horse%"   --Query the information of the person surnamed ma
          select * from stu where like "_turn%"   --The second word in the query name is the humanized person
          select * from stu where like "___"   --Query people whose names are three words

4. Query sorting:

   1. Syntax:

      • order by Sort field 1 sort by 1 , Sort field 2 sort by 2 ......

   2. Sort by:

      • ASC: ascending (default)

      • DESC: descending

   • be careful:

     • If there are multiple sorting conditions, the second condition will be judged only when the previous condition values are the same

     • select * from stu order by math ASC , english DESC;

     • In ascending order of math scores, if the math scores are the same, in descending order of English scores

5. Aggregate function:

   • Count: count

   • max: calculate maximum

   • min: calculate the minimum value

   • sum: Calculation and

   • avg: calculate average

     be careful:

     • Calculation of aggregate function, excluding null values

     • Solution: 1. Select a column that does not contain null for calculation: primary key, count(*) 2. ifnull(english,0)

6. Group query:

   1. Syntax:

      • group by group field

7. Paging query:

   1. Syntax:

      • Index starting from limit, number of queries per page

   2. Formula: starting index = (current page number - 1) * number of queries per page

   3. eg:

      --Three records per page
      select * from stu limit 0,3; --first page
      select * from stu limit 3,3; --Page 2
      select * from stu limit 6,3; --Page 3

5, Restraint

1. Non NULL constraint: not null, value cannot be null

   1. Add constraints when creating tables:

      • create table stu(
            id int,
            name varchar(20) not null; 
        )

   2. Add constraints after creating tables:

      • alter table stu modify name varchar(20) not null;

   3. To delete a non empty constraint:

      • alter table stu modify name varchar(20);

2. Unique constraint: unique, the value cannot be repeated

   1. Adding constraints when creating tables: omitted

   2. To add a unique constraint after creating a table:

      • alter table stu modify phone_num varchar(20) unique;

   3. Delete unique constraint:

      • alter table stu drop index phone_num;

      Note: in MySQL, the value of a column limited by a unique constraint can have multiple null values

3. Primary key constraint: primary key, non empty and unique

   1. Adding constraints when creating tables: omitted

   2. Add constraints after creating tables:

      • alter table stu modify id int primary key;

   3. To delete a primary key constraint:

      • alter table stu drop peimary key;

   4. Automatic growth:

      1. When creating a table, add a primary key constraint and complete the automatic growth of the primary key: omitted

      2. Add primary key auto growth after table creation:

         • alter table stu modify id int auto_increment;

      3. Delete automatic growth:

         • alter table stu modify id int; --This will not delete the primary key. Delete the primary key with drop

4. Foreign key constraint: foreign key, which associates the table with the table to ensure the correctness of the data

   1. Add foreign keys when creating tables:

      create table Table name(
          .....
          Foreign key column
          constraint Foreign key name foreign key (Foreign key column name) references Main table name(Main table column name)
      );

   2. To add a foreign key after creating a table:

      • alter table employee add constraint emp_dep_id foreign key dep_id references department(id); 

   3. To delete a foreign key:

      • alter table employee drop foreign key emp_dep_id;

   4. Cascade operation:

      • alter table employee add constraint emp_dep_id foreign key (dep_id) references department(id) on update cascade on delete cascade;

      • on update cascade: if the column name of the main table is updated, the foreign key column name is updated randomly

      • on delete cascade: cascade delete: if the name of the main table column is deleted, the foreign key column name will be deleted randomly

6, Multi table query

1. Internal connection query:

   1. Implicit concatenation: use the where condition to eliminate useless statements

      • select
            t1.name,  --Name of employee table
            t1.gender, --Gender of employee table
            t2.name  -Name of department table
        from
            emp t1,
            dept t2
        where 
            t1.'dept_id' = t2.'id';

   2. Explicit inner join

      • select * from emp [inner] join dept on emp.'dept_id' = dept.'id';

2. External connection query:

   1. Left outer connection:

      • select Field list from Table name 1 left [outer] join Table name 2 on condition;
        -- The query is all the data in the left table and the intersection part

   2. Right outer connection:

      • select Field list from Table name 1 right [outer] join Table name 2 on condition;
        -- The query is all the data in the right table and the intersection part

3. Subquery:

   1. The result of subquery is single row and single column:

      select avg(salary) from emp;           --3000
      select * from emp where salary < 3000;
      combination:
      select * from emp where salary < (select avg(salary) from emp);

   2. The result of subquery is multi row and single column:

      select id from dept where name='Finance Department' or name='Marketing Department';    --2,3
      select * from emp where dept_id = 2 or dept_id = 3;
      combination:
      select * from emp where dept_id = (select id from dept where name='Finance Department' or name='Marketing Department');

   3. The result of subquery is multi row and multi column:

      • --A subquery can be used as a virtual table

        -- The entry date of the employee is 2011-11-11 Subsequent employee information and department information
        ​
        -- Subquery:
        select * from  dept t1,(select * from emp where emp.'join_date' > '2011-11-11') t2 where t1.'id' = t2.'dept_id';
        ​
        --Internal connection:
        select * from emp t1,dept t2 where t1.'dept_id' = t2.'id' and emp.'join_date' > '2011-11-11';

7, Business

1. Basic introduction to transaction:

   1. Concept:

      • If a business operation with multiple steps is managed by a transaction, these operations either succeed or fail at the same time

   2. Operation:

      1. Start transaction: start transaction

      2. Rollback: rollback (a problem is found and the transaction is rolled back)

      3. Commit: commit (if there is no problem with execution, commit the transaction)

   3. There are two ways to commit a transaction:

      1. Auto submit:

         • MySQL database is automatically committed by default. A DML (add, delete, modify) statement will automatically commit a transaction

      2. Manual submission:

         • Oracle database is manually committed by default. You need to start the transaction first and then commit manually

      3. To modify the default commit method of a transaction:

         • To view the default commit method of a transaction:

           • select @@autocommit; --1 for automatic submission and 2 for manual submission

         • To modify the default submission method:

           • set @@autocommit = 0;

2. Four characteristics of transactions:

   1. Atomicity: it is the smallest indivisible operation unit, which either succeeds or fails at the same time

   2. Persistence: after the transaction is committed or rolled back, the database will persist the data

   3. Isolation: multiple things are independent of each other

   4. Consistency: after the transaction operation, the total amount of data remains unchanged

8, Backup and restore of database

1. Backup: mysqldump -u user name - p password database name > saved path

2. Restore: login Database > create key database > Use Database > execute file (source file path)

9, DCL

1. Manage users:

   1. Add user:

      • create user 'user name'@'host name' identified by 'password';

   2. Delete user:

      • drop user 'user name'@'host name';

   3. Query user:

      • 1. Switch to MySQL database: use mysql;
        2. Query user table: select * from user;

   4. Modify user password:

      • update user set password = password('New password') where user = 'user name';
        set passwprd for 'user name'@'host name' = password('New password');

2. jurisdiction:

   1. Query authority:

      • show grants for 'user name'@'host name';

   2. Grant permissions:

      • grant Permission list on Database name.Table name to 'user name'@'host name';

   3. Revoke permissions

      • revoke Permission list on Database name.Table name from 'user name'@'host name';