MySQL Create and manipulate database tables demo

"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.

drop TABLE IF EXISTS ' workers_info ';
create TABLE ' Workers_info ' (
' id ' int (one) not NULL auto_increment,
' workername ' varchar (20) Not NULL,
' salary ' int (one) DEFAULT ' 0 ',
' email ' varchar,
Span style= "Background-color: #99cc00;" > ' employeddates ' date,
' Department ' varchar (),
Span style= "Background-color: #99cc00;" >primary KEY (' id ')

mysql> ALTER TABLE Workers_info ADD sex enum (' F ', ' M ', ' S ');

"3" View the structure of the table

mysql> desc Workers_info;

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

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

mysql> ALTER TABLE workers_info ADD QQ int;
ALTER TABLE workers_info MODIFY employeddates date after QQ;

"5" Change the email field to mailbox------Modify the field name

mysql> ALTER TABLE workers_info Change email mailbox varchar (30);

"6" adds 10 records to the table------add data

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

"7" Modify data for Tel and mailbox of two records------

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

"8" view------query data for added records

Mysql> select * from Workers_info;

"9" View name and entry time record

Mysql> select Workername,employeddates from Workers_info;

"10" Check the------date of entry time before 2003 filter

Mysql> SELECT * FROM Workers_info where year (Employeddates) < 2003;

"11" queries the highest and lowest employee names------sort

Mysql> SELECT * from Workers_info ORDER by salary limit 1;
Mysql> SELECT * from Workers_info ORDER by salary desc LIMIT 1;

"12" query average wage------averaging function

Mysql> Select AVG (Salary) from Workers_info;

"13" Statistics number of male employees, number of female employees------SUM function

Mysql> Select COUNT (1) from Workers_info where sex= "M";

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

Mysql> SELECT * from Workers_info ORDER by employeddates limit 5;

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

----------

Demo:

1, the simplest:

CREATE TABLE T1 (
ID int not null,
Name Char (20)
);

2, with the primary key:

CREATE TABLE T1 (
ID int not NULL primary key,
Name Char (20)
);

Composite PRIMARY Key:

CREATE TABLE T1 (
ID int not null,
Name Char (20),
Primary KEY (Id,name)
);

3, with the default value:

CREATE TABLE T1 (
ID int not null default 0 primary Key,
Name char (default ' 1 ')
);

Reprint

CREATE TABLE PLAYERS
(Playerno INTEGER not NULL PRIMARY KEY,
NAME CHAR(a) is not NULL,
initials CHAR(3) not NULL,
birth_date DATE,
SEX CHAR(1) not NULL
CHECK(SEX in (' M ',' F ')),
JOINED SMALLINT not NULL
CHECK(JOINED > 1969),
STREET CHAR (+) not NULL,
Houseno CHAR(4),
postcode CHAR(6) CHECK(postcode like ' ___ '),
Town CHAR(ten) is not NULL,
Phoneno CHAR(+),
Leagueno CHAR(4))
;

CREATE TABLE TEAMS
(Teamno INTEGER not NULL PRIMARY KEY,
playerno INTEGER not NULL,
Division CHAR(6) not NULL
CHECK(Division in (' first ',' second ')),
FOREIGN KEY (playerno) REFERENCES PLAYERS (playerno))
;

CREATE TABLE MATCHES
(Matchno INTEGER not NULL PRIMARY KEY,
Teamno INTEGER not NULL,
playerno INTEGER not NULL,
WON SMALLINT not NULL
CHECK(WON between 0 and 3),
LOST SMALLINT not NULL
CHECK(LOST between 0 and 3),
FOREIGN KEY (teamno) REFERENCES TEAMS (Teamno),
FOREIGN KEY (playerno) REFERENCES PLAYERS (playerno))
;

CREATE TABLE Penalties
(Paymentno INTEGER not NULL PRIMARY KEY,
playerno INTEGER not NULL,
payment_date DATE not NULL
CHECK(payment_date >= DATE (' 1969-12-31 ')),
AMOUNT DECIMAL(7,2) not NULL
CHECK (AMOUNT > 0),
FOREIGN KEY (playerno) REFERENCES PLAYERS (playerno))
;

CREATE TABLE committee_members
(Playerno INTEGER not NULL,
begin_date DATE not NULL,
end_date DATE,
POSITION CHAR(+),
PRIMARY KEY (Playerno, begin_date),
FOREIGN KEY (playerno) REFERENCES PLAYERS (playerno),
CHECK(begin_date < end_date),
CHECK(begin_date >= DATE (' 1990-01-01 ' )))

;

MySQL Create and manipulate database tables demo