Using CheatSheets To Apply Best Practices

CheatSheet: SQL & MySql

CheatSheet: SQL & MySql

1.1 SQL Problems

Name Problem
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
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
Rank score (select @rank := 0, @prev := -1) as rows Leetcode: Rank Scores
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
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
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 *