库操作:
SHOW DATABASES;#显示所有数据库
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET UTF8; # 创建库(选择字符编码)
SHOW CREATE DATABASE db_name CHARACTER SET UTF8;# 查看新建库的方式
ALTER DATABASE db_name CHARACTER SET UTF8; # 修改数据库
USE db_name; # 使用数据库
SELECT DATABASE(); # 查看当前使用的数据库
表操作:
SHOW TABLES;
#创建表
CREATE TABLE tab_name (field type [完整性约束条件] CHECK (id>0)) CHARACTER SET UTF8;
-- CHECK 为约束，插入更新数据时会检查，保证id是否大于0
DESC tab_name; # 查看表结构
RENAME TABLE 旧表名 TO 新表名; # 重命名表
ALTER TABLE tab_name ADD 列名 type [完整约束条件],
ADD 列名 type [完整约束条件];
ALTER TABLE tab_name DROP 列名; # 删除列
ALTER TABLE tab_name MODIFY 列名 type [完整约束条件] DEFAULT;
ALTER TABLE tab_name CHANGE 旧列名 新列名 type; --改列名
DROP TABLE tab_name; --删表
#注释
/* 注释 */
--注释
SELECT 语句:
SELECT DISTINCT name FROM emp; #检索出不同的值(检索多列时若两列不完全相同,
所有列都会被检索出来)
SELECT id FROM emp LIMIT 5 OFFSET 5; #检索从第5行起后5行
ORDER BY:
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price,prod_name;
-- 仅在prod_price出现相同的值时，才会对prod_name进行排序，如果prod_price的值唯一
--则不会对prod_name排序
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2,3;
-- 先对第2个列进行排序，再对第三个列进行排序
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC,prod_name;
--有DESC的进行升序排列,默认排列为A到Z (ASC)
SELECT prod_name as name FROM Product;
--SELECT指定别名或进行运算都不会改动原表
WHERE:
SELECT vend_id,prod_name
FROM Products
WHERE vend_id <> 'DLL01';
--检索除DLL01的值
--进行不匹配，匹配检索时，null值不会出现在检索结果之中
SELECT vend_id,prod_name
FROM Products
WHERE NOT vend_id = 'DLL01';
SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;--范围检查
SELECT prod_name,prod_price
FROM Products
WHERE prod_price IS NULL;--检索空值
SELECT vend_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <=4;
-- ORDER BY 子句应在WHERE之后
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
--AND的优先级比OR高，一般要用圆括号分组
--过滤：
SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;
SELECT vend_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
--检索prod_name中以Fish开头的值（区分大小写），但不会检索出NULL的行
--还可以这样用：'%fish%'
SELECT vend_id,prod_name
FROM Products
WHERE prod_name LIKE '_inch teddy bear';
-- 下划线也是通配符，匹配一个字符
--函数：
SELECT Concat(vend_name,'(',RTRIM(vend_country),')') --合并
AS vend_title -- 去掉右边的空格
FROM Vendors ORDER BY vend_name; -- LTRIM()去掉左边的空格，TRIM()去两边
SELECT quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
SELECT cust_name,cust_contant FROM Customers
WHERE SOUNDEX(cust_contact)=SOUNDEX('Michael Green'); --拟声
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;
GROUP BY:
SELECT vend_id ,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id; -- 要在WHERE子句之后，ORDER BY子句之前
-- GROUP BY 后记得 ORDER BY
HAVING -- 过滤组：
SELECT cust_id,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING orders >=2;
SELECT vend_id,COUNT(*) AS num_prods
FROM Products
WHERE prod_price >=4
GROUP BY vend_id
HAVING num_prods >=2;
--子查询：
-- 策略：看最后一个WHERE 和各个SELECT前的检索值
SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
-- 还可通过联结进行查询
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id)
AS orders
FROM Customers
ORDER BY cust_name;
--联结表：
--内联结：
--内联结就是将两个表并在一起，将两表中的信息同时检索出来
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JION Products
ON Vendors.vend_id = Products.vend_id; --'标准写法'
SELECT prod_name,vend_name,prod_price,quantity
FROM OrderItems,Products,Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007; --联结三张表
SELECT cust_name,cust_contact
FROM Customers AS C,Orders AS O,OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'
--自联结:
SELECT C1.cust_id,C1.cust_name,C1.cust_contact
FROM Customers AS C1,Customers AS C2
WHERE C1.cust_name = C2.cust_name
AND C2.cust_contact = 'Jim Jones'
--外联结:
--包含了那些联结表中没有关联的行，叫外联结
SELECT C.cust_id,O.order_num
FROM Customers AS C LEFT OUTER JOIN Orders AS O
ON C.cust_id = O.cust_id;
-- LEFT OUTER 指左边未关联的行，RIGHT 为右边
SELECT C.cust_id,
COUNT(O.order_num) AS num_ord
FROM Customers AS C LEFT OUTER JOIN Orders AS O
ON C.cust_id = O.cust_id
GROUP BY C.cust_id;
--组合查询：
--将多条SELECT语句的查询结果按一个结果返回
--常用于不同表之间的查询
--UNION ALL 返回所有行（包括重复的)
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IN','IL','MI')
UNION
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name --ORDER BY 语句必须放在最后一个SELECT语句后
--插入数据：
INSERT INTO emp (name,department)
VALUES("远东","销售部"),("安发","财政部");
INSERT INTO emp
SET name="hzw"; # 插入表记录
INSERT INTO Customers(cust_id)
SELECT cust_id
FROM CustNew --导入数据
SELECT *
INTO CustCopy
FROM Customers;--导出数据
CREATE TABLE CustCopy AS
SELECT * FROM Customers;
--运行中创建的表CustCopy将与Customers一样
--更新与删除：
--UPDATE 修改列数据，DELETE删除行
UPDATE emp SET depart = "销售部",
age = 18
WHERE name = "ldy" # 修改表记录,UPDATE只能用SET修改
DELETE FROM emp; # 删除全部表记录
DELETE FROM emp WHERE id=1 OR id=2; # 删除两行表记录
DELETE FROM emp WHERE name ="ldy" AND DEPART = "技术部"; -- 准确删除表记录，安全模式下要加上id>0
TRUNCATE TABLE emp; --删表(先摧毁再新建一个一模一样的)
--视图：
CREATE VIEW ProductCustomers AS
SELECT cust_name,cust_contact,prod_id
FROM Customers AS C,Orders AS O,OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num;
SELECT cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
--事务处理：
START TRANSACTION;
INSERT INTO tab_name (name) values('ldy');
SAVEPOINT INSERT1;
ROLLBACK TO INSERT1; --可回退INSERT,UPDATE,DELETE
COMMIT TRANSACTION;--提交事务
--只有提交了事务sql语句才真正生效
--游标：
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL --创建游标
OPEN CURSOR CustCursor --使用游标
约束条件:
PRIMARY KEY # 非空且唯一
UNIQUE # 唯一
NOT NULL # 不为空
AUTO_INCREMENT # 自增长
类型:
数字类型: TINYINT,SMALLINT,INT
字符串类型: CHAR(2) # 只能存2个字节的字符串
VARCHAR（20） #存20字节以内的字符串
BLOB # 2进制字符串
TEXT #文本字符串
时间字符串类型:
DATA 格式: yyyy-mm-dd
TIME 格式: hh:mm:ss
YEAR格式: yyyy
DATETIME 格式: yyyy-mm-dd hh:mm:ss
--外键：
CREATE tab_name (
filed_1 INT PRIMARY KEY
FOREIGN KEY (filed_1) REFERENCES new_tab_name(filed)
) -- new_tab_name 被称为主表，tab_name 为子表
ENGINE = INNODB;--创建外键,filed_1数据类型必须与new_tab_name(filed)的一致
ALTER TABLE tab_name
ADD CONSTRAINT abc
FOREIGN KEY (filed_1)
REFERENCES new_tab_name(filed)
常用函数:
LEFT()--返回字符串左边的字符
RIGHT()--返回字符串右边的函数
LENGTH()--返回字符串的长度
LOWER(),UPPER()--小写大写
RTRIM(),LTRIM(),TRIM()--删除空格
SOUNDEX()--返回字符串的SOUNDEX值 --soundex可将任何文本转换成语音表示字母数字模式的算法
AVG()--某列平均值
COUNT()--返回某列行数
MAX()--返回某列的最大值,用于文本数据时返回最后一行
MIN()--返回某列的最小值，用于文本数据时返回最前一行，均忽略null值
SUM()--返回某列之和
COUNT(*)--对所有行进行计数
COUNT(column)--对特定列进行计数
LIMIT 5 --检索5行
OFFSET 5 -- 从第5行开始检索
DISTINCT field --返回不同的值
CURRENT_DATE() --获得系统日期