随便写的sql语句

随便写的简单的sql语句，贴上来便于以后回顾

mysql -uroot -p; #输入密码，登录数据库

show databases; #查看所有数据库

create database zqs_db character set utf8; #创建数据库并设置编码为utf8

use zqs_db; #选择数据库

show tables; #查看当前数据库中所有的数据表


#创建数据表
create table student(
	id int unsigned not null auto_increment primary key,
	name char(8) not null,
	sex enum('男','女') not null default '男',
	age tinyint unsigned not null,
	tek char(13) not null default '-'
	#foreign key(classid) references class(id);#创建外键
);


#插入数据（全部字段）
insert into student values(null,'张三','男',21,15212344321);
#插入部分数据
insert into student (name) values('周一');


#查询数据
select * from student where age>18 and sex='男';


#删除数据 
delete from student where age<18 and tel = '-';


#添加字段
alter table student add address char(60) not null after id;
#删除字段
alter table student drop address;
#修改字段名称
alter table student change tek tel char(13) not null default '-';


#explain select * from student;
#show status;
#show status like 'uptime'; #当前mysql 运行时间
#show status like 'connections'; #查看数据库的链接数量
#show status like 'slow_queries'; #显示慢查询
#set long_query_time=2;#即修改慢查询时间为2秒 
#show variables like 'long_query_time';
#create index name_index  on student(name);#创建普通索引
#explain select name from student; 
#create unique index id_un on student(id); #创建为一索引
#explain select * from student where id>1;
#create fulltext index address_ft on student(name);#创建全文索引
#explain select * from student where match(name)against('周一');


#show index from student; #查看指定表的全部索引
#show keys from student; #查看指定表的全部索引
select id,sex,avg(age) as age_avg,sum(age) as age_sum,count(*) as count
from student
where id>1  #where 必须出现在group by前
group by sex
having age_sum >100; #having 必须出现在 group by 后