CheatSheet: SQL & MySql
- PDF Link: cheatsheet-mysql-A4.pdf, Category: languages
- Blog URL: https://cheatsheet.dennyzhang.com/cheatsheet-mysql-A4
- Related posts: CheatSheet: shell, #denny-cheatsheets
1.1 SQL Statement
| Name | Problem |
|---|---|
| SQL problems | #sql |
| topn entris for each group | Leetcode: Department Top Three Salaries |
| Rank score | Leetcode: Rank Scores |
1.2 Mysql Packages
| Name | Command |
|---|---|
| Install mysql server | apt-get install mysql-server |
| Install mysql client | apt-get install mysql-client libmysqlclient-dev, yum install mysql-devel |
| Install python mysql library | easy_install mysql-python |
1.3 DB Management
| Name | Command |
|---|---|
| mysql connect | mysql -u$username -p$password -P$port -h$dbhost $dbname |
| database encoding | set names utf8; |
| List databases | show databases; |
| List tables for current db | show tables; |
| Check table definition | describe $tablename; |
| Run sql in non-interactive way | mysql -uUSER -pPASSWORD databasename -e "select * from t limit 10" |
| Import db | mysql -uUSER -pPASSWORD dbname < backup.sql |
| export db | mysqldump -uUSER -pPASSWORD DATABASE > backup.sql |
| export db without schema | mysqldump -uUSER -pPASSWORD DATABASE --no-data=true --add-drop-table=false > backup.sql |
| Grant access | GRANT SUPER ON `DBNAME`.`user` TO 'DBUSER'@'%' |
| Add column | ALTER TABLE expenses ADD COLUMN createtime timestamp not null default now(); |
| Delete Column | ALTER TABLE expenses DROP COLUMN createtime; |
| Delete index | DROP INDEX indexname ON table_name; |
| Create index | create index idindex on table_name(col_name) using btree; |
| Reset password | UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’; FLUSH PRIVILEGES; |
| ERROR 1396 (HY000): Operation CREATE USER… | drop user 'braindenny'@'%'; flush privileges; CREATE USER... |
| mysql8 create user with password | CREATE USER 'myuser'@'%' IDENTIFIED BY 'MYPASSWORD'; |
| mysql8 grant privileges to user | GRANT ALL PRIVILEGES ON mydbname.* TO 'myuser'@'%' WITH GRANT OPTION; |
1.4 SQL Statement
| Name | Command |
|---|---|
| Select with regexp in where clause | select * from expenses where date regexp '2013-0[4-5]' limit 2; |
| Select with like in where clause | select * from expenses where date like '2013-0%' limit 2; |
| Select unix timestamp | select unix_timestamp(createtime) from expenses limit 1; |
| Offset limit | select * from student limit 4 offset 9 |
| Use replace function | UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def'); |
| Use if function | select Db, IF(IFNULL(User, “”)=””, DB, User) from db; |
1.5 PSQL/Postgres
| Name | Command |
|---|---|
| Install postgres in Ubuntu | apt-get install postgresql postgresql-client postgresql-contrib |
| Install postgres in Mac OS | brew install postgres |
| Check postgres status | service postgresql status |
| Check version | psql --version |
| Connect postgresql | psql -U postgres |
| Create user | CREATE USER tom WITH PASSWORD ''; |
| Grant access | GRANT ALL PRIVILEGES ON DATABASE launchpad_empty to tom; |
| Postgres configuration | /var/lib/pgsql/data/pg_hba.conf, /etc/postgresql/*/main/postgresql.conf |
| Promote to super admin | ALTER USER myuser WITH SUPERUSER; |
| List datbases | \l |
| List all tables in a database | \d |
| Change database | \connect launchpad_dev; |
| Show tables | \ds |
| Copy databse | createdb -O root -T launchpad_dev launchpad_ustack_cn |
| Drop database | drop database launchpad_ustack_cn; |
| Connect db | psql -h myhost -d mydb -U myuser -W |
| Create user | CREATE USER tom WITH PASSWORD 'myPassword'; |
| Create db | CREATE DATABASE jerry; |
