MySQLデータベース入門練習200文

目的:
***IT技術は特にそうである.忘れた方法を解決したほうがいいのは、多くの繰り返しだが、大きな頭を抱えて新しい目から見ると明らかに効率が悪い.データベースの入門レベルの知識については、1、2回見ても使い方が理解でき、コードの練習が欠けています.そこで最近はMySQLを再利用して入門しながら、使用しているコードをまとめて練習しています.これからの復習に使う.ある日、自分が疎かになったことに気づき、少し時間をかけて叩くと、記憶が戻ってきた.
説明:
1ほとんどの文は非常に簡単で、少量の注釈を行った.2コードは少し冗長で、熟練した操作のためです.3意図的にいくつかのエラーを設定して、データベースの返す情報に注意します;4すべてのテストはMariaDBで行われ、MariaDBとMySQLはほとんど同じですが、間違いがあれば、手を結んだり、理解したりして、責任を持ってください.5本文は全部で100文のコードで、上書きはデータベースから削除して、表は削除の修正を作成して、キーは削除を作成して、インデックスは削除の内容を作成して、それから引き続き増加します;6附《MySQL入门はとても简単です》一书电子版,可以配合练习,网盘住所,密码:77 p 4
——2016/12/14
200文について
私の前のMySQLの入門練習の100文を続けて、200文まで増加して、この部分は主にSQL言語の添削の調査に関連します.煩わしいのはクエリーで、MySQLは非常に柔軟で多様なクエリーをサポートしています.ここでも毛皮に足を踏み入れるが、操作を熟知するのに依然として有益である.一部の内容は少し詳しくないので、後で自分で練習するときに補充します.
コード#コード#

//     
101, create database testdatabase;use database testdatabase;
102, create table tt1(id int, name varchar(20),age int,sex boolean);
103, show tables;desc tt1;

//  
104, insert into tt1 values(1,"zhang",25,0);
105, insert into tt1 values(2,"wang",25,1);
106, select * from tt1;
107, insert into tt1(id,name,age,sex) values(3,"li",28,1);
108, insert into tt1(id,name,sex,age) values(4,"sun",0,22);
109, insert into tt1(id,name,sex) values(5,"zhao",30,1);
110, insert into tt1(id,age,name) values(6,"he",47,0);
111, insert into tt1(id,age,name) values(7,"chen",22,1),(7,"zhang",22,1),(7,"xie",32,1);
112, select * from tt1;

//  
113, update tt1 set id=10,name="new",age=100,sex=0 where id=1; select * from tt1;
114, update tt1 set id=11,name="new" where id=2,age=25; select *from tt1;
115, update tt1 set id=12,sex=1 where id=7; select * from tt1;
116, update tt1 set sex=1 where id>3;
117, update tt1 set sex=0 where id<4;


//  
118, delete from tt1 where id=1;select * from tt1;
119, delete from tt1 where id=12;select * from tt1;

//  
120, alter table tt1 add address varchar(30);
121, update tt1 set address="Beijing" where sex=1;
122, update tt1 set address="Shanghai" where sex=0;
//    
123, select id from tt1;
124, select id,name from tt1;
125, select id,name,address from tt1;
//    
126, select id,name,address from tt1 where address="Beijing";
127, select * from tt1 where id in(2,3,4);
128, select * from tt1 where id not in(2,3,4);
129, select * from tt1 where id between 2 and 5;
130, select * from tt1 where id not between 2 and 5;
131, select * from tt1 where address like "beijing";
132, select * from tt1 where address like "bei";
133, select * from tt1 where address like "bei%";
134, select * from tt1 where address not like "bei%";
135, select * from tt1 where address is null;
136, select * from tt1 where address is not null;
137, select * from tt1 where age<20 and sex=1;
138, select * from tt1 where sex=0 or age>30;
//       
139, select distinct address from tt1;  
//      
140, select * from tt1 order by age;
141, select * from tt1 order by age asc;
142, select * from tt1 order by age desc;
//    
143, select * from tt1 group by sex;//    group by              
//group by   group_concat    
144, select group_concat(name),sex from tt1 group by sex;
145, select group_concat(name),group_concat(age),sex from tt1 group by sex;
146, select group_concat(name,age),sex from tt1 group by sex;
//group by         
147, select sex,count(sex) from tt1 group by sex;
148, select sex,count(sex) from tt1 group by sex having sex>2;//having              
149, select sex,count(sex) from tt1 group by sex having count(sex)>2;
//with rollup
150, select sex,count(sex) from tt1 group by sex with rollup;//             ，           ，        
//limit    
151, select * from tt1;
152, select * from tt1 limit 2;
153, select * from tt1 limit 3;
154, select * from tt1 limit 0,2;
155, select * from tt1 limit 1,2;
//        
//          ，     
156, create table grade(id int,name varchar(10),subject varchar(10),score int,sex boolean);
157, insert into grade values(1,"wang","math",100,1),(1,"wang","english",96,1),(1,"wang","physics",90,1);
     insert into grade values(2,"li","math",96,1),(2,"li","english",85,1),(2,"li","physics",99,1);
     insert into grade values(3,"sun","math",85,0),(3,"sun","english",98,0),(3,"sun","physics",80,0);
158, select * from grade;
159, select count(*) from grade;
160, select id,name,sum(score) from grade where id=1;
161, select id,name,sun(score) from grade group by id;
162, select id,name,sum(score) from grade group by id order by sum(score) desc;
163, select id,name,avg(score) from grade where id=2;
164, select id,name,avg(score),sum(score) from grade where id =3;
165, select id,name,avg(score) from grade group by id;
166, select subject,avg(score) from grade group by subject;* from 
167, select subject,avg(score) from grade group by subject order by avg(score);
168, select name,max(score) from grade where subject="math";
169, select name,max(score) from grade where subject="english";
//    
    //   
170, create table stu(id int,name varchar(10),age int,sex boolean);
171, insert into stu values(1,"wang",25,1),(2,"li",23,1),(3,"sun",23,0),(4,"zhou",27,1),(5,"zhang",22,0);
172, select id,name,age,sex,score from stu,grade where stu.id=grade.id;
173, select stu.id,stu.name,stu.age,stu.sex,score from stu,grade where stu.id=grade.id;
174, select stu.id,stu.name,stu.age,stu.sex,score from stu,grade where stu.id=grade.id and score>90;
    //   
175, select stu.id,stu.name,stu.age,stu.sex,score from stu left join grade on stu.id=grade.id;
176, select stu.id,stu.name,stu.age,stu,sex,score from stu right join grade on stu.id=grade.id;
177, insert into grade values(4,"hu","math",99,1),(5,"liu","english",97,0);
178, select stu.id,stu.name,stu.age,stu.sex,subject,score from stu left join grade on stu.id=grade.id;
179, select stu.id,stu.name,stu.age,stu.sex,subject,score from stu right join grade on stu.id=grade.id;
    //   
180, select * from stu where id in (select *from grade);
181, select * from grade where id in (select * from stu);
182, create table scholarship(level int,money int,score int);
183, insert into scholarship values(1,10000,90),(2,5000,85),(3,3000,80);
184, select id,name,score from grade where score>=(select score from scholarship where level=1);
185, select id,name,score from grade where score>=(select min(score) from scholarship);
    //exists   
186, select * from stu where exists (select name from grade where id=1);
187, select * from grade where score>any(select score from scholarship);
188, select * from grade where score>all(select score from scholarship);
//      
189, select name from stu union select name from grade;
190, select name from stu union all select name from grade;
//  
191, select * from scholarship s where s.level=1;
192, select * from scholarship s where s.money=5000;
193, select s.name from stu s,grade g where s.name=g.name;
194, select subject as su,score as sc from grade;
195, select * from stu where name regexp "^w";
196, select * from stu where name regexp "g$";
197, select * from stu where name regexp "^w.g$";
198, select * from stu where name regexp "^w..g$";
199, select * from stu where name regexp "[aeo]";
200, select * from stu where name regexp "[a-g]";