1, Mysql database

1. What are sql, DB and DBMS, and the relationship between them?

• DB: database (database, which actually exists as a file on the hard disk)
• DBMS: database management system (database management system, including Mysql, Oracle and DB2)
• SQL: structured query language, a standardized general language. Standard SQL is suitable for all database products. In fact, SQL statements are compiled internally before SQL execution (the compilation of SQL statements is completed by DBMS)
• DBMS is responsible for executing SQL statements and operating data in DB by executing SQL statements
• DBMS -- (execution) – > sql -- (operation) – > DB

2. Classification of SQL statements

1. DQL (database query language): query statements. All select statements are DQL
2. DML (database operation language): insert, delete and update to add, delete and modify the data in the table
3. DDL (data definition statement): create, drop, alter, addition, deletion and modification of table structure
4. TCL (transaction control statement): commit transaction, rollback rollback transaction, (T is Transaction)
5. DCL (data control statement): grant authorization, revoke authority, etc

3. The file ends with sql. Such a file is called "sql script". What is an sql script?

When the extension of a file is. sql and a large number of sql statements are written in the file, we call such a file sql script
Note: you can execute sql scripts directly using the source command
When the amount of data in the sql script is too large, it cannot be opened. Please use the source command to complete the initialization

4. MySql common commands

1. show databases; See which databases are available
2. use database_name; Which database do you use
3. select database(); View the database currently in use
4. \c exit writing sql statement
5. exit ; Exit Mysql database
6. show tables; View tables in the current database
7. show tables from database_name; View tables in other libraries
8. desc table_names; View table structure

5. What is a watch?

1. Table is the basic unit of database. All data is composed in the form of table for strong readability
2. A table contains rows and columns:
   1. Row: called data / record (data)
   2. Columns: called columns
3. Each field should contain the field name, data type, and related constraints

6. Simple query

• All data is stored in mnyuansql database

  • desc dept;
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | DEPTNO | int(2)      | NO   | PRI | NULL    |       |
    | DNAME  | varchar(14) | YES  |     | NULL    |       |
    | LOC    | varchar(13) | YES  |     | NULL    |       |
    +--------+-------------+------+-----+---------+-------+
    

  • desc emp;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | EMPNO    | int(4)      | NO   | PRI | NULL    |       |
    | ENAME    | varchar(10) | YES  |     | NULL    |       |
    | JOB      | varchar(9)  | YES  |     | NULL    |       |
    | MGR      | int(4)      | YES  |     | NULL    |       |
    | HIREDATE | date        | YES  |     | NULL    |       |
    | SAL      | double(7,2) | YES  |     | NULL    |       |
    | COMM     | double(7,2) | YES  |     | NULL    |       |
    | DEPTNO   | int(2)      | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    

  • desc salgrade;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | GRADE | int(11) | YES  |     | NULL    |       |
    | LOSAL | int(11) | YES  |     | NULL    |       |
    | HISAL | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    

• Syntax format:

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

• Example:

  --1.Query employee's annual salary(Fields can participate in mathematical operations)
  --Note: try to use single quotation marks for strings
  --as You can alias fields, as Can be omitted
  select ename,sal * 12 as 'year_sal' from emp;
  --2.Query all fields(It is not recommended to use in development, and the efficiency is low)
  select * from emp;
  

7. Condition query

• Syntax format:

  select 
  	Field 1,Field 2...
  from 
  	Table name
  where 
  	query criteria;
  

• Execution order: from, where and select

• Conditional operators supported by where:

  operator	explain
  =	be equal to
  < > or=	Not equal to
  <	less than
  <=	Less than or equal to
  >	greater than
  >=	Greater than or equal to
  between ... and ....	Between two values, equivalent to > = and<=
  is null	Null (is not null, not null)
  and	also
  or	perhaps
  in	Contains, which is equivalent to multiple or (not in is not in this range)
  not	not can take non, mainly used in is or in
  like	like is called fuzzy query. It supports% or underscore matching% to match any character underscore. An underscore matches only one character

• Example:

  --Query the name of an employee whose salary is equal to 5000
  select ename from emp where sal = 5000;
  --query SMITH Salary
  select ename,sal from emp where ename = 'SMITH';
  --Identify employees with wages higher than 3000
  select ename,sal from emp where sal > 3000;
  --Find out if the salary is not equal to 3000
  select ename,sal from emp where sal <> 3000; --or
  select ename,sal from emp where sal != 3000;
  --Find employees with salaries between 1100 and 3000, including 1100 and 3000
  --between...and...It can be used not only between numbers, but also between strings(less)
  select ename,sal from emp where sal >= 1100 and sal <= 3000; --or
  select ename,sal from emp where sal between 1100 and 3000;
  --Find out who doesn't have an allowance in the database null Represents no value, not null
  select ename,comm from emp where comm is null or comm = 0;
  --Finding jobs is MANAGER and SALESMAN Employees
  select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; --or
  select ename,job from emp where job in ('MANAGER','SALESMAN');
  --Find employees whose salary is greater than 1000 and department number is 20 or 30
  select ename,sal from emp where sal > 1000 and deptno > 20 or deptno < 30;--FALSE
  select ename,sal from emp where sal > 1000 and (deptno > 20 or deptno < 30);
  --Find the name with'o'Employees
  select ename from emp where ename like '%o%';
  --Find the second letter'A'Employees
  select ename from emp where ename like '_A%';
  --Find the name with'_'Employees
  select ename from emp where ename like '%\_%'; --use\Escape
  --Find the last letter in the name 'T'Employees
  select ename from emp where ename like '%T';
  

8. Data sorting

• Syntax format:

  select
  	Field 1, field 2...
  from
  	Table name
  where 
  	query criteria
  order by
  	sort field;
  

• Execution order: from, where, select, and order by

• Example:

  --Find out the employee name and salary in ascending order?
  select ename,sal from emp order by sal;
  --The default is ascending: asc Indicates ascending order desc Indicates descending order
  --Sort according to the descending order of wages. When the wages are the same, sort according to the ascending order of names
  select ename,sal from emp order by sal desc,ename;
  --Note: the more front-end fields play a leading role. Only when the current field cannot be sorted, the later fields will be enabled
  --Finding jobs is'SALESMAN'Employees and sort them in descending order of salary
  select ename,job,sal from emp where job = 'SALEMAN' order by sal desc;
  --Proof ranking order by The statement is the last to execute
  select sal * 12 as salary from emp where job = 'SALESMAN' order by salary asc;
  

9. Grouping function

1. count() count

2. sum()

3. avg() average

4. max

5. min

6. There are 5 grouping functions in total. All grouping functions operate on "a group" data. Alias: multi line processing function. Features: input multiple lines, and the final output result is 1 line. The grouping function automatically ignores null values

   • Example:

     --Find the maximum wage
     select max(sal) from emp;
     --Find the minimum wage
     select min(sal) from emp;
     --Calculate average wage
     select avg(sal) from emp;
     --Calculate total wages
     select sum(sal) from emp;
     --Find the total number
     select count(ename) from emp; -- or
     select count(*) from emp;
     --Calculate the annual salary of all personnel, there are always in the database nul The results of the operations involved are null
     select ename,(sal + comm) * 12 as year_sal from emp; --FALSE
     select ename,(sal + ifnull(comm,0)) * 12 as year_sal from emp;
     --ifnull() The single line handler will likely be null Single line processing function: input a line and output a line
     

   • What is the difference between count(*) and count (a specific field)?

     • count(*): not the number of data in a field, but the total number of records. (independent of a field)
     • count(comm): indicates the total number of non null data in the comm field

10. Grouping query

• Syntax format:

  select
  	Field 1, field 2...
  from
  	Table name
  where 
  	query criteria
  group by
  	Field name;
  

• Execution order: first from, then where, then group by, and then select

• Note: the grouping function is executed after group by

• Example:

  --Identify employees whose wages are above average
  select ename,sal from emp where sal > avg(sal); --FALSE
  --Grouping function(count max min avg sum)Execute in group by Statement, and group by Statement execution in where After the statement,
  --That is, only where Only after the execution is completed can it be executed group by Statement, and then to the grouping function, so an error is reported
  --as for select sum(sal) from emp;Using grouping functions directly like this sql The statement is actually omitted group by sentence,
  --Treat the whole table as a group, so in where Grouping functions cannot be used in statements
  select ename,sal from emp where sal > (select avg(sal) from emp);--Subquery, correct
  --Find out the highest salary for each job
  select max(sal),job from emp group by job;
  --After grouping select Only grouping function and grouping basis fields can appear later, otherwise they are all wrong
  select max(sal),job,ename from emp group by job;--FALSE
  --Think about it ename The number of entries must be greater than that after grouping
  --How to display the data?So the statement is wrong
  --Can multiple fields be grouped together? The answer is yes. When you understand it, you can combine multiple fields as a new field
  --Find out the highest salary for different positions in each department
  select deptno,job,max(sal) from emp group by deptno,job;
  --Find out the maximum salary of each department and display the data with salary greater than 2900
  select max(sal) from emp where sal > 2900 group by deptno; --or
  select max(sal) from emp group by deptno having max(sal) > 2900; --Low efficiency
  --Find out the average salary of each department and display the data with salary greater than 2000
  select avg(sal) from emp group by deptno having avg(sal) > 2000;
  

• How should a completed DQL statement be written?

  select 						5
  	Field 1, field 2...
  from						1
  	Table name
  where						2
  	query criteria
  group by					3
  	Grouping conditions
  having						4
  	Query criteria after grouping
  order by					6
  	sort..;
  

11. Remove duplicate records

1. The distinct keyword must appear first in all fields

2. When distinct is used for de duplication, if there are multiple fields, the de duplication is performed after combining multiple fields

3. Example:

   -- How many jobs does query have job
   select distinct job from emp;
   -- Count the number of Posts
   select count(distinct job) from emp;