数据库练习题汇总2

1、修改视图v_stu,查询student表中,分数不在90~100分之间的,学员的编号(sid),学员姓名(sname),分数(score),地址(address)

alter view v_stu

as

select sid,sname,score,address

from student

where not score between 90 and 100;

select * from v_stu;

2、删除视图v_stu

drop view v_stu;

select * from student;

3、创建表(复合主键)

create table stu(

    id int(4),

    name varchar(20),

    age int(3),

    primary key(id,name)

);

4、查看表结构  

desc 表名;

5、查看数据库中的全部表

show tables;

6、插入语句

insert into stu values(101,'rise',20);

insert into stu(id,name) values(102,'tom');

insert into stu values(103,'sdf',20),(104,'sff',23);

insert into stu(id,name) values(105,'gfg'),(106,'45');

7、更新语句

update stu set name='tose',age=100 where id=102;

update stu set name='trt';

8、删除表中数据

delete from stu where id=102;

delete from stu where id in(102,103,105);

delete from stu;

9、删除表

drop table 表名;

10、查询语句

（1)查询全部列数据

            select * from stu;

（2)查询指定列数据

            select id,name,age from stu;

(3)给列起别名

            select id 编号,name 姓名 from stu;

            select count(*) 数量,sum(sal) 求和 from stu;

            select id,name,(sal+comm) 总和 from emp;

(4)去掉重复的列值

            select distinct 列名 from 表名;

            select count(distinct job) from 表名;

(5)限制查询

            select * from emp limit 初始位置,行数;

            初始位置,默认值为0,表示第一条记录

            select * from emp limit 2,7;

            select * from emp order by desc limit 2,5;

(6)排序(order by)

            select id,name,sal from emp order by sal desc;

            select id,name,sal,deptno from emp order by sal desc,deptno asc;

            多列排序时,先排第一列,如果第一列,中有相同的列值时,才会进行第二列排序

(7)条件查询

            select id,name,sal from emp where sal>1000;

            select id,name from emp where sal<>1250;

            select id,name,sal from emp where sal>1000 and sal<5000;

            select id,name,sal from emp where sal>1000 or loc='北京';

            select id,name,sal from emp where sal>1000 and (sal<5000 or deptno=30);

            select id,name,sal from emp where sal<>1250;

            select id,name,sal from emp where not sal=1250;

            select * from emp where comm is null;

            select * from emp where mgr is not null;

            select id,name,sal from emp where sal between 1000 and 5000;

            select id,name,sal from emp where sal >=1000 and sal<=5000;

            select id,name,sal from emp where not sal between 1000 and 5000;

            select id,name,sal from emp where deptno in(101,202,231);

            select id,name,sal from emp where deptno=101 or deptno=202 or deptno=231;

            select id,name,sal from emp where not deptno in(101,202,231);

            select id,name from emp where name like '李%';

            select id,name from emp where name like '%李%';

            select id,name from emp where name like '%李';

            select id,name from emp where name like '_李%';

            select id,name from emp where name like '%李__';

            select id,name from emp where not name like '%李%';

(8)聚合函数

            count(*/列名)统计数量

            sum(列名)      求和

            avg(列名)       求平均数  

            min(列名)       求最小值

            max(列名)      求最大值

(9)MySQL函数

round(数值,位数)  四舍五入函数

truncate(数值,位数) 截取函数

rand(n) 随机数函数

sqrt(n)  平方根函数

mod(n,m) 取余函数

length(字符串/列名)  统计个数

select * from emp where length(ename)>5;

trim(字符串/列名) 去掉字符串/列值2端的空格

substring(参数1,参数2,参数3) 字符串截取函数

参数1:被截取的字符串/列名

参数2:从哪里开始截取

参数3:截取的个数

reverse(字符串/列名) 字符串逆序函数

concat(字符串/列名,字符串/列名,... 字符串/列名)  字符串拼接函数

select id,name,sal,comm,concat(sal,comm) sal from emp;

curdate()  获取当前系统日期

curtime()  获取当前系统时间

sysdate()  获取当前系统日期时间

year(date)  获取年份

where year(hiredate)='1981';

month(date) 获取月份

where month(hiredate)='10';

(10)分组查询

select deptno,sum(sal),count(*)

from emp

where sal>1000

group by deptno

order by count(*) desc,deptno asc;

根据某一列,把相同的列值分成一组,然后对

每一组数据使用聚合函数,聚合函数经常和分组查询一起使用。

(11)having语句

在分组查询得到结果后,再次对数据进行筛选,使用having,必须和group by一起使用

select deptno,sum(sal),avg(sal)

from emp

where not ename like '%K%'

group by deptno

having avg(sal)>2000

order by deptno desc;

11、约束

主键约束(primary key)

唯一约束(unique)

[if !supportLists]l [endif]删除唯一约束

alter table 表名 drop index key_name;

show keys from 表名\G;

默认值约束(default)

非空约束(not null)

create table stu1(

id int(4) primary key,

name varchar(20) unique,

age int(3) default 18,

address varchar(30) not null,

email varchar(50)

);

12、视图

视图就是一张虚拟表,可以通过视图来查询数据

create view v_emp

as

select * from emp;

select * from v_emp;

show tables;

select id,name,sal from v_emp

where sal>1000;

alter view v_emp

as

select * from emp where sal>1000;

insert into v_emp values(101,'rose',20);

select * from emp;

update v_emp set name='rose';

select * from emp;

delete from v_emp where name='rose';

select * from emp;

drop view视图名称;

13、查询emp表中,员工的编号,姓名,工资,奖金,工资+奖金之和

select empno,ename,sal,comm,sal+comm from emp;

select empno,ename,sal,comm,sal+ifnull(comm,0) from emp;

如果comm列中列值为空使用0代替,如果不为空,就使用列值本身。