Please enable JavaScript.
Coggle requires JavaScript to display documents.
Mysql (SQL (Basic Queries (SELECT * FROM tableName; (SELECT * FROM…
Mysql
SQL
Structured Query Language
Basic Queries
SELECT * FROM tableName;
SELECT * FROM tableName WHERE id IN ('sample', 'sample');
DELETE FROM tableName WHERE <Condition>;
UPDATE tableName SET <column>=<value> WHERE <Condition>;
INSERT INTO tableName (<Columns>, <Columns>, ..);
or we can specify the fields
INSERT INTO tableName(<ColumnName>, <ColumnName>...) VALUES (<Value>, <Value>../);
Create a Table
CREATE TABLE tableName(<ColumnName> <Datatype>, <ColumnName> <Datatype>, PRIMARY KEY(<ColumnName));
CREATE DATABASE databaseName;
UNIONS
Select from tables
Must have the same columns when selecting
should be the same datatype
SAMPLE
SELECT <ColumnName> FROM table1 UNION SELECT <ColumnName> FROM table2;
Aliases
use of the keyword
AS
Example
SELECT CONCAT(lastName,', ',firstName)
AS
fullName FROM tableName;
ALTERING A TABLE
add new column
ALTER TABLE tableName ADD columnName DATATYPE CONSTRAINTS;
drop a column
ALTER TABLE tableName DROP COLUMN columnName;
DROP tableName
Delete a table
Constraints
eg: NOT NULL, AUTO_INCREMENT etc.
FOREIGN KEYS
Link between the tables.
Example
SELECT t1.id, t1.name, t2.name FROM table1 AS t1JOIN table2 AS t2 ON t2.id = t1.id;
SELECT t1.id, CONCAT(t1.lastName,', ', t1.firstName) AS name, CONCAT(t2.lastName,', ',t2.firstname) AS adviser FROM students AS t1 JOIN advisers AS t2 ON t2.adviser_id = t1.adviserId
Adding new column with foreign keys
note: you should have an empty field for it.
ALTER TABLE tableName ADD FOREIGN KEY(fieldName) REFERENCES sourceTable(fieldName) ON DELETE SET NULL;
NESTED SQL
SELECT * FROM tbl1 WHERE itemPrice > (SELECT AVG(price) FROM tbl2);
LIKE
_ - represents a single character
% - represents multiple characters
EXAMPLE
SELECT * FROM tbl1 WHERE date LIKE '
_-12-
';
SELECT * FROM tbl1 WHERE name LIKE '%sam%';
Used for specifying a pattern
AES_ENCRYPT
inserting a record with aes_encrypt
INSERT INTO tblname VALUES AES_ENCRYPT(value, key); note: use VARBINARY as a datatype
AES_DECRYPT
view the encrypted text from the db
SELECT CAST(AES_DECRYPT(column, key) AS CHAR) AS result FROM tablename note: We use CAST to view our decrypted text as a CHAR by using CHAR