Account features: MySql
- The manual and required knowledge
- Your database
- Entering MySQL shell
- Exiting the MySQL shell
- Entering SQL queries at the MySQL shell
MySql is a relational database system running on Positive Internet Company servers. Each account has the capability to have a MySQL database. If you requested a database when you signed up for your account it will be ready to use. If you would like to request for the database to be set up after signing up please do so by e-mailing email@example.com. If you don't yet need the facilities of a database that integrates with your website, or have a simpler solution, then you can safely ignore the feature until such time as you do.
Using a MySQL as the "back-end" of your website is appropriate, for example, if you wish to set up a site which includes product catalogues, records visitor information or manipulates, filters and presents any tables of dynamic data. Of course, a relational database is not the only way to do these things - you could manually edit HTML pages, write scripts to read from "flat file" data sources for example - but for anything slightly more involved, getting to grips with MySQL is well worth the effort. What's more, with an ODBC driver (freely available - see below), you can manage your database on our server from within Microsoft Access or Excel right from your desktop!
The manual and required knowledge
As its name suggests, MySQL uses the Structured Query Language. If you have had experience with Microsoft SQL server, Oracle or most other modern database systems, you will have encountered it. If not, there are several good books on the subject. Even if you don't use MySQL, a knowledge of SQL looks good on a CV! Assuming you have a working knowledge of general SQL, the exhaustive MySQL manual illustrates the specifics. It makes good reading on long train journeys. As long as the scenery isn't too enticing.
Apart form SQL, you should also know about CGI scripts. Positive servers are installed with the necessary DBI Perl modules to access your database and present the information through a webpage. See below for information on their use. PHP, a language whose forte is integrating database information into HTML documents, is also installed and available for immediate use. Again, information is available below.
When we create your database we set up an empty database in which you may create your own tables. The database's name is the same as your login name. The username to access the database is the same as your primary account's login name. The database's password is the same as your primary account's original password. Please Email us if you want it changed or you can change it at the command shell. Changing your login password will not change your database's password. Databases created before 1st Jan 2004 will usually exist on the same server as your web hosting account (localhost), databases set up after Jan 1st 2004 will exist on a seperate dedicated database server. ( mysql.positive-internet.com )
Entering MySQL shell
There are several ways to access your database. One of the quickest (if most rudimentary) is through the MySQL shell. Through it, you can issue any SQL query and see its results. To use the MySQL shell, you must use ssh to access the command shell on your primary account. If the last statement was meaningless to you, you have other parts of this manual to read yet!
Then, as an example, let's assume your Positive Internet login name is fishcom. As explained above, your database is therefore also called fishcom. So at Unix command prompt, type the following and press return:
mysql -p database
The -p option tells mysql to ask you for a password, which is necessary because we set up all databases with passwords. Again, this password will be the same as your original primary account's password, unless you have asked for it to be changed.
Instead of database, you should type the name of the database you wish to access. This will usually be the sames as your Positive login name.
You will be prompted for a password. It won't appear on screen of course. Type it in and press return.
If you have typed in the correct password, you will see something like this:
Reading table information for completion of table and column names
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 484 to server version: 3.22.4-beta
Type 'help' for help.
The mysql> is the shell's prompt, beckoning you to type SQL queries at your leisure.
Exiting the MySQL shell
Simply type exit and press return. If you're particularly lazy, press ctrl with d on a blank line and you will be taken back to the system prompt thus:
It responds and you're back at the system prompt. MySQL is nothing if not courteous.
Entering SQL queries at the MySQL shell
Of course, we're not going to go into the full semantics of SQL here - the MySQL manual is for that! Remember that all queries should end with a semicolon. Bearing this in mind, you can split your query over as many lines as you wish - it will only be acted upon when the final semicolon is entered and the return key pressed.
If the query returns data (a select query, for instance), the information will be presented. Unfortunately, if there are more columns than room to display them, everything wraps rather unattractively. A solution which often works is to copy and paste the output into Windows Notepad and make sure wrapping is turned off. Or select fewer columns in your select statement! For complex queries with lengthy output, there are better solutions than the MySQL shell (discussed below).
If the query acts upon the table but doesn't return any data (for example, a create, alter, update or delete statement), the number of affected rows and columns is output. In this example, we create a new table called fish with two columns:
mysql> create table fish (
-> name varchar(25) primary key,
-> cost decimal (12,2) not null,
-> description mediumtext,
-> index name(name),
-> index cost(cost)
Query OK, 0 rows affected (0.02 sec)
The bold text above is what was typed in. The rest is MySQL's response. This whole query could have been typed on one line, but it was neater to press return after the first parenthesis and then after each column or index definition. While you're still in the middle of a multi-line query, the MySQL shell reminds you by indenting the line and prompting you with ->
Let's insert something into the table:
mysql> insert into fish values ('haddock','12.50','A nice smoked fish ');
Query OK, 1 row affected (0.00 sec)
And finally, let's select something from the table:
mysql> select * from fish where cost < '15.00';
| name | cost | description |
| haddock | 12.50 | A nice fish smoked |
1 row in set (0.00 sec)
Again, don't worry too much here about the actual SQL syntax - it's all explained in the MySQL manual. For futher information on using the MySQL shell, type help at its prompt.