GrassField

one more mysql post for this week!

variables are mysql specific extension.

They hold single values. They are case sensitive. if you assign to a variable using a statement, that returns multiple rows, the value from the last variable is used. if there are no items in the query, and if it is assigned to a variable, no values will be assigned to the variable. instead the old value is retained, if it has any!

User variables are set for the duration of the thread. they are useful particularly when we work with mysql via console.

here are some examples

mysql> select * from delhi;
+—————+——–+
| first_name | amount |
+—————+——–+
| kavitha | 25 |
| raman | 68 |
| anbu | 45 |
| selva | 98 |
| nalini | 25 |
| amutha | 36 |
| richard | 75 |
| lessthanseven | 5 |
+—————+——–+
8 rows in set (0.00 sec)

mysql> set @testvar=5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from delhi where amount<@testvar+1;
+—————+——–+
| first_name | amount |
+—————+——–+
| lessthanseven | 5 |
+—————+——–+
1 row in set (0.00 sec) mysql> set @a=5, @b=6, @c=7; Query OK, 0 rows affected (0.00 sec)

mysql> set @a=5, @b=6, @c=7;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=(@b:=@b+1);
+—————-+
| @a:=(@b:=@b+1) |
+—————-+
| 7 |
+—————-+
1 row in set (0.00 sec)

A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier

mysql> set @thisname:=’first_name’;
Query OK, 0 rows affected (0.00 sec)

mysql> select @thisname from delhi;
+————+
| @thisname |
+————+
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
+————+
8 rows in set (0.00 sec)

Very smart

Hi, this is just an information, i need to keep it over here for my future reference. How to a table with the data collected from two different tables?

Mysql offers the MERGE option. a MERGE table is a collection of identical MyISAM* tables those can be used as one! Identical means, all tables shud have identical columns and index Information.

here is what the query I have used to test it.

create database pandian;

use pandian;
/*table 1,mumbai*/
create table mumbai
(first_name varchar(30),
amount int(10));

/*table 2,mumbai*/
create table delhi
(first_name varchar(30),
amount int(10)
);

/*Insert data*/
insert into mumbai values (‘ranjini’, 75);
insert into mumbai values (‘angel’, 55);
insert into mumbai values (‘mahendran’, 55);
insert into mumbai values (‘veera’, 55);
insert into mumbai values (‘pandian’, 55);
insert into mumbai values (‘balu’, 55);

insert into delhi values (‘kavitha’, 25);
insert into delhi values (‘raman’, 68);
insert into delhi values (‘anbu’, 45);
insert into delhi values (’selva’, 98);
insert into delhi values (‘nalini’, 25);
insert into delhi values (‘amutha’, 36);

/*new table with the data from mumbai and delhi*/
create table total (
name varchar(30),
cash int (10)
) type=merge union=(mumbai, delhi);

/*check whether any changes made to individual tables are getting reflected in the total table, wow yes!*/
insert into delhi values (‘richard’, 75);

/*what will happen if the resultant table is of different structure??*/
create table total_different (
name varchar(15),
cash int (10)
) type=merge union=(mumbai, delhi);
/*
oops.. Gone!

Unable to open underlying table which is differently defined or of non-MyISAM type or doesn’t exist
*/

I was configuring struts DS to connect to mysql today morning. i got failed day be4 yesterday, it ended with some classnot found exception. ooops! i noted tday, there was some error in the webpage i read. here is the details.

mydb name: shiksha
u/n: root
pwd: mysql

here is the datasource part of my struts-config.xml

<>
< type="org.apache.tomcat.dbcp.dbcp.BasicDataSource">
< property="driverClassName" value="com.mysql.jdbc.Driver">
< property="url" value="jdbc:mysql://localhost:3306/shiksha">
< property="username" value="root">
< property="password" value="mysql">
< property="maxActive" value="10">
< property="maxWait" value="5000">
< property="defaultAutoCommit" value="false">
< property="defaultReadOnly" value="false">
< property="validationQuery" value="SELECT COUNT(*) FROM test">
< /data-source>
< /data-sources>

and my action mapping goes like this

< path="/DataSource" type="org.shiksha.TestDB">
< name="success" path="/pages/Welcome.jsp">
< /action>

when i hit
http://localhost/shiksha/DataSource.do

I got the following in the trace

******************************************
********Out Put from TestDataSource ******
User Name is: rajesh
User Name is: george
User Name is: prakash
******************************************

Enjoy buddy, have a nice day.

help: http://forum.java.sun.com/thread.jspa?threadID=528490&messageID=2974589

here is a query to define the default value of a column.

create table test(
username varchar(20) not null default ”
) type=MyISAM;


–
—————————————————
*Free* software is a matter of liberty not price. You should think of "free" as in "free speech".

I am very bad at quering. I was playing with mysql yesterday and find it is very easy to create tables with foreign key relations. see the query

Table1: USER
——————————
user_id* | user_nick
——————————

create table user(
user_id int not null auto_increment,
primary key (user_id),
user_nick varchar(128) not null);

Table1: PROFILE
——————————
profile_id* | profile_nick
——————————
create table profile(
profile_id int not null auto_increment,
primary key(profile_id),
profile_nick varchar(128) not null);

Table1: USER_INFO
————————————————–
user_id*# | user_role# | user_name | user_avatar
————————————————–

create table user_info(
user_id int not null,
primary key (user_id),
index (user_id),
foreign key (user_id)
references user(user_id)
on update cascade
on delete restrict,
user_role int not null,
index (user_role),
foreign key (user_role)
references profile(profile_id)
on update CASCADE
on delete restrict,
user_name varchar(128),
user_avatar varchar(128)
);

Table1: PROFILE_INFO
——————————————————————–
profile_id*# | browse | own_entries | contributor | share | admin |
——————————————————————–
create table profile_info(
profile_id int not null,
primary key(profile_id),
index (profile_id),
foreign key(profile_id)
references profile(profile_id)
on update cascade
on delete restrict,
browse boolean,
own_entries boolean,
contributor boolean,
share boolean,
admin boolean);