表内数据操作:
增
insert into 表名 values ();
删
delete from 表名 where name="张三";
改
update 表名 set name="李四" where id=1;
查
select * from 表名 where 条件;
SELECT【INTO】 新表名FROM【WHERE】【GROUP BY】分组依据【HAVING】分组后加函数【ORDER BY ASC/DESC】
对表的操作
use 数据库
主键:alter table 表名 add constraint PK_自定义 primary key (列名)
外键:alter table 表名 add constraint FK_自定义 foreign key(列名1) references 表名2(列名2)
唯一:UK_自定义 unique(列名)
默认:DF_自定义 default(默认) for 列名
检查:CK_自定义 check(s=`男` or s=`女`)
复合:alter table 表名 add constraint FK_自定义 primary key(列名1,列名2)
删除约束:alter table 表名 drop constraint 约束名
自增:IDENTITY(a,b)a是初始值,b是增长值
特殊字段
【DISTINCT】消除重复
select DISTINCT * from
【TOP】筛选
select TOP 20 * from
select TOP 20 percent from
【AS】替换
select age AS 年龄 from
【BETWEEN】在....之间
select * from st where age BETWEEN 18 and 20
【IN】在
select * from students where address IN(湘潭,地址不详)
【LIKE】通佩符
select * from students where nam LIKE ‘黄%’
【IS NULL】是空
select * from students where address IS NULL
【AND】和
select * from students where address=‘湘潭’ AND age=‘18’
【GROUP BY】分类
select count(*) as 数量 from students GROUP BY address
【HAVEING】分类+
select count(*) as 数量 from students GROUP BY address HAVEING count>1
group by后面一般只有haveing
select deptno,max(sal) from emp where job in (‘clerk‘,‘saleman‘) group by deptno having max(sal)>1500
having后面必须加一个组函数(max等),这条语句只能查询到group by后面的字段
【ORDER BY ASC/DESC】排序
select * from students where age >18 ORDER BY age ASC
select *,(表达式:列名+列名) as 总成绩 from