This is the 10th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

The basic concept

  1. SQL: Structured Query Language

    Structured query language (STL) is a standard computer language for accessing and processing databases

  2. SQL is divided into DML, DCL, DQL, DDL, etc

    • DQL, Data Query Language, is a Query statement
    • Data Manipulation Language (DML) is a database Manipulation Language that adds, deletes, and updates Data
    • DDL, Data Definition Language, database Definition Language, that is, build database, build table, etc
    • DCL, Data Control Language, database Control Language, such as role and permission Control

  3. Oracle Database is one of the most popular databases available today

  4. Data File (DBF)

    Data is stored in the database, ultimately on the physical disk, data file is the physical storage unit of the database

  5. Tablespace (tablespace)

    A table space is a mapping of a database to related data files on physical storage

    A database is divided into one to several tablespaces, with at least one tablespace

    Each tablespace consists of one or more data files on disk

    A data file can only belong to one tablespace

  6. Users and Schemas

    Users are used to connect to the database and access the database objects, and hold the permissions and resources of the system

    A schema is a collection of data objects, including tables, functions, packages, and so on

    In Oracle, a schema is a collection of all the objects under a user

    Generally, a user corresponds to a schema. The schema name of the user equals to the user name and is used as the default schema of the user

To build libraries built table

  1. Create a user

    create userThe user name identifiedbyPassword;Copy the code

  2. Example View tablespace information

    SELECT
	*
FROM
	Dba_Tablespaces;
Copy the code

  3. Example View tablespace configuration file information

    SELECT
	*
FROM
	Dba_data_files;
Copy the code

  4. Create table space

    CREATE TABLESPACE Name of the tablespace DATAFILE Location where the configuration file is stored SIZE SIZE of the configuration fileCopy the code

  5. Specify the default tablespace

    ALTER DATABASE DEFAULTTABLESPACE User name;Copy the code

  6. View the default tablespace

    SELECT
	default_tablespace
FROM
	user_users;
Copy the code

  7. Authorizing the user

    GRANT DBA TOThe user nameCopy the code

  8. Create data table

    CREATE TABLE USER(
	ID NUMBER,
    NAME VARCHAR2(32),
    AGE NUMBER,
    BIRTH DATE
)
Copy the code

    Several string types:

    • CHAR(n)

      The length is fixed, and Spaces are automatically filled when insufficient. Chinese characters occupy 2 bytes, letters 1 byte, and n is the number of bytes

    • VARCHAR(n)

      Variable length, 2 bytes for Chinese characters, 1 byte for letters, n is the number of bytes

    • VARCHAR2(n)

      Non-industrial standard, guaranteed version compatibility, occupation is related to the character set, null string processing, n is the number of bytes, a maximum of 4000

    • NVARCHAR(n)

      Variable length, Unicode encoding, n is the number of characters

    • NVARCHAR2

      Chinese characters and letters occupy two bytes

  9. Set the auto-increment ID

    The increment sequence used to get the ID

    CREATE SEQUENCE SQ_USER_ID
MINVALUE 1 MAXVALUE 99999999
INCREMENT BY 1 START WITH 1
Copy the code

    Triggers that automatically populate the ID

    CREATE OR REPLACE TRIGGER USER
    BEFORE INSERT
    ON USER
    FOR EACH ROW
BEGIN
    SELECT SQ_USER_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
Copy the code

Add and delete

  1. insert

    INSERT INTO table_name VALUES(value_1, value_2, value_3);
INSERT INTO table_name(column_1, column_3) VALUES(value_1, value_3);
Copy the code

  2. delete

    DELETE FROM 
	table_name 
WHERE 
	column_1='xxx';
Copy the code

  3. update

    UPDATE 
	table_name 
SET 
	column_1="xxx", 
	column_2="xxx" 
WHERE column_3="xxx"
Copy the code

  4. select

    SELECT
  column_1, 
  column_2
FROM
  table_name;
Copy the code

Advanced query

  1. where

    Represents a query condition and can be used together with and/or

    SELECT 
	*
FROM
	table_name
WHERE 
	(column_1='xxx'
	AND column_2='xxx')
	OR column_3='xxx';
Copy the code

  2. distinct

    To obtain unique records, multiple columns can be restricted

    SELECT 
	DISTINCT column_1
FROM
	table_name
Copy the code

  3. order by

    Sort the query results in a certain order: ASC ascending (default), DESC descending

    If it is a string, sort it alphabetically

    Order by is usually used at the end of SQL statements

    SELECT 
	column_1, column_2
FROM
	table_name
ORDER BY
    column_1 DESC
Copy the code

  4. group by

    Group a collection of records

    The fields after group by indicate the grouping of these fields. A record is formed according to these fields, and the same group is repeated. Finally, the groups are returned

    SELECT
	name, age, sex
WHERE
	sex='male'
GROUP BY
	name, age, sex
Copy the code

    After grouping, the actual operation of the SELECT statement is for each group data, and each loop is also for each group, not for a single record

  5. having

    Having is used to filter groups after group by. It is similar to WHERE, except that it can only filter the fields in the query results after group by

    For example, look at the person table for the number of times each of the boys’ names is the same name, and show groups with more than two

    SELECT
	name, COUNT(*) num
FROM
	person
WHERE
	sex='male'
GROUP BY
	name
having 
	num > 2
Copy the code

  6. Nested query

    A subquery is a query statement nested within a query statement and is a complete query statement

    The result set of a subquery can also be used as a WHERE clause query condition in addition to the data table of the parent query from

    The result set is a single row and is usually judged using =

    The result set is one row and one column, that is, one value

    SELECT
	*
FROM
	table_1 t1
WHERE
	t1.t1_colum_1 = (
    	SELECT
			t2_colum_1
		FROM
			table_2
    )
Copy the code

    The result set is a single row with multiple columns, that is, one row of records

    SELECT
	*
FROM
	table_1 t1
WHERE
	(t1.t1_colum_1, t1.t1_colum_2) = (
    	SELECT
			t2_colum_1, t2_colum_2
		FROM
			table_2
    )
Copy the code

    The result set has multiple rows, and three main operators are used: IN, ANY, and ALL

    The result set is multiple rows and a single column, that is, multiple values

    SELECT
	*
FROM
	table_1 t1
WHERE
	t1.t1_colum_1 IN (
    	SELECT
			t2_colum_1
		FROM
			table_2
    )
Copy the code

    The result set is multi-row and multi-column, that is, multi-row records

    SELECT
	*
FROM
	table_1 t1
WHERE
	(t1.t1_colum_1, t1.t1_colum_2) IN (
    	SELECT
			t2_colum_1, t2_colum_1
		FROM
			table_2
    )
Copy the code

    IN means matching across multiple values

    = ANY is the same thing as IN

    < ANY indicates that the value is smaller than the maximum value in the subquery result set

    > ANY indicates that the value is larger than the smallest result set in the subquery

    <> ALL is equivalent to NOT IN

    < ALL indicates smaller than the smallest in the subquery result set

    > ALL indicates that the value is larger than the maximum value in the result set of the subquery

    Note:

    <> ANY returns true if it is different from ANY record in the result set

    <> ALL: returns true if it is not equal to each record

    In addition, the EXSITS construct is used to determine whether any data is returned from a subquery, and returns true if it does

    SELECT
	*
FROM 
	table_1
WHERE
	EXSITS (
        SELECT
            column_1, 
            column_2
        FROM
        	table_name
	)
Copy the code

  7. The joint query

    UNION: Performs the operation of UNION of two result sets and deletes duplicate records

    UNION ALL: The operation of UNION without removing duplicate records is faster than UNION

    SELECT column_1 FROM table_name1
UNION ALL
SELECT column_1 FROM table_name2
Copy the code

    UNION requires that the two result sets have the same number of columns and does not require the same column names

    The column name in the UNION result set is always equal to the column name in the first SELECT statement in the UNION

    INTERSECT is used for intersection operations and MINUS is used for difference set operations

  8. Join queries

    LEFT JOIN: LEFT JOIN, return all rows of the LEFT table, and select data from the right table for matching. If the data fails to match, the LEFT JOIN is null

    RIGHT JOIN: returns all rows of the RIGHT table and takes data from the left table for matching. If the data fails to match, it is null

    INNER JOIN: INNER JOIN that returns matched rows from both tables

    SELECT 
	t1.cloumn_1,
	t2.cloumn_1,
	t2.cloumn_2
FROM 
	table_1 t1
LEFT JOIN 
	table_2 t2
ON 
	table_name1.cloumn_1 = table_name2.cloumn_1
Copy the code

    Self connection:

    SELECT 
	t1.id,
	t2.id
FROM 
	table1 t1, table_1 t2
WHERE 
	t1.id = t2.parentId
Copy the code

Tool function

  1. Aggregation function

    AVG: Take the average

    The SUM, SUM

    COUNT: Indicates the number of statistics

    MIN, MAX: find the maximum and minimum records

    An aggregate function operates on a set of data and returns a row of results

    When you need to return other fields, you need to group them by this field

    SELECT
	SUM(cloumn_1)
FROM
	table_1;
	
SELECT
	cloumn_1, SUM(cloumn_2)
FROM
	table_1
GROUP BY
	cloumn_1;
Copy the code

  2. string

    LENGTH(STR) : Returns the LENGTH of the string

    SUBSTR(STR, start[, length]) : Intercepts length characters from start. By default, length is at the end

    SELECT
	*
FROM
	table_1
WHERE
	SUBSTR(id, 2) = 'xxx' 
Copy the code

    The second argument to SUBSTR starts with 1 and represents the first character. If it’s minus 1, it’s the reciprocal, and so on

    INSTR: Queries the position of a string within another string

    SELECT 
	INSTR('hello'.'l'.1.2)
FROM 
	DUAL;
Copy the code

    INSTR(orign, target[, start, NTH]) : query the location of the target in origin, start from start and find the NTH target

    If start is negative, the search is performed backwards, but the return value is the same as before. If 0 is returned, the search is not performed

  3. formatting

    TO_DATE(STR [, FMT]) : Converts a string to the date type in FMT format

    TO_CHAR(d[, FMT]) : converts a date or number to a string in FMT format

    SELECT 
	TO_CHAR(TO_DATE('20210314'.'yyyymmdd'))
FROM 
	DUAL;
Copy the code

    TO_CHAR’s FMT argument:

    • Iw: The week of acquisition
    • Yyyy: indicates the year in which the file is obtained
    • Mm: acquisition month
    • Dd: indicates the acquisition date
    • Day: The day of the week
    • Hh24: Obtain the 24-hour system hour
    • Mi: Get minutes
    • Ss: Get second

  4. The date of

    SYSDATE: Obtains the current time

    ADD_MONTHS(d,n) : d specifies the date. N indicates the number of months to be added. The value can be negative

    LAST_DAY(d) : displays the last day of the month at the specified time

    NEXT_DAY(d, ‘Monday ‘) : Gets the Monday of the next week

    TRUNC(d[, FMT]) : Interception time, usually used to obtain the first day of the time range

    TRUNC(SYSDATE, ‘yy’) The first day of the current year

    TRUNC(SYSDATE, ‘q’) The first day of this quarter

    TRUNC(SYSDATE, ‘mm’) The first day of this month

    TRUNC(SYSDATE, ‘d’) The first day of the week, starting on Sunday

    For example:

    • Get last 7 days (including current day)

      SELECT 
	TO_CHAR(SYSDATE - 6.'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;
Copy the code

    • Get last 4 weeks (including current week)

      SELECT 
	TO_CHAR(TRUNC(SYSDATE - 21.'d') + 1.'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;
Copy the code

    • Get the last 3 months (including the current month)

      SELECT 
	TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 2 -), 'mm'), 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;
Copy the code

    • Get the first day of the current quarter

      SELECT 
	TO_CHAR(TRUNC(SYSDATE, 'q'), 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;
Copy the code

    • Get the first day of last quarter

      SELECT 
	TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'q'), -3), 'yyyy-mm-dd hh24:mi:ss') 
FROM 
	dual;
Copy the code

  5. digital

    ABS: the absolute value

    CEIL: Round up

    FLOOR: Round down

    ROUND(x, y) : x is rounded to the y-th place

  6. judge

    NVL(x, v) : if x is empty, return v, otherwise return x

    NVL2(x, v1, v2) : if x is empty, v2 is returned, otherwise V1 is returned

    DECODE(x, v1, r1, v2, r2, … Vn, Rn, default) : similar to if-else, where x equals v1 returns R1, and so on until the default value

    For example, to query students, you need to display more than 20 when the age is above 20, display less than 20 when the age is below 20, and display exactly 20 when the age is 20:

    select t.id,
       t.name,
       t.age,
       DECODE(SIGN(t.age - 20),
              1.'more than 20'.- 1.'under 20'.0.'just 20'.'unknown') sex
  from student t
Copy the code

    CASE WHEN: Similar to switch-case, more complex and flexible than DECODE, this function is also available in other databases

    The example above uses case when to implement:

    select t.id,
       t.name,
       t.age,
       (CASE 
       		WHEN t.age = 20 THEN 'just 20'
       		WHEN t.age < 20 THEN 'under 20'
       	ELSE 'more than 20'
       END) sex
  from student t
Copy the code