MySql笔记
1、数据库基础操作语句
链接数据库：mysql -u root -p 123456;
选择数据库：USE database_name;
显示可以数据库：SHOW DATABASES;
显示数据库所有表：SHOW TABLES;
显示表的列：SHOW COLUMNS FROM table_name;
2、检索数据
检索单个列：SELECT prod_name FROM table_name;
检索多个列：SELECT prod_id,prod_name,prod_price FROM table_name;
检索所有列：SELECT * FROM table_name;
检索不同的行：SELECT DISTINCT prod_id FROM table_name;
现在结果：SELECT prod_name FROM table_name LIMIT 5; 至多返回前5行
SELECT prod_name FROM table_name LIMIT 5,5; 返回从5行开始的5行
使用完全限定的表面：SELECT products.prod_nam FROM products;
3、排序检索数据
排序数据：SELECT prod_name FROM products ORDER BY prod_name;
按多个列排序：SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
指定排序方向：SELECT prod_name FROM products ORDER BY prod_name DESC; 默认升序排序，DESC降序排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;
4、过滤数据
使用WHERE字句：SELECT prod_name FROM products WHERE prod_price = 2.5;
WHERE字句操作符：= <>（不等于） != < > <= >= BETWEEN
范围值检查：SELECT prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;
空值检查：SELECT prod_name FROM products WHERE prod_price IS NULL;
5、数据过滤
AND操作符：SELECT prod_name FROM products WHERE prod_price < 10 AND prod_id = 100;
OR操作符：SELECT prod_name FROM products WHERE prod_price = 10 OR prod_price = 20;
IN操作符：SELECT prod_name FROM products WHERE prod_price IN (10,20,30);
NOT操作符：SELECT prod_name FROM products WHERE prod_price NOT IN (10,20,30);
6、用通配符进行过滤
百分号（%）通配符：SELECT prod_name FROM products WHERE prod_name LIKE '%jet%'; %匹配任何个数字符
下滑先（_）通配符：SELECT prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; _匹配一个字符
7、用正则表达式进行搜索
基本字符匹配：SELECT prod_name FROM products WHERE prod_name REGEXP '1000';
正则特殊字符：.（任意匹配一个字符） |（或） []
8、计算字段
拼接字段：SELECT Concat(ventor_name, '(', ventor_country, ')') FROM ventors ORDER BY ventor_name;
去掉有边的所有空格：SELECT Concat(RTrim(ventor_name), '(', RTirm(ventor_country), ')') FROM ventors ORDER BY ventor_name; （LTrim去掉左边空格，Trim去掉左右边空格）
使用别名：SELECT Concat(ventor_name, '(', ventor_country, ')') AS ventor_title FROM ventors ORDER BY ventor_name;
执行算数运算：SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005; （支持 + - * /）
9、使用数据处理函数
去除右边列值右边空格：RTirm()
去除左边列值右边空格：LTirm()
去除左右边列值右边空格：Tirm()
将文本转换为大写：Upper()
将稳步转换为小写：Lower()
返回串的长度：Length()
返回串左边的字符：Left()
返回串右边的字符：Right()
将任何文本串转为描述其语言表示的字母数字模式算法：Soundex()
日期和时间处理函数
增加一个日期（天、周等）：AddDate()
增加一个时间（时、分等）：AddTime()
返回当前日期：CurDate()
返回当前时间：CurTime()
返回日期时间的日期部分：Date()
计算连个日期之差：DateDiff()
高度灵活的日期运算函数：Date_Add()
返回一个格式化的日期或时间串：Date_Format()
返回一个日期的年数部分：Year()
返回一个日期的月数部分：Month()
返回一个日期的天数部分：Day()
对于一个日期，返回对应的星期几：DayOfWeek()
返回一个时间的小时部分：Hour()
返回一个时间的分钟部分：Minute()
返回一个时间的秒钟部分：Second()
返回一个日期时间的时间部分：Time()
返回当前日期和时间：Now()
10、汇总数据-聚集函数
返回某列的平均值：AVG()
SELECT AVG(prod_price) AS avg_price FROM products;
返回某列的行数：COUNT()
SELECT COUNT(*) AS prod_count FROM products;
返回指定列的最大值：MAX()
SELECT MAX(prod_price) AS max_price FROM products;
返回指定列的最小值：MIN()
SELECT MIN(prod_price) AS min_price FROM products;
返回某列之和：SUM()
SELECT SUM(prod_price) AS sum_price FROM products;
聚集不同值：DISTINCT
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products;
11、分组数据
创建分组：GROUP BY
SELECT ventor_id,COUNT(*) AS num_prods FROM products GROUP BY ventor_id;
过滤分组：HAVING
SELECT ventor_id,COUNT(*) AS num_prods FROM products GROUP BY ventor_id HAVING COUNT(*)>2;
SELECT字句顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
12、使用子查询
利用子查询进行过滤
SELECT cust_name FROM customers WHERE cust_id IN (
SELECT cust_id FROM orders WHERE order_num IN (
SELECT order_num FROM orderitems WHERE order_id = 'TNT2'
)
);
作为计算字段使用子查询
SELECT cust_name,(
SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id
) AS orders FROM customers ORDER BY cust_name;
13、联结表
等值联结
SELECT ventor_name,prod_name,prod_price FROM ventors,products WHERE ventors.ventor_id = products.ventor_id ORDER BY ventor_name,prod_name;
SELECT ventor_name,prod_name,prod_price FROM ventors INNER JOIN products ON ventors.ventor_id= products.ventor_id;
自联结
SELECT prod_id,prod_name FROM products WHERE vend_id = (
SELECT vend_id FROM products WHERE prod_id = 'DTNTR'
);
SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id = 'DTNTR';
外部联结
SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders IN customers.cust_id=orders.cust_id;
SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders IN customers.cust_id=orders.cust_id;
14、组合查询
创建组合查询
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);
15、插入数据
插入完整的行
INSERT INTO customers(cust_id,cust_name_cust_age) VALUES(10002,'xxx',28);
插入多行
INSERT INTO customers(cust_id,cust_name_cust_age) VALUES(10002,'xxx',28),(10003,'yyy',18);
插入检索出的数据
INSERT INTO customers(cust_id,cust_name_cust_age) SELECT cust_id,cust_name_cust_age FROM custnew;
16、更新和删除数据
UPDATE customers SET cust_name='ddd',age='40 WHERE cust_id = 100001;
DELETE FROM customers WHERE cust_id = 100001;
17、创建和操纵表
创建
CREATE TABLE customers(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_age int NOT NULL DEFAULT 10,
PRIMARY KEY(cust_id)
)ENGINE=InnoDB;
更新
给表插入一个列：ALTER TABLE vendors ADD vend_phone CHAR(20);
删除一个列：ALTER TABLE vendors DROP vend_phone;
删除表：DROP TABLE vendors;
重命名表：RENAME TABLE vendors TO vendors1;
18、使用视图
利用视图简化复杂的联结
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,pro_id FROM customers, orders, orderitems WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num;
SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='100002';
19、使用存储过程
执行存储过程
CALL productpricing(@pricelow,@prichigh,@priceaverage);
创建存储过程
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage FROM products;
END //
DELIMITER ;
删除存储过程：DROP PROCEDURE productpricing;
传入返回参数
DELIMITER //
CREATE PROCEDURE productpricing(OUT p1 DECIMAL(8,2),OUT p2 DECIMAL(8,2),OUT p3 DECIMAL(8,2))
BEGIN
SELECT MIN(prod_price)
INTO p1
FROM products;
SELECT MAX(prod_price)
INTO p2
FROM products;
SELECT AVG(prod_price)
INTO p3
FROM products;
END//
DELIMITER ;
CALL productpricing(@max,@min,@avg);
SELECT @max,@min,@avg;
传入使用参数
DELIMITER //
CREATE PROCEDURE ordertotal(IN onmuber INT, OUT ototal DECIMAL(8,2))
BEGIN
SELECT SUM(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;
END//
DELIMITER ;
CALL ordertotal(2005,@ototal);
检测存储过程：SHO CREATE PROCEDURE ordertotal;
20、使用触发器
只支持DELETE/INSERT/UPDATE语句，BEFORE和AFTER事件
创建触发器：CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'prodcuct add';
删除触发器：DROP TRIGGER newproduct;
INSERT触发器：可引用一个名为NEW的虚拟表，访问被插入的值，在BEFORE INSERT 触发器中，NEW中的值也可以被更新，对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
DELETE触发器：可引用一个名为OLD的虚拟表，访问被删除的行，OLD中的值全都是只读的，不能更新
CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
INSERT INTO archive_order(order_num,order_date,cust_id) VALUE (OLD.order_num,OLD.order_date,OLD.cust_id);
END;
由于某种原因，BEGIN END 内执行失败，DELETE本身将被放弃。
UPDATE触发器：可引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问更新的值；在BEFORE UPDATE触发器中，NEW中的值允许被修改；OLD中的值全都是只读
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);
21、管理事务处理
关键词：
事务（transaction）指一组SQL语句
回退（rollback）指撤销指定SQL语句的过程
提交（commit）指将未存储的SQL语句结果写入数据库
保留点（savepoint）指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）
事务的开始：START TRANSACTION
事务的回退：ROLLBACK
START TRANSACTION;
DELETE FROM orders;
ROLLBACK;
事务的提交：COMMIT
START TRANSACTION;
DELETE FROM orders;
COMMIT;
设置保留点：SAVEPOINT delete1;
ROLLBACK TO delete1;
22、安全管理
MySQL用户账号和信息存储在名为mysql的数据库中
USE mysql;
SELECT user FROM user;
创建用户账号
CREATE USER ben IDENTIFIED BY 'xxxxx';
重命名用户名
RENAME USER ben TO bforta;
删除用户账号
DROP USER bforta;
设置访问权限
查看用户权限：SHOW GRANTS FOR bforta;
设置权限：GRANT SELECT ON crashcourse.* TO bforta;
撤销权限：REVOKE SELECT ON crashcourse.* FROM bforta;
GRANT和REVOKE可在几个层次上控制访问权限：
整个服务器，使用GRANT ALL和REVOKE ALL
整个数据库：使用ON database.*
特定的表：使用ON database.table
权限：
ALL：除GRANT OPTION外所有权限
ALTER：使用ALTER TABLE
ALTER ROUTINE：使用ALTER PROCEDURE和DROP PROCEDURE
CREATE：使用CREATE TABLE
CREATE ROUTINE：使用CREATE PROCEDURE
CREATE TEMPORARY：使用CREATE TEMPORARY TABLE
CREATE USER：使用CREATE USER/DROP USER/RENAME USER/REVOKE ALL PRIVILEGES
CREATE VIEW：使用CREATE VIEW
DELETE：使用DELETE
DROP：使用DROP TABLE
EXECUTE：使用CALL和存储过程
FILE：使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPITION：使用GRANT和REVOKE
INDEX：使用CREATE INDEX和DROP INDEX
INSERT：使用INSERT
LOCK TABLES：使用LOCK TABLES
PROCESS：使用SHOW FULL PROCESSLIST
RELOAD：使用FLUSH
REPLICATION CLIENT：服务器位置的访问
REPLICATION SLAVE：由复制从属使用
SELECT：使用SELECT
SHOW DATABASES：使用SHOW DATABASES
SHOW VIEW：使用SHOW CREATE VIEW
SHUTDOWN：使用mysqladmin shutdown（用来关闭MySQL）
SUPER：使用CHANGE MASTER/KILL/LOGS/PURGE/MASTER/SET GLOBAL，
UPDATE：使用UPDATE
USAGE：无访问权限
