mysql ubuntu 应用记录 — Head First SQL (四)
fish_info.sql 少一个分号 ;
fish_records.sql 少一个分号;
CREATE TABLE `fish_info` (
`common` varchar(50) NOT NULL,
`species` varchar(50) NOT NULL,
`location` varchar(50) NOT NULL,
`weight` varchar(15) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO fish_info VALUES ('bass, largemouth' ,'M. salmoides' ,'Montgomery Lake, GA', '22 lb 4 oz');
INSERT INTO fish_info VALUES ('walleye', 'S. vitreus', 'Old Hickory Lake, TN', '25 lb 0 oz');
INSERT INTO fish_info VALUES ('trout, cutthroat', 'O. Clarki', 'Pyramid Lake, NV', '41 lb 0 oz');
INSERT INTO fish_info VALUES ('perch, yellow', 'P. Flavescens', 'Bordentown, NJ', '4 lb 3 oz');
INSERT INTO fish_info VALUES ('bluegill', 'L. Macrochirus', 'Ketona Lake, AL', '4 lb 12 oz');
INSERT INTO fish_info VALUES ('gar, longnose', 'L. Osseus', 'Trinity River, TX', '50 lb 5 oz');
INSERT INTO fish_info VALUES ('crappie, white', 'P. annularis', 'Enid Dam, MS', '5 lb 3 oz');
INSERT INTO fish_info VALUES ('pickerel, grass', 'E. americanus', 'Dewart Lake, IN', '1 lb 0 oz');
INSERT INTO fish_info VALUES ('goldfish', 'C. auratus', 'Lake Hodges, CA', '6 lb 10 oz');
INSERT INTO fish_info VALUES ('salmon, chinook', 'O. Tshawytscha', 'Kenai River, AK', '97 lb 4 oz');
source /home/jack/sql/HeadFirstSQLTables/fish_records.sql
select * from fish_info whinfo where location LIKE ‘%NJ’;
select * from fish_records WHERE state = ‘NJ’ ;
desc my_contacts ;
DESCRIBE my_contacts ;
show create table drink_info ;
ALTER TABLE my_contacts ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (contact_id);
source /home/jack/sql/HeadFirstSQLTables/my_contacts_ch2_aa.sql
select * from my_contacts ;
source /home/jack/sql/HeadFirstSQLTables/projekts.sql
DESCRIBE projekts;
ALTER TABLE projekts RENAME TO project_list ;
DESCRIBE project_list;
select * from project_list ;
ALTER TABLE project_list CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (proj_id);
ALTER TABLE project_list CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100), CHANGE COLUMN contractoronjob con_name VARCHAR(30);
DESCRIBE project_list;
ALTER TABLE project_list MODIFY COLUMN proj_desc VARCHAR(120) ;
ALTER TABLE project_list ADD COLUMN con_phone VARCHAR(10), ADD COLUMN start_date DATE , ADD COLUMN est_cost DECIMAL(7,22);
ALTER TABLE project_list DROP COLUMN start_date;
page 253
source /home/jack/sql/HeadFirstSQLTables/movie_table.sql
select title ,category FROM movie_table where category = ‘family’ ORDER BY title ;
SELECT title, category ,purchased FROM movie_table ORDER BY category,purchased ;
SELECT title,purchased FROM movie_table ORDER BY title ASC,purchased DESC;
source /home/jack/sql/HeadFirstSQLTables/cookie_sales.sql
select * from cookie_sales ORDER BY sales DESC;
Page 298
mysql -u root -p
USE gregs_list;
CREATE TABLE interests ( int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, interest VARCHAR(50) NOT NULL, contact_id INT NOT NULL, CONSTRAINT my_contacts_contact_id_fk FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id) );
CREATE TABLE interests ( int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, interest VARCHAR(50) NOT NULL, contact_id INT NOT NULL, CONSTRAINT my_contacts_contact_id_fk FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id) );
page 378 前面的 也没有看懂