MySQL basic queries en commands

Connect to a MySQL instance (commandline)

 Connect to A MySQL instance using named pipe sessies.

>mysql -u root -p

MySQL commands

Some examples to show database objects.

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM naam_van_table;
SHOW STATUS;
SHOW FULL PROCESSLIST;
SHOW PROCEDURE STATUS;

View a stored procedure.

SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES
WHERE SPECIFIC_NAME = '<procedure_name>'
AND ROUTINE_SCHEMA = '<database_name>;

Create, remove and select

CREATE DATABASE naam_van_database;
DROP DATABASE naam_van_database;
USE naam_van_database;

Create and alter tables

CREATE and ALTER table syntax.

CREATE TABLE naam_van_table (column0_id int(10) unsigned NOT NULL primary
key auto_increment,
column1 varchar(75) NOT NULL default '',
column2 varchar(75) NOT NULL default '',
column3 varchar(40) NOT NULL default '',
column4 int(10) unsigned default NULL) engine = InnoDB;
ALTER TABLE naam_van_table ADD COLUMN (column5 bool default 0);
ALTER TABLE naam_van_table ADD COLUMN column6 int unsigned AFTER column2;
ALTER TABLE naam_van_table DROP COLUMN column2;
ALTER TABLE naam_van_table ADD INDEX naam_van_index (naam_van_column);

Queries (Selectie, Insert and Update)

SELECT * FROM naam_van_table WHERE naam_van_column='text';
SELECT column1, column2 FROM naam_van_table WHERE column3='text';
SELECT COUNT(*) FROM naam_van_table;
INSERT INTO naam_van_table VALUES (1, 'Jan', 'Jansen', MD5('zaq123'), 'Docent');
INSERT INTO naam_van_table (voornaam, achternaam, pswd) VALUES
('Jan', 'Jansen', MD5('zaq123')),
('Piet', 'Pieterse', MD5('qaz321'));
UPDATE naam_van_table SET naam_van_column1 = 'X' WHERE naam_van_column2='Y';

Create users and set privileges

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT Select ON databasename.tablename TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'%.domain.tld'; 

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

Set file privileges

Add file_priv to account to make it possible to use a bulk import procedure.

USE MYSQL;
UPDATE user SET file_priv = 'Y' WHERE user='username';
FLUSH PRIVILEGES;

Related Posts