create database `test` charset utf8 ; #创建数据库
drop database `test` ; #删除数据库
create database `test` ;#创建数据库
use test ;#选择数据库
create table `user` (
`id` int not null ,
`name` VARCHAR(255) not null
)charset utf8; #创建表
Alter table `user` add column `grade` int not null DEFAULT 0; #增加一个字段
create table `user2` like `user`; #使用旧表创建新表
create table `user3` as SELECT `id`,`name` FROM `user`; #使用旧表创建新表
drop table `user2`,`user3`; #删除表
ALTER TABLE `user` ADD PRIMARY KEY (`id`); #添加主键
ALTER TABLE `user` ADD UNIQUE (`id`); #添加唯一索引
ALTER TABLE `user` ADD INDEX `name` (`name`); #添加索引
create index `grade` on `user`(`grade`) ; #添加索引
ALTER TABLE `user` DROP INDEX `id`;#删除索引
drop index `grade` on `user` ;#删除索引
ALTER table `user` modify `id` int not null AUTO_INCREMENT; #设置自增(必须是主键)
insert into `user`(`name`,`grade`) values('小明','100'),('小红','99'),('小刚','90'); #添加数据
delete from `user` where `name`='小明'; #删除数据
update `user` set `grade`= 100 where `name`='小红';#修改数据
select * from `user` where `grade`>90 order by `grade` desc; #查询数据
select count(*) as `totalcount` from `user`; #总数
select sum(`grade`) as `sumvalue` from `user`; #求和
select avg(`grade`) as `avgvalue` from `user`; #平均值
select max(`grade`) as `maxvalue` from `user`; #最大
select min(`grade`) as `minvalue` from `user`; #最小