SQL Grammar Practice (2): Basic Grammar

Practice code

Practice based on a very detailed introduction to SQL syntax written by a great guy. It is highly recommended. The link is as follows:
Jump to the original text

--This table is supplemented based on the following content. It is no problem for practice use.
CREATE TABLE friend(
    fid INT NOT NULL,
    NAME VARCHAR(10) NOT NULL,
    age INT NOT NULL,
    adress VARCHAR(10)
);
INSERT INTO friend VALUES(1,'Jack',19,'Beijing'),
                         (2,'Lihua',20,'Wuhan'),
                         (3,'Xiao',21,'Wuhan'),
                         (4,'Wangdong',18,'Shanghai'),
                         (5,'MiaoMiao',18,'Wuhan'),
                         (6,'Yenuoyi',20,'Beijing'),
                         (7,'Zhangsan',21,'Wuhan'),
                         (8,'Lisi',22,'Wuhan');
SELECT * FROM friend;


--VIEW
SELECT * FROM friend;
CREATE VIEW f_view AS SELECT fid FROM friend WHERE fid =1;
CREATE OR REPLACE VIEW f_view AS SELECT * FROM friend WHERE fid<3;
SELECT * FROM f_view;
DROP VIEW f_view;

--FUNCTION
SELECT * FROM friend;
SELECT AVG(age) FROM friend;
SELECT * FROM friend WHERE age<(SELECT AVG(age)FROM friend);

--COUNT
SELECT * FROM friend;
SELECT COUNT(DISTINCT age) FROM friend;
SELECT COUNT(*) FROM friend;
SELECT COUNT(fid) FROM friend;

--MAX AND MIN note:null_is_include
SELECT * FROM friend;
SELECT MAX(fid)FROM friend;
SELECT MAX(age) FROM friend;
SELECT MAX(NAME) FROM friend;

--SUM
SELECT * FROM friend;
SELECT SUM(age) FROM friend;
SELECT SUM(fid) FROM friend;

--GROUP BY
SELECT * FROM friend;
SELECT fid, COUNT(age)
FROM friend
WHERE age<22
GROUP BY fid;

SELECT fid,COUNT(age)
FROM friend
WHERE age=18
GROUP BY fid;

--HAVING
SELECT * FROM friend;
SELECT fid,COUNT(age)
FROM friend
WHERE age = 18
GROUP BY fid
HAVING COUNT(age);

--UCASE/UPPER AND LCASE/LOWER
SELECT * FROM friend;
SELECT UPPER(NAME) FROM friend;
SELECT UPPER(adress),fid FROM friend;
SELECT UCASE(adress) FROM friend;
SELECT fid,UPPER(NAME),age, LOWER(adress) FROM friend;

--len/LENGTH
SELECT * FROM friend;
SELECT len(NAME) FROM friend;/FALSE
SELECT LENGTH(NAME) FROM friend;
SELECT LENGTH(NAME),fid FROM friend;
SELECT NAME,LENGTH(NAME) FROM friend;
SELECT LENGTH(NAME),NAME FROM friend;

--ROUND
SELECT * FROM friend;
SELECT ROUND(age,2) FROM friend;
SELECT ROUND(NAME,2) FROM friend;
ALTER TABLE friend ADD COLUMN number VARCHAR(50) DEFAULT NULL;
UPDATE `rock_sql`.`friend` SET `number` = '3.1415' WHERE `fid` = '1' L;
UPDATE friend SET `number` = 30.1415 WHERE fid = 2;
UPDATE friend SET `number` = 300.1415 WHERE fid = 3;
UPDATE friend SET `number` = 3000.1415 WHERE fid = 4;
UPDATE friend SET `number` = 3000.1415 WHERE fid = 5;
UPDATE friend SET `number` = 345.1415 WHERE fid = 6;
UPDATE friend SET `number` = 344.1415 WHERE fid = 7;
UPDATE friend SET `number` = 333.1415 WHERE fid = 8;
SELECT ROUND(number,2) FROM friend;
SELECT ROUND(number,3) FROM friend;
SELECT ROUND(number,11) FROM friend;

CREATE TABLE friend(
    fid INT NOT NULL,
    NAME VARCHAR(10) NOT NULL,
    age INT NOT NULL,
    adress VARCHAR(10)
);
DROP TABLE friend

INSERT INTO friend VALUES(1,'Jack',19,'Beijing'),
                         (2,'Lihua',20,'Wuhan'),
                         (3,'Xiao',21,'Wuhan'),
                         (4,'Wangdong',18,'Shanghai'),
                         (5,'MiaoMiao',18,'Wuhan'),
                         (6,'Yenuoyi',20,'Beijing'),
                         (7,'Zhangsan',21,'Wuhan'),
                         (8,'Lisi',22,'Wuhan');
        
SELECT * FROM friend;

INSERT INTO friend (fid,NAME,age,adress)VALUES(9,'Rock',23,'Wuhan')
SELECT * FROM friend

--Add sex attributes to fid and name--Add column column
ALTER TABLE friend
ADD COLUMN sex VARCHAR(50) AFTER fid;
SELECT * FROM friend;
-- Delete column--drop column
ALTER TABLE friend
DROP COLUMN sex;
SELECT * FROM friend;

SELECT DISTINCT age FROM friend;

SELECT fid,sex,adress FROM friend WHERE adress = 'Wuhan';
UPDATE friend SET sex = 'boy' WHERE fid = '1';
SELECT * FROM friend
UPDATE friend SET sex = 'boy' WHERE fid = '1';
UPDATE friend SET sex = 'gril' WHERE fid = '2';
UPDATE friend SET sex = 'boy' WHERE fid = '3';
UPDATE friend SET sex = 'boy' WHERE fid = '4';
UPDATE friend SET sex = 'gril' WHERE fid = '5';
UPDATE friend SET sex = 'gril' WHERE fid = '6';
SELECT * FROM friend
UPDATE friend SET sex = 'boy' WHERE fid = '6';
UPDATE friend SET sex = 'gril' WHERE fid = '7';
UPDATE friend SET sex = 'gril' WHERE fid = '8';
UPDATE friend SET sex = 'boy' WHERE fid = '9';

SELECT fid,NAME,adress
FROM friend
WHERE age IN(20,21)
GROUP BY fid;

SSELECT fid,NAME,adress
FROM friend
WHERE age IN(20,21)
GROUP BY fid;ELECT fid,COUNT(adress) AS number
FROM friend
WHERE adress ='Wuhan';

SELECT fid
FROM friend
GROUP BY fid
WHERE adress ='Wuhan';

--The difference between where and having is:
--where filtering has a wide range of applications and can be used in select, update, delete
--having filter conditions in group by group, only used with select

Grammar summary

CREATE TABLE Persons
(
Id_P INT,
LastName VARCHAR(255),
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255)
);
-- INSERT – Insert data
INSERT INTO table name VALUES (value 1, value 2,....);
INSERT INTO table_name (column 1, column 2,...) VALUES (value 1, value 2,....);
-- SELECT – Query data. The asterisk (*) is a shortcut for selecting all columns.
SELECT * FROM table name;
SELECT column name FROM table name;
-- DISTINCT – Remove duplicate values
SELECT DISTINCT column name FROM table name;
-- WHERE – conditional filtering
SELECT column name FROM table name WHERE column operator value;
-- AND & amp; OR - Operator
SELECT * FROM table name WHERE column operator value AND column operator value;
/*ORDER BY – sort
NULL values are sorted after rows with values by default.
The default sorting is ASC ascending, DESC stands for descending.
*/
SELECT * FROM table name ORDER BY column 1, column 2 DESC;
-- UPDATE – update data
UPDATE table name SET column name = new value WHERE column name = some value;
-- DELETE – delete data
-- All rows can be deleted without deleting the table.
-- This means that the table's structure, attributes, and indexes are complete:
DELETE FROM table name WHERE column name = value;
-- TRUNCATE TABLE – Clear table data
TRUNCATE TABLE table name;
-- LIKE – find similar values
/*
value
'N%' starts with "N"
'%N' "N" ending
'%N%' "N" middle
*/
SELECT column name/(*) FROM table name WHERE column name LIKE value;
-- IN – lock multiple values
SELECT column name/(*) FROM table name WHERE column name IN (value 1, value 2, value 3);
-- BETWEEN – select interval data
/*
The operator BETWEEN … AND selects a range of data between two values.
These values can be numeric, text, or dates.
*/
SELECT column name/(*) FROM table name WHERE column name BETWEEN value 1 AND value 2;

-- AS – alias
SELECT column name/(*) FROM table name AS alias;
SELECT column name AS alias FROM table name;
-- JOIN – multi-table association
/*
JOIN is used to query data from two or more tables based on the relationship between columns in those tables.
JOIN: Return rows if there is at least one match in the table
INNER JOIN: Internal join, returns matching rows in two tables
LEFT JOIN: Returns all rows from the left table even if there is no match in the right table
RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table
FULL JOIN: Return rows as long as there is a match in one of the tables
*/
SELECT column name
FROM tableA
INNER|LEFT|RIGHT|FULL JOIN Table B
ON table A primary key column = table B foreign key column;

-- UNION – merge result sets
-- The UNION operator is used to combine the result sets of two or more SELECT statements.

SELECT column name FROM tableA
UNION
SELECT column name FROM table B;

SELECT column name FROM tableA
UNION
SELECT column name FROM table B;
-- NOT NULL – not empty
CREATE TABLE table
(
Column INT NOT NULL
);
-- VIEW – view
CREATE VIEW view name AS
SELECT column name
FROM table name
WHERE query conditions;
/*If you need to update columns or other information in the view,
No need to delete, use CREATE OR REPLACE VIEW option: */
CREATE OR REPLACE VIEW view name AS
SELECT column name
FROM table name
WHERE query conditions;
-- Commonly used function learning NULL values are not included in calculations
SELECT FUNCTION(column) FROM table;
AVG (listed)
COUNT(column name)
COUNT(*)
COUNT(DISTINCT column name)
MAX(listed)
MIN(column name)
SUM(column name)

-- GROUP BY – group
SELECT column name A, statistical function (column name B)
FROM table name
WHERE query conditions
GROUP BY column name A;

-- HAVING – link at the end of sentence
SELECT column name A, statistical function (column name B)
FROM table_name
WHERE query conditions
GROUP BY column name A
HAVING statistical function (column name B) query conditions;

-- UCASE/UPPER – uppercase
-- LCASE/LOWER – lowercase
SELECT UPPER(column name) FROM table name;

-- LEN/LENGTH – Get the length
SELECT LENGTH(column name) FROM table name;

-- ROUND – Numerical round-off
SELECT ROUND(column name,precision) FROM table name;