Tutorial by Examples: sql

Object oriented style Connect to Server $conn = new mysqli("localhost","my_user","my_password"); Set the default database: $conn->select_db("my_db"); Connect to Database $conn = new mysqli("localhost","my_user","my_password&q...

The query function takes a valid SQL string and executes it directly against the database connection $conn Object oriented style $result = $conn->query("SELECT * FROM `people`"); Procedural style $result = mysqli_query($conn, "SELECT * FROM `people`"); CAUTION A ...

PHP makes it easy to get data from your results and loop over it using a while statement. When it fails to get the next row, it returns false, and your loop ends. These examples work with mysqli_fetch_assoc - Associative array with column names as keys mysqli_fetch_object - stdClass object with ...

Query caching is a Rails feature that caches the result set returned by each query. If Rails encounters the same query again for that request, it will use the cached result set as opposed to running the query against the database again. For example: class ProductsController < ApplicationControl...

To connect to MySQL you need to use the MySQL Connector/J driver. You can download it from http://dev.mysql.com/downloads/connector/j/ or you can use Maven: <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version...

java.util.Date to java.sql.Date conversion is usually necessary when a Date object needs to be written in a database. java.sql.Date is a wrapper around millisecond value and is used by JDBC to identify an SQL DATE type In the below example, we use the java.util.Date() constructor, that creates a D...

The IQueryable and IQueryable<T> interfaces allows developers to translate a LINQ query (a 'language-integrated' query) to a specific datasource, for example a relational database. Take this LINQ query written in C#: var query = from book in books where book.Author == "Steph...

The following command imports CSV files into a MySQL table with the same columns while respecting CSV quoting and escaping rules. load data infile '/tmp/file.csv' into table my_table fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n' ignore 1...

Another use of RODBC is in connecting with SQL Server Management Database. We need to specify the 'Driver' i.e. SQL Server here, the database name "Atilla" and then use the sqlQuery to extract either the full table or a fraction of it. library(RODBC) cn <- odbcDriverConnect(connect...

Please read Preventing SQL injection with Parametrized Queries for a complete discussion of why prepared statements help you secure your SQL statements from SQL Injection attacks The $conn variable here is a MySQLi object. See MySQLi connect example for more details. For both examples, we assume t...

For creating new user, We need to follow simple steps as below : Step 1: Login to MySQL as root $ mysql -u root -p Step 2 : We will see mysql command prompt mysql> CREATE USER 'my_new_user'@'localhost' IDENTIFIED BY 'test_password'; Here, We have successfully created new user, But this u...

Assuming the call to your web application's login handler looks like this: https://somepage.com/ajax/login.ashx?username=admin&password=123 Now in login.ashx, you read these values: strUserName = getHttpsRequestParameterString("username"); strPassword = getHttpsRequestParameterSt...

// Uses Windows authentication. Replace the Trusted_Connection parameter with // User Id=...;Password=...; to use SQL Server authentication instead. You may // want to find the appropriate connection string for your server. string connectionString = @"Server=myServer\myInstance;Database=myDa...

/*(8)*/ SELECT /*9*/ DISTINCT /*11*/ TOP /*(1)*/ FROM /*(3)*/ JOIN /*(2)*/ ON /*(4)*/ WHERE /*(5)*/ GROUP BY /*(6)*/ WITH {CUBE | ROLLUP} /*(7)*/ HAVING /*(10)*/ ORDER BY /*(11)*/ LIMIT The order in which a query is processed and description of each section. V...

The query attribute on queryset gives you SQL equivalent syntax for your query. >>> queryset = MyModel.objects.all() >>> print(queryset.query) SELECT "myapp_mymodel"."id", ... FROM "myapp_mymodel" Warning: This output should only be used for d...

Suppose we want to query the "cheapest products" from the "top categories". Here is an example of query using Common Table Expressions -- all_sales: just a simple SELECT with all the needed JOINS WITH all_sales AS ( SELECT product.price as product_price, category.id a...

In SQL Server 2016 finally they have introduced Split string function : STRING_SPLIT Parameters: It accepts two parameters String: Is an expression of any character type (i.e. nvarchar, varchar, nchar or char). separator : Is a single character expression of any character type (e.g. nv...

Retrieve the last ID generated by an INSERT query on a table with an AUTO_INCREMENT column. Object-oriented Style $id = $conn->insert_id; Procedural Style $id = mysqli_insert_id($conn); Returns zero if there was no previous query on the connection or if the query did not update an AUTO...

PL/SQL stands for Procedural Language extensions to SQL. PL/SQL is available only as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side applicati...

Setting Up the database public class DatabaseHelper extends SQLiteOpenHelper { // Database Version private static final int DATABASE_VERSION = 1; // Database Name private static final String DATABASE_NAME = "database_name"; // Table Names private static...