2016.9.23 Small Program---Database

Student table: T_student
Number s_id shaping primary key auto-increment
Name: S_name string does not allow duplicates
Gender: S_sex String Default value male
Age: S_age Plastic
Class: S_class string

Build tables and add data with no less than 10 data, and complete the following topics

1. Change the name of Liu Ji to Liu Bowen
2. Tong Pak-fu age 20, sex male, Class is T130, add to Table
3. Find out all the information about all the people whose names include Bobbie
4, check the information of all persons aged 10-20
5, query for information about the first 5 people aged 10-20
6, the age of 10-20, and the age of each person plus 10.
7, inquires the T130 class all boys.
8. Delete the record with ID 3
9, display all personnel information, and show the age level: Under 18 years of age for minors, 18-30 for youth, 30-50 for the prime, 50 above for the elderly

1 CREATE TABLET_student (2s_idINT PRIMARY KEYAuto_increment,3S_nameVARCHAR( -)UNIQUE,4S_sexVARCHAR(2)DEFAULT 'male'  CHECK(S_sexinch('male','female') ) ,5S_ageINT,6S_classVARCHAR(Ten)7 8 );9 SELECT *  fromt_student;Ten DROP TABLEt_student; One  A INSERT  intot_student (S_name,s_sex,s_age,s_class) - VALUES('Zhang San','male', +,'T130'), -('Liubebe','male', -,'T130'), the('Liu Ji','male', One,'T130'), -('Zhang Ling','female', at,'T130'), -('Zhang Boshan','male', *,'T130'), -('Li Yihu','male', -,'T130'), +('Tangmei','female', to,'T130'), -('John Doe','male', -,'T130'), +('Lifei','female', +,'T130'), A('Sun','female', -,'T130'), at('Harry','male', -,'T130'), -('Wang Yibe','male',8,'T130'), -('Liu Bocheng','male', -,'T130'), -('Zhang June','male', +,'T130'), -('China','female', -,'T130'), -('Chanzhong','male', the,'T130'), in('Wang Junmei','female', -,'T130'), -('Zhou Bushan','female', -,'T130'), to('Wu Sugar','female', -,'T130'); + --1. Change the name of Liu Ji to Liu Bowen - UPDATET_studentSETS_name='Liu Bowen' WHERES_name='Liu Ji'; the --2. Tong Pak-fu age 20, sex male, Class is T130, add to Table * INSERT  intoT_student (S_name,s_sex,s_age,s_class)VALUES('Pak Fu','male', -,'T130'); $ --3. Find out all the information about all the people whose names include BobbiePanax Notoginseng SELECT *  fromT_studentWHERES_name like '% Job%'; - --4, check the information of all persons aged 10-20 the SELECT *  fromT_studentWHERES_age>Ten  andS_age< -; + --5, query for information about the first 5 people aged 10-20 A SELECT *  fromT_studentWHERES_age>Ten  andS_age< -LIMIT0,5 the --6, will be the age of 10-20 of all personnel, each person's age plus ten + UPDATET_studentSETS_age=S_age+Ten WHERES_age>Ten  andS_age< -  - --7, inquires the T130 class all boys.  $ SELECT *  fromT_studentWHERES_sex='male'  andS_class='T130' $ --8. Delete the record with ID 3 - DELETE  fromT_studentWHEREs_id=3 - --9, display all personnel information, and show the age level: Under 18 years of age for minors, 18-30 for youth, 30-50 for the prime, 50 above for the elderly the SELECTU.*,( Case  whenS_age< -  Then 'Minors' -  whenS_age>= -  andS_age< -  Then 'Youth'Wuyi  whenS_age>= -  andS_age< -  Then 'Prime' the ELSE 'old age' END -  Wu) Agegrade fromT_student u;

2016.9.23 Small Program---Database