mysql statments

28
Statement and Syntax Data Definition Statements CREATE DATABASE CREATE TABLE CREATE INDEX CREATE VIEW DROP DATABASE DROP TABLE DROP INDEX DROP VIEW ALTER TABLE RENAME TABLE

Upload: shc

Post on 06-May-2015

2.563 views

Category:

Technology


2 download

DESCRIPTION

Here is the list of all important MySQL functions. Each function has been explained along with suitable example. * MySQL Group By Clause - The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s). * MySQL IN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition. * MySQL BETWEEN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition. * MySQL UNION Keyword - Use a UNION operation to combine multiple result sets into one. * MySQL COUNT Function - The MySQL COUNT aggregate function is used to count the number of rows in a database table. * MySQL MAX Function - The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column. * MySQL MIN Function - The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column. * MySQL AVG Function - The MySQL AVG aggregate function selects the average value for certain table column. * MySQL SUM Function - The MySQL SUM aggregate function allows selecting the total for a numeric column. * MySQL SQRT Functions - This is used to generate a square root of a given number. * MySQL RAND Function - This is used to generate a random number using MySQL command. * MySQL CONCAT Function - This is used to concatenate any string inside any MySQL command. * MySQL DATE and Time Functions - Complete list of MySQL Date and Time related functions. * MySQL Numeric Functions - Complete list of MySQL functions required to manipulate numbers in MySQL. * MySQL String Functions - Complete list of MySQL functions required to manipulate strings in MySQL.

TRANSCRIPT

Page 1: Mysql Statments

Statement and Syntax

Data Definition Statements

CREATE DATABASE CREATE TABLE CREATE INDEX CREATE VIEW DROP DATABASE DROP TABLE DROP INDEX DROP VIEW ALTER TABLE RENAME TABLE

Page 2: Mysql Statments

CREATE DATABASE

CREATE DATABASE creates a database with the given name.

Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Example

CREATE schema test1; CREATE database test1;

Page 3: Mysql Statments

CREATE TABLE

creates a table with the given name. You must have the CREATE privilege for the table.

Syntax

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]

Example

CREATE TABLE `test`.`students` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,`name` VARCHAR( 30 ) NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = InnoDB

Page 4: Mysql Statments

CREATE INDEX creates a index with the given name.

Syntax

CREATE INDEX index_nameON tbl_name (index_col_name,...)

Example

create index tt on students(name)

Page 5: Mysql Statments

CREATE VIEW

The CREATE VIEW statement creates a new view, or replaces an existing one if the OR REPLACE clause is given.

Syntax

CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select_statement

Example

CREATE VIEW test.v AS SELECT * FROM t;

Page 6: Mysql Statments

DROP DATABASE

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement!

Syntax

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Example

Drop database test;

Page 7: Mysql Statments

DROP index indexname

DROP INDEX drops the index named index_name from the table

DROP INDEX index_name ON tbl_name

DROP TABLE removes one or more tables.

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

Page 8: Mysql Statments

RENAME TABLE

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

ALTER TABLE

You can rename a column using a CHANGE old_col_name new_col_name column_definition clause.

ALTER TABLE t1 CHANGE a b INTEGER;

ALTER TABLE `t` ENGINE = MYISAM

Page 9: Mysql Statments

INSERT UPDATE DELETE SELECT TRUNCATE

Data Manipulation Statements

Page 10: Mysql Statments

Syntax

INSERT INTO table_nameVALUES (value1, value2, value3,...)

INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)

Example

INSERT INTO PersonsVALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

The INSERT INTO statement is used to insert a new row in a table.

INSERT

Page 11: Mysql Statments

The UPDATE statement is used to update existing records in a table.

UPDATE

UPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_value

Example

UPDATE PersonsSET Address='Nissestien 67', City='Sandnes'WHERE LastName='Tjessem' AND FirstName='Jakob'

Syntax

Page 12: Mysql Statments

The DELETE statement is used to delete rows in a table.

DELETE

Syntax

DELETE FROM table_nameWHERE some_column=some_value

Example

DELETE FROM PersonsWHERE LastName='Tjessem' AND FirstName='Jakob'

Page 13: Mysql Statments

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SELECT column_name(s)FROM table_name [WHERE where_condition]

SELECT

Syntax

SELECT LastName,FirstName FROM Persons

Example

Page 14: Mysql Statments

Delete the records inside the table.

TRUNCATE

TRUNCATE TABLE table_name

Syntax

TRUNCATE TABLE Persons

Example

Page 15: Mysql Statments

Utility Statements

DESCRIBE EXPLAIN HELP USE

Page 16: Mysql Statments

DESCRIBE provides information about the columns in a table. The EXPLAIN statement can be used either as a synonym for DESCRIBE

Syntax

{DESCRIBE | DESC} tbl_name [col_name]

Example

Describe test;

DESCRIBE

Page 17: Mysql Statments

Example

HELP ‘select'

The HELP statement returns online information from the MySQL Reference manual.

HELP 'search_string'

USE db_name

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued:

HELP

Syntax

Page 18: Mysql Statments

Create a database on the mysql.

create database [databasename];

List all databases on the mysql.

show databases;

Switch to a database. use [db name];

To see all the tables in the db.

show tables;

To see database's field formats. describe [table name];

Page 19: Mysql Statments

To delete a db.

drop database [database name];

To delete a table.

drop table [table name];

Show all data in a table.

SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

show columns from [table name];

Page 20: Mysql Statments

Show certain selected rows with the value "whatever".

SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444'.

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.

SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Page 21: Mysql Statments

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show unique records.

SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

SELECT COUNT(*) FROM [table name];

Page 22: Mysql Statments

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

SET PASSWORD FOR 'user'@'hostname'= PASSWORD('passwordhere');

Joining Tables

With our tables now separated by entity, we join the tables together in our SELECT queries and other statements to retrieve and manipulate related data.

When joining tables, there are a variety of JOIN syntaxes available. Typically developers use the INNER JOIN and OUTER JOIN syntaxes.

Page 23: Mysql Statments

INNER JOIN

Author Table

Author_ID First_Name Last_name

1 Chad Russell2 Jon Stephens3 Mike Hillyer

Book_Author

ISBN Author_ID

1590593324 11590593324 2

Page 24: Mysql Statments

An INNER JOIN query returns one row for each pair or matching rows in the tables being joined. Take our Author and Book_Author tables as an example:

SELECT First_Name, Last_Name, ISBN FROM Author INNER JOIN Book_Author ON Author.Author_ID = Book_Author.Author_ID;

First_Name Last_Name ISBN

Chad Russell 1590593324 Jon Stephens 1590593324

Page 25: Mysql Statments

OUTER JOIN

The third author in the Author table is missing because there are no corresponding rows in the Book_Author table. When we need at least one row in the result set for every row in a given table, regardless of matching rows, we use an OUTER JOIN query.

There are three variations of the OUTER JOIN syntax: LEFT OUTER JOIN, RIGHT OUTER JOIN..

The syntax used determines which table will be fully represented. A LEFT OUTER JOIN returns one row for each row in the table specified on the left side of the LEFT OUTER JOIN clause.

The opposite is true for the RIGHT OUTER JOIN clause.

Page 26: Mysql Statments

In each case, a row of NULL values is substituted when a matching row is not present. The following is an example of a LEFT OUTER JOIN:

SELECT First_Name, Last_Name, ISBNFROM Author LEFT OUTER JOIN Book_Author ON Author.Author_ID = Book_Author.Author_ID;

First_Name Last_Name ISBN

Chad Russell 1590593324 Jon Stephens 1590593324 Mike Hillyer NULL

The third author is returned in this example, with a NULL value for the ISBN column, indicating that there are no matching rows in the Book_Author table.

Page 27: Mysql Statments

quit (\q) Quit mysql.

go (\g) Send command to mysql server.

exit (\q) Exit mysql. Same as quit.

prompt (\R) Change your mysql prompt.

status (\s) Get status information from the server.

use (\u) Use another database. Takes database name as argument.

exit (\q) Exit mysql. Same as quit.

? (\?) Synonym for `help'.

help (\h) Display this help.

mysql Commands

Page 28: Mysql Statments

SHOW ENGINES

SHOW DATABASES

SHOW STATUS

SHOW TABLES

SHOW TABLE STATUS

USE DATABASES