"MySQL" MySQL common build Table statement

Reference: http://blog.csdn.net/kakane/article/details/7401111

"1" Create Employee profile form
Requirements field: Employee employee number, employee name, gender, salary, email, entry time, department.


"2" reasonable choice of data type and field modifiers, requires not null,auto_increment, primary key and so on.

[SQL]View PlainCopy

2. --
4. --Make by Kakane
6. --
12. DROP TABLE IF EXISTS ' workers_info ';
14. CREATE TABLE ' Workers_info ' (
16. ' ID ' int (one) not NULL auto_increment,
18. ' Workername ' varchar (not NULL) ,
20. ' Sex ' enum (f,m,s),
22. ' Salary ' int (one) DEFAULT ' 0 ',
24. ' Email ' varchar (+),
26. ' Employeddates ' date,
28. ' Department ' varchar (+),
30. PRIMARY KEY (' id ')
32. ) Engine=myisam DEFAULT Charset=utf8;
38. mysql> alter table workers_info ADD sex enum (' F ',' M ',' S ');

[SQL]View PlainCopy

"3" View the structure of the table

[HTML]View PlainCopy

1. MySQL> desc workers_info;

"4" added QQ and Tel fields, require tel field before email, require the entry time is the last field

mysql> ALTER TABLE workers_info ADD Tel varchar (all) after salary;

[SQL]View PlainCopy

1. mysql> ALTER TABLE workers_info ADD qq int;
2. ALTER TABLE workers_info MODIFY employeddates date after QQ;

"5" Change the email field to mailbox
mysql> ALTER TABLE workers_info Change email mailbox varchar (30);

"6" adds 10 records to the table
Mysql> INSERT into workers_info values (NULL, ' Xing ', 10000, ' 1598232123 ', ' [email protected] ', ' Yanfa ', 736019646,20121221);

[SQL]View PlainCopy

1. Mysql> INSERT into workers_info (workername,salary,tel,mailbox,department,qq,employeddates) values (  ' Xing ', 10000,' 1598232123 ',' [email protected] ',' Yanfa ', 736019646,20121221);

"7" Modify the Tel and mailbox of two records
Mysql> UPDATE workers_info SET mailbox = ' [email protected] ' WHERE id = 14;

[SQL]View PlainCopy

1. mysql> UPDATE workers_info SET mailbox = ' [email protected] ', tel=' 1246543423 ' where id = 13;

"8" view added records
Mysql> select * from Workers_info;

"9" View name and entry time record
Mysql> select Workername,employeddates from Workers_info;

"10" inquiry into the employment time before 2003

[SQL]View PlainCopy

1. Mysql> SELECT * from workers_info where year (Employeddates) < 2003;

[SQL]View PlainCopy

"11" Search for the highest and lowest employee names

[SQL]View PlainCopy

1. Mysql> SELECT * from workers_info ORDER by salary limit 1;
2. Mysql> SELECT * from workers_info ORDER by salary desc LIMIT 1;

"12" Query average salary

[SQL]View PlainCopy

1. mysql> Select avg (Salary) from Workers_info;

[SQL]View PlainCopy

"13" Statistics of male employees, number of female employees

[SQL]View PlainCopy

1. mysql> Select count (male) from workers_info where sex="M";
2. mysql> Select count (male) from workers_info where sex="M";

"14" is sorted according to the time of entry and shows the first 5 employees ' names

[SQL]View PlainCopy

1. Mysql> SELECT * from workers_info ORDER by employeddates limit 5;

"MySQL" MySQL common build Table statement