使用SSMS新建SQLServer的一个表

打开查询页面

点击已新建的数据库，右键，然后点击新建查询。

写入查询语句

CREATE TABLE reader
(
read_id char(8) NOT NULL,
reader_name nvarchar(50) NOT NULL,
reader_sex char(2) NOT NULL,
reader_department nvarchar(60) NOT NULL
)
GO


插入一条记录

其中jy是数据库名称,book是表名，注意插入的记录要与表的结构一致。

use jy
go
insert into book values(‘b0001‘,‘sql宝典‘, ‘976-7-121-22013-5‘,‘廖梦依‘,‘电子工业出版社‘, 89,18)


查询练习

?

SQL查询

1.查询读者表reader的读者姓名和所在系，要求查询结果显示为"姓名"和"院系"，按姓名排序.

select reader_name as 姓名, reader_department as 院系
from reader
order by 姓名


2.查询读者表reader中所有姓"李"的读者的信息

select * from reader
where read_name like ‘李%‘


3.分组查询，查询渡河表reader中各专业的男女人数.

select reader_department, reader_sex, count(*) as 人数
from reader
group by reader_department, reader_sex


4.查询图书价格超过电子工业出版社图书平均单价的图书信息

select book_name, book_author, book_publisher
from book
where book_price >
(select avg(book_price)
from book
where book_publisher=‘电子工业出版社‘)


5.查询编号为"r0007"读者的姓名，所在院系以及所借阅的图书的书名(有三个表book,reader,record)

select reader_name, reader_department, book_name
from book,reader,record
where book.book_id = record.book_id and
reader.reader_id = record.reader_id adn
reader.reader_id = ‘r0007‘


6.创建视图，用于查看借阅了图书的读者姓名,以及他们借阅的图书名称

create view v_record as
select reader_name, book_name
from reader,book,record
where book.book_id = record.book_id and
reader.reader_id=record.reader_id


接下来,

create view v_book as
select * from book where book_price <30 with check option


7.创建索引,要提高按照读者姓名查询信息的速度，就必须要在读者表reader的读者姓名reader_name列上建立非聚力集索引i_name.

create nonclustered index i_name on reader(reader_name)