Sql语言基础回顾

DDL语言(数据定义语言)

DDL之数据库操作（DATABASE）

主要完成数据库的创建，查看，使用和删除。

1
2
3
4
5
6
7
CREATE DATABASE test;  # 创建数据库
CREATE DATABASE IF NOT EXISTS mysql_db CHAR SET 'utf8';  
# 创建数据库并定义字符集为utf8（同时如果数据库存在就不用创建）charset=utf8
SHOW DATABASES;  # 查看服务器中所有的数据库
USE mysql_db;  # 使用数据库
SELECT database(); # 查看我们使用的是哪一个数据库
DROP DATABASE test;  # 删除数据库

主要目的是能够完成表的创建，查看，重命名和删除

DDL之数据表操作（TABLE）

1
2
3
4
5
6
7
8
9
CREATE TABLE student(
    id INT NOT NULL PRIMARY KEY ,
    name VARCHAR(20),
    age INT
);  # 创建student表
SHOW TABLES ;  # 查看当前数据库有哪些表
DESC student;  # 查看当前表的结构
RENAME TABLE student TO stu;  # 修改表名
DROP TABLE student;  # 删除表
  • 常用的数据类型:
    • 整数：int，bit
    • 小数：decimal
    • 字符串：varchar，char
    • 日期时间: date， time， datetime
    • 枚举类型(enum)
  • 常见的约束:
    • 主键约束 primary key
    • 非空约束 not null
    • 惟一约束 unique
    • 默认约束 default
    • 外键约束 foreign key
  • 数据类型和约束保证了表中数据的准确性和完整性

DDL之表结构操作（字段操作）

能够完成字段的添加，修改和删除

1
2
3
4
ALTER TABLE stu ADD score INT NOT NULL ;  # 向表中添加字段（列）
ALTER TABLE stu ADD `desc` VARCHAR(100);  # 向表中添加的字段也可用esc下面的符号
ALTER TABLE stu CHANGE `desc` grade VARCHAR(10);  # 将表中的desc字段换成grade列
ALTER TABLE stu DROP grade;  # 删除表中的grade字段(列)

DML语言(数据操作语言)

数据记录的插入，更新和删除操作

1
2
3
4
5
6
7
8
9
10
11
DESC stu;  # 查看stu表结构
INSERT INTO stu VALUES (1,'小明',18,88),(2,'小华',20,99);  #按行（记录）插入数据
INSERT INTO stu(id,name,age,score) VALUES (3,'小张',19,66);  # 指定字段插入
INSERT INTO stu(id,age,score) VALUES (4,20,99);  # 向id、age、score字段插入数据
INSERT INTO stu(id,score) VALUES (5,80),(6,70),(7,88); # 向id，score字段插入多组数据

UPDATE stu SET name = '小李' WHERE id=4;  # 将id为4的学生姓名改为指定名称
UPDATE stu SET name = '小赵',age= 10 WHERE score=80;  # 将成绩为80的学生姓名和年龄都更改为指定值

DELETE FROM stu WHERE id=6;  # 删除id为6的记录
TRUNCATE student;  # TRUNCATE用于清空表中的所有记录

sql约束

1
2
3
ALTER TABLE student DROP PRIMARY KEY ;  # 删除student表中的主键约束
ALTER TABLE student ADD PRIMARY KEY (id);  # 添加student表中id未主键约束
INSERT INTO student(id,name,age) VALUES (1,'小华',18);  # 一般情况下主键必须不能不传值，而且不能为空

DQL语言(数据查询语言)

简单查询

先创建表，然后依次通过inser插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 插入记录
/*
 INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,NULL);
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');
 */
1
2
3
4
5
6
CREATE TABLE product(
    pid INT PRIMARY KEY ,
    pname VARCHAR(20),
    price DOUBLE,
    category_id VARCHAR(20)
);
1
2
3
SELECT * FROM product;  # 获取表的全部信息
SELECT price,pname FROM product;  # 获取商品名称和价格信息
SELECT pname,price+10 FROM product;  # 获取商品名称和电商价（商品售价+10）

条件语句

1
2
3
4
5
6
7
SELECT pname FROM product WHERE price>10;  # 查询所有价格大于10的商品名称
SELECT * FROM product WHERE price=5000;  # 查询价格等于5000的商品信息
SELECT * FROM product WHERE price >= 200 AND price <= 200;  # 查询200-2000区间的商品信息
SELECT * FROM product WHERE pname LIKE '%斯';  # 模糊查询，%匹配为多个字符
SELECT * FROM product WHERE price BETWEEN 200 AND 2000;  # 等价于price >= 200 AND price <= 200
SELECT * FROM product WHERE category_id IS NULL;  # 查询category为null的商品信息
SELECT * FROM product WHERE category_id IS NOT NULL ;  # 查询category不为null的商品信息

排序查询

1
2
3
SELECT * FROM product ORDER BY price DESC ;  # 将所有的商品查询出来之后,根据价格进行降序排列
SELECT * FROM product ORDER BY price DESC, pid DESC ; 
# 将所有的商品查询出来之后,根据价格进行降序排列,如果价格一样就按照pid降序

聚合函数

1
2
3
4
5
6
7
8
SELECT count(*) FROM product;  # 求当前商品一共有多少件
SELECT count(category_id) FROM product;  # 查看当前有多少category_id不为空的商品
SELECT count(*) FROM product WHERE price>500;  # 计算商品价格大于500的商品个数是多少
SELECT max(price) FROM product WHERE category_id='c001';  # 求category_id = c001的所有商品的价格最大值
SELECT min(price) FROM product WHERE category_id='c002';  # 求category_id = c002的所有商品的价格最小值
SELECT sum(price) FROM product WHERE category_id='c001';  # 求所有category_id = c001的商品的价格总和
SELECT avg(price) FROM product;  # 求所有商品的平均价格
SELECT avg(price)-max(price) FROM product;  # 求所有商品的平均价格与价格最大值的差值

分组查询(group by)

having与where的区别:

  • having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤。
  • having后面可以使用分组函数(统计函数),where后面不可以使用分组函数。
1
2
3
4
5
SELECT category_id FROM product GROUP BY category_id;  # GROUP BY根据指定的字段对数据记录进行分组,分组后查看表中有哪些category_id
SELECT category_id, max(price) FROM product GROUP BY category_id;  # 查看每类商品的最大价格
SELECT avg(price), category_id FROM product GROUP BY category_id HAVING avg(price)>600;  # 将所有商品按组分类,获取每组的平均价格大于600的所有分组
SELECT count(category_id) FROM product;  # 统计各种类别商品的个数
SELECT count(*),category_id FROM product GROUP BY category_id HAVING count(*)>1;  # 统计各个分类商品的个数,且只显示个数大于1的信息

分页查询

介绍：浏览商品列表的时候，由于数据特别多，一页显示不完，一页一页的进行显示，这就是分页查询

分页查询语法：

1
SELECT 字段列表 FROM 表名 LIMIT M, N

说明:

  1. limit是分页查询关键字
  2. M表示开始行索引，默认是0
  3. N表示查询条数
1
2
3
4
5
6
7
8
9
# 分页查询
# 格式: select 字段列表 from 表名 limit M, N
# m:就代表从下标为m的位置开始分页
# n:代表提取多少条数据

# 获取表中第一条记录
SELECT * FROM product LIMIT 0,1;
# 获取表中从下标为2的记录开始的2条记录
SELECT * FROM product LIMIT 2,2;
1
2
3
4
# 获取当前产品中,类别为c002的产品里,价格最低的2件商品
SELECT * FROM product WHERE category_id='c002' ORDER BY price LIMIT 0,2;
SELECT * FROM product WHERE category_id='c002' ORDER BY price LIMIT 2;  #如果分页从0(起始位置)开始,则可以省略0
SELECT * FROM product WHERE category_id='c002' ORDER BY price LIMIT 25,5; #当分页展示的商品不存在时,不报错,只不过查询不到任何数据