D427 - 3. MySQL Syntax

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/71

encourage image

There's no tags or description

Looks like no tags are added yet.

72 Terms

1
New cards

CREATE DATABASE Syntax

CREATE DATABASE database_name;

2
New cards

DROP DATABASE Syntax

DROP DATABASE database_name;

3
New cards

USE Syntax

USE database_name;

4
New cards

CREATE TABLE Syntax

CREATE TABLE table_name (column1 datatype constraints, column2 datatype constraints, …);

5
New cards

DROP TABLE Syntax

DROP TABLE table_name;

6
New cards

TRUNCATE TABLE Syntax

TRUNCATE TABLE table_name;

7
New cards

ALTER TABLE ADD Syntax

ALTER TABLE table_name ADD column_name datatype constraints;

8
New cards

ALTER TABLE DROP COLUMN Syntax

ALTER TABLE table_name DROP COLUMN column_name;

9
New cards

ALTER TABLE MODIFY Syntax

ALTER TABLE table_name MODIFY COLUMN column_name new_datatype new_constraints;

10
New cards

ALTER TABLE RENAME COLUMN Syntax

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

11
New cards

ALTER TABLE ADD CONSTRAINT Syntax

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

12
New cards

SELECT Basic Syntax

SELECT column1, column2 FROM table_name;

13
New cards

SELECT All Syntax

SELECT * FROM table_name;

14
New cards

SELECT with WHERE Syntax

SELECT column1, column2 FROM table_name WHERE condition;

15
New cards

SELECT with ORDER BY Syntax

SELECT column1 FROM table_name ORDER BY column1 ASC;

16
New cards

SELECT with LIMIT Syntax

SELECT column1 FROM table_name LIMIT n;

17
New cards

SELECT with DISTINCT Syntax

SELECT DISTINCT column1 FROM table_name;

18
New cards

SELECT with Alias Syntax

SELECT column1 AS alias_name FROM table_name AS t;

19
New cards

INSERT INTO Syntax

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

20
New cards

INSERT Multiple Rows Syntax

INSERT INTO table_name (column1, column2) VALUES (val1, val2), (val3, val4);

21
New cards

UPDATE Syntax

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

22
New cards

DELETE Syntax

DELETE FROM table_name WHERE condition;

23
New cards

CREATE INDEX Syntax

CREATE INDEX index_name ON table_name (column_name);

24
New cards

CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX index_name ON table_name (column_name);

25
New cards

DROP INDEX Syntax

DROP INDEX index_name ON table_name;

26
New cards

CREATE VIEW Syntax

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

27
New cards

DROP VIEW Syntax

DROP VIEW view_name;

28
New cards

PRIMARY KEY in CREATE TABLE Syntax

column_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (column_name)

29
New cards

FOREIGN KEY in CREATE TABLE Syntax

FOREIGN KEY (column_name) REFERENCES other_table(other_column)

30
New cards

FOREIGN KEY ON DELETE CASCADE Syntax

FOREIGN KEY (column_name) REFERENCES other_table(other_column) ON DELETE CASCADE

31
New cards

CHECK Constraint Syntax

column_name datatype CHECK (column_name condition)

32
New cards

DEFAULT Constraint Syntax

column_name datatype DEFAULT default_value

33
New cards

INNER JOIN Syntax

SELECT a.col, b.col FROM tableA a INNER JOIN tableB b ON a.id = b.id;

34
New cards

LEFT JOIN Syntax

SELECT a.col, b.col FROM tableA a LEFT JOIN tableB b ON a.id = b.id;

35
New cards

RIGHT JOIN Syntax

SELECT a.col, b.col FROM tableA a RIGHT JOIN tableB b ON a.id = b.id;

36
New cards

FULL OUTER JOIN Simulated Syntax

SELECT * FROM tableA LEFT JOIN tableB ON tableA.id = tableB.id UNION SELECT * FROM tableA RIGHT JOIN tableB ON tableA.id = tableB.id;

37
New cards

CROSS JOIN Syntax

SELECT a.col, b.col FROM tableA a CROSS JOIN tableB b;

38
New cards

Self-Join Syntax

SELECT e1.name AS Employee, e2.name AS Manager FROM Employee e1 INNER JOIN Employee e2 ON e1.manager_id = e2.id;

39
New cards

GROUP BY Syntax

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

40
New cards

HAVING Syntax

SELECT column1, COUNT() FROM table_name GROUP BY column1 HAVING COUNT() > 5;

41
New cards

WHERE with LIKE Syntax

SELECT * FROM table_name WHERE column1 LIKE 'A%';

42
New cards

WHERE with IN Syntax

SELECT * FROM table_name WHERE column1 IN ('val1', 'val2', 'val3');

43
New cards

WHERE with BETWEEN Syntax

SELECT * FROM table_name WHERE column1 BETWEEN 10 AND 50;

44
New cards

WHERE with IS NULL Syntax

SELECT * FROM table_name WHERE column1 IS NULL;

45
New cards

Subquery in WHERE Syntax

SELECT * FROM table_name WHERE column1 = (SELECT MAX(column1) FROM table_name);

46
New cards

Subquery in FROM Syntax

SELECT sub.col FROM (SELECT column1 AS col FROM table_name) AS sub;

47
New cards

UNION Syntax

SELECT column1 FROM tableA UNION SELECT column1 FROM tableB;

48
New cards

UNION ALL Syntax

SELECT column1 FROM tableA UNION ALL SELECT column1 FROM tableB;

49
New cards

COUNT() Syntax

SELECT COUNT(*) FROM table_name;

50
New cards

COUNT(DISTINCT) Syntax

SELECT COUNT(DISTINCT column1) FROM table_name;

51
New cards

SUM() Syntax

SELECT SUM(column1) FROM table_name WHERE condition;

52
New cards

AVG() Syntax

SELECT AVG(column1) FROM table_name;

53
New cards

MIN() / MAX() Syntax

SELECT MIN(column1), MAX(column1) FROM table_name;

54
New cards

ROUND() Syntax

SELECT ROUND(column1, 2) FROM table_name;

55
New cards

CONCAT() Syntax

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM table_name;

56
New cards

CASE Expression Syntax

SELECT column1, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS alias FROM table_name;

57
New cards

IFNULL() Syntax

SELECT IFNULL(column1, 'Unknown') FROM table_name;

58
New cards

AUTO_INCREMENT Syntax

column_name INT NOT NULL AUTO_INCREMENT

59
New cards

GRANT Syntax

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

60
New cards

REVOKE Syntax

REVOKE SELECT ON database_name.table_name FROM 'username'@'host';

61
New cards

CREATE USER Syntax

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

62
New cards

DROP USER Syntax

DROP USER 'username'@'host';

63
New cards

START TRANSACTION Syntax

START TRANSACTION;

64
New cards

COMMIT Syntax

COMMIT;

65
New cards

ROLLBACK Syntax

ROLLBACK;

66
New cards

SAVEPOINT Syntax

SAVEPOINT savepoint_name;

67
New cards

ROLLBACK TO Syntax

ROLLBACK TO savepoint_name;

68
New cards

SHOW DATABASES Syntax

SHOW DATABASES;

69
New cards

SHOW TABLES Syntax

SHOW TABLES;

70
New cards

DESCRIBE Syntax

DESCRIBE table_name;

71
New cards

SHOW CREATE TABLE Syntax

SHOW CREATE TABLE table_name;

72
New cards