18.17 MySQL Creating New Database
Create a new database called nhds (National Hospital Discharge Suvery). The sample data we use comes from http://www.cdc.gov/nchs/.
$ mysql -u root -p mysql Password: mysql> CREATE DATABASE nhds;
The data to populate a new table we will create is stored in the file
nhds99.data as comma separated values. We can write a simple
script to create the table and import the data. The script is called
USE nhds; CREATE TABLE nhds99 ( svy_year int, newborn char(1), age_unit char(1), age int, sex char(1), race char(1), m_status char(1), dis_month int, dis_status char(1), days_care int, los_flag char(1), geo_region char(1), no_beds char(1), hos_ownership char(1), ana_weight int, dc1 char(5), dc2 char(5), dc3 char(5), dc4 char(5), dc5 char(5), dc6 char(5), dc7 char(5), pc1 char(4), pc2 char(4), pc3 char(4), pc4 char(4), p_src_payment char(2), s_src_payment char(2), drg char(3)); LOAD DATA LOCAL INFILE 'nhds99.data' INTO TABLE nhds99 FIELDS TERMINATED BY ',';
Then simply run it through mysql:
$ mysql -u root -p nhds < nhds99.sql
You can remove the table from the database with:
$ mysql -u root -p nhds mysql> DROP TABLE nhds99;
Using mysql-navigator you can not currently create new
databases (except by running an external script) but you can create
tables. You can also run SQL scripts. So create your database for
nhds as above. Then start up the navigator and open
root@localhost. Then under the MySQL menu choose
Script. Identify the script and a log file then Fire. The
new table should then appear under the nhds database.
With the navigator you can even edit the script files with the Edit item in the MySQL menu.
Your donation will support ongoing development and give you access to the PDF version of this book. Desktop Survival Guides include Data Science, GNU/Linux, and MLHub. Books available on Amazon include Data Mining with Rattle and Essentials of Data Science. Popular open source software includes rattle, wajig, and mlhub. Hosted by Togaware, a pioneer of free and open source software since 1984. Copyright © 1995-2021 Graham.Williams@togaware.com Creative Commons Attribution-ShareAlike 4.0.