Welcome to sysadminguru.com

Unix

Windows

Shell
Scripting

Mysql

Web
Hosting

Forum

Home | About us | FAQ

UNix System Adminitrator Page

Home -> Mysql

Mysql Basics | Troubleshooting Errors | Backup & Restore

Creating a mysql database

syntax :CREATE DATABASE dbname;

Example : mysql> create database employee

Selecting a database in mysql

mysql> USE database;

Listing databases in mysql

mysql> SHOW DATABASES;

Listing tables in a database

mysql> SHOW TABLES;

Describing the format of a table

mysql> DESCRIBE table;

Creating a table

syntax: CREATE TABLE table_name (field1 TYPE(SIZE), field2 TYPE(SIZE));

Example: mysql> CREATE TABLE dept (fist_name VARCHAR(20), last_nameCHAR(20), dob);

Load tab-delimited data into a table

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;

Inserting one record at a time

mysql> INSERT INTO table_name VALUES ('lee', 'Chanr', '1976-10-07');

Quering and displaying records from the table

syntax:: SELECT from_columns FROM table WHERE conditions;

mysql > select * from dept;

Quering and displaying records from the table on a single criteria basis

SELECT * FROM table WHERE rec_name = "value";

Quering and displaying records from the table on multiple criteria basis

SELECT * FROM TABLE WHERE (field1 = "value1" AND (field2 = "value2";

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table

mysql> DELETE FROM dept;

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Selecting specific columns:

mysql> SELECT column_name FROM table;

Displaying unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Displaying and sorting record from a table

mysql> SELECT col1, col2 FROM table ORDER BY col2;

Display record with specific pattern

mysql> SELECT * FROM table WHERE rec LIKE "rose%";

Counting Records

mysql> SELECT COUNT(*) FROM table;

Grouping with Counting:

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;

Selecting from multiple tables:

How to see system variables from command line

root@srvr [~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7686 to server version: 4.0.25-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

mysql>

Untitled Document

Free Link Exchange Cheap Hosting Domain Registration Affiliate Program