https://blog.csdn.net/m0_50546016/article/details/120070003

I ❤ Introduction to database and SQL ❤

Case insensitive

1. SQL classification
SQL can be divided into two parts: data operation language (DML) and data definition language (DDL).

⭐ Data query language (DQL)

⭐ Data Manipulation Language (DML)

• Data definition language (DDL)
• Data control language (DCL): permissions

2. SQL function
SQL is an ANSI standard computer language used to access and operate database systems. SQL statements are used to retrieve and update data in the database.

SQL database oriented query execution
SQL retrieves data from the database
SQL inserts new records into the database
SQL updates the data in the database
SQL deletes records from the database
SQL creates a new database
SQL creates a new table in the database
SQL creates stored procedures in the database
SQL creates views in the database
SQL can set permissions for tables, stored procedures, and views

**3. What is the database**
What is a database - as the name suggests, you can understand that a database is a container for storing data.
DB+DBMS storage + management
A database is where data is stored. It is precisely because with the database, we can directly find the data. For example, you use yu'e Bao to check your account income every day, which is given to you after reading data from the database; library

The most common database type is relational database management system (RDBMS):

RDBMS is the foundation of SQL and all modern database systems, such as MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access.
The data in RDBMS is stored in database objects called tables. A table is a collection of related data items. It consists of columns and rows.

• Non relational database (NoSQL)
• Relational database (SQL structured query language as DDL and DML) > two-dimensional table
  (the bottom layer is similar)
• Distributed database
  Differences among Oracle, MySQL and SQL server

4. Several common Oracle clients

• SQL*PLUS
• PL/SQL Developer (third-party software) > imitating Oracle SQL Developer (not as good as PL)
• Navicat (third party software) (Oracle+MySQL)

scott user introduction

Oracle Database - scott user (4 tables)

• orcl is the default instance of Oracle database, with sys advanced permissions and database objects (tables, views, indexes...)

• Textconnection name; scott user name; localhost address; orcl user instance name
• orcl and text are the same database instance, so they are the same database, but they have different users, different permissions and different tables.
• scott's four tables
  Because the naming does not conform to the current standard, it is difficult to see the meaning of the name, and new users appear
  
  

II 🌙 DQL 🌙

(Oracle)

1. Single table query

🐦 select xxx from xxx where

 select columeName1,columeName2... from tableName where...
Search for qualified records eg: Take out consistent xxx Conditional person

① Expression

Refer to expressions in Java, arithmetic operators, logical operators, assignment operators

- keyword
   between and Equivalent to<= =>
- Logical operator  and  or  not
- empty         is null  and is  not null
- in replace or(Multi table query)

/*Query the people whose salary is more than 1000 in the company and output their name and salary*/
select ename,sal from emp where sal>=1000;

select ename,sal from emp where sal>=1000 and sal<=1500;
select ename,sal from emp where sal between 1000 and 1500;
/* between and Equivalent to < = = > 
People whose output salary is greater than or equal to 1000 and less than or equal to 1500*/

select ename, comm from emp where comm is not null;
/* Query the person with bonus in the company, that is, the bonus is not empty, and output the name and bonus 
Note: whether there is a bonus or not is different from whether the bonus is 0
*/

② Fuzzy query like placeholder%

Placeholder: _ -Refers to a character; %-Refers to any number of characters (0 to infinity)

select ename from emp where ename like '%m%';
/* Query the person whose name contains M, which can be% m, M%,% m%, M 
Query result should be empty
*/
select ename from emp where ename like '%M%';

select ename from emp where ename like '_M%';
/* Query the person whose second letter in the name is m*/

As can be seen from the figure, the names of all members with m include SMITH, but note that M is capitalized.

Column names are not case sensitive and are all capitalized; However, content, that is, data, is case sensitive

🐒 select xxx from xxx

select columeName from tableName；
As long as a certain attribute eg: Take out all names

select * from emp;
/*    Query all columns in table emp, where * indicates all columns   */

select ename from emp;
/* Query the column named ename in the emp table  */

select ename ，empno from emp;
/* Query the columns named ename and empno in the table emp. Separate multiple columns with ','
Display in column name order
 */

① Alias as

as " "(Can be omitted as " "，(must add "") when there are spaces in the alias
 Double quotation marks -- strings, case sensitive; When there are no double quotation marks, they are all capitalized and are not case sensitive.

Different from string, double quotation mark, double column name

select ename as "Name ", empno from emp;
select ename full name, empno from emp;
select ename "Name ", empno from emp;

② Expressions can exist in queries

select ename ， sal*12 as "nian xin" from emp;
/*  Query name and annual salary ABCD = monthly salary * 12 */

③ Splice string||

- Java of use+Splice strings, SQL Medium or(||)

select ename||'job is'||job from emp;
/* Query ename splice string job is splice job. Pay attention to single quotation marks of string and double quotation marks of column name */

④ De duplication distinct

select job from emp;
/*For example, if you query the type of work of the company, all of them will be listed in this way, and there are duplicates*/
select distinct job from emp;
/* duplicate removal */

🐢 order by asc/desc

-	Sort and output the query results in a certain order
- 	asc(Default) from small to large
	desc From big to small

/* Take out employees whose salary is greater than or equal to 1000 and sort the salary from high to low*/
select sname ， sal from emp where sal>= 1000 order by sal desc;
select sname ， sal from emp where sal>= 1000 order by sal desc，comm desc;
/* Multiple conditions are sorted, and the second condition is completed when the previous condition is met */

🐅 System function

Oracle single line function multi line function

(1) Single line function

Each data is calculated independently, and then each data gets a result.

1. Features
① The real data is not changed, but the data is further modified or processed and displayed.
② Can be mixed with fields

2. Use

 select Field name, function name(Field name)，Field name....from Table name

3. Classification:
String -- initcap (char) lower (char) upper (char) replace (char, search_str, replace_str) instr (char, substr [, POS]) substr (char, POS, len) concat (char1, char2)
Value - ABS(n) CEIL(n) FLOOR(n) MOD(m,n) ROUND(m,n) TRUNC(m,n)
Date - MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY (different for each database)
Conversion -- a function that converts between types
to_char() to_date() to_number()
General: functions that can be used for string, numeric value and date

select instr('hsdhdhs','d') from dual;//3
select instr('hsdhdhs','d',4) from dual;//5

select sysdate from dual;
select ename , hiredate ,sysdate-hiredate from emp;//Date difference = working days
select round(sysdate , 'year') from emp;//less

select extract(year from sysdate) from dual;//Year of return date
select extract(month from sysdate) from dual;//Month of return date

select to_char(sysdate,'yyyy.mm.dd.hh24:mi:ss  dy') from dual;
select to_date('2022-2-18','yyyy-mm-dd')from dual;
......

 be careful: dual Represents that the virtual table is an empty table, which is often used to query the system date sysdate
 		SUBSTR And Java Medium distinction

(2) Multiline function

Multiple data are calculated at the same time, and finally a result data is obtained. It also becomes an aggregation function and grouping function, which is mainly used to complete some statistical functions. (group function)
 Generally, group functions are used after grouping, but group functions may not be used after grouping, that is, they are called for all records
 (max,min,avg,sum,count)Very important

1. Function: make statistics on the queried data
2. Use

select Multiline function name(Field name),Multiline function name(Field name)..from Table name

select sal*12+nvl(comm,0) ,sal*12 from emp;
select count(sal) from emp;

Note: multi line functions cannot be mixed with ordinary fields and single line functions unless grouped
– max (field name) returns the maximum value of the field
– min (field name) returns the minimum value of the field
– sum (field name) returns the sum of the field
– AVG (field name) returns the average value of the field
–count
– count(*) returns the number of records in the table
– count (field name) returns the number of non null values
– number of duplicate field names (distinct)

🐘 group by having -- group function

Grouping, using group function to carry out unified recording operation

	1.    select xxx from xxxx group by xxxxx ；
	2.    having Filter the grouped data
	       select xxx from xxxx where ① group by ② having ③;   before①where Row filter, after②Group and finally③After grouping having filter

Common errors

group by after grouping, there can only be two things in the filter column - group function and grouping condition
select count(sal) ，deptno ，job from emp group by deptno；

select count(sal) ，deptno  from emp group by deptno;//Check the number of employees in each department
select count(sal) ，deptno ， max(sal) from emp group by deptno;//Check the number of employees in each department and the maximum salary
//select count(sal) ，deptno ，job   from emp group by deptno；
select count(sal) ，deptno ， max(sal) , job from emp group by deptno ,job order by deptno;//Check the number and maximum salary of employees in different departments and different types of work, and sort by department number

select count(sal) ，deptno ， avg(sal) from emp where mgr is not null group by deptno order by deptno;
//The average salary and number of employees other than boss in different departments are sorted by department number
select count(sal) ，deptno ， avg(sal) from emp where mgr is not null group by deptno having avg(sal)<2000 order by deptno;
//For other employees except boss, the average salary and number of employees in different departments whose average salary is less than 2000 shall be sorted by department number

🐕 SQL execution order is important

[interview involved]

1. from table
2. join
3. on
4. where filter rows
5. group by (start using the alias in select, which can be used in subsequent statements) - group function
6. Filtering after grouping
7. select filter column
8. distinct de duplication
9. order by sort
10. limit

2. Multi table query

Classes in Java programs correspond to entity tables in the database. There are five kinds of relationships between classes. In fact, there are also relationships between tables. Multi table operations often require multi table queries.

	Subqueries and table joins are usually mixed. Subqueries are the results of the same table. Table joins are the results of multiple tables

(1) Table join - the key columns of the query result set are records in multiple tables

① Internal connection (most commonly used) has no master-slave internal join on and can be omitted

Equivalent = and self connection

/* Query employee and department information → equivalent internal connection*/
select ename,dname from emp  inner join dept  on emp.depno = dept.deptno;//Basic writing method
select ename ,dname from emp e inner join dept d on e.depno = d.deptno;//Alias simplification
select ename ,dname from emp e ,dept d where e.depno = d.deptno;//simplify
select ename ,dname ，e.deptno from emp e ,dept d where e.depno = d.deptno;//deptno appears in two tables. It is unknown which one needs to be indicated in the query

//When the primary key and foreign key names are the same, on can be replaced by using
select ename ,dname from emp e inner join dept d using(deptno);//on simplification

/*  Query the names of employees and their leader s → self connect*/
select ename as employee ，ename as leader from emp e,emp l where e.mgr = l.empno;//How to solve the problem of one less employee?

Non equivalent < >

/* Query the employee's name and salary level → non equivalent internal connection */
select ename ，grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal; 

select ename ，grade from emp e ，salgrade s where e.sal between s.losal and s.hisal; //simplify

② The external connection has a master-slave left / right outer join on, and the outer can be omitted

All adjective keywords are used as the main table
The primary table can be specified with (+) in Oracle, but MySQL does not support it

Left lateral connection

The main watch is on the left and connected on the left

Right external connection

The main watch is on the right and connected on the right

Full connection → MySQL does not support full join, Oracle does

When supporting, 15 records will appear (including employees without departments and departments without employees)

/*  Query the name of the employee and his leader */
select ename as employee ，ename as leader from emp e,emp l where e.mgr = l.empno;//How to solve the problem of one less employee? There is no employee information without Department - the solution is as follows
/* Query all departments and employee information of the Department → left external connection */
select ename ,dname ,d.deptno from dept d left outer join emp e on e.deptno =d.deptno;

/* Query the information of all employees and their departments → right external link */
select ename ,dname ,d.deptno from dept d right outer join emp e on e.deptno =d.deptno;//Departments without employees also show

External connection selection method:

③ Natural connection

(2) Subquery - the columns in the query result set are records in a table

The essence of subquery: the nesting of multiple select statements. Select within parentheses first, and then select outside parentheses

① Single line subquery = < > (most commonly used)

Number of records single line

/* Query employee information of the same department as SMITH
select * from emp where//Outside
select deptno from emp where ename = 'SMITH'//within
 */
select * from emp where deptno=(select deptno from emp where ename = 'SMITH');


/* Query the information of employees whose salary is higher than scott's */
select * from emp where sal > (select sal from emp where ename = 'scott');

② Multiline subquery in any > all<

Number of records: multiple lines

/* Employee information greater than the salary of all employees in the Sales Department 
select sal from emp where deptno=30
select * from emp where sal > all(select sal from emp where deptno=30)
*/
select * from emp where sal > all(select sal from emp where deptno=30)//Compare the salary of each employee with that of each employee in the Sales Department [multiple lines]
select * from emp where sal > (select max(sal) from emp where deptno=30)//Compare each employee with the person with the highest salary in the sales department, the more employees, the higher performance [single line]

/* Query the information of employees in other departments whose wages are the same as those in department 20 */
select * from emp where sal in(select sal from emp where deptno=20) and deptno <> 20;

③ Subquery as query table

Subqueries can be placed after where, from, or select.
After select, it is equivalent to table connection. (for() is equivalent to where (true))

III ⭐ DML ⭐

1. insert into values / select

• Single line insertion: insert into tableName [(columeName)] values (value1, value2);
  keyword
  Enter in italics
  Table name, non keyword
  [] optional, column name. If the value after columeName is omitted, it needs to be filled in according to the table. It must be consistent with the number of columns in the table. If it is empty, fill in null. When the number of columns is small, it can be omitted. Generally, it should not be omitted
  
• Multi row insert: insert into tableName [(columeName)] select
  Find out the records in one table and add them to another table

2. Modify update set where

• update tableName set columeName1 = value1 ,columeName2= value2...[where...]
  1. Any value can be modified and separated by ','
  2. The where clause is optional or not, but it should be available in practice

3. delete from where

• delete from tableName [where...]；
  The where clause is optional or not, but it should be available in practice

#IV 🌼 DDL + DCL understanding 🌼 No statement implementation, but

(1) DDL - create, DROP, ALTER

Data definition language, add, delete and modify the objects of the database
Create - create table. Note that different types of columns and databases are different
DROP delete - drop table
ALTER modification - i. modify Table ii Modify column -- addition, deletion and modification of column

operation	sentence
Create table	create table table name (column name column type, column name column type, column name column type,..., column name column type);
Delete table	drop table name
Add column to modify table	alter table table name add (column name column type, column name column type, column name column type,..., column name column type);
Modify the column type of the table	alter table name modify the column type modified by the column name;
Modify the column name of the table	alter table name change original column name new column type;
Modify the deleted column of the table	alter table name drop column name;
Name of modified table	alter table original table name rename to new table name;

(2) DCL things - create, grant, revoke

Data control language is used to grant or reclaim certain privileges (permissions) to access the database, control the time and effect of database manipulation transactions, and monitor the database [TCL].

1. Permission control
   ① First, create a new user using DDL

**create user** "cy1212" @ "localhost";

② DCL grant and revoke

//Grant the view permission in cy1212 to "cy1212", * represents all permissions
grant select on CY1212 .* to "cy1212";
//Reclaim the query permission of cy1212 from cy1212 (under root)
revoke select on CY1212 .* from "cy1212"; 

1. Transaction operation
   Transaction operations, now rarely write process functions in the database, and mostly put the business in the process functions in the program and realize them in the code.

• COMMIT commit
• SAVEPOINT savepoint
• ROLLBACK rollback
• SET TRANSACTION sets the characteristics of the current transaction, which has no impact on subsequent transactions