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 Problems
| Name | Problem |
|---|---|
| Use having with group-by | group by class having count(distinct student)... Leetcode: Classes More Than 5 Students |
| Update records with case clause | set sex = case when sex = 'm' then 'f' else 'm' end Leetcode: Swap Salary |
| Sum with case | sum(case when state = 'approved' then 1 else 0 end) Leetcode: Monthly Transactions I |
| Rank score | select @rownr:=@rownr+1 as rank Leetcode: Rank Scores |
| Subquery | where (customer_id, order_date) in (select ... Leetcode: Immediate Food Delivery II |
| Sql isnull and if | if(s1.id%2=0, s1.student, if(isnull(s2.student), s1.student, s2.student)) |
| Use having with group-by | group by class having count(distinct student)>4 Leecode: Classes More Than 5 Students |
| round float | (round(accepts/requests, 2) Leecode: Friend Requests I: Overall Acceptance Rate |
| Select clause add an incremental id | select (@cnt1 := @cnt1 + 1) as id Leetcode: Students Report By Geography |
| Get accumulated sum for each group | Leetcode: Last Person to Fit in the Elevator |
| nth element in each group | Leetcode: Nth Highest Salary |
| topn entris for each group | Leetcode: Department Top Three Salaries |
union vs union all |
Leetcode: Friend Requests II: Who Has the Most Friends |
| If no match show null | Leetcode: Second Highest Salary |
| Get maximum of multiple columns | Leetcode: Triangle Judgement |
| Tree hierachy in SQL | Leetcode: All People Report to the Given Manager |
| Impressive sql problems | Leecode: Find Median Given Frequency of Numbers, Leetcode: Consecutive Available Seats |
| Impressive sql problems | Leetcode: Rank Scores, Leetcode: Exchange Seats, Leetcode: Students Report By Geography |
| mysql doesn’t support full outer join | Leetcode: Monthly Transactions II |
| When left join+groupby, group by which tables’ field? | Leetcode: Team Scores in Football Tournament |
sum(if(isnull( vs ifnull(sum( |
Leetcode: Team Scores in Football Tournament |
| Three tables’ join | Leetcode: Students and Examinations |
| Reference | Leetcode SQL problems |
1.2 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.3 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.4 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.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; |
