从0开始MySQL

Introduction

（Po主整理笔记的自用小白日记）

基本规则

*Record=Raw = 行 - Horizontal entity
Field=Column = 列 - Vertical enity
*大小写不敏感
*Object的命名中间不可以有空格。（eg：‘PurchaseNmuber’ or ‘Purchase_Number’ is ok，but 'Purchase number’会报错。）
*SQL KeyWords是蓝色，Object Name是黑色，数字是橙色（MySQL中）,Comment是灰色

Ctrl+B Beautify/Reformat code优化代码格式
Ctrl+Alt+Enter Run 运行所有代码
Ctrl+Enter Run 运行选中代码
Ctrl+S Save
Ctrl+Shift+S Save as 另存为

Syntax

1. DDL , Data Definition Language ;
   create / alter / drop / rename / truncate (remove all the records)
2. DML , Data Manipulation Language ;
   select / insert / update / delete (precisely remote)
3. DCL , Data Control Language ;
   give rights: Grant / Revoke
4. TCL , Transaction Control Language ;
   commit (提教语句) / rollback (撤回上一步)

Compare

Primary & Unique

	Primary key	Unique Key
null value	×	√
number of keys	×	√
适用于多列	√	√

######Comparison Operators

SQL
=	equal to
＞	greater than
＞=	greater than or equal to
＜	less than
＜=	less than or equal to
!=	not equal, different from
<>	not equal, different from

DML Example

1.Create

CREATE DATABASE IF NOT EXISTS Sales;
CREATE SCHEMA IF NOT EXISTS Sales;

USE sales;

CREATE TABLE customers
(
	customer_id INT,
	first_name VARCHAR(255),
	last_name VARCHAR(255),
	email_address VARCHAR(255),
	name_of_complaints INT
);

【;】分号表该句话结束
【( )】注意括号中最后一行没有逗号

2.Select

SELECT * FROM sales;
SELECT * FROM sales.sales;

3.Drop

DROP TABLE customers;

4.Primary Key

CREATE TABLE customers
(
	customer_id INT,
	first_name VARCHAR(255),
	last_name VARCHAR(255),
	email_address VARCHAR(255),
	number_of_complaints INT,
PRIMARY KEY (customers_id)
);

CREATE TABLE items
(
	items_id VARCHAR(255),
	item VARCHAR(255),
	unit_price numeric(10,2),
	company_id VARCHAR(255),
PRIMARY KEY (items_id)
);

CREATE TABLE compamies
(
	company_id VARCHAR(255),
	company_name VARCHAR(255),
	headquarters_phone_number INT(12),
PRIMARY KEY (company_id)
);

5.Unique key

CREATE TABLE companies
(
	company_id VARCHAR(255),
	company_name VARCHAR(255) DEFAULT'X',
	headquarters_phone_number INT(12),
PRIMARY KEY (company_id),
UNIQUE KEY (headquarters_phone_number)
);

DROP TABLE companies;

6.Alter

ALTER TABLE customers
ADD UNIQUE KEY (email_address);

ALTER TABLE customers
CHANGE COLUNM number_of_complaints number_of_complaints INT DEFAULT 0;

7.INSERT

INSERT INTO customers (first_name, last_name,gender)
VALUES ('Peter', 'Figaro', 'M')
;

SELECT * FROM customers;

ALTER TABLE customers
ALTER COLUMN number_of_complaits DROP DEFAULT;

8.NOT NULL/NULL

ALTER TABLE companies
MODIFY company_name VARCHAR(255) NULL;

ALTER TABLE companies
CHANGE COLUMN company_name company_name VARCHAR(255) NOT NULL;

INSERT INTO companies (headquarters_phone_number)
VALUES ('+1 (202) 333-5218')   #执行时会报错 ERROR CADE 1364,doesn`t have default value 需要修改为：
INSERT INTO companies (headquarters_phone_number, company_name)
VALUES ('+1 (202) 333-5218','company A')
;

9.Condition

SELECT * FROM employees
WHERE first_name = 'Deris' AND gender = 'M';

SELECT * FROM employees
WHERE first_name = 'Denis' OR first_name = 'Elvis';

SELECT * FROM employees
WHERE last_name = 'Deris' AND (gender = 'M' OR gender = 'F');
#AND优先级大于OR，若想生效‘M OR F’，要加括号

SELECT * FROM employees
WHERE first_name IN ('Cathie','Mark','Nathan');
#IN ('...','...','...')等同OR ... OR ...
#NOT IN ('','','')同理

10.Pattern matching
% 替代字符序列
_ 表示单个字符。 eg：Mar_ → Mark Mary

SELECT * FROM employees
WHERE first_name LIKE('Mar%'); #Mark/Marv...
# %ar% ——→Mark、Varty、Larity...

11.Between and

SELECT * FROM employees
WHERE hire_date BETWEEN '1990-01-01' AND '2020-01-01';
#NOT BETWEEN同理

12.Select distinct

SELECT DISTINCT gender
FROM employees; #选择不重复的

SELECT DISTINCT 
hire_date
FROM employees;

13.Order by

SELECT * FROM employees
ORDER BY first_name; #按firstname的顺序排序

ORDER BY first_name ASC; # ASC是ascending升序；DESC是descending 降序

ORDER BY first_name, last_name ASC; 

14.Aggregate Function

SELECT COUNT(emp_no) FROM employees;

SELECT COUNT(DISTINCT emp_no) FROM employees;
#此处distinct一定要放在括号里面

15.Group By, AS(Alias Name), Having, Order by

SELECT emp_no
FROM dept_emp
WHERE from_date > '2000-01-01'
GROUP BY emp_no
HAVING COUNT(from_date) > 1
ORDER BY emp_no;

16. LIMIT
    Edit-Reference-SQL Excution

SELECT * FROM salaries
ORDER BY salary DESC
LIMIT 10;

顺序：
SELECT column_name COUNT column_name AS new_name
FROM table_name
WHERE conditions
GROUP BY column_name
HAVING conditions
ORDER BY column_name
LIMIT number ;

17.INSERT

SELECT 
    *
FROM
    employees
ORDER BY emp_no DESC
LIMIT 10;
insert into employees
(
	emp_no,
    birth_date,
    first_name,
    last_name,
    gender,
    hire_date
)values
(
	999901,  #999901是String，不要加引号。
    '1986-04-21',
    'john',
    'smith',
    'm',
    '2011-01-01'
);
#INSERT...VALUE要按照严格顺序和格式

从另一张表中插入数据

SELECT 
    *
FROM
    departments
LIMIT 10;


CREATE TABLE departments_dup 
(
    dep_no CHAR(4) NOT NULL,
    dept_name VARCHAR(40) NOT NULL
);


SELECT 
    *
FROM
    departments_dup;
insert into departments_dup
(
	dep_no,
    dept_name
)
select * from departments;


SELECT 
    *
FROM
    departments_dup
ORDER BY dep_no;

TCL Example

1.Update
update… set…where …
如果没有加WHERE condition，所有数据都会被update

use employees;
SELECT 
    *
FROM
    employees
WHERE
    emp_no = 999901;

UPDATE employees 
SET 
    first_name = 'Stella',
    last_name = 'Parkinson',
    birth_date = '1990-12-31',
    gender = 'F'
WHERE
    emp_no = 999901;
    
SELECT 
    *
FROM
    employees
ORDER BY emp_no DESC
LIMIT 10;

2.Rollback

SELECT 
    *
FROM
    departments_dup
ORDER BY dep_no;

commit;    #commit非常重要！一定要保存。且一旦commit后，就无法rollback了

UPDATE departments_dup 
SET 
    dep_no = 'd011',
    dept_name = 'Quality Control';
    
rollback;

3.Delete
DELETE中的WHERE condition也非常重要！
缺失WHERE condition会将整表删除

DELETE FROM employees 
WHERE
    emp_no = 999903;
#parent table`s primary key 被删掉后, child table里的内容也会全部删掉

DROP	TRUNCATE	DELETE
整表删除，不能ROLLBACK（包括Structure、all related objects、indexes、constraints）	删除所有内容，但column还在（约等于DELETE时没有加WHERE condition）auto-increment values将重置（不是接着原表的9.10.11，而是从1.2.3开始）	row by row删除（新录入的数据auto_increment会从11.12.13往下接）

TRUNCATE不是row by row删除，整体删，速度比DELETE快。