#库操作： 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 UTF-8]; # 修改数据库 USE db_name; # 使用数据库 SELECT DATABASE(); # 查看当前使用的数据库 #表操作： SHOW TABLES; CREATE TABLE tab_name （field type [完整性约束条件] CHECK (id>0)) [CHARACTER SET UTF-8]; #创建表 -- 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() --获得系统日期
