MySQL Basic operation exercise

1 Create TableEmployee (namevarchar( -), SexChar(2), ageint, addressvarchar( -));2 3 Insert  intoEmployeeValues('Zhang San','female', +,'Beijing');4 Insert  intoEmployeeValues('John Doe','male', -,'Shanghai');5 Insert  intoEmployeeValues('Harry','female', -,'Guangzhou');6 Insert  intoEmployeeValues('Shili','female', -,'Beijing');7 Insert  intoEmployeeValues('Harry','male', A,'Beijing');8 Insert  intoEmployeeValues('Zhao Qi','male', -,'Shanghai');9 Insert  intoEmployeeValues('Zhang Si','female', at,'Beijing');Ten  One#（1). Write the SQL statement and query all employees older than 20 (2 points) A Select * fromEmployeewhereAge> -; -#（2). Write the SQL statement and query all female employees younger than 25 years old (3 points) - Select * fromEmployeewhereSex='female'  andAge< -; the#（3). Write the SQL statement to count the number of male and female employees (3 points) -#Countfunction - Select Count(*) fromemployee; #统计表元素个数 - Select Count(*) fromEmployeewhereSex='female'; #统计女生个数 + SelectSexCount(*) fromEmployeeGroup  bysex;#Group  bySex groups by gender, grouped by sex tags - SelectSexCount(*) asNum fromEmployeeGroup  bysex;# asalias from +  A#（4). Write SQL statements to get employee information in reverse chronological order (3 points) at Select *  fromEmployeeOrder  byAgedesc; -#（5). Write the SQL statement to get the name of the employee who has a duplicate (3 points) - SELECT *   -    fromEmployee -  WHERENameinch(SELECTname -                    fromEmployee in                  GROUP  byname -                  having COUNT(*)> 1)   to#（6). Write the SQL statement and query all the employees surnamed Zhang (3 points) + Select *  fromEmployeewhereName like '% sheet%'#模糊查询, with a single word - Select *  fromEmployeewhereName like 'Zhang%'; #只要姓张 the #占位符写法 * Select *  fromEmployeewhereName like 'Zhang __'; $#（7). Write the SQL statement to find the first 3 records of Beijing (3 points)Panax Notoginseng Select *  fromEmployeewhereAddress='Beijing'  Order  byNameASCLimit0,3; #从下标几开始, several - Select *  fromEmployee limit3,3; the#（8). Write the SQL statement and query the total number of employees (3 points) + Select Count(*) asAllnum fromemployee; A#（9). Write the SQL statement and insert a record (2 points) into the table the Insert  intoEmployee (name,sex,age,address)Values('77','male', -,'Shenzhen'); +#（Ten). Write the SQL statement and modify the address of the employee Zhang Xi Nanjing (2 min.) - UpdateEmployeeSetAddress='Nanjing' whereName='Zhang Si'; $#（ One). Write out the SQL statement to delete female employees older than 24 years (2) $ Delete  fromEmployeewhereAge> -  andSex='female';

View Code

MySQL Basic operation exercise