mysql test

75 阅读 0 评论 50 点赞

我是靠谱客的博主开放太阳，最近开发中收集的这篇文章主要介绍mysql test，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

create database mytest;
use mytest;
create table stus(id int(11) not null primary key auto_increment, name varchar(10) not null, age int(11) not null, addr varchar(11), classId int(11) not null);
create table class(id int(11) not null primary key auto_increment, classId int(11) not null, stuNum int(11) not null);
alter table class add className varchar(11);

rename table stus to sts;
rename table sts to stus;

insert into stus(id, name, age, addr, classId) values(0,'tom',20,'bj',1),(0, 'kjq',23,'gs4',1),(0, 'qsi',18,'fj4',2),(0, 'rfm',26,'zf0',2),(0, 'xrc',28,'qf4',1),(0, 'kza',26,'sn1',1),(0, 'fwg',30,'fj4',1),(0, 'vlo',16,'ds3',1),(0, 'uud',16,'hw5',2)

insert into class values(0, 1, 12, '111');
insert into class values(0, 2, 20, '222');
insert into class values(0, 3, 21, '333');

update class set stuNum=22 where id=1;

desc class;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| classId | int(11) | NO | | NULL | |
| stuNum | int(11) | NO | | NULL | |
| className | varchar(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+

mysql> desc stus;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| addr | varchar(11) | YES | | NULL | |
| classId | int(11) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+

alter table stus add gender bit not null;
update stus set gender=id%2;
聚合分组
select classId, count(classId) from stus group by classId ;

alter table my_tab1 add [constraint 外键名] foreign key(外键字段名) references mytab2(主键字段名);

mysql> select * from class inner join stus on class.id=stus.classId;
+----+---------+--------+-----------+----+------+-----+------+---------+--------+
| id | classId | stuNum | className | id | name | age | addr | classId | gender |
+----+---------+--------+-----------+----+------+-----+------+---------+--------+
| 1 | 1 | 22 | 111 | 1 | tom | 20 | bj | 1 | |
| 1 | 1 | 22 | 111 | 5 | erq | 10 | bk3 | 1 | |
| 1 | 1 | 22 | 111 | 7 | ytp | 10 | ly4 | 1 | |
| 1 | 1 | 22 | 111 | 10 | tom | 20 | bj | 1 | |
| 1 | 1 | 22 | 111 | 11 | kjq | 23 | gs4 | 1 | |
| 1 | 1 | 22 | 111 | 14 | xrc | 28 | qf4 | 1 | |
| 1 | 1 | 22 | 111 | 15 | kza | 26 | sn1 | 1 | |
| 1 | 1 | 22 | 111 | 16 | fwg | 30 | fj4 | 1 | |
| 1 | 1 | 22 | 111 | 17 | vlo | 16 | ds3 | 1 | |
| 2 | 2 | 12 | 222 | 2 | jsm | 29 | kn5 | 2 | |
| 2 | 2 | 12 | 222 | 6 | hky | 16 | vq5 | 2 | |
| 2 | 2 | 12 | 222 | 8 | hxq | 30 | ln5 | 2 | |
| 2 | 2 | 12 | 222 | 12 | qsi | 18 | fj4 | 2 | |
| 2 | 2 | 12 | 222 | 13 | rfm | 26 | zf0 | 2 | |
| 2 | 2 | 12 | 222 | 18 | uud | 16 | hw5 | 2 | |
| 3 | 3 | 23 | 333 | 3 | agt | 30 | iq0 | 3 | |
| 3 | 3 | 23 | 333 | 4 | amj | 19 | qt1 | 3 | |
| 3 | 3 | 23 | 333 | 9 | bws | 22 | uf3 | 3 | |
+----+---------+--------+-----------+----+------+-----+------+---------+--------+

mysql> select * from class left join stus on class.id=stus.classId;
+----+---------+--------+-----------+------+------+------+------+---------+--------+
| id | classId | stuNum | className | id | name | age | addr | classId | gender |
+----+---------+--------+-----------+------+------+------+------+---------+--------+
| 1 | 1 | 22 | 111 | 1 | tom | 20 | bj | 1 | |
| 1 | 1 | 22 | 111 | 5 | erq | 10 | bk3 | 1 | |
| 1 | 1 | 22 | 111 | 7 | ytp | 10 | ly4 | 1 | |
| 1 | 1 | 22 | 111 | 10 | tom | 20 | bj | 1 | |
| 1 | 1 | 22 | 111 | 11 | kjq | 23 | gs4 | 1 | |
| 1 | 1 | 22 | 111 | 14 | xrc | 28 | qf4 | 1 | |
| 1 | 1 | 22 | 111 | 15 | kza | 26 | sn1 | 1 | |
| 1 | 1 | 22 | 111 | 16 | fwg | 30 | fj4 | 1 | |
| 1 | 1 | 22 | 111 | 17 | vlo | 16 | ds3 | 1 | |
| 2 | 2 | 12 | 222 | 2 | jsm | 29 | kn5 | 2 | |
| 2 | 2 | 12 | 222 | 6 | hky | 16 | vq5 | 2 | |
| 2 | 2 | 12 | 222 | 8 | hxq | 30 | ln5 | 2 | |
| 2 | 2 | 12 | 222 | 12 | qsi | 18 | fj4 | 2 | |
| 2 | 2 | 12 | 222 | 13 | rfm | 26 | zf0 | 2 | |
| 2 | 2 | 12 | 222 | 18 | uud | 16 | hw5 | 2 | |
| 3 | 3 | 23 | 333 | 3 | agt | 30 | iq0 | 3 | |
| 3 | 3 | 23 | 333 | 4 | amj | 19 | qt1 | 3 | |
| 3 | 3 | 23 | 333 | 9 | bws | 22 | uf3 | 3 | |
| 4 | 4 | 54 | 444 | NULL | NULL | NULL | NULL | NULL | NULL |
+----+---------+--------+-----------+------+------+------+------+---------+--------+