forked from cheat/cheatsheets
-
Notifications
You must be signed in to change notification settings - Fork 0
/
mysql
37 lines (26 loc) · 1.04 KB
/
mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# To connect to a database:
mysql -h <host> -u <username> -p
# To backup all databases:
mysqldump --all-databases --all-routines -u <username> -p > ~/dump.sql
# To restore all databases:
mysql -u <username> -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;