PHP and MySQL
Terms
undefined, object
copy deck
- Create a database named jokes
- create database jokes;
- Specify that you want to work with the database jokes.
- use jokes;
- Create a table named Jokes, with fields called ID, JokeText, and JokeDate.
-
create table Jokes (
ID int not null auto_increment primary key,
JokeText text,
JokeDate date
); - See all the tables in a database.
- show tables;
- Add a record.
-
insert into Jokes set
JokeText = "Why did the chiecken cross the road. To get to the other side.",
JokeDate = "2004-12-25"; - See all the tables in the database jokes.
- show tables;
- See the structure of the table Jokes.
- describe Jokes;
- Delete the table Jokes.
- drop table Jokes;
- Add a joke and the date to the table Jokes.
-
insert in Jokes set
JokeText="Why did the chicken cross the road?",
JokeDate="2004-12-15"; - See all the records in Jokes.
- select * from Jokes;
- See all the IDs and JokeText in Jokes.
- select ID, JokeText from Jokes;
- See the first 20 characters of JokeText and also the date in Jokes.
- select left(JokeText,20), JokeDate from Jokes;
- Find out how many records are in Jokes.
- select count(*) from Jokes;
- See how many records there are with a date after or on Dec. 10, 2004.
- select count(*) from Jokes where JokeDate >= "2004-12-10";
- See all JokeText that contains the word "chicken."
- select JokeText from Jokes where JokeText like "%chicken%";
- See all the JokeText that contains the word "knock" that has a JokeDate later or on Dec. 10, 2004 and before Jan. 1 2005.
-
select JokeText from Jokes where
JokeText like "%knock%" and
JokeDate >= "2004-12-10" and
JokeDate < "2005-01-01"; - Change the JokeDate of the first record to Dec. 16, 2004.
- update Jokes set JokeDate = "2004-12-16" where ID = 1;
- Change JokeDate to Jan. 1, 2005, for all jokes that contain the word "chicken."
-
Update Jokes set JokeDate = "2005-01-01"
where JokeText like "%chicken%"; - Delete all jokes that contain the word "chicken."
- delete from Jokes where JokeText like "%chicken%";
- Empty the Jokes table.
- delete from Jokes;