Primary Keys:
- all tables should have "primary keys"
- column that is 100% unique
- no rows can have some primary key.
SHOW databases;
USE <db_name>
SHOW tables;
SHOW COLUMNS FROM customers;
DESC customers;
SELECT * FROM customers LIMIT 0,30;
SELECT city FROM customers;
; is must at the end of the query.
white space & multiple line is allowed.
Its a company standard to use "standard CAPS"
SELECT name,city FROM customers;
SELECT * FROM customers;
SELECT DISTINCT state FROM customers;
SELECT COUNT(state) FROM customers;
SELECT DISTINCT state FROM customers LIMIT 5;
SELECT id,name FROM customers LIMIT 5;
[ its ordering using PRIMARY KEYS by default? ]
SELECT id,name FROM customers LIMIT 5,10
[ From id 6 to 15. (total 10 rows) ]
[In mysql db record start with 0]
Fully qualify name:
SELECT customers.address FROM customers;
SELECT name FROM customers ORDER BY name;
SELECT name,address FROM customers ORDER BY id;
SELECT state,city,name FROM customers ORDER BY state,name,
SELECT name,zip FROM customers ORDER BY zip DESC;
[ASC: for ascending, but this is by default. ]
SELECT name,id FROM customers ORDER BY id DESC LIMIT 1;
SELECT name FROM customers ORDER BY name LIMIT 1;
* BASIC DATA FILTERING:
SELECT id,name FROM customers WHERE id=54;
SELECT id,name FROM customers WHERE id!=54;
SELECT id,name FROM customers WHERE id<8;
SELECT id,name FROM customers WHERE id<=8;
SELECT id,name FROM customers WHERE id BETWEEN 25 AND 30;
[ in WHERE with char,use 'SINGLE QUOTATION' mark. ]
SELECT name,state FROM customers WHERE state='CA';
SELECT name,state,city FROM customers WHERE state='ca' AND city='Hollywood';
SELECT name,state,city FROM customers WHERE city='Boston' OR state='CA';
SELECT id,name,ciry FROM customers WHERE (id=1 OR id=2) AND city='Raleigh';
[ if you have multiple AND or OR statement please use () to group them. ]
*) IN or NOT IN:
SELECT name,state FROM customers WHERE state='CA' OR state='NC' OR state='NY';
(for bunch of OR...)
SELECT name,state FROM customers WHERE state IN('CA','NC','NY') ORDER BY state;
[ its a good idea to show your record in some order. ]
SELECT name,state FROM customers WHERE state NOT IN('CA','NC','NY') ORDER BY state;
% -> wild card -> any things.
SELECT name FROM items WHERE name LIKE 'name%';
SELECT name FROM items WHERE name LIKE '%couputer%';
[ mysql is not case sensitive. ]
- -> only a single character.
*) Regular Expression in mysql:
we can use the same regular expression.
SELECT name FROM items WHERE name REGEXP 'abc|xyz';
SELECT name FROM items WHERE name REGEXP '[0-9]boxes';
* when you want to include your own character in your sql output:
SELECT CONCAT(city,',',state) FROM customers;
SELECT CONCAT(city,','state) AS new_address FROM customers;
SELECT name,cost,cost-1 AS sale_proce FROM items;
* MYSQL FUNCTION:
SELECT name,UPPER(name) FROM customers;
SELECT AVG(cost) FROM items;
SELECT SUM(bids) FROM items;
SELECT COUNT(name) FROM items WHERE seller_id=6;
SELECT seller_id,COUNT(NAME) FROM items GROUP BY seller_id ORDER BY seller_id;
SELECT AVG(cost) FROM items WHERE seller_id=6;
SELECT COUNT(*) AS item_count, MAX(cost) AS max, AVG(cost) AS avg FROM items WHERE seller_id=12;
* GROUP BY:
(instead of WHERE multiple time, we can use GROUP BY )
SELECT seller_id,COUNT(*) AS item_count FROM items GROUP BY seller_id;
[ HAVING is something like WHERE in GROUP BY, so we have to use HAVING in a GROUP BY query. ]
SELECT seller_id,COUNT(*) AS item_count FROM items GROUP BY seller_id HAVING COUNT(*) >=3;
[ So, in GROUP BY use HAVING ]
SELECT seller_id,COUNT(*) AS item_count FROM items GROUP BY seller_id HAVING COUNT(*) >=3 ORDER BY items_count DESC;
*) subquery:
Lets say list those items that's cost is more then the AVG(cost);
- Mysql work like inside-out, so first () and then rest;
SELECT name,cost FROM items WHERE cost > (SELECT AVG(cost)FROM items) ORDER By cost DESC;
query(subquery)
*) mysql join tables:
SELECT cousters.id,customers.name,items.name,item.cost FROM customers,items WHERE customers.id=items.seller_id ORDER BY customers.id;
* using AS we can also give a table 'nick name' not just column.
SELECT i.seller_id,i.name,c.id FROM customers AS C, items AS i WHERE i.seller_id=c.id
* Outer joins:
Inner join: when we have to column(from different table) and we want to match them together.
[ customers.id=items.seller_id => both should have values. ]
Outer joins:
example: I want to list the customers.name even if they are not selling or list even items, that are not getting sold by any customers now.
SELECT customers.name, items.name FROM customers LEFT OUTER JOIN items ON customers.id=items.seller_id;
[ will list coustomers even if its not there in items table. (because customers is in the left side of LEFT OUTER JOIN)
To include all the rows from the table in LEFT.
The other one:
SELECT customers.name, items.name FROM customers RIGHT OUTER JOIN items ON customers.id=seller_id;
*) UNION:
SELECT name,cost,bids FROM items WHERE bids > 190 OR cost>1000 ORDER BY cost;
But if its become more complex:
SELECT name,cost,bids FROM items WHERE bids>190
UNION
SELECT name,cost,bids FROM items WHERE cost>1000 ORDER BY cost;
taking multiple queries & getting them into one result set. (UNION):
for every UNION column's need to be same.
By default is removes the duplicate entries.
If you don't want to remove duplicate then use UNION ALL in place of UNION
* Fully-text searching:
ALTER TABLE items ADD FULLTEXT(name);
DESC items; -> it you see KEY column for name row we have an INDEX having MUL.
SELECT name,cost FROM items WHERE MATCH(name) AGAINST('baby');
[no regexp or wild card over here. ]
IT do the ranking too & faster.
SELECT name,cost FROM items WHERE MATCH(name) AGAINST('+baby -coat' IN BOOLEAN MODE)
################################################
*) INSERT:
INSERT INTO items(id,name,cost,seller_id,bids) VALUES('102','fish','10','1',0');
*) Multiple Row Insert:
INSERT INTO items(id,name,cost,seller_id,bids)
VALUES
('103','apple','1','1','0'),
('104','shoe','ro','1','0'),
('105','ring','100','1','0');
INSERT INTO items(id,name,cost,seller_id,bids) SELECT id,name,cost,seller_id,bids FROM AnotherTable;
*) UPDATE:
(Best to have LIMIT too) for to be safe.
UPDATE items SET name='applecake' WHERE id=103;
UPDATE items SET name='bananna',cost='2' WHERE id=103 LIMIT 1;
UPDATE user SET Password=PASSWORD('new-password') WHERE User='root';
DELETE FROM items WHERE id=103 LIMIT 1;
*) use PRIMARY KEY in UPDATE or DELETE to be safe.
#####################################
CREATE TABLE:
CREATE TABLE user(
id int,
Username varchar(30),
Password varchar(20),
PRIMARY KEY(id)
);
*)
CREATE TABLE classics(
auther varchar(128),
title varchar(128),
category varchar(16),
year smallINT,
isbn char(13),
INDEX (auther(20)),
INDEX (title(20)),
INDEX (category(4)),
INDEX (year),
PRIMARY KEY (isbn)
) ENGINE MyISAM;
#####################################
*) ALTER/DROP/RENAME Table:
ALTER TABLE user ADD address varchar(30);
ALTER TABLE user DROP column address;
*) TO DROP A TABLE:
DROP TABLE user_old1;
*) Too Rename A TABLE:
RENAME TABLE user1 to users;
###########################################
*) views:
- temporary table;
- don't carry any own data, its a tempory table created by other tables;
CREATE VIEW mostbids AS
SELECT id,name,bids FROM items ORDER BY bids DESC LIMIT 10;
[ top 10 bids, dynamic tables. ]
[NOTE: After AS is query and the view name over here its before AS. ]
CREATE VIEW customers AS SELECT * FROM user;
[ Over here if you delete some thing from this view table, then it will update as original table too. like sym link? ]
CREATE VIEW address AS SELECT CONCAT(city,',',state) AS fulladdress FROM users;
[ view don't take any memory. ]
#################################################
Few more topics:
trigger, cursors, store procedures.
##################################################
No comments:
Post a Comment