sql server中的表(二)------表的插入与查询

在表中插入单行数据

INSERT INTO user_00(id,name,age)
VALUES('04183003','李华','19')
INSERT INTO user_00(id,name,age)
VALUES('04183004','danny','17')

在表中插入多行数据

INSERT INTO user_00(id,name,age)
VALUES('04183005','小明','18'),
('04183006','小白','19');

从别的表复制到这张表

INSERT INTO user_02  (id,name,age)
SELECT id,name,age FROM user_00

对表的内容全部查询

SELECT * FROM user_00    /*其中的”*“指全部的内容*/

查询自己想要查询的项

SELECT name FROM user_00

去除查询结果中重复的部分

SELECT  age FROM user_00 

SELECT  DISTINCT age FROM user_00 

查询表中的头n项内容

SELECT TOP 3 * FROM user_00

查询某一项的内容

SELECT * FROM user_00
WHERE id ='04183001'

 模糊查询

SELECT * FROM user_00
WHERE name LIKE '李%'

SELECT * FROM user_00
WHERE name LIKE '%李'

 '%'的位置决定了所要查询的内容是开头还是结尾

SELECT * FROM user_00
WHERE name LIKE '%一'

SELECT * FROM user_00
WHERE name LIKE '一%'

SELECT * FROM user_00
WHERE name LIKE '%一%'

限定范围的查询(between)

SELECT * FROM user_00
WHERE age BETWEEN 17 AND 19;

SELECT * FROM user_00
WHERE id BETWEEN '04183002' AND '04183005';

SELECT * FROM user_00
WHERE id NOT BETWEEN '04183002' AND '04183005';

 IN查询

SELECT * FROM user_00 
WHERE id IN('04183002','04183006')

SELECT * FROM user_00 
WHERE id NOT IN('04183002','04183006')

 --------------------------------------------------------------------------------------------------------------------------------------

 

SELECT * FROM user_00
WHERE id  IN (SELECT id FROM user_02 )