Gallery

Handbok Databaskommunikation

Huvudmoment

Databas

Explore

Huvudmoment

Lektionsfiler

⁠

12 Databaskommunikation.pdf

30.6 kB

⁠

SQL Dag 1 (Intro med CRUD)

⁠

SQL.sql

1.3 kB

⁠

Exempel-SQL-databas:

⁠

https://dev.mysql.com/doc/index-other.html⁠

⁠

SQL Dag 2 ( Joins, Datum, Matte, Where, limit, Sort)

⁠

Joins_&_Where.sql

950 B

⁠

INSERT INTO course (name) VALUES

("marketing 1"), ("marketing 2"), ("Lab 1"), ("lab 2"),

("physics 1"), ("physics 2");

INSERT INTO students (age, name,stad) VALUES

(19," Stefan", "Skövde"), (46, "Annelie","Stöckholm"), (33, "Eva","Stad 2"), (43, "Erik","Stad 33"),

(26,"Alexander","Stad 3"), (28,"Alex","Stad 4");

INSERT INTO student_courses (student, course) VALUES

(1, 1),(1, 2),(1, 5),(1, 4),(2, 1),(2, 5),(2, 6),(3,5),(6,1),(6,2);

/* Join med "INNER JOIN" */

SELECT students.name,students.age,student_courses.course, course.name

FROM students INNER JOIN student_courses

ON students.idstudents = student_courses.student

INNER JOIN course

ON course.idcourse = student_courses.course;

/* Join med "WHERE" */

SELECT students.name,students.age,student_courses.course, course.name

FROM students, course, student_courses

WHERE students.idstudents = student_courses.student

AND course.idcourse = student_courses.course;

/* Nested Query kurs-ID */

SELECT students.name, idstudents

FROM students WHERE students.idstudents IN (

SELECT student FROM student_courses WHERE course = 2

);

/* Nested Query STAD */

SELECT students.name, idstudents

FROM students WHERE students.stad IN (

"Skövde","Stöckholm"

);

/* Ranges: */

SELECT 2 + 6 WHERE 7 BETWEEN 6 AND 9;

/* Datum */

SELECT CURDATE();

/* Datum + TID = Datetime */

SELECT NOW();

SELECT product_name from Products WHERE sales_time

BETWEEN NOW() - interval 1 month AND NOW()

AND SUM((SELECT price FROM transactions WHERE city = "Göteborg")) > 10000

AND city = "Göteborg"

;

/* Variabler "SET" */

SET @dagens_datum = CURDATE();

SELECT @dagens_datum;

/* As */

SELECT 29 + 3 AS "Total";

SELECT s.name, sc.course, c.name

FROM students s, course c, student_courses sc;

/* Plocka ut studenter från signup-time */

SELECT s.name,s.age, sc.course, c.name, s.points,sc.signup_time AS "Register time"

FROM students s, course c, student_courses sc

WHERE s.idstudents = sc.student

AND c.idcourse = sc.course

AND sc.signup_time BETWEEN NOW() - interval 4 week AND NOW()

ORDER BY s.points DESC LIMIT 3;

Skapa Dabatasen som vi satte upp (Tables):

CREATE TABLE `course` (

`idcourse` int NOT NULL AUTO_INCREMENT,

`name` varchar(45) NOT NULL,

PRIMARY KEY (`idcourse`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `student_courses` (

`student` int NOT NULL,

`course` int NOT NULL,

`register_id` int NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`register_id`),

KEY `student_link_idx` (`student`),

KEY `course_link_idx` (`course`),

CONSTRAINT `course_link` FOREIGN KEY (`course`) REFERENCES `course` (`idcourse`) ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT `student_link` FOREIGN KEY (`student`) REFERENCES `students` (`idstudents`) ON DELETE RESTRICT

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `students` (

`idstudents` int NOT NULL AUTO_INCREMENT,

`age` int DEFAULT NULL,

`name` varchar(45) NOT NULL,

`stad` varchar(45) NOT NULL,

PRIMARY KEY (`idstudents`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.