SQL语句1——单表查询

create database aaadrop database aaa--结构drop--内容delete--列名　数据类型　［完整性约束］--1、列名，命名规则--2、数据类型:text char(8000) nchar varchar nvarcha     int bigint smallint tinyint    money float,numeric(20,3) ,decimal(10,5)     datetime smalldatetime     binary(20),bit,imag　　　　--3\约束：实体：primary key         　参照：foreingn key           自己定义：not null                    unique                    check                    defaultcreate table emp(eno char(10) primary key, ename varchar(30) not null unique, eage tinyint check(eage<100), eworktime datetime )DDL:create dropDML:insert--1、insert intoinsert into 表名(列名１,列名２,..)values(值１，值２。。)select * from stuselect * from scinsert into stuvalues('006','puppy',21,'m','wlx')insert into scvalues('002','c01',70)--2 select:表、行、列select 列from 　表where　条件（行）一、select后列的操作1、所有列select *from  stu２、部分列select sname,sno,ssexfrom stu3、重新命名列select sname　as name,sno,ssexfrom stuselect sname　as 姓名,sno 学号from stu4　、列的计算　一、字符类型的列的连接 +　select sno+sname as xuehaoxingming　from  stuselect Rtrim(sno)+sname as xuehaoxingmingfrom stu--字符串的操作select '2009'+sno newsnofrom stu--常量列select '2009'from stu二、数值类型的计算　＋　——　＊　／select 2012-sage as birthyearfrom stu三、日期时间类型的计算,getdate(),year(),month,day()select year(getdate())-sage birthyearfrom stu4、消除重复列select distinct sdeptfrom stu--where后的筛选条件,and or notselect *from stuwhere sname like '%j__'1、比较>,<,>=,<=,<>,!=2、闭区间　between and 的等价关系　　　　　　not between and　3、模糊比较 like　作用范围：用于字符类型和日期时间类型，　凡是加引号的 %:代表任意多个字符 _:代表任意单个字符4、空值的比较 is null / is not nullselect * from stuwhere sdept is not null  and sage>20and sname like '_i%'--from 后表的连接--查询所有学生的姓名和成绩select stu.snofrom (stu inner join sc on stu.sno=sc.sno)     select *from stu,scwhere stu.sno=sc.snoselect  列from 　表where　条件