-----创建学生表----
create table user(
name char(8) primary key,
tel varchar(12) not null unique ,
content char(8) ,
time date
);
-----表中 插入数据------
insert into user values('张三','12345678909','本科毕业',20030909); 如果插入汉子出现错误则进行编码设置charset gbk
insert into user values('王麻子,'12345678949','本科毕业',20120909);
insert into user (name,tel) values('李四','12345432123');
----查询----
select* from user ; (查询所有值);
select* from user where time >20030909 order by time asc;
select * from user where time between 20030909 and 20120909;
select* from user group by content having content like'本科毕业' ;
select * from user where name like'李%';
select* from user where content in (select content from user where time>20120909);
-----修改数据-----
update user
set content='硕士毕业'
where name= '张三';
-----删除元组 -----
delete from user where name='李四';
还可以使用 top n ,top n%
聚合函数来实现一些功能: count(*/all) count(distinct /all 列名) 求元组个数。
{sum(列名) avg(列名)列的属性类型必须为数值型}
max/min(列名) 求列的最小最大。
--------表与表的链接查询---------
内连接: select* from 表1 as b1 (inner)join 表2 as b2 on b1.列名= b2.列名;
多张表链接: select* from 表1 as b1 join 表2 as b2 on b1.列名=b2.列名
join 表3 as b3 on b3.列名=b2.列名
join 表4 as b4 on b4.列名=b3.列名
where 条件;
------外连接---------
select * from 表1 as b1 right outer join 表2 as b2 on b1.列名= b2.列名;
select* from 表2 as b2 leift outer jion 表2 as b2 on b1.列名= b2.列名;
左外链接就是左边的有没有都要输出 同样右外连接的时候右边的都要输出;
注意点: 在insert 中一般大家都会省去要插入的属性列的列出但是这样有一个影藏的危险,就是当你插入的数据一个相互兼容或者是相同类型的时候,你只要输入就可以。这样不好,我们要在插入的时候吧每个列列出来这样的话就安全多了。