mysql学习点滴，附正确的crashcourse教材sql脚本，网上的脚本不完善，不能直接运行

安装mysql客户端：

          yum install mysql

 

安装mysql 服务器端：

          yum install mysql-server

 

          yum install mysql-devel

启动&&停止

 

数据库字符集设置

          mysql配置文件/etc/my.cnf中加入default-character-set=utf8

 

启动mysql服务：

          service mysqld start或者/etc/init.d/mysqld start

开机启动：

          chkconfig -add mysqld，查看开机启动设置是否成功chkconfig --list | grep mysql*

登录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;

这样就不会出现烦人的续行问题了