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代替,如果不为空,就使用列值本身。
网友评论