Skip to content
STI Handbok Databaskommunikation
Huvudmoment
Databas
Protokoll
API
Säkerhet
MySQL Workbench
Resurser
More
Share
Explore
Huvudmoment

icon picker
Lektionsfiler

⁠
12 Databaskommunikation.pdf
30.6 kB
⁠
⁠

SQL-Exempel 1 (Intro med CRUD)

⁠
SQL.sql
1.3 kB
⁠
⁠
Exempel-SQL-databas:
⁠
https://dev.mysql.com/doc/index-other.html
⁠
⁠

SQL-Exempel mer( 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;



MongoDB: ​

Få ut alla:
db.min_collection.find()

Få ut på filtrering på värden:
db.min_collection.find({"color":"red"})

db.min_collection.insertOne({"my_field" : "Ett värde"})

db.min_collection.insertMany([
{color:"blue", product_name:"Annan bil" },
{color:"purple",product_name: "Tredje bil"}
])


db.min_collection.updateMany({ color: "blue" }, { $set: { product_name: "coola bilen" } })

db.min_collection.updateOne({color :"blue"} , {$set:{ product_name:"coola bilen"}})


db.min_collection.removeOne({_id: ObjectId('637b48b0047e550a648207d6')})

db.min_collection.removeMany({ color: “red”})
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.