|Home||Switchboard||Unix Administration||Red Hat||TCP/IP Networks||Neoliberalism||Toxic Managers|
May the source be with you, but remember the KISS principle ;-)
Bigger doesn't imply better. Bigger often is a sign of obesity, of lost control, of overcomplexity, of cancerous cells
|Securing the Initial MySQL Accou|
|Apache||MS Access to mySql conversion||PHP integration||
|Comparison with other databases||Resetting root password on MySQL||Humor||Etc|
Sun's MySQL MySQL 5.0 Reference Manual contains a useful Tutorial. It covers
To connect to the server, you will usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you will also need to specify a host name. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:
mysql -h host -u user -pEnter password:
the host name where your MySQL server is running and the user name of your MySQL
account. Substitute appropriate values for your setup. The
******** represents your password; enter it when
mysql displays the
Enter password: prompt.
If that works, you should see some introductory information followed by a
mysql -h host -u user -pEnter password:
********Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25338 to server version: 5.0.23-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
mysql> prompt tells you that
mysql is ready for you to enter commands.
If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following:
mysql -u user -p
If, when you attempt to log in, you get an error message such as ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that that MySQL server daemon (Unix) or service (Windows) is not running. Consult the administrator or see the section of Chapter 2, Installing and Upgrading MySQL that is appropriate to your operating system.
For help with other problems often encountered when trying to log in, see Section A.2, “Common Errors When Using MySQL Programs”.
Some MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:
After you have connected successfully, you can disconnect any time by typing
\q) at the
On Unix, you can also disconnect by pressing Control-D.
Most examples in the following sections assume that you are connected to the
server. They indicate this by the
Make sure that you are connected to the server, as discussed in the previous section. Doing so does not in itself select any database to work with, but that's okay. At this point, it's more important to find out a little about how to issue queries than to jump right in creating tables, loading data into them, and retrieving data from them. This section describes the basic principles of entering commands, using several queries you can try out to familiarize yourself with how mysql works.
Here's a simple command that asks the server to tell you its version number and
the current date. Type it in as shown here following the
prompt and press Enter:
SELECT VERSION(), CURRENT_DATE;+----------------+--------------+ | VERSION() | CURRENT_DATE | +----------------+--------------+ | 5.0.7 | 2005-07-11 | +----------------+--------------+ 1 row in set (0.01 sec) mysql>
This query illustrates several things about mysql:
QUIT, mentioned earlier, is one of them. We'll get to others later.)
mysql>prompt to indicate that it is ready for another command.
Keywords may be entered in any lettercase. The following queries are equivalent:
SELECT VERSION(), CURRENT_DATE;mysql>
select version(), current_date;mysql>
SeLeCt vErSiOn(), current_DATE;
Here's another query. It demonstrates that you can use mysql as a simple calculator:
SELECT SIN(PI()/4), (4+1)*5;+------------------+---------+ | SIN(PI()/4) | (4+1)*5 | +------------------+---------+ | 0.70710678118655 | 25 | +------------------+---------+ 1 row in set (0.02 sec)
The queries shown thus far have been relatively short, single-line statements. You can even enter multiple statements on a single line. Just end each one with a semicolon:
SELECT VERSION(); SELECT NOW();+----------------+ | VERSION() | +----------------+ | 5.0.7-beta-Max | +----------------+ 1 row in set (0.00 sec) +---------------------+ | NOW() | +---------------------+ | 2005-07-11 17:59:36 | +---------------------+ 1 row in set (0.00 sec)
A command need not be given all on a single line, so lengthy commands that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)
Here's a simple multiple-line statement:
CURRENT_DATE;+---------------+--------------+ | USER() | CURRENT_DATE | +---------------+--------------+ | jon@localhost | 2005-07-11 | +---------------+--------------+
In this example, notice how the prompt changes from
-> after you enter the first line of a multiple-line
query. This is how mysql indicates that it has
not yet seen a complete statement and is waiting for the rest. The prompt is your
friend, because it provides valuable feedback. If you use that feedback, you can
always be aware of what mysql is waiting for.
If you decide you do not want to execute a command that you are in the process
of entering, cancel it by typing
Here, too, notice the prompt. It switches back to
after you type
\c, providing feedback to indicate that
mysql is ready for a new command.
The following table shows each of the prompts you may see and summarizes what they mean about the state that mysql is in:
||Ready for new command.|
||Waiting for next line of multiple-line command.|
||Waiting for next line, waiting for completion of a string that began
with a single quote (‘
||Waiting for next line, waiting for completion of a string that began
with a double quote (‘
||Waiting for next line, waiting for completion of an identifier that
began with a backtick (‘
||Waiting for next line, waiting for completion of a comment that began
In the MySQL 5.0 series, the
/*> prompt was implemented
in MySQL 5.0.6.
Multiple-line statements commonly occur by accident when you intend to issue a command on a single line, but forget the terminating semicolon. In this case, mysql waits for more input:
If this happens to you (you think you've entered a statement but the only response
-> prompt), most likely
mysql is waiting for the semicolon. If you don't
notice what the prompt is telling you, you might sit there for a while before realizing
what you need to do. Enter a semicolon to complete the statement, and
mysql executes it:
;+---------------+ | USER() | +---------------+ | jon@localhost | +---------------+
occur during string collection (another way of saying that MySQL is waiting for
completion of a string). In MySQL, you can write strings surrounded by either ‘
"’ characters (for example,
and mysql lets you enter strings that span multiple
lines. When you see a
prompt, it means that you have entered a line containing a string that begins with
'’ or ‘
"’ quote character,
but have not yet entered the matching quote that terminates the string. This often
indicates that you have inadvertently left out a quote character. For example:
SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
If you enter this
SELECT statement, then press
Enter and wait for the result, nothing happens.
Instead of wondering why this query takes so long, notice the clue provided by the
'> prompt. It tells you that
mysql expects to see the rest of an unterminated
string. (Do you see the error in the statement? The string
'Smith is missing the second single quote mark.)
At this point, what do you do? The simplest thing is to cancel the command. However,
you cannot just type
\c in this case, because
mysql interprets it as part of the string that
it is collecting. Instead, enter the closing quote character (so
mysql knows you've finished the string), then type
SELECT * FROM my_table WHERE name = 'Smith AND age < 30;'>
The prompt changes back to
mysql>, indicating that
mysql is ready for a new command.
`> prompt is similar to the
"> prompts, but
indicates that you have begun but not completed a backtick-quoted identifier.
It is important to know what the
`> prompts signify,
because if you mistakenly enter an unterminated string, any further lines you type
appear to be ignored by mysql — including a line
QUIT. This can be quite confusing, especially
if you do not know that you need to supply the terminating quote before you can
cancel the current command.
Once you know how to enter commands, you are ready to access a database.
Suppose that you have several pets in your home (your menagerie) and you would like to keep track of various types of information about them. You can do so by creating tables to hold your data and loading them with the desired information. Then you can answer different sorts of questions about your animals by retrieving data from the tables. This section shows you how to:
The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations in which a similar type of database might be used. For example, a database like this could be used by a farmer to keep track of livestock, or by a veterinarian to keep track of patient records. A menagerie distribution containing some of the queries and sample data used in the following sections can be obtained from the MySQL Web site. It is available in both compressed tar file and Zip formats at http://dev.mysql.com/doc/.
SHOW statement to find out what databases
currently exist on the server:
SHOW DATABASES;+----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+
The list of databases is probably different on your machine, but the
are likely to be among them. The
mysql database is
required because it describes user access privileges. The
test database is often provided as a workspace for users to try things out.
Note that you may not see all databases if you do not have the
SHOW DATABASES privilege. See
Section 188.8.131.52, “
test database exists, try to access it:
USE testDatabase changed
does not require a semicolon. (You can terminate such statements with a semicolon
if you like; it does no harm.) The
USE statement is
special in another way, too: it must be given on a single line.
You can use the
test database (if you have access
to it) for the examples that follow, but anything you create in that database can
be removed by anyone else with access to it. For this reason, you should probably
ask your MySQL administrator for permission to use a database of your own. Suppose
that you want to call yours
menagerie. The administrator
needs to execute a command like this:
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
your_mysql_name is the MySQL user name assigned
to you and
your_client_host is the host from which
you connect to the server.
VARCHARcolumns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a
VARCHARin MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. If you provide fewer characters than [n] specifies, MySQL will automatically decrease the size of that field to match the number of characters provided, thus saving disk space.
If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:
CREATE DATABASE menagerie;
Under Unix, database names are case sensitive (unlike SQL keywords), so you must
always refer to your database as
menagerie, not as
or some other variant. This is also true for table names. (Under Windows, this restriction
does not apply, although you must refer to databases and tables using the same lettercase
throughout a given query. However, for a variety of reasons, our recommended best
practice is always to use the same lettercase that was used when the database was
Note: If you get an error such as ERROR 1044 (42000): Access denied for user 'monty'@'localhost' to database 'menagerie' when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 5.8, “The MySQL Access Privilege System”.
Creating a database does not select it for use; you must do that explicitly.
menagerie the current database, use this command:
USE menagerie;Database changed
Your database needs to be created only once, but you must select it for use each
time you begin a mysql session. You can do this
by issuing a
USE statement as shown in the example.
Alternatively, you can select the database on the command line when you invoke
mysql. Just specify its name after any connection
parameters that you might need to provide. For example:
mysql -h host -u user -p menagerieEnter password:
menagerie in the command just shown is
not your password. If you want to supply
your password on the command line after the
you must do so with no intervening space (for example, as
-pmypassword, not as
-p mypassword). However, putting your password on the
command line is not recommended, because doing so exposes it to snooping by other
users logged in on your machine.
Creating the database is the easy part, but at this point it's empty, as
SHOW TABLES tells you:
SHOW TABLES;Empty set (0.00 sec)
The harder part is deciding what the structure of your database should be: what tables you need and what columns should be in each of them.
You want a table that contains a record for each of your pets. This can be called
pet table, and it should contain, as a bare minimum,
each animal's name. Because the name by itself is not very interesting, the table
should contain other information. For example, if more than one person in your family
keeps pets, you might want to list each animal's owner. You might also want to record
some basic descriptive information such as species and sex.
How about age? That might be of interest, but it's not a good thing to store in a database. Age changes as time passes, which means you'd have to update your records often. Instead, it's better to store a fixed value such as date of birth. Then, whenever you need age, you can calculate it as the difference between the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not difficult. Storing birth date rather than age has other advantages, too:
You can probably think of other types of information that would be useful in
pet table, but the ones identified so far are sufficient:
name, owner, species, sex, birth, and death.
CREATE TABLE statement to specify the layout
of your table:
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),->
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
VARCHAR is a good choice for the
species columns because the column values vary in length.
The lengths in those column definitions need not all be the same, and need not be
20. You can normally pick any length from
65535, whatever seems
most reasonable to you. (Note: Prior
to MySQL 5.0.3, the upper limit was 255.) If you make a poor choice and it turns
out later that you need a longer field, MySQL provides an
ALTER TABLE statement.
Several types of values can be chosen to represent sex in animal records, such
'f', or perhaps
is simplest to use the single characters
The use of the
DATE data type for the
is a fairly obvious choice.
Once you have created a table,
SHOW TABLES should
produce some output:
SHOW TABLES;+---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+
To verify that your table was created the way you expected, use a
DESCRIBE pet;+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
You can use
DESCRIBE any time, for example, if you
forget the names of the columns in your table or what types they have.
For more information about MySQL data types, see Chapter 11, Data Types.
These are a few common and much used indexes:
After creating your table, you need to populate it. The
LOAD DATA and
INSERT statements are useful for
Suppose that your pet records can be described as shown here. (Observe that MySQL
expects dates in
'YYYY-MM-DD' format; this may be different
from what you are used to.)
You could create a text file
one record per line, with values separated by tabs, and given in the order in which
the columns were listed in the
CREATE TABLE statement.
For missing values (such as unknown sexes or death dates for animals that are still
living), you can use
NULL values. To represent these
in your text file, use
\N (backslash, capital-N). For
example, the record for Whistler the bird would look like this (where the whitespace
between values is a single tab character):
Whistler Gwen bird \N 1997-12-09 \N
To load the text file
pet.txt into the
pet table, use this command:
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
Note that if you created the file on Windows with an editor that uses
\r\n as a line terminator, you should use:
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet->
LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'.)
You can specify the column value separator and end of line marker explicitly
LOAD DATA statement if you wish, but the defaults
are tab and linefeed. These are sufficient for the statement to read the file
If the statement fails, it is likely that your MySQL installation does not have
local file capability enabled by default. See
Section 5.7.4, “Security Issues with
LOAD DATA LOCAL”,
for information on how to change this.
When you want to add new records one at a time, the
statement is useful. In its simplest form, you supply values for each column, in
the order in which the columns were listed in the
statement. Suppose that Diane gets a new hamster named “Puffball.”
You could add a new record using an
INSERT INTO pet->
Note that string and date values are specified as quoted strings here. Also,
INSERT, you can insert
directly to represent a missing value. You do not use
like you do with
From this example, you should be able to see that there would be a lot more typing
involved to load your records initially using several
statements rather than a single
LOAD DATA statement.
SELECT statement is used to pull information
from a table. The general form of the statement is:
what_to_select indicates what you want
to see. This can be a list of columns, or
* to indicate
indicates the table from which you want to retrieve data. The
WHERE clause is optional. If it is present,
conditions_to_satisfy specifies one or
more conditions that rows must satisfy to qualify for retrieval.
Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers : Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism : The Iron Law of Oligarchy : Libertarian Philosophy
War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda : SE quotes : Language Design and Programming Quotes : Random IT-related quotes : Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce : Bernard Shaw : Mark Twain Quotes
Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 : Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law
Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds : Larry Wall : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS : Programming Languages History : PL/1 : Simula 67 : C : History of GCC development : Scripting Languages : Perl history : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history
The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month : How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite
Most popular humor pages:
Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor
The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D
Copyright © 1996-2018 by Dr. Nikolai Bezroukov. www.softpanorama.org was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) in the author free time and without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.
FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.
This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...
|You can use PayPal to make a contribution, supporting development of this site and speed up access. In case softpanorama.org is down you can use the at softpanorama.info|
The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the author present and former employers, SDNP or any other organization the author may be associated with. We do not warrant the correctness of the information provided or its fitness for any purpose.
Last modified: September 12, 2017