Amazon

Monday, June 8, 2009

MySQL - Part 1 - Step by Step

Start the MySql Database
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld
Listing the Databases/schemas available using root user.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow -u root
+--------------------+
Databases
+--------------------+
information_schema
mysql
test
+--------------------+

Here three Databases/schemas are available as seen listed above.

Listing content of Schema test, by the command below.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow -u root test
Database: test
+--------+
Tables
+--------+
+--------+

Its not having any table.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow -u root information_schema
Database: information_schema
+---------------------------------------+
Tables
+---------------------------------------+
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
KEY_COLUMN_USAGE
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
+---------------------------------------+

A lot of tables are available in information_schema.

Selecting the status of MySql Database on your machine

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqladmin version status proc
mysqladmin Ver 8.42 Distrib 5.1.31, for Win32 on ia32
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.1.31-community
Protocol version 10
Connection localhost via TCP/IP
TCP port 3308
Uptime: 15 min 10 sec

Threads: 1 Questions: 31 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.34
Uptime: 910 Threads: 1 Questions: 31 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second
avg: 0.34
mysqladmin: process list failed; error: 'Access denied; you need the PROCESS privilege for this operation'

Using test database.

Open test database

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.1.31-community MySQL Community Server (GPL)

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

Create table shop in test database.

mysql> CREATE TABLE shop (
-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
-> dealer CHAR(20) DEFAULT '' NOT NULL,
-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
-> PRIMARY KEY(article, dealer));
Query OK, 0 rows affected (0.03 sec)

Insert records in shop table

mysql> INSERT INTO shop VALUES
-> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
-> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0

Select records from shop table.

mysql> select * from shop;
+---------+--------+-------+
article dealer price
+---------+--------+-------+
0001 A 3.45
0001 B 3.99
0002 A 10.99
0003 B 1.45
0003 C 1.69
0003 D 1.25
0004 D 19.95
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql>exit

Listing tables present in test database. The shop table created above.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow test
Database: test
+--------+
Tables
+--------+
shop
+--------+

No comments:

Post a Comment

Amazon Best Sellors

Weblogic - Server Creation - Issues

Datasource creation using python script.  Python Script was failing after creating Datasource. From UI Admin Console, if I try to activat...