mysql常用语句

mysql常用语句


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`; #最小


Comments are closed.