Summary of SQL statement usage

use quan56_goods; Use database

show tables; Show Data Table

Fuzzy Query

select
	*
from
	tb_brand
where
	name like '%Forest%';

order

Writing Order

SELECT Field List

FROM table name

WHERE Record Filtering Criteria

GROUP BY Grouped Field List

HAVING Grouping Filter Criteria

ORDER BY Sorted Field List

Execution order

FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

Specifically:

FROM: Assemble records from different data sources

WHERE: Filters the records retrieved in the previous step based on the specified criteria

GROUP BY: Grouping filtered records above by specified criteria

SUM/AVG/COUNT: Calculating using aggregation functions

HAVING: Filter all groups according to specified criteria

SELECT: Extracts a list of fields (including aggregation, calculation, expression, and so on) for a specified query from each grouped record filtered in the previous step

ORDER BY: Sorts the result set from the previous query by a list of sorted fields and outputs the sorted results

Duplicate removal

select distant *

sort

SelectXX from XX where XX order by XX DESC (reverse) / ASC (positive order)

Range Value

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
//There are only two strings, the range of which is numeric and the range of which is date
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

system time

Create Table

CONSTRAINT restriction, used to add fields written above when building tables, or to rename primary keys

CREATE TABLE tb_emp1
        (
        id INT(11) not null identity(1,1) primary key, //Non-empty, self-increasing, primary key
        name VARCHAR(25) comment 'Notes',
        deptId INT(11),
        salary FLOAT,
        CONSTRAINT pk_PersonID PRIMARY KEY (id,name) //Rename primary key and set multiple fields as primary keys
        );

Foreign Key Constraints

Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

Modify Table

ALTER TABLE student RENAME TO tb_students_info;
{ ADD COLUMN <Column Name> <type>
 | CHANGE COLUMN <Old column name> <New Column Name> <New Column Type>
 | ALTER COLUMN <Column Name> { SET DEFAULT <Default value> | DROP DEFAULT }
 | MODIFY COLUMN <Column Name> <type>
 | DROP COLUMN <Column Name>
 | RENAME TO <New table name>
 | CHARACTER SET <Character Set Name>
 | COLLATE <Proofreading Rule Name> }

Query Table

    SELECT
    {* | <Field Column Name>}
    [
    FROM <Table 1>, <Table 2>...
    [WHERE <Expression>
    [GROUP BY <group by definition>
    [HAVING <expression> [{<operator> <expression>}...]]
    [ORDER BY <order by definition>]
    [LIMIT[<offset>,] <row count>]
    ]

SELECT DISTINCT < Field Name > FROM < Table Name >; // De-distinct

Fuzzy Query

SELECT
	spu.`name`,spu.`caption`
FROM
	tb_spu spu
WHERE
	spu.name LIKE 'Huawei%' OR spu.name LIKE 'Motorcycles%';

And not like

wildcard describe
% Represents zero or more characters
_ Replace only one character
[charlist] Any single character in a character column
[^ charlist] or [! Charlist] Be not in
Find No A,L,N initial
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

Joint Query union

select * from class
union
select * from person;

insert data

INSERT INTO Table Name VALUES (Value 1, Value 2,....)  //Full field
INSERT INTO table_name (Column 1, Column 2,...) VALUES (Value 1, Value 2,....)  //Specify Fields
INSERT INTO <Table Name> [ <Column Name 1> [ , ... <Column Name n>] ]
VALUES (Value 1) [... , (value n) ];
INSERT INTO <Table Name>
SET <Column Name 1> = <Value 1>,
        <Column Name 2> = <Value 2>;
NSERT INTO tb_courses
    -> (course_name,course_info,course_id,course_grade)
    -> VALUES('Database','MySQL',2,3);

Modify data

UPDATE Table Name SET Column Name = New Value WHERE Column Name = A value

UPDATE <Table Name> SET Field 1=Value 1 [,Field 2=Value 2... ] [WHERE clause ]
[ORDER BY clause] [LIMIT clause]

mybatis Pass Object
@Update("UPDATE tb_request_ip t SET count = #{ip.count}, last_time = #{ip.lastTime}, info = #{ip.info} WHERE id = #{ip.id};")
    int updateById(@Param("ip") RequestIp ip);

Delete data

DELETE FROM <Table Name> [WHERE clause] [ORDER BY clause] [LIMIT clause]

DELETE FROM Table Name WHERE Column Name = value

Self-join query

SELECT 
	t1.name, t2.name, t3.name
FROM
	tb_category t1,tb_category t2, tb_category t3
WHERE
	t1.parent_id = t2.id AND t2.parent_id = t3.id;

Result

Indexes

Indexes are automatically created when a primary key or unique constraint is established.

  • Small data tables should not use indexes;
  • Tables that require frequent bulk updates or insertions;
  • If the column contains large numbers or NULL values, it is not appropriate to create an index.
  • Frequently operated columns should not be indexed.

Create a single-column index with no columns or multiple columns separated by','

CREATE INDEX index_name ON table_name(column_name);

Create Unique Index

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Cancel Index

ALTER TABLE table_name DROP INDEX index_name

view

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

sql function

time

NOW() CURDATE() CURTIME()
2008-11-11 12:45:34 2008-11-11 12:45:34

Can be used to select, default values for table building statements

CREATE TABLE Orders  
(  
OrderId int NOT NULL,  
ProductName varchar(50) NOT NULL,  
OrderDate datetime NOT NULL DEFAULT NOW(),  
PRIMARY KEY (OrderId)  
)
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate  
FROM Products;

The EXTRACT() function returns a separate part of the date/time, such as year, month, day, hour, minute, and so on.

Unit value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,  
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,  
EXTRACT(DAY FROM OrderDate) AS OrderDay  
FROM Orders  
WHERE OrderId=1

DATE_ The ADD() function adds a specified time interval to a date

Set end time

DATE_ADD(date,INTERVAL expr type)

The date parameter is a valid date expression. The expr parameter is the interval you want to add.

The type parameter can have the following values:

Type value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate  
FROM Orders

DATE_ The SUB() function subtracts the specified time interval from the date

DATE_SUB(date,INTERVAL expr type)

The date parameter is a valid date expression. The expr parameter is the interval you want to add.

The type parameter can have the following values:

Type value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

The DATEDIFF() function returns the number of days between two dates

SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate

The GETDATE() function returns the current date and time

CREATE TABLE Orders  
(  
OrderId int NOT NULL PRIMARY KEY,  
ProductName varchar(50) NOT NULL,  
OrderDate datetime NOT NULL DEFAULT GETDATE()  
)

2008-11-11 12:45:34.243

The DATEPART() function returns a separate part of the date/time, such as year, month, day, hour, minute, and so on.

DATEPART(datepart,date)

The date parameter is a valid date expression. The datepart parameter can be the following:

datepart Abbreviation
year yy, yyyy
quarter qq, q
month mm, m
Day of year dy, y
day dd, d
week wk, ww
week dw, w
hour hh
Minute mi, n
second ss, s
Millisecond ms
subtle mcs
nanosecond ns

Conditional Screening

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Similarly, MIN minimum, total number of COUNT s, AVG average, SUM total, FIRST first, LAST last, LOWER(s) conversion value lowercase, UPPER(s) conversion value uppercase, LCASE (column name) conversion column value lowercase, UCASE (column name) conversion column value uppercase

lookup

Target the first parameter, find the first parameter in the subsequent parameters, find the return index (from 1), and return 0 if none

SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');

This sql returns 2.

Grouping

The second order by is to sort the groupings according to the total number after count ing the total number of groupings

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

Supplementary condition, having clause

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

The difference between having and where

Where is filtered based on the data table, where is filtered based on the results, and aggregate functions such as count are used as criteria, only have is used, and fields other than those selected by select are used as criteria, only where is used.

where:
Where is a constraint statement that uses where to constrain data from a database;
where works before the result is returned;
Aggregate functions cannot be used in where.
having:
having is a filter declaration;
Filtering of query results after the query returns a result set;
Aggregate functions can be used in having.

Aggregation functions are functions such as SUM, COUNT, MAX, AVG that operate on a set of (multiple) data and need to be used with group by.

The where clause filters the records before aggregation, that is, before the group by clause and the having clause, while the having clause filters the group records after aggregation.

scene

  1. Scenes that can be used with where and having

select goods_price,goods_name from sw_goods where goods_price > 100
select goods_price,goods_name from sw_goods having goods_price > 100

Interpretation: The precondition for having above is that I have filtered goods_price field, which is equivalent to where in this case, but if I don't select goods_price will make a mistake!! Because having is filtered from previously filtered fields, whereas is filtered directly from fields in the data table.

  1. where only, not having

select goods_name,goods_number from sw_goods where goods_price > 100
Select goods_ Name, goods_ Number from sw_ Goods having goods_price > 100 // Error!!! Because goods_was not previously filtered out Price field

  1. Only have, not where

Query for each goods_ Category_ Average price of ID commodity, get commodity information with average price greater than 1000 yuan

select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000
Select goods_ Category_ Id, AVG (goods_price) as Ag from sw_goods where ag>1000 group by goods_ Category //Error!! Because from sw_ There is no Ag field in this table of goods

Note: where is followed by fields in the data table, and it would be wrong if I changed ag to avg(goods_price)! Because there is no field in the table. And having just follows what it was queried before.

SELECT *, COUNT(course) count
FROM student_course
WHERE course = 'Mathematics'
GROUP BY course
HAVING COUNT(course) > 1
ORDER BY COUNT(course) DESC;

Specified part of mid extract value

SELECT MID(column_name,start[,length]) FROM table_name;
parameter describe
column_name Required. The field in which to extract the characters.
start Required. Specify the starting position (starting value is 1).
length Optional. Number of characters to return. If omitted, the MID() function returns the remaining text.
SELECT MID(City,1,4) AS ShortCity  
FROM Customers;

The LEN() function returns the length of a value in a text field

SELECT LEN(column_name) FROM table_name;

The ROUND() function rounds a numeric field to a specified number of decimal places

SELECT ROUND(column_name,decimals) FROM table_name;
SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;
parameter describe
column_name Required. The field to round.
decimals Required. Specifies the number of decimal places to return.

The SQRT function is used to calculate the square root of any number

SELECT name, SQRT(daily_typing_pages) FROM employee_tbl;

RAND function, used to generate random numbers between 0 and 1

You can use ORDER BY RAND() to randomize a set of records

SELECT * FROM employee_tbl ORDER BY RAND();

The CONCAT function is used to concatenate two strings into one string

Split two fields or add a suffix or prefix

SELECT CONCAT(id, name, work_date) FROM employee_tbl;

ifnull sets a default value for field null

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))  
FROM Products

REPLACE() String Replacement Function

update `article` set title=replace(title,'CodingDict','hello');

TRIM() function removes leading and trailing spaces from strings

  • MySQL: TRIM( ), RTRIM( ), LTRIM( )
  • Oracle: RTRIM( ), LTRIM( )
  • SQL Server: RTRIM( ), LTRIM( )

trigger

Set up to 6 tables per table

Create trigger format:

CREATE  TRIGGER  Trigger name Trigger time Trigger event
ON  Table Name  FOR  EACH  ROW
BEGIN
	Trigger
END

1) Trigger name is unique

2) Trigger time is divided into two types: BEFORE, AFTER

3) Trigger events are divided into three types: INSERT, UPDATE, DELETE

4) FOR EACH ROW: Represents row-level triggers (one trigger is executed for each record)

5) Trigger: A program that the system automatically runs when an event is triggered

//A database can set up to six types of triggers because of trigger time and trigger events

//Currently MySQL only supports row-level triggers, not statement-level triggers

case

# Delete trigger 
DELIMITER //
CREATE TRIGGER trig_book2 AFTER DELETE
  ON t_book FOR EACH ROW
  BEGIN
    UPDATE t_book_type SET book_num=book_num-1 WHERE old.book_type_id=t_book_type.id;
    INSERT INTO t_log VALUES(NULL,NOW(),'stay book A piece of data has been deleted from the table');
    DELETE FROM t_test WHERE old.book_type_id=t_test.id;
  END //
 DELIMITER;
 # old above refers to the transition variable that was deleted Insert | Update to new

cancel

DROP TRIGGER table name. Trigger name;

Tags: Java

Posted by Lijoyx on Mon, 18 Jul 2022 08:34:51 +0930