DDL 語句

DDL 是資料定義語言的縮寫，常用在資料庫的建立、刪除、修改上

建立資料庫

CREATE DATABASE dbname;
use dbname

刪除資料庫

DROP DATABASE dbname

建立表

CREATE TABLE tablename (
    column_name_1 column_type_1 constraints, 
    column_name_2 column_type_2 constraints, 
    ...
    column_name_n column_type_n constraints, 
);

CREATE TABLE emp(
  ename varchar(10), 
  hiredate date, 
  sal decimal(10, 2),
  deptno int(2)
);

-- 查看 table 定義
DESC tablename;

SHOW CREATE TABLE tablename \G;

刪除表

drop table emp;

修改表

1. 修改表類型

ALTER TABLE tablename MODIFY [COLUMN] column_name column_definition [FIRST|AFTER col_name];
alter table emp modify ename varchar(20);
alter table emp modify age int(3) first;

2. 增加表字段

ALTER TABLE tablename ADD [COLUMN] column_name column_definition [FIRST|AFTER col_name];
alter table emp ADD COLUMN age int(3);
alter table emp ADD COLUMN birth date after ename;

3. 刪除表字段

ALTER TABLE tablename DROP [COLUMN] column_name;
alter table emp DROP COLUMN age;

4. 欄位改名

ALTER TABLE tablename CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
alter table emp change age age1 int(4);

5. 更改表名

ALTER TABLE tabelname RENAME [TO] new_tablename
alter table emp rename to emp1;

CHANGE / FIRST | AFTER COLUMN 這些關鍵字屬於 MySQL 在標準 SQL 上的擴充，在其他資料庫上不一定適用。

DML

DML 是指對資料庫中表紀錄的操作，主要包含了插入（ insert ）、更新（ update ）、刪除（ delete ）、和查詢（ select ）。

插入紀錄

INSERT INTO tablename (field1, field2, field3, ..., fieldn) VALUES (value1, value2, value3, ..., valuen);
-- 一次插入多筆
INSERT INTO tablename (field1, field2, field3, ..., fieldn) VALUES 
    (record1_value1, record1_value2, record1_value3, ..., record1_valuen);
    (record2_value1, record2_value2, record2_value3, ..., record2_valuen);
    ...
    (recordn_value1, recordn_value2, recordn_value3, ..., recordn_valuen);

insert into emp (ename, hiredate, sal, deptno) values ('zzxl', '2020-01-01', '2000', 1);
-- 新增一筆資料，如果資料已經存在改成更新
insert into emp (ename, hiredate, sal, deptno) values ('zzxl', '2020-01-01', '2000', 1)
        ON DUPLICATE KEY UPDATE
        hiredate = NOW(), sal = 5000;

更新紀錄

UPDATE tablename SET field1=value1, field2=value2, ... fieldn=valuen [WHERE CONDITION];
-- 一次更改多個表
UPDATE t1, t2, ... tn SET t1.field1=value1, t2.field2=value2, ... tn.fieldn=valuen [WHERE CONDITION];

update emp set sal=4000 where ename='lisa';

刪除紀錄

DELETE FROM tablename [WHERE CONDITION];

delete from emp where ename='dony';

查詢紀錄

查詢方法通式

SELECT *|field1, ..., fieldn FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC]] [LIMIT offset_start, row_count];

去除重複

-- 去除重複
select distinct deptno from emp;

條件查詢

-- 還可以使用 >、<、<=、>=、=、!=
select * from emp where deptno=1 and sal<3000;

排序和限制

# desc 代表降冪、asc 代表升冪。 預設是升冪。
select * from emp order by deptno desc, sal ;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| lisa   | 2003-02-01 | 4000.00 |      2 |
| zzxl   | 2020-01-01 | 2000.00 |      1 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
+--------+------------+---------+--------+

select * from emp order by sal limit 2;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzxl  | 2020-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 4000.00 |      2 |
+-------+------------+---------+--------+
-- 從第二條開始數兩條
select * from emp order by sal limit 1,2;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
+--------+------------+---------+--------+

limit 屬於 MySQL 擴充語法，在其他資料庫上並不能通用。

聚合

SELECT [field1, field2, ..., fieldn] fun_name FROM tablename [WHERE CONDITION] [GROUP BY field1, field2, ..., fieldn [WITH ROLLUP]] [HAVING CONDITION]

• fun_name 表示要做的聚合操作，也就是匯總函數，常用的有 sum() （求總和）、count(*)（紀錄數）、max()（最大值）、min()（最小值）
• GROUP BY 表示要分類聚合的欄位
• WITH ROLLUP 表名是否對分類聚合後的節果在匯總
• HAVING 表示對分類後的結果再進行條件過濾

DCL

grant select, insert on sakila.* to 'zl'@'localhost' identified by '123'; 

revoke insert on sakila.* from 'zl'@'localhost'