mysql -u root (assuming this gets you into the root user) mysql> show databases; mysql> create database test; (it may already exist) mysql> show databases; mysql> select user,host,password from mysql.user; mysql> create user guest@localhost; (it may already exist) mysql> select user,host,password from mysql.user; mysql> select user,host from mysql.db; mysql> grant all on test.* to guest@localhost; mysql> select user,host from mysql.db; mysql> quitTest the effectiveness by accessing the test database as the guest user:
mysql -u guest test -p Password: ENTERCompare the differences in priviledges between the root and guest users (the \G statement terminator is used to field information in list form instead of tabular form):
mysql -u root mysql mysql> select * from user where user='root' and host='localhost' \G mysql> select * from user where user='guest' and host='localhost' \G
SQL syntax learning examplesHere are some examples you can use to help learn basic SQL commands. The test database accessible by the guest user without password is assumed. Start a command shell and, from the command-line, execute:
mysql -u guest -p (you're into MySQL, but not any database) mysql> use test; mysql> create table things (item varchar(10), qty int); mysql> show tables; mysql> describe things; mysql> insert into things values ( 'book', 10 ); mysql> insert into things values ( 'pencil', 4 ), ( 'book', 5 ); mysql> select * from things; mysql> delete from things where item='book'; mysql> select * from things; mysql> insert into things values ('table', 2), ('chair',12); mysql> select * from things; mysql> update things set qty=qty-1 where qty>10; mysql> select * from things; mysql> update things set qty=qty+1 where item='table'; mysql> select * from things; mysql> drop table things; mysql> quit
sqlite-tools-win32-x86-3160200in which you can find the executable sqlite3.exe. Unlike the mysql client program which accesses the MySQL database wherever it is called, it is easiest to be "right next" to the SQLite database file when sqlite3 is used. Therefore, to make effective use of this executable, you need to install this into a folder which is accessible to the system PATH. I would recommend making a dedicated folder for such binaries:
C:\binand moving sqlite3.exe into this folder. Then add the folder C:\bin into the PATH. From the Control Panel, select
System ⇾ Advanced system settings ⇾ Environment variables.Edit for Path in the System Variables or User Variables list. Append this text to the end:
;C:\binor prepend this to the beginning:
C:\bin;For MAC OSX, the executable sqlite3 may already be on your system. If not, there is an executable which can be downloaded from the SQLite site. For Ubuntu Linux, install it as:
$ sudo apt-get install sqlite3
Client usageThe sqlite3 client behaves much like mysql. The most obvious difference is the meta-commands (show tables, describe, etc.), which are achieved through commands starting with a period. Try this, creating the database file sample.sqlite:
sqlite3 sample.sqlite sqlite> .help sqlite> create table things (item varchar(10), qty integer); sqlite> .schema things sqlite> insert into things values ( 'pencil', 4 ), ( 'book', 5 ); sqlite> select * from things; sqlite> .headers on sqlite> select * from things; sqlite> .quitA very useful feature is working with efficient in-memory databases. For example, try this:
sqlite3 sqlite> create table things (item varchar(10), qty integer); sqlite> insert into things values ( 'pencil', 4 ), ( 'book', 5 ); sqlite> .save sample1.sqlite sqlite> .quit
MySQL WorkbenchMySQL itself offers a multi-platform GUI tool
$ sudo apt-get install mysql-workbench
PhpMyadminThis already comes with the XAMPP software. If you have a web server (preferably Apache) which can access the MySQL, a good, multi-platform choice is Navicat software is excellent and is available for multiple platforms and multiple DBMSs. Unfortunately, it is not free in its current incarnation. Prior to 2011, a free stripped-down version called Navicat Lite was provided as an inducement to buy the full version. This frozen-in-time version is still free and available from various download sites and still suits most of our needs.
Download Navicat Lite from the course web site:
$ cd ~/Downloads $ sudo tar xvozf navicat100_lite_en.tar.gz -C /usr/localThe executable is
/usr/local/navicat100_lite_en/start_navicatA good idea is to make a "desktop launcher" out of this the procedure to do so depends on your Linux distribution. No icon is provided so you can use this one (navicat.png):
Navicat MySQL usageAfter installation, start it up and create a root connection to your MySQL installation. On MAC OX X, you have to initially open it through the Applications folder by right-clicking. Thereafter you can open it normally. Press the Connection button.
- Select MySQL from the choices.
- Set the Connection Name to root. Everything else should be in place already. If your password is not empty, enter it into the password field.
- Click the Test Connection button to double-check.
- Make sure the Save Password checkbox is checked.
MySQL Command-line administrationMySQL software provides a number of useful commands to manipulate its databases, including:
- mysqladmin: basic administration commands
- mysqldump: dump the contents of table(s) in a database
- mysqlshow: show table/fields in a database
- mysql command line interpreter for entering SQL commands
$ mysqlshow -u root $ mysqlshow -u root mysql $ mysqlshow -u root mysql user $ mysqldump -u root mysql userTo ensure that changes made to the mysql database are picked up, do one of this::
$ mysqladmin -u root reloadThere's also the "refresh" command which has a somewhat different outcome.
Backup and reloadThe mysql commands provide an excellent scheme by which a database can be "backed up" and then "reloaded". Do the backup like this:
$ mysqldump -u root somedb > somedb.sqlThe somedb.sql file contains the data in all the tables plus the commands needed to recreate these tables. A restoration from an non-existent database would be done like this:
$ mysql -u root mysql> create somedb mysql> use somedb mysql> source somedb.sql
MySQL access principlesMySQL is a network-oriented DBMS. Client programs may reside on different hosts than the server. The access rights of MySQL client has to a MySQL DBMS database is determined at the beginning by three factors managed by the administrative mysql database:
- the user specified by the client
- the host on which the client operates
- the database that the client is trying to access
- user table:
When the client on host connects to the MySQL server, the pair
(user, host)is matched against the (user,host) values in the rows of the user table. If no such user or host exists, an empty is used to match. If the password field is non-empty the client must provide the password. The client obtains global privileges from the remaining fields in the record.
- db table:
Each database, with user access information is listed in the db table.
(user, database)is used to match against the entries in the db table, and therefore further augment the privileges available to the client as this particular user.
- for different tables within a single database (tables_priv)
- individual access privileges for columns within a database table (columns_priv)
- access privileges for stored procedures within a database (proc_priv)
$ mysql -u root mysql mysql> describe user; mysql> select user,host,password from user; mysql> describe host; mysql> select host,db from host; mysql> describe db; mysql> select user,db from db; mysql> select * from db where db='test' \G (mostly 'Y')The root user has all global privileges and the anonymous user has none. Nevertheless, the anonymous user gains privileges for the test database by matching an entry in the db table.