addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscrossdots-three-verticaleditemptyheartexporteye-with-lineeyefacebookfolderfullheartglobegmailgooglegroupsimageimagesinstagramlinklocation-pinm-swarmSearchmailmessagesminusmoremuplabelShape 3 + Rectangle 1outlookpersonJoin Group on CardStartprice-ribbonImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruseryahoo

Sunday's Notes

From: Aoirthoir An B.
Sent on: Monday, November 16, 2009 1:33 PM
Here are the notes from yesterday's meetup:



Create, Insert, Describe, Show, Describe, Select, Delete Data in Tables in MySQL

working in the database 'learnadd1' on the server for our class

To connect to the mysql server location ssh or Putty to:

[address removed]

 

the password is:

learn*01

 

cd student

cd james/

 

cd = change directory

ls = list

nano = is an editor in of place of  pico

'control' L = clear the terminal screen

 

at the command prompt (which looks like this):

([address removed] ~/students/james $)

 

type:

mysql -u learnadd1 -p -h mysql.tastala.com learnadd1

-u = username = learnadd1

-p = give me a password prompt (this is not a good place to add the password ��� secuirty issues)

-h = host = mysql.tastala.com

the database we are using is learnadd1 we add that at the end of this string to get to the right database

 

the password is

learn*01

 

use ` back ticks when creating names, it differentiates between the two languages.

Surround `database` names, `table` names, and `column` names by back ticks as this will keep you safe from accidentally causing a conflict with a command.

 

CREATE table 'james_students' ('id' INT NOT NULL auto_increment, 'first_name' VARCHAR(30), 'last_name' VARCHAR(30), primary key ('id')) ENGINE=myisam;

 

you can see an example of create table in book at the bottom of page 48

 

SHOW tables; page 84

SHOW create table `james_students`\G;

              the capital G shows the exact statement used to create the table.

DESCRIBE `james_students`;

              page 59

INSERT into `james_students`(`first_name`, `last_name`) VALUES ('James', 'Stone'), ('Stuart', 'Smith'), ('Carmen', 'Carol');

              page 68

SELECT from `james_students`; page 75

specify the columns using the SELECT statement, you gain more control

 

do not use wild cards ��� it is bad practice

 

reduces the time it takes to track down an error, keeps security high

 

SELECT id, first_name, last_name, now() from james_students;

SELECT id, first_name, last_name, now() from james_students ORDER BY last_name;

              ORDER BY by page 80

SELECT id, first_name, last_name, now() from james_students ORDER BY LIMIT 0,2;

              LIMIT page

 

UPDATE `james_students` SET `last_name`='Arnold' WHERE `id`=5; this works

UPDATE `james_students` SET `last_name`='Arnold' WHERE `last_name`='Arnold'; this does not work

UPDATE `james_students` SET `last_name`='Arnold' WHERE `last_name` LIKE 'A%';

              the % is a valid wild card to see all names with A at the beginning

 

DELETE FROM `james_students`;

 

 

 

 

 

 



People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy