MySQL basic syntax

Published by jamie on

Database and Table
· Create Database 
CREATE DATABASE <database name>;

CREATE DATABASE shop;
· Create Table 
CREATE TABLE <table name>
(<column name> <data type> <constrains>,
<column name> <data type> <constrains>,
...,
<constrains of the table>,<constrains of the table>,...);

CREATE TABLE product
(id CHAR(4) NOT NULL,
name VARCHAR(100) NOT NULL,
price INTEGER,
PRIMARY KEY (ID));
· Common Data Types
CHAR(n)
VARCHAR(n)
BINARY(n)
VARBINARY(n)
BOOLEAN
INTEGER
SMALLINT
BIGINT
DECIMAL(p,s)
NUMERIC(p,s)
FLOAT
DATE
TIME
TIMESTAMP
ARRAY
MULTISET
XML
character or String with fixed length ‘n’
variable length String, with maximum length ‘n’
binary String with fixed length ‘n’
binary String with maximum length ‘n’
TRUE or FALSE
integer(4 bytes)
integer(2 bytes)
integer(8 bytes)
decimal with length ‘p’ and number of decimal place ‘s’
same with DECIMAL
float
year-month-day
hour-minute-second
year-month-day-hour-minute-second
ordered array
disordered set
XML data

· Common Constraints

1.NOT NULL: Promise the field not null.
2.DEFAULT：Set default valule
3.PRIMARY KEY：Primary Key, guarantee that field unique and not null.
4.UNIQUE：Guarantee that field unique, could be null.
5.CHECK：(MySQL not support)，Checks whether the value of the field is in the specified values.
6.FOREIGN KEY：Foreign key, used to restrict the relationship between two tables, used to ensure that the value of the field must come from the value of the associated column in the primary table, added foreign key constraint in the secondary table, used to reference some values in the primary table. 
CREATE TABLE t_stuinfo(
    id INTEGER,    
    stuName VARCHAR(20) NOT NULL,    
    gender CHAR(1),    
    seat INTEGER UNIQUE,    
    age INTEGER DEFAULT 18,    
    majorId INTEGER ,
    PRIMARY KEY (id),
    FOREIGN KEY (majorId) REFERENCES major(id),
    CHECK(gender='M' or gender='F')
    );
· Delete Table 
DROP TABLE <table name>;

DROP TABLE product;
· Alter Table Attributes 
ALTER TABLE <table_name> ADD <column_name> <datatype>;
ALTER TABLE <table_name> DROP COLUMN <column_name>;
ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype> (<constrains>);
ALTER TABLE <table_name> ADD <constrains>;

ALTER TABLE Persons ADD DateOfBirth DATE;
ALTER TABLE Persons DROP COLUMN DateOfBirth;
ALTER TABLE Persons MODIFY COLUMN name VARCHAR(50) NOT NULL;
ALTER TABLE Persons ADD PRIMARY KEY(id);
Update Data
· Insert Data 
INSERT INTO <table_name> VALUES (<value1>,<value2>,...);
INSERT INTO <table_name> (<column1>,<column2>,...) VALUES (<value1>,<value2>,...);

INSERT INTO <table_name> (<column1>,<column2>,...) 
SELECT <column1>,<column2>,... FROM <table_name_2>;
· Delete Data 
DELETE FROM <table_name> (WHERE <conditions>);

DELETE FROM Websites;
DELETE FROM Websites WHERE name='Facebook' AND country='USA';

DROP vs DELETE vs TRUNCATE

· Update Data 
UPDATE <table_name> SET <column1>=value1,<column2>=value2,... 
WHERE <some_column>=some_value;
Transaction
BEGIN TRANSACTION;
...
COMMIT;/ROLLBACK;

ACID Properties

Query
· Simple Query 
-- query all data from the table
SELECT * FROM <table_name>; 
-- query specific columns
SELECT <column_name>,<column_name> FROM <table_name>; 
-- only show distince values
SELECT DISTINCT <column_name>,<column_name> FROM <table_name>;
-- specify alias
SELECT <column_name> AS name1, ... FROM <table_name>; 
-- filter by condition
SELECT * FROM <table_name> 
WHERE <condition1> AND <condition2> OR <condition3>;
-- some operators
+ - * /
= <> >= > <= < 
( ) NOT AND OR

cannot use '= NULL'
should use IS NULL or IS NOT NULL
· Aggregate Query 
-- Sort result
-- When ORDER BY multiple columns, sort by the first column name first, then by the second column name.
-- When ASC DESC is not specified, the default is ASC.
-- ASC DESC can be specified for each column name individually.
SELECT <column_name>,<column_name>
FROM <table_name>
ORDER BY <column_name>,<column_name> [ASC|DESC];

-- Count the number of records in the table
SELECT COUNT(*) FROM <table_name>;

-- Count the number of values of the specified column (not include NULL)
SELECT COUNT(<column_name>) FROM <table_name>;
SELECT COUNT(DISTINCT <column_name>) FROM <table_name>;

-- Calculate the total value
SELECT SUM(<column_name>) FROM <table_name>;

-- Calculate the average value
SELECT AVG(<column_name>) FROM <table_name>;

-- Calculate the maximum or minimum value
SELECT MAX(<column_name>) FROM <table_name>;
SELECT MIN(<column_name>) FROM <table_name>;

-- Grouping
-- When using the GROUP BY clause, column names other than 
-- aggregate keys cannot appear in the SELECT clause.
SELECT <column_name1>, <column_name2> <aggregate_function(column_name)>
FROM table_name (WHERE <condition>)
GROUP BY <column_name1>, <column_name2>;

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

-- The result of GROUP BY is unordered.
SELECT site_id, SUM(count) AS nums
FROM access_log GROUP BY site_id ORDER BY SUM(count);

-- HAVING: Specify conditions for the aggregated results.
-- aggregate functions cannot follow WHERE as its condition, 
-- but can be placed after HAVING.
SELECT <column_name>, <aggregate_function(column_name)>
FROM <table_name>
WHERE <column_name> <operator> <value>
GROUP BY <column_name>
HAVING <aggregate_function(column_name)> <operator> <value>;

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums 
FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

The order of a SELECT statement:
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

View

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested.

The fundamental difference between view and table: whether or not it stores the actual data. What the view actually stores is the SELECT statement.

Advantages of VIEW:

  • Views take very littel space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
  • Views allow frequently used SELECT statements to be saved as views so that they don’t have to be rewritten each time. Using views can greatly improve efficiency.
  • Views can join and simplify multiple tables into a single virtual table.
  • Views can hact as aggregated tables and presents the calculated results as part of the data.
  • Views can hide the complexity of data.
  • Views can represent a subset of the data contained in a table. A view can limit the degree of exposure of the underlying tables to the outer world.
  • Depending on the SQL engine used, ciews can provide extra security.

Database users can manipulate nested views, thus one view can aggregate data from other views.

Limits:

  • You cannot use ORDER BY when defining a view. Because, like in a table, the data rows are not ordered.
  • Updates (INSERT DELETE UPDATE) can only be made to views that are not obtained by aggregation.
-- create view
CREATE VIEW <view_name> AS
SELECT <column_name(s)>
FROM <table_name>
WHERE <condition>;

-- example
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products);

SELECT * FROM [Products Above Average Price];

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;

-- alter view
CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name(s)>
FROM <table_name>
WHERE <condition>;

ALTER VIEW <view_name> AS <SELECT statement>;

-- delete view
DROP VIEW <view_name>;

-- update view
INSERT INTO <view_name> VALUE();
UPDATE <view_name> SET <column_name(s)>=<values> WHERE <condition>;
DELETE FROM <view_name> WHERE <condition>;
Subquery
-- where
SELECT cat_id,good_id,good_name 
FROM goods 
WHERE good_id in(
    SELECT max(good_id) 
    FROM goods 
    GROUP BY cat_id
);

-- from
SELECT * FROM (
    SELECT cat_id,good_id,good_name 
    FROM goods 
    ORDER cat_id asc, good_id desc) AS tep 
GROUP BY cat_id;

-- in
SELECT * 
FROM department 
WHERE did in(
    SELECT did 
    FROM employee 
    WHERE age=20
);
    
-- exists
SELECT * 
FROM department 
WHERE EXISTS (
    SELECT did 
    FROM employee 
    WHERE age>21
);

-- any
SELECT * 
FROM department 
WHERE did > ANY (
    SELECT did 
    FROM employee 
);

-- all
SELECT * 
FROM department 
WHERE did > ALL(
    SELECT did 
    FROM employee 
);

-- comparison operator
SELECT * 
FROM department 
WHERE did = ALL(
    SELECT did 
    FROM employee 
    WHERE name='赵四'
);

Classification:

  • Scalar subquery: return the result of only one row and one column (i.e. a single value).
  • Linked subquery: A linked subquery is used when comparing within a subdivided group.
-- scalar
SELECT id, price 
FROM Products
WHERE price > ( 
    SELECT AVG(price) 
    FROM Products
);

-- linked
SELECT id,name,price 
FROM Products AS s1
WHERE price > (
    SELECT AVG(price)
    FROM Products AS s2
    WHERE s1.classification = s2.classification 
    GROUP BY classification
    );
-- condition must be placed in suquery
Functions

Arithmetic functions

+
-
*
/

-- absolute value
ABS(num) 

SELECT ABS(m) AS abs_val
FROM Sample;

-- calculate the remainder
MOD(dividend, divisor)

SELECT n,p,MOD(n,p) AS remainder
FROM Sample;

-- round off
ROUND(num,number of decimal places retained) 

SELECT ROUND(m,n) AS round_val
FROM Sample;

String functions

-- Concatenate Strings
SELECT str1 || str2 AS str_concat
FROM Sample
WHERE str1 = 'a';

-- length of Strings
SELECT str1, LENGTH(str1) AS len_str
FROM Sample;

-- toggle case
SELECT LOWER(str1) as low_str
FROM Sample;

SELECT UPPER(str1) as up_str
FROM Sample;

-- String substitution
REPLACE(str, target_substring, replaced_substring)

SELECT REPLACE(str1,'abc','ABC') AS rep_str
FROM Sample;

-- substring
SUBSTRING(str FROM gegin_index FOR length)

SELECT SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM Sample;

Date function

SELECT CURRENT_DATE;

SELECT CURRENT_TIME;

SELECT CURRENT_TIMESTAMP; -- date+CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP,
       EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
       EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
       EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
       EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
       EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
       EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

Cast functions

CAST(value AS type)

SELECT CAST('0001', AS SIGNED INTEGER) AS int_col; 
COALESCE(data1,data2,data3,...)
-- COALESCE is a specificspecific SQL function. This function 
-- returns the first value in the variable arguments that is 
-- not NULL starting from the left.

SELECT COALESCE(str2, 'NULL')
FROM Sample;

Result:

coalesce
———————
iv
def
‘NULL’
aaa
‘NULL’

Predicates
-- comparison predicate 
= < > <> <= >=

-- like
SELECT *
FROM Sample
WHERE str LIKE 'ddd%'; -- '%' represents 'any string longer than 0'
SELECT *
FROM Sample
WHERE str LIKE '%ddd%';
SELECT *
FROM Sample
WHERE str LIKE '%ddd';
SELECT *
FROM Sample
WHERE str LIKE 'ddd__'; -- a '_' represents a character

-- between
SELECT id, price
FROM Products
WHERE price BETWEEN 100 AND 1000; -- include 100 and 1000

-- is null / is not null
SELECT id, price
FROM Products
WHERE price IS NULL;

-- in / not in
SELECT id, price
FROM Products
WHERE price in (320,500,5000);

SELECT id, price
FROM ProductsA
WHERE id IN (SELECT id
             FROM ProductsB
             WHERE classification = 'A');

-- exists / not exists
-- Exist records meet the condtion, return TRUE;
-- not, return FALSE.
SELECT id, name, price
FROM Products AS P
WHERE EXISTS ( SELECT *
               FROM ProductsB AS PB
               WHERE PB.classification = 'C' 
               AND PB.id = P.id;
Case
SELECT name,
       CASE WHEN classification = 'A'
            THEN classification || ': clothes'
            WHEN classification = 'B'
            THEN classification || ': office'
            WHEN classification = 'C'
            THEN classification || ': kitchen'
            ELSE NULL
       END AS abc_class
FROM Products;
Set operation
-- UNION
SELECT id, name
FROM Products
UNION
SELECT id, name
FROM Products2;

-- UNION ALL : retain duplicate data
SELECT id, name
FROM Products
UNION ALL
SELECT id, name
FROM Products2;

-- INTERSECT
SELECT id, name
FROM Products
INTERSECT
SELECT id, name
FROM Products2
ORDER BY id;

-- EXCEPT : difference set
SELECT id, name
FROM Products
EXCEPT
SELECT id, name
FROM Products2
ORDER BY id;
Join
SELECT <column_name(s)>
FROM <table1>
JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN <table2>
ON <table1>.<column_name>=<table2>.<column_name>;

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

-- natural join
SELECT emp.ename,dept.dname
FROM emp 
NATURAL JOIN dept;
-- 1. If the two tables doing the natural join have more than one 
--    field of the same name and type, then they will be treated 
--    as a natural join.
-- 2. If the natural join is between two tables that only have the
--    same field name but different data types, then an error will 
--    be returned.
-- 3. Columns used in a natural join cannot have a table name or 
--    alias as a qualifier.
Categories: Backend DevelopmentDatabaseMySQL
Tags:

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

Related Posts

Java

Multithreading in Java and Spring Boot

1. Creating Threads in Java 1.1 Extending the Thread Class public class MyThread extends Thread { public void run() { // Code to be executed in the new thread for (int i = 0; i Read more…

hibernate

Hibernate in Spring Boot – Quick Start

Set up dependency <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> Configuring JPA and Hibernate  in application.properties file spring.datasource.url=jdbc:mysql://localhost:3306/dbname spring.datasource.username=root spring.datasource.password=password spring.jpa.hibernate.ddl-auto=update or application.yml spring: application: name: demoapp datasource: url: jdbc:mysql://localhost:3306/dbname username: root password: password driver-class-name: com.mysql.cj.jdbc.Driver jpa: hibernate: Read more…

Docker

Docker VII: DockerFile

DockerFile Reference: https://docs.docker.com/reference/dockerfile/ Best practices for Dockerfile instructions: https://docs.docker.com/develop/develop-images/instructions/

Table of Contents

← Catalogue