MySql
You can Download mysql from www.mysql.com
You can download DBBrowser from
http://databasebrowser.sourceforge.net
Steps to install MYSQL
1.Double click the downloaded rar file
2.Double Click mysql-nt.exe file to start install
3.After install Configure the MYSql using configuration wizard
from start->allprograms->MYSQL server->mysql instance configwizard
4.Choose standard configuration
5.Check include bin to directory path
6.Give user name and password click ok
7.Now you can able to create table using mysql.
Steps to run a Jdbc Program using MYSQL
1.Copy paste the myjdbc-connector in to MYSQL Installed folder
2.Set classpath to that Path.
ex:set classpath=C:/Program Files/MySQL/MySQL Server 5.0/
mysql-connector-java-5.1.6/
Example MYSQL JAVA JDBC PROGRAM
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class mysql
{
public static void main(String[] args) {
Statement stmt = null;
ResultSet rs = null;
try {
// The newInstance() call is a work around for some
// broken Java implementations
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
} catch (Exception ex) {
// handle the error
}
try {
Connection conn =
DriverManager.getConnection(“jdbc:mysql://localhost/test?” +
“user=root&password=*****”);
stmt = conn.createStatement();
rs = stmt.executeQuery(“SELECT * FROM one”);
// or alternatively, if you don’t know ahead of time that
// the query will be a SELECT…
if (stmt.execute(“SELECT * FROM one”)) {
rs = stmt.getResultSet();
// Do something with the Connection
while(rs.next())
{
String name=rs.getString(“name”);
System.out.println(name);
}
}
}
catch (SQLException ex) {
// handle any errors
System.out.println(“SQLException: ” + ex.getMessage());
System.out.println(“SQLState: ” + ex.getSQLState());
System.out.println(“VendorError: ” + ex.getErrorCode());
}
}
}
DBBrowser Sofware Connection setup ways
Choose the Name : MyDatabase
Select the Dbms type 
racle or MySQL
Choose jdbc jar file in bin of Mysql installation or Mysql connector folder
Database ur :jdbc:mysql://localhost:3306/
username:root
password:*****
click test connection and save
Now click on top the connection and click connect
Now You can view your table values from dbbrowser itself.
Creaing database in Mysql
Before creating
mysql>show databases;
+——————–+
| Database |
+——————–+
| information_schema |
mysql |
| test |
+——————–+
mysql> create database jamal;
Query OK, 1 row affected (0.08 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| jamal |
| mysql |
| test |
+——————–+
4 rows in set (0.05 sec)
Now use the database by putting command
mysql>use jamal;
Now u can create tables and use this database
CREATING TABLE ON MYSQL
CREATE TABLE fish (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY pk (id),
UNIQUE KEY uk (name)
) ENGINE=InnoDB;
INSERT INTO fish VALUES
-> (’1′,’guppy’), (’2′,’tuna’), (’3′,’shark’),
-> (’4′,’manta ray’), (’5′,’grouper’), (’6′,’puffer’);
enter
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from fish;
+—-+———–+
| id | name |
+—-+———–+
| 5 | grouper |
| 1 | guppy |
| 4 | manta ray |
| 6 | puffer |
| 3 | shark |
| 2 | tuna |
+—-+———–+
6 rows in set (0.02 sec)
type sql statement syntax in mysql;
mysql> create table name(name varchar(20),no int primary key not null);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into name values(‘jamal’);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
mysql> insert into name values(‘jamal’,1);
Query OK, 1 row affected (0.06 sec)
mysql> insert into name values(‘safiq’,1);
ERROR 1582 (23000): Duplicate entry ’1′ for key ‘PRIMARY’
mysql> create table name1(name varchar(20),no int primary key key);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into name1 values(‘safiq’,1);
Query OK, 1 row affected (0.03 sec)
mysql> select e.name,a.name1 from name e,name1 a;
ERROR 1054 (42S22): Unknown column ‘a.name1′ in ‘field list’
mysql> select e.name,a.name from name e,name1 a;
+——-+——-+
| name | name |
+——-+——-+
| jamal | safiq |
+——-+——-+
1 row in set (0.03 sec)
mysql> select * from name join name1;
+——-+—-+——-+—-+
| name | no | name | no |
+——-+—-+——-+—-+
| jamal | 1 | safiq | 1 |
+——-+—-+——-+—-+
1 row in set (0.00 sec)
How to alter,update table in mysql
Follw the commands and type in your mysql console and you came to know about creating,updating,modifying tables
easily.
mysql> create table two(age int);
Query OK, 0 rows affected (0.09 sec)
mysql> Alter table one add(age int);
Query OK, 2 rows affected (0.45 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc two;
+——-+———+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———+——+—–+———+——-+
| age | int(11) | YES | | NULL | |
+——-+———+——+—–+———+——-+
1 row in set (0.31 sec)
mysql> desc one;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set (0.00 sec)
mysql> select * from one;
+——-+——+
| name | age |
+——-+——+
| jamal | NULL |
| safiq | NULL |
+——-+——+
2 rows in set (0.01 sec)
mysql> update one set age=10 WHERE name=’jamal’;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from one;
+——-+——+
| name | age |
+——-+——+
| jamal | 10 |
| safiq | NULL |
+——-+——+
2 rows in set (0.03 sec)
mysql> alter table one engine=innodb;
Query OK, 2 rows affected (0.47 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+——-+——+
| name | age |
+——-+——+
| jamal | 23 |
| safiq | 19 |
+——-+——+
2 rows in set (0.00 sec)
mysql> alter table one auto_increment=3
-> ;
Query OK, 2 rows affected (0.19 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+——-+——+
| name | age |
+——-+——+
| jamal | 23 |
| safiq | 19 |
+——-+——+
2 rows in set (0.00 sec)
mysql> set insert_id=3;
Query OK, 0 rows affected (0.09 sec)
mysql> alter table one auto_increment=3;
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+——-+——+
| name | age |
+——-+——+
| jamal | 23 |
| safiq | 19 |
+——-+——+
2 rows in set (0.00 sec)
AUTO INCREMENT ID CREATION
mysql> alter table one add(id int auto_increment primarykey);
ERROR 1064 (42000): You have an error in your SQL syntax; check the m
corresponds to your MySQL server version for the right syntax to use
rykey)’ at line 1
mysql> alter table one add(id int auto_increment primary key);
Query OK, 2 rows affected (0.20 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+——-+——+—-+
| name | age | id |
+——-+——+—-+
| jamal | 23 | 3 |
| safiq | 19 | 4 |
+——-+——+—-+
2 rows in set (0.00 sec)
ORDER BY CLAUSE
mysql> select * from one order by age;
+——-+——+—-+
| name | age | id |
+——-+——+—-+
| safiq | 19 | 4 |
| jamal | 23 | 3 |
+——-+——+—-+
2 rows in set (0.00 sec)
Change Password For MYSQL DATABASE
Here in this post you will find
how to change the password in the mysql open source database
mysql is better used with php and java
.
Because of being opensource it will be highly recommended for all users.Getting the help from mysql itself is easy
if you type anything related to help it will show the exact word needed to get help.
for ex:
mysql> help change password
Nothing found
Please try to run ‘help contents’ for a list of all accessible topics
Then type help contents and get the catagory of help
mysql> help Account management
You asked for help about help category: “Account Management”
For more information, type ‘help ‘, where is one of the following
topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
mysql> help set password
Name: ‘SET PASSWORD’
Description:
Syntax:
SET PASSWORD [FOR user] =
{
PASSWORD(‘some password’)
| OLD_PASSWORD(‘some password’)
| ‘encrypted password’
}
The SET PASSWORD statement assigns a password to an existing MySQL user
account.
If the password is specified using the PASSWORD() or OLD_PASSWORD()
function, the literal text of the password should be given. If the
password is specified without using either function, the password
should be the already-encrypted password value as returned by
PASSWORD().
With no FOR clause, this statement sets the password for the current
user. Any client that has connected to the server using a non-anonymous
account can change the password for that account.
With a FOR clause, this statement sets the password for a specific
account on the current server host. Only clients that have the UPDATE
privilege for the mysql database can do this. The user value should be
given in user_name@host_name format, where user_name and host_name are
exactly as they are listed in the User and Host columns of the
mysql.user table entry. For example, if you had an entry with User and
Host column values of ‘bob’ and ‘%.loc.gov’, you would write the
statement like this:
SET PASSWORD FOR ‘bob’@'%.loc.gov’ = PASSWORD(‘newpass’);
URL: http://dev.mysql.com/doc/refman/5.1/en/set-password.html
mysql> SET PASSWORD FOR ‘root’@'localhost’=PASSWORD(‘root’);
Query OK, 0 rows affected (0.01 sec)
mysql>
