perlbyexample中mysql例子

时间：2009-06-20 来源：brain2007

mysql --user=root --password=my_password --host=localhost mysql -uroot -p******* create database sample_db;
SHOW DATABASES;
USE sample_db; CREATE TABLE teams(name varchar(100) not null,wins int unsigned,losses int unsigned);
SHOW TABLES;
DESCRIBE teams; CREATE TABLE coaches(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(75),team VARCHAR(100),title VARCHAR(50),start_date date,PRIMARY KEY(id));
DESCRIBE coaches;
SHOW TABLES;
INSERT INTO teams set name='Fremont Tigers', wins=24,losses=26;
INSERT INTO teams set name='Chico Hardhats',wins=19,losses=25;
INSERT INTO teams values ('Bath Warships',32,3);
INSERT INTO teams values ('Bangor Rams',22,24); INSERT INTO coaches(name,team, title,start_date) values('John Doe','Chico Hardhats','Head Coach',20021210);
INSERT INTO coaches(name,team, title,start_date) values('Jack Mattsone','Chico Hardhats','Offensive Coach','20041005');
INSERT INTO coaches(name,team, title,start_date) values( 'Bud Wilkins', 'Fremont Tigers', 'Head Coach','19990906');
INSERT INTO coaches(name, team, title,start_date) values( 'Joe Hayes', 'Fremont Tigers', 'Defensive Coach','19980616'); Operator Description Example
= Equal to where country = 'Sweden'
<>, != Not equal to[a] where country <> 'Sweden'
> Greater than where salary > 50000
< Less than where salary < 50000
>= Greater than or equal
<= Less than or equal
IS [NOT] NULL Is NULL (no value) or Not NULL where birth = NULL
BETWEEN Between an inclusive range where last_name BETWEEN 'Doe' AND 'Hayes'
LIKE Search for value like a pattern where last_name LIKE 'D%'
NOT LIKE Search for a value not like a pattern where country NOT LIKE 'Sw%'
!, NOT logical NOT for negation where age ! 10;
||, OR logical OR where order_number > 10 || part_number = 80
&&, AND logical AND where age > 12 && age < 21
XOR Exclusive OR where status XOR
ORDER BY name
ORDER BY name DESC
ORDER BY WINS LIMIT 2 SELECT teams.name, coaches.name, teams.wins FROM teams,coaches WHERE teams.name = coaches.team && coaches.id = 4;
SELECT teams.name, coaches.name, teams.wins FROM teams,coaches WHERE teams.name = coaches.team && coaches.title = "Head Coach";
SELECT t.name, c.name, t.wins FROM teams t, coaches c WHERE t.name = c.team && c.title LIKE "Head%";
DELETE FROM teams WHERE name = "Bath Warships"; UPDATE teams SET wins=wins + 2 WHERE name="Chico Hardhats";
UPDATE teams SET name="Bath Destroyers" where name="Bath Warships"; ALTER TABLE teams ADD captain varchar(100);
ALTER TABLE teams DROP captain; ALTER TABLE teams MODIFY name VARCHAR(100) NOT NULL,ADD PRIMARY KEY(name);
DROP TABLE teams;
DROP DATABASE sample_db;