MySQL必知必会

花了几天时间,学习了MySQL这本书的内容,总体是比较简单易学,对付基本的数据库操作是够用的。主要包含的内容有

  • 连接数据库,数据库用户创建,权限设置
  • 检索数据
  • 排序数据
  • 数据过滤,通配符数据过滤
  • 正则表达式搜索
  • 创建计算字段
  • 使用函数处理数据
  • 汇总数据
  • 分组数据
  • 子查询
  • 联结表
  • 高级联结
  • 组合查询
  • 全文本搜索
  • 插入数据
  • 更新和删除数据
  • 使用视图
  • 存储过程
  • 游标
  • 触发器
  • 事务管理

整理学习时敲的代码如下,仅供记录参考。

USE lk;
show tables;
show columns from products;
select * FROM products;
select distinct  vend_id, prod_id from products;
select prod_name from products limit 5;
select prod_name from products limit 5,6;
select prod_name from products limit 5 offset 6;-- 与上相同,表示第5行开始的6行数据
select vend_id from products order by vend_id;
select prod_name from products order by prod_name;
select prod_id,prod_name,prod_price from products order by prod_price desc,prod_name;
select prod_price from products order by  prod_price DESC LIMIT 1; 
select prod_name,prod_price from products where prod_price >2.5 order by prod_pr;
select prod_name,prod_price from products where prod_name='fuses'; -- 不区分大小写;
select prod_name ,prod_price from products where prod_price between 5 and 30;
select prod_id,prod_name,prod_price from products where prod_price <=10 and vend_id=1001;
select prod_id,prod_name,prod_price from products where vend_id=1002 OR vend_id=1003 order by prod_price DESC;
select prod_id,prod_name,prod_price from products where (vend_id=1002 OR vend_id =1003) AND prod_price>=10 order by prod_price DESC;
select prod_id,prod_name,prod_price from products where vend_id in(1002,1003) and prod_price <=10;
select prod_id,prod_name,prod_price from products where vend_id not in(1002,1003) order by prod_name;
-- 0629
USE lk;
##########################################
#time:0629
#cont:通配符
##########################################
select prod_id,prod_name,prod_price from products where prod_name LIKE 'JET%';
select prod_id,prod_name,prod_price from products where prod_name LIKE '%anvil%';
select prod_id,prod_name,prod_price from products where prod_name LIKE 'anvil%';
select prod_id,prod_name,prod_price from products where prod_name LIKE '%100%';

#正则表达式
select prod_id,prod_name,prod_price from products where prod_name REGEXP '1' ORDER BY PROD_NAME;
select prod_name from products where prod_name REGEXP '.000' order by prod_name;
select prod_name from products where prod_name REGEXP '1000|2000|3000' order by prod_name;
select prod_name from products where prod_name REGEXP '[123] TON' order by prod_name;

select prod_name from products where prod_name REGEXP '\\([0-9] sticks?\\)' order by prod_name;
select prod_name from products where prod_name REGEXP '[[:digit:]]{4}';# 如果只有一个中括号,啥也不返回。
select prod_name from products where prod_name REGEXP '[0-9][0-9][0-9][0-9]';# SAME_name AS THE ONE BEFORE 
select prod_name from products where prod_name REGEXP 'stick' order by prod_name;
select prod_name from products where prod_name REGEXP '^[0-9\\.]' order by prod_name;
select 'hello' REGEXP 'l*';#return 1
##10创建计算字段
use vendors;
select * from vendors;
select concat(vend_name,'(',vend_country,')') from vendors order by vend_name;

select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from vendors order by vend_name;
-- 起别名
select concat(rtrim(vend_name),'(',rtrim(vend_country),')') as vend_title from vendors order by vend_name;
select prod_id,quantity,item_price from orderitems where order_num=20005;
select  prod_id,
        quantity,
        item_price,
        quantity*item_price as expanded_prices
        from orderitems
        where order_num=20005;
select now();
select trim(' gooooogle  ');

####################################
#time:0629xiawu
#内容:使用函数处理数据
#####################
select vend_name,upper(vend_name) as vend_name_upper from vendors order by vend_name;
select vend_name,length(vend_name) as vend_name_upper from vendors order by vend_name;

select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y LIE');


#日期处理函数
select curdate();
SELECT NOW();
select date(NOW());
SELECT cust_id,order_date,order_num from orders where date(order_date)='2005-09-01';
select cust_id,order_date,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30';
select cust_id,order_date,order_num from orders where  year(order_date) and month(order_date) ='9';-- 不加引号也可以.

################################
#12章 ,聚集函数。
####################
select AVG(prod_price) as avg_price from products;
select AVG(prod_price) as avg_price from products where vend_id=1003;
select count(*) as num_cust from customers;
select count(cust_email) as num_cust from customers;
select max(prod_price) as max_price from products;
select min(prod_price) as min_price from products;
select sum(quantity) as items_ordered from orderitems where order_num=20005;
select quantity ,item_price from orderitems;
select sum(quantity*item_price) as total_price,sum(quantity) as quan from orderitems;-- where order_num=20005;
select AVG(DISTINCT prod_price) as avg_price from products where vend_id=1003;
select AVG(prod_price) as avg_price from products where vend_id=1003;

select count(*) as all_count,
        min(prod_price) as min_price,
        max(prod_price) as max_price,
        avg(prod_price) as avg_price
        from products;

#####################################################
#13章,分组数据
#################################################@###

select vend_id,count(*) as num_prods from products group by vend_id;
select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;
######
#列出具有2个以上,价格大于10的产品的供应商。
######
select vend_id,count(*) as num_prods 
        from products 
        where prod_price >=10
        group by vend_id
        having count(*)>=2;

select order_num,sum(quantity*item_price) as ordertotal
        from orderitems
        group by order_num
        having sum(quantity*item_price) >=50
        order by ordertotal;


##########################################
#time:0630
#cont:子查询 chapter 14
##########################################
use lk;
select order_num from orderitems where prod_id='TNT2';
select cust_id from orders where order_num in (20005,20007);
-- combaine the two into one
select cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2');
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 ='TNT2'));

select count(*) as orders from orders where cust_id='10001';
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 count(*) as orderss  from orders where orders.cust_id=customers.cust_id; -- error,unknown column ´customer.cust_id;´
select count(*) as orderss  from orders where orders.cust_id in (select customers.cust_id from customers);
select cust_id from orders; 
select distinct customers.cust_id from customers;

##############################################
#chapter 15
#联结
###########################################
select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id order by vend_name,prod_name;

select vend_name,prod_name,prod_price 
       from vendors inner join products
       on vendors.vend_id=products.vend_id;
#####
#显示编号为20005的产品
select prod_name,vend_name,prod_price,quantity
        from products,vendors,orderitems
        where products.vend_id=vendors.vend_id
        and orderitems.prod_id=products.prod_id
        and order_num=20005;

#####################################
#chapter 16
#cont:advanced join
#####################################
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='TNT2';

select prod_id,prod_name
        from products
        where vend_id=(select vend_id from products where prod_id='DTNTR');

select prod_id,prod_name
        from products as p1,products as p2
        where p1.vend_id=products.vend_id
        and products.prod_id='DTNTR';
select c.*,o.order_num,o.order_date
        ,oi.prod_id,oi.quantity,oi.item_price
        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='FB';
select customers.cust_id,orders.order_num
        from customers inner join orders 
        on customers.cust_id=orders.cust_id;

select customers.cust_id,orders.order_num
        from customers left outer join orders
        on customers.cust_id=orders.cust_id;

select customers.cust_name,
        customers.cust_id,
        count(orders.order_num) as num_ord
        from customers inner join orders
        on customers.cust_id=orders.cust_id
        group by customers.cust_id;

######################################
##chapter 17
##union select
#######################################
use lk;
select prod_id,prod_name,prod_price
        from products
        where prod_price<=5
        order by prod_price DESC;

select prod_id,prod_name,prod_price
    from products
    where vend_id in(1001,1002);

select prod_id,prod_name,prod_price
        from products
        where prod_price<=5 union
        select prod_id,prod_name,prod_price
    from products
    where vend_id in(1001,1002);

##################################
#chapter 18
#全文本搜索
###################3#####################
use lk;
select note_text from productnotes
     where match(note_text) against('rabbit');

select note_text,
        match(note_text) against('rabbit') as rank
        from productnotes;

select note_text
       from productnotes
       where match(note_text) against('anvils');
select note_text
        from productnotes
        where match(note_text) against('anvils' with query expansion);


select note_text from productnotes 
       where match(note_text) against('heavy -rope*' in boolean mode);
select note_text from productnotes
        where match(note_text) against('+rabbit +bait' in boolean mode);
select note_text from productnotes 
        where match(note_text) against('rabbit bait' in boolean mode);

select note_text from productnotes 
        where match(note_text) against('"rabbit bait"' in boolean mode);

select note_text from productnotes 
        where match(note_text) against('>rabbit <bait' in boolean mode); #increase the rank of rabbit ,decrease the rank of bait;

select note_text from productnotes 
        where match(note_text) against('+safe +(<combination)' in boolean mode);

#################################################
#chapter 19
#insert
#################################################
#缺点是不安全,必须按照表中定义列的顺序插入数据,如果表的结构发生改变,将会和预计的结果不一致甚至失败。
#有效的方法是插入列名。
insert into customers
values(
    NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA',
    NULL,
    NULL
    );
select * from customers;

insert into customers(
    CUST_NAME,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
values(
    'Pep E. LaPewG',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA',
    NULL,
    NULL);
USE lk;
insert into customers(
    CUST_NAME,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
values(
    'Pep E. LaPewG',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'CHN',
    NULL,
    NULL),(
    'M.Martian',
    '42 Galaxy Way',
    'New Yotk',
    'NY',
    '11213',
    'USA',
    NULL,
    NULL);

############################################
#CHAPTER 20
#update and delete
#############################################
update customers set cust_email='274857347@qq.com' where cust_id=10005;
delete from customers where cust_id=10006;
create table  if not exists orders -- if not exists 创建表时做检查.
(   
    order_num   int      NOT NULL auto_increment,
    order_state datetime NOT NULL,
    cust_id     int      NOT NULL,
    PRIMARY KEY(order_num)
)ENGINE = InnoDB;-- 报警告,提示已经存在表orders。

ALTER TABLE  vendors add vend_phone char(20); -- 增加一列
ALTER TABLE vendors drop vend_phone;    


ALTER TABLE orderitems add constraint fk_orderitems_orders foreign key(order_num) 
    references orders(order_num);

DROP TABLE customers;
RENAME TABLE customers2 to customers;
############################################
#chapter 22
#视图
##############################################
select cust_name,cust_contact
    from customers,orders,orderitems
    where customers.cust_id=orders.cust_id
    and orderitems.order_num=orders.order_num
    and prod_id='TNT2';


#########################################################
#chapter 22
#view
##########################################################
USE lk;
#####返回已订购任意商品的所有用户的列表
CREATE VIEW productcustomers AS
    SELECT cust_name,cust_contact,prod_id
    FROM customers,orders,orderitems
    WHERE customers.cust_id=orders.cust_id
    AND orderitems.order_num =orders.order_num;
SELECT * FROM productcustomers;######返回已订购任意商品的所有用户的列表
SELECT cust_name,cust_contact 
    FROM productcustomers WHERE prod_id='TNT2';#返回订购了产品TNT2的客户。
############################################
#chapter 23
#存储过程
##########################################
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
    SELECT avg(prod_price) as priceaverage
    FROM products;
END //
DELIMITER ;

CALL productpricing(); -- 调用存储过程
DROP PROCEDURE IF EXISTS productpricing;

DELIMITER //
CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
    )
    BEGIN
    SELECT Min(prod_price) INTO pl FROM products;
    SELECT Max(prod_price) INTO ph FROM products;
    SELECT Avg(prod_price) INTO pa FROM products;
    END //
DELIMITER ;

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage
                    );
SELECT @priceaverage;

DELIMITER //
CREATE procedure ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
    )
    BEGIN
    SELECT Sum(item_price*quantity) INTO ototal FROM orderitems WHERE order_num=onumber;
    END //
DELIMITER ;

CALL ordertotal(20005,@total);
SELECT @total;

DELIMITER //
CREATE PROCEDURE ordertotal2(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
    )COMMENT 'Obtain order total,optionally adding tax'
    BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare taxrate INDEFUALT 6;
    DECLARE taxrate INT DEFAULT 6;
    SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
    IF taxable THEN
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    SELECT total INTO ototal;
    END //
DELIMITER ;
CALL ordertotal2(20005,0,@total);
select @total;

CALL ordertotal2(20005,1,@total);
select @total;

SHOW CREATE PROCEDURE ordertotal2;
SHOW PROCEDURE STATUS;


#######################################################
#chapter 24
#使用游标
#######################################################
DELIMITER //
CREATE PROCEDURE processorders2()
    BEGIN
     DECLARE ordernumbers CURSOR
     FOR
     SELECT order_num FROM orders;
     OPEN ordernumbers;
     CLOSE ordernumbers;
     END //
DELIMITER ;

-- OPEN  ordernumbers;

###############################################
#chapter 25
#触发器#   time:0703
##################################################
CREATE TRIGGER newproduct AFTER INSERT ON products 
    FOR EACH ROW SELECT 'product added';

CREATE TRIGGER neworder2 AFTER INSERT ON orders
    FOR EACH ROW SELECT NEW.order_num;

INSERT INTO orders(order_date,cust_id) VALUES(NOW(),10002);

############################################################
#chapter 26
#事务处理
###########################################################
CREATE TABLE ordertotal
    (
        order_num INT ,
        total DECIMAL(8,2)
    );
INSERT INTO ordertotal(order_num,total) VALUES(3,6);

SELECT * FROM ordertotal;

START TRANSACTION;
DELETE FROM ordertotal;
SELECT * FROM ordertotal;
ROLLBACK;

SELECT * FROM ordertotal;

-- START TRANSACTION;
-- DELIMITER //
-- DELETE FROM orderitems WHERE order_num=20010;
-- DELETE FROM orders WHERE order_num=20010;
-- COMMIT //
-- DELIMITER ;
use mysql;
select * from user;

CREATE USER ben IDENTIFIED BY 'p@$$wOrd';
select * from user;
rename user ben to bforta;
select * from user;
DROP USER bforta;

SHOW GRANTS FOR bforta;

GRANT SELECT ON crashcourse.* TO bforta;
SHOW GRANTS FOR bforta;

REVOKE SELECT ON crashcourse.* from bforta;
SHOW GRANTS FOR bforta;

SET PASSWORD FOR bforta = password('n3w p@$$wOrd');
SET PASSWORD = Password('nihao p@$$wOrd');
SET PASSWORD =Password('nihao');
use mysql;
select * from user;

use lk;
select @@tx_isolation;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值