美文网首页
java学习 sql操作常用语句

java学习 sql操作常用语句

作者: zzj丶 | 来源:发表于2018-05-24 10:44 被阅读35次

    一、创建表:
    表名:employee

    create table employee  
    (  
        id int,  
        name varchar(40),  
        sex varchar(4),  
        birthday date,  
        entry_date date,  
        salary decimal(8,2),  
        resume text  
    );
    

    二、增删改查
    (1)插入数据

    insert into employee(id,name,sex,birthday,entry_date,salary,resume) values(1,'zhangsan','male','1993-03-04','2016-11-10','1000','i am a developer');  
    /*可以省略表字段,但是必须插入全部字段*/  
    insert into employee values(1,'zhangsan','male','1993-03-04','2016-11-10','1000','i am a developer'); 
    

    (2)指定某列插入数据

    insert into employee(id) values(6);  
    

    (3)查看汉字时不乱码

    insert into employee(id,name) values(6,'张三');  
     /*告诉mysql客户采用gb2312编码*/  
      
        show variables like 'chara%';  
      
        set character_set_client=gb2312;  
      
        insert into employee(id,username) values('3','张三');  
      
         /*查看汉字时不乱码*/  
      
           show variables like 'chara%';  
        set character_set_results=gb2312;  
        select * from employee;  
    

    (4)删除表数据

    /*删除表中名称为’zs’的记录*/  
    delete from employee where name='zs';  
      
    /*删除表中所有记录*/  
    delete from employee;  
      
    /*使用truncate删除表中记录(快速删除表中所有数据,保留表的数据结构)*/  
    truncate table employee;   
    

    (5)修改表中数据

    /*将所有员工薪水修改为5000元*/  
    update employee set salary=5000;  
      
    /*将姓名为’zs’的员工薪水修改为3000元*/  
    update employee set salary = 3000 where name='zs';  
      
    /*将姓名为’aaa’的员工薪水修改为4000元,job改为ccc*/  
    update employee set salary = 4000,job='ccc' where name='aaa';  
      
    /*将lisi的薪水在原有基础上增加1000元*/  
    update employee set salary = salary+1000 where name='lisi';  
    

    (6)查询表中数据

    /*查询表中所有学生的信息*/  
    select id,name,chinese,english,math from student;  
      
    /*查询表中所有学生的姓名和对应的英语成绩*/  
    select name,english from student;  
      
    /*过滤表中重复数据*/  
    select distinct english from student;  
      
    在所有学生分数上加10分特长分*/  
    select name,(chinese+english+math)+10 from student;  
      
    /*统计每个学生的总分*/  
    select name,(chinese+english+math) from student;  
      
    /*使用别名表示学生分数*/  
    select name,(chinese+english+math) as 总分 from student;  
      
    /*使用别名表示学生分数,可以不用as*/  
    select name,(chinese+english+math) 总分 from  student;  
      
    /*查询姓名为张三的学生成绩*/  
    select * from student where name='张三';  
      
    /*查询英语成绩大于90分的同学*/  
    select * from student where english>'90';  
      
    /*查询总分大于200分的所有同学*/  
    select name,(chinese+english+math) 总分 from student where chinese+english+math>200;  
      
    /*查询英语分数在 80-90之间的同学*/  
    select * from student where english>=80 and english=<90;  
    select * from student where english between 80 and 90;  
      
    /*查询数学分数为89,90,91的同学*/  
    select * from student where math=89 or math=90 or math=91;  
    select * from student where math in(89,90,91);  
      
    /*查询所有姓李的学生成绩*/  
    select * from student where name like '李%';  
    select * from student where name like '李_';  
      
    /*查询数学分>80,语文分>80的同学*/  
    select * from student where math>80 and chinese>80;  
    select * from student where chinese is null;  
      
    /*对数学成绩排序(降序:从高到低,desc)后输出。*/  
    select name,math from student order by math desc;  
      
    /*对数学成绩排序(升序:从低到高,asc)后输出。*/  
    select name,math from student order by math asc;  
      
    /*对总分排序后输出,然后再按从高到低的顺序输出*/  
    select name,math+english+chinese from student order  by math+english+chinese desc;  
      
    /*对姓李的学生成绩排序输出*/  
    select name,math+english+chinese from student where name like '李%' order by math+english+chinese desc;  
      
    /*统计一个班级共有多少学生*/  
    select count(*) from student;  
    select count(id) from student;  
      
    /*统计数学成绩大于80的学生人数*/  
    select count(*) from student where math>80;  
      
    /*统计总分大于250的人数*/  
    select count(*) from student where math+english+chinese>250;  
      
    /*细节 null不能被count*/  
    select count(chinese) from student;  
      
    /*统计一个班级数学总成绩*/  
    select sum(math) from student;  
      
    /*统计一个班级语文、英语、数学各科的总成绩s*/  
    elect sum(math),sum(english),sum(chinese) from student;  
      
    /*统计一个班级语文、英语、数学的成绩总和*/  
    select sum(math+english+chinese) from student;  
      
    /*统计一个班级语文成绩平均分*/  
    select sum(chinese)/count(*) from student;  
      
    /*求一个班级数学平均分*/  
    select avg(math) from student;  
      
    /*求一个班级总分平均分*/  
    select avg(math+english+chinese) from student;  
      
    /*求班级最高分和最低分(数值范围在统计中特别有用)*/  
    select max(math+english+chinese) from student;  
    select min(math+english+chinese) from student;  
      
    /*对订单表中商品归类后,显示每一类商品的总价*/  
    select product,sum(price) from orders group by product;  
      
    /*查询购买了几类商品,并且每类总价大于100的商品*/  
    select product,sum(price) from orders group by product having sum(price)>100;  
    

    相关文章

      网友评论

          本文标题:java学习 sql操作常用语句

          本文链接:https://www.haomeiwen.com/subject/cohlaxtx.html