forked from pingcap/mysql-tester
-
Notifications
You must be signed in to change notification settings - Fork 6
/
Copy pathselect.test
169 lines (169 loc) · 8.12 KB
/
select.test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
drop table if exists t1;
create table t1 (spID int,userID int,score smallint);
insert into t1 values (1,1,1);
insert into t1 values (2,2,2);
insert into t1 values (2,1,4);
insert into t1 values (3,3,3);
insert into t1 values (1,1,5);
insert into t1 values (4,6,10);
insert into t1 values (5,11,99);
select userID, MIN(score) from t1 group by userID order by userID desc;
select userID, MIN(score) from t1 group by userID order by userID asc;
select userID, SUM(score) from t1 group by userID order by userID desc;
select userID as a, MIN(score) as b from t1 group by userID order by userID;
select userID as user, MAX(score) as max from t1 group by userID order by user;
select userID as user, MAX(score) as max from t1 group by userID order by max desc;
select userID,count(score) from t1 group by userID having count(score)>1 order by userID;
select userID,count(score) from t1 where userID>2 group by userID having count(score)>1 order by userID;
select distinct spID,userID from t1;
select distinct spID,userID from t1 where score>2;
select distinct spID,userID from t1 where score>2 order by spID asc;
select distinct spID,userID from t1 where spID>2 order by userID desc;
select userID,MAX(score) from t1 where userID between 2 and 3 group by userID order by userID;
select userID,MAX(score) from t1 where userID not between 2 and 3 group by userID order by userID desc;
select spID,userID,score from t1 limit 2,1;
select spID,userID,score from t1 limit 2 offset 1;
select sum(score) as sum from t1 where spID=6 group by score order by sum desc;
select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t1;
drop table if exists a;
create table a(a int);
insert into a values(1),(2),(3),(4),(5),(6),(7),(8);
select count(*) from a where a>=2 and a<=8;
drop table if exists t1;
create table t1 ( id int, name varchar(50) );
insert into t1 values (1, 'aaaaa');
insert into t1 values (3, "aaaaa");
insert into t1 values (2, 'eeeeeee');
select distinct name as name1 from t1;
drop table if exists t2;
create table t2(name char(10),owner char(10), species char(10), gender char(1), weight float,age int);
insert into t2 values ('Sunsweet01','Dsant01','otter','f',30.11,2), ('Sunsweet02','Dsant02','otter','m',30.11,3);
insert into t2(name, owner, species, gender, weight, age) values ('Sunsweet03','Dsant01','otter','f',30.11,2), ('Sunsweet04','Dsant02','otter','m',30.11,3);
select * from t2 limit 2, 18446744073709551615;
drop table if exists t3;
create table t3 (spID int,userID int,score smallint);
insert into t3 values (1,1,1);
insert into t3 values (2,2,2);
insert into t3 values (2,1,4);
insert into t3 values (3,3,3);
insert into t3 values (1,1,5);
insert into t3 values (4,6,10);
insert into t3 values (5,11,99);
select userID,MAX(score) max_score from t3 where userID <2 || userID > 3 group by userID order by max_score;
select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t3 where userID > 3 ;
select CAST(userID AS CHAR) userid_cast, userID from t3 where CAST(spID AS CHAR)='1';
select CAST(userID AS DOUBLE) cast_double, CAST(userID AS FLOAT(3)) cast_float , CAST(userID AS REAL) cast_real, CAST(userID AS SIGNED) cast_signed, CAST(userID AS UNSIGNED) cast_unsigned from t3 limit 2;
select * from t3 where spID>2 AND userID <2 || userID >=2 OR userID < 2 limit 3;
select * from t3 where (spID >2 or spID <= 2) && score <> 1 AND userID/2>2;
select * from t3 where spID >2 || spID <= 2 && score !=1 limit 3;
select userID,MAX(score) max_score from t3 where userID <2 || userID > 3 group by userID order by max_score;
select * from t3 where userID/2>2;
drop table if exists t4;
create table t4(c1 int, c2 int);
insert into t4 values (-3, 2);
insert into t4 values (1, 2);
select c1, -c2 from t4 order by -c1 desc;
drop table if exists t5;
create table t5(a int,b varchar(10),c varchar(10));
insert into t5 values(1,'ab','cd'),(2,'ba','dc'),(3,'bc','de'),(4,'cb','ed'),(5,'cd','ef'),(6,'dc','fe'),(2,'de','fg'),(1,'ed','gf');
select * from t5 where (b='ba' or b='cb') and (c='dc' or c='ed');
drop table if exists tbl_01;
create table tbl_01 (col1 int, a bigint unsigned, c char(10) not null);
insert into tbl_01 values (1,1,"a");
insert into tbl_01 values (2,2,"b");
insert into tbl_01 values (2,3,"c");
insert into tbl_01 values (3,4,"E");
insert into tbl_01 values (3,5,"C");
insert into tbl_01 values (3,6,"D");
drop table if exists t1;
drop table if exists t2;
create table t1 (id int primary key);
create table t2 (id int);
insert into t1 values (75);
insert into t1 values (79);
insert into t1 values (78);
insert into t1 values (77);
insert into t1 values (104);
insert into t1 values (103);
insert into t1 values (102);
insert into t1 values (101);
insert into t1 values (105);
insert into t1 values (106);
insert into t1 values (107);
insert into t2 values (107),(75),(1000);
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id order by t1.id desc;
drop table if exists t1;
create table t1 (
value64 bigint unsigned not null,
value32 int not null
);
insert into t1 values(17156792991891826145, 1);
insert into t1 values(9223372036854775807, 2);
select * from t1;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (libname1 varchar(21) not null primary key, city varchar(20));
create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60));
create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int);
insert into t2 values ('001','Daffy','Aducklife');
insert into t2 values ('002','Bugs','Arabbitlife');
insert into t2 values ('003','Cowboy','Lifeontherange');
insert into t2 values ('000','Anonymous','Wannabuythisbook?');
insert into t2 values ('004','BestSeller','OneHeckuvabook');
insert into t2 values ('005','EveryoneBuys','Thisverybook');
insert into t2 values ('006','SanFran','Itisasanfranlifestyle');
insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
insert into t3 values('000','NewYorkPublicLibra',1);
insert into t3 values('001','NewYorkPublicLibra',2);
insert into t3 values('002','NewYorkPublicLibra',3);
insert into t3 values('003','NewYorkPublicLibra',4);
insert into t3 values('004','NewYorkPublicLibra',5);
insert into t3 values('005','NewYorkPublicLibra',6);
insert into t3 values('006','SanFransiscoPublic',5);
insert into t3 values('007','BerkeleyPublic1',3);
insert into t3 values('007','BerkeleyPublic2',3);
insert into t3 values('001','NYC Lib',8);
insert into t1 values ('NewYorkPublicLibra','NewYork');
insert into t1 values ('SanFransiscoPublic','SanFran');
insert into t1 values ('BerkeleyPublic1','Berkeley');
insert into t1 values ('BerkeleyPublic2','Berkeley');
insert into t1 values ('NYCLib','NewYork');
select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1;
drop table if exists t1;
create table t1(a int,b varchar(5));
insert into t1 values(1,'a');
insert into t1 values(null,null);
insert into t1 values(null,'b');
insert into t1 values(1,null);
select avg(a),b from t1 group by b order by b;
drop table if exists t1;
CREATE TABLE t1 (a int default NULL);
INSERT INTO t1 VALUES (NULL),(NULL);
select * from t1;
DROP TABLE if EXISTS t1;
DROP TABLE if EXISTS t2;
create table t1 (a int, b int);
insert into t1 values(10,null);
create table t2 (c int, d int);
insert into t2 values(20,null);
drop table if exists t1;
CREATE TABLE t1 (a int default null, b varchar(16) default null, c datetime DEFAULT null);
INSERT INTO t1(a, c) values (1,"2003-01-14 03:54:55");
INSERT INTO t1(a, c) values (1,"2004-01-14 03:54:55");
INSERT INTO t1(a, c) values (1,"2005-01-14 03:54:55");
INSERT INTO t1(a, b) values (1,"2022year");
INSERT INTO t1(b,c) values ("2022year","2003-01-14 03:54:55");
INSERT INTO t1(b,c) values ("2021year","2003-01-14 03:54:55");
INSERT INTO t1(b,c) values ("2020year","2003-01-14 03:54:55");
select max(a),b,c from t1 group by b,c order by b,c;
drop table if exists t1;
create table t1(i int);
insert into t1 values(1),(2),(3),(4),(5);
insert into t1 values(null);
select count(*) from t1 where i=2;
drop table if exists t1;
CREATE TABLE t1 (c0 varchar(0) DEFAULT NULL);
insert into t1 values();
insert into t1 values('');
select * from t1;