MySQL Basic Commands

Below when you see "#" or "$" it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.

To login (from Linux shell) use -h only if needed.

#mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from Linux shell.

# mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privileges.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Example 2 : LOAD DATA INFILE '/var/lib/mysql-files/test.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db's.

# mysqldump -u root -p --single-transaction --quick --lock-tables=false --all-databases > full-backup-$(date +\%F).sql

or

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

if you want to extract the .gz file, use the command below:
$ gunzip [backupfile.sql.gz]

Dump one database for backup.

# mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# mysql -u username -ppassword databasename < /tmp/databasename.sql


To restore compressed backup files you can do the following:

#gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

#mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Queries 
UPDATE table1 t1 SET table_field = (SELECT tabel_field from table2 t2 WHERE t1.job_no = t2.job_no)

Create user and database  , set password 

#
# Connect to the local database server as user root
# You will be prompted for a password.
#
mysql -h localhost  -u root -p

#
# Now we see the 'mysql>' prompt and we can run
# the following to create a new database for Paul.
#
mysql> create database pauldb;
Query OK, 1 row affected (0.00 sec)

#
# Now we create the user paul and give him full 
# permissions on the new database
mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;
Query OK, 0 rows affected (0.00 sec)

#
# Next we set a password for this new user
#
mysql> set password for paul@localhost = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)

#
# Cleanup and ext
mysql> flush privileges;
mysql> exit;

Working Experience on "office automation" project."

We are working on office automation project , this is the project will reduce the office work with help of computer applications. There will be no need of calculating funds again and again , also no need of making paper records for the various funds transactions.
Our seniors had also tried their hands on this project and they were very close in completion. but, their were some problems in implementation of the project.
I along with my classmate Daljeet Singh Pathania again start working on this project under Dr. H.S.Rai sir's guidance.We started our journey of making project from the collection of previous work done and other details of the project available with Dr. H.S Rai sir. We both were very excited to work on this project and we goes as following:-

First we can try to run on Linux its difficult because our seniors made this on Windows . So our main problem is Linux is case sensitive and Windows not . So we can rename all the files according to file name is used in other files . So we can read all the files and modified according to need . And also make links between the files .

Second major problem is different version of same software a used by the our seniors and Now a we are use different Version . So there are some Keywords which are not support by older or new version .Some of the statement are Not working in new Version software . So we are modified these files .

In between these problems we are also get help from our respected teacher Dr. Hsrai sir . I was facing the problem of using username and password use in this software . Me used the Database username and password on that place by misunderstanding . So I told our sir , about this problem than they will tell to me thats username & password was not databases username & password , its a username which are written by in database tables .

Rai sir's guidance and motivation was always there to help and support us throughout the project.
We were facing the problem in “logging in“ in the software . We were using the database user and password in logging in the project but Rai sir suggest us the write user name and password (i.e. Which stored in the database table).
Now we are able to solve these problem and our project is on 90% worked condition.

Problems still facing

1. How to add new users in the project and how to set the password for the new users.
2. we also have to make amendments in the project according to the current policies of the college.
3. Create the .odt file .

Now I can able to add the user  user ,but how to set the different password for new users , we cannot able to set this. but try in future ..
Now go to second Problem .
Our seniors are make this software according to  that time it need , but now there are many changes which we have to change
1. Add the one new field  in final reports which is generate that is " Higher Education tax" .
To add that field first  we change the database TABLE NAME "amt"  we can add new field that is "hedu" .
After this we can change the  whole system  files to display and also make calculations to add that data in TOTAL AMOUNT  and change the Service TAX ,EDUCATION TAX according to need.
Thats  diffcult job to read and modifly the whole system files ,but we can do it
2. The  SUSPENCE REPORT  is not Working  for ALL  the fields Which user required.
For this  we can modify some programming  files in which the SUSPENCE is not working .
3. In SUSPENCE REPORT  we are not able to get the DATE of CHECK /DD NUMBER .
For this we first add the DATE option in which the user can add  the date of CHECK / DD NUMBER  , than  its this date  can go to database TABLE NAME suspence  ,than  we can retrieve the date form database than Display on the Final SUSPENCE REPORT..
4. User also want to display the extra field in SUSPENCE REPROT  like TDS Amonut , Balance , Service tax and Total Amount.
so we can change this account to requirement .
5. Now the big issue is to generate the "" PROFORMA BILL REPROT"", BILL FEE REPORT" .For the addition of Proforma Bill & Bill Report we can again working with database file and add two tables PROFROBILL and BILL in database TC .After this we can add a new option in MENU.PHP ,than Create on the user interface for this field to add our data in database. Than we can retrieve the data form database tables and able to Create the report according to the user need.
6 .User also required to add the "SOIL" Field in the INSTITUTIONAL phase
7 .Problem with _POST method is working with the string value in PHP ....... This can be solved with help of Hsrai sir....... by using Inter values can be used without any quite, but string values need to be enclosed in single quote.
So Now we can working on this ...............


 

How to Install LAMP in Windows?

Install the Apache Web Server.
1) If you haven't done so already, download the Apache HTTPD Web Server from the Apache web site . Be sure to download the apache_2.2.11-win32-x86-no_ssl MSI installer here: (http://httpd.apache.org/download.cgi)

2) save & Double click the msi file saved on your Windows Desktop. click next
3) Next, fill in all the text boxes with the following information:

1. "Network Domain": localhost
2. "Server Name": localhost
3. "Administrator's Email Address": your email address

 4) Make sure the radio button "for all users, on port 80, as a service - recommended" is selected.Click "Next>".On the next window, click the radio button "Custom", and then click "Next>".

 5) On the next window highlight "Apache HTTP Server" and click the "change" button.

6) We are going to install all the packages and scripts in the folder C:\Server\Apache2\ (assuming C: is your main hard drive). So in the text box "Folder name:" type in "C:\Server\Apache2\". The ending backslash is important.

7) After you have typed in the path, click "OK" and then click "Next>".

8)Click "Install" to begin the installation.

9)Once the Apache installation software has finished installing all the files on your computer, you will see a final window letting you know the installation was a success. Click the "Finish" button.

10)To check that the software was in fact installed successfully, open your favorite browser and type "http://localhost/" into the address bar. You should see a page that looks like this or the words "It Works!":

Install the MySQL Database Server.

1)If you havn't done so already, download the free MySQL Server Community Edition -- you can download the software from the MySQL website. Be sure to download the Windows (x86) version (way at the bottom of the page), which includes a Windows Installer. Save the file on your Windows Desktop.

2)It's time to install SQL Server. The installation file comes as a .zip file. Double click the file. Your unzipping software should open the file and show you a list of files inside the archive. There should be only 1, "Setup.exe". There is no need to extract the file from the archive, just go ahead and double click the "Setup.exe" file. click next > 

3)On the next window, click the radio button "Custom", and then click "Next>". Since we have installed Apache in C:\Server, we are going to install MySQL in the same directory.

4)On the next window, highlight "MySQL Server", and then click the "change" button. 

5)On the next window, in the text box "Folder name:", change the directory to "C:\Server\MySQL\". Make sure you include the ending backslash. Then click "OK".

6)On the next window, click "Next>". 

7)Now MySQL is ready to install.

Click "Install".

8)Once the installation is complete, you will be presented with a "MySQL Sign-Up" window. Click the radio button "Skip Sign-Up", and then click "Next>". You can sign-up for MySQL.com later if you like.

9)MySQL was successfully installed.

Then Install the PHP5 AND phpmyadmin.