MYSQL和ORACLE增删改查

抛开ORM框架后的数据库操作（增删改查）-mysql和oracle版本

1、MYSQL——增删改查

增:  增加字段----alter table Student addcolumnscore varchar(20);

增加数据----insert into table Student(name)values(‘guoling’);

删:  删除字段----alter table Student drop columnscore;

删除数据----delete from Student where name=’guoling’;

删除表中所有数据----truncate table  Student;/deletefromStudent;

删除表----drop table Student;

删除数据库----drop database student;

改:  改字段名称—-alter table Student change namesName varchar(20);

     改字段类型、长度和约束----alter table Studentmodify  name varchar(30) not null;

     改表名----rename table Student to Teacher;

     改表中数据----update Student set name=’guoling’;

查:   查询表中所有数据----select *from Student;

查询排出了重复的数据---select distinct name from Student;

     查询数据使用别名表示----selectname math+chinese+English as Total  fromStudent;

     根据条件查询----where:select* from Student where name=’guoling’;

                 ----相等=不等<>:select  * from  Student where English>=90and math<=80;        

                 ----between…and…:select* from Student where English between 90 and 100;

                 ----in(value1,value2,value3):select* from Student where English in(65,75,85); 

               ----like模糊查询:select *from Student where name like ‘%guo%’;

               ----isnull:select * from Student where English is null;

2、ORACAL增删改查

 

增：增加字段----alter  table Student add(score number (varchar(20));

        增加数据----insertinto Student(name)values('guoling');

删：删除字段----alter  table Student drop column  score;

        删除数据----deletefrom Student where name='郭玲';

        删除表中所有数据----truncatetable Student;/delete table Student;

        删除表----droptable Student;  

        |*MYSQL:drop table ifexists Student;(oracle 米有if exits关键字)

        删除数据库----dropdatabase student;

改： 改字段名称----alter tableStudent rename column name to sName;

        |* MYSQL: alter tableStudent change column name sName varchar(20);

        改字段类型、长度和约束----altertable Student motify  (name varchar(10));

        改表名---- rename Student to Teacher ;

        改表中数据----updateStudent set name=‘guoling’;     

查：查询表中所有数据----select  *  fromStudent ;

         查询排除了重复的数据----selectdistinct name from Student;

         查询数据使用别名表示----select name math+chinese+English as Total from Student;

        根据条件查询----  where:where:select * from Student where name=’guoling’;

                    ----  相等=不等<>:select  * from  Student where English>=90and math<=80;      

                    ---- between ..and:    select  *  fromStudent where  english between 80 and 90;

                    ---- in(value1,value2,value3):   select * from Student  whereenglish in(65,75,85);

                    ----   like模糊查询:   select name from Student where sex like'%ling%';

                    ----  isnull:select * from Student where English is null;