Sql

From MusiKiwi

Jump to: navigation, search

Contents

Create Tables

  CREATE TABLE R(
     A CHAR(100),
     B INT,
     C CHAR(1) DEFAULT '?'
  );

Delete Tables

  DROP TABLE R;

Delete Relation/Tuple

  DELETE FROM R WHERE <condition>;

Change Tables

  ALTER TABLE R ADD C CHAR(16);
  ALTER TABLE R DROP C;
  ALTER TABLE R ADD C CHAR(1) DEFAULT '?';

Insert / Add

  INSERT INTO R(A1, ..., An) VALUES(V1, ..., Vn);

Conditionals

CASE

This will print all the tuples from table R, followed by either "Print This" or "Print That", depending on if attribute A1 matches value1 or value2.

  SELECT *, CASE WHEN A1=<value1> THEN 'Print This' WHEN A1=<value2> THEN 'Print That' END FROM R;

The order could be switched by executing:

  SELECT CASE WHEN A1=<value1> THEN 'Print This' WHEN A1=<value2> THEN 'Print That' END, * FROM R;

WHERE

This selects all tuples from the relation R where A1 is value1, and A2 is less than three months ago.

  SELECT * FROM R WHERE A1=<value1> AND A2 < DATE('NOW', '-3 MONTH');

Administration

Show Users

  SELECT user,host,password FROM mysql.user;

Grant permissions

  GRANT CREATE, SELECT, INSERT, UPDATE, DELETE, DROP, ALTER ON database.* TO user@hostname;
  FLUSH PRIVILEGES;

Passwords

  SET PASSWORD FOR user@localhost = PASSWORD('password');
Personal tools