【SQL Server】（三）DML--select、insert、delete、update

【SQL Server】（三）DML--select、insert、delete、update

一、SELECT

1、查询所有列/指定列

select * from student;
select id, name from student;

2、distinct

去重

select distinct name from student;

3、where

1) 关系操作符: <, >, <=, >=, <>, !=, =

select * from student where id < 3;

2) 比较操作符: BETWEEN, LIKE, IN, NULL

 (1) BETWEEN:

select * from student where id between 1 and 5;  --闭区间

 (2) LIKE:

      %  代表多个任意字符、_  代表一个任意字符、

      [charlist] 代表字符列中的任何单一字符、 [^charlist] 代表不在字符列中的任何单一字符.

 

select * from student where name like '赵%';
select * from student where name like '赵_';
select * from student where name like '[赵刘马李]%' --姓赵/刘/马/李的人
select * from student where name like '[^赵刘马李]%' --不姓赵/刘/马/李的人

 

 (3) IN:

select * from student 
where id in (1, 2, 3);

 (4) IS [NOT] NULL

 

select * from student where birthday IS NULL;

3) 逻辑运算符: AND, OR

select * from student where id < 6 and sex = '女';
select * from student where id < 6 or sex = '女';

4) TOP 语句---TOP x [PERCENT]

 (1) 取前 x 条数据:

select TOP 5 * from student;

 (2) 取前百分之x的数据:

select TOP 50 PERCENT * from student;

4、group by、order by (desc 倒序 / asc 默认的正序)、having

select sid, cid, AVG(scores) from score
group by cid, sid, scores
having AVG(scores) > 70 
order by scores desc;

5、AS

--as 可省
--别名是英汉混写的话需要引上
select name as 姓名_, sex '性别sex' from student;

6、JOIN (相当于列增加)

--（1）INNER JOIN
select * from
student stu inner join score sco on stu.id = sco.sid;  
--或：student stu join score sco on stu.id = sco.sid;  
--（2）LEFT JOIN
select * from
student stu left join score sco on stu.id = sco.sid;  
--（3）RIGHT JOIN
select * from
course cou right join teacher tea on tea.tid = cou.tid;  
--（4）FULL JOIN
select * from
student stu full join score sco on stu.id = sco.sid;

7、UNION (相当于行增加)

--UNION 结果集中的列名总是等于UNION 中第一个SELECT 语句中的列名。
select * from score where scores >=60
union
select * from score where scores >=90;

select * from score where scores >=60
union all
select * from score where scores >=90;

二、INSERT

insert into student values(9, '阿密丢', '2000/06/20', '女');
insert into student(id, name, sex) values(10, '刘欣荣','女');

三、DELETE

delete from student where id = 10;
------ 删除表中所有数据
delete from student;

四、UPDATE

update student set birthday = '2000/06/20', name = '欣荣' where id = 10;