Skip to content
Branch: master
Find file History
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
README.org
cheatsheet-mysql-A4.pdf

README.org

1 CheatSheet: SQL & MySql

linkedin
github
slack


PRs Welcome

File me Issues or star this repo.

1.1 SQL Problems

NameProblem
Use having with group-bygroup by class having count(distinct student)... Leetcode: Classes More Than 5 Students
Update records with case clauseset sex = case when sex = 'm' then 'f' else 'm' end Leetcode: Swap Salary
Get maximum of multiple columnsLeetcode: Triangle Judgement
Rank scoreLeetcode: Rank Scores
topn entris for each groupLeetcode: Department Top Three Salaries
subqueryLeetcode: Immediate Food Delivery II
SQL problems#sql

1.2 SQL Statement

NameCommand
Select with regexp in where clauseselect * from expenses where date regexp '2013-0[4-5]' limit 2;
Select with like in where clauseselect * from expenses where date like '2013-0%' limit 2;
Select unix timestampselect unix_timestamp(createtime) from expenses limit 1;
Offset limitselect * from student limit 4 offset 9
Use replace functionUPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def');
Use if functionselect Db, IF(IFNULL(User, “”)=”“, DB, User) from db;

1.3 Mysql Packages

NameCommand
Install mysql serverapt-get install mysql-server
Install mysql clientapt-get install mysql-client libmysqlclient-dev, yum install mysql-devel
Install python mysql libraryeasy_install mysql-python

1.4 DB Management

NameCommand
mysql connectmysql -u$username -p$password -P$port -h$dbhost $dbname
database encodingset names utf8;
List databasesshow databases;
List tables for current dbshow tables;
Check table definitiondescribe $tablename;
Run sql in non-interactive way=mysql -uUSER -pPASSWORD databasename -e “select * from t limit 10”=
Import dbmysql -uUSER -pPASSWORD dbname < backup.sql
export dbmysqldump -uUSER -pPASSWORD DATABASE > backup.sql
export db without schemamysqldump -uUSER -pPASSWORD DATABASE --no-data=true --add-drop-table=false > backup.sql
Grant access=GRANT SUPER ON `DBNAME`.`user` TO ‘DBUSER’@’%’=
Add columnALTER TABLE expenses ADD COLUMN createtime timestamp not null default now();
Delete ColumnALTER TABLE expenses DROP COLUMN createtime;
Delete indexDROP INDEX indexname ON table_name;
Create indexcreate index idindex on table_name(col_name) using btree;
Reset passwordUPDATE 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 passwordCREATE USER 'myuser'@'%' IDENTIFIED BY 'MYPASSWORD';
mysql8 grant privileges to userGRANT ALL PRIVILEGES ON mydbname.* TO 'myuser'@'%' WITH GRANT OPTION;

1.5 PSQL/Postgres

NameCommand
Install postgres in Ubuntuapt-get install postgresql postgresql-client postgresql-contrib
Install postgres in Mac OSbrew install postgres
Check postgres statusservice postgresql status
Check versionpsql --version
Connect postgresqlpsql -U postgres
Create userCREATE USER tom WITH PASSWORD '';
Grant accessGRANT 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 adminALTER USER myuser WITH SUPERUSER;
List datbases\l
List all tables in a database\d
Change database\connect launchpad_dev;
Show tables\ds
Copy databsecreatedb -O root -T launchpad_dev launchpad_ustack_cn
Drop databasedrop database launchpad_ustack_cn;
Connect dbpsql -h myhost -d mydb -U myuser -W
Create userCREATE USER tom WITH PASSWORD 'myPassword';
Create dbCREATE DATABASE jerry;

1.6 More Resources

License: Code is licenmysql under MIT License.

You can’t perform that action at this time.