Using CheatSheets To Apply Best Practices

CheatSheet: SQL & MySql

CheatSheet: SQL & MySql

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.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
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
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;

1.6 More Resources

License: Code is licenmysql under MIT License.

Leave a Reply

Your email address will not be published. Required fields are marked *