入门SQL语句

对以下两张表进行基本数据库操作

 

 

1.查询所有行的数据

select * from club_activity;

 2.查询指定行的数据

seelct activity_leader,holding_time from club_activity

 3.筛选数据

select * from club_activity where activity_leader='mary';

 4.多个条件筛选数据

select * from club_activity where activity_leader='mary' and holding_place='home';

 5.去重

select distinct activity_leader from club_activity;

 6.排序

select holding_club,holding_contents from club_activity order by holding_time;

 7.多列排序

select activity_leader,id from club_activity order by id desc,activity_leader;

 8.筛选非空数据

select id,activity_leader from club_activity where id is not null;

 9.通配符（匹配含有的字符）

select * from club_activity where activity_leader like '%a%';

 10.concat()函数

将两列合并

select concat(activity_leader,' ',id) from club_activity;

 11.并集

将多个SQL语句查询得到的结果集合并到一起

select activity_leader from club_activity
union all
select founder from club;

 12.计算数据行数

select count(id) from club_activity;

 13.平均值

select avg(id) from club_activity;

 14.插入数据

insert into club_activity(activity_leader,holding_club,id)
values('gem','Sing Club',9);

 15.更新数据

update club_activity set holding_contents='sleep';

 16.新建一张表

create table Employee(id int,firstname varchar(20));

 17.删除表内所有数据

delete from club;

 18.删除某张表格

drop table Employee;