mysql ubuntu 应用记录 --- Head First SQL (四)

最新推荐文章于 2023-12-06 15:33:15 发布

wowocpp

最新推荐文章于 2023-12-06 15:33:15 发布

阅读量194

点赞数

分类专栏： web_html python

本文链接：https://blog.csdn.net/wowocpp/article/details/103183084

版权

python 同时被 2 个专栏收录

313 篇文章 14 订阅

订阅专栏

web_html

86 篇文章 0 订阅

订阅专栏

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) );