To login (from unix shell) use -h only if needed.
# [mysql
dir]/bin/mysql -h hostname -u root -p
# [mysql dir]/bin/mysql localhost -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];
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');
To insert Data into value into table
mysql>insert into [tablename] values ('Sunder','9934041999');
to show to data in 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 unix shell.
# [mysql
dir]/bin/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/privilages.
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.
calculation in particular column
==============================================================================
make sum of particular column in the table
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
calculation in particular column
==============================================================================
make sum of particular column in the table
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
The COUNT() function returns the number of rows that matches a specified criteria.
SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
The FIRST() Function
The FIRST() function returns the first value of the selected column.
SQL FIRST() Syntax
SELECT FIRST(column_name) FROM table_name
SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1
The LAST() Function
The LAST() function returns the last value of the selected column.
SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name
mysql> alter table
[table name] modify [column name] VARCHAR(3);
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1
The MAX() Function
The MAX() function returns the largest value of the selected column.
SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
FROM table_name
WHERE column_name operator value
GROUP BY column_name
SQL GROUP BY Example
We have the following "Orders" table:
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/11/12 | 1000 | Hansen |
2 | 2008/10/23 | 1600 | Nilsen |
3 | 2008/09/02 | 700 | Hansen |
4 | 2008/09/03 | 300 | Hansen |
5 | 2008/08/30 | 2000 | Jensen |
6 | 2008/10/04 | 100 | Nilsen |
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
GROUP BY Customer
The result-set will look like this:
Customer | SUM(OrderPrice) |
---|---|
Hansen | 2000 |
Nilsen | 1700 |
Jensen | 2000 |
Nice! Isn't it? :)
Let's see what happens if we omit the GROUP BY statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
The result-set will look like this:
Customer | SUM(OrderPrice) |
---|---|
Hansen | 5700 |
Nilsen | 5700 |
Hansen | 5700 |
Hansen | 5700 |
Jensen | 5700 |
Nilsen | 5700 |
The result-set above is not what we wanted.
Explanation of why the above SELECT statement cannot be used: The SELECT statement above has two columns specified (Customer and SUM(OrderPrice). The "SUM(OrderPrice)" returns a single value (that is the total sum of the "OrderPrice" column), while "Customer" returns 6 values (one value for each row in the "Orders" table). This will therefore not give us the correct result. However, you have seen that the GROUP BY statement solves this problem.
GROUP BY More Than One Column
We can also use the GROUP BY statement on more than one column, like this:
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate
GROUP BY Customer,OrderDate
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
SQL HAVING Example
We have the following "Orders" table:
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/11/12 | 1000 | Hansen |
2 | 2008/10/23 | 1600 | Nilsen |
3 | 2008/09/02 | 700 | Hansen |
4 | 2008/09/03 | 300 | Hansen |
5 | 2008/08/30 | 2000 | Jensen |
6 | 2008/10/04 | 100 | Nilsen |
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
The result-set will look like this:
Customer | SUM(OrderPrice) |
---|---|
Nilsen | 1700 |
Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
The result-set will look like this:
Customer | SUM(OrderPrice) |
---|---|
Hansen | 2000 |
Jensen | 2000 |
The UCASE() Function
The UCASE() function converts the value of a field to uppercase.
SQL UCASE() Syntax
SELECT UCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT UPPER(column_name) FROM table_name
SQL UCASE() Example
We have the following "Persons" table:
P_Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Hansen | Ola | Timoteivn 10 | Sandnes |
2 | Svendson | Tove | Borgvn 23 | Sandnes |
3 | Pettersen | Kari | Storgt 20 | Stavanger |
Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to uppercase.
We use the following SELECT statement:
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
The result-set will look like this:
LastName | FirstName |
---|---|
HANSEN | Ola |
SVENDSON | Tove |
PETTERSEN | Kari |
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);
Dump all databases
for backup. Backup file is sql commands to recreate all db's.
# [mysql
dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql
dir]/bin/mysqldump -u username -ppassword --databases databasename
>/tmp/databasename.sql
Dump a table from a database.
# [mysql
dir]/bin/mysqldump -c -u username -ppassword databasename tablename >
/tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql
dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
No comments:
Post a Comment