MySQL is one of the most widely used relational database management systems (RDBMS). MySQL is used to manage databases in a wide variety of applications including the integrated web solution known as LAMP (Linux Apache MySQL Perl\/PHP\/Python). Database management is accomplished in MySQL using Structured Query Language (SQL). Because it scales well, MySQL works for projects of almost any size and complexity. Strong security features and the abundance of tools to control it make MySQL attractive to users who want to protect their data and still have easy access to and easy use of it. <\/p>\n
This guide has been created to serve as a reference for most basic commands in SQL. Below, we have used a random test database to show you the basic principles of operating MySQL directly from the Command Line Interface (CLI). If you master the commands below, you will be well on your way to understanding how to effectively manage a database using MySQL. <\/p>\n
Connecting to MySQL<\/strong><\/p>\n To get started, establish an active SSH session on your server. We will connect using the root user of our MySQL instance for convenience. To follow along, you will need to know your MySQL root password to initiate your session.<\/p>\n The appearance of the mysql> prompt after you enter the command to initiate the session indicates you are connected to the MySQL instance on your server and can execute commands.<\/p>\n View databases<\/strong><\/p>\n Our first command is SHOW DATABASES. When you enter this command, a list of all the active databases on this MySQL instance will print to the screen.<\/p>\n Create a database<\/strong><\/p>\n To use MySQL for your own projects you will need your own databases. Whether you want to start from scratch, or import a schema or data from an existing database you will need first to create the databases you want to use in this instance of MySQL. Go ahead and create the database “mynewdb” with the following command:<\/p>\n Your database “mynewdb” is now ready to use. Now, you can create tables in it or import the contents of an existing database or schema.<\/p>\n Use a database<\/strong><\/p>\n The USE command will enable you to select a specific database to work on. For instance, you may use mynewdb by entering<\/p>\n Populating a database<\/strong><\/p>\n You have two basic options for populating mynewdb: Manually create tables and insert data into those tables or import the contents of an existing database or schema structure into mynewdb. Because importing is more straightforward, we will start there.<\/p>\n Import data<\/strong><\/p>\n To import data from an existing database, you must execute the command outside of MySQL, directly in the SSH command line. MySQL database backups are text files that are typically created with the .sql file extension name so that they may be easily identified as database backups. For our example, we'll import the contents of the radius.sql file into the mynewdb database.<\/p>\n The IMPORT command populated mynewdb by creating a copy of all of the tables and records that were found in the original database. MySQL accomplished this duplication by running every SQL command that had been run to populate the original database.<\/p>\n Delete a database<\/strong><\/p>\n When a database has reached the end of its useful life deleting it is quite a straightforward process. Simply do as we show below to remove a database from your MySQL instance.<\/p>\n Be careful with this command. Once a database is deleted the data in it cannot be recovered from within MySQL. Of course, if you have retained a backup you can always create a new database and import the contents of the old one.<\/p>\n Create a table<\/strong><\/p>\n MySQL databases are built using tables. The creation a MySQL table is a complex task. Tables are so customizable that they really need to be designed to meet your specific needs or the needs of your application. To keep this guide brief we'll show you how we created a specific table we'll be using in our further examples below. <\/p>\n One of the tables in our sample database was created like so:<\/p>\n Show tables within the database<\/strong><\/p>\n Tables are associated with the database in which they are created. To see a list of all the tables in the database with which you are working use the SHOW TABLES command:<\/p>\n If you are following along with our guide here, you should see the newly created table in your list.<\/p>\n Delete a table<\/strong><\/p>\n Occasionally, you realize you don\u2019t need a table anymore. Simply follow the command below to eliminate the unwanted table.<\/p>\n As with the delete database command, you should use this command with caution as it is not easy to recover from the mistake of using it when you don\u2019t mean to do so. <\/p>\n Show the structure of a table<\/strong><\/p>\n During the lifetime of your database, you will likely want to add (INSERT) or modify (UPDATE) fields. To use both the INSERT and UPDATE commands effectively you need to know what is already in the database. To list the existing columns, rows, and all their associated parameters use the DESCRIBE command:<\/p>\n Select rows from a table<\/strong><\/p>\n Use the SELECT command to view a particular portion of your data in a table (or all of it). This command is highly customizable. We'll show you a few variants below.<\/p>\n Select everything<\/strong><\/p>\n If you wish to generate a list of everything from the table enter the following.<\/p>\n Select everything where groupname is default<\/strong><\/p>\n Groupname is the title of a column or field in our table. Use of the command<\/p>\n will return the whole row where the value of the entry in the column groupname matches the \"default\" value.<\/p>\n Select everything where value is smaller or equal to 15<\/strong><\/p>\n The following will return the whole row where the value field is populated with a value less than or equal to 15 (<=). \n\n\n You may construct similar commands with other operators such as <, >, <=, >= and =.<\/p>\n Select the data from only 1 field for all rows<\/strong><\/p>\n In this example, we will select only one field from the table in question.<\/p>\n The data is displayed in a single column rather than being formatted like the whole table.<\/p>\n Select the data from only multiple fields for all rows<\/strong><\/p>\n Here we will select multiple fields from the table in question. You'll see that the data is displayed in two columns.<\/p>\n Following the pattern established above, you can select the data from as many or as few of the fields in a table as you wish.<\/p>\n INSERT Rows in a Table<\/strong><\/p>\n The INSERT command enables you to insert rows into a specific table. Inserts can be performed in a few different manners. The method you choose will depend on the mechanics of your code. We will show you two common methods to give you a general sense for how it is done.<\/p>\n First insert method<\/strong><\/p>\n Our first example of how to use INSERT is based on our previous examples of how to use SELECT. This command creates a row consistent with the same table format used above:<\/p>\n To check that the row has been inserted, we can perform a select on radgroupreply. The command and results should appear as follows:<\/p>\n A new method of inserting data into MySQL tables has gained popularity recently. This method achieves the same thing as the first but the formatting of the query is quite a bit different, as you can see below:<\/p>\n As with the previous INSERT method, to confirm the insertion worked properly, perform a select on radgroupreply as follows:<\/p>\n and confirm the new row is now in the table.<\/p>\n The UPDATE Command to Update Rows \/ Fields in a Table<\/strong><\/p>\n Using UPDATE you can change the contents of existing rows in a table. This command is quite versatile and will become handy in any SQL project you work on. We'll show you a couple of basic examples of how to use the UPDATE command to get you started.<\/p>\n Update a single value from a row<\/strong><\/p>\n The smallest change you can make to a table is to update a single value from a specific row. To change just one value you need a unique identifier for the row you wish to change. Every row in a given table has a unique row id that is recorded in the id column. To make sure you update the right row use the WHERE statement combined with the desired value from the id column as in the example below.<\/p>\n Our example should have changed the value for the groupname \u201ctest 1\u201d to \u201c9\u201d. We can confirm that change using the following SELECT command:<\/p>\n Update multiple values in multiple rows<\/strong><\/p>\n You can also update multiple rows at the same time. We have provided a simple example of such an UPDATE command in which we change the value fields to \u201c0\u201d for all the rows in which the value of the op field is \u201c=\".<\/p>\n Again, we will use the SELECT command to check whether the desired changes occurred. Given that several rows were affected, it will be easier to figure out if the changes have been made if we print the whole table like so.<\/p>\n The results of this latest SELECT should show that the value fields have been updated to \"0\" for all rows where the op field was \"=\".<\/p>\n DELETE rows in a table<\/strong><\/p>\n There are times when you need to delete data from your tables. Such deletions can be done one at a time or in bulk. <\/p>\n Deleting a single row<\/strong><\/p>\n The safest way to delete a specific single row is to specify that row in your DELETE command based on the id column. The following command is an example of this approach:<\/p>\n If you print the full table, using the command below, you should see that the row with id value \u201c6\u201d is no longer present in the table.<\/p>\n Deleting a multiple rows<\/strong><\/p>\n You will often need to remove multiple fields from a table for housekeeping reasons. MySQL tables can become quite large and it's often wise to clean them up for manageability and performance purposes.<\/p>\n Below we'll show you how to delete multiple rows based on a common factor. For this example, we'll delete ALL the rows for which the amount in the value column is below 15. Given the data in our table, that should mean that all rows other than the first row will be deleted when you enter this command:<\/p>\n With the following SELECT command, we can confirm that all rows have been deleted other than the one where the value column indicates a value of 15 or above.<\/p>\n Now you know all the basic commands needed to administer MySQL data. There are myriad variations on the above commands that will afford you greater and greater control over the behavior and content of MySQL databases. The best way to learn these variations and the efficiency that comes of using them is to take what you have learned in this tutorial and play with it. <\/p>\n","protected":false},"excerpt":{"rendered":" MySQL 101 – The basics MySQL is one of the most widely used relational database management systems (RDBMS). MySQL is used to manage databases in a wide variety of applications including the integrated web solution known as LAMP (Linux Apache MySQL Perl\/PHP\/Python). Database management is accomplished in MySQL using Structured Query Language (SQL). Because it<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61],"tags":[],"class_list":["post-3157","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nmysql -u root -p<\/code><\/p>\nPassword:
\n<\/br>
\nWelcome to the MySQL monitor. Commands end with ; or \\g.
\nYour MySQL connection id is 5975
\nServer version: 5.1.73 Source distribution
\n<\/br>
\nCopyright (c) 2000, 2013, Oracle and\/or its affiliates. All rights reserved.
\nOracle is a registered trademark of Oracle Corporation and\/or its
\naffiliates. Other names may be trademarks of their respective
\nowners.
\n<\/br>
\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
\n<\/br>
\nmysql><\/code><\/p>\nmysql> show databases;<\/code>
\n+--------------------+
\n| Database |
\n+--------------------+
\n| information_schema |
\n| mysql |
\n| radius |
\n+--------------------+
\n3 rows in set (0.02 sec)<\/code><\/p>\nmysql> create database mynewdb;<\/code>
\nQuery OK, 1 row affected (0.00 sec)<\/code><\/p>\n[root@server ~]# mysql -u root -p mynewdb < radius.sql<\/code>
\nEnter password:<\/code><\/p>\nmysql> use radius<\/code>
\nReading table information for completion of table and column names
\nYou can turn off this feature to get a quicker startup with -A
\nDatabase changed<\/code><\/p>\nmysql> drop database mynewdb;<\/code>
\nQuery OK, 35 rows affected (0.05 sec)<\/code><\/p>\nmysql> CREATE TABLE `radgroupreply`<\/code> (
\n `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
\n `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
\n `attribute` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
\n `op` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '=',
\n `value` varchar(253) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
\n PRIMARY KEY (`id`),
\n KEY `groupname` (`groupname`(32))
\n) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
\nQuery OK, 0 rows affected (0.06 sec)<\/code><\/p>\nmysql> show tables;<\/code>
\n+------------------------+
\n| Tables_in_radius |
\n+------------------------+
\n| batch_history |
\n| billing_history |
\n| billing_merchant |
\n| billing_paypal |
\n| billing_plans |
\n| billing_plans_profiles |
\n| billing_rates |
\n| cui |
\n| dictionary |
\n| hotspots |
\n| invoice |
\n| invoice_items |
\n| invoice_status |
\n| invoice_type |
\n| nas |
\n| node |
\n| operators |
\n| operators_acl |
\n| operators_acl_files |
\n| payment |
\n| payment_type |
\n| proxys |
\n| radacct |
\n| radcheck |
\n| radgroupcheck |
\n| radgroupreply |
\n| radhuntgroup |
\n| radippool |
\n| radpostauth |
\n| radreply |
\n| radusergroup |
\n| realms |
\n| userbillinfo |
\n| userinfo |
\n| wimax |
\n+------------------------+
\n35 rows in set (0.01 sec)<\/code><\/p>\nmysql> drop table radgroupreply;<\/code>
\nQuery OK, 0 rows affected (0.01 sec)<\/code><\/p>\nmysql> desc wimax;<\/code>
\n+----------+--------------+------+-----+-------------------+-----------------------------+
\n| Field | Type | Null | Key | Default | Extra |
\n+----------+--------------+------+-----+-------------------+-----------------------------+
\n| id | int(11) | NO | PRI | NULL | auto_increment |
\n| username | varchar(64) | NO | MUL | | |
\n| authdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
\n| spi | varchar(16) | NO | MUL | | |
\n| mipkey | varchar(400) | NO | | | |
\n| lifetime | int(12) | YES | | NULL | |
\n+----------+--------------+------+-----+-------------------+-----------------------------+
\n6 rows in set (0.00 sec)<\/code><\/p>\nmysql> SELECT * FROM radgroupreply;<\/code>
\n+----+-----------+-----------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+-----------+-----------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n| 2 | test1 | This is a test | = | 5 |
\n| 3 | test2 | This is a second test | = | 29 |
\n| 4 | test3 | This is a third test | = | 12 |
\n+----+-----------+-----------------------+----+-------+<\/code><\/p>\nmysql> SELECT * FROM radgroupreply WHERE groupname=\"default\";<\/code>
\n+----+-----------+-----------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+-----------+-----------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n+----+-----------+-----------------------+----+-------+
\n1 row in set (0.00 sec)<\/code><\/p>\nmysql> SELECT * FROM radgroupreply WHERE value<=15;<\/code><\/p>\n+----+-----------+-----------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+-----------+-----------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n| 2 | test1 | This is a test | = | 5 |
\n| 4 | test3 | This is a third test | = | 12 |
\n+----+-----------+-----------------------+----+-------+
\n3 rows in set (0.00 sec)<\/code><\/p>\nmysql> SELECT value FROM radgroupreply;<\/code>
\n+-------+
\n| value |
\n+-------+
\n| 15 |
\n| 5 |
\n| 29 |
\n| 12 |
\n+-------+
\n4 rows in set (0.00 sec)<\/code><\/p>\nmysql> SELECT groupname,value FROM radgroupreply;<\/code>
\n+-----------+-------+
\n| groupname | value |
\n+-----------+-------+
\n| default | 15 |
\n| test1 | 5 |
\n| test2 | 29 |
\n| test3 | 12 |
\n+-----------+-------+
\n4 rows in set (0.00 sec)<\/code><\/p>\nmysql> INSERT INTO radgroupreply<\/code> (groupname, attribute, op, value) VALUES ('my group is the best', 'this attribute rocks', '=', '12');
\nQuery OK, 1 row affected (0.00 sec)<\/code><\/p>\nmysql> SELECT * FROM radgroupreply;<\/code>
\n+----+----------------------+-----------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+----------------------+-----------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n| 2 | test1 | This is a test | = | 5 |
\n| 3 | test2 | This is a second test | = | 29 |
\n| 4 | test3 | This is a third test | = | 12 |
\n| 5 | my group is the best | this attribute rocks | = | 12 |
\n+----+----------------------+-----------------------+----+-------+
\n5 rows in set (0.00 sec)<\/code>
\n\u2003
\nSecond insert method<\/strong><\/p>\nmysql> INSERT INTO radgroupreply SET groupname='This other group rocks', attribute='Second type of insert method', op='+', value='11';<\/code>
\nQuery OK, 1 row affected (0.00 sec)<\/code><\/p>\nmysql> select * from radgroupreply;<\/code>
\n+----+------------------------+------------------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+------------------------+------------------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n| 2 | test1 | This is a test | = | 5 |
\n| 3 | test2 | This is a second test | = | 29 |
\n| 4 | test3 | This is a third test | = | 12 |
\n| 5 | my group is the best | this attribute rocks | = | 12 |
\n| 6 | This other group rocks | Second type of insert method | + | 11 |
\n+----+------------------------+------------------------------+----+-------+
\n6 rows in set (0.01 sec)<\/code><\/p>\nmysql> UPDATE radgroupreply SET value='9' WHERE groupname='test1';<\/code>
\nQuery OK, 1 row affected (0.00 sec)
\nRows matched: 1 Changed: 1 Warnings: 0<\/code><\/p>\nmysql> SELECT value FROM radgroupreply WHERE groupname=\"test1\";<\/code>
\n+-------+
\n| value |
\n+-------+
\n| 9 |
\n+-------+
\n1 row in set (0.01 sec)<\/code><\/p>\nmysql> UPDATE radgroupreply SET value=\"0\" WHERE op=\"=\";<\/code>
\nQuery OK, 4 rows affected (0.00 sec)
\nRows matched: 4 Changed: 4 Warnings: 0<\/code><\/p>\nmysql> SELECT * FROM radgroupreply; <\/code>
\n+----+------------------------+------------------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+------------------------+------------------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n| 2 | test1 | This is a test | = | 0 |
\n| 3 | test2 | This is a second test | = | 0 |
\n| 4 | test3 | This is a third test | = | 0 |
\n| 5 | my group is the best | this attribute rocks | = | 0 |
\n| 6 | This other group rocks | Second type of insert method | + | 11 |
\n+----+------------------------+------------------------------+----+-------+
\n6 rows in set (0.00 sec)<\/code><\/p>\nmysql> DELETE FROM radgroupreply WHERE id=\"6\";<\/code>
\nQuery OK, 1 row affected (0.00 sec)<\/code><\/p>\nmysql> SELECT * FROM radgroupreply;<\/code>
\n+----+----------------------+-----------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+----------------------+-----------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n| 2 | test1 | This is a test | = | 0 |
\n| 3 | test2 | This is a second test | = | 0 |
\n| 4 | test3 | This is a third test | = | 0 |
\n| 5 | my group is the best | this attribute rocks | = | 0 |
\n+----+----------------------+-----------------------+----+-------+
\n5 rows in set (0.00 sec)<\/code><\/p>\nmysql> DELETE FROM radgroupreply WHERE value<\"15\";<\/code>
\nQuery OK, 4 rows affected (0.00 sec)<\/code><\/p>\nmysql> SELECT * FROM radgroupreply; <\/code>
\n+----+-----------+-----------------------+----+-------+
\n| id | groupname | attribute | op | value |
\n+----+-----------+-----------------------+----+-------+
\n| 1 | default | Acct-Interim-Interval | := | 15 |
\n+----+-----------+-----------------------+----+-------+
\n1 row in set (0.00 sec)<\/code><\/p>\nConclusion<\/h2>\n