|
- Sintaksisi
- CREATE TABLE table_name
- (Column_name datatype[(size)],
- Column_name datatype[(size)],
- )
- Misol:
- CREATE TABLE books
- (ISBN char(20),
- Title char(50),
- AuthorID Integer,
- Price float)
- 4 ta ustun (maydon)li jadval yaratildi
MB jadvalini yaratish - Jadval CREATE TABLE so’rovi yordamida yaratiladi: Misol: CREATE TABLE friends (
- first_name varchar(25),
- last_name varchar(25),
- gender enum(‘M’, ‘F’),
- grade smallint unsigned,
- hair_color varchar(20),
- email varchar(30),
- screen_name varchar(25),
- phone char(12));
SQL da ma`lumot toifalari - Satr toifalari
- CHAR(n) – Belgilangan uzunlikdagi ma`lumotlar uchun, n ma`lumotdagi simvollar soni. Uning maksimal darajasi 2000 bayt.
- VARCHAR2(n) – O`zgaruvchan uzunlikdagi ma`lumotlar uchun. Maksimal darajasi 4000 bayt.
- Raqamli toifalar
- NUMBER – faqat raqamli ma`lumotlar
- INTEGER – Butun sonlar uchun
- FLOAT – Haqiqiy sonlar uchun
- MONEY – Haqiqiy sonlar ko’rinishida bo’ladi
- Vaqt/sana toifasi
- MB jadvalini o’zgartirish (ALTER TABLE)
- Mavjud jadvalga ustun qo’shish yoki o’chirish.
- ALTER TABLE
- ADD attr datatype;
- yoki
- ALTER TABLE
- DROP COLUMN attr;
- MBdan jadvalni o’chirish (DROP TABLE)
- DROP TABLE
- Misollar:
- CREATE TABLE FoodCart (
- date varchar(10),
- food varchar(20),
- profit float
- );
- ALTER TABLE FoodCart (
- ADD sold int
- );
- ALTER TABLE FoodCart(
- DROP COLUMN profit
- );
- DROP TABLE FoodCart;
SELECT operatori - SELECT (tanlash) SQL tilining eng muxim va ko‘p ishlatiladigan opеratori hisoblanadi. U ma’lumotlar bazasi jadvalidan axborotlarni tanlab olish uchun mo‘ljallangan.
- SELECT opеratori sodda holda quyidagi ko‘rinishda yoziladi.
- SELECT [DISTINCT] <atributlar ro‘yxati>
- FROM <jadvallar ro‘yxati>
- [WHERE <tanlash sharti>]
- [ORDER BY < atributlar ro‘yxati >]
- [GROUP BY < atributlar ro‘yxati >]
- [HAVING <shart> ]
SELECT so’rovi (query) - SELECT so’rovi
- Bir nechta jadvallardan ma’lumotlarni yig’ish
- Format
- SELECT * FROM TableName
- * - barchasi
- SELECT * FROM Authors
- Authors jadvalidan barcha maydonlarni tanlash
- Maydonlarni tanlash
Selectga misollar: Person - 1) SELECT * FROM person WHERE age > 30;
- 2) SELECT weight FROM person WHERE age > 30;
- 3) SELECT distinct weight FROM person WHERE age > 30;
WHERE - Shart asosida tanlash
- SELECT * FROM TableName WHERE shart
- Misol
- SELECT * FROM Authors WHERE YearBorn > 1960
WHERE - Shartlar
- <, >, <=, >=, =, <> va LIKE
- LIKE – shablonli tekshirish uchun
- Search for similar strings
- Wildcard characters * and ?
- * - bir nechta belgilar
- SELECT * FROM Authors WHERE LastName LIKE 'D*'
- LastName ustunidan 'D' bilan boshlanuvchi ixtiyoriy belgilar qatori
WHERE - Shart
- ? – ixtiyoriy bitta belgi
- SELECT * FROM Authors WHERE LastName LIKE '?i*'
- LastName ustunidan ixtiyoriy belgidan boshlanuvchi ikkinchi belgisi 'i' bo’luvchi qator
WHERE - Shart
- Belgilar diapazoni
- SELECT * FROM Authors WHERE LastName LIKE '?[a-i]*'
- Start with any letter, second letter between a and i, followed by any number of characters
Saralash (ORDER BY) - SELECT * FROM TableName ORDER BY field ASC SELECT * FROM TableName ORDER BY field DESC
- field – saralsh uchun foydalaniladigan maydon
- ASC/DESC – o’sish/kamayish tartibida saralash (ascending/descending sort)
- SELECT * FROM Authors ORDER BY LastName ASC
Saralash (ORDER BY) - SELECT * FROM Authors ORDER BY LastName, FirstName
Saralash (ORDER BY) - Kombinatsiya
- SELECT * FROM Titles WHERE Title LIKE '*How to Program' ORDER BY Title ASC
- SELECT * FROM Product WHERE Category=‘Gadgets’
- SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100
- “tanlash” and
- “solishtirish”
- SELECT DISTINCT category
- FROM Product
- SELECT category
- FROM Product
- SELECT Category
- FROM Product
- ORDER BY PName
- SELECT DISTINCT Category
- FROM Product
- ORDER BY Category
- SELECT DISTINCT Category
- FROM Product
- ORDER BY PName
- SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200
- SELECT count(*)
- FROM Product
- WHERE year > 1995
- SELECT avg(price)
- FROM Product
- WHERE maker=“Toyota”
- SQL ko’pgina birlashtirish operatorlarini qo’llab quvvatlaydi:
- sum, count, min, max, avg
- SELECT Count(category)
- FROM Product
- WHERE year > 1995
- SELECT Count(DISTINCT category)
- FROM Product
- WHERE year > 1995
- SELECT Sum(price * quantity)
- FROM Purchase
- WHERE product = ‘bagel’
- SELECT product, Sum(price*quantity) AS TotalSales
- FROM Purchase
- WHERE date > ‘10/1/2005’
- GROUP BY product
- COUNT(attr) -> bo’sh bo’lmagan qatorlar sonini aniqlaydi
- Misol: COUNT(distinct food) from FoodCart;
- SUM(attr) -> ko’rsatilgan maydon qiymatlari yig’indisini aniqlaydi
- Misol: SUM(sold) from FoodCart;
- MAX(attr) -> ko’rsatilgan maydondan eng kattasini aniqlaydi
- Misol: MAX(sold) from FoodCart;
- MIN(attr) -> ko’rsatilgan maydondan eng kichigini aniqlaydi
- Misol: MIN(sold) from FoodCart;
- AVG(attr) -> ko’rsatilgan maydon o’rta arifmetigini aniqlaydi
- Misol: AVG(sold) from FoodCart;
- INSERT INTO R(A1,…., An) VALUES (v1,…., vn)
- INSERT INTO Purchase(buyer, seller, product, store)
- VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’)
- Vazifa: Product jadvaliga Purchase dan barcha prodNames qo’shish
- Product(name, listPrice, category)
- Purchase(prodName, buyerName, price)
INSERT INTO friends VALUES (‘Daffy’, ‘Duck’,‘M’,11,’Brunette’,‘daffy@disney.com’, YellowBill’, ’315-555-1213’); - INSERT INTO friends VALUES (‘Daffy’, ‘Duck’,‘M’,11,’Brunette’,‘daffy@disney.com’, YellowBill’, ’315-555-1213’);
- INSERT into
- VALUES ('value1', 'value2', NULL);
- Misol:
- INSERT into FoodCart
- VALUES (’02/26/08', ‘pizza', 70 );
- FoodCart
- UPDATE PRODUCT
- SET price = price/2
- WHERE Product.name IN
- (SELECT product
- FROM Purchase
- WHERE Date =‘Oct, 25, 1999’);
UPDATE friends SET phone=‘315-555-1234’ WHERE first_name=‘Daffy’ and last_name = ‘Duck’; - UPDATE friends SET phone=‘315-555-1234’ WHERE first_name=‘Daffy’ and last_name = ‘Duck’;
- UPDATE
SET = - WHERE ;
- Misol:
- UPDATE FoodCart SET sold = 349
- WHERE date = ’02/25/08’ AND food = ‘pizza’;
- FoodCart
- DELETE FROM PURCHASE
- WHERE seller = ‘Joe’ AND
- product = ‘Brooklyn Bridge’
DELETE from friends WHERE first_name=‘Daffy’ and last_name=‘Duck’; - DELETE from friends WHERE first_name=‘Daffy’ and last_name=‘Duck’;
- Jadvaldan qatorlarni o’chirish:
- DELETE FROM
- WHERE ;
- Misol:
- DELETE FROM FoodCart
- WHERE food = ‘hotdog’;
- FoodCart
JDBC - Get a Connection to the database.
- Create a Statement using the Connection.
- Execute the Statement with SQL string.
- Use the results.
JDBC JDBC Code - static final String URL = "jdbc:mysql://dbserver/world";
- static final String USER = "student";
- static final String PASSWORD = "secret";
- // 1. Get a Connection to the database.
- Connection connection = DriverManager.getConnection( URL, USER, PASSWORD );
- // 2. Create a Statement
- Statement statement = connection.createStatement();
- // 3. Execute the Statement with SQL command.
- ResultSet rs = statement.executeQuery("SELECT * FROM ...");
- // 4. Use the Result.
- while ( rs.next( ) ) {
- String name = rs.getString("name");
Connecting to a Database in Java (1) - java.sql.Connection is a standard interface for connecting to any database.
- Each database type requires its own jdbc driver that implements this interface.
- MySQL driver mysql-connector-java-5.1.7-bin.jar
- Derby driver: derby.jar or derbyclient.jar
- HSQLDB driver: hsqldb.jar
- DriverManager selects the driver based on URL.
DriverManager returns a Connection - DriverManager
- getConnection( url, user, passwd) : Connection
- <>
- Connection
- createStatement(): Statement
- close( )
- isClosed( ): boolean
- getCatalog( ): String
- url = "jdbc:mysql://hostname/database"
Database URL - String DB_URL = "jdbc:mysql://dbserver:3306/world";
- The format of a database URL is:
- Port is the TCP port number where the database server is listening.
- 3306 is the default port for MySQL
- Use hostname "localhost" for the local machine.
Tavsiya etiladigan adabiyotlar - An Introduction to Network Programming with Java. Java 7 Compatible. Jan Graba. 3rd Ed. Springer. 2013
- Java Network Programming. Elliotte Rusty Harold.-4th ed. Publiseilly Media. 2014.
- https://www.javatpoint.com/java-jdbc
- https://www.tutorialspoint.com/jdbc/index.htm
Do'stlaringiz bilan baham: |
