create database FySql
use FySql
创建表命令
create table Users
(
UserId int primary key not null,
UserName varchar(20) not null,
passwd varchar(20) not null,
sex bit not null,
birthday datetime null,
RegTime datetime,
)

插入表users的数据的几种方法
insert users values(1,'fangsong','123','20020101',20)
insert users1(id,username,passwd,birthday,age) values(2,'fenxji','123','20020401',30)
insert into users1 values(1,'women','123','20020103',30)

插入数据命令
insert into Users values(1,'liuhu','123',1,'19770522','20061114')
insert into Users values(2,'分手','123',1,'19790602','20061113')
insert into Users values(3,'柳叶','123',0,'19780512','20061004')

insert into Users values(5,'人类','123',0,'19751102','20061006')
insert into Users values(6,'飞儿','123',0,'19780503','20061007')
insert into Users values(7,'构飞','123',1,'19830301','20051004')

检索数据的方法-选出数据

select * from Users
select Userid,UserName,passwd,sex,birthday from users
select [Userid],[UserName],[password],[sex],[birthday] from [users]
select "Userid","UserName","password","sex","birthday" from "users"
select userId 用户id,username 用户名,sex 性别 from users
select userId as 用户id,username as 用户名,sex as  性别 from users

select UserId 用户id,UserName 用户名,sex 性别,birthday as 生日 from users
下面只返回一个字段里面的唯一值
select distinct sex from users
select top 5 * from users
select top 5 percent * from users

用函数进行统计
select count(*)  from Users
select count(birthday)  from Users
select max(UserId) 最大用户id from users
select max(UserId) 最小用户id from users
select sum(UserId) 所有id的和 from users
select avg(UserId) 所有id的和 from users
select UserId+sex, "UserName"+"Password" from users

带条件检索数据

(1)查出放松的资料
select * from Users where Username='放松'
select userId,UserName,password from Users where UserName='放松'

查出性别是女的并且UserId小于等于6的姓名,and代表且,or代表或者
select UserName from Users where sex=0 and UserId<=6

查出1978年前出生的数据(含1978年)
select *  from users where year(birthday)<'1980'

查出1978年前出生的数据(含1978年),但是列名改为中文的哈,(用as 或空格作为别名),并且只显示年份
select userId,UserName,password,sex,year(birthday) as 出生年份 from users where year(birthday)<'1980'

查出在今年注册的用户
select UserId,userName from Users where datediff(yy,regtime,getdate())<1
查出在本月注册的用户
select UserId,userName from Users where datediff(mm,regtime,getdate())<1

查出在今天注册的用户
select UserId,userName from Users where datediff(dd,regtime,getdate())<1

查出本月注册的用户数
select count(*) from Users where datediff(mm,regtime,getdate())<1
--或
select count(*) as 注册的用户数  from Users where datediff(mm,regtime,getdate())<1

此外 >=,<= <> !> !< between and  not between and  或in(a,b,c) not in(a,b,c)
注意in ===or