This article was published as part of the Data Science Blogathon
– Basic knowledge of any database.
– Basic coding knowledge.
Are you ready to improve your coding skills and master databases?? Excellent!! Yes … you are on the right track here.
Introduction
SQL stands for structured query language. SQL is a widely used language for working with data. All organizations have data and store at least some of the data in a database.
SQL is the language of databases; learning SQL allows you to use them effectively.
MySQL it's a popular relational database, open source that you can use to create all kinds of web databases, namely, from simple, cataloging basic information such as book recommendations to the most complex data stores and hosting hundreds of thousands of records in. Learning MySQL is a great step for those who already know PHP or the Perl language. In this case, you can create websites that can interact with a MySQL database in real time and display categorized and searchable records (recovered) to users.
SQL allows you to view data, analyze data and perform calculations with data. With the help of SQL, you can change the data stored in a database or change the way the database works. SQL also allows you to set rules on how data can be changed and who by.
Agenda
1) How to download and configure SQL?
2) Create the database and connect it
3) SQL statements
4. Conclution
How to download and configure SQL?
You will need two pieces of software: (1) SQL Server itself (2) SQL Server Management Studio (SSMS).
Microsoft provides both online for free, as a single package to download and install too. SQL Server is the software that handles any SQL query you run, but it doesn't actually show the query results nor does it appear on the screen. SSMS is the software that will actually see the user interface. Here you can write the queries you want to test,
and this is what the results show.
Below is the image showing what SSMS looks like after installation: –
Image source: https://www.mssqltips.com/tipimages2/5174_Capture7.jpg
Now, What exactly download?
Microsoft offers several “editions” different from SQL Server online. Basically, SQL Server Express is the free one on which this book is based. In the Express edition, Microsoft also offers several different download "packages", for instance, Advanced, with tools, etc. SQL Server Express with Advanced Services including SSMS.
Where will you download it from?
Where to download it You can download a free version of the above from the Microsoft website, by clicking on the
download link on the web page below (if you enter “descargar SQL Server” and Google, this page should appear near the top of your results): –
https://www.microsoft.com/en-in/sql-server/sql-server-downloads
When you click the link above, the Microsoft site will ask you for a message to sign in or sign up for an account. Once i have done this, will provide you with a selection of SQL Server Express packages to download. If you sign up for a new Microsoft account and then it doesn't take you directly to the download options, go back to the previous web page, then click on the download link and log in there with your newly registered account details. Then you will be in the
correct page to choose your download package.
Below is the link where you can see the documentation on how to download and install SSMS: –
Once you have selected the appropriate package, Click the button “Continue” at the bottom of the page. This will take you to a page that starts the download. The download is just a folder, not a single file. If your browser gives you the option to save the download, save it anywhere you want, since you know where it is.
For instance, save settings to your desktop for easy download access. If you can't choose where to save it, it will almost certainly be downloaded to your computer's downloads folder, generally “C: Users (Your username) downloads”.
Downloading may take some time, but it will continue on its own, so at this point, you can leave the process to finish the download.
Creating the database and connecting it
After successfully installing SSMS, we have to create the database and we will see how to connect it to the SQL server.
To configure or create a new database use this line:
CREATE DATABASE db_name;
Now to see all your databases, we will use the following command:
show databases;
To start working with MySQL, you will need to establish an active SSH session on your SQL server. MySQL connection.
mysql -u root -p
To get rid of a useless database, just type the command as follows:
DROP DATABASE db_name;
This is how you can write any query here in SSMS: –
Image source: https://www.essentialsql.com/wp-content/uploads/2014/08/SSMS-Run-Query.png
SQL statements
The following diagrams show all the necessary declarations to implement Query.
Then, there are four types of SQL statements:
- Language of definition of data (DDL) Declarations- DDL updates / change the table structure, how to create a new table, delete a table, alter a table, etc.
- Data manipulation language (DML) Declarations: DML commands are used to modify or update the database. You are responsible for all forms of any changes to the database.
- Transaction control language (TCL) Declarations: TCL commands can only be used with DML commands like “INSERT, DELETE and UPDATE “ only.
- Data control language (DCL) Declarations: DCL commands are used to grant authority or permission and regain authority by revoking from any database user at any time.
Let's first build the table to implement the above SQL statements: –
Work with tables
Tables are the key element of MySQL databases, as they allow you to store all the information in organized rows. Each row consists of columns that present a specific data type. You have a lot
options for customization using the following commands.
The basic syntax for creating a table:
CREATE TABLE [IF NOT EXISTS] table_name( column_list );
The following code fragment is a table with the characteristics of a list of movies that we want to organize by different
attributes:
CREATE TABLE movies( title VARCHAR(100), year VARCHAR(100), director VARCHAR(50), genre VARCHAR(20), rating VARCHAR(100) );
To see our table:
Use the following commands to get more information about the tables stored in your database.
show tables; #call a list of all tables associated with a database.
DESCRIBE table_name; #see the columns of your table.
DESCRIBE table_name column_name; #review the information of the column in your table
How to delete a table?
To get rid of the table, you need to specify the table name in the following command:
DROP TABLE table_name;
Work with table columns
Use columns to store similar information that share the same attribute (for instance, names of film directors).
Columns are defined by different types of storage:
– CHAR
– VARCHAR
– TEXT
– BLOB
– EUT
Specify what type of information you want to retrieve from a certain row.
When designing columns for your database, your goal is to select the optimal length to avoid
wasted space and maximizes performance.
Below are the SQL commands for working with tables.
1) If you want to add any new column in particular table, type below command:
ALTER TABLE table ADD [COLUMN] column_name;
2) Let's say you have an unusual column that is not needed, then, you can drop or drop that particular table using the following command:
ALTER TABLE table_name
DROP [COLUMN] column_name;
3) Now add a particular row or record to the table using the following code:
INSERT INTO table_name (field1, field2, ...) VALUES (value1, value2, ...);
4) Now, if we want to retrieve specific data from the columns, we will write:
SELECT value1, value2 FROM table_name;
5) Do you want to delete a record that was entered by mistake?? Yes, you can do this. Here we have used the where clause that follows the condition that must be met to retrieve a certain record: –
DELETE FROM movies WHERE budget="1";
6) Similarly, you can use different clauses to update all or specified rows in your table.
To update all rows:
UPDATE table_name SET column_1 = value_1 WHERE budget="5";
7) You can modify any existing column with the following code snippet:
ALTER TABLE movies MODIFY COLUMN number INT(3);
8) You can combine two or more columns together with CONCAT function easily using the following code:
SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;
If you are storing important data, has four main concerns:
> Data storage should be quick and easy because it is supposed to be done frequently.
> The storage medium must be reliable. Therefore, you won't want to come back later and find some are missing (the all) your data.
> Data recovery should be quick and easy, regardless of how many items you store there.
> You have required an easy way to separate the exact information you want now
of the tons of data you don't want right now.
Conclution
We have seen the initial steps required to configure the software, etc. I hope you liked this article. We will see more implementations in the next article. Thanks!
The media shown in this article is not the property of DataPeaker and is used at the author's discretion.
