SQL command

learning SQL using phpMyAdmin

learning SQL Using phpMyAdmin

This tutorial is amined at programmers. analysts, and designers of dynamic Web sites
who want to learn the basic of SQL.

Q. if programmers, analyst and designer want to build dynamic web sites, what sort of things will be necessary .

What is SQL?

Structured Query Language is a non-procedural language used to define , manipulate and retireve data .

It was developed by IBM(System/R project) in 1974-1979

The American National Standards institute (ANSI) published in 1986 the firest offical standard of language

Virutally everydatabase system nowadays is interfaced through SQL

The specific data arhitecture addressed by SQL is called the relational architecture

SPECIFIC DATA ARCHITECTURE ADDRESSED BY SQL = RELATOINAL ARACHIECTURE

various pieces of data (columns) are grouped into tables contianed in databases,
and we retrieve data susing RELATIONS expressed b/w the tables

CREATING SAMPLE TABLES

I want to design two tables, which will be part of database called geodb

to create the tables we can use phpMyAdmin's structure sub-page in database view ,
we can sue the sql query box to enter the appropriate statement


The table creation is accompished with the CREATE TABLE statement, which we give the new table's name
The statement begins with CREAT TABLE followed by the table name

Then, enclosed in brackets, we put the list of columns, and information abouthe keys

Each column is assigned a name, data type, the NULL or NOT NULL

attribute (here NOT NULL

mean


CREATE TABLE cities (

id int(11) NOT NULL auto_increment,

city_name varchar(50) NOT NULL default '',

latitude varchar(15) NOT NULL default '',

longitude varchar(15) NOT NULL default '',

population int(11) NOT NULL default '0',

country_code char(2) NOT NULL default '',

PRIMARY KEY (id)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

틀린 예문
CREATE TABLE CITIES (

ID NIT(11) NOT NULL auto_increment,
city_name varchar(50) NOT NULL default '',
latitude varchar(15) NOT NULL default '',
longitude varchar(15) NOT NULL default'',
populatoin int(11) NOT NULL default '0',
country_code char(2) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=1;



바른 예문 . 테이블 만들기

CREATE TABLE cities (

id int(11) NOT NULL auto_increment,

city_name varchar(50) NOT NULL default '',

latitude varchar(15) NOT NULL default '',

longitude varchar(15) NOT NULL default '',

population int(11) NOT NULL default '0',

country_code char(2) NOT NULL default '',

PRIMARY KEY (id)

) TYPE=MyISAM AUTO_INCREMENT=1 ;



설명

The id column is our primary key, a column whch uniquely identifies each city.

its data type is INT (an integer number) and MySQL will assign unique numbers to it, thanks to
the AUTO_INCREMENT attribute

note that we cannot use the city name as a primary key, as some city names are not unique in the world

We also use an integer for the populatiion data..

the other columns use character (char) or variable character (varchar)data type.

When we knwot he exact length of data, it's better to use char, specificyging the lenght of the
column as in char(2) otherwise we use a varibale character data type, which will take only the space

needed by each piece of data, and we spefiy the maximum length, as in

VARCHAR(15)

after the columns ist, we have some table-specific information , like its type, and the first
value for the auto-increment column SQL. statments end with a semi-colon


create table countries (
country_code char(2) NOT NULL default '',
country_name varchar(100) NOT NULL default ''

)TYPE=MyISAM:




DATA MODIFICATOIN

in this sectoin, I will do basic syntax for the

INSERT, UPDATE, DELETE and SELECT

statements


ADDING DATA WITH insert

examine the INSERT statement, by looking at the code phpMyAdmin generates

when we do an INSERT OPERATOIN. WE bring up the insert sub-page.

view for the COUNTRIES table, and we enter data about a country



When we click Go, the data is inserted and phpMyAdmin shows us the insert statement used

틀린 입력문
INSERT INTO `countries` ( `country_code` , `country_name` )
VALUES ('ca', 'Canada');

바른 입력문
INSERT INTO 'COUNTRIES' ("COUNTRY_CODE","COUNTRY_NAME") VALUE ('CA','CANADA');


틀린것
insert into "cities" ("id","city_name","latitude","longitude","population","country_code")values('','Sherbrooke','45 23 59.00','-71 46 11.00',125000,'ca');

바른것
INSERT INTO `cities` ( `id` , `city_name` , `latitude` , `longitude` , `population` , `country_code` )
VALUES ('', 'Sherbrooke', '45 23 59.00', '-71 46 11.00', 125000, 'ca');


INSERT INTO `countries` (`country_code` , `country_name` ) VALUES ('zh','China');


INSERT INTO `countries` ( `country_code` , `country_name` ) VALUES ('zh', 'China');

INSERT INTO `countries` ( `country_code` , `country_name` )
VALUES ('zh', 'China');

INSERT INTO `cities` ( `id` , `city_name` , `latitude` , `longitude` , `population` , `country_code` )
VALUES ('', 'Shanghai', '31 13 58.00', '121 26 59.99', 11000000, 'zh');




update data with UPDATE

by clicking on the small pencil-shaped (or edit link)
I can g to the edit panel for the row

change the populatoin value to 130000, After a click on

save, phpMyadmin shows the following statement

UPDATE `cities` SET `population` = '130000' WHERE `id` = '1' LIMIT 1 ;

update `cities` Set `populatoin` = `130000` where 'id' = '1' limit 1;


set 이하는 ' 을 사용한다


UPDATE `cities` SET `city_name` = 'Sherbrooke, Qu bec',
`population` = '130001' WHERE `id` = '1' LIMIT 1 ;



지우기


DELETE FROM `cities` WHERE `id` = '1' LIMIT 1 ;


SELECT `city_name` , `population`
FROM `cities`
WHERE 1
ORDER BY `population` DESC LIMIT 0,30

SELECT country_code, AVG(population)

FROM cities

GROUP BY country_code


조인

SELECT cities.city_name, cities.population, countries.country_name
FROM cities, countries
WHERE cities.country_code = countries.country_code LIMIT 0,30

http://www.php-editors.com/articles/sql_phpmyadmin.php

SQL tip difference b/w `` and ''

`` ''

looks similar but is obviouly different


INSERT INTO `countries` (`country_code` , `country_name` ) VALUES ('zh','China');

remember to use ` before values

use' ' after values