[root@host]# mysql -u root -p -h 192.168.1.1
Enter password:ShaZam
mysql> exit
Bye
CREATE DATABASE 數(shù)據(jù)庫名;
[root@host]# mysql -u root -p
Enter password:****** # 登錄后進入終端
mysql> create DATABASE CI;
mysqladmin -u root -p create CI;
drop database <數(shù)據(jù)庫名>;
mysql> drop database CI
mysqladmin -u root -p drop CI;
[root@host]# mysql -u root -p
Enter password:******
mysql> use CI;
Database changed
mysql>
To be continue
CREATE TABLE table_name (column_name column_type);
CREATE TABLE IF NOT EXISTS `ci_diff`(
`id` INT UNSIGNED AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE table_name ;
root@host# mysql -u root -p
Enter password:*******
mysql> use CI;
Database changed
mysql> DROP TABLE ci_diff
Query OK, 0 rows affected (0.8 sec)
mysql>
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ),(),();
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]
你可以使用 LIMIT 屬性來設(shè)定返回的記錄數(shù)
你可以通過OFFSET指定SELECT語句開始查詢的數(shù)據(jù)偏移量耳标。默認情況下偏移量為0。
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]]
condition2.....
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
DELETE FROM table_name [WHERE Clause]
SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = somevalue'
LIKE 通常與 % 一同使用邑跪,類似于一個元字符的搜索次坡。
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
DISTINCT: 可選,刪除結(jié)果集中重復(fù)的數(shù)據(jù)呀袱。默認情況下 UNION 操作符已經(jīng)刪除了重復(fù)數(shù)據(jù)贸毕,所以 DISTINCT 修飾符對結(jié)果沒啥影響。
ALL: 可選夜赵,返回所有結(jié)果集,包含重復(fù)數(shù)據(jù)乡革。
SELECT field1, field2,...fieldN table_name1, table_name2...ORDER BY field1, [field2...] [ASC [DESC]]
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
SELECT coalesce(name, '總數(shù)'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
- 連接的使用: 多張表的數(shù)據(jù)匯總成一種形式的數(shù)據(jù)
- INNER JOIN(內(nèi)連接,或等值連接):獲取兩個表中字段匹配關(guān)系的記錄寇僧。
- LEFT JOIN(左連接):獲取左表所有記錄,即使右表沒有對應(yīng)匹配的記錄沸版。
- RIGHT JOIN(右連接): 與 LEFT JOIN 相反嘁傀,用于獲取右表所有記錄,即使左表沒有對應(yīng)匹配的記錄视粮。
INNER JOIN 代表關(guān)聯(lián)表
ON 代表之間相同的字段
WHERE 代表從數(shù)據(jù)庫獲取對應(yīng)的數(shù)值
SELECT a.diff_id, a.revision_id, b.board_name, b.process_type, COUNT(*) as numm, a.username FROM phab_monitor a INNER JOIN phab_compile_result b ON a.diff_id = b.diff_id where a.diff_id=391517 group by b.board_name;
IS NULL: 當(dāng)列的值是 NULL,此運算符返回 true细办。
IS NOT NULL: 當(dāng)列的值不為 NULL, 運算符返回 true。
<=>: 比較操作符(不同于=運算符),當(dāng)比較的的兩個值為 NULL 時返回 true笑撞。