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;