sql 语句 练习

sql 语句记忆

简单的数据库语句练习 :

select * from student where id=13  查出所有符合条件的记录

select distinct name from student  查询所有不重复的名字

select * from student where name like 'c%' 查询c开头的name

select * 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的数据的name

delete 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 当前时间格式设置