2024/2/23

子句

SELECT

select语句：为了使用select检索表的数据，必须至少给出两条信息——想选择什么，从什么地方选择

###检索单个列
select prod_name
from products;


###检索多个列
select prod_id,prod_name,prod_price
from products;

###检索所有列
select *
from products;

###检索列中不同信息
select distinct vend_id
from products;

###使用完全限定的表名(下面的例子等同于第一句)
select products.prod_name
from products;

ORDER BY

order by语句:为了排序筛选出的数据，可使用order by子句。

###升序排序
select prod_name
from products
order by prod_name;

###降序排序
select prod_name
from products
order by prod_name desc;

###排序多个列，当前一个条件相同按照后一个条件排序
select prod_name，prod_price
from products
order by prod_name,prod_price;

WHERE

where语句:数据根据where子句中指定的搜索条件进行过滤。（where子句在from子句之后给出）

###检索单个值
select prod_name,prod_price
from products
where prod_name = 'fuses';

###组合WHERE子句
select prod_id,prod_price,prod_name
from products
where vend_id = 1003 and prod_price <= 10;

###where子句中会先判断and再判断or，所以特殊情况需要加括号 
select prod_name,prod_price
from products
where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

GROUP BY

分组允许把数据分为多个逻辑组，以便对每个组进行聚集计算。

###按vend_id排序并分组数据。
select vend_id,count(*) as num_prods
from products
group by vend_id

HAVING

除了GROUP BY分组数据以外，mysql还允许对分组进行过滤。

###只保留num_prod  > 2
select vend_id,count(*) as num_prods
from products
group by vend_id
having num_prod > 2

2024/2/25

计算字段

计算字段：计算字段并不实际存在于数据库表中。是在运行时在select语句内创建的。字段基本上与列(column)的意思相同，经常互换使用。

###将两个不同列合成一个字段,vend_title就是一个计算字段，任何客户机都可以按名引用
select CONCAT(vend_name,'(',vend_country,')')  as vend_title
from vendors 
ORDER BY vend_name;

###计算字段一个常见用途在于可以对检索出的数据进行算术计算。
SELECT prod_id,quantity,item_price,(quantity*item_price) AS expanded_price 
from orderitems
where order_num = 20005;

函数

与其他大多数计算机语言一样，sql支持利用函数来处理数据。

select AVG(prod_price) as avg_price from products; 使⽤AVG函数返回所有平均值
select AVG(prod_price) AS AVG_PRICE from products where vend_id = 1003; 返回特定列的平均值
select COUNT(*) as num_cust from customers; 返回客户的总数，不管有没有值或者是为NULL
select COUNT(cust_email) AS num_cust from customers; 可以返回特定列的汇总数量
17
select MAX(prod_price) as max_price from products; 返回最⼤值
select MIN(prod_price) as min_price from products; 返回最⼩值
select SUM(prod_price) as sum_price from products; 返回总数
select SUM(item_price*quantity) AS total_price from orderitems where order_num = 20005; SUM函
数并且取个别名
select prod_price from products where vend_id = 1003;
select DISTINCT prod_price from products where vend_id = 1003;
select SUM(DISTINCT prod_price) as avg_distinct_price from products where vend_id = 1003;
DISTINCT 可以⽤来去重，对于那些重复的只统计⼀次。
select COUNT(*) AS num_items, MIN(prod_price) AS min_price, MAX(prod_price) as max_price,
AVG(prod_price) as avg_price from products; 实际上SELECT语句可根据需要包含多个聚集函数.这⾥
⽤单条SELECT语句执⾏了4个聚集计算，返回4个值（products表中物品的数⽬，产品价格的最⾼、最
低以及平均值）

联结

对于数据而言，相同数据出现多次不是一件好事。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联。

###将多张表通过有关联的键联结。
select vend_name,prod_name,prod_price from vendors,products 
where vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

###使用别名
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';

###自联结(这是⼦查询的⽅法，下⾯的是⾃联结，假如你发现某物品（其ID为DTNTR）存在问题，因此想知道⽣产该物品的供应商⽣产的其他物品是否也存在这些问题。此查询要求⾸先找到⽣产ID为DTNTR的物品的供应商，然后找出这个供应商⽣产的其他物品。)#
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';

2024.2.29

组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条select语句。MYSQL支持多个查询(多条select语句)，并将结果作为单个查询结果返回。
UNION：在每条select语句中放上关键字UNION

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)

插入数据

INSERT：它可以用来1.插入完整的行；2.插入行的一部分；3.插入多行；4.插入某些查询的结果；

###插入一整行(需要注意在插入过程中需与表结构对应)
insert INTO customers VALUES(NULL,'pep E.Lapew','100 Main','los'，XXXX)
###插入的过程中指定插入的位置与值，此时可以不用与原表结构一一对应。
insert into customers
(cust_name,cust_contact,cust_email,cust_address,cust_city,cust_state,cust_zip,cust_co
untry) values('pep E.lapew',NULL,NULL,'100 main street','los angeles','CA','90046','USA');
###插入多行
insert into
customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_contry) values('pep','100
main','los angels','CA','90046','usa'),('m.martian','42 galaxy','new york','NY','11213','USA')

更新数据

UPDATE：1.更新表中特定行，2.更新表中所有行。

update customers
set cust_email = 'elmer@fudd.com'
where cust_id = 10005;

###如果没有where则会更新表中所有行
update customers
set cust_email = 'elmer@fudd.com';

删除数据

DELETE： 1.删除特定行，2.删除所有行

###同update一样
delete from customers
where cust_id = 10006;

创建表

CREATE TABLE：创建表必须给出以下信息:1.新表的名字；2.表列的名字和定义用逗号分割。

###创建表
 create TABLE orders(
 order_num int NOT NULL AUTO_INCREMENT,//自增，不为空
 order_item int  NOT NULL,
 prod_id char(10) NOT NULL,
 item_price decimal(8,2) NOT NULL,
 PRIMARY KEY(order_num,order_item) //主键，主键唯一且每个表中必须有一个主键
  )ENGINE = InnoDB;

更新表

ALTER TABLE：在之后给出要更改的表名并选择修改方法。
RENAME TABLE:更新表名

###添加列
ALTER TABLE vendors
ADD vend_phone char(20);

###删除列(但不能删除主键)
ALTER TABLE vendors
drop column vendors;

###定义外键
alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders(order_num);

###更新表名
RENAME TABLE back_customers TO customers，
back_venders TO vendors,
back_products TO products;