38 lines
1.0 KiB
Plaintext
38 lines
1.0 KiB
Plaintext
# To connect to a database
|
|
mysql -h localhost -u root -p
|
|
|
|
# To backup all databases
|
|
mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql
|
|
|
|
# To restore all databases
|
|
mysql -u root -p < ~/fulldump.sql
|
|
|
|
# To create a database in utf8 charset
|
|
CREATE DATABASE owa CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
# To add a user and give rights on the given database
|
|
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;
|
|
|
|
# To list the privileges granted to the account that you are using to connect to the server. Any of the 3 statements will work.
|
|
SHOW GRANTS FOR CURRENT_USER();
|
|
SHOW GRANTS;
|
|
SHOW GRANTS FOR CURRENT_USER;
|
|
|
|
# Basic SELECT Statement
|
|
SELECT * FROM tbl_name;
|
|
|
|
# Basic INSERT Statement
|
|
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
|
|
|
|
# Basic UPDATE Statement
|
|
UPDATE tbl_name SET col1 = "example";
|
|
|
|
# Basic DELETE Statement
|
|
DELETE FROM tbl_name WHERE user = 'jcole';
|
|
|
|
# To check stored procedure
|
|
SHOW PROCEDURE STATUS;
|
|
|
|
# To check stored function
|
|
SHOW FUNCTION STATUS;
|