单表查询

1、排序格式

通过order by语句，可以将查询出的结果进行排序。防止在select语句的最后

语法 select * from 表名 where 条件 order by 排查字段 ASC|DESC； ASC 升序（默认） DESC 降序

例1：使用价格排序（降序）

  1. select * from d_product order by price desc

例2：显示商品的价格（去重复），并排序（降序）

  1. select distinct price from d_product order by price desc

例3：模糊查询之后，降序

  1. select pname,price from d_product where panem like "%o%" order by price desc

2、聚合

使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
5个常用的聚合函数：

  • count：统计指定列不为NULL的记录行数
  • sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果都为0
  • max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算（即按字典排序）
  • min：计算指定列的最小值，如法国指定列是字符串类型，那么使用字符串排序运行（即按字典排序）
  • avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
    1. /*聚合函数*/
    2. #1、count(列名)统计个数
    3. #统计价格在800以上一共有多少商品
    4. SELECT COUNT(*) AS total FROM d_product WHERE price>=800
    5. #统计一共有几种价格
    6. SELECT COUNT(DISTINCT price)FROM d_product
    7. #统计价格的何止
    8. SELECT SUM(price) FROM d_product
    9. #获取价格最大的商品的价格
    10. SELECT MAX(price) FROM d_product
    11. #获取价格最小的商品的价格
    12. SELECT MIN(price) FROM d_product
    13. #获取所有商品的平均价格（返回的是double类型）
    14. SELECT AVG(price) FROM d_product

3、分组

分组是指视同group by 子句对查询信息进行分组
语法格式

  1. select 字段1，字段2.... from 表名 group by 分组字段 having 分组条件

分组操作中的having子语句，是用于在分组后，对数据进行过滤，作用类似于where条件

having与where的区别

  • having是在分组后对数据进行过滤
  • where是在分组前对数据进行过滤
  • having后面可以使用分组函数（统计函数）
  • where后面不可以使用分组函数


  1. /*分组 最好和聚合函数结合使用*/
  2. #按照价格分组
  3. SELECT price FROM d_product GROUP BY price
  4. #与下面结果相同
  5. SELECT DISTINCT price FROM d_product ORDER BY price ASC
  7. #按照类别id分组 该类名的商品个数 平均价格 类别
  8. SELECT COUNT(*),AVG(price),category_id FROM d_product GROUP BY category_id
  10. #先筛选后分组 对非空的category_id进行分组域 注意where的位置
  11. SELECT COUNT(*),AVG(price),category_id FROM d_product WHERE category_id IS NOT NULL GROUP BY category_id
  13. #先分组后筛选  筛选出平均价格在500以上的商品分组
  14. SELECT COUNT(*),AVG(price),category_id FROM d_product GROUP BY category_id HAVING AVG(price)>500

约束

1、添加主键约束

primary key 约束唯一标识数据库表中的每条记录

主键列不能包含NULL值

每个表都应该有一个主键，并且每个表只能有一个主键

  • 添加主键方法一：创建表时，在此字段描述出，声明指定字段为主键

    1. create table Persion(
    2. id int primary key,
    3. name varchar(30)
    4. )

  • 添加主键方法二：创建表时，在constraint约束区域，声明指定字段为主键

    • 语法格式
  1. 格式：【constraint 主键别名】 primary key（字段名）
  2. 注意：
  3. 1、关键字constraint 可以省略 ，如果需要为主键命名，constraint不能省略，主键名一般没用
  4. 2、字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们成为联合主键
  1. create table Persion(
  2. id int,
  3. name varchar(30),
  4. constraint pk_PersionID primary key(id)
  5. )
  1. CREATE TABLE Persons
  2. (
  3. FirstName varchar(255),
  4. LastName varchar(255),
  5. Address varchar(255),
  6. City varchar(255),
  7. PRIMARY KEY (FirstName,LastName)
  8. )
  • 添加主键方法三：创建表之后，通过修改表结构，声明指定字段为主键
    1. alter table Persion add [constaint 名称] primary key （字段列表）
    ```sql CREATE TABLE Persons ( FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255) ) ALTER TABLE Persons ADD PRIMARY KEY (FirstName,LastName)
  1. <a name="8nwm9"></a>
  2. ## 2、删除主键
  3. ```sql
  4. alter table Persions drop primary key

3、自动增长

  1. 使用auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键（一般为主键）
  1. create table Persion1(
  2. id int primary key AUTO_INCREMENT,
  3. name varchar(30)
  4. )

面试：delete和truncate的区别

  • delete删除表中得的数据，但不充值auto_increment记录数
  • truncate删除表中的数据，auto_increment记录数将重置。是因为truncate先删除表，然后重新创建一个一模一样的新表

扩展：磨人的，auto_increment的开始值是1，如果希望修改起始值，可以使用：

  1. alter table Persion auto_increment=100

4、外键约束

通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多的关系。即一对多，是相对于主表而言的，而多对一是相对于从表而言的。

外键特点

  • 从表外键的值是对主表主键的引用
  • 从表外键类型，必须与主表主键类型一致

外键约束语法

添加外键：alter table 从表 add constraint【外键名称】foreign key （从表外键字段名）references 主表（主表的主键）

删除外键：alter table 从表 drop foreign key 外键名称

使用外键的目的：保证数据的完整性

代码

  1. #创建商品表：
  2. CREATE TABLE d_product(
  3.     pid INT PRIMARY KEY,
  4.     pname VARCHAR(20),
  5.     price DOUBLE,
  6.     category_id VARCHAR(32)
  7. );
  8. INSERT INTO `d_product` VALUES ('1', '苹果', '5000', 'c001');
  9. INSERT INTO `d_product` VALUES ('2', 'vivo', '3000', 'c001');
  10. INSERT INTO `d_product` VALUES ('3', '华为', '5000', 'c001');
  11. INSERT INTO `d_product` VALUES ('4', 'oppo', '1000', 'c001');
  12. INSERT INTO `d_product` VALUES ('5', '真维斯', '200', 'c002');
  13. INSERT INTO `d_product` VALUES ('6', 'only', '440', 'c002');
  14. INSERT INTO `d_product` VALUES ('7', '圣迪奥', '800', 'c002');
  15. INSERT INTO `d_product` VALUES ('8', 'vero moda', '800', 'c003');
  16. INSERT INTO `d_product` VALUES ('9', '相宜本草', '200', 'c003');
  17. INSERT INTO `d_product` VALUES ('10', '卫龙辣条', '5', NULL);
  18. INSERT INTO `d_product` VALUES ('11', '好想你枣', '56', NULL);
  19. INSERT INTO `d_product` VALUES ('12', '香飘飘奶茶', '3', NULL);
  20. INSERT INTO `d_product` VALUES ('13', '小浣熊干脆面', '1', NULL);
  22. SELECT*FROM d_product
  24. SELECT pname, price FROM d_product WHERE price>=56 ORDER BY price DESC
  26. SELECT COUNT(*) total FROM d_product WHERE price>=800
  28. SELECT COUNT(DISTINCT price) price FROM d_product
  30. SELECT SUM(price) FROM d_product
  32. SELECT MAX(price) FROM d_product
  34. SELECT AVG(price) FROM d_product
  36. SELECT* FROM d_product GROUP BY price
  38. SELECT COUNT(*),AVG(price),category_id FROM d_product GROUP BY category_id
  41. CREATE TABLE Persion(
  42. pid VARCHAR(30),
  43. pname VARCHAR(30),
  44. paddress VARCHAR(30)
  45. )
  47. #添加主键
  48. ALTER TABLE Persion ADD PRIMARY KEY(pid)
  50. DROP TABLE Persion
  52. DESC Persion
  54. #删除主键
  55. ALTER TABLE persion DROP PRIMARY KEY
  57. #自动增长
  58. CREATE TABLE Persion(
  59. pid INT(8) PRIMARY KEY AUTO_INCREMENT,
  60. pname VARCHAR(30),
  61. paddress VARCHAR(30)
  62. )
  64. #修改自增长的开始值
  65. ALTER TABLE Persion AUTO_INCREMENT=100
  68. SELECT * FROM d_product
  70. CREATE TABLE category(
  71. cid VARCHAR(32) PRIMARY KEY,
  72. NAME VARCHAR(50)
  73. )
  75. DROP TABLE category
  76. DESC category
  79. CREATE TABLE products(
  80. pid VARCHAR(32) PRIMARY KEY,
  81. pname VARCHAR(50),
  82. price DOUBLE,
  83. category_id VARCHAR(32)
  84. )
  87. DROP TABLE products
  88. DESC products
  90. #添加外键
  91. ALTER TABLE products ADD CONSTRAINT product_fk FOREIGN KEY (category_id) REFERENCES category (cid)
  94. INSERT INTO category(cid,NAME) VALUES('c001','电脑')
  96. SELECT*FROM category
  98. #插入主表数据
  99. INSERT INTO products(pid,pname,price) VALUES('p001','iphone X',888)
  101. #插入从表数据
  102. INSERT INTO products(pid,pname,price,category_id) VALUES('p002','iphone X',666,'c001')
  105. #插入如主表不存在的category_id的从表数据（失败）
  106. INSERT INTO products(pid,pname,price,category_id) VALUES('p003','iphone X',666,'c002')
  108. #如果主表数据 已经关联了从表数据，删除主表数据就失败
  109. DELETE FROM category WHERE cid='c001'
  111. SELECT * FROM products
  113. CREATE TABLE orders(
  114.     oid VARCHAR(32) PRIMARY KEY,
  115.     totalprice DOUBLE
  116. );
  119. #中间表
  120. CREATE TABLE orderitem(
  121. pid VARCHAR(32),
  122. oid VARCHAR(32)
  123. )
  125. ALTER TABLE orderitem ADD CONSTRAINT orderitem_products_fk FOREIGN KEY(pid) REFERENCES products(pid);
  126. ALTER TABLE orderitem ADD CONSTRAINT orderitem_orders_fk FOREIGN KEY(oid) REFERENCES orders(oid);
  127. #5、扩展：中间表两个外键，一般会形成联合主键（2个组合在一起唯一）[可选]
  128. ALTER TABLE orderitem ADD CONSTRAINT PRIMARY KEY(pid,oid);
  130. INSERT INTO products(pid,pname,price) VALUES('p003','IBM台式机',10000);
  131. INSERT INTO orders(oid,totalprice) VALUES('x001',10000);
  132. INSERT INTO orderitem(pid,oid)  VALUES('p003','x001');
  133. INSERT INTO orderitem(pid,oid)  VALUES('p001','x001');
  134. #插入失败
  135. INSERT INTO orderitem(pid,oid)  VALUES('p004','x001');
  137. #删除中间表的数据
  138. DELETE FROM orderitem WHERE pid='p001' AND oid='x001'
  141. SELECT*FROM category
  142. SELECT * FROM products
  143. SELECT*FROM orders
  144. SELECT*FROM orderitem

day05(MySQL)笔记.docxjavaweb-day20每日作业卷答案.docx