Creating a database in MySQL with tables and Foreign keys (SQL)

PHOTO

Sun May 01 2022 19:23:49 GMT+0000 (Coordinated Universal Time)

Saved by @soyreynald #mysql #sql #scripting

/* AQUI CREARE LA TABLA */
CREATE DATABASE fejesus;

CREATE TABLE leccion (
    id_leccion INT NOT NULL AUTO_INCREMENT, 
    titulo VARCHAR(30),
    instructor VARCHAR(50),
    no_leccion INT,

    PRIMARY KEY(id_leccion),
    INDEX(no_leccion)
    
) ENGINE=INNODB;

DESCRIBE leccion;

CREATE TABLE preguntas (
    id_pregunta INT NOT NULL AUTO_INCREMENT,
    pregunta VARCHAR(200),
    verso VARCHAR(40),
    id_leccion INT NOT NULL,
    id_seccion INT NOT NULL,

    PRIMARY KEY (id_pregunta),
    INDEX(id_leccion),
    INDEX(id_seccion),

    FOREIGN KEY (id_leccion)
        REFERENCES leccion(id_leccion)
        ON UPDATE CASCADE ON DELETE RESTRICT
    
) ENGINE=INNODB;

DESCRIBE preguntas;

CREATE TABLE seccion ( 
    id_seccion INT NOT NULL AUTO_INCREMENT, 
    leccion_id INT NOT NULL, 
    titulo VARCHAR(100), 

    PRIMARY KEY(id_seccion), 
    INDEX (leccion_id), 
        
    FOREIGN KEY (leccion_id) 
        REFERENCES leccion(id_leccion) ON UPDATE CASCADE ON DELETE RESTRICT

) ENGINE=INNODB;

DESCRIBE seccion;

COPY

This code is very usefull when we need to create some tables that depends one from each other. The purpose of this code is to create 3 tables: 1- Lessons (leccion) 2- Questions (preguntas) 3- Sections (seccion) Each Lesson is a container for Questions and each question can have two or more Sections.

Save snippets that work from anywhere online with our extensions

Comments

MySQL

@soyreynald

Creating a database in MySQL with tables and Foreign keys (SQL)

#sql #nodejs

javascript - Import sql file in node.js and execute against PostgreSQL - Stack Overflow

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

#sql

mysql FIND_IN_SET equivalent to postgresql

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

#sql

sql.How to get Quarter’s Start and End Date for a given date in Sql Server

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

#mysql

mysql - What are these files ,can I delete them manually? - Server Fault

[root@jiaoyou mysql]# pwd
/var/lib/mysql
[root@jiaoyou mysql]# ls -ls
338256 -rw-rw---- 1 mysql mysql 346030080 2010-04-22 08:08 ibdata1
626812 -rw-rw---- 1 mysql mysql 641222072 2010-01-26 07:17 mysql-bin.000008
316892 -rw-rw---- 1 mysql mysql 324173772 2010-03-25 12:51 mysql-bin.000009
52724 -rw-rw---- 1 mysql mysql  53931666 2010-04-12 12:13 mysql-bin.000010
10136 -rw-rw---- 1 mysql mysql  10359639 2010-04-22 08:32 mysql-bin.000011

mysql> SHOW BINARY LOGS; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 | 641222072 | 
| mysql-bin.000009 | 324173772 | 
| mysql-bin.000010 |  53931666 | 
| mysql-bin.000011 |  10360680 | 
+------------------+-----------+


Solution

PURGE BINARY LOGS TO 'mysql-bin.010';

#mysql

MySQL Database won't start in XAMPP Manager-osx - Stack Overflow

sudo killall mysqld
sudo /Applications/XAMPP/xamppfiles/bin/mysql.server start

#sql #mysql

MySQL set DB collation

ALTER DATABASE database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#sql #mysql

SQL iki tabloyu birleştirme

SELECT id, account_id, amount, created_at, null as type
 	FROM subscription_invoice
UNION ALL
SELECT id, account_id, amount, created_at, 
	`type` as type
FROM subscription_payment
ORDER BY created_at DESC

#prestashop #delete #mysql #db #database #ids

Delete from DB if not in array

Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")

#mysql

Create table sample

CREATE TABLE firends (
	id INTEGER,
  	firstname VARCHAR(64)
);

#mysql

mySQL timestamp for created at

CURRENT_TIMESTAMP

#mysql

mySQL timestamp for updated at

CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

#mysql

Select Distinct column (a) & order by column (b)

SELECT ticker,`datetime`
FROM (
  SELECT ticker, MAX(`datetime`) `datetime`
  FROM Bloomberg_hist_data
  GROUP BY ticker
) t ORDER BY `datetime`

#mysql

Update column with random string from list

UPDATE deltadb.transactions_temp
SET ticker =(CASE CEIL(RAND()*2)
              WHEN 1 THEN '3CN:SP'
              WHEN 2 THEN 'AGV:SP'
          END);

#mysql

mySQL FIND IN SET Multiple

CONCAT(',', section_id, ',') REGEXP ',(val1|val2|val3),'"

#sql #mysql

SQL Database - School

-- these scripts will delete the tables if it's already there

DROP TABLE IF EXISTS StudentEnrollments;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Classrooms;


-- create and populate the students table
CREATE TABLE Students
(
	StudentId INTEGER PRIMARY KEY,
	FirstName VARCHAR(200) NOT NULL,
	LastName VARCHAR(200) NOT NULL,
	Nationality VARCHAR(100) NOT NULL,
	DateOfBirth DATETIME NULL
);


INSERT INTO Students
	(StudentId, FirstName, LastName, Nationality, DateOfBirth)
VALUES
	('1','Mickey', 'Mouse', 'American', '1991-05-02'),
	('2','Donald', 'Duck', 'Japanese', '1992-11-12'),
	('3','Goofy', 'Goof', 'American', '1980-04-15'),
	('4','Daisy', 'Duck', 'French', '1985-02-16'),
	('5','Huey', 'Duck', 'French', '1986-05-19'),
	('6','Scrooge', 'McDuck', 'Japanese', '1983-11-11'),
	('7','Minnie', 'Mouse', 'Canadian', '1983-11-30'),
	('8','Louie', 'Duck', 'French', '1985-09-09');

-- create and populate the classroom table
CREATE TABLE Classrooms
(
	ClassroomId INTEGER PRIMARY KEY,
	ClassName VARCHAR(200) NOT NULL,
	Weight DECIMAL NOT NULL
);

INSERT INTO Classrooms
	(ClassroomId, ClassName, Weight)
VALUES
	(1, 'Public Interaction', 0.10),
	(2, 'Pranks', 0.15),
	(3, 'Running', 0.15),
	(4, 'Acting', 0.30),
	(5, 'Making Jokes', 0.30);

-- create and populate the student enrollment table
CREATE TABLE StudentEnrollments
(
	StudentEnrollmentId INTEGER PRIMARY KEY,
	StudentId INTEGER NOT NULL,
	ClassroomId INTEGER NOT NULL,
	Grade DECIMAL NOT NULL,
	FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
	FOREIGN KEY(ClassroomId) REFERENCES Classrooms(ClassroomId)
);

INSERT INTO StudentEnrollments
	(StudentEnrollmentId, StudentId, ClassroomId, Grade)
VALUES
	(1, 1, 1, 91),
	(2, 1, 2, 68),
	(3, 1, 3, 89),
	(4, 1, 4, 60),
	(5, 1, 5, 65),
	(6, 2, 1, 79),
	(7, 2, 2, 85),
	(8, 2, 3, 68),
	(9, 2, 4, 89),
	(10, 2, 5, 80),
	(11, 3, 1, 96),
	(12, 3, 2, 62),
	(13, 3, 3, 78),
	(14, 3, 4, 100),
	(15, 3, 5, 64),
	(16, 4, 1, 81),
	(17, 4, 2, 90),
	(18, 4, 3, 85),
	(19, 4, 4, 95),
	(20, 4, 5, 64),
	(21, 5, 1, 81),
	(22, 5, 2, 73),
	(23, 5, 3, 60),
	(24, 5, 4, 99),
	(25, 5, 5, 70),
	(26, 6, 1, 75),
	(27, 6, 2, 74),
	(28, 6, 3, 69),
	(29, 6, 4, 79),
	(30, 6, 5, 88),
	(31, 7, 1, 60),
	(32, 7, 2, 75),
	(33, 7, 3, 82),
	(34, 7, 4, 66),
	(35, 7, 5, 65),
	(36, 8, 1, 69),
	(37, 8, 2, 81),
	(38, 8, 3, 100),
	(39, 8, 4, 63),
	(40, 8, 5, 62);

Creating a database in MySQL with tables and Foreign keys (SQL)

Save snippets that work from anywhere online with our extensions

Comments

More like this

MySQL

Browse more snippets >>

Creating a database in MySQL with tables and Foreign keys (SQL)

Save snippets that work from anywhere online with our extensions

Comments

More like this

MySQL

Browse more snippets >>

Embed code snippet