SQL基本语句

最新推荐文章于 2024-09-14 19:55:29 发布

C_Jing_

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量264

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/Capricorn_Jing/article/details/72675207

版权

关系型数据库中的数据结构：
SERVER > DATABASE > TABLE > ROW > COLUMN

下面以拟建一个数据库为例，列出了一些基本的SQL语句

/*1. 设置SQL语句的编码格式           
2. 删除数据库dangdang
3. 创建数据库dangdang，指定存储数据所用的编码
4. 进入数据库
5. 创建表 dd_category(cid, cname, count)
6. 添加三行记录，三个书籍分类(10/20/30)
7. 查询出所有的书籍分类
8. 创建表 dd_book(bid,title,pic,price,pubDate,isOnsale,categoryId)
9. 为每种分类添加两三条记录
10. 查询出所有的书籍
11. 查询出所有的“计算机”类书籍
12. 删除10号分类及其下所有的书籍
13. 删除编号为6的书籍，需要修改对应分类的书籍数量*/

SET NAMES UTF8;
DROP DATABASE IF EXISTS dangdang;
CREATE DATABASE dangdang CHARSET=UTF8;
USE dangdang;

CREATE TABLE dd_category(
  cid INT PRIMARY KEY,
  cname VARCHAR(6),
  count INT
);
INSERT INTO dd_category VALUES
(10,'计算机','3'),
(20,'美食','3'),
(30,'历史','3');
SELECT * FROM dd_category;

CREATE TABLE dd_book(
  bid INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(64),
  pic VARCHAR(32),
  price FLOAT(10,2),
  pubDate BIGINT,
  isOnsale BOOLEAN,
  categoryId INT
);
INSERT INTO dd_book VALUES
(NULL,'javascript高级','img/1.jpg','98.68','123457890123','1',10),
(NULL,'html高级','img/2.jpg','98.68','123457890123','1',10),
(NULL,'css高级','img/3.jpg','98.68','123457890123','0',10),
(NULL,'鱼香肉丝','img/4.jpg','98.68','123457890123','1',20),
(NULL,'地锅鲶鱼','img/5.jpg','98.68','123457890123','1',20),
(NULL,'老醋花生','img/6.jpg','98.68','123457890123','0',20),
(NULL,'丝绸之路','img/7.jpg','98.68','123457890123','1',30),
(NULL,'抗日战争','img/8.jpg','98.68','123457890123','0',30),
(NULL,'成吉思汗','img/9.jpg','98.68','123457890123','1',30);
SELECT * FROM dd_book;

SELECT title FROM dd_book WHERE categoryId=(
  SELECT cid FROM dd_category WHERE cname='计算机'
);
DELETE FROM dd_category WHERE cid=10;
DELETE FROM dd_book WHERE categoryId=10;
UPDATE dd_category SET count=count-1 WHERE cid=(
  SELECT categoryId FROM dd_book WHERE bid=6
);
DELETE FROM dd_book WHERE bid=6;