MySQL: 练习 SQL语句

##########################################################################
Show databases;
create database `students` charset='utf8';
use `students`;
select database();
show create database `students`;
##########################################################################
show tables;
create table `student`(
	id int unsigned primary key auto_increment not null,
	name varchar(20) not null,
	age tinyint unsigned default 0,
	height decimal(5,2),
	weight decimal(5,2),
	gender enum('male','female','unknown'),
	class enum('cls1','cls2','cls3')
);
desc `student`;
alter table `student` rename to `stu`;
alter table `stu` add `delete` int unsigned;
alter table `stu` modify `delete` boolean;
alter table `stu` change `delete` `del` boolean;
alter table `stu` drop `del`;
##########################################################################
insert into `stu` values (0,'Tom',23,175,63,'male','cls1',0);
insert into `stu` values (0,'Bonnie',26,167,49,'female','cls2',0),(0,'Edith',27,165,51,'female','cls3',0);
insert into `stu` (`name`,`age`,`weight`,`gender`,`class`) values ('Bert',52,52,'unknown','cls1'),('Ken',27,55,'male','cls3');
#------------------------------------------------------------------------#
select * from `stu`;
select * from `stu` where `id`>2 limit 0,3;
select * from `stu` where `id`>2 order by age asc,height desc;
select `id`,`name` from `stu`;
select `s`.`id` as `序号`,`s`.`name` as `姓名` from `stu` as `s`;
select `s`.`id` as `序号`,`s`.`name` as `姓名`,`s`.`age` as `年龄`,`s`.`weight` as `体重` from `stu` as `s` inner join `stu` as `ss` on `s`.`age`=`ss`.`weight` where `s`.`id`>1;
select `id`,`name` from `stu` where `id`=1 or `id`>=3; 
select `id`,`name` from `stu` where `id` between 1 and 3;
select `id`,`name` from `stu` where `id` in (1,3);
select `id`,`name`,`height` from `stu` where `height` is null;
select `id`,`name` from `stu` where `name` like 'B%'or  `name` like'_o%';
select `id`,`name` from `stu` where not (`name` like 'B%'or `name` like'_o%');
select * from `stu` where `age`>(select avg(`age`) from `stu`);
select * from `stu` where (`age`,'height')=(select max(`age`),max(`height`) from `stu`);
select `gender`,group_concat(`name`),avg(`age`),count(*) from `stu` group by `gender` with rollup having count(*)>=2 ;
select count(*) from `stu`;
select distinct age from `stu`;
#------------------------------------------------------------------------#
update `stu` set `name`='John' where `id`=1;
#------------------------------------------------------------------------#
delete from `stu` where `id`=1;
update `stu` set `del`=True where `id`=2;
##########################################################################
create table `class`(
	id int unsigned primary key auto_increment not null,
	cls varchar(20) not null,
	qty int unsigned
);
insert into `class`(`cls`,`qty`) select `class`,count(*) from `stu` group by `class`;
drop table `class`;
#------------------------------------------------------------------------#
create table `class`(
	id int unsigned primary key auto_increment not null,
	cls varchar(20) not null,
	qty int unsigned
) select `class` as `cls`,count(*) as `qty` from `stu` group by `class`;
#------------------------------------------------------------------------#
alter table `stu` change `class` `cls_id` varchar(20);
update `stu` as `s` inner join `class` as `c` on `s`.`cls_id`=`c`.`cls` set `s`.`cls_id`=`c`.`id`;
alter table `stu` change `cls_id` `class` varchar(20);
update `stu` as `s` inner join `class` as `c` on `s`.`class`=`c`.`id` set `s`.`class`=`c`.`cls`;
##########################################################################
create table `teacher`(
	id int unsigned primary key auto_increment not null,
	name varchar(20) not null,
	class varchar(20) not null
);
insert into `teacher` values (0,'teacher1','cls1'),(0,'teacher2','cls2'),(0,'teacher3','cls3');
alter table `class` add index `class_cls` (`cls`);
show index from `class`;
alter table `teacher` add foreign key(`class`) references `class`(`cls`);
drop table `teacher`;
alter table `class` drop index `class_cls`;
#------------------------------------------------------------------------#
create table `teacher`(
	id int unsigned primary key auto_increment not null,
	name varchar(20) not null,
	class varchar(20) not null,
	foreign key(`class`) references `class`(`cls`)
);
insert into `teacher` values (0,'teacher1','cls1'),(0,'teacher2','cls2'),(0,'teacher3','cls3');
show create table `teacher`; #查看外键约束名称
alter table `teacher` drop foreign key 外键名;
##########################################################################


SQL语句功能	对应的动词
数据查询	SELECT
数据定义	CREATE、ALTER、DROP
数据操纵	INSERT、UPDATE、DELETE
数据控制	GRANT、REVOKE
SQL语句的编写顺序
SELECT ...
FROM ...
WHERE ... 
GROUP BY ... HAVING ...
ORDER BY ...
比较运算符查询: 等于 = , 大于 > , 大于等于 >= , 小于 < , 小于等于 <= , 不等于 != , 或 <>
逻辑运算符查询: and , or , not
模糊查询: like是模糊查询关键字 , %表示任意多个任意字符 , _表示一个任意字符
范围查询: between...and...表示在一个连续的范围内查询 , in表示在一个非连续的范围内查询
空判断查询: 判断为空使用 is null , 判断非空使用 is not null (不能使用 where `xx` = / ! = null 判断空/非空, null 不等于 '' 空字符串)
排序: 升序 asc , 降序desc
分页查询: limit start,count
聚合函数: 指定列总行数 count(col) #count(*)返回包含null值记录的总行数 , 指定列最大值 max(col) , 指定列最小值 min(col) , 指定列的和 sum(col) , 指定列平均值 avg(col)
连接查询: 内连接/自连接 [inner] join...on... , 左连接/右连接 left/right [outer] join...on...
事务: 开启事务 begin / start transaction; 取消自动提交 set autocommit = 0; 回滚事务 rollback; 提交事务 commit;