首頁 » SQL » mysql

Mysql - Syntax

由 datahunter 在 五, 17/08/2012 - 22:09 發表

最後更新: 2018-12-10

介紹

  • Comment
  • PRIMARY 與 UNIQUE Key 分別
  • INSERT
  • DELETE
  • SELECT
  • UPDATE
  • LIMIT
  • DB (CREATE, DROP)
  • Table (Create, Drop)
  • Show(tbl, db, permission)
  • Clone Tables
  • CHARSET
  • DISTINCT
  • Group By
  • ORDER BY
  • JOIN
  • FOREIGN KEY
  • TMP Table
  • Sub-Queries
  • INCREMENT
  • TRUNCATE tablename 與 Delete from tablename
  • 解決 username 有 duplicate
  • 計算
    - DIV()
  • TMP table Usage
  • INCREMENT
  • 解決 username 有 duplicate
  • VIEW
  • Show PROCEDURE, FUNCTION, TRIGGERS
  • FOREIGN KEY
  • ON DUPLICATE KEY UPDATE
  • 找出同一 transaction_id 內多隻 record
  • 找出會員最後一次購買記錄

 

Comment

 

Syntax

  • # character to the end of the line.
  • -- sequence to the end of the line
  • /* sequence to the following */

ie.

CREATE TABLE another_table SELECT /* your query goes here */

INSERT INTO new_table_name SELECT -- your query goes here

 

PRIMARY 與 UNIQUE Key 分別

 

有 Key = 有 index

A UNIQUE index creates a constraint for a column whose values must be unique.

Unlike the PRIMARY index, MySQL allows NULL values in the UNIQUE index.

In addition, a table can have multiple UNIQUE indexes.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key

 

INSERT

 

INSERT

# 支援 Column 的運算

INSERT INTO tbl_name (col1, col2) VALUES(15, col1 * 2);

# 填所有 Column 可以直用 VALUES

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

# 支援多項 record 一次過 insert

INSERT INTO example
  (id, name, value)
VALUES
  (001, 'Name 1', 'Value 1'),
  (002, 'Name 2', 'Value 2');

INSERT ... SELECT

寫法 1

INSERT INTO
  vmail.`control_relay_ip`(ip_address, remark)
SELECT
  ip_address , remark
FROM
  mail_old.`control_relay_ip`

寫法 2

INSERT INTO wblist VALUES (
    (SELECT id FROM users where email=BINARY('@datahunter.org')),
    (SELECT id FROM users where email=BINARY('@datahunter.org')),
    "W"
)

所有 table feild 一樣時

INSERT INTO
  roundcubemail.contacts
SELECT
    *
FROM
  roundcubemail_old.contacts

 

DELETE

 

DELETE

DELETE FROM table_name [WHERE Clause]

i.e.

delete from `user` where convert(`user`.`email` using utf8)  = \'user@domain\'  limit 1

DELETE ... Select

SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause:

DELETE FROM posts WHERE id IN (
    SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
)

Table lock time (LIMIT)

When a deletion query using a WHERE clause starts, it will lock that table.

 * 如果很多 record 但沒有 LIMIT, 那會 lock 很耐 Table !!

Delete FROM `Mail` WHERE `received_time` < date_sub(now(), interval 1 QUARTER) LIMIT 1000

Other settings

LOW_PRIORITY modifier

The server delays execution of the DELETE until no other clients are reading from the table.

This affects only storage engines that use only table-level locking.
(such as MyISAM, MEMORY, and MERGE)

QUICK modifier

For MyISAM tables, the storage engine does not merge index leaves during delete,
 which may speed up some kinds of delete operations.

同一個 Tables 的 Delete

You cannot modify the same table you selected from a subquery within the same query.

However, you can either SELECT then DELETE in separate queries,
 or nest another subquery and alias the inner subquery result (looks rather hacky, though):

錯的:

DELETE FROM document_revisions d1 WHERE edit_date <
  (SELECT MAX(edit_date) FROM document_revisions d2
   WHERE d2.document_id = d1.document_id);

正確:

DELETE d1 FROM document_revisions d1 JOIN document_revisions d2
  ON d1.document_id = d2.document_id AND d1.edit_date < d2.edit_date;

 

SELECT

 

select 時 mysql 會把時間停止

select current_timestamp(6),sleep(1),current_timestamp(6);

current_timestamp 的結果是一樣

mysql select one column like another column

寫法1

SELECT *
FROM alias
WHERE goto LIKE concat( '%', address, '%' )

寫法2

SELECT Id, Url, ModelId
From <table>
WHERE Url like '%' + ModelId + '%'

fields containing leading or trailing whitespace

寫法1

SELECT * FROM Foo WHERE field != TRIM(field);

寫法2

SELECT * FROM `foo`
WHERE
   (name LIKE ' %')
OR
   (name LIKE '% ')

 

IN

 

SELECT  source
FROM    forwardings
WHERE   source NOT IN
(
  SELECT  email
  FROM    users
)

 

Optimization SQL Query - 不用 "IN"

 

原本 IN 的 SQL

SELECT node_revisions.vid, node.nid
FROM node_revisions
LEFT JOIN node ON node_revisions.vid = node.vid AND node.nid = '2529'
WHERE node_revisions.nid = '2529';

優化思路: 改用 LEFT JOIN

SELECT node_revisions.vid
FROM node_revisions
LEFT JOIN node ON node_revisions.vid = node.vid AND node.nid = '2529'
WHERE node.nid IS NULL AND node_revisions.nid = '2529';

 

 

User-defined variable

 

exist only within the context of that session;

原本

SELECT vid FROM node_revisions WHERE nid="2529" AND
vid NOT IN (SELECT vid FROM node WHERE nid="2529");

改用 VAR

SET @nid = 2529;
SELECT vid FROM node_revisions WHERE nid=@nid AND
vid NOT IN (SELECT vid FROM node WHERE nid=@nid);

 

Count()

 

Select count(*) from t1

 

UPDATE

 

* You can update values in a single table at a time.

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

# update with join

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2.C2
SET T2.C3 = expr
WHERE condition

 

 

LIMIT

 

Using LIMIT you will not limit the count or sum but only the returned rows.

 

UNION

 

用於連接兩個 "SELECT" 並合併結果

Usage: SELECT ... UNION [ALL | DISTINCT] SELECT ...

i.e.

CREATE TABLE `food` (`id` INT(11) AUTO_INCREMENT, `name` CHAR(50), PRIMARY KEY (`id`));
CREATE TABLE `fruit` ( `id` INT(11) AUTO_INCREMENT, `name` CHAR(50), PRIMARY KEY (`id`));
INSERT INTO food(name) VALUES("apple"), ("banana"), ("bread");
INSERT INTO fruit(name) VALUES("apple"), ("apple"), ("banana"), ("orange");

# DISTINCT <- DEFAULT

合併兩次 SELECT 的結果, 並只保留一個重複

SELECT name FROM food UNION SELECT name FROM fruit        -- 只出現 1 個 apple

# ALL

SELECT name FROM food UNION ALL SELECT name FROM fruit    -- 會出現 3 個 apple

 

DB (CREATE, DROP)

 

CREATE

CREATE DATABASE dbname;

CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

 

DROP

DROP database dbname;

 

Table (Create, Drop)

 

Create

CREATE TABLE table_name (column_name1 column_type, column_name2 column_type);

Set engine

SET default_storage_engine=MYISAM;

CREATE TABLE table_name (.....) ENGINE = INNODB;

Drop

DROP TABLE table_name;

List

SHOW FULL TABLES;

 * Table_type 會有 "BASE TABLE", "VIEW"

Create table by select

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

 

Show(tbl, db, permission)

 

tbl:

show tables;

SHOW CREATE TABLE table_name;

show tables in dbname like '%keyword';

describe table_name

OR

desc table_name

db:

# 查看有什麼 DB

show databases;

# 查看 DB Schema

# DATABASE 與 SCHEMA 沒有分別

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

i.e.

show create database My_DB_Name

CREATE DATABASE `My_DB_Name` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */

permission:

SHOW GRANTS FOR 'root'@'localhost';

 

Clone Tables

 

CREATE TABLE tbl1 LIKE tbl;

INSERT INTO tbl1 SELECT * FROM tbl;

 

CHARSET

 

CHARSET utf8

 

數不同(DISTINCT)

 

COUNT(DISTINCT alias.address) AS alias_count

 

Group By

 

Animal

Type | Color  | Num
cat  | red    | 3
dog  | back   | 5
cat  | yellow | 2
dog  | red    | 1

mysql combine rows with same type

i.e.

select type, group_concat( color separator ',') as color
from Animal group by type;

 * Table name 有分大細階, column 則沒有

out

+------+------------+
| type | color      |
+------+------------+
| cat  | red,yellow |
| dog  | back,red   |
+------+------------+

用到 AVG(), COUNT(), MAX(), MIN(), SUM() 時就要用 GROUP BY

i.e.

SELECT type, SUM(num) FROM Animal GROUP BY type;

out

+------+----------+
| type | SUM(num) |
+------+----------+
| cat  |        5 |
| dog  |        6 |
+------+----------+

GROUP BY on COUNT/MIN/MAX/SUM/AVG/DISTINCT

COUNT [GROUP BY]

COUNT + HAVING + GROUP BY (HAVING可以看作后置WHERE语句)

MIN/MAX [GROUP BY]

SUM/AVG [GROUP BY]

DISTINCT [GROUP BY]

所有的这些聚合函数加上 GROUP BY 之后, 都只对 GROUP BY 部分有效.

 

GROUP_CONCAT

 

# 將多個 username 合併並以 "," 分隔

SELECT DISTINCT GROUP_CONCAT(username SEPARATOR ',') FROM  `mailbox`;

# Default

 * group_concat_max_len = 1024 bytes

 * group_concat_max_len 與 max_allowed_packet 有關 (max_allowed_packet = 1048576)

 

ORDER BY

 

Basic Usage

# Default: ASC (1 -> 9, a -> z)

SELECT * FROM t1 ORDER BY key_part1 [ASC | DESC];

Sub Order

只有第一列相同時, 才使用第二列:

SELECT * FROM t1 ORDER BY key_part1, key_part2, ... [ASC | DESC];

Mix ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

 

JOIN

 

INNER JOIN

Example:

Table

  • tbl_user                # Uid | Gid | UserName
  • tbl_group              # Gid | GroupName

# JOIN ... ON ... 寫法

Select UserName, GroupName
From tbl_user as t1 Join tbl_group as t2
On t1.gid = t2.gid;

 * 只有符合 "= " 才出現

sqlite> SELECT Name, Day FROM Customers AS C JOIN Reservations
   ...> AS R ON C.CustomerId=R.CustomerId;

# WHERE ... = ... 寫法

SELECT Name, Day 
FROM Customers AS C, Reservations AS R
WHERE C.CustomerId = R.CustomerId;

 * 只有符合 "= " 才出現

LEFT JOIN, RIGHT JOIN

# LEFT JOIN: table1 的永遠出現, 沒有的值會用 NULL 補上

SELECT table_column1, table_column2···
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.column_name = t2.column_name;

# RIGHT JOIN

 


 





	TMP table Usage


 


CREATE TEMPORARY TABLE t1 (select * from t2);


--------------


SELECT * from t1


--------------


show status like '%tmp%';


--------------


DROP TEMPORARY TABLE IF EXISTS `t1`;


--------------


 * 當連線關閉時，TEMPORARY TABLE會自動被刪除


 * 當 Table 的大小超過 tmp_table_size 時, 資料將會寫入 Disk


 





	Sub-Queries


 


# SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause.


DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

 





	AUTO_INCREMENT


 


 * Each table can have only one AUTO_INCREMENT column.


 * It must defined as a key(indexed) (PRIMARY KEY | UNIQUE | INDEX ...)


    i.e.


CREATE TABLE `food` (`id` INT(11) AUTO_INCREMENT, `name` CHAR(50), INDEX `id` (`id`));
 * AUTO_INCREMENT column should always be an Integer type(最好加上 UNSIGNED )


---


If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.


When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails.


---


Reset the AUTO_INCREMENT counter


ALTER TABLE tablename AUTO_INCREMENT = 1
 





	TRUNCATE tablename 與 Delete from tablename


 


TRUNCATE users TB 時會見到以下 msg


#1701 - Cannot truncate a table referenced in a foreign key constraint
 (`roundcubemail`.`contactgroupmembers`, CONSTRAINT `contact_id_fk_contacts` 
 FOREIGN KEY (`contact_id`) REFERENCES `roundcubemail`.`contacts` (`contact_id`))
mysql roundcube -e "SET FOREIGN_KEY_CHECKS = 0; TRUNCATE users"


truncate v.s. delete


    

  • 
		truncate      # DDL
    • 

  • 
		delete         # DML
    • 



truncate 無法通過binlog rollback


truncate會重置表的自增值；delete不會


truncate不會激活與表有關的刪除觸發器


truncate後會使表和索引所佔用的空間會恢復到初始大小


 





	解決 username 有 duplicate


 


證明真是有 duplicate:


SELECT * FROM users
where username LIKE 'postmaster@%'
找出所有 duplicate


SELECT * FROM users group by username having count(*) >= 2
 





	VIEW


 


Create


CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Drop


# If you drop a view you don’t lose any data


DROP VIEW view_name;
List


[1]


SHOW FULL TABLES
WHERE table_type = 'VIEW';
[2]


SELECT table_name
FROM information_schema.views
WHERE information_schema.views.table_schema = 'MyDbName';
Show Create


SHOW CREATE VIEW view_name
 





	計算


 


DIV()


MySQL division operator is used for integer division.


[1] "/" - num / num


i.e.


SELECT 12 / 3;


[2] "DIV" - expression DIV num


# An expression which contains the dividend.


i.e.


SELECT 12 DIV 3;


 





	Show PROCEDURE, FUNCTION, TRIGGERS


 


Procedure:


USB DB


SHOW PROCEDURE STATUS;


Or


SHOW FUNCTION STATUS;


Create Procedure [Procedure Name] ([Parameter 1], [Parameter 2], ... )
Begin
SQL Queries..
End
# Call


CALL [Procedure Name] ([Parameters]...)
Trigger:


USB DB


SHOW TRIGGERS;


CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype
BEGIN
   declaration_section
   executable_section
END;
PROCEDURE, FUNCTION, TRIGGERS 分別


A stored routine is either a procedure or a function.


----


A PROCEDURE does not return a value.


Instead, it is invoked with a CALL statement to perform an operation such as


    modifying a table or processing retrieved records.


You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.


Stored procedures have a precompiled execution plan, where as functions are not.


----


A FUNCTION always returns a value using the return statement.


A Function returns 1 value only.


Functions are normally used for computations where as procedures are normally used for executing business logic.


A function can be called directly by SQL statement like select func_name from dual while procedures cannot.


 





	FOREIGN KEY


 


i.e.


# 在 Table dictionary


CONSTRAINT `user_id_fk_dictionary`
FOREIGN KEY (`user_id`)  REFERENCES `users`(`user_id`)
ON DELETE CASCADE ON UPDATE CASCADE
查看


SHOW CREATE TABLE dictionary
A FOREIGN KEY is a key used to link two tables together


A FOREIGN KEY is a field (or collection of fields) in one table

	that refers to the PRIMARY KEY in another table.


The table containing the foreign key is called the child table,

	and the table containing the candidate key is called the referenced or parent table.


The FOREIGN KEY constraint is used to

	 - prevents invalid data from being inserted into the foreign key column

	 - prevent actions that would destroy links between tables


Syntax


[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
reference_option:


RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT


Symbol


MySQL implicitly creates a foreign key index that is named according to the following rules:

	If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used.


Add


ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Remove


ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
 





	ON DUPLICATE KEY UPDATE

	 


update data if a duplicate in the UNIQUE index or PRIMARY KEY error occurs when you insert a row into a table.


you specify a list of column-value-pair assignments in case of duplicate.


INSERT INTO quota (username, quota_bytes) VALUES ('$username_field', '$value') 
 ON DUPLICATE KEY UPDATE quota_bytes='$value'

returns the number of affected-rows based on the action it performs:


    

  • 
		If the new row is inserted, the number of affected-rows is 1.
    • 

  • 
		If the existing row is updated, the number of affected-rows is 2.
    • 

  • 
		If the existing row is updated using its current values, the number of affected-rows is 0.
    • 



UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated.


 * In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.


 





	找出同一 transaction_id 內多隻 record


 


例子: 收據與產品


情況: 一張單(transaction_id)內多件產品(item_id)


select transaction_id, created_time, item_id,
group_concat(name separator ','),
group_concat(CONVERT(price, CHAR) separator ',')
from transaction
group by transaction_id
 





	找出會員最後一次購買記錄


 


Table


CREATE TABLE IF NOT EXISTS `MyOrder` (
  `id` int(11) unsigned NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `member` varchar(255) NOT NULL,
  `buy_datetime` datetime NOT NULL,
  `iteam` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `MyOrder`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `MyOrder`
  MODIFY `id` int(11) unsigned NOT NULL AUTO_INCREMENT;
Record


INSERT INTO `test`.`MyOrder` (`transaction_id`, `member`, `buy_datetime`, `iteam`)
VALUES
('1', 'A', '2021-08-01 00:00:00', 'apple'),
('2', 'A', '2021-08-02 00:00:00', 'orange'),
('3', 'B', '2021-08-03 00:00:00', 'orange'),
('4', 'B', '2021-08-04 00:00:00', 'apple'),
('1', 'A', '2021-08-05 00:00:00', 'orange'),
('2', 'A', '2021-08-06 00:00:00', 'orange');
找最後 record


SELECT m.*
FROM MyOrder m                              -- "m" from "max"
    LEFT JOIN MyOrder b                     -- "b" from "bigger"
        ON m.member = b.member
        AND m.buy_datetime < b.buy_datetime -- want "bigger" than "max"
WHERE b.buy_datetime IS NULL                -- keep only if there is no bigger than max
 




 

  


    
» 
  • 瀏覽次數： 117814
    • 

  

  

    

    


Creative Commons license icon
Creative Commons license icon