common-usage

SQL Common Usage (MySQL)

Jupyter Notebook With Output

-- show databases
SHOW DATABASES;
-- show users and show privileges
SELECT *
FROM mysql.user;
 
-- create and drop schema/databse
CREATE SCHEMA testschema;
DROP SCHEMA IF EXISTS testschema;
 
CREATE DATABASE testdb;
CREATE DATABASE IF NOT EXISTS testdb;
DROP DATABASE IF EXISTS test;
 
-- create privileged user
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR admin@localhost;
-- remove all privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user@localhost;
SHOW GRANTS FOR admin@localhost;
 
-- drop user
DROP USER 'admin'@'localhost';
 
-- grant specific privileges
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
SHOW GRANTS FOR user@localhost;
GRANT ALL ON world.* TO 'user'@'localhost';
SHOW GRANTS FOR user@localhost;
 
USE world;
 
SHOW TABLES;
 
SHOW TABLE STATUS LIKE 'city';
SHOW ENGINES;
ALTER TABLE city
    ENGINE = 'myisam';
 
DESC city;
DESCRIBE country;
DESCRIBE countrylanguage;
SHOW CREATE TABLE city;
 
-- crud
-- read
SELECT *
FROM city
LIMIT 5;
SELECT *
FROM country
LIMIT 5;
SELECT *
FROM countrylanguage
LIMIT 5;
 
-- country who has the largest percentage of population speaking english
SELECT c.name
FROM country AS c
         INNER JOIN
     (SELECT countrycode
      FROM countrylanguage
      WHERE percentage = (SELECT max(percentage) AS percentage FROM countrylanguage WHERE language = 'english')
        AND language = 'english') AS cl
     ON c.code = cl.countrycode
;
 
-- percentage of chinese speaking population in the world population
SELECT concat(round((SELECT sum(population.population)
                     FROM (SELECT pbp.population * pbp.percentage / 100 AS population
                           FROM (SELECT c.population, cl.percentage
                                 FROM country AS c
                                          INNER JOIN (SELECT * FROM countrylanguage WHERE language = 'chinese') AS cl
                                                     ON c.code = cl.countrycode) AS pbp) AS population) /
                    (SELECT sum(population) FROM country) * 100, 2), '%') AS ratio;
 
 
-- create
INSERT INTO city (name, countrycode, district, population)
VALUES ('shenzhen', 'chn', 'unknown', 10000000);
 
SELECT *
FROM city
ORDER BY id DESC
LIMIT 1;
 
CREATE TABLE person
(
    id                int NOT NULL AUTO_INCREMENT,
    firstname         varchar(30),
    lastname          varchar(30),
    home_country_code char(3),
    CONSTRAINT pk_id PRIMARY KEY (id)
) ENGINE = innodb;
 
CREATE TABLE student
(
    id       int NOT NULL AUTO_INCREMENT,
    personid int NOT NULL,
    CONSTRAINT pk_id PRIMARY KEY (id),
    CONSTRAINT fk_person FOREIGN KEY (personid) REFERENCES person (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE = innodb;
 
DESC student;
 
ALTER TABLE student
    ADD COLUMN school varchar(50),
    ADD COLUMN grade  int UNSIGNED,
    ADD COLUMN class  int UNSIGNED;
 
DESC student;
 
INSERT INTO student (personid, school) -- should fail because of foreign key constraint, the personid dne in person table
VALUES (10, 'bcs');
INSERT INTO person (firstname, lastname)
VALUES ('first', 'last');
INSERT INTO person (id, firstname, lastname)
VALUES (100, 'one', 'two');
 
SELECT *
FROM person;
INSERT INTO student (personid, school)
VALUES ((SELECT id FROM person LIMIT 1), 'bcs');
INSERT INTO student (personid, school)
VALUES (100, 'uoft');
SELECT *
FROM student;
 
-- update
UPDATE person
SET id=99,
    firstname='three',
    lastname='four'
WHERE id = 100;
 
SELECT *
FROM person;
 
SELECT *
FROM student;
-- check cascading update student's personid
 
-- delete
DELETE
FROM person
WHERE id = 99;
 
SELECT *
FROM student; -- check cascading delete student with person id=100
 
DELETE
FROM person;
 
DELETE
FROM student;
 
DROP TABLE IF EXISTS student;
 
DROP TABLE IF EXISTS person;