[go: up one dir, main page]
Scribd
Upload
100 views2 pages

A Quick Guide To Mysql Tables & Queries: Inserting Data

The document provides a quick reference guide for MySQL including how to create and modify databases and tables, perform queries to retrieve, insert, update and delete data, use patterns and sorting, grouping and limiting results. MySQL is an open source relational database management system that runs on various platforms.

Uploaded by

ĦasĦirSalam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
100 views2 pages

A Quick Guide To Mysql Tables & Queries: Inserting Data

The document provides a quick reference guide for MySQL including how to create and modify databases and tables, perform queries to retrieve, insert, update and delete data, use patterns and sorting, grouping and limiting results. MySQL is an open source relational database management system that runs on various platforms.

Uploaded by

ĦasĦirSalam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

A Quick Guide To MySQL

Tables & Queries


This is a Quick reference Guide for MySQL 5.x.
MySQL is a relational database management system
(RDBMS) based on SQL (Structured Query Language).
MySQL is available under GNU public license and
through subscription to MySQL Network for business
applications. It runs on Unix, iMac, and Windows and
provides rich API for many programming languages
including C, C++, Java, Perl, Python, Ruby and PHP.

Show table format with column names and data types

Inserting Data:

Modify the structure of table

> INSERT INTO <table> (<columns>) VALUES (<data>)


> INSERT INTO teachers (id, name, age) VALUES
(NULL, John, 12);

>DESCRIBE <table name>


>DESCRIBE teachers;

>ALTER TABLE <table name> <alter


specifications>
>ALTER TABLE teachers DROP COLUMN salary;
>ALTER TABLE teachers ADD COLUMN salary
INT(5);
>ALTER TABLE teachers CHANGE firstName
name VARCHAR(20);

Delete the table

>DROP TABLE <table name>


>DROP TABLE teachers;

Retrieving Data:

Database Queries:

>SELECT <columns> FROM <tables> WHERE <conditions>

List all databases

Retrieve from all columns

>SHOW databases;

Select the database

>USE <database name>


>USE university;

Create a database

>CREATE DATABASE <database name>


>CREATE DATABASE university;

Delete a database

>DROP DATABASE <database name>


>DROP DATABASE university;

Rename a database

>ALTER DATABASE <database name> RENAME <new


database name>
>ALTER DATABASE university RENAME faculty

Table Queries:
Create a table

>CREATE TABLE <table name> (<field name> <field type>


(<field size>), )
>CREATE TABLE teachers (name varchar(20),
age INT(10));

List all tables in the database


>SHOW tables;

>SELECT * FROM <tables>


>SELECT * FROM teachers;

Retrieve from selected columns

>SELECT <column 1>, <column 2> FROM <tables>


>SELECT id, name FROM teachers;

Retrieve from selected tables

>SELECT <columns> FROM <table 1>, <table 2>


>SELECT teachers.name, students.name FROM
teachers, students;

Retrieve unique values

>SELECT DISTINCT <column name> FROM <table>


>SELECT DISTINCT name FROM teachers;

Retrieve data satisfying a given condition

>SELECT <columns> FROM <tables> WHERE <condition>


>SELECT name FROM teachers WHERE age > 35;

Retrieve data satisfying multiple conditions

>SELECT <columns> FROM <tables> WHERE <condition>


AND <condition>
>SELECT name FROM teachers WHERE age > 35
AND gender = female;

Loading Data from Files:


>LOAD DATA LOCAL INFILE <filename> INTO TABLE
<table>
>LOAD DATA LOCAL INFILE file.sql INTO
TABLE teachers;
This is very convenient to load data directly from files when
you have thousands of entries.

Modifying Data:
>UPDATE <table> SET <field1> = <value1> AND <field2>
= <value2> WHERE <conditions>
>UPDATE teachers SET status = enrolled
WHERE fees = paid;

Deleting Data:
>DELETE FROM <table> WHERE <condition>
>DELETE FROM teachers WHERE fees = paid;

Pattern Matching Examples:


>SELECT * FROM teachers WHERE name LIKE
j%;
Wildcard % selects joe, john, jones, etc.
> SELECT * FROM teachers WHERE name LIKE
_ _ _ ;
Selects 3 character values.
> SELECT * FROM teachers WHERE name
REGEXP ^A;
Selects all entries beginning with A.
> SELECT * FROM teachers WHERE name
REGEXP p$;
Selects all entries ending with p.
[abc]
[^abc]
[A-Z]
[a-z]
[0-9]

match a, b, or c
match all expect a, b, or c
match uppercase
match lowercase
match any digit

*
match zero or more instances
+
match one or more instances
?
match zero or one instance
.
match any single char
^
match the beginning
$
match the end
|
separates alternatives
The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text or dates.
{n,m}
match at least n times but notmore
than m times
{n}
{n,}

string must occur exactly n times


string must occur at least n times

Sorting:
>SELECT <columns> FROM <table> ORDER BY
<column> <ASC or DESC>
>SELECT * FROM teachers ORDER BY age;
>SELECT * FROM teachers ORDER BY name
DESC;
Sorts the query results.

Limiting:
>SELECT <columns> FROM <table> LIMIT <from>, <to>
>SELECT * FROM teachers LIMIT 1,5;
Limits query results to specific range.

Grouping:
>SELECT <columns> FROM <table> GROUP BY <column>
>SELECT name, COUNT(*) FROM faculty GROUP
BY name;
GROUP BY is used to group values from a column. You can
also perform calculations on that column using count, sum,
avg. Count returns the number of rows which are selected.

This document was written by Awais Naseem & Nazim


Rahman from EMBnet Pakistan Node and being distributed
by Education & Training Program Committee of EMBnet.
EMBnet European Molecular Biology Network is a
bioinformatics support network of bioinformatics support
centers situated primarily in Europe.
http://www.embnet.org/

A Quick Guide To MySQL Tables & Queries


First edition 2010

You might also like