Since 334 has to and likes to play with DATA, learning DB is essential.

RUN→_→

Relational database management system

Intro

Database: A collection of associated tables.
Table: A matrix of data.
Redundancy: Storing duplicate data, enhancing data security.
Primary Key:

  • A primary key is unique.
  • A data table can only contain one primary key.

Foreign Key: Associate two tables.
Composite Key: use multiple columns as a single index key, usually used for composite indexes.

Install

Im lazy,see⬇️

doc

SQL

Data Definition Language

  • Obtain data in the database catalog
  • Part of action queries
  • CREATE, DROP, ALTER

Data Manipulation Language

  • Add, modify, and remove rows from tables
  • INSERT, UPDATE, DELETE

Data Query Language

  • retrieve data from tables, determining how the data is presented to applications.
  • SELECT, WHERE, ORDER BY, GROUP BY, HAVING

Transaction Processing Language

  • Let all rows affected by DML update promptly
  • BEGIN TRANSACTION, COMMIT, ROLLBACK

Cursor Control Language

  • Independent operations on one or more tables.

  • DECLARE CURSOR, FETCH INTO, UPDATE WHERE CURRENT

Data Control Language

  • Determine individual user’s access to database objects
  • GRANT, REVOKE

DDL

DB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#create

create database <dbName>;
create database if not exists <dbName>;
create database <dbNAme> character set <charSet>;

#show

show databases;
show create database <dbName>;


alter database <dbName> character set <charSet>;
drop databse <dbName>; #delete
select databse(); #check current db
use <dbName>; #switch db

Table

1
2
3
4
5
CREATE table table_name(
 column1 datatype1,
 column2 datatype2,
 columnN datatypeN
);

Show

1
2
3
4
5
6
show tables;

desc <dbName>;      #show table structure

show create table <dbName>.  #show table information

Create

1
2
create table <NewDB> like <OldDB>;  #Same Structure

Delete

1
2
3
drop table <dbName>;
drop table if exists <dbName>;

Alter

1
2
3
4
5
alter table <dbName> add <columnName> datatype;     #add
alter table <dbName> modify <columnName> datatype(new);  #modify type

#modify ColumnName
alter table <dbName> change <oldColName> <newColName> datatype;

Delete

1
2
3
alter table <dbName> drop <columnName>;   #delete column
rename table <oldDb> to <newDb>;    #change table name
alter table <dbName> character set <charSet>; #change charSet

DML

Insert

1
insert into <dbName> [column1,...,columnN] values(value1,...,valueN);

Update

1
2
update <dbName> set col1=val1,...,colN=valN [where conditionExpression];
#conditionExpression eg. id=34

Delete

1
2
3
4
5
delete from <dbName> [where conditionExpression];

#without condition
delete from <dbName>;
truncate table <dbName>;

delte remove records

truncate removes the complete data from an existing table but not the table itself

DQL

Query

1
2
select * from <dbName>; # query all the records, show all the cols
select col1,...,colN from <dbName>; #query all the records, show the selected cols;

Alias

1
2
select <colName> as <aliasName> from <dbName>;
select <colName(s)> from <dbName> as <aliasDBName>;

Distinct

1
select distinct col1,...,colN from <dbName>;

Operation

  1. <> unequal
    In mysql could also use !=

  2. between … and … (Contain border)

  3. in(val1,…, valN) Equal to the values

  4. not in (val1, …, valN) not equal to the values

  5. is, don’t use ‘’=’’ !!!!!!!!!!!!!

  6. and, or ,not (yknow)

Like

Search for a specified pattern in a col

  • % : represent any amount of characters

  • _ : represent a single character
    eg

1
select * from people where name like 'a%'

Sort

1
select */cols from <dbName> [where conditionExpression] [order by <colName> [asc/desc]]

asc: ascending,default
desc: descending

Group by

  1. Exhaustiveness
  2. Mutual exclusivity
1
select */cols from <dbName> [group by cols [having conditionExpression]]

WHERE

  • Filter data before grouping
  • Cannot use aggregate functions

HAVING

  • Filter data after grouping
  • Can use aggregate functions.

Aggregate Functions

count(colName)
sum(colName)
avg(colName) # Average
min(colName)
max(colName)

1
select func(colName) from <dbName>;

group_concat

Concatenate data from rows;

1
select col1,...,colN group_concat ([distinct] col1 [order by clause] [separator str_val]) from <dbName> group by col2;

Distinct: eleminate the repetition
order by: sort
separator: default is ,

with rollup

Add a new row at the bottom to record the total sum of all records in the columns

limit

1
select * from <dbName> [limit offset, count];

offset: start from the offset, default 0
count: number of records to display

Fuzzy Query

%: any number of characters
_: single character, usually used to limit the length of the string
[]: any character in the brackets (like regex)
[^]: any character not in the brackets