select查询数据库和创建表格

一、select查询数据库

在MySQL中，SELECT查询是用于从数据库表中检索数据的基本命令。您可以根据需要检索所有列或特定列，也可以通过条件过滤结果。以下是一些基本的SELECT查询示例：

查询

SELECT  [DISTINCT] *|{字段名1, 字段名2, 字段名3,……}
FROM 表名
[WHERE 条件表达式1]
语法：SELECT  *|{字段名1, 字段名2, 字段名3,……}FROM 表名
特点：SQL语言是一种非过程化的结构查询语言，简洁

语法：字段名 as 标题名 或 字段名 标题名

【例1】从TBL_Bookinfo 表查询所有图书的记录内容

select  *  from  tbl_bookinfo;

【例2】查询TBL_Bookinfo 表中所有图书的BookID,ISBN, BookName

select BookID,ISBN,BookName from TBL_Bookinfo;

【例3】查询TBL_Bookinfo 表中所有图书的BookID,ISBN, BookName

法一：

select BookID as 索引号,ISBN as 图书编号,BookName as 书名 from TBL_Bookinfo;

法二：

select BookID 索引号,ISBN 图书编号,BookName 书名 from TBL_Bookinfo;

检索

要从书籍表中检索所有列和数据，您可以使用：

SELECT * FROM Books;

如果您只想检索书籍的名称和作者，可以使用：

SELECT book_name, author FROM Books;

【例1】从TBL_User表中检索出姓“李”的读者信息

select * from TBL_User where UserName like '李%';

【例2】从TBL_ User表中检索出名字的第二个字是“文”或“明”的读者信息

SELECT * FROM tbl_user WHERE UserName like'_文%' or UserName like'_明%';

【例3】检索出姓名中不包含有“明”字的读者信息.

法一：

select * from TBL_User where UserName not like '%明%';

法二：

select * from TBL_User where not UserName like '%明%';

【例4】在TBL_BookInfo中检索书名中有”工程”两个字的图书的书名,出版社,作者及出版日期。

select BookName,Publisher,Author,PublishDate from tbl_bookinfo  where BookName like '%工程%';

【例5】检索在TBL_Bookinfo中包含哪些出版社

--distinct 去除重复项，把这个关键字放在select后面

select distinct Publisher from TBL_Bookinfo;

使用WHERE子句过滤

您可以使用WHERE子句来过滤结果。例如，如果您想找出所有由“J.K. Rowling”写的书：

SELECT * FROM Books WHERE author = 'J.K. Rowling';

排序结果

--单字段排序 (单重排序)
语法：ORDER BY 字段名 [ASC|DESC]   ASC(升序)/DESC（降序）
-- 字段集为utf8是不按中文排序  字符集为gbk
-- 可用show create table tbl_bookinfo;强制转换为gbk

使用ORDER BY子句可以对结果进行排序。例如，按出版日期对书籍进行排序：

SELECT * FROM Books ORDER BY publication_date;

【例1】按照出版社降序排序检索TBL_Bookinfo表的记录

select * from TBL_Bookinfo order by Publisher DESC;

-- 通过查看表的创建信息来查看表的字符集

show create table  TBL_Bookinfo;

-- 如果该字段存储的是中文字段，但是字符集为utf8的话，order by 不能排序，只能将该字段强制转化为gbk才能按拼音排序，转化的方法是convert(字段 using gbk)

select * from TBL_Bookinfo order by CONVERT(Publisher using gbk) DESC;

【例2】按出版日期的升序检索TBL_Bookinfo表的记录

select * from TBL_Bookinfo order by PublishDate ASC;

- -多字段排序 (多重排序)
语法：ORDER BY 字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]]…
说明：多字段排序首先按照第一字段值排序，当第一字段的值相同时，再按照第二字段值进行排序，依次类推。

【例1】按出版社的降序和出版日期的升序检索TBL_Bookinfo表的记录

select * from TBL_Bookinfo order by CONVERT(Publisher using gbk) DESC,PublishDate asc;

【例5】从TBL_BookInfo表中检索出最厚的三本书的书名、出版社及页数

-- 按PageCount降序排序
-- 限量显示3条记录

select BookName,Publisher,PageCount from TBL_Bookinfo order by PageCount desc limit 3;

语法：LIMIT [偏移量,] 记录数
说明：
1、记录数表示限定获取的最大记录数量。仅含此参数时，表示从第1条记录开始获取。
2、偏移量用于设置从哪条记录开始，默认第1条记录的偏移量值为0，第2条记录的偏移量值为1，依次类推。
3、LIMIT 后使用无符号的整数。

【例1】tbl_bookinfo表中第6-10行记录。

select * from TBL_Borrowinfo limit 5,5;

【例2】tbl_bookinfo表中第11-20行记录。

select * from TBL_Borrowinfo limit 10,10;

联合查询

如果您想从多个表中检索数据，可以使用联合查询。例如，要检索读者的借阅记录和对应的书籍信息：

SELECT Readers.reader_name, Books.book_name, BorrowRecords.borrow_date, BorrowRecords.return_date
FROM Readers
JOIN BorrowRecords ON Readers.reader_id = BorrowRecords.reader_id
JOIN Books ON BorrowRecords.book_id = Books.book_id;

【例1】查询图书信息表中最厚五本书及最薄五本书
#使用UNION

(select * from tbl_bookinfo order by PageCount DESC limit 5)
UNION
(select * from tbl_bookinfo order by PageCount ASC limit 5)

-- 包含limit 子句时，括号不能省略

存入新表：create table 新表名 select 查询语句 [ 临时表（temporary）]

【例】查询tbl_bookinfo表中所有图书的bookid，isbn，bookname放在一个新表bookinfo_simple中

-- 新建一个表

create table bookinfo_simple select BookID,ISBN,BookName from tbl_bookinfo;

-- 查看

show create table bookinfo_simple;

[例2】查询tbl_user表中的userid ，username，class将结果放在一个新表new_user中

create table new_user select userid,username,class from tbl_user;

-- 查看

show create table new_user;

分组查询

使用GROUP BY子句可以对结果进行分组。例如，要找出每本书的借阅次数：

SELECT book_id, COUNT(*) AS borrow_count
FROM BorrowRecords
GROUP BY book_id;

这些只是基本的查询示例

二、项目

以下为您提供一个简单的图书馆管理系统数据库设计示例：图书馆管理系统数据库设计 一、项目简介及实体模型 

1. 项目简介- 本图书馆管理系统旨在实现对图书馆书籍、读者和借阅信息的有效管理，提高图书馆的运营效率和服务质量。

2. 实体模型- 书籍（Book）：包含书籍编号、书名、作者、出版社、出版日期、库存数量等属性。- 读者（Reader）：包含读者编号、姓名、性别、年龄、联系电话等属性。- 借阅记录（BorrowRecord）：包含借阅编号、读者编号、书籍编号、借阅日期、归还日期等属性

ER-模型 ：

1. 书籍与借阅记录之间存在“被借阅”的关系，一本书可以被多次借阅，一个借阅记录对应一本书。2. 读者与借阅记录之间存在“借阅”的关系，一个读者可以有多个借阅记录，一个借阅记录对应一个读者。

将 ER-模型转换为关系模型，并进行规范化 ：

1. 书籍（book_id, book_name, author, publisher, publication_date, stock_quantity）- book_id 为主键

2. 读者（reader_id, reader_name, gender, age, phone_number）- reader_id 为主键

3. 借阅记录（borrow_id, reader_id, book_id, borrow_date, return_date）- borrow_id 为主键- reader_id 为外键，引用读者表的 reader_id- book_id 为外键，引用书籍表的 book_id

关系模型与规范化

1. 书籍（Books）

   • book_id (主键): 书籍的唯一标识符。
   • book_name: 书名。
   • author: 作者。
   • publisher: 出版社。
   • publication_date: 出版日期。
   • stock_quantity: 库存数量。

2. 读者（Readers）

   • reader_id (主键): 读者的唯一标识符。
   • reader_name: 读者姓名。
   • gender: 性别。
   • age: 年龄。
   • phone_number: 联系电话。

3. 借阅记录（BorrowRecords）

                  1.borrow_id (主键): 借阅记录的唯一标识符。

                   2.reader_id (外键): 引用读者表的reader_id。

                   3.book_id (外键): 引用书籍表的book_id。

                   4.borrow_date: 借阅日期。

                   5.return_date: 归还日期。

  1.  创建书籍表

2.创建读者表

3.创建借阅记录表

这些SQL语句将创建三个表格，分别对应书籍、读者和借阅记录。每个表格都有适当的数据类型和约束，如主键和外键，以确保数据的完整性和一致性。