十一，数据库的增删改查基础

show DATABASES;--查询所有数据库

2，新增数据库：create database dataname;

create DATABASE xinjing;

3,使用数据库，

use xinjing;

4,新建数据表，

create table student(id int(4) primary key,name char(20),age int(4));

5，查询所有数据表

show tables;

6，删除表

drop table student;

7，修改表名字

alter table student rename stu; 

8，查看表结构

desc stu;

9，增加表字段

alter table stu add sex char(4); 
alter table stu add sex char(10) not null;

10，删除表中的字段

alter table stu drop sex;

11，修改表中字段的属性

alter table stu change sexx sex char(4) not null;

12，删除表中的所有主键

alter table stu drop primary key; 

13，增加新主键

alter table stu change id id int(4) PRIMARY key;

14，查询表中的所有数据

SELECT * from stu;

15，插入一行

insert into stu(id,name,age,sex) values (100,'zhang',24,'nv');

16，插入多行数据

insert into stu(id,name,age,sex) values (101,'zhang',24,'nv'),(102,'zhang',24,'nv');

17，删除一行

delete from stu where id=100;

18，修改更新某些行数据

update stu set sex='nan' where id=102;

19，查询

select * from stu where id=102;

20，根据排序查询，默认升序，降序要加desc

select * from stu order by age;
select * from stu order by age desc;

21，查询

select * from stu order by age limit 0,1;
select id,max(age) from stu;
select id,max(age),sex from stu group by sex;
select name as "名字",(math+chinese+english) as "总分" from students where address like "湖南%"

select name as "姓名",total as "总分" from (select name,(math+chinese+english) as total from students where address like "湖南%")s1,(select max(math+chinese+english) as max from students where address like "湖南%")s2 where s1.total=s2.max;

22，给查出来的列改名字

select name as "姓名" ,sex as "性别" from stu;