Transcript SQL

LIS651 lecture 6
mySQL
Thomas Krichel
2010-03-03
today
• Today I talk about
– arrays and table (again, since this causes problems)
– some basic mySQL commands
– a PHP interface to mySQL called mysqli
• Today you do
– create a table with phpmyadmin
– write PHP scripts to read/write data to the table using
the web
SQL
• SQL, also pronounced sequel, stands for
"structured query language".
• It is a standard language for querying database.
• In database speak a query is anything one can do
to a database.
casing and colon
• Traditionally SQL commands are written with
uppercase.
• mySQL commands are really case-insensitive.
• But variable names in the commands are casesensitive. I will therefore write them in lowercase.
• All SQL statements are ended with a semicolon.
mySQL
• They are a very successful, open-source vendor
of SQL software.
• Their product is basically freely available.
• We will learn the mySQL dialect of SQL.
using mySQL
•
•
•
mySQL server is installed on wotan.
It is a daemon that deals with client requests.
There is also a tty client installed. To use it you
log into wotan and type
mysql -u user -p
•
and then you type in your password.
We will cover this in the last lecture.
CREATE DATABASE
• CREATE DATABASE a mySQL command to
create a new database.
• Example
CREATE DATABASE newbase;
• creates a database newbase.
• You have no privileges to create a database.
• But I don’t see the reason you wanting to do that.
creating mySQL databases
• To create a user account for your, I did
CREATE DATABASE user_name;
• user_name is your user name. It is the name of
your database as well as the name under which
you are logging in.
USE
• USE database; tells mySQL to start working with
the database database.
• If you have not issued a USE command, you can
still address a table table by using database.table,
where datebase is the name of your database
and table is the name of your table. You are using
the dot to link the two together.
GRANT
• This is a command to create users and give them
privileges. A simplified general syntax is
GRANT privileges ON item TO user_name
[IDENTIFIED BY 'password'] [WITH GRANT OPTION]
• If you use WITH GRANT OPTION, you allow the
user to grant other users the privileges that you
have given to her.
user privileges I
• SELECT allows users to select (read) records
from tables. Generally select is a word used for
read in databases.
• INSERT allows users to insert new rows into
tables.
• UPDATE allows users to change values in
existing table rows.
• DELETE allows users to delete table rows
(records)
• INDEX
allows user to index tables
user privileges II
• ALTER allows users to change the structure of
the database.
– adding columns
– renaming columns or tables
– changing the data types of tables
• DROP allows users to delete databases or
tables. In general, the word drop refers to deleting
database or tables.
user privileges III
• CREATE allows users to create new databases or
tables. If a specific table or database is
mentioned in the GRANT statement, users can
only create that database or table, which will
mean that they have to drop it first.
• USAGE allows users nothing. This is a useful
point to start with if you just want to create a user.
creating mySQL databases
• To allow you access to your databases, I did
GRANT ALL ON user_name.* TO user_name
IDENTIFIED BY 'secret_word' WITH GRANT OPTION;
• user_name is your user name. It is the name of
your database as well as the name under which
you are logging in.
• 'secret_word' is the secret word we use for the
quizzes.
REVOKE
• This is the opposite of GRANT.
create a web user
• You do not want to give the same access rights to
people coming in from the web as you have.
• You do not want to do this. You personally have
too many privileges.
• I have yet to find out how you can create a web
user by yourself.
creating tables
• This is done conveniently in phpmyadmin.
• Here is an example for real SQL code
CREATE TABLE customers (customer_id INT UNSIGNED
NOT NULL AUTO_INCREMENT PRIMARY KEY, name
CHAR(50) NOT NULL, address CHAR(100) NOT NULL,
email CHAR(40), state CHAR(2) NOT NULL);
column data types
• TINYINT can hold a number between -128 and
127 or between 0 to 255. BIT or BOOL are
synonyms for the TINYINT.
• SMALLINT can hold a number between -32768
and +32767 or 0 and 65535
• INT can hold a number between -2**31 and
2**31-1 or between 0 and 2**32-1. INTEGER is a
synonym for INT.
• BIGINT can hold a number between -2**63 and
2**61-1 or between 0 and 2**64-1.
column data types: float
• FLOAT is a floating number on 4 bytes
• DOUBLE is a floating number on 8 bytes
• DECIMAL(x,y) where x is the number of digits
before the decimal point and y is the number of
digits after the decimal point.
column data types: dates
• DATE is a day from 1000-01-01 to 9999-12-31.
• TIME is a time from -838:59:59 to 838:59:59
• DATETIME is a date and time, usually displayed
as YYYY-MM-DD HH:MM:SS
• However, the easiest way I find is to store a date
as as INT.
date as an INT
• The UNIX time stamp is the number of seconds
from the 1st of January 1970.
• The current value is returned by the PHP function
time().
• All values can be converted by the PHP funcition
date() function into strings that humans can easily
understand. Please refer to the help page
http://php.net/manual/en/function.date.php to see
the formating option.
field options
• PRIMARY KEY says that this column is a the
primary key. There can be only one such column.
Values in the column must be unique.
• AUTO_INCREMENT can be used on columns
that contain integer values.
• NOT NULL requires the field not to be empty.
addressing database tables
columns
• Let there by a database database with a table
table and some column column. Then it is
addressed as database.table.column.
• Parts of this notation can be left out if it is clear
what is meant, for example if you have issued
USE database before, you can leave out the
database part.
character sets
• There is a special configuration option with mysql
working on wotan that tries to ensure utf-8 is the
default character set.
[mysqld]
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
• These options are set in a file
/etc/mysql/conf.d/local.cnf on wotan.
collations
• Collations are specifications of word order in
sorting, and equivalences in searching. They are
language specific.
• A list of collation algorithms, that are compiled
against the default unicode collection, can be
found at http://developer.mimer.com/charts/.
• If you require sorting of columns in a certain
language, look for an available collation.
INSERT
• INSERT inserts new rows into a table. In its
simples form
INSERT INTO table VALUES (value1, value2, ..);
• Example:
INSERT INTO products VALUES ('','Neufang Pils',1.23);
• Note that in the example, I insert the null string in
the first column because it is an auto_increment.
• Mark Sandford says: If you use an
auto_increment variable, you may as well have it
last.
partial INSERT
• If you are only giving a part of a record, or if you
want to enter them in a different order you will
have to give a list of column names.
INSERT INTO products (name,id) VALUES ('Neufang
Pils','');
SELECT
• This is the SQL statement to select rows from a
table. Here is the full syntax:
SELECT [options] columns [INTO file_details]
FROM table [WHERE conditions]
[GROUP BY group_type]
[HAVING where_definitions]
[ORDER BY order_type] [LIMIT limit_criteria]
[PROCEDURE proc_name(arguments)]
[lock_options]
columns to SELECT
• You can have a comma-separated list of columns
SELECT name, price FROM products;
• You can use the star to get all columns
SELECT * FROM products;
WHERE condition to SELECT
• = means equality
WHERE id = 3
•
•
•
•
>, <, >=, <= and != also work as expected
IS NULL tests if the value is null
IS NOT NULL
IN allows you to give a set
WHERE state IN ("NY","NJ","CT")
ORDER
• You can order by a field by saying ORDER BY.
• You can add ASC or DESC to achieve ascending
or descending order.
SELECT name, address FROM customers ORDER BY
name ASC
LIMIT
• This can be used to limit the amount of rows.
LIMIT 10 19
• This is useful it web sites where you show a
selection of the results.
• This ends the discussion of the SELECT
command.
UPDATE
• UPDATE [LOW_PRIORITY] [IGNORE] table SET
column1=expression1, column2=expression2...
[WHERE condition] [ORDER BY order_criteria]
[LIMIT number] ;
• This changes values in a row.
• An example is
UPDATE students SET email= '[email protected]'
WHERE name='Janice Insinga';
• IGNORE instructs to ignore errors.
• LOW_PRIORITY instructs to delay if the server is
busy.
DELETE
• DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM table [WHERE condition] [ORDER BY
order_criteria] [LIMIT number]
• Here is a very bad example
DELETE FROM customers;
• Here is a good example
DELETE FROM customers WHERE
customer.name='Thomas Krichel‘;
http://openlib.org/home/krichel
Thank you for your attention!
Please switch off machines b4 leaving!