mysql查看主键别名_mysql简单查询

过滤重复的数据

select distinct 字段1,字段2.. from 表名;

注意:

distinct关键字之后只有一个字段,那么就会过滤掉这个字段中重复的数据

distinct关键字之后如果有多个字段,那么多个字段必须同时满足才会过滤掉

-- 查询商品价格

SELECT price FROM product;

-- 查询商品价格,过滤掉重复的价格

SELECT DISTINCT price FROM product;

-- 查询商品名称和价格,过滤调用名称和价格同时重复的数据

SELECT DISTINCT pname,price FROM product;

1.别名只是对查询的结果临时起一个名字,不会改变表中原有的名字

2.别名中如果有特殊符号或者空格,那么必须使用引号包裹起来

给表起别名:多表查询时使用,简化表的名字

SELECT * FROM product AS p;

SELECT * FROM product p;

给表中的字段起别名

SELECT pname AS 商品名称,price AS 商品价格 FROM product;

SELECT pname 商品名称,price 商品价格 FROM product;

SELECT pname 商品名称,price '商品 价格' FROM product;

查询语句中,可以直接进行数据计算

查询商品名称和商品价格,把商品价格在查询的时候增加10000

SELECT pname,price+10000 FROM product;

SELECT pid+price FROM product;

模糊查询:使用关键字like　　%:多个任意字符　　_:1个任意字符

查询商品名称中以雷神开头的所有商品信息

SELECT * FROM product WHERE pname LIKE '雷神%';

查询有分类的商品(商品分类不是null的)

SELECT * FROM product WHERE category_id IS NOT NULL;-- 是不空

SELECT * FROM product WHERE NOT(category_id IS NULL);

排序查询语句:对查询的结果进行排序

使用关键字: order by [ASC]|DESC

格式:

select *|字段 from 表名 [where 条件] order by 被排序字段 [ASC]|DESC;

ASC:升序,不写默认

DESC:降序

注意:

无论sql语句多么复杂,排序语句必须写在sql语句的末尾

使用价格排序(升序)

SELECT * FROM product ORDER BY price ASC;

使用价格排序(降序)

SELECT * FROM product ORDER BY price DESC;

显示商品的价格(去重复)，并排序(降序)

SELECT DISTINCT price FROM product ORDER BY price DESC;

在价格排序(降序)的基础上，以分类排序(降序)

SELECT * FROM product ORDER BY price DESC,category_id DESC;

聚合查询语句:对某列的数据进行查询,结果返回一个单一的值,会忽略null值

- count：统计指定列不为NULL的记录行数；

- sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

- max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

- min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

- avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

格式:

select count(字段|*),sum(字段),max(字段),min(字段),avg(字段) from 表名;

查询商品的总条数

SELECT COUNT(pid) FROM product; -- 13

SELECT COUNT(pname) FROM product; -- 13

SELECT COUNT(category_id) FROM product;-- 12

SELECT COUNT(*) FROM product; -- 13

查询价格大于200商品的总条数

SELECT COUNT(*) FROM product WHERE price >200; -- 7

查询商品的总金额

SELECT SUM(price) FROM product; -- 17503

查询分类为'c001'的所有商品价格的总和

SELECT SUM(price) FROM product WHERE category_id='c001'; -- 1300

查询商品的平均价格

SELECT AVG(price) FROM product; -- 1346.3846153846155

查询商品的最大价格和最小价格

SELECT MAX(price),MIN(price) FROM product;

sum,avg如果指定列类型不是数值类型，那么计算结果为0

SELECT SUM(pname),AVG(pname) FROM product; -- 0,0

分组查询语句:

格式:

select 被分组的字段,(求和,平均值,个数,最大,最小) from 表名 [where 条件] group by 被分组的字段 [having 条件]

注意:被分组的字段,一般都写在select语句的后边,作为查询条件,方便查看结果

-- 按照商品类别分组,统计各个分组商品的个数

SELECT category_id,COUNT(*) FROM product GROUP BY category_id;

-- 按照商品类别分组,统计每组商品的平均价格

SELECT category_id,AVG(price) FROM product GROUP BY category_id;

where:用于分组前进行条件过滤

having:用于分组后,对分组的数据进行条件过滤

-- 按照商品类别分组,统计各个分组商品的个数,只查询每组商品价格大于200的商品

-- 先where过滤,在分组,在统计

SELECT category_id,COUNT(*) FROM product WHERE price>200 GROUP BY category_id;

-- 按照商品类别分组,统计各个分组商品的个数,且只显示统计个数等于3的信息

-- 先where过滤,在分组,在统计,在having过滤

SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*)=3;

分页查询语句(了解)

使用关键字:limit

格式:

只要前n条数据

select *|字段 from 表名 limit n;

分页查询

select *|字段 from 表名 limit m,n;

m:每页开始行的索引,变化的

n:每页显示的数量,不变的

注意:

数据库行的索引从0开始,列的索引从1开始

-- 只要前6条数据

SELECT * FROM product LIMIT 6;

-- 分页查询,每页显示5条,查询第1页

SELECT * FROM product LIMIT 0,5;

-- 分页查询,每页显示5条,查询第2页

SELECT * FROM product LIMIT 5,5;

-- 分页查询,每页显示5条,查询第3页

SELECT * FROM product LIMIT 10,5;

1对多关系

建表原则:从表使用主表的主键作为外键

主表:分类表category

从表:商品表products

-- 创建主表:分类表category 字段:分类主键,分类名称

CREATE TABLE category(

-- 分类主键

cid INT PRIMARY KEY AUTO_INCREMENT,

-- 分类名称

cname VARCHAR(20)

);

-- 分类表添加数据

INSERT INTO category(cname) VALUES('服装'),('家电'),('化妆品'),('零食');

-- 创建从表:商品表products 字段:商品主键,商品名称,商品价格,商品分类

CREATE TABLE products(

-- 商品主键

pid INT PRIMARY KEY AUTO_INCREMENT,

-- 商品名称

pname VARCHAR(20),

-- 商品价格

price DOUBLE,

-- 商品分类

category_cid INT

);

-- 商品表添加数据

INSERT INTO products(pname,price,category_cid) VALUES('羽绒服',300,1),('秋裤',20,1);

INSERT INTO products(pname,price,category_cid) VALUES('空调',998,2),('手电筒',10,2);

INSERT INTO products VALUES(NULL,'香奈儿香水',800,3),(NULL,'大宝',8,3);

添加外键约束:

格式:使用修改表结构,添加列

alter table 从表名 add [constraint 外键名称] foreign key(从表中的外键字段) references 主表名(主键);

foreign:外部的

key:关键字

作用:保证数据的准确性和完整性

主表中有的数据,从表可以有,可以没有

主表中没有的数据,从表中也不能有

删除主表中的数据,必须保证从表没有使用

-- 给从表商品表中的字段category_cid添加外键约束,使用主表分类表的主键作为外键

ALTER TABLE products ADD CONSTRAINT products_fk FOREIGN KEY(category_cid) REFERENCES category(cid);

-- 删除主表中的数据,必须保证从表没有使用

-- 删除主表中cid为3的数据,从表正在使用,不能删除

-- 先删除从表中使用了cid为3的数据

DELETE FROM products WHERE category_cid=3;

-- 在删除主表中cid为3的数据,从表没有使用就可以删除

DELETE FROM category WHERE cid =3;

删除外键约束:使用修改表结构,删除列

alter table 从表名 drop foreign key 外键名称;　　ALTER TABLE products DROP FOREIGN KEY products_fk;

商品表和订单表的多对多关系

建表原则:创建一张中间表,使用两个主表的主键作为外键

主表: 商品表products 订单表orders

中间表:products_orders

-- 创建主表订单表orders 字段:订单主键,订单总金额

CREATE TABLE orders(

-- 订单主键

oid INT PRIMARY KEY AUTO_INCREMENT,

-- 订单总金额

totalPrice DOUBLE

);

-- 订单表添加数据

INSERT INTO orders(totalPrice) VALUES(2098),(38),(328);

-- 创建中间表:products_orders

CREATE TABLE products_orders(

-- 商品主键(外键)

products_pid INT,

-- 订单主键(外键)

orders_oid INT,

-- 添加products_pid字段的外键约束,使用products表的pid作为外键

FOREIGN KEY(products_pid) REFERENCES products(pid),

-- 添加orders_oid字段的外键约束,使用orders表中的oid作为外键

FOREIGN KEY(orders_oid) REFERENCES orders(oid)

);

-- 中间表添加两个主表都有的数据

INSERT INTO products_orders VALUES(1,1),(3,1),(5,1);

INSERT INTO products_orders VALUES(2,2),(4,2),(6,2);

INSERT INTO products_orders VALUES(1,3),(2,3),(6,3);

-- 先删除中间表中使用了oid为3的数据

DELETE FROM products_orders WHERE orders_oid=3;

-- 再删除主表订单表中oid为3的数,中间表没有使用,可以删除

DELETE FROM orders WHERE oid=3;