SQL操作
CREATE TABLE
CREATE TABLE用來創(chuàng)建一個(gè)表
//創(chuàng)建一個(gè)表技潘，表的名稱為table_name犯建，該表有三列蛹头，列名分別為column_1专甩，column_2函卒，column_3.列中的數(shù)據(jù)類型分別為整形辆憔，文本，整形
CREATE TABLE table_name (
column_1 INTEGER,
column_2 TEXT,
column_3 INTEGER
);
eg:
CREATE TABLE table_test (
id INTEGER,
name TEXT,
age INTEGER
);
INSERT INTO
INSERT INTO用于向列中插入值
//分別向列id, name, age中插入 1, 'Justin Bieber', 21
INSERT INTO table_test (id, name, age) VALUES (1, 'Justin Bieber', 21);
//插入多條數(shù)據(jù)
1报嵌、MySQL數(shù)據(jù)庫：
INSERT INTO table_test (id, name, age)
SELECT 1, 'Justin Bieber', 21
UNION SELECT 2, 'Marry', 25
UNION SELECT 3, 'JOJO', 23 ;
INSERT DELAYED INTO `table_test ` (`id`, `name`, `age`) VALUES
(1, 'Justin Bieber',21),
(2, 'Marry', 25),
(3, 'JOJO',23);
2虱咧、oracle數(shù)據(jù)庫：
-----------第一種插入方式---------------
INSERT INTO table_test (id, name, age) VALUES (1, 'Justin Bieber',21);
INSERT INTO table_test (id, name, age) VALUES (2, 'Marry',25);
INSERT INTO table_test (id, name, age) VALUES (3, 'JOJO',23);
-----------第兩種插入方式---------------
INSERT INTO table_test (id, name, age)
SELECT 1, 'Justin Bieber',21 FROM dual UNION ALL
SELECT 2, 'Marry',25 FROM dual UNION ALL
SELECT 3, 'JOJO',23 FROM dual;
UPDATE
用于修改表中的數(shù)據(jù)
//語法
UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值
//修改id=1這一行的age列的數(shù)據(jù)為22
UPDATE table_test SET age = 22 WHERE id = 1;
ALTER TABLE
修改表，用于增加锚国，修改腕巡，刪除列
//ADD COLUMN添加一個(gè)列
ALTER TABLE table_test ADD COLUMN address TEXT;
//DROP COLUMN刪除一個(gè)列
ALTER TABLE table_test DROP COLUMN address ;
DELETE
刪除表中的行
//IS NULL表示值是NULL或者不存在，刪除age列中值不存在的行
DELETE FROM table_test WHERE age IS NULL;
SQL查詢
DISTINCT
過濾重復(fù)的數(shù)據(jù)
SELECT DISTINCT * FROM table_test ;
SELECT DISTINCT age FROM table_test ;
WHERE
通過各種條件定位到具體的數(shù)據(jù)
操作符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某個(gè)范圍內(nèi)
LIKE 搜索某種模式
//統(tǒng)計(jì)age大于24的數(shù)據(jù)
SELECT * FROM table_test WHERE age > 24;
LIKE
模糊匹配
//_通配一個(gè)字符
SELECT * FROM table_test WHERE name LIKE 'Mar_y';
//%通配任意多個(gè)字符
SELECT * FROM table_test WHERE name LIKE '%Bieber%';
BETWEEN
匹配一個(gè)范圍
//查到age范圍在23到30之間的數(shù)據(jù)
SELECT * FROM table_test WHERE age BETWEEN 23 and 30;
AND
邏輯與
//查到age范圍在23到30之間的數(shù)據(jù)
SELECT * FROM table_test WHERE age >= 23 and age <= 30;
OR
邏輯或
//查到age范圍不在在23到30之間的數(shù)據(jù)
SELECT * FROM table_test WHERE age <= 23 or age >= 30;
等同于
SELECT * FROM table_test WHERE age not BETWEEN 23 and 30;
ORDER BY
//依據(jù)某一列進(jìn)行排序血筑，ASC是順序排序绘沉，DESC是逆序排序。
SELECT * FROM table_test ORDER BY age ASC;
SQL計(jì)算
COUNT()
COUNT()是最快的方式豺总，統(tǒng)計(jì)一張表總共的行數(shù)车伞。COUNT()函數(shù)的參數(shù)是一個(gè)列的名稱，統(tǒng)計(jì)整個(gè)表所有的行數(shù)時(shí)喻喳，采用通配符*;
//統(tǒng)計(jì)整個(gè)表的行數(shù)
SELECT COUNT(*) FROM table_test ;
//統(tǒng)計(jì)age為25的行數(shù)
SELECT COUNT(*) FROM table_test WHERE age= 25;
GROUP BY
GROUP BY <列名> 將一列中相同值的列分成一組
//按照age進(jìn)行分組另玖，并統(tǒng)計(jì)每組元素的個(gè)數(shù)
SELECT age, COUNT(*) FROM table_test GROUP BY age;
SUM()
SQL通過SUM()可以很容易統(tǒng)計(jì)一列的和
//計(jì)算age一列的和
SELECT SUM(age) FROM table_test ;
MAX()
MAX()函數(shù)可以找到一列中的最大值
SELECT MAX(age) FROM table_test ;
MIN()
MIN()函數(shù)可以找到一列中最小的值，用法與MAX()相同
AVG()
AVG()函數(shù)計(jì)算一列的平均值，用法與MAX()相同
ROUND()
設(shè)定數(shù)值到指定的精度
//設(shè)定計(jì)算的平均值精度精確到小數(shù)點(diǎn)后兩位
SELECT ROUND(AVG(age), 2) FROM table_test;
多個(gè)表操作
PRIMARY KEY
在使用CREATE TABLE時(shí)為id添加PRIMARY KEY谦去，PRIMARY KEY是一張表中每一行獨(dú)一無二的標(biāo)識(shí)慷丽，該表將id作為主鍵。通過主鍵將多個(gè)表聯(lián)系起來鳄哭。
CREATE TABLE table_test (id INTEGER PRIMARY KEY, name TEXT)
