Basic sql queries pdf download


















Today, SQL is a universal language of data. It is used in practically all technologies that process data. If there's no matching row, NULL s are returned as values from the second table. If there's no matching row, NULL s are returned as values from the left table. There are two syntaxes available. It computes summaries aggregates for each unique combination of values.

The string length is from 0 - Variable-length string similar to CHAR , but with a length from 0 to TEXT size. Holds a string of a maximum of 65, bytes. Holds a string of a maximum of characters. Holds a string with a maximum length of 16,, characters. Holds a string with a maximum length of 4,,, characters. Similar to CHAR but stores binary byte strings. BLOB size. For holding blobs of up to 65, bytes. Has a max length of bytes.

Holds blobs of up to 16,, bytes. Holds blobs of size up to 4,,, bytes. ENUM val1,val2,…. A string object that can have only one value is chosen from a list of possible values of up to values in an ENUM list. If the value inserted is not in the list, a blank value will be inserted. SET val1,val2,…. A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list.

BIT size. A bit-value type. The size parameter can hold a value from 1 to The default value for size is 1. INT size. A medium integer with a signed range of to , and an unsigned range from 0 to A very small integer. The signed range is from to The unsigned range is from 0 to A small integer. A medium integer. A large integer. FLOAT p. A floating-point value.

This makes the storage of values more efficient. A floating-point number value where the total digits are set by the size parameter, and the number of digits after the decimal point is set by the d parameter. An exact fixed-point number with the total number of digits set by the size parameters, and the total number of digits after the decimal point set by the d parameter. For size, the maximum number is 65 and the default is 10, while for d, the maximum number is 30 and the default being TIME fsp.

The supported range is from ' ' to ' '. A year in four-digit format with the range as - to Add a new column to an existing table. Renames a table or column with an alias value that only exists for the duration of the query.

Adds a constraint that limits the value which can be added to a column. Creates a new database. Creates a new table. Set the default value for a column.

Delete values from a table. Deletes a column from a table. Deletes a complete database along with all the tables and data inside. Removes a default value for a column. Delete a table from a database. Specifies which table to select or delete data from. Overall, it is a great book on SQL and database management and we highly recommend it to all newbie and advanced database developers.

Download full-text PDF. This is perhaps the most widely used SQL query. If you want to display all the attributes from a particular table, this is the right query to use: 3. Query for Creating a View A view is a tailored table that is formed as a result of a query. Query for Retrieving a View The standard syntax of selecting attributes from a table is applicable to views as well.

Query to Display User Tables A user-defined table is a representation of defined information in a table, and they can be used as arguments for procedures or user-defined functions. Query to Display Primary Keys A primary key uniquely identifies all values within a table. Displaying Foreign Keys Foreign keys link one table to another — they are attributes in one table which refer to the primary key of another table. Displaying Internal Tables Internal tables are formed as a by-product of a user-action and are usually not accessible.

Displaying a List of Procedures A stored procedure is a group of SQL queries that logically form a single unit and perform a particular task. Thus, using the following query you can keep track of them Swapping the Values of Two Columns in a table In this and subsequent examples, we will use a common company database including several tables which are easily visualized.

We can easily fix this problem with the following SQL statement: Returning a Column of Unique Values Now, suppose that our data entry operator added the same Customers to the Customers table more than once by mistake. In this example we will return the top 25 from our Customers table: Between Monday and Tuesday Today is Wednesday, and we arrive at work and discover that our new data entry clerk in training has entered all new orders incorrectly on Monday and Tuesday.

The Between clause makes the task a breeze: Finding the Intersection of Two Tables Undoubtedly the whole reason that a relational database exists in the first place is to find matching records in two tables!

Making Column Labels More Friendly Aliasing column labels give us the convenience of renaming a column label to something more readable. Always and Everywhere! In the following example, we will return records from the Orders table where the idea is to get a list of high volume orders for a given item, in this case for customers who ordered more than 50 of the product: Here we can simply add the comment delimiter to deactivate it momentarily: Here, we are going to create the DB as a container for our Customers and Orders tables used in the previous ten examples above: Sql Queries Pdf Also supplied by most hosting services is phpMyAdmin you can also install it anywhere you want, as it's open source and free.

This tool will allow you to view all the MySQL database, tables, and entries, as well as perform SQL queries remotely through a web browser. It's easy-to-use interface will allow you to do many common MySQL tasks quickly and easily, saving you many beginner headaches and helping you understand what's going on in a more visual manner. If you already have that base covered feel free to skip on to the next lesson.

Type the keyword "cmd" into the text field and press Enter to launch Window's command line interface. The most popular options include:. MySQL Administrator This tool comes from the creators of MySQL, so you can be assured they have a solid understanding of database optimization and stability for power users.

A brief overview of their product Navicat Admin can be found on their website. Very few special characters and symbols are required to create a MySQL query, and most queries consist entirely of English words! Because this tutorial focuses on the combination of MySQL and PHP, most of the examples are ready for you to copy, paste, and run on your web server. This helps draw them out from the rest of the code and makes them much easier to read.

Capitalizing them allows you to tell from a quick glance that this query selects data from a table. Do not worry if it takes you more than a week to finish this tutorial. If you take the time to progress slowly, you will be much more well-informed about the MySQL database system than if you rushed through it in one sitting.

Rather a MySQL database is a way of organizing a group of tables. If you were going to create a bunch of different tables that shared a common theme, you would group them into one database to make the management process easier. Create a database and assign a new user to this database.

For all of our beginning examples we will be using the following information:. Server - localhost. Database - test. Table - example.

Username - admin. Password - 1admin Note: The table may change in the advanced lessons, but everything else will remain the same! The server is the name of the server we want to connect to. Because all of our scripts are going to be placed on the server where MySQL is located the correct address is localhost. If the MySQL server was on a different machine from where the script was running, then you would need to enter the correct url ask your web host for specifics on this.

Your database, table, username, and password do not have to match ours. If you choose a different set of login information, remember to insert your own information when copying the scripts in this tutorial.

Status Check So far, you should have created a new database and assigned a user to it. You should not have created a table yet. If you are up-to-date, then continue the tutorial.

We will be making our first table in an upcoming lesson. This is done with the MySQL connect function. MySQL localhost If you've been around the internet a while, you'll know that IP addresses are used as identifiers for computers and web servers. In this example of a connection script, we assume that the MySQL service is running on the same machine as the script.

Please contact your web host for more details if localhost does not work. Server, username, and password. In our example above these arguments were:. Password - 1admin The "or die mysql Double-check your username, password, or server if you receive this error. Choosing the Working Database After establishing a MySQL connection with the code above, you then need to choose which database you will be using with this connection.

If you are up-to-date then continue the tutorial. We will be making our first table in the next lesson. In MySQL and other database systems, the goal is to store information in an orderly fashion. The table gets this done by making the table up of columns and rows.

The columns specify what the data is going to be, while the rows contain the actual data. Below is how you could imagine a MySQL table. This table has three categories, or "columns", of data: Name, Age, and Weight. This table has four entries, or in other words, four rows. Create Table MySQL Before you can enter data rows into a table, you must first define what kinds of data will be stored columns.

We are now going to design a MySQL query to summon our table from database land. In future lessons we will be using this table, so be sure to enter this query correctly! That's a lot of code all at once! Let's get down in the dirt and figure this stuff out.

We will be going through the code line by line. The two capitalized words are reserved MySQL keywords. Clear names will ensure that you will know what the table is about when revisiting it a year after you make it. The column "id" is not something that we need to worry about after we create this table, as it is all automatically calculated within MySQL. INT - This stands for integer or whole number.

This means that no two ids can be the same, or else we will run into trouble. This is why we made "id" an auto-incrementing counter in the previous line of code. It's "variable" because it can adjust its size to store as little as 0 characters and up to a specified maximum number of characters. We will most likely only be using this name column to store characters A-Z, a-z. The number inside the parentheses sets the maximum number of characters.

In this case, the max is Notice that there are no parentheses following "INT". MySQL already knows what to do with an integer. The possible integer values that can be stored in an "INT" are -2,,, to 2,,,, which is more than enough to store someone's age! Your Homework Using the MySQL administration tool that your web host has, check to see if the table was created correctly. Afterwards, try creating a few of your own, with PHP or with a MySQL administration tool, to be sure that you have gotten the hang of it.

When inserting data it is important to remember the exact names and types of the table's columns. If you try to place a word essay into a column that only accepts integers of size three, you will end up with a nasty error!

Inserting Data Into Your Table Now that you have created your table, let's put some data into that puppy! This code is much simpler to understand than the create table code, as will be most of the MySQL queries you will learn in the rest of this tutorial. Once again, we will cover the code line by line. The name of the table we specified to insert data into was "example". Here we enter the name Timmy Mellowman for "name", and 23 for "age".



0コメント

  • 1000 / 1000