Creating SQL Tables: What Do I Need to Know?

[kolleamm]

I created a database on my website, and now I'm guessing I have to add tables for the information. I have no idea what I'm looking at when I'm asked to create a table.

 

[newjerseyrunner]

That's phpmyadmin, not SQL. I would ignore it, and learn how to interact with SQL directly.

Sql is like a programming language, and included in that is building commands. You can store all of this in a file and simply tell mysql to run the file.

myfile.sql

CREATE TABLE animals (
     id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

mysql -u'user' -p'password' < myfile.sql

You can also use a tool called MySQLWorkbench, which can do everything phpmyadmin can do, and a whole lot more.

 

[kolleamm]

That's phpmyadmin, not SQL. I would ignore it, and learn how to interact with SQL directly.

Sql is like a programming language, and included in that is building commands. You can store all of this in a file and simply tell mysql to run the file.

myfile.sql

CREATE TABLE animals (
     id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

mysql -u'user' -p'password' < myfile.sql

You can also use a tool called MySQLWorkbench, which can do everything phpmyadmin can do, and a whole lot more.

I need a program where I can organize my data similar to excel, would MySQLWorkbench be able to do this? Can excel create sql files?

Thanks in advance

 

[QuantumQuest]

I created a database on my website, and now I'm guessing I have to add tables for the information. I have no idea what I'm looking at when I'm asked to create a table.

Table is a database construct that comprises of rows and columns. Columns have properties (like name, data type etc.) and each column is a property of a particular object (e.g. a person's surname, name, address etc.). Rows are essentially collections of values of various columns. Each row holds the data of a particular object (e.g. a person or any other). So, data and by extension information, is organized in the form of various tables in a database. In your attached image you have a snapshot of phpmyadmin. You have to fill in the values for the properties of each field that your table will have.

I need a program where I can organize my data similar to excel, would MySQLWorkbench be able to do this? Can excel create sql files?

Filling in the details in phpmyadmin, you will create your table(s) and then using PHP you can insert and retrieve data from there.

Now, MySQL Workbench is a visual tool that provides data modelling, SQL development and helps in administration tasks (like server configuration for MySQL server, user administration and other useful things). It is very helpful in designing your database in an easy (visual) way.

Excel cannot create sql-type files directly. Excel files can be transferred to a MySQL database by creating a .csv (Comma Separated Values) file from your Excel spreadsheet and then importing this file into your database using phpmyadmin. Also, you can save a MySQL file as .csv through phpmyadmin and import it to Excel. You can google it for the details.

 

[stoomart]

I need a program where I can organize my data similar to excel

A database may be overkill depending on why you are looking to move away from Excel. I suggest starting with MS Access as a gateway tool between Excel and a full-blown RDBMS.

 

[jack action]

https://www.physicsforums.com/threads/learning-mysql.894672/#post-5628835

 

[WWGD]

I need a program where I can organize my data similar to excel, would MySQLWorkbench be able to do this? Can excel create sql files?

Thanks in advance

There are import/export capabilities/wizards between Excel and SQL. Look up the import/export wizard in SQL Server and similar in Excel. If you want to export from Excel to SQL , you may have to do a bit of scripting if the receiving database schema is too different from the file, but you can also rewrite your database.

 

[Inventive]

I think a design plan would help here. Where do you see your needs now and in the future? How are you planning to access the data (now and in the future)? Does your environment require a secure environment? If you are doing a small scale project you might consider a used database server with a older version of sql server software. Those servers usually have Enterprise Manager installed to create tables, stored procedures, etc. Excel files can easily be imported into that system. It really depends on your data security and access requirements m