SQL语句回忆（一）

mysql> create table chen(`id` int(5) primary key not null,`name` char(10));   建表语句[列名不是单引号是反撇号]
mysql> create table chen( `id` int(4) AUTO_INCREMENT primary key not null ,`name` varchar(10) default "*");  #比较复杂的建表语句
mysql> create table math(`id` int,foreign key(`id`) references chen(`id`));  #设置外键约束
mysql> insert into chen values(1,'qq');  #插入语句
mysql> create table sd(`id` int primary key not null default '1' comment 'as');  #创建表有默认值和注释
mysql> create table fg like math;  快速建表
mysql> drop table math 删除表
mysql> update fg set `id`=2   where `name`='chen'   #更新语句
mysql> delete from fg where `id`=3;  #删除语句
mysql> alter table fg add `name` char(12) default 'qq' comment 'dd';  #增加表列
mysql> alter table fg drop `name`;#删除表的列
mysql> SET FOREIGN_KEY_CHECKS=0;  #设置外键检查为0
mysql> status;   #显示mysql版本信息及其他
mysql> drop database if exists text;  #判断数据库是否存在删除数据库
mysql> show databases;   #展示数据库列表
mysql> show tables;  展示表列表
mysql> drop table if exists student;  #判断表是否存在，删除表
mysql> create table student(id int auto_increment primary key,namevarchar(50),sex varchar(20),date varchar(50),content varchar(100))default charset=utf8;  3先建表
mysql> insert into student values(null,'aa','男','1988-10-2','......');
mysql> insert into student values(null,'bb','女','1889-03-6','......');
mysql> insert into student values(null,'cc','男','1889-08-8','......');
mysql> insert into student values(null,'dd','女','1889-12-8','......');
mysql> insert into student values(null,'ee','女','1889-09-6','......');
mysql> insert into student values(null,'ff','null','1889-09-6','......');  #插入语句
#练习查询语句
mysql> select * from student where `id`>1 and `id`<3;
mysql> select * from student where `id`=2 or `name`='aa';
mysql> select * from student where `id` in (1,2,3);
mysql> select * from student limit 2,5;  #查询第三到六条的记录
mysql> select * from student order by `id` desc  #降序排列
mysql> select count(`id`) as count,`sex` from student group by `sex`; #以性别分组，查询每组人数
mysql> select min(`id`) as min_id,`sex` from student  group by `sex`;  #以性别分组，查询每组最小的id
mysql> select sum(`id`) as sum_id,`sex` from student group by `sex`;  #以性别分组，查询每组的总id
mysql> select avg(`id`) as avg_id,`sex` from student group by `sex`;  #以性别分组，查询每组的id平均数
mysql> select max(`id`) as max_id,`sex` from student group by `sex`;  #查询每组的最大id
mysql> select * from student where `id` between 3 and 4;  #查询三到四的记录（包括三和四）
mysql> alter table student_bak rename to student;  #修改表名
mysql> alter table student change `name` `name_b` varchar(10);  #修改表的列名
mysql> create table df(`id` int ,`name` char(10),primary key (`id`))default charset=utf8;
mysql> create table df(`id` int primary key,`name` char(10),unique (`name`))default charset=utf8;  #设置唯一键
mysql> alter table sd alter id set default "123";
mysql> alter table sd add(`name` char(10) not null);  #增加列
mysql> grant all privileges on *.* to root@'%' identified by '123456';  #授予root用户操作所有数据库和表的权限