Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.
Course Of The Month
4 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Empirically learning My SQL #1, Saving recrods
Posted on 2013-06-27
Hello All...
This is me:
http://www.experts-exchange.com/M_2293853.html
(...just so you know that my background is in MS Access)
I am attempting to teach myself MySQL empirically, so some of my questions here will be fairly simple...
Yes, I know that MySQL is not the same as MS Access, but I am just starting out with MySQL and I just want the learn the basics of creating tables, adding records and querying the tables.
;-)
Ready? Here we go....
I created a simple Customers table:
cID: INT, PK, NN, AI
cName: VARCHAR(15), NN
cDOB: DATE, NN
cCreditLimit: DOUBLE, NN
(Now in the main window, I do have a "Local Instance" connection, and a "Local Server"
Don't ask me how I created them, because I don't remember. ;-)
....but again, remember, all I want to do is get familiar with creating tables, adding records and querying the tables.)
Great, ...looks good...
Now all I want to do at this moment is to open the table and add some records into it (to see the auto increment work, to see that only numbers can be entered into the cCreditLimit field, and verify the 15 character limit on the cName field, ...etc)
So I click the "Inserts" tab and I go about entering data....
Much to my surprise, ...I can pretty much type in anything in any field!?
I can even click "Save" and exit the file, ...and when I reopen the file (and the table) all of this "invalid" data is still there.
What is happening here?
In other words, what is the "Inserts" tab for, if I can simply "insert" whatever I want, wherever I want...
Put still another way,...once I create a table and set the columns, how do I actually open the table to enter records manually and see the table column settings in effect?
(Yes there are tutorials and videos out there, but none that say: "After you set up your table and columns you can enter data and test these setting by:....?"
Thanks!
If I have forgotten anything or if anything is unclear, just let me know...
;-)
JeffCoachman
This is me:
http://www.experts-exchange.com/M_2293853.html
(...just so you know that my background is in MS Access)
I am attempting to teach myself MySQL empirically, so some of my questions here will be fairly simple...
Yes, I know that MySQL is not the same as MS Access, but I am just starting out with MySQL and I just want the learn the basics of creating tables, adding records and querying the tables.
;-)
Ready? Here we go....
I created a simple Customers table:
cID: INT, PK, NN, AI
cName: VARCHAR(15), NN
cDOB: DATE, NN
cCreditLimit: DOUBLE, NN
(Now in the main window, I do have a "Local Instance" connection, and a "Local Server"
Don't ask me how I created them, because I don't remember. ;-)
....but again, remember, all I want to do is get familiar with creating tables, adding records and querying the tables.)
Great, ...looks good...
Now all I want to do at this moment is to open the table and add some records into it (to see the auto increment work, to see that only numbers can be entered into the cCreditLimit field, and verify the 15 character limit on the cName field, ...etc)
So I click the "Inserts" tab and I go about entering data....
Much to my surprise, ...I can pretty much type in anything in any field!?
I can even click "Save" and exit the file, ...and when I reopen the file (and the table) all of this "invalid" data is still there.
What is happening here?
In other words, what is the "Inserts" tab for, if I can simply "insert" whatever I want, wherever I want...
Put still another way,...once I create a table and set the columns, how do I actually open the table to enter records manually and see the table column settings in effect?
(Yes there are tutorials and videos out there, but none that say: "After you set up your table and columns you can enter data and test these setting by:....?"
Thanks!
If I have forgotten anything or if anything is unclear, just let me know...
;-)
JeffCoachman
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
4
11 Comments
Workbench's GUI, unfortunately, doesn't read the constraints on the table beforehand. It just reads in field1, field2, field3, etc.
Now, nothing prevents you from inserting whatever value you want in an auto_increment field. If th new value is greater than the old index value, the next index value will be your value plus one.
As for the other fields, you can type in whatever you want in the GUI, but when you click on "Apply" (and then Apply on the resulting SQL) the queries will fail.
I've been a command line guy for years, recently started using MSSQL/SSMS and like that a lot (where it's 'Edit Top X rows' does what I think you want it to do - immediate error on data type mismatch), and I agree with you that Workbench needs more work.
Now, nothing prevents you from inserting whatever value you want in an auto_increment field. If th new value is greater than the old index value, the next index value will be your value plus one.
As for the other fields, you can type in whatever you want in the GUI, but when you click on "Apply" (and then Apply on the resulting SQL) the queries will fail.
I've been a command line guy for years, recently started using MSSQL/SSMS and like that a lot (where it's 'Edit Top X rows' does what I think you want it to do - immediate error on data type mismatch), and I agree with you that Workbench needs more work.
Active 1 day ago
Where are you going to end up with this? Are you going to be using a programming language to access the database? A big note: MySQL (and MS SQL and most SQLs) are servers that are not intended for desktop use like Access is. Any desktop app is outside of MySQL.
On the web, the most common admin application is 'phpMyAdmin' which is a PHP app that talks directly to the MySQL server. If you edit something, you just click 'Go' and it's done. It's not saved until you 'Apply' it. If you are on web hosting, you will normally only be allowed access to your own database and tables. If you are the host or on your own computer, you would often use 'root' for total access.
I only have MySQL Workbench on one computer here and that was to answer a question on EE. I think the 'localhost' connection is semi-automatic because for an admin app, that is the most common. You can connect to remote installations if you have permission and it is available thru the firewalls.
On the web, the most common admin application is 'phpMyAdmin' which is a PHP app that talks directly to the MySQL server. If you edit something, you just click 'Go' and it's done. It's not saved until you 'Apply' it. If you are on web hosting, you will normally only be allowed access to your own database and tables. If you are the host or on your own computer, you would often use 'root' for total access.
I only have MySQL Workbench on one computer here and that was to answer a question on EE. I think the 'localhost' connection is semi-automatic because for an admin app, that is the most common. You can connect to remote installations if you have permission and it is available thru the firewalls.
Active 1 day ago
Just to refresh my memory, I fired up MySQL Workbench. It is a lot like SSMS (SQL Server Management Studio) for Microsoft SQL Server. It is very much an admin tool and not the way people use MySQL when they are coding an application.
Active 6 days ago
nemws1,
Thanks for your reply
But to be clear, I did click "apply" and nothing failed, the data was saved when I reopened the file
Now I realize that only after I connect to the "Server" (whether remote or local) can I truly test the column settings
__________________________
DaveBaldwin,
Thanks for your reply...
So my feeling was that I could do things locally for now, then move up to a "Server" later.
BTW, I did eventually get the updates to work when I made a connection to the "local server"
I was just confused as to what use the "Inserts" tab was useful for?
...if you can simply type in whatever you wanted...
Here was my "Empirical" thinking:
1. Download and Install MySQL
2. Open Workbench and design a simple table.
3. Open this table and test my column settings.
What I mean here is that you can actually do all three of the above steps, but nothing tells you that in order to really "test" your table, you have to connect to your local server.
Again, what is the purpose of having a "Inserts" tab available, if it is meaningless until you connect to a server. (why not grey it out until you are connected to a server?)
What I mean is, Workbench will let you install itself locally, then lets you design a table, then it allows you to click an "Inserts" tab and let's you immediately violate all of the column settings you just made...
;-)
Again, thanks for all of your replies, the purpose of this post is to document my journey of learning MySQL
Thanks to you both.
;-)
JeffCoachman
Thanks for your reply
Workbench's GUI, unfortunately, doesn't read the constraints on the table beforehand. It just reads in field1, field2, field3, etc.OK, ..thanks
Now, nothing prevents you from inserting whatever value you want in an auto_increment field. If th new value is greater than the old index value, the next index value will be your value plus one.Yes, But I actually could enter text and non-sequential numbers... Again, I can save it and reopen it and still see these "invalid" entries...
But to be clear, I did click "apply" and nothing failed, the data was saved when I reopened the fileNow I realize that only after I connect to the "Server" (whether remote or local) can I truly test the column settings
__________________________
DaveBaldwin,
Thanks for your reply...
Where are you going to end up with this?...Yes the goal is to eventually end up developing Apps with PHP,... My goal here was just to get familiar with the Database design aspects of MySQL.
So my feeling was that I could do things locally for now, then move up to a "Server" later.
BTW, I did eventually get the updates to work when I made a connection to the "local server"
I was just confused as to what use the "Inserts" tab was useful for?
...if you can simply type in whatever you wanted...
Here was my "Empirical" thinking:
1. Download and Install MySQL
2. Open Workbench and design a simple table.
3. Open this table and test my column settings.
What I mean here is that you can actually do all three of the above steps, but nothing tells you that in order to really "test" your table, you have to connect to your local server.
Again, what is the purpose of having a "Inserts" tab available, if it is meaningless until you connect to a server. (why not grey it out until you are connected to a server?)
What I mean is, Workbench will let you install itself locally, then lets you design a table, then it allows you to click an "Inserts" tab and let's you immediately violate all of the column settings you just made...
;-)
Again, thanks for all of your replies, the purpose of this post is to document my journey of learning MySQL
Thanks to you both.
;-)
JeffCoachman
Active 1 day ago
If you're going to use PHP, you would be much better off using phpMyAdmin which won't let you do some of those kinda silly things you noted. It will also generate SQL statements and PHP code that you can actually use. Web hosting with PHP and MySQL is available for as little as $36 for a year at Godaddy. For that you get a web host that supports PHP with MySQL supported by phpMyAdmin. That is what most of us use. I prefer Linux hosting but I also have Windows hosting from Godaddy that also supports PHP, MySQL, and phpMyAdmin.
http://www.godaddy.com/hosting/web-hosting.aspx?ci=9009
http://www.godaddy.com/hosting/web-hosting.aspx?ci=9009
Active 1 day ago
Thinking about it some more, I think you would be better served by learning MySQL by learning PHP first. PHP has MySQL drivers built into it and there is a lot of sample code for database operations and commands on http://www.php.net/ , the PHP web site which is the Best resource for all of this. The most common book recommendation is PHP & MySQL, Novice to Ninja. http://www.sitepoint.com/books/phpmysql5/
Active 6 days ago
Dave,
Thanks for your comments.
<I think you would be better served by learning MySQL by learning PHP first. >
I tried that, but I am somewhat weak at SQL, and since the DB is the backbone, I would like to focus in on that first.
If I end up with an in-efficient, un-normalized design, the user interface won't matter much...
;-)
But I will look into the tools you mentioned.
Thanks for being patient, informed and understanding.
;-)
JeffCoachman
Thanks for your comments.
<I think you would be better served by learning MySQL by learning PHP first. >
I tried that, but I am somewhat weak at SQL, and since the DB is the backbone, I would like to focus in on that first.
If I end up with an in-efficient, un-normalized design, the user interface won't matter much...
;-)
But I will look into the tools you mentioned.
Thanks for being patient, informed and understanding.
;-)
JeffCoachman
Active 1 day ago
You're welcome and thanks for the points. I think you'll find PHP/MySQL actually easier in the long run than Access is. The split is actually more like HTML/CSS for presentation in a page with javascript when you need something active in the client, PHP for program logic and MySQL for the database. Basic SQL is not that hard.
The one catch is that you have 3 'languages' to use, javascript, PHP, and (My)SQL. While they are all similar in many ways, the details sometimes make you stop and think, "Which one am I using?".
The one catch is that you have 3 'languages' to use, javascript, PHP, and (My)SQL. While they are all similar in many ways, the details sometimes make you stop and think, "Which one am I using?".
Active 6 days ago
Thnaks Dave...
To make a long story (kinda) short...
As you know MS Access is having a tough time transitioning to the web.
So either you go the Sharepoint route (if you want to stay with MS and Access), or bump up to .Net/SQL Server.
The Web guy at my company now, uses PHP/MYSQL
...and you can't beat the price..
...So I figure I'd go that route and be consistent
Thanks Again for all your help...
;-)
Jeff
To make a long story (kinda) short...
As you know MS Access is having a tough time transitioning to the web.
So either you go the Sharepoint route (if you want to stay with MS and Access), or bump up to .Net/SQL Server.
The Web guy at my company now, uses PHP/MYSQL
...and you can't beat the price..
...So I figure I'd go that route and be consistent
Thanks Again for all your help...
;-)
Jeff
Featured Post
Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
Since I wrote the original article about
Handling Date and Time in PHP and MySQL
several years ago, it seemed like now was a good time to update it for object-oriented PHP. This article does that, replacing as much as possible the pr…
This article shows the steps required to install WordPress on Azure.
Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan.
WordPress is no exception and requires an App Service Plan and Database to install
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
Suggested Courses
Course of the Month4 days, 20 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.