7.笔记 MySQL学习——向表中添加行

来源：互联网发布：知乎周刊第一本编辑：程序博客网时间：2024/06/05 21:00

7.笔记 MySQL学习——向表中添加行

往表中添加内容可以是insert语句，也可以是LOADDATA语句或者mysqlimport客户端程序。

1. 查看表中行数量

mysql> select * from student;

Empty set (0.00 sec)

显示为空

2. 利用INSERT添加行

mysql> insert into student values('Kyle','M',NULL);

Query OK, 1 row affected (0.00 sec)

mysql> insert into grade_event values('2012-09-03','Q',NULL);

Query OK, 1 row affected (0.00 sec)

mysql> insert into student (name,sex)values ('Abby','F'),('Joseph','M');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into member setlast_name='Stein',first_name='Waldo';

Query OK, 1 row affected (0.00 sec)

3. 利用文件添加新行

有两种方式，一种是直接执行如下：

[root@OEL sampdb]# mysql sampdb <insert_president.sql -u root -p

Enter password:

另一种在MYSQL下执行

mysql> source insert_president.sql

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

其中insert_president.sql 文件内容如下：

DELETE FROM president;

INSERT INTO president VALUES('Washington','George',NULL,'Wakefield','VA','1732-02-22','1799-12-14');

INSERT INTO president VALUES('Adams','John',NULL,'Braintree','MA','1735-10-30','1826-07-04');

INSERT INTO president VALUES('Jefferson','Thomas',NULL,'Albemarle County','VA','1743-04-13','1826-07-04');

INSERT INTO president VALUES('Madison','James',NULL,'Port Conway','VA','1751-03-16','1836-06-28');

INSERT INTO president VALUES('Monroe','James',NULL,'Westmoreland County','VA','1758-04-28','1831-07-04');

INSERT INTO president VALUES ('Adams','JohnQuincy',NULL,'Braintree','MA','1767-07-11','1848-02-23');

INSERT INTO president VALUES('Jackson','Andrew',NULL,'Waxhaw settlement','SC','1767-03-15','1845-06-08');

INSERT INTO president VALUES ('VanBuren','Martin',NULL,'Kinderhook','NY','1782-12-05','1862-07-24');

INSERT INTO president VALUES ('Harrison','WilliamH.',NULL,'Berkeley','VA','1773-02-09','1841-04-04');

INSERT INTO president VALUES('Tyler','John',NULL,'Greenway','VA','1790-03-29','1862-01-18');

INSERT INTO president VALUES ('Polk','JamesK.',NULL,'Pineville','NC','1795-11-02','1849-06-15');

INSERT INTO president VALUES('Taylor','Zachary',NULL,'Orange County','VA','1784-11-24','1850-07-09');

INSERT INTO president VALUES('Fillmore','Millard',NULL,'Locke','NY','1800-01-07','1874-03-08');

INSERT INTO president VALUES('Pierce','Franklin',NULL,'Hillsboro','NH','1804-11-23','1869-10-08');

INSERT INTO president VALUES('Buchanan','James',NULL,'Mercersburg','PA','1791-04-23','1868-06-01');

INSERT INTO president VALUES('Lincoln','Abraham',NULL,'Hodgenville','KY','1809-02-12','1865-04-15');

INSERT INTO president VALUES ('Johnson','Andrew',NULL,'Raleigh','NC','1808-12-29','1875-07-31');

INSERT INTO president VALUES ('Grant','UlyssesS.',NULL,'Point Pleasant','OH','1822-04-27','1885-07-23');

INSERT INTO president VALUES ('Hayes','RutherfordB.',NULL,'Delaware','OH','1822-10-04','1893-01-17');

INSERT INTO president VALUES ('Garfield','JamesA.',NULL,'Orange','OH','1831-11-19','1881-09-19');

INSERT INTO president VALUES ('Arthur','ChesterA.',NULL,'Fairfield','VT','1829-10-05','1886-11-18');

INSERT INTO president VALUES ('Cleveland','Grover',NULL,'Caldwell','NJ','1837-03-18','1908-06-24');

INSERT INTO president VALUES('Harrison','Benjamin',NULL,'North Bend','OH','1833-08-20','1901-03-13');

INSERT INTO president VALUES('McKinley','William',NULL,'Niles','OH','1843-01-29','1901-09-14');

INSERT INTO president VALUES('Roosevelt','Theodore',NULL,'New York','NY','1858-10-27','1919-01-06');

INSERT INTO president VALUES ('Taft','WilliamH.',NULL,'Cincinnati','OH','1857-09-15','1930-03-08');

INSERT INTO president VALUES ('Wilson','Woodrow',NULL,'Staunton','VA','1856-12-19','1924-02-03');

INSERT INTO president VALUES ('Harding','WarrenG.',NULL,'Blooming Grove','OH','1865-11-02','1923-08-02');

INSERT INTO president VALUES('Coolidge','Calvin',NULL,'Plymouth Notch','VT','1872-07-04','1933-01-05');

INSERT INTO president VALUES ('Hoover','HerbertC.',NULL,'West Branch','IA','1874-08-10','1964-10-20');

INSERT INTO president VALUES ('Roosevelt','FranklinD.',NULL,'Hyde Park','NY','1882-01-30','1945-04-12');

INSERT INTO president VALUES ('Truman','HarryS',NULL,'Lamar','MO','1884-05-08','1972-12-26');

INSERT INTO president VALUES ('Eisenhower','DwightD.',NULL,'Denison','TX','1890-10-14','1969-03-28');

INSERT INTO president VALUES ('Kennedy','JohnF.',NULL,'Brookline','MA','1917-05-29','1963-11-22');

INSERT INTO president VALUES ('Johnson','LyndonB.',NULL,'Stonewall','TX','1908-08-27','1973-01-22');

INSERT INTO president VALUES ('Nixon','RichardM.',NULL,'Yorba Linda','CA','1913-01-09','1994-04-22');

INSERT INTO president VALUES ('Ford','GeraldR.',NULL,'Omaha','NE','1913-07-14','2006-12-26');

INSERT INTO president VALUES ('Carter','JamesE.','Jr.','Plains','GA','1924-10-01',NULL);

INSERT INTO president VALUES ('Reagan','RonaldW.',NULL,'Tampico','IL','1911-02-06','2004-06-05');

INSERT INTO president VALUES ('Bush','GeorgeH.W.',NULL,'Milton','MA','1924-06-12',NULL);

INSERT INTO president VALUES ('Clinton','WilliamJ.',NULL,'Hope','AR','1946-08-19',NULL);

INSERT INTO president VALUES ('Bush','GeorgeW.',NULL,'New Haven','CT','1946-07-06',NULL);

INSERT INTO president VALUES ('Obama','BarackH.',NULL,'Honolulu','HI','1961-08-04',NULL);

4. 利用LOAD DATA语句导入

如下：

mysql> load data local infile 'member.txt' intotable member;

Query OK, 102 rows affected (0.09 sec)

Records: 102 Deleted: 0 Skipped: 0 Warnings: 0

其中member.txt文件如下：

\N Solow Jeanne \N 2013-11-15 jeanne_s@earth.com 16 Ludden Dr. Austin TX 33347 964-665-8735 Great Depression,Spanish-AmericanWar,Westward movement,Civil Rights,Sports

\N Lundsten August \N 2015-08-11 august.lundsten@pluto.com 13 O'Malley St. Bogalusa LA 96127 196-481-0897 Korean War,World War I,Civil Rights

\N ErdmannKay \N 2013-01-14 \N 42 Fremont Av. Grenada MI 44953 493-610-3215 Education,Roosevelt,Presidential politics

\N Arbogast Ruth \N 2015-02-10 arbogast.ruth@mars.net 95 Carnwood Rd. Paris IL 31483 539-907-5225 Colonial period,Social Security,Constitution,Lincoln,Goldrush

\N DorfmanCarol \N 2016-09-15 c.dorfman@uranus.net 21 Winnemac Av. Trenton MO 99887 597-290-3955 RevolutionaryWar,Cold War,immigration

\N EliasonJessica \N 2013-12-27 jessica.eliason@pluto.com 60 Century Av. Osborne KS 63198 896-268-0569 World War I,KoreanWar

\N Sawyer Dennis \N 2014-11-21 s_dennis@jupiter.com 48 Jenifer St. Denver CO 23728 775-983-4182 Industrial revolution,Great Depression,Armedservices,Education

\N Phillips Donald III 2013-03-05 d_phillips@neptune.org 13 Lake St. San Antonio TX 87154 898-166-9341 Revolutionary War,Abolition,Armedservices,Lincoln,Gold rush

\N Anderson Marcia \N 2014-02-10 \N 31 Bigelow Rd. Cedar Rapids IA 45150 445-539-3384 Armed services,immigration,Gold rush,ColdWar,Education

\N KilgoreLeonard \N 2016-05-09 \N 19 Pagelow Ln. Beloit WI 16235 963-699-2715 Spanish-American War,Founding fathers,WorldWar I,Presidential politics

\N Lederman Judith \N 2017-07-09 judith_lederman@mercury.net 218 N. Sherman Av. Appleton WI 63330 380-077-6632 World War II,Great Depression,War of1812,Spanish-American War,Vietnam War

\N Bodell Bonnie \N 2015-02-10 \N 674 Marledge St. Geneva IL 34619 790-449-4910 War of 1812,Spanish-American War,KoreanWar,World War I,Constitution

\N Reusch Diane \N 2013-03-14 reusch_d@venus.org 111 Dogwood Pl. Burlington IL 03211 964-681-8054 Vietnam War,Roosevelt

\N Hilby Bernard \N 2014-06-04 bernard.hilby@saturn.org 111 West St. Clinton IA 46161 333-263-2057 Westwardmovement,Cold War

\N PropperHarvey \N 2014-04-03 harvey_propper@pluto.com 853 Van Hise Av. Lansing MI 39980 184-832-6901 Industrialrevolution,Founding fathers,Great Depression,Constitution,Presidential politics

\N Michaels Amanda \N 2015-10-01 \N 805 Chase Blvd. South Bend IN 58751 295-458-1334 Abolition,Roosevelt,Vietnam War,SocialSecurity

\N Hagler Carolyn \N 2016-10-13 hagler.c@mars.net 834 Woods Edge Rd. Fort Wayne IN 65594 828-991-7354 Lincoln,Civil Rights,Goldrush,Revolutionary War,Civil War

\N York Mark II 2012-08-24 york_mark@earth.com 449 Meyer Av. Peoria IL 90108 845-137-2175 Cold War,immigration

\N Feit Daniel \N 2014-05-04 daniel.feit@pluto.com 181 E. Washington Av. Stockton CA 90255 167-064-7158 Colonial period,Vietnam War,KoreanWar,Presidential politics

\N Overland Roland \N \N \N 949 Mineral Point Rd. Minot ND 45600 232-732-1438 Gold rush,immigration,Spanish-American War

\N Lacke Bruce \N 2012-10-10 \N 617 West Lawn Av. Aberdeen SD 97919 171-132-0360 Vietnam War,Education

\N Hurst Sally \N 2013-12-31 hurst.s@mars.net 201 Laurel Ln. Warren MN 37373 553-257-4344 Spanish-American War

其中LOCAL会引发客户端程序读取数据文件，并把文件内容发送到服务器进行加载。

也可以如下

[root@OEL sampdb]# mysqlimport --local sampdb member.txt -u root -p

Enter password:

sampdb.member: Records: 102 Deleted: 0 Skipped: 0 Warnings: 0

mysqlimport 程序是根据数据文件的名字来确定表名的。

阅读全文

0 0