Databases
misc
Queries on mysql databases
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 |
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_optionsmysqldump DB backups
mysqldump -h hostname -u user -pPassword dbname >/path/to/fileNote 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
- mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* to 'myuser'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;"
- mysql -u root -p -e "FLUSH PRIVILEGES;"
- mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* to 'myuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;"
- mysql -p -u root -e "FLUSH PRIVILEGES;"
Simple SQL Queries
- INSERT queries - All database start empty, so you need to put data in. These calls add data to the an existing database
- SELECT queries - Once data is in, you need to use it. Select queries retrieve data from the server, potentially filtering it and performing other logic in the process.
- DELETE queries - Once data is no longer needed, you may want to remove it from your records. These calls removes informatio from a server.
- UPDATE queries - Sometimes information is outdated, these calls modify existing records
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 followingINSERT 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 recordsInsert 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 #] |
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 JoinsLeft 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 |
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:
- Queries can be made to data in a much faster way, especially when there are many concurrent queries
- It is easier to scale a database server as needed, using replication as needed.
- sql syntax is relatively standard and allows for portable code across database servers
- data can be accessed easily through apis in multiple programming environments
- there is a security api included with the sql server which allows for better data security as well as integrity through things such as transactions
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:
- if data is going to be used multiple times, data caching can speed up very easily web page access as well as decrease processor resources being hit
- if many queries are to be done, a well placed index can decrease page processing speed as well as optimise resource use.
- If many inserts / deletes/ updates are to be done, using transactions can greatly improve performance
- There are cases where a simple flat text file can do the job faster and in a much easier way
- Using a GUI tool like phpmyadmin can make your life a lot easier by providing CRUD functionality (create read update delete) through a graphical interface to databases. It also makes creation, editing of databases themselves easier and less error prone. Make sure you password phpmyadmin access if the server is going to be publically accessible, Preferably, do not put it in the default /phpmyadmin/ path to maximise security.
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
- Mysql queries end in a semi column or in the monitor, with g
- Several queries can be put in one string