1,234
edits
Changes
→Some basic SQL
Though the SQL console looks a little bit like the Linux commandline: they have almost nothing in common. In the SQL console you can write some MariaDB commands, but most of the time you write SQL statements. You can't execute any shell commands. SQL statements (queries) end with a semicolon, to make it clear that you're not trying to run a multi-line query.
* Out of the box your ops345db has four databases, all of which are used internally by MariaDB. They are not used to store data for your applications. Run this:
<source>MariaDB [(none)]> show databases;
+--------------------+
4 rows in set (0.00 sec)
</source>
* To run SQL queries you need to choose the database you're working with. Let's look at the "mysql" database:
<source>use mysql;
MariaDB [(none)]> use mysql;
Database changed</source>
* Now let's see what tables are inside this database:
<source>MariaDB [mysql]> show tables;
+-------------------------------+
| Tables_in_mysql |
+-------------------------------+
| column_stats |
| columns_priv |
| db |
| event |
...
| transaction_registry |
| user |
+-------------------------------+
40 rows in set (0.00 sec)
</source>
An SQL table is made of columns and rows. The columns are part of the definition of the table. The rows are the data that you can insert into the table. You will never look at most of these particular tables, but one of them is interesting.
* Show all the rows (*) in the '''user''' table:
<source>SELECT * FROM user;</source>
* This table has many columns, you can limit which columns you want to see for each row by specifying them explicitly. Choose some columns from the previous output that looks interesting and display just those:
<source>MariaDB [mysql]> SELECT User,Password,password_expired from user;
+-------------+-------------------------------------------+------------------+
| User | Password | password_expired |
+-------------+-------------------------------------------+------------------+
| mariadb.sys | | Y |
| rdsadmin | *0BC5B85E564E990A9F12E40305716A5E3B2D703E | N |
| dbroot | *0463BE0B12D9D44F189C02D447529D16242028CD | N |
+-------------+-------------------------------------------+------------------+
3 rows in set (0.00 sec)
</source>
<source></source>
<source></source>
<source></source>
* use mysql; show tables; select * from user; select user from user;
* mysql root is not the same as the linux root, but it is an administrator and you should only use it for creating users, databases, and assigning permissions.