登录mysql
运行:source /home/mysql/scripts/create.sql
第二步:source /home/mysql/scripts/populate.sql
好好享受crashcourse教程吧,这本书写的真不错
下面是我学习过程中的命令,没有整理,供大家参考
SQL语句命名规范,关键字大写,函数第一个字母大写,其他小写
运行crashcourse的脚本,里面没有创建数据库的文件
mysql的帮助help ***;
SQL语句命名规范,关键字大写,函数第一个字母大写,其他小写
设置变量值: SET @tmp = 20010;
sql脚本调试,打印变量信息select tmp;
命令行下变量的定义和打印
CALL ordertotal(20005, 5, @tmp);
SELECT @tmp;
常用命令:
mysqladmin -uroot -pnewpassword password 'password'
mysql -uroot -pnewpassword
show databases;
use crashcourse;
show tables;
describe orders;
show processlist;
create database abc;
drop database abc;
ORDER BY prod_price ASC|DESC;
GROUP BY, it must be at the end of sql sentence;
SELECT cust_name FROM customers where cust_email IS NULL;
SELECT prod_name, prod_price FROM products where prod_price BETWEEN 5 and 10 ORDER BY prod_price DESC;
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton' ORDER BY prod_name ASC; 1--2--3 TON 三种选择,正则表达式是中间匹配
正则表达式转义字符\\
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
匹配开始^ $
^有两种用途一种是制定起始位置,另外一种是在[]中字符集取反
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]';
LIKE 和 REGEXP区别:LIKE是全字符串匹配,REGEXP是部分匹配
SELECT prod_name FROM products WHERE prod_name LIKE 'JET%';
SELECT prod_name FROM products WHERE prod_name LIKE '_ TON ANVIL';
函数
SELECT Concat(RTrim(vend_name), '(', vend_country, ')') FROM vendors ORDER BY vend_name;
AS关键字
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vender_title FROM vendors;
SELECT order_item, quantity, item_price, quantity*item_price AS total_price FROM orderitems WHERE order_num = 20005;
mysq函数
SELECT order_num, order_date, cust_id FROM orders WHERE order_date BETWEEN '2005-09-01' AND '2005-09-30';
SELECT order_num, order_date, cust_id FROM orders WHERE Year(order_date) = 2005 and Month(order_date) = 9;
聚合
SELECT AVG(DISTINCT prod_price) FROM products;
Count(*)和Count()的区别,一个是计算行总数,一个是计算非空字段数目
SELECT count(*) FROM customers;
SELECT Count(cust_email) FROM customers;
SELECT COUNT(*) AS num_items,
MAX(prod_price),
MIN(prod_price),
AVG(prod_price) FROM products;
分组
HAVING 必须在GROUP BY 之后, ORDER BY 在最后。
SELECT SUM(quantity*item_price) AS total_price FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) > 50 ORDER BY total_price DESC;
子查询
子查询
SELECT cust_name, cust_address FROM customers WHERE cust_id IN ( SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
作为计算字段
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS num_order FROM customers GROUP BY cust_id ORDER BY num_order DESC;
表联接
不设置WHERE 的关联字段,将进行笛卡尔积运算N*M
解决上面问题
SELECT customers.* FROM customers, orderitems, orders WHERE orders.order_num = orderitems.order_num AND customers.cust_id = orders.cust_id AND orderitems.prod_id = 'tnt2';
1.自联接
列出提供DTNTR产品的的供应商提供的所有产品,第一先在第一张表中查出供应商id,然后根据id选择出提供的所有产品
SELECT p1.prod_id, p1.prod_name, p1.prod_price, p1.vend_id FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
2.自然联接
不同表之间的相同列连接(主要用于统计)。
3.外部联接
(1)列出所有客户下了多少订单进行计数,包括尚未下订单的客户。
(2)列出所有产品以及订购数量,包括没有人订购的产品。
(3)计算平均销售规模,包括那些至今尚未下订单的客户。
SELECT customers.cust_id, COUNT(orders.order_num) FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
union, union all
太简单了
sql语句的赋值用select
SELECT total INTO ototal
触发器的使用
insert,update,delete
NEW是指刚刚插入到数据库的数据表中的一个域
OLD,OLD是在delete触发器中有用的,意思是说我要删除一条数据
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @tmp_order_num;
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @tmp_order_num;
CREATE TRIGGER updatevendor BEFORE UPDATE on vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10005);
delete from orders where order_num = 20012;
update vendors set vend_state ='abcde' where vend_id = 1006;
事务处理
START TRANSACTION
ROLLBACK;
START TRANSACTION
COMMIT;
SET autocommit=1;
SAVEPOINT delete1;
ROLLBACK TO delete1;
select 20013 into @order_id;
START TRANSACTION;
2 DELETE FROM orderitems WHERE order_num = @order_id;
3 DELETE FROM orders WHERE order_num = @order_id;
4 COMMIT;
数据库的备份和恢复
mysqldump -uroot -pnewpassword --opt crashcourse | gzip > /home/mysql/crashcourse.bak
gunzip < /home/mysql/crashcourse.bak | mysql -uroot -pnewpassword crashcourse;
存储过程中很重要,否则脚本不能运行成功
DELIMITER //
×××
//
DELIMTER;
这样就不会出现烦人的续行问题了