CheatSheet: SQL & MySql


- PDF Link: cheatsheet-mysql-A4.pdf, Category: interview
- Blog URL: https://cheatsheet.dennyzhang.com/cheatsheet-mysql-A4
- Related posts: CheatSheet: System Design For Job Interview, interview
1.1 SQL Problems
Name | Problem |
---|---|
Reference | Leetcode SQL problems |
Use having with group-by | group by class having count(distinct student)>4 Leecode: 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 |
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)) |
round float | (round(accepts/requests, 2) Leecode: Friend Requests I: Overall Acceptance Rate |
Rank score | (select @rank := 0, @prev := -1) as rows LeetCode: Rank Scores |
Select clause add an incremental id | select (@cnt1 := @cnt1 + 1) as id LeetCode: Students Report By Geography |
MySQL DATESUB | DATE_SUB(record_date, INTERVAL 6 DAY) |
Get accumulated sum for each group | LeetCode: Last Person to Fit in the Elevator |
nth element in each group | LeetCode: Nth Highest Salary, LeetCode: Get the Second Most Recent Activity |
topn entris for each group | LeetCode: Department Top Three Salaries |
union vs union all |
LeetCode: Friend Requests II: Who Has the Most Friends |
Convert float to int | cast(amount as SIGNED) |
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 |
Mysql distinct in having clause | LeetCode: Active Users |
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’ inner join | LeetCode: Students and Examinations |
Find Continuous Ranges | Find the Start and End Number of Continuous Ranges |
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 |
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; |