Search This Blog

Friday, July 6, 2012

Day 21 - 25: MySQL

Day 21: Relational Database Concepts
  • Database intro
  • Tables, Columns, Rows, Values, Keys, Schemas
  • Relationship: 1-1, 1-many, many-many
  • database diagram, database dictionary
  • database fundamentals
  • mySQL monitor : c:\xampp\mysql\bin -> mysql.exe -u root [-p]
  • create database, drop
  • php connect to mySQL DB
  • mySQL Administration DCL: create user, grant ... to, revoke ... from, privileges
  • Storage Engines/Table Type : myisam, innodb
  • Data Types : 
    • numeric - tinyint(bool), int
    • float - float(m,d, double)
    • date & time: date, time, datetime, timestamp
    • string: char(fix length char), varchar; text, blob[binary large object:example-image]
  • DDL: create, alter, drop  table

Day 22-23:
  • DML: insert, update, delete
  • DQL: select ... where
  • indexing
  • keyword: 
    • null, not null, auto_increment, unsigned(int)
    • distinct, order by asc|desc, limit
  • operator:
    • =, >, <, >=, <=, !=, 
    • is null, is not null, between, in, not in, like("%"), not like, regexp
  • join type: union, inner join, left join
  • grouping & aggregating
    • group by... having
    • avg(), count(), min(), max(), sum()
  • subqueries: any, in , some, all
Day 24-25:
Posted by irene at

4 comments:

  1. Rahman AzharJuly 11, 2012 at 4:12 PM

    1a.htm 3b

    select name, region, population from bbc a
    where (select sum(population)
    from bbc b
    where a.region=b.region) < 250000000

    ReplyDelete
  2. Rahman AzharJuly 12, 2012 at 9:15 AM

    1a.html 3c


    SELECT name, region FROM bbc x
    WHERE population > 3 *
    (SELECT population
    FROM bbc y
    WHERE x.region=y.region
    ORDER BY population DESC limit 1,1)

    ReplyDelete
  3. ahmad syahirJuly 12, 2012 at 10:40 AM

    SELECT name, region, population
    FROM bbc x
    WHERE 25000000 >= ALL (SELECT population
    FROM bbc y
    WHERE x.region=y.region)

    ReplyDelete
  4. AnonymousJuly 12, 2012 at 2:18 PM

    http://www.sqlzoo.net/2b.htm
    5a
    SELECT yr, subject FROM nobel
    WHERE yr>='2000'
    GROUP BY yr, subject
    HAVING COUNT(subject)=3

    ReplyDelete

Subscribe to: Post Comments (Atom)