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