MySQL
MYSQL data manipulation language (DML) commands?
Data Manipulation Language
Data Manipulation Language (DML) statements are used for managing data within tables. Some commands of DML are:
1. SELECT - retrieve data from the a database 2. INSERT - insert data into a table 3. UPDATE - updates existing data within a table 4. DELETE - deletes all records from a table, the space for the records remain 5. MERGE - UPSERT operation (insert or update) 6. CALL - call a PL/SQL or Java/php subprogram 7. LOCK TABLE - control concurrency |
MYSQL data definition language (DDL) commands?
Data Definition Language (DDL)
DDL statements are used to define and modify the database structure of your tables or schema. When you execute a DDL statement, it takes effect immediately.
Some commands of DDL are:
1. CREATE - to create table (objects) in the database 2. ALTER - alters the structure of the database 3. DROP - delete table from the database 4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed 5. COMMENT - add comments to the data dictionary 6. RENAME - rename a table |
How to backup and restore a MySQL database?
You can use mysqldump to create a simple backup of your database using the following syntax.
mysqldump -u [username] -p [password] [databasename] > [backupfile.sql] To Restore mysql -u [username] -p [password] [database_to_restore] < [backupfile] |
MySql frequently used commands?
MySQL is one of the best and globally used database. It will be good to learn the basic commands in Mysql to work interactively with the website and database servers. It is an GPL software as well as paid version is available with support. MySQL uses Structured Query Language (SQL-pronounced sequel), here i have listed mostly used commands for requesting information from a database. Mysql sends each SQL statement that you issue to the server to be executed. Because of its fast performance, reliability, ease of use, and versatility in working with programming languages NASA, FedEx, Yahoo other big php application are using mysql as backend.
This is a list of handy MySQL mysql-textboxs that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP functions you can use to interface with MySQL.
Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.
To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.
mysql> create database [databasename];
List all databases on the sql server.
mysql> show databases;
Switch to a database.
mysql> use [db name];
To see all the tables in the db.
mysql> show tables;
To see database’s field formats.
mysql> describe [table name];
To delete a db.
mysql> drop database [database name];
To delete a table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];
Show certain selected rows with the value “whatever”.
mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;
Show all records containing the name “Bob” AND the phone number ’3444444′.
mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;
Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;
Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.
mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;
Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on
lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,'username’,PASSWORD(‘password’));
mysql> flush privileges;
Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’
Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables.
Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user “bob” to connect to the server from localhost using the password “passwd”.
Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES
(‘%’,'databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = ‘whatever’;
Update database permissions/privilages.
mysql> flush privileges;
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);
Dump all databases for backup. Backup file is sql mysql-textboxs to recreate all db’s.
# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql
Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid
VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default
‘bato’);
| MYSQL Statements and clauses | String Functions | Date and Time Functions |
| ALTER DATABASE
ALTER TABLE ALTER VIEW ANALYZE TABLE BACKUP TABLE CACHE INDEX CHANGE MASTER TO CHECK TABLE CHECKSUM TABLE COMMIT CREATE DATABASE CREATE INDEX CREATE TABLE CREATE VIEW DELETE DESCRIBE DO DROP DATABASE DROP INDEX DROP TABLE DROP USER DROP VIEW EXPLAIN FLUSH GRANT HANDLER INSERT JOIN KILL LOAD DATA FROM MASTER LOAD DATA INFILE LOAD INDEX INTO CACHE LOAD TABLE…FROM MASTER LOCK TABLES OPTIMIZE TABLE PURGE MASTER LOGS RENAME TABLE REPAIR TABLE REPLACE RESET RESET MASTER RESET SLAVE RESTORE TABLE REVOKE ROLLBACK ROLLBACK TO SAVEPOINT SAVEPOINT SELECT SET SET PASSWORD SET SQL_LOG_BIN SET TRANSACTION SHOW BINLOG EVENTS SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW CREATE DATABASE SHOW CREATE TABLE SHOW CREATE VIEW SHOW DATABASES SHOW ENGINES SHOW ERRORS SHOW GRANTS SHOW INDEX SHOW INNODB STATUS SHOW LOGS SHOW MASTER LOGS SHOW MASTER STATUS SHOW PRIVILEGES SHOW PROCESSLIST SHOW SLAVE HOSTS SHOW SLAVE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW VARIABLES SHOW WARNINGS START SLAVE START TRANSACTION STOP SLAVE TRUNCATE TABLE UNION UNLOCK TABLES USE |
AES_DECRYPT
AES_ENCRYPT ASCII BIN BINARY BIT_LENGTH CHAR CHAR_LENGTH CHARACTER_LENGTH COMPRESS CONCAT CONCAT_WS CONV DECODE DES_DECRYPT DES_ENCRYPT ELT ENCODE ENCRYPT EXPORT_SET FIELD FIND_IN_SET HEX INET_ATON INET_NTOA INSERT INSTR LCASE LEFT LENGTH LOAD_FILE LOCATE LOWER LPAD LTRIM MAKE_SET MATCH AGAINST MD5 MID OCT OCTET_LENGTH OLD_PASSWORD ORD PASSWORD POSITION QUOTE REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SHA SHA1 SOUNDEX SPACE STRCMP SUBSTRING SUBSTRING_INDEX TRIM UCASE UNCOMPRESS UNCOMPRESSED_LENGTH UNHEX UPPER |
ADDDATE
ADDTIME CONVERT_TZ CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATE_ADD DATE_FORMAT DATE_SUB DATEDIFF DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS FROM_UNIXTIME GET_FORMAT HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SEC_TO_TIME SECOND STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIMEDIFF TIMESTAMP TIMESTAMPDIFF TIMESTAMPADD TIME_FORMAT TIME_TO_SEC TO_DAYS UNIX_TIMESTAMP UTC_DATE UTC_TIME UTC_TIMESTAMP WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK |
| Mathematical and Aggregate Functions | Flow Control Functions/Command-Line Utilities | PHP API – using functions built into PHP with MySQL |
| ABS
ACOS ASIN ATAN ATAN2 AVG BIT_AND BIT_OR BIT_XOR CEIL CEILING COS COT COUNT CRC32 DEGREES EXP FLOOR FORMAT GREATEST GROUP_CONCAT LEAST LN LOG LOG2 LOG10 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT STD STDDEV SUM TAN TRUNCATE VARIANCE |
CASE
IF IFNULL NULLIF Command-Line Utilities comp_err isamchk make_binary_distribution msql2mysql my_print_defaults myisamchk myisamlog myisampack mysqlaccess mysqladmin mysqlbinlog mysqlbug mysqlcheck mysqldump mysqldumpslow mysqlhotcopy mysqlimport mysqlshow perror |
mysql_affected_rows
mysql_change_user mysql_client_encoding mysql_close mysql_connect mysql_create_db mysql_data_seek mysql_db_name mysql_db_query mysql_drop_db mysql_errno mysql_error mysql_escape_string mysql_fetch_array mysql_fetch_assoc mysql_fetch_field mysql_fetch_lengths mysql_fetch_object mysql_fetch_row mysql_field_flags mysql_field_len mysql_field_name mysql_field_seek mysql_field_table mysql_field_type mysql_free_result mysql_get_client_info mysql_get_host_info mysql_get_proto_info mysql_get_server_info mysql_info mysql_insert_id mysql_list_dbs mysql_list_fields mysql_list_processes mysql_list_tables mysql_num_fields mysql_num_rows mysql_pconnect mysql_ping mysql_query mysql_real_escape_string mysql_result mysql_select_db mysql_stat mysql_tablename mysql_thread_id mysql_unbuffered_query |
What is New In MySQL 5.0?
MySQL is mostly used for web development in the Linux environment. Specially LAMP. It is the “M” in the acronym LAMP (Linux operating system, Apache web server, MySQL database, and Perl / PHP / Python scripting languages).
MySQL is usually described as open source. MySQL is actually available under both free and commercial licenses. MySQL is licensed under the GNU Public License (GPL).
Stored program objects are the most valuable significant upgrade to MySQL5.0.
A) Views
B) Stored Procedures
C) Functions
D) Triggers
Views
A view is a virtual table: a SELECT statement with a name. Microsoft SQL Server calls them views as well; Microsoft Access calls them queries. Selecting from the view name executes the underlying SELECT statement, and returns the results as columns in the virtual table. MySQL views may be read only or updateable. A check option can be specified to prevent views from being updated with rows that they cannot themselves SELECT
Disadvantage.
It is not possible to create an index on a view.
Subqueries cannot be used in the FROM clause of a view.
There is a general principle that you cannot modify a table and select from the same table in a subquery.
Example
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Stored procedures
Stored procedures are created via the CREATE PROCEDURE statement, and executed via the CALL statement. They may include input, output, and input-output parameters. MySQL stored procedures follow the SQL Server model, which permits a rowset to be returned simply by including a SELECT statement in the procedure. Unlike SQL Server, however, stored procedures in MySQL are not compiled. They do share many of the same advantages, such as standardizing code and reducing network traffic by performing business logic within the server.
Why use Stored Procedures?
Stored procedures are fast! It takes some advantage of caching, just as prepared statements do. There is no compilation, so an SQL stored procedure won’t work as quickly as a procedure written with an external language such as C.
2. Stored procedures are components! Suppose that you change your host language — no problem, the logic is in the database not the application.
3. Stored procedures are portable! When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package.
4. Stored procedures are stored! If you write a procedure with the right naming conventions, for example saying chequing_withdrawal for a bank transaction, then people who want to know about chequing can find your procedure. It’s always available as ‘source code’ in the database itself.
5. Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003 standard. Others (DB2, Mimer) also adhere. Others (Oracle, SQL Server).
A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.
CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1); /* statement */
END /* end of block */
What I’m going to do is explain in detail all the things you can do with stored procedures. We’ll also get into another new database object, triggers, because there is a tendency to associate triggers with stored procedures.
Triggers
Triggers are event-driven stored procedures. They are tied to a specific table, and to an event on that table (INSERT, UPDATE, or DELETE). When the event occurs, the trigger is executed (or “fired”.)
One key difference between MySQL triggers and those in SQL Server is that MySQL triggers can be called either before the triggering action or after it, whereas SQL Server triggers are after only. SQL Server does have an INSTEAD OF trigger not present in MySQL. Another key difference is the FOR EACH ROW syntax in MySQL, that will cause the trigger to execute for each row modified. The prefixes “OLD.” and “NEW.” enable the trigger body to reference columns before or after being modified. SQL Server triggers execute once per statement, and must take into account the possibility of multiple rows being affected.
Storage enhancements and tools
1. VARCHAR can store max of 65,532 bytes
2. There is a new BIT datatype.
3. MySQL’s architecture uses plug-in storage engines to implement the physical storage of database tables.
4. Each table may use a different storage engine.
5. The default storage engine, MyISAM, is very fast but does not have the ability to capture transactions.
6. InnoDB storage engine is good for transactions, and aslo provide row-level locking.
7. The InnoDB engine uses a more compact storage format than previously.
8. MySql 5.0 have new storage engine types to the product: Archive, and Federated
9. The Federated storage engine enables access to remote tables, similar to a linked server definition in Microsoft SQL Server.
10. MySql now includes a set of graphical user interfaces for common administration and development tasks.
- MySQL Instance Configuration Wizard – This tool is a step-by-step guide to configuring an instance of MySQL. Specifically, it creates the my.ini file, a text file containing startup configuration parameters.
- MySQL Query Browser – This tool can be used to build queries and test them. It’s similar to Query Analyzer in SQL Server 7.0 and 2000, with a schemata browser
- MySQL Administrator – Common administrative tasks such as creating, altering, and dropping tables in a database can be performed visually via the Table Editor. Indexes and constraints such as foreign keys can also be defined here.
- MySQL System Tray Monitor - Similar to the Service Manager in SQL Server 2000, this tool puts an icon in the Windows SysTray to display the status of the MySQL Instance.
How to Stop SQL Injection in MYSQL?
Every PHP-MYSQL programmer need to know Anti-SQL Injection.
Please take a look at very simple function which can save your database!!
<?Php function ClearInput($dirty){ if (get_magic_quotes_gpc()) { $clean = mysql_real_escape_string(stripslashes($dirty)); }else{ $clean = mysql_real_escape_string($dirty); } return $clean; } ?> |
Commonly Used MySQL Commands.
To login from linux shell -
Mysql>; -h hostname -u root -p
List all available databases with that username.
Show databases;
To use a database -
Use dbname;
To see tables within selected database;
show tables;
To see table field formats;
describe [xyzTableName];
To delete database -
Drop database [xyzDatabase];
To delete a table –
Drop table [xyzTableName];
Show all data in a table –
SELECT * FROM xyzTableName;
