Databases

This page links to all other sub pages about databases (mostly MySQL) [sb_child_list]

misc

  add an index on a table: ALTER TABLE mytable ADD UNIQUE INDEX code(code);  

Queries on mysql databases

Example queries on mysql tables Creating a user with all privileges: grant all privileges on *.* to 'myuser'@'localhost' identified by 'mypassword' with grant option; Queries on a database:
Open a database use databasename
Create a table create table tablename (field1 int unsigned auto_increment,field2 char(10) not null)
Rename a table rename table oldname to newname
Delete a table drop table [if exists] tablename
Compact a table optimize table tablename
Check table check table tablename [quick|fast|medium|extended]
Backup to file backup tablename[, othertable] TO ‘/the/path/to/backup/dir/’
Restore from file restore tablename[,othertable] FROM ‘/the/path/to/backup’
Repair tables repair table tablename[,othertable] [QUICK | EXTENDED]
List table names Show tables
Show fields Show columns from tablename
Add field in table alter table tablename add column thecounter TINYINT signed auto_increment
  Syntax for getting table info

    SHOW DATABASES [LIKE wild]

or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]

or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]

or SHOW INDEX FROM tbl_name [FROM db_name]

or SHOW TABLE STATUS [FROM db_name] [LIKE wild]

or SHOW STATUS [LIKE wild]

or SHOW VARIABLES [LIKE wild]

or SHOW LOGS

or SHOW [FULL] PROCESSLIST

or SHOW GRANTS FOR user

or SHOW CREATE TABLE table_name

  Alter Table Syntax ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]   alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or    ADD [COLUMN] (create_definition, create_definition,...) or    ADD INDEX [index_name] (index_col_name,...) or    ADD PRIMARY KEY (index_col_name,...) or    ADD UNIQUE [index_name] (index_col_name,...) or    ADD FULLTEXT [index_name] (index_col_name,...) or    ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) [reference_definition] or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} or    CHANGE [COLUMN] old_col_name create_definition or    MODIFY [COLUMN] create_definition or    DROP [COLUMN] col_name or    DROP PRIMARY KEY or    DROP INDEX index_name or    RENAME [TO] new_tbl_name or    ORDER BY col or    table_options

mysqldump DB backups

To do a manual backup a mysql database, use mysqldump with the following syntax:
mysqldump -h hostname -u user -pPassword dbname >/path/to/file
Note that hostname is optional and will default to localhost. Depending on some settings user and password may be optional as well. Now, asssuming you are using a linux shell, if we want to have rotating logs,  add the date name and stick the whole line in a script running in cron:
mysqldump -h hostname -u root mydb > /path/to/backup/$(date +%a)

Add a user to mysql

Here are a couple user creation scenarios for adding a mysql user to a database. Please note that when you create a remote user with a wildcard host %, it does not automatically grant access from localhost. In otherwords, to connect, you have to have to use the external ip even if you are accessing localhost. Also, make sure that mysql is configured to allow access from the ip you are using. Add a local user to mysql from the command line:
  1. mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* to 'myuser'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;"
  2. mysql -u root -p -e "FLUSH PRIVILEGES;"
Add a remote user to mysql:
  1. mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* to 'myuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;"
  2. mysql -p -u root -e "FLUSH PRIVILEGES;"
Add a remote user to mysql:  

Simple SQL Queries

SQL servers provide a way to store data so that it can be retrieved and modified efficiently while providing an english like language called SQL that describes the actions needed. The server divides the data into logical sub units called databases which contain multiple tables of information. These tables contain individual information units called rows that describe individual items such as a person, an object or any bits of information that go together. Finally, each row contains individual fields or columns that describe the item. To use an analogy, suppose the database server is a file cabinet, each individual drawers would represent a database - dividing the space into logical units that store data according to a general logical units (for example, supposing we had multiple computer programs, each application would store information in its own database). Then to push the analogy further, in each drawer we would have folders that further subdivide the data into a specific topic. For example, we could put all accounting data in one table, all sales information into another table, all client information in another table and so on. Finally, each document would represent an actual record or row in the database. As long as information is stored in a logical way, it is easy to tell anyone to add information, remove or update information by simply specifying where the document is and what we want done to it. SQL, short for standard query language is designed for doing such queries in an english like language. There are four major types of operations you can do on the data:
The following queries assume you have a table users defined as follows: CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100), firstname VARCHAR(100), lastname VARCHAR(100), url VARCHAR(100) );

Insert queries

Whenever you need to insert data into a database, you use an insert query. Assuming you you are connected to your database and you have a table called MyTable, the syntax is the following

INSERT INTO MyTable (field1,field2,field3) VALUES (VALUE1,VALUE2, VALUE3)

Please note that sometimes, you will need to insert more than one row at the time. For performance reasons, rather than using multiple insert statements, you can specify more than one row at the time: INSERT INTO users (username,first,last) VALUES ('jstewart','Jon', 'Stewart')  

Select Queries

Queries on records
Insert a row insert into tablename (field1,field2) VALUES (field1_content,field2_content)
List all record select * from tablename
find on a criteria select * from tablename where numericfield=value [and stringfield=’value’]
Delete records delete [LOW_PRIORITY] FROM tablename [where field(s)=criteria] [limit maxrows]
Update a row (s) UPDATE SET field=’value’[,field2=’value’..] [where clause] [order clause] [limit #]
  You can get infos about executed queries with mysql_info(), returning a string in the following format:

Record: 100              Duplicates: 0            Warnings: 0

  Warnings: when null placed in not null column (reverts to default), out of range or illegal value (0 or stripped) Duplicates: Errors due to attempted insertion of duplicate value in unique field   Select query Syntax SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] [select_expression,... [fieldname] [, table.field] [concat(last_name, ‘, ‘, First_Name) as fieldalias] [, math expression] [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [tablename [AS tablealiasforclarity] [, database2.table2] ] [WHERE where_definition] [GROUP BY {unsigned_integer | col_name | formula}] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ] 

Table Joins





Left Outer Join
select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;




Natural [left] join
All Columns with the same name are assumed equal




RIGHT JOIN
Similar to left join, but not recommended to keep queries portable




The USING clause
A LEFT JOIN B USING (Column1, Column2, Column3,...)




STRAIGHT_JOIN
The left table is explicitely loaded before the right, to make sure the right order is respected





 

Examples:

select * from table1,table2 where table1.id=table2.id;

select * from table1 LEFT JOIN table2 ON table1.id=table2.id;

select * from table1 LEFT JOIN table2 USING (id);

select * from table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;

select * from table1 USE INDEX (key1,key2) WHERE key1=1 and key2=2 AND key3=3;

select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND key3=3;





    

    

    
Intro to databases

    

    
Typically, eventually, anyone using php will at some point use it in conjunction with a database server and in most cases, this database server will be MYSQL.



What Are the advantages of using a database server?


The advantages of a database server are multiple:




Things to be aware of when using a database server


Database servers are powerful and used well can bring awesome data crunching capabilities. It is however important to do careful planning or it may easily cripple your web app, especially on a high performance website. 


Here are a couple of tips:




It is also interesting to note that in many cases (especially if the queries are simple), when a mysql server is not available sqlite can be an excellent alternative providing fast performance and small memory foot print… sometimes very easily by simply slightly changing the function names (which are very similar).


 If sqlite is used, make sure the database files are protected from unauthorized web access either by putting them outside the web server document root or with an appropriate htaccess entry.



Notes about queries