- 博客(16)
- 收藏
- 关注
RSS订阅原创 SQL 必知必会第十七课 创建和操纵表
SQL 必知必会第十七课 创建和操纵表SET SQL_SAFE_UPDATES = 0; Alter table Vendorsadd vend_phone CHAR(20);Alter table vendorsdrop column vend_phone;## ChallengesAlter table Vendorsadd vend_web longtext ; -- 1update Vendorsset vend_web = 'Http://xxxxx'; -- 2
2020-12-18 16:07:44
83
1
原创 SQL 必知必会第十六课 更新和删除数据
SQL 必知必会第十六课 更新和删除数据## 更新数据SET SQL_SAFE_UPDATES = 0; -- 首先关闭安全模式-- 更新一行update Customersset cust_email = '[email protected]'where cust_id = 1000000005;update customersset cust_contact = 'Sam Roberts', cust_email = '[email protected]'where cus
2020-12-04 11:03:43
99
原创 SQL 必知必会第十五课 插入数据
SQL 必知必会第十五课 插入数据## 插入完整的行insert into customersvalues(1000000006,'Toy Land','123 Any Street','New York','NY','11111','USA', null, null); -- 繁琐却更加保险的方法insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_countr
2020-12-01 14:37:40
66
原创 SQL 必知必会第十四课 组合查询
SQL 必知必会第十四课 组合查询select cust_name, cust_contact, cust_emailfrom customerswhere cust_state in('IL','IN','MI');select cust_name, cust_contact, cust_emailfrom customerswhere cust_name = 'Fun4All';select cust_name, cust_contact, cust_email,cust_state
2020-11-27 08:48:50
74
原创 SQL 必知必会第十三课 创建高级联结
SQL 必知必会第十三课 创建高级联结##使用表别名select concat(vend_name,'(',vend_country,')') as vend_titlefrom vendorsorder by vend_name;select cust_name, cust_contactfrom customers as C, orders as O, OrderItems as OIwhere C.cust_id = O.cust_idand OI.order_num = O.ord
2020-11-26 10:12:16
109
原创 SQL 必知必会第十二课 联结表
SQL 必知必会第十二课 联结表## 创建链接select vend_name, prod_name, Prod_pricefrom vendors, productswhere vendors.vend_id = Products.vend_id; -- 此处的where就是作为联结的工具，将两个表配对select vend_name, prod_name, Prod_pricefrom vendorsinner join products on vendors.vend_id = pro
2020-11-24 08:51:05
56
原创 SQL 必知必会第十一课 使用子查询
SQL 必知必会第十一课 使用子查询select order_num from orderitems where prod_id = 'RGAN01';select cust_id from orders where order_num in (20007,20008);##结合两句查询select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'RGAN01');
2020-11-23 08:01:24
80
原创 SQL 必知必会第十课 分组数据
SQL 必知必会第十课 分组数据select count(*) as num_prods from Products where vend_id = 'DLL01';## 创建分组select vend_id,count(*) as num_prods from products group by vend_id;## 过滤分组select cust_id,count(*) from orders group by cust_id having count(*) >=2 ;select
2020-11-22 13:44:39
112
原创 SQL 必知必会第九课 汇总数据
SQL 必知必会第九课 汇总数据# 平均函数用法select avg(prod_price) as avg_price from products;select avg(prod_price) as avg_price from products where vend_id = 'DLL01';# 计数函数用法select count(*) as num_cust from customers; -- 对customer的行进行计数select count(cust_email) as n
2020-11-21 21:42:07
67
原创 SQL 必知必会第八课 使用函数处理数据
SQL 必知必会第八课 使用函数处理数据select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name;select cust_name, cust_contact from customers where cust_contact = 'Michael Green';select cust_name, cust_contact from customers where soundex(cu
2020-11-19 14:00:14
88
原创 SQL 必知必会第七课 创建计算字段
SQL 必知必会第七课 创建计算字段select concat(vend_name, '( ',vend_country,' )') from Vendors order by vend_name;## 拼接供应商名，左括号，供应商国家，右括号 select concat(vend_name, '( ',vend_country,' )') as vend_title from Vendors order by vend_name;select prod_id,quantity, item_pr
2020-11-19 07:38:19
60
原创 SQL 必知必会第六课 用通配符进行过滤
SQL 必知必会第六课 用通配符进行过滤select prod_id, prod_name from Products where prod_name like 'fish%'; ## %告诉DBMS接受fish之后的任意字符select prod_id, prod_name from Products where prod_name like '%bean bag%';## 匹配任何位置上包含文本bean bag的值select prod_name from Products where p
2020-11-18 13:50:43
84
1
原创 SQL 必知必会第五课 高级数据过滤
SQL 必知必会第五课 高级数据过滤select prod_id, prod_price, prod_name from Products where vend_id = 'DLL01' and prod_price <= 4;select prod_id, prod_price, prod_name from Products where vend_id = 'DLL01' or vend_id = 'BRS01';select prod_name, prod_price from Pro
2020-11-18 09:56:16
69
原创 SQL 必知必会第四课 过滤数据
SQL 必知必会第四课 过滤数据select prod_name, prod_price From Products Where prod_price = 3.49; -- where是指定搜索条件进行过滤select prod_name, prod_price From Products Where prod_price < 10;select prod_name, prod_price From Products Where prod_price <= 10;select ven
2020-11-18 08:44:08
263
2
原创 SQL 必知必会第三课 排序检索数据
SQL 必知必会第三课 排序检索数据Select prod_name From Products; -- 上一节课学的选出列Select prod_name From Products Order By prod_name; -- 以字母顺序排列数据Select prod_id, prod_price, prod_name From Products Order By prod_price, prod_name; ## 先对price进行排序，随和相同p用名字进行排序Select prod_id,
2020-11-17 13:43:08
50
原创 SQL 必知必会第二课 检索数据
SQL 必知必会第二课 检索数据`学习select语句检索一个或多个列Select prod_name From products; -- 查询列Select prod_id, prod_name, prod_price From products; -- 查询多个列Select * From Products; -- 查询所有的列 *是通配符Select vend_id From Products;Select Distinct vend_id From Products; -- 只返回不同的
2020-11-16 08:49:56
62
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人