Mysql commands: Difference between revisions

From KlavoWiki
Jump to navigationJump to search
 
(15 intermediate revisions by the same user not shown)
Line 3: Line 3:
= From your login shell =
= From your login shell =


Creating a Database<br>
== Securing MySQL ==
<pre>
mysql_secure_installation
</pre>
 
== Creating a Database ==
<pre>
<pre>
mysqladmin create mydatabase
mysqladmin create mydatabase
</pre>
</pre>


Dropping (Removing) a Database<br>
== Dropping (Removing) a Database ==
<pre>
<pre>
mysqladmin drop mydatabase
mysqladmin drop mydatabase
</pre>
</pre>


Populating an Existing Database from a *.sql File <br>
== Import Database ==
Populating an Existing Database from a *.sql File
<pre>
<pre>
mysql databasename < filename.sql
mysql databasename < filename.sql
</pre>
</pre>


Dumping Database Structure and Data to a *.sql file<br>
== Export Database ==
=== To a Local file ===
Dumping Database Structure and Data to a *.sql file
<pre>
mysqldump databasename > filename.sql
mysqldump --all-databases  > full-backup-$(date +%Y%m%d-%H%M).sql
</pre>
 
<pre>mysqldump databasename table1 > table1.sql</pre>
 
=== To a Remote Server ===
<pre>
mysqldump <-uUserName> <-pMyPassword> [DatabaseName] | ssh root@myremoteserver.mydomain.com "cat > /tmp/DatabaseName.sql"
</pre>
 
== User Management ==
=== List Users ===
<pre>
mysql -B -N -pMyPassword -e "SELECT user, host FROM user" mysql
</pre>
 
=== List User Permissions ===
<pre>
<pre>
mysqldump databsename > filename.sql;
mysql -B -N -pMyPassword -e "SHOW GRANTS FOR 'MyUserName'"
</pre>
</pre>


= From within the MySQL interface =
= From within the MySQL interface =


Starting MySQL from the Command Line<br>
== MySQL CLI ==
Starting MySQL from the Command Line
<pre>
<pre>
mysql
mysql -uusername -pMyPassword
</pre>
</pre>


Line 40: Line 68:
</pre>
</pre>


Seeing What Databases are Available<br>
== Show Databases ==
Seeing What Databases are Available
<pre>
<pre>
show databases;
show databases;
Line 46: Line 75:
(be sure to use the semi-colon to terminate the command)
(be sure to use the semi-colon to terminate the command)
<br>
<br>
== Select Database ==
Telling MySQL to Use a Specific Database<br>
Telling MySQL to Use a Specific Database<br>
<pre>use mydatabase;</pre>
<pre>use mydatabase;</pre>


== Show Tables ==
Seeing What Tables are Available Within a Database<br>
Seeing What Tables are Available Within a Database<br>
<pre>
<pre>
Line 54: Line 87:
</pre>
</pre>


== List Data from a Table ==
Looking at the Data in a Particular Table<br>
Looking at the Data in a Particular Table<br>
<pre>
<pre>
select * from [tablename];
select * from tablename;
</pre>
</pre>


Select the last 10 entries by column date
<pre>
SELECT * FROM (
    SELECT * FROM tablename ORDER BY date DESC LIMIT 10
) sub
ORDER BY date ASC;
</pre>
List data between a date range
<pre>
select * from 3kw where date >= '2015-08-05 17:20:00' and curdate();
</pre>
or
<pre>
select * from 3kw where date >= '2015-08-05 10:30:00' and date <= '2015-08-05 11:00:00';
select date, PAC, ETODAY from 3kw where date >= '2015-08-05 10:30:00' and date <= '2015-08-05 11:00:00';
</pre>
== Indexes ==
=== Show index ===
<pre>
show index from tablename;
</pre>
=== Create index ===
<pre>
create index indexname on tablename(columnname);
</pre>
=== Delete index ===
<pre>
drop index indexname from tablename;
</pre>
== Export Data to CSV ==
<pre>
SELECT * FROM databasename WHERE calldate >= DATE(20141028) AND calldate <= DATE(20141105) into outfile 'calls.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
</pre>
Note: outfile must be before the FIELDS, ENCLOSED and LINES parameters.
== Rename Table ==
Rename an existing Table
Rename an existing Table
<pre>
<pre>
Line 64: Line 143:
</pre>
</pre>


Adding a Database User with Password<br>
== Delete Table ==
<pre>
use databasename;
drop tablename;
</pre>
 
== User Management ==
 
=== Privileges ===
List of privileges : https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
 
=== Add User ===
Adding a Database User with Password
<pre>
grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]';
grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]';
<pre>grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';
grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';
 
grant select on mydatabase.tablename to 'username'@192.168.1.3 identified by 'supersecretpasswd';
grant select on mydatabase.* to 'username'@'%' identified by 'supersecretpasswd';
grant create on mydatabase.* to 'username'@'%' identified by 'supersecretpasswd';
 
flush privileges;
flush privileges;
</pre>
</pre>


=== Delete User ===
Removing a Database User<br>
Removing a Database User<br>
<pre>
<pre>
Line 76: Line 174:
</pre>
</pre>


== Data Manipulation ==
=== Add Data ===
Add data to a table<br>
Add data to a table<br>
<pre>
<pre>
Line 83: Line 184:
</pre>
</pre>


Delete data
 
=== Delete Data ===
<pre>
<pre>
delete from cid where name like = '0%';
delete from cid where name like '0%';
</pre>
</pre>


=== Search & Replace ===
Performing Search-and-Replace Actions on a Table<br>
Performing Search-and-Replace Actions on a Table<br>
<pre>
update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");
update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");
<pre>update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");</pre>
update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");
</pre>


[[Category : Linux]]
[[Category : Linux]]

Latest revision as of 21:10, 7 May 2024

Handy Cheat-Sheet of MySQL Commands

From your login shell

Securing MySQL

mysql_secure_installation

Creating a Database

mysqladmin create mydatabase

Dropping (Removing) a Database

mysqladmin drop mydatabase

Import Database

Populating an Existing Database from a *.sql File

mysql databasename < filename.sql

Export Database

To a Local file

Dumping Database Structure and Data to a *.sql file

mysqldump databasename > filename.sql
mysqldump --all-databases  > full-backup-$(date +%Y%m%d-%H%M).sql
mysqldump databasename table1 > table1.sql

To a Remote Server

mysqldump <-uUserName> <-pMyPassword> [DatabaseName] | ssh root@myremoteserver.mydomain.com "cat > /tmp/DatabaseName.sql"

User Management

List Users

mysql -B -N -pMyPassword -e "SELECT user, host FROM user" mysql

List User Permissions

mysql -B -N -pMyPassword -e "SHOW GRANTS FOR 'MyUserName'"

From within the MySQL interface

MySQL CLI

Starting MySQL from the Command Line

mysql -uusername -pMyPassword

You will be welcomed with the following message:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is ## to server version: #.##.##

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

The prompt changes to "mysql>", which will be shown in each example below.

Show Databases

Seeing What Databases are Available

show databases;

(be sure to use the semi-colon to terminate the command)

Select Database

Telling MySQL to Use a Specific Database

use mydatabase;


Show Tables

Seeing What Tables are Available Within a Database

show tables;

List Data from a Table

Looking at the Data in a Particular Table

select * from tablename;


Select the last 10 entries by column date

SELECT * FROM (
    SELECT * FROM tablename ORDER BY date DESC LIMIT 10
) sub
ORDER BY date ASC;

List data between a date range

select * from 3kw where date >= '2015-08-05 17:20:00' and curdate();

or

select * from 3kw where date >= '2015-08-05 10:30:00' and date <= '2015-08-05 11:00:00';
select date, PAC, ETODAY from 3kw where date >= '2015-08-05 10:30:00' and date <= '2015-08-05 11:00:00';

Indexes

Show index

show index from tablename;

Create index

create index indexname on tablename(columnname);

Delete index

drop index indexname from tablename;

Export Data to CSV

SELECT * FROM databasename WHERE calldate >= DATE(20141028) AND calldate <= DATE(20141105) into outfile 'calls.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Note: outfile must be before the FIELDS, ENCLOSED and LINES parameters.

Rename Table

Rename an existing Table

RENAME TABLE tbl_name TO new_tbl_name

Delete Table

use databasename;
drop tablename;

User Management

Privileges

List of privileges : https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

Add User

Adding a Database User with Password

grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]';
grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';

grant select on mydatabase.tablename to 'username'@192.168.1.3 identified by 'supersecretpasswd';
grant select on mydatabase.* to 'username'@'%' identified by 'supersecretpasswd';
grant create on mydatabase.* to 'username'@'%' identified by 'supersecretpasswd';

flush privileges;

Delete User

Removing a Database User

delete from mysql.user where user='techgeek' and host='localhost';
flush privileges;

Data Manipulation

Add Data

Add data to a table

insert into cid  (name, number) values 
('My Name', '0731234321'),
('Mr Name', '0412344321');


Delete Data

delete from cid where name like '0%';

Search & Replace

Performing Search-and-Replace Actions on a Table

update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");
update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");