赞
踩
DML:(insert into ,update,delete from)
插入数据
修改数据
删除数据
DQL:查询(select)
插入数据:
insert into table_name [(column[,column.])]
values(value[,value]);
eg: insrt into teacher_table(teacher_name)
values('hhh');
如果不想在标后用括号列出所有列,需要为所有列指定值,如果某列值不能确定,需要分配null
insert into teacher_table
--null代替主键
values(null,'hello')
update :修改数据库记录
update table_name
set column1=value.....
[where condition];
eg: update teacher_table
set teacher_name='孙悟空';
update teacher_table
set teacher_name='八戒'
where teacher_id>1;
delete:删除指定数据表的记录
delete from table_name
[where condition];
eg: delete from student_table;
delete from student_table
where teacher_id>5;
select 语句
select column1,column2.....
from 数据源
[where condition]
eg:
select *
from teacher_table
select student_name
from student_table
where java_teacher>3
使用算是运算符:
数值型数据列,变量,常量(+ - * /)
日期型数据列,变量,常量(+ -)
eg:select teacher_id+5
from teacher_table
select *
from teacher_table
where teacher_id*2>7
concat 连接
select concat(teacher_name,'hahha')
from teacher_table;
select concat(teacher_name,null)
from teacher_table;
as:起别名
select teacher_id+5 as my_ID
from teacher_table;
为多列起别名:
select teacher_id+5 my_ID, teacher_name 老师
from teacher_table;
为表起别名:
select teacher_id+5 my_ID, teacher_name 老师
from teacher_table tt;
distinct 关键字去除重复行
select student_name ,java_teacher
from student_table;
--去除重复行
select distinct student_name ,java_teacher
from student_table;
运算符 含义
between val1 and val2 在val1和val2之间
in(list) 等于括号里多个值得任意之一
like 字符串匹配,like后支持通配符
is null 要求指定值等null
eg:
select * from student_table
where student_id between 2 and 4;
select * from student_table
where student_id between java_teacher and student_id;
--in使用
--选出student_id为2和4 的
select * from student_table
where student_id in(2,4);
--like使用
--(_)代表任意一个字符(%)任意多个字符
select * from student_table
where student_name like '白%';
select * from student_table
where student_name like '_';
--is null使用
--SQL中null=null 返回null
select * from student_table
where student_name is null;
多条件组合查询(and or not)
select * from student_table
where student_name like '_' and student_id>10;
select * from student_table
where not student_name like '_' and student_id>10;
运算符优先级:
运算符 优先级(小的优先)
比较运算符 1
not 2
and 3
or 4
eg:select * from student_table
where (student_id>3 or student_name>'李')
and java_teacher>1;
查询后1的结果默认按照插入顺序排列 如果需要按照列值大小排序 (order by)
order by column_name1[desc] ,colulmn_name2.............升序排列
降序排列(desc)
eg: select * from student_table
order by java_teacher;
多序排列:asc desc 必须单独设定,如果多个拍序列,第一个是首要排序列,第一个有多个相同的值是,第二个起作用
select * from student_table
order by java_teacher desc ,student_name; (按照java_teacher 降序,java_teacher 相同时,按照student_name升序)
数据库函数:
function_name(arg1,arg2.....)
eg:
--teacher_name 列的字符长度
select char_length(teacher_name)
from teacher_table;
--teacher_name 列的sin
select sin(teacher_name)
from teacher_table;
--获取当前日期
select CURDATE();
--md5加密
select MDT('testing');
处理null的函数:
ifnull(expr1,expr2) 如果expr1位null, 返回expr2,
nullif(expr1,expr2) 相等返回null,否则返回expr1
if(expr1,expr2,expr3) 三目运算符,如果expr1为true,不等于0,切不等于null,返回exr2,否则返回expr3
isnull(expr1)判断expr1是否为null,如果是返回true,否则返回false
eg:
select ifnull(student_name,'没有名字')
from student_table;
select nullif(student_name,'张三')
from student_table;
select if(isnull(student_name),'么有名字','有名字')
from student_table;
case 语句
case value
when compare_value1 when result1
when compare_value2 when result2
....
else result
end
case语句用value和compare_value1依次进行比较,value与compare_value1相等,则返回对应result1
eg:
select student_name,
case java_teacher
when 1 when 'java老师'
when 2 when 'python老师'
else '其他老师'
end
from student_table;
分组和组函数:
avg(【distinct||all】expr)计算多行expr平均值
count({*|[distinct||all]expr})总条数
max([distinct]expr)
min([distinct]expr)
sum([distinct||all]expr)
group by 分组、
select count(*)
from studnt_table
--与java_teacher值相同的分为一组
group by java_teacher;
对分组进行过滤:having(可以使用组函数,where不能使用组函数)
select count(*)
from studnt_table
group by java_teacher
having count(*)>2;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。