sql 语句 练习
来源:互联网 发布:软件怎么制作二维码 编辑:程序博客网 时间:2024/06/16 20:45
sql 语句记忆
简单的数据库语句练习 :
select * from student where id=13 查出所有符合条件的记录select distinct name from student 查询所有不重复的名字select * from student where name like 'c%' 查询c开头的nameselect * from student order by name ASC (desc) 按照name 升序排序insert into student(id,name) values (16,'222') 插入一条数据update student set name='yy' where id=0; 修改id=0的数据的namedelete from student where name='cc' 删除name为cc的数据select * from student limit 2 查出数据限制两条select * from student where id in (5,14,15) 查询id是5,14,15的数据select * from student where id between 1 and 20 查询id在1到20之间的数据select name as bieming from student where id= 20 将name显示为bieming查询输出select student.id, student.name ,teacher.name from student,teacher where student.techer_id=teacher.id 连表查询 学生id,name,老师name 情况是学生表的老师与老师表一致
create table student (id int ,name varchar(255)) 新建学生表alter table student add uniqe(name) 学生表加唯一索引alter table student drop index name 删除索引alter table student alter teacher_id set default 0 设置默认值create index idx_name on student(name) 建立普通索引selcet * from student order by cast(score as decimal) 字符型按数字排序selcet * from student order by score+0 字符型按数字排序 alter table student drop index idx_name 删除索引alter table student drop cloumn school 删除行alter table student add school carchar(255) 增加行select * from orders where orderDate='2010-12-12' 查询按照日期select id,name from student where school is not null 查询学校字段不为空的select id,name*(techer_id+ifNUll(school,0)) from student 计算的时候如果school为null用0代替计算select AVG(orderPrice) as orderAverage from orders 算平均值
代码3
select student.id,student.name,teacher.name from student join(left join,right join,full join) teacher on student.id=teacher.id 连表查询允许左空右空
select name from student union(union all) selcet name form teacher 查询结果放一起
select count(*) from orders 总数
selcet Max(score) from orders 最大值
select Customer ,sum(orderPrice) from orders group by Customer 总和按组查
select Customer ,sum(orderPrice) from orders 总和不分组
where语句不能和合计函数一起使用
select customer ,sum(orderPrice) from orders group by customer having sum(orderPrice)>1500 查询按人分组总和大于1500的
select MID(city,1,3) as smallCity from Persons 选取字段的第几个开始的几个字符作为字段内容
select productname,unitprice,Now() as PerDate from products 当前时间
selcet productname,unitprice,FORMAT(Now(),’YYYY-MM-DD’)as PerDate 当前时间格式设置
- SQL查询语句练习
- SQL 语句练习
- sql语句练习
- SQL语句练习
- SQL 语句练习
- sql语句练习
- Sql语句练习三
- sql 语句练习
- sql 语句练习
- SQL语句练习
- sql语句小练习
- sql语句练习
- 一些sql语句练习
- Oracle sql语句练习
- sql语句练习
- sql 语句练习
- sql语句练习
- sql语句练习
- CentOS上安装JDK1.7 + Tomcat7
- 代码管理_SVN_TortoiseSVN 冲突解决详细步骤 (图)
- 解决Android使用第三方库.so自动删除问题
- 下拉刷新/下拉加载
- 关于更新Android sdk之后出现This Android SDK requires Android Developer Toolkit version 23.0.0 or above的解决方法
- sql 语句 练习
- 中国互联网大会--------网易云捕助力产品打造高品质APP
- Fragment重叠问题
- Delphi反汇编内部字符串处理函数/过程不完全列表
- iOS4 中 Core Motion 框架的介绍和使用
- rancher安装和基本使用
- eclipse package,source folder,folder区别及相互转换
- Android ListView加入CheckBox/RadioButton选择状态保持、全选、反选的状态
- swift 数据类型1