SELECT * FROM world WHERE area > 377930 AND population > 127090000 ORDER BY gdp SELECT * FROM world WHERE area < 377930 AND population < 127090000 ORDER BY gdp DESC 77
SELECT * FROM world WHERE area > 377930 AND population > 127090000 ORDER BY gdp SELECT * FROM world WHERE area < 377930 AND population < 127090000 ORDER BY gdp DESC 昇順 78
SELECT * FROM world WHERE area > 377930 AND population > 127090000 ORDER BY gdp SELECT * FROM world WHERE area < 377930 AND population < 127090000 ORDER BY gdp DESC 降順 79
SELECT * FROM world WHERE area > 377930 AND population > ( SELECT population FROM WORLD WHERE name = 'Japan' ) ORDER BY gdp SELECT * FROM world WHERE area > 377930 AND population > 127090000 ORDER BY gdp 88
Exercise 5 : 副問い合わせ SQLZOO: 0 SELECT basics 面積も副問い合わせを利用するようにしよう SELECT * FROM world WHERE area > 377930 AND population > ( SELECT population FROM WORLD WHERE name = 'Japan' ) ORDER BY gdp 90
id name division 1 John 1 2 Jane 2 3 Naruse 3 id name 1 Customer Support 2 System partners divisions SELECT * FROM partners JOIN divisions ON partners.division = divisions.id 171
id name division 1 John 1 2 Jane 2 3 Naruse 3 id name 1 Customer Support 2 System partners divisions SELECT * FROM partners JOIN divisions ON partners.division = divisions.id partners.id partners.name partners.division divisions.id divisions.name 1 John 1 1 Customer Support 2 Jane 2 2 System 172
id name division 1 John 1 2 Jane 2 3 Naruse 3 id name 1 Customer Support 2 System partners divisions SELECT * FROM partners LEFT OUTER JOIN divisions ON partners.division = divisions.id 173
id name division 1 John 1 2 Jane 2 3 Naruse 3 id name 1 Customer Support 2 System partners divisions SELECT * FROM partners LEFT OUTER JOIN divisions ON partners.division = divisions.id 174
id name division 1 John 1 2 Jane 2 3 Naruse 3 id name 1 Customer Support 2 System partners divisions partners.id partners.name partners.division divisions.id divisions.name 1 John 1 1 Customer Support 2 Jane 2 2 System 3 Naruse 3 NULL NULL SELECT * FROM partners LEFT OUTER JOIN divisions ON partners.division = divisions.id 175
CREATE TABLE world ( name VARCHAR(20), continent VARCHAR(20), area INTEGER, population INTEGER, gdp INTEGER, capital VARCHAR(20), tld VARCHAR(4) ) カラムの宣言 191
CREATE TABLE world ( name VARCHAR(20), continent VARCHAR(20), area INTEGER, population INTEGER, gdp INTEGER, capital VARCHAR(20), tld VARCHAR(4) ) カラムの型 VARCHAR は可変長な文字列 192
CREATE TABLE world ( name VARCHAR(20), continent VARCHAR(20), area INTEGER, population INTEGER, gdp INTEGER, capital VARCHAR(20), tld VARCHAR(4) ) カラムの型 VARCHAR は可変長な文字列 データベースによって方言があるので注意 193
CREATE TABLE world ( name VARCHAR(20) NOT NULL, continent VARCHAR(20) NOT NULL, area INTEGER NOT NULL, population INTEGER NOT NULL, gdp INTEGER NOT NULL, capital VARCHAR(20) NOT NULL, tld VARCHAR(4) NOT NULL ) 203
CREATE TABLE world ( name VARCHAR(20) NOT NULL, continent VARCHAR(20) NOT NULL, area INTEGER NOT NULL, population INTEGER NOT NULL, gdp INTEGER NOT NULL, capital VARCHAR(20) NOT NULL, tld VARCHAR(4) NOT NULL ) 204
CREATE TABLE world ( name VARCHAR(20) UNIQUE, continent VARCHAR(20) NOT NULL, area INTEGER NOT NULL, population INTEGER NOT NULL, gdp INTEGER NOT NULL, capital VARCHAR(20) NOT NULL, tld VARCHAR(4) NOT NULL ) 210
CREATE TABLE world ( name VARCHAR(20) UNIQUE, continent VARCHAR(20) NOT NULL, area INTEGER NOT NULL, population INTEGER NOT NULL, gdp INTEGER NOT NULL, capital VARCHAR(20) NOT NULL, tld VARCHAR(4) NOT NULL ) 211
SELECT * FROM users WHERE users.id = '' users.password = '' ID: naruse PASS: ‘ OR ‘a’ = ‘a SELECT * FROM users WHERE users.id = 'naruse’ AND users.password = '' OR 'a' = 'a' 244
SELECT * FROM users WHERE users.id = '' users.password = '' ID: naruse PASS: ‘ OR ‘a’ = ‘a 245 SELECT * FROM users WHERE users.id = 'naruse’ AND users.password = '' OR 'a' = 'a' アタック成功
OOP RDB Employee Name Age Division Position name age division position Jane 22 1 1 id name 1 System Division id name 1 Partner employees divisions positions 261
OOP RDB Employee Name Age Division Position name age division position Jane 22 1 1 id name 1 System Division id name 1 Partner employees divisions positions O/R Mapper 262
public class MyDbContext : DbContext { public DbSet Users { get; set; } } public class User { public string Id { get; set; } public string Name { get; set; } } 263
public class MyDbContext : DbContext { public DbSet Users { get; set; } } public class User { public string Id { get; set; } public string Name { get; set; } } using (var context = new MyDbContext()) { var user = context.Users.Where(x => x.Name == "naruse"); ... } 264
public class MyDbContext : DbContext { public DbSet Users { get; set; } } public class User { public string Id { get; set; } public string Name { get; set; } } using (var context = new MyDbContext()) { var user = context.Users.Where(x => x.Name == "naruse"); ... } SELECT * FROM users WHERE namee = 'naruse' 265
public class MyDbContext : DbContext { public DbSet Users { get; set; } } public class User { public string Id { get; set; } public string Name { get; set; } } using (var context = new MyDbContext()) { var user = context.Users.Where(x => x.Name == "naruse"); ... } SELECT * FROM users WHERE namee = 'naruse' SQL が生成されて 実行される 266