MySQL The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at https://en.wikibooks.org/wiki/MySQL Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License. For a more general introduction see the SQL Wikibook. Structured Query Language is a third generation language for working with relational databases. Being a 3G language it is closer to human language than machine language and therefore easier to understand and work with. Dr. E. F. Ted Codd who worked for IBM described a relational model for database in 1970. In 1992, ANSI (American National Standards Institute), the apex body, standardized most of the basic syntax. Its called SQL 92 and most databases (like Oracle, MySQL, Sybase, etc.) implement a subset of the standard (and proprietary extensions that makes them often incompatible). Free as in Freedom - Released with GPL version 2 license (though a different license can be bought from Oracle, see below) Cost - Free! Support - Online tutorials, forums, mailing list (lists.mysql.com), paid support contracts. Speed - One of the fastest databases available. ([2]) Functionality - supports most of ANSI SQL commands. Ease of use - less need of training / retraining. Portability - easily import / export from Excel and other databases Scalable - Useful for both small as well as large databases containing billions of records and terabytes of data in hundreds of thousands of tables. Permission Control - selectively grant or revoke permissions to users. MySQL is available under a dual-licensing scheme: Under the GNU General Public License, version 2, ("or later" allowed in versions released before 2007): this is a Free (as in freedom), copyleft software license that allows you to use MySQL for commercial and non-commercial purposes in your application, as long as your application is released under the GNU GPL. There is also a "FLOSS Exception" which essentially allows non-GPL'd but Free applications (such as the PHP programming language, under the PHP license) to connect to a MySQL server. The exception lists a set of free and open-source software license that can be used in addition to the GNU GPL for your MySQL-dependent Free application. A so-called "commercial" , paid license, that is, a license where MySQL grants you the right to integrate MySQL with a non-FLOSS application that you are redistributing outside your own organization. MySQL is Free Software, so some forks and unofficial builds delivering contributions from the community exist. In 2008 Sun Microsystems bought MySQL, Sun being itself later acquired by Oracle, in 2010. After the acquisition, the development process has changed. The team has started to release new MySQL versions less frequently, so the new code is less tested.There were also less contributions from the community. In 2009 Monty Widenius, the founder of MySQL, left the company and created a new one, called The Monty Program. He started a new fork called MariaDB. The scopes of MariaDB, import all the new code that will be added to the main MySQL branch, but enhancing it to make it more stable; clean the MySQL code; add contributions from the community (new plugins, new features); develop the Aria storage engine, formerly named Maria; improving the performance; adding new features to the server. The license is the GNU GPLv2 (inherited from MySQL). The primary platform for MariaDB is GNU/Linux, but also works on one proprietary system. The following Storage Engine have been added: Aria (also used for internal tables) PBXT XtraDB FederatedX SphinxSE OQGRAPH Others may be added in the future. In 2008 Brian Aker, chief architect of MySQL, left the project to start a new fork called Drizzle. While Oracle initially funded the project, Drizzle is now funded by Rackspace. Its characteristics are: only a small part of the MySQL code has survived in this fork, the rest being removed: only essential features are implemented in the Drizzle server; the survived code has been cleaned; Drizzle is modular: many features are or can be implemented as plugins; the software is optimized for multiCPU and multicore 64 bit machines; only GNU/Linux and UNIX systems are supported. There are no public releases of this fork, still. Its main license will be the GNU GPLv2 (inherited from MySQL), but where possible the BSD license is applied. OurDelta is another fork, maintained by Open Query. The first branch, which has number 5.0, is based on MySQL 5.0. The 5.1 branch is based on MariaDB. OurDelta includes some patches developed by the community or by third parties. OurDelta provides packages for some GNU/Linux distributions: Debian, Ubuntu, Red Hat/CentOS. It is not available for other systems, but the source code is freely available. Percona Server is a MySQL fork maintained by Percona. It provides the ExtraDB Storage Engine, which is a fork of InnoDB, and some patches which mainly improve the performance. Calling it "commercial" is misleading, because the GNU GPL can be used in commercial (but non-proprietary) projects. Proprietary projects still can connect to a MySQL server without purchasing this license by using old versions of the MySQL client connection libraries (under the GNU Lesser General Public License). However, these libraries cannot connect to the newest versions of the MySQL server. As MySQL alone isn't enough to run a real database server, the more practical way to install it is to deploy an all in one pack in this purpose, including all the needed additional elements: Apache and PHP. On Linux: XAMP or LAMP. On Windows: XAMP, WAMP, or EasyPHP. Attention on Windows 10: The server IIS is launched by default, which forces Apache to change its port (888 instead of 80). To resolve this, just untick Internet Information Services in Programs and functionalities, Activate or deactivate the Windows functionalities. In the same way, the MySQL port can change from 3306 to 3388. Moreover, EasyPHP development server (alias Devserver, the red version) doesn't work properly (MSVCR110.dll is missing) but EasyPHP hosting server (alias Webserver, the blue one) yes. However, it launched automatically at each boot which slows the system significantly. To avoid this, execute services.msc, and toggle the three services below in manual start. Then to launch them on demand (as an administrator), create a script called MySQL.cmd, containing the following lines: net start ews-dbserver net start ews-httpserver net start ews-dashboard pause net stop ews-dashboard net stop ews-httpserver net stop ews-dbserver This guide is written from the perspective of using the Linux Shell with Ubuntu and apt-get[3]. If you want to solely use the Terminal: Make sure you have the MySQL Client and Server installed. To install the client and the server under apt-get distributions (for example Debian and Ubuntu), Execute: apt-get install mysql-client mysql-client-5.0 mysql-server mysql-server-5.0 About the MySQL package: [4] Having a secure installation: If all your answers are "yes" to what follows, this cleans up your installation, forces you to set a root password, asks you to test for anonymous users and makes your database internal. Just be careful. Be sure that you are configuring MySQL to the specifications you want. Here's the code: mysql_secure_installation Now that MySQL is installed, you wouldn't necessarily have your own account, so you have to log in as root. To do this type: sudo mysql -u root -p (This means that you're logging on as the user "root" (-u root) and that you're requesting the password for "root" (-p) ) Once you've managed to log in, your command-line should look like this: mysql> By the way, if your command-line ends up looking like this: -> theres an explanation behind it. In MySQL each command you do has to end with ; . This way it knows that everything behind ; is a command. So to get out of there, simply type ; There will be more on this later. Now you can check what databases (if any) are available to your user (in this case "root" ): show databases; Let's get straight to the chase and create our own database. Let's call it people. While we're doing this we can also create our own user account. Two birds with one stone. So first create the database: create database people; (NOTE: in this particular case, you have to be "root" to create new databases.) Now we want to grant ( GRANT ) all user rights ( ALL ) from ( ON ) the entire ( * ) people database to ( TO ) your account ( yourusername@localhost ) with your user password being stuffedpoodle ( IDENTIFIED BY "stuffedpoodle" ). So we'd input this as: GRANT ALL ON people. * TO yourusername@localhost IDENTIFIED BY "stuffedpoodle"; Tada! You now have your own user account. Let's say you chose ted as your username. You've configured MySQL to say that ted can play around with the people database in whatever ways he wishes. Now get out of MySQL by typing exit To start working with the people database, you can now login as ted: mysql -u ted -p In MySQL information is stored in tables. Tables contain columns and rows. Ted has now created a people database. So we want now to enter some information into a table. Login as ted. Firstly, we need to make sure we're working with the people database. So typing: select database(); will show you what database you're currently using. You should see a NULL , meaning that you're working with nothing at the moment. So to start using the people database, type: \u people (NOTICE: Typing: USE people OR logging in as mysql people -u ted -p is also acceptable.) So how to create a table. Keep in mind that we need to set all the column values (like surname, age etc.). Now, remember that annoying -> symbol? MySQL reads your command as just one command, not a series. So, -> enables you to enter your inputs in a nicer way than just writing everything on one line. (NOTE: The problem with this method is that if you screw up on a line and press ENTER to go to the next line, you can't go back and fix your mistake. That's why a nice way to do this is using something like SciTE Text Editor (set language to SQL) to write your code and just copy/paste that into the shell.) Another thing is that you must separate your lines with , at the end of each line except when you've written your two last lines. On the second to last line, don't add , and the last line always ends with ; . First I have to explain a few things so you're not blown away by an unfamiliar bunch of code. If you don't know, we use brackets () to encapsulate code. (Often called parenthesis). The first thing we will be writing after the CREATE TABLE tableName and the first bracket will be the database ID number(we use integers [5]) of each person, mainly known as the Primary Key. It's kinda like a passport ID number. Each number is unique to its owner and it has to be to prevent duplication and imposters. Now, any variable in SQL is created as variableNAME variableTYPE otherVariableAttributes . So in order to define the Primary Key variable, we need to type for example: peopleID(variableNAME) int(variableTYPE - short for "integer") unsigned(means we want our integer value to always be a positive number) not null(we want each row to have a value, so obviously the value can't be empty(NULL) ) auto_increment(this ensures that each new row that is created will be a unique value) primary key(we are saying that this particular variable will be our Primary Key for this Table. ), (a reminder that the , symbol indicates the end of this line so MySQL knows to go to the next line) You already know about the int variable. There is another which is kinda like String (for example: if you've programmed in Java before). It's called varchar which stands for variable characters. You set the amount of characters someone is able to input into a varchar variable. Like this: nameOfFattestMooseAlive varchar(30) So nameOfFattestMooseAlive can have a maximum of 30 characters. Okay, so let's see an example of how to create a table relating to the people database: CREATE TABLE peopleInfo ( peopleID int unsigned not null auto_increment primary key, firstName varchar(30), lastName varchar(30), age int, gender varchar(13) ); Just a note that I set the maximum value of gender to 13 because "hermaphrodite" has 13 characters. :) Now you can type: CREATE TABLE peopleInfo and press ENTER if you'd like to start -> and write the rest of the code or you can use SCITE and copy/paste it into your shell. Great. We now completed our first Table. Now comes the part when we have to get some actual people into our peopleInfo Table. Since your already using the people database, you can type show tables; to see what tables are currently in your database. To see the properties of your table type: describe peopleInfo; So, how to fill in our peopleInfo table with people... This is done by telling MySQL what rows you are filling in and the actual information/data you want to fill in. So we want to insert into our table (specifying the rows) and inputting the values(actual data) that we want. (NOTE: We are not filling in the primary key.) To create our first person you would type this: INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Bill", "Harper", 17, "male"); Great. Now if you want to printout to the screen all the information about your table, type: select * from peopleInfo; and there you have it. Your table now has one person stored in it. Inserting lots of information into your table: A brief point that shall be covered later, MySQL backs-up itself in .sql files. The reason this is smart is because it backs-up the actual code inside the text file. Keeping this in mind, let's say we want to add 10 other people into your peopleInfo table. It would be one hell of a hassle typing each person into existence. What if there were a 1000? So I've graciously typed out the code of filling in 10 other people to a database. :) Create a blank .txt file and copy/paste this information into it, saving it as tenPeople.sql . INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mary", "Jones", 21, "female"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jill", "Harrington", 19, "female"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Bob", "Mill", 26, "male"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Alfred", "Jinks", 23, "male"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Sandra", "Tussel", 31, "female"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mike", "Habraha", 45, "male"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("John", "Murry", 22, "male"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jake", "Mechowsky", 34, "male"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Hobrah", "Hinbrah", 24, "hermaphrodite"); INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Laura", "Smith", 17, "female"); Excellent. Now we want to get all these people in our table. exit MySQL and go to the directory where you saved the tenPeople.sql file. Once there, to get all the data into your database, type: mysql -u ted -p people 17; will show everyone in your table who is older than 17. Little index here: > greater than < less than >= greater or equal to <= less than or equal to <> not equal to Let's say we wanted to display all people whose first names began with the letter "j". We would use the LIKE condition. (Makes sense, is your name LIKE the letter "j", well it start with j so yes. :) ) About the LIKE condition. [6] select * from peopleInfo where firstName LIKE "j%"; (NOTE: LIKE 's evil opposite cousin is NOT LIKE) There is a function called mysqldump. This is a way to backup your database. Remember how you managed to get information into your database from tenPeople.sql? Well that's how you restore information to a database. (In this particular case you gotta make sure that in your database you have a table called "peopleInfo") Now... To backup your database (in this case backup the people database): We first have to create the .txt file that we will be backing it up to. Open a blank .txt file and save it as backupfile.sql . Now we can type: mysqldump -u ted -p people > backupfile.sql Congratulations. You have now backuped your people database. WARNING! mysqldump is one of the worst ways to backup production databases for the following reasons: it will take quite a lot of time to dump data even more time to restore. Depends on datasize, it can be counted in days! locking problem with MyISAM tables or mixed environment Better solutions are based on binary copy. It allows you to perform non-locking, consistent backups. For MyISAM or mixed environment: LVM snapshots For InnoDB: LVM snapshots ZFS snapshots (for Solaris systems) InnoDB Hot Backup XtraBackup (similar to InnoDB Hot Backup but free) This graphic interface allows the generation of SQL code by selecting some options with the mouse. This software has its own wiki on http://wiki.cihar.com/pma/Welcome_to_phpMyAdmin_Wiki. To enter the SQL commands: Launch MySQL in shell: Linux: mysql -h localhost -u root MyDB Windows: "C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe" -h localhost -u root MyDB Or open an SQL window in PhpMyAdmin (eg: http://localhost/modules/phpmyadmin/#PMAURL-1:server_sql.php?server=1). select "hello world"; +-------------+ | hello world | +-------------+ | hello world | +-------------+ 1 row in set (0.00 sec) CREATE DATABASE database; Require? Privilege. mysqladmin create is a command-line wrapper for this function. NB: in MySQL, CREATE SCHEMA is a perfect synonym of CREATE DATABASE, contrarily to some other DBMS like Oracle or SQL Server. DROP DATABASE database; Require ? privilege. mysqladmin drop is a command-line wrapper for this function. The -f option can be used to suppress the interactive confirmation (useful for unattended scripts). In some 5.1.x versions there was a RENAME DATABASE db1 TO db2; command, but it has been removed because renaming databases via SQL caused some data loss problems. However, in the command-line, you can create/export/import/delete: mysqladmin create name2 mysqldump --opt name1 | mysql name2 mysqladmin drop -f name1 Another option, if you have root access, is to rename the database directory: cd /var/lib/mysql/ /etc/init.d/mysql stop mv name1/ name2/ /etc/init.d/mysql start You also need to drop privileges on name1 and recreate them on name2: UPDATE mysql.db SET `Db`='name2' WHERE `Db`='name1'; FLUSH PRIVILEGES; There is no direct copy command in MySQL. However, this can easily be done using some tools. The mysqldump command-line can be used to generate a complete flat-file copy of the database. You can then reinject this copy in another database. This requires a direct access to the database; if you do not have it, you may need to use phpMyAdmin instead. # First, clean-up the target database: mysqladmin drop -f base2 mysqladmin create base2 # Copy base1 to base2: mysqldump --opt base1 | mysql base2 To set an automatic backup every day at midnight, in Linux: $ crontab -e 0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS base1 | gzip -c > `date “+\%Y-\%m-\%d”`.gz With Linux: mysql -h localhost -u root MaBase < MaBase.sql With Windows, the program may not be into the environment variables: "C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe" -h localhost -u root MyDB < MyDB.sql Contrarily to the PhpMyAdmin importations, there is no limit. For example, we can load a 2 GB database in five minutes. Tools: MySQL Migration Toolkit MySQL Query Browser apparently includes a MySQL Table Editor module. Kexi (wikipedia: Kexi) DBDesigner begins to be old. It is released under the GNU GPL, but it cannot be fully considered as free software since it requires the non-free Kylix compiler to build. But MySQL AB acquired fabFORCE[citation needed], who distributed DB Designer, and MySQL Workbench is the next version. For now the project is still Alpha and not ready for use yet. Meanwhile, if you use the latest release of DBDesigner, you'll find that it cannot connect to MySQL, with the "unable to load libmysqlclient.so" error. To workaround this, Install the MySQL "Shared compatibility libraries" (from http://dev.mysql.com/downloads/mysql/5.0.html#downloads for version 5.0, generic RPMS aka MySQL-shared-compat.i386 will do). Replace DBDesigner's version of libmysqlclient.so with the newly installed one: sudo ln -sf /usr/lib/libmysqlclient.so.10 /usr/lib/DBDesigner4/libmysqlclient.so Find and install kylixlibs3-unwind-3.0-rh.4.i386.rpm Find an old xorg (e.g. xorg-x11-libs-6.8.2-37.FC4.49.2.1.i386.rpm from FC4) and extract it: rpm2cpio x.rpm | cpio -i Get libXft.so.1.1 in that package and install it: sudo cp libXft.so.1.1 /usr/lib ldconfig You now can connect to your MySQL5 server from DBDesigner4. Consider this a temporary work-around waiting for community (free) and commercial (not free) versions MySQL Workbench. Typical configuration : MySQL database on a host machine (which name is mysqlhost below) OOo 2 on a client machine (Debian GNU/Linux for instance) Connection via ODBC. It's a client configuration : we need mysql-client: aptitude install mysql-client Under Fedora/CentOS: yum install mysql Before installing ODBC, we can test the remote connexion locally: $ mysql -h mysqlhost -u user1 mysqldatabase -p Enter password: PassUser1 You must have create the database mysqldatabase and the user user1 on mysqlhost. It seems there is no problem (hope there is not ;-)): Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 to server version: 5.0.24a-Debian_5~bpo.1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> Then, it's possible to test, through different queries : mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysqldatabase | +--------------------+ 2 rows in set (0.00 sec) .... mysql> quit; Bye Fine ! Let's go with OOo and ODBC, on the client machine: aptitude install libmyodbc unixodbc For Fedora/CentOS: yum install mysql-connector-odbc unixODBC /etc/odbc.ini (empty file) and /etc/odbcinst.ini are created. odbcinst.ini declares the available ODBC driver. Here's the MySQL statement (paths to the .so files may vary depending on the distribution); for Debian: [MySQL] Description = MySQL driver Driver = /usr/lib/odbc/libmyodbc.so Setup = /usr/lib/odbc/libodbcmyS.so CPTimeout = CPReuse = FileUsage = 1 for CentOS: [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc3.so Setup = /usr/lib/libodbcmyS.so FileUsage = 1 Now we can use odbcinst : # odbcinst -j unixODBC 2.2.4 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /root/.odbc.ini For further options : man odbcinst First of all, we have to create at least one DSN (Data Source Name or Data Set Name), because every ODBC connection is initialized through an existing DSN. It's true in every cases, so it is required for an ODBC connection from OOo. To create a DSN, one have different possibilities : Modify /etc/odbc.ini (concerns all users) Modify ~/.odbc.ini (concerns a specific user) Use graphical applications such as ODBCConfig (Debian: unixodbc-bin, Fedora: unixODBC-kde). Finally, these graphical applications modify /etc/odbc.ini or ~/.odbc.ini For instance, a /etc/odbc.ini file (the name of the DSN is between brackets []): [MySQL-test] Description = MySQL ODBC Database TraceFile = stderr Driver = MySQL SERVER = mysqlhost USER = user1 PASSWORD = DATABASE = mysqldatabase In that case, the DSN is called MySQL-test Then we can test, using isql command: $ isql -v MySQL-test user1 PassUser1 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-------------------+ | Database | +-------------------+ | information_schema| | mysqldatabase | +-------------------+ 2 rows affected 2 rows returned SQL> quit; And now, from OOo: -> File -> New -> Database -> Connecting to an existing database -> MySQL -> Next -> Connect using ODBC -> Next -> Choosing a Data Source -> MySQL-test -> Next -> Username : user1 (tick password required) -> Yes, register the database for me -> Finish At that step, one is connected to the mysqldatabase database, under the user user1. Just before accessing the database, for example to create tables, one will give user1 password. Then, through OOo, it is now quite easy to access and manipulate the database. We can just notice that Java is required in the following cases : Wizard to create a form (at the opposite, to create a form directly don't need any JRE). Wizard to create reports. Wizard to create queries (at the opposite, to create a query directly or through a view don't need any JRE). Wizard to create tables (at the opposite, to create a table directly or to create a view don't need any JRE). GNU/Linux distros usually ships OpenOffice with IcedTea (openjdk-6-jre/java-1.6.0-openjdk) or GCJ (java-gcj-compat/java-1.4.2-gcj-compat) so that these Java-based features work. https://dev.mysql.com/doc/refman/5.1/en/rename-database.html http://stackoverflow.com/questions/6645818/how-to-automate-database-backup-using-phpmyadmin In the forums: [1] but we'd need something more official Browsing the databases Specifying table names Definitions User Variables Alias Data Types Table manipulation Data manipulation Queries Using/Dealing with NULL Operators Import/export Functions Exercises Reserved Words information_schema is a virtual database provided by MySQL 5 and later, that contains metadata about the server and the databases. You can't modify structure and data of information_schema. You can only query the tables. Many information_schema tables provide the same data you can retrieve with a SHOW statement. While using SHOW commands is faster (the server responds much faster and you type less characters), the information_schema provides a more flexible way to obtain and organize the metadata. The INFORMATION_SCHEMA table containing the databases information is SCHEMATA. The mysqlshow command line tool (DOS/Unix) can be used instead. You can't show databases if the server has been started with the --skip-all-databases option. If you don't have the 'SHOW DATABASES' privilege, you'll only see databases on which you have some permissions. The following SQL commands provide information about the databases located on the current server. Show all databases: SHOW DATABASES; The SCHEMA keywords can be used in place of DATABASES. MySQL doesn't support standard SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with other DBMSs. SHOW DATABASES LIKE 'pattern'; The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with 'my': SHOW DATABASES LIKE 'my%'; You can add more complex filters using the WHERE clause: SHOW DATABASES WHERE conditions; WHERE clause allows you to use regular expressions, '=', '<' and '>' operators, string functions or other useful expressions to filter the records returned by SHOW DATABASES. The following SQL commands provide information about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this information are `TABLES` and `VIEWS`. Since the following statements provide very little information about views, if you need to get metadata about them you'll probably prefer to query the VIEWS table. The mysqlshow command line tool can be used instead. USE `database`; SHOW TABLES; SHOW TABLES FROM `database`; The 2 forms shown above are equivalent. You can apply a filter to the tables names, to show only tables whose name match a pattern. You can use the LIKE operators, as you do in SELECTs or in the DML statements: SHOW TABLES LIKE `pattern`; Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause: SHOW TABLES WHERE condition; (see below) By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword: SHOW FULL TABLES; This will add a column called `Table_type`. This can have three values: 'BASE TABLE' for tables, 'VIEW' for views and 'SYSTEM VIEW' for special tables created by the server (normally used only INFORMATION_SCHEMA tables). So you can only list tables: SHOW FULL TABLES WHERE `Table_type`='BASE TABLE'; Or, you can only list views: SHOW FULL TABLES WHERE `Table_type`='VIEW'; You can get a list of the non-temporary tables (not views) which are open in the cache: SHOW OPEN TABLES; This command has the same parameters as SHOW TABLES, except for FULL (useless in this case). You can't get this information from the INFORMATION_SCHEMA. The following SQL commands provide information about the columns in a table or in a view. The INFORMATION_SCHEMA table containing this information is COLUMNS. The mysqlshow command line tool can be used instead. DESCRIBE `table`; DESCRIBE `database`.`table`; DESCRIBE `table` 'filter'; DESC can be used as a shortcut for DESCRIBE. 'filter' can be a column name. If a column name is specified, only that column will be shown. If 'filter' contains the '%' or the '_' characters, it will be evaluated as a LIKE condition. For example, you can list all fields which start with 'my': DESC `table` 'my%'; A synonym is: EXPLAIN `table`; Another synonym is: SHOW FIELDS FROM `table`; Another synonym is: SHOW COLUMNS FROM `table`; -- possible clauses: SHOW COLUMNS FROM `table` FROM `database`; SHOW COLUMNS FROM `table` LIKE 'pattern'; SHOW COLUMNS FROM `table` WHERE condition; FIELDS and COLUMNS are synonyms. EXPLAIN is a synonym of SHOW COLUMNS / FIELDS too, but it doesn't support all of its clauses. A databases name can be specified both in the form SHOW COLUMNS FROM `table` FROM `database`; both: SHOW COLUMNS FROM `database`.`table`; Using the FULL keyword, extra info can be retried: the columns' collation, privileges you have on the column and the comment. The following SQL commands provide information about the indexes in a table. Information about keys is contained in the `COLUMNS` table in the INFORMATION_SCHEMA. The mysqlshow -k command line tool can be used instead. SHOW INDEX FROM `TABLE`; SHOW INDEX FROM `TABLE` FROM `databases`; The KEYS reserved word can be used as a synonym of INDEX. No other clauses are provided. Result example: Remark: with phpMyAdmin it's easy to create the same index multiple times, which slows the requests. To remove an index: DROP INDEX `date_2` on `Table1` In this book, we will quote the MySQL identifiers (tables names, fields, databases, etc.) using backquotes (`). Backquote is ASCII 96. It can be type on Linux systems by pressing: ALT+'. Most often, this is optional. However, this allows better error messages from MySQL. For example, this error is not very helpful: mysql> SELECT user_id, group_id FROM user,group LIMIT 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group LIMIT 1' at line 1 But this one is better: mysql> SELECT user_id, group_id FROM `user`,`group` LIMIT 1; ERROR 1146 (42S02): Table 'savannah.group' doesn't exist Ok, it was just a missing s: mysql> SELECT user_id, group_id FROM `user`,`groups` LIMIT 1; +---------+----------+ | user_id | group_id | +---------+----------+ | 100 | 2 | +---------+----------+ 1 row in set (0.02 sec) This syntax allows the user to use reserved words and some illegal characters in objects' names. It is even possible to use backquotes by typing it twice: RENAME TABLE `user` TO ```` However, this is not a portable syntax. The SQL standard recommends the use of a double quote ("). If you want to write portable SQL quote, do not quote the identifiers. But is there something like portable SQL, even remotely? Data Definition Language (DDL) refers to the CREATE, ALTER and DROP statements. DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. DDL allows to add / modify / delete the logical structures which contain the data or which allow users to access / maintain the data (databases, tables, keys, views...). DDL is about "metadata". Data Manipulation Language (DML) refers to the INSERT, UPDATE and DELETE statements DML allows to add / modify / delete data itself. Data Query Language (DQL) refers to the SELECT, SHOW and HELP statements (queries) SELECT is the main DQL instruction. It retrieves data you need. SHOW retrieves infos about the metadata. HELP... is for people who need help. Data Control Language (DCL) refers to the GRANT and REVOKE statements DCL is used to grant / revoke permissions on databases and their contents. DCL is simple, but MySQL's permissions are rather complex. DCL is about security. Data Transaction Language (DTL) refers to the START TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK [TO SAVEPOINT] statements DTL is used to manage transactions (operations which include more instructions none of which can be executed if one of them fails). The local variables can't be reached from outside their function or stored procedure. They are declared like this: DECLARE MyVariable1 INT DEFAULT 1; The ability to set variables in a statement with the := assignment operator: For e.g. (@total) to calculate the total in an example, you have to have the total column first because it must be calculated before the individual percentage calculations. Session variables are set for the duration of the thread. In the vast majority of cases you'd use a programming language to do this sort of thing. MySQL variables can be useful when working on the MySQL command line. If no records are returned, the user variable will not be set for that statement. A user variable set in the field list cannot be used as a condition. The value of a variable is set with the SET statement or in a SELECT statement with := select @test := 2; select @test + 1; -- returns 3 set @startdate='some_start_date', @enddate='some_end_date' SELECT @toremember:=count(*) FROM membros; select @numzero := count(*) from table1 where field=0; select @numdistinct := count(distinct field) from table1 where field <> 0 ; select @numzero @numdistinct; You can copy values retrieved by a SELECT into one or more variables: SET @id = 0, @name = ''; SELECT id, name INTO @id, @name FROM table1 limit 1; SELECT @id, @name; A global variable is visible to all users, it allows to modify the configuration files settings during the session or definitely. So when changing them, it's necessary to precise this permanent or ephemera criteria, with respectively set global and set session. Example: mysql> set @@global.max_connections = 1000; mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 60 | +---------------+-------+ 1 row in set (0.00 sec) mysql> set @@session.wait_timeout=120; http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference http://dev.mysql.com/doc/refman/5.7/en/declare-local-variable.html An expression and a column may be given aliases using AS. The alias is used as the expression's column name and can be used with order by or having clauses. For e.g. SELECT CONCAT(last_name,' ', first_name) AS full_name, nickname AS nick FROM mytable ORDER BY full_name These aliases can be used in ORDER BY, GROUP BY and HAVING clauses. They should not be used in WHERE clause. A table name can have a shorter name for reference using AS. You can omit the AS word and still use aliasing. For e.g. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings AS B ON U.User_ID = B.Rep_ID AND B.Project_ID = '10' GROUP BY (U.User_Location) Aliasing plays a crucial role while you are using self joins. For e.g. people table has been referred to as p and c aliases! SELECT p.name AS parent, c.name AS child, MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) AS minage FROM people AS p LEFT JOIN people AS c ON p.name=c.parent WHERE c.name IS NOT NULL GROUP BY parent HAVING minage > 50 ORDER BY p.dob; VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (upto 65,535 characters). A VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 or 2 bytes (1 if the length is < 255) to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes. CHAR(n) is similar to varchar(n) with the only difference that char will occupy fixed length of space in the database whereas varchar will need the space to store the actual text. So a CHAR field takes more storage space, but is 20% faster to search if index. A BLOB or TEXT column with a maximum length of 65,535 characters. The required space is the real length of the stored data plus 2 bytes (1 byte if length is < 255). The BLOB / TEXT data is not stored in the table's data file. This makes all operations (INSERT / UPDATE / DELETE / SELECT) involving the BLOB / TEXT data slower, but makes all other operations faster. Specifying an n value has no effect whatsoever. Regardless of a supplied value for n, maximum (unsigned) value stored is 429 crores. If you want to add negative numbers, add the "signed" keyword next to it. Attention: to store a number greater than the limit (eg: 1234567890123456789), the software can tell that the operation has been done although it hasn't. So the type must be declared as BIGINT in this case. Remark: the booleans are declared as tinyint(1). The number between the parenthesis after the integer types indicates on how many digits the stored integer should be displayed. However, if it's longer than that, it won't affect its storing. decimal(n,m) decimal(4,2) means numbers upto 99.99 (and NOT 9999.99 as you may expect) can be saved. Four digits with the last 2 reserved for decimal. Out of the three types DATETIME, DATE, and TIMESTAMP, the DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The DATETIME type is used when you need values that contain both date and time information. The difference between DATETIME and TIMESTAMP is that the TIMESTAMP range is limited to 1970-2037 (see below). TIME can be used to only store the time of day (HH:MM:SS), without the date. It can also be used to represent a time interval (for example: -02:00:00 for "two hours in the past"). Range: '-838:59:59' => '838:59:59'. YEAR can be used to store the year number only. If you manipulate dates, you have to specify the actual date, not only the time - that is, MySQL will not automagically use today as the current date. On the contrary, MySQL will even interpret the HH:MM:SS time as a YY:MM:DD value, which will probably be invalid. The following examples show the precise date range for Unix-based timestamps, which starts at the Unix Epoch and stops just before the first new year before the 2 31 − 1 {\displaystyle 2^{31}-1} usual limit (2038). mysql> SET time_zone = '+00:00'; -- GMT Query OK, 0 rows affected (0.00 sec) mysql> SELECT FROM_UNIXTIME(-1); +-------------------+ | FROM_UNIXTIME(-1) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(0); -- "Epoch" +---------------------+ | FROM_UNIXTIME(0) | +---------------------+ | 1970-01-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916799); +---------------------------+ | FROM_UNIXTIME(2145916799) | +---------------------------+ | 2037-12-31 23:59:59 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916800); +---------------------------+ | FROM_UNIXTIME(2145916800) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec) A SET datatype can hold any number of strings from a predefined list of strings specified during table creation. The SET datatype is similar to the ENUM datatype in that they both work with predefined sets of strings, but where the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype allows you to store any of the values together, from none to all of them. Example: SET("madam", "mister") -- authorizes an empty field, "madam", "mister", "madam, mister", or "mister, madam" ENUM("madam", "mister") -- authorizes an empty field, "madam" or "mister" https://dba.stackexchange.com/questions/424/performance-implications-of-mysql-varchar-sizes/1915#1915 https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html Create table syntax is: Create table tablename (FieldName1 DataType, FieldName2 DataType) The rows returned by the "select" query can be saved as a new table. The datatype will be the same as the old table. For e.g. CREATE TABLE LearnHindi select english.tag, english.Inenglish as english, hindi.Inhindi as hindi FROM english, hindi WHERE english.tag = hindi.tag The table size limit depends on the filesystem, and is generally around 2TB. Moreover, MySQL can assure the unique keys auto-incrementation with the option AUTO_INCREMENT. In case of table truncation, the counter can be reset with: ALTER TABLE tablename AUTO_INCREMENT = 1 To duplicate the same structure (names, fields types, and indexes, but no record): CREATE TABLE `new1` LIKE `old1`; To copy the records into the previous result: INSERT INTO `new1` SELECT * FROM `old1`; It's possible to create variables of type "table", which as the other variables, will be erased at the end of their scripts. It's called the "temporary tables": CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1 AS (SELECT * FROM MyTable1) Example with a named column: CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1(id INT) AS (SELECT id FROM MyTable1) Attention: if the temporary table column name doesn't correspond to the field which fills it, an additional column will be added with this field name. Eg: CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1(id1 INT) AS (SELECT id FROM MyTable1); SHOW FIELDS FROM MyTempTable1; Field Type Null Key Default Extra id1 int(11) YES NULL id int(11) NO 0 Attention: all temporary tables are dropped at the end of the MySQL connection which had created them. ALTER TABLE command can be used when you want to add/delete/modify the columns and/or the indexes; or, it can be used to change other table properties. Add a column: ALTER TABLE awards ADD COLUMN AwardCode int(2) Modify a column: ALTER TABLE awards CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL ALTER TABLE awards MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL Drop a column: ALTER TABLE awards DROP COLUMN AwardCode Re-order the record in a table: ALTER TABLE awards ORDER BY id (this operation is only supported by some Storage Engines; it could make some query faster) In order to rename a table, you must have ALTER and DROP privileges on the old table name (or on all the tables), and CREATE and INSERT privileges on the new table name (or on all the tables). You can use ALTER TABLE to rename a table: RENAME TABLE `old_name` TO `new_name` You can rename more than one table with a single command: RENAME TABLE `old1` TO `new1`, `old2` TO `new2`, ... RENAME is a shortcut. You can also use the ALTER TABLE statement: ALTER TABLE `old` RENAME `new` Using ALTER TABLE you can only rename one table per statement, but it's the only way to rename temporary tables. DROP TABLE `awards` Will completely delete the table and all the records it contains. You can also drop more than one table with a single statement: DROP TABLE `table1`, `table2`, ... There are come optional keywords: DROP TEMPORARY TABLE `table`; DROP TABLE `table` IF EXISTS; TEMPORARY must be specified, to drop a temporary table. IF EXISTS tells the server that it must not raise an error if the table doesn't exist. http://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html http://www.mysqltutorial.org/mysql-temporary-table/ The syntax is as follows: Insert value1 into Column1, value2 into Column2, and value3 into Column3: INSERT INTO TableName (Column1, Column2, Column3) VALUES (value1, value2, value3) Insert one record (values are inserted in the order that the columns appear in the database): INSERT INTO TableName VALUES (value1, value2, value3) Insert two records: INSERT INTO TableName VALUES (value1, value2, value3), (value4, value5, value6) INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00); INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman'); You can also insert records 'selected' from other table. INSERT INTO table1(field1, field2) SELECT field1, field2 FROM table2 INSERT INTO World_Events SELECT * FROM National_Events Performance tips: To insert many rows, consider using LOAD DATA INFILE instead. If bulk INSERTs are too slow and they operate on indexed non-empty tables, maybe you should increase the value of bulk_insert_buffer_size. Before performing bulk inserts, you may want to disable the keys. LOCKing a table also speeds up the INSERT. The syntax is: UPDATE table SET field1 = newvalue1, field2 = newvalue2 WHERE criteria ORDER BY field LIMIT n Examples are: UPDATE owner SET ownerfirstname = 'John' WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase'); UPDATE antiques SET price = 500.00 WHERE item = 'Chair'; UPDATE order SET discount=discount * 1.05 UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl2.status='Active' UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz') UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id = p.id SET pc.prod_sequential_id = p.sequential_id UPDATE table_name SET col_name = REPLACE(col_name, 'host.domain.com', 'host2.domain.com') UPDATE posts SET deleted=True ORDER BY date LIMIT 1 With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT). It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP: mysql> UPDATE spip_auteurs SET pass = (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin'; ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause TODO: describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions. Performance tips UPDATEs speed depends of how many indexes are updated. If you UPDATE a MyISAM table which uses dynamic format, if you make rows larger they could be split in more than one part. This causes reading overhead. So, if your applications often do this, you may want to regularly run an OPTIMIZE TABLE statement. Performing many UPDATEs all together on a LOCKed table is faster than performing them individually. REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. Since MySQL 5.5, "INSERT IGNORE" and "REPLACE IGNORE" allow, when a duplicate key error occurs, to display some warnings and avoid the statement to abort. Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations. DELETE [QUICK] FROM `table1` TRUNCATE [TABLE] `table1` If you don't use a WHERE clause with DELETE, all records will be deleted. It can be very slow in a large table, especially if the table has many indexes. If the table has many indexes, you can make the cache larger to try making the DELETE faster (key_buffer_size variable). For indexed MyISAM tables, in some cases DELETEs are faster if you specify the QUICK keyword (DELETE QUICK FROM ...). This is only useful for tables where DELETEd index values will be reused. TRUNCATE will delete all rows quickly by DROPping and reCREATE-ing the table (not all Storage Engines support this operation). TRUNCATE is not transaction-safe nor lock-safe. DELETE informs you how many rows have been removed, but TRUNCATE doesn't. After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster. For a InnoDB table with FOREIGN KEYs constraints, TRUNCATE behaves like DELETE. DELETE FROM `antiques` WHERE item = 'Ottoman' ORDER BY `id` LIMIT 1 You can order the rows before deleting them, and then delete only a given number of rows. http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ http://dev.mysql.com/doc/refman/5.5/en/replace.html select syntax is as follows: SELECT * FROM a_table_name WHERE condition GROUP BY grouped_field HAVING group_name condition ORDER BY ordered_field LIMIT limit_number, offset You must specify what data you're going to retrieve in the SELECT clause: SELECT DATABASE() -- returns the current db's name SELECT CURRENT_USER() -- returns your username SELECT 1+1 -- returns 2 Any SQL expression is allowed here. You can also retrieve all fields from a table: SELECT * FROM `stats` If you SELECT only the necessary fields, the query will be faster. If you are retrieving results from a table or a view, usually you specify the table's name in the FROM clause: SELECT id FROM `stats` -- retrieve a field called id from a table called stats Or: SELECT MAX(id) FROM `stats` SELECT id*2 FROM `stats` You can also use the `db_name`.`table_name` syntax: SELECT id FROM `sitedb`.`stats` But you can also specify the table's name in the SELECT clause: SELECT `stats`.`id` -- retrieve a field called id from a table SELECT `sitedb`.`stats`.`id` You can set a filter to decide what records must be retrieved. For example, you can retrieve only the record which has an id of 42: SELECT * FROM `stats` WHERE `id`=42 Or you can read more than one record: SELECT * FROM `antiques` WHERE buyerid IS NOT NULL You can group all records by one or more fields. The record which have the same value for that field will be grouped in one computed record. You can only select the grouped record and the result of some aggregate functions, which will be computed on all records of each group. For example, the following will group all records in the table `users` by the field `city`. For each group of users living in the same city, the maximum age, the minimum age and the average age will be returned: SELECT city, MAX(age), MIN(age), AVG(age) GROUP BY `city` In the following example, the users are grouped by city and sex, so that we'll know the max, min and avg age of male/female users in each city: SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city`, `sex` The HAVING clause declares a filter for the records which are computed by the GROUP BY clause. It's different from the WHERE clause, that operates before the GROUP BY. Here's what happens: The records which
{'wiki_source': 'wikibooks', 'other_info': "{'content_model': 'wikitext', 'language': 'en', 'title': 'MySQL/Print version', 'type': 'text'}"}