Written by Constraints
In SQL, constraints limit what data can go into the field and are usually added when creating the table:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
And well be looking at each individually in the upcoming days.
SQL – part 27
SQL – part 26
For most of you: phpMyAdmin
This is a very useful tool, however if you wish to create a database using SQL you can:
1 | CREATE DATABASE name |
Line 1: Where name is the name of the database to be created.
SQL – part 25
Two or more tables, One result set!
That’s what Union does anyway.
Table1
| ID | Name |
|---|---|
| 1 | PersonA |
| 2 | PersonB |
| 3 | PersonC |
| 4 | PersonD |
Table2
| ID | Name |
|---|---|
| 1 | PersonC |
| 2 | PersonD |
| 3 | PersonE |
| 4 | PersonF |
And we just use:
1 | SELECT Name FROM Table1 UNION ALL SELECT Name FROM Table2 |
And you get:
PersonA
PersonB
PersonC
PersonD
PersonE
PersonF
It only selects Distinct values, use UNION ALL to ignore this and return all the data:
1 | SELECT Name FROM Table1 UNION SELECT Name FROM Table2 |
PersonA
PersonB
PersonC
PersonD
PersonC
PersonD
PersonE
PersonF
SQL – part 24
Joins
Sometimes it’s important to join tables to avoid having duplicate values. Instead we pass unique IDs to a tables row and then reference it again from another table.
We can read the data using joins.
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
1 2 | SELECT * FROM People INNER JOIN Countries ON People.Country=Countries.Name |
And that’s the standard format, just use it for the different scenarios 
Line of Sight – AS2!
Remember this?
Well one of my weird friends has decided he needed in AS2:
The following code is the AS2 version, you should be able to work out what does what and if you need anything just comment below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | //-----Create visual placeholders----- //New MovieClip lineOfSight = _root.createEmptyMovieClip("lineOfSight",1) //-----Define key press automation----- var keys:Array = new Array() var key:Number = 0 var minKeys:Number = 37 var maxKeys:Number = 40 for(key = minKeys; key < maxKeys; key++){ keys[key] = false } //-----Variable definition----- //Movement speed var speed:Number = 4 //Line / sight distance var sightDistance:Number = 100 //Line / sight spread var sightSpread:Number = 60 //Line / sight accuracy (How many lines) var sightAccuracy:Number = 75 //Line / sight hit accuracy (How many 'part' lines) var sightHitAccuracy:Number = 50 _root.onEnterFrame = function(){ move_hero() collision_hero() lineOfSight.clear() create_Sight() } function move_hero(){ if(Key.isDown(Key.LEFT)){ //Left hero._x -= speed } if(Key.isDown(Key.UP)){ //Up hero._y -= speed } if(Key.isDown(Key.RIGHT)){ //Right hero._x += speed } if(Key.isDown(Key.DOWN)){ //Down hero._y += speed } } function collision_hero(){ radiusX = hero._width/2 radiusY = hero._height/2 while(wall.hitTest(hero._x - radiusX, hero._y, true)){ hero._x ++ } while(wall.hitTest(hero._x + radiusX, hero._y, true)){ hero._x -- } while(wall.hitTest(hero._x, hero._y - radiusY, true)){ hero._y ++ } while(wall.hitTest(hero._x, hero._y + radiusY, true)){ hero._y -- } } function create_Sight(){ //Read global variables for mouse position and convert into local variable radiusX = hero._width/2 radiusY = hero._height/2 xMouse = _root._xmouse yMouse = _root._ymouse xDif = xMouse - hero._x yDif = yMouse - hero._y sightAngleBaseRadians = Math.atan2(yDif, xDif) sightAngleBase = 360 * sightAngleBaseRadians / (2 * Math.PI) lineOfSight.lineStyle(3,0xff0000) lineOfSight.beginFill(0xff0000) startX = hero._x + Math.cos(sightAngleBase * Math.PI / 180) * radiusX startY = hero._y - Math.sin(sightAngleBase * Math.PI / 180) * -radiusX //Draw the line lineOfSight.moveTo(startX, startY) for (var sightAngle:Number = 0; sightAngle <= sightSpread; sightAngle += (sightSpread/sightAccuracy)){ //Find individual line angle var sightAngleIndividual = (sightAngleBase-(sightSpread/2) + sightAngle)*(Math.PI/180) for (var sightLength:Number = 0; sightLength < sightDistance; sightLength += (sightDistance/sightHitAccuracy)){ //Find end of line endX = hero._x + Math.cos(sightAngleIndividual) * sightLength endY = hero._y + Math.sin(sightAngleIndividual) * sightLength if(wall.hitTest(endX, endY, true)){ break } } lineOfSight.lineTo(endX, endY) } lineOfSight.lineTo(startX, startY) floor.setMask(lineOfSight) } |
And, thanks to Timothy John McLennan
SQL – part 23
Aliases
When writing SQL queries you might find that it gets complicated to reference many tables at once. Therefore a temporary alias could be used.
1 | SELECT COLUMN FROM TABLE AS alias |
So:
1 | SELECT * FROM people AS p |
and then just use p wherever you might use people
SQL – part 22
Between A and B
The BETWEEN operator is used in a WHERE clause to select a range of data between two values.
1 | SELECT * FROM people WHERE ID BETWEEN 3 AND 7 |
Will return the records for 3, 4, 5, 6 and 7 (Some databases may not show 3 or 7)
And to return everything outside 3 – 7 we can use NOT BETWEEN
so:
1 | SELECT * FROM people WHERE ID NOT BETWEEN 3 AND 7 |
will display 1, 2, 8, 9 etc…
SQL – part 21
The IN Operator
The syntax:
1 2 3 | SELECT column_name(s) FROM TABLE_NAME WHERE column_name IN (value1,value2,...) |
It’s like selecting multiple specific values for a specific column.
1 2 | SELECT * FROM people WHERE Country IN ('England', 'Scotland') |
This will return any records where Country is England or Scotland
SQL – part 20
Wildcards
| Wildcard | Description |
|---|---|
| % | A substitute for zero or more characters |
| _ | A substitute for exactly one character |
| [charlist] | Any single character in charlist |
| [^charlist] | Any single character not in charlist |
These can be used within the SEARCH OF:
LIKE ‘SEARCH’
Please see the regex tutorial series for more information.
SQL – part 19
Searching LIKE a word
That’s right, we can use SQL to find data that is LIKE a word!
And when we combine it with Wildcards (%)
1 | SELECT * FROM People WHERE Country LIKE 's%' |
This will return all of the records where the Country begins with S
1 | SELECT * FROM People WHERE Country LIKE '%d' |
This will return all of the records where the Country ends with S
And using both:
1 | SELECT * FROM People WHERE Country LIKE '%land%' |
This will return any records where the Country CONTAINS land