DDL查询和创建数据库:
查询所有数据库:SHOW DATABASES;
查询数据库的创建语句:SHOW CREATE DATABASE 数据库名称;
创建数据库: CAREAT DATABASE数据库名称;
创建数据库(不存在再创建):CREATEDATAVBASE IF NOT EXISTS 数据库名称;
创建数据库(指定字符集):CREATE DATABASE 数据库名称 CHARACTER SET 数据库名称
修改数据库(修改字符集):ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称:
删除数据库:DROP DATABASE 数据库名称
删除数据库(存在再删除):DROR DATAVBASE IF EXISTS 数据库名称;
查看当前使用的数据库:SELECT DATABASE();
使用数据库:USE 数据库名称
DDL查询和创建数据表:
查询所有数据表:SHOW TABLES;
查询表结构:DESC 表名;
查询字符集:SHOW TABLE STATUS FROM 库名 LIKE'表名';
创建数据表:CREATE TABE:表名(列名 数据类型 约束,
列名 数据类型 约束,);
数据类型: int ,double ,date:日期年月日,datetime:日期精确到时分秒, timestamp:时间戳类型 ,varchar(长度):字符串类型,
DDL删除数据表:
删除数据表:DROP TABLE 表名;
删除数据表(存在再删除):DROP TABLE IF EXISTS 表名;
DML新增表数据:
给指定列添加数据
标准语法:
INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...);
*/
-- 向product表添加一条数据
INSERT INTO product (id,NAME,price,stock,insert_time) VALUES (1,'手机',1999.99,25,'2020-02-02');
-- 向product表添加指定列数据
INSERT INTO product (id,NAME,price) VALUES (2,'电脑',3999.99);
/*
给全部列添加数据
标准语法:
INSERT INTO 表名 VALUES (值1,值2,值3,...);
*/
-- 默认给全部列添加数据
INSERT INTO product VALUES (3,'冰箱',1500,35,'2030-03-03');
/*
批量添加所有列数据
标准语法:
INSERT INTO 表名 VALUES (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);
*/
-- 批量添加数据
INSERT INTO product VALUES (4,'洗衣机',800,15,'2030-05-05'),(5,'微波炉',300,45,'2030-06-06');
DML删除和修改表数据:
/*
修改表数据
标准语法:
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [where 条件];
*/
-- 修改手机的价格为3500
UPDATE product SET price=3500 WHERE NAME='手机';
-- 修改电脑的价格为1800、库存为36
UPDATE product SET price=1800,stock=36 WHERE NAME='电脑';
/*
删除表数据
标准语法:
DELETE FROM 表名 [WHERE 条件];
*/
-- 删除product表中的微波炉信息
DELETE FROM product WHERE NAME='微波炉';
-- 删除product表中库存为10的商品信息
DELETE FROM product WHERE stock=10;
DQL数据表查询
/*
查询全部数据
标准语法:
SELECT * FROM 表名;
*/
-- 查询product表所有数据
SELECT * FROM product;
/*
查询指定列
标准语法:
SELECT 列名1,列名2,... FROM 表名;
*/
-- 查询名称、价格、品牌
SELECT NAME,price,brand FROM product;
/*
去除重复查询
标准语法:
SELECT DISTINCT 列名1,列名2,... FROM 表名;
*/
-- 查询品牌
SELECT brand FROM product;
-- 查询品牌,去除重复
SELECT DISTINCT brand FROM product;
/*
DQL条件查询:
/*
条件查询
标准语法:
SELECT 列名列表 FROM 表名 WHERE 条件;
*/
-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;
-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand='华为';
-- 查询金额在4000 ~ 6000之间的商品信息
SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
-- 查询库存为14、30、23的商品信息
SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
SELECT * FROM product WHERE stock IN(14,30,23);
-- 查询库存为null的商品信息
SELECT * FROM product WHERE stock IS NULL;
-- 查询库存不为null的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;
-- 查询名称以小米为开头的商品信息
SELECT * FROM product WHERE NAME LIKE '小米%';
-- 查询名称第二个字是为的商品信息
SELECT * FROM product WHERE NAME LIKE '_为%';
-- 查询名称为四个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';
-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE NAME LIKE '%电脑%';
SQL聚合函数查询:
/*
聚合函数
标准语法:
SELECT 函数名(列名) FROM 表名 [WHERE 条件];
*/
-- 计算product表中总记录条数
SELECT COUNT(*) FROM product;
-- 获取最高价格
SELECT MAX(price) FROM product;
-- 获取最低库存
SELECT MIN(stock) FROM product;
-- 获取总库存数量
SELECT SUM(stock) FROM product;
-- 获取品牌为苹果的总库存数量
SELECT SUM(stock) FROM product WHERE brand='苹果';
-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM product WHERE brand='小米';
SQL排序查询:
/*
排序查询
标准语法:
SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
*/
-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;
-- 查询名称中包含手机的商品信息。按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
-- 按照金额升序排序,如果金额相同,按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;
SQL分组查询:
/*
分组查询
标准语法:
SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
*/
-- 按照品牌分组,获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额,只显示总金额大于7000元的
SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额,只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand,SUM(price) getSum FROM product
WHERE price > 4000
GROUP BY brand
HAVING getSum > 7000
ORDER BY getSum DESC;
SQL分页查询:
/*
分页查询
标准语法:
SELECT 列名 FROM 表名
[WHERE 条件]
[GROUP BY 分组列名]
[HAVING 分组后条件过滤]
[ORDER BY 排序列名 排序方式]
LIMIT 当前页数,每页显示的条数;
LIMIT 当前页数,每页显示的条数;
公式:当前页数 = (当前页数-1) * 每页显示的条数
*/
-- 每页显示3条数据
-- 第1页 当前页数=(1-1) * 3
SELECT * FROM product LIMIT 0,3;
-- 第2页 当前页数=(2-1) * 3
SELECT * FROM product LIMIT 3,3;
-- 第3页 当前页数=(3-1) * 3
SELECT * FROM product LIMIT 6,3;
约束:
SQL主键约束(自带非空和唯一功能):
-- 创建学生表(编号、姓名、年龄) 编号设为主键
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(30),
age INT
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (1,'张三',23);
INSERT INTO student VALUES (2,'李四',24);
-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;
-- 建表后单独添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;
SQL主键自增约束(与主键约束同时存在):
-- 创建学生表(编号、姓名、年龄) 编号设为主键自增
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23),(NULL,'李四',24);
-- 删除自增约束
ALTER TABLE student MODIFY id INT;
INSERT INTO student VALUES (NULL,'张三',23);
-- 建表后单独添加自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
SQL唯一约束(添加唯一约束后不能添加重复信息):
-- 创建学生表(编号、姓名、年龄) 编号设为主键自增,年龄设为唯一
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT UNIQUE
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);
INSERT INTO student VALUES (NULL,'李四',23);
-- 删除唯一约束
ALTER TABLE student DROP INDEX age;
-- 建表后单独添加唯一约束
ALTER TABLE student MODIFY age INT UNIQUE;
SQL非空约束(不能出现null值):
-- 创建学生表(编号、姓名、年龄) 编号设为主键自增,姓名设为非空,年龄设为唯一
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30) NOT NULL,
age INT UNIQUE
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);
-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30);
INSERT INTO student VALUES (NULL,NULL,25);
-- 建表后单独添加非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30) NOT NULL;
SQL外键约束:
外键约束
标准语法:
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
-- 建表时添加外键约束
-- 创建user用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
NAME VARCHAR(20) NOT NULL -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
-- 创建orderlist订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
number VARCHAR(20) NOT NULL, -- 订单编号
uid INT, -- 外键列
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
/*
删除外键约束
标准语法:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
*/
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
SQL外键级联操作:
/*
添加外键约束,同时添加级联更新 标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE;
添加外键约束,同时添加级联删除 标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON DELETE CASCADE;
添加外键约束,同时添加级联更新和级联删除 标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE ON DELETE CASCADE;
*/
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
-- 添加外键约束,同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
ON UPDATE CASCADE ON DELETE CASCADE;
-- 将李四这个用户的id修改为3,订单表中的uid也自动修改
UPDATE USER SET id=3 WHERE id=2;
-- 将李四这个用户删除,订单表中的该用户所属的订单也自动删除
DELETE FROM USER WHERE id=3;