MySQL 数据库基本操作

create table Students(StudentID int NOT NULL, primary key (StudentID), StudentName varchar(255), Grade varchar(255), class varchar(255));

1.插入数据，

   insert into Students(StudentID,StudentName,Grade,class ) values (‘20150314',"Dubingjie","one","three");

2.更新数据， 

   select * from Students where class in (‘Two’,’three’);

    update Students set class="Two" where StudentId=‘20150314’;

3.增加表字段，

   alter table Students add Lessons char(255);

4.删除某行数据，

   delete from Students where StudentID='20150316';

5.删除表，

   drop table teachers;

6.order by排序

   select * from Students order by Lessons;

7.distinct去重显示数据，

   insert into Students(StudentID,StudentName,Grade,class ) values    (‘20150317',"Dbj","one","three");

8.AND、OR进行查找，

   select * from Students where (class='Two' or class='three') and Lessons='3';

9.IN操作，

    select * from Students where class in (‘Two’,’three’);

10.Between方法，

    select * from Students where StudentName between 'Bingjie' and 'Dbj';

11.内连接inner join.

    select Students.StudentName,Teachers.TeacherName from Students inner join Teachers on Students.class=Teachers.class;

12.Alias假名

    select Stu.StudentName,Teac.TeacherName from Students as Stu, Teachers as Teac where Stu.class=Teac.class;

13.left  join 左连接

     select Students.StudentName,Teachers.TeacherName,Students.class from Students left join Teachers on Students.class=Teachers.class order by Students.class; 

14.right  join  右连接

     select Students.StudentName,Teachers.TeacherName,Students.class from Students right join Teachers on Students.class=Teachers.class order by Students.class; 

15.备份一张表

     create table Teachers_backup as( select * from Teachers );

16.复制表结构到另一张表中

     insert into Teachers_backup select * from Teachers;

17.插入现在时间Now（）；

     update Teachers_backup set class=Now() where TeacherID='201503140';  

18.创建索引create index

     create index index_name on Teachers(TeacherID); 

19.查看索引
     show index from Teachers; 

 20.删除索引

     alter table Teachers drop index index_name;

 21.建立视图view

     create view my_view as select StudentName from Students;  

 22.日期函数

Now()  curdate() date() date_Format()

 23.NULL与0不等价，并且始终用IS NULL 来查找NULL的值

 24.AVG,count,max,first函数运用

select AVG(number) from Teachers;

select count(number) from Teachers;

select first(number) from Teachers; 

select sum(number) from Teachers; 

select max(number) from Teachers;