(source: Codecademy, https://www.codecademy.com)
SQL, Structured Query Language
What is a Relational Database?
A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables.
What is a Relational Database Management System (RDBMS)?
A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.
What is SQL?
SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret.
Many RDBMSs use SQL (and variations of SQL) to access the data in tables. For example,
Popular Relational Database Management Systems
SQL syntax may differ slightly depending on which RDBMS you are using. Here is abrief description of popular RDBMSs:
- MySQL is the most popular open source SQL database. It is typically used for web application development, and often accessed using PHP.
- The main advantages of MySQL are that it is easy to use, inexpensive, reliable (has been around since 1995), and has a large community of developers who can help answer questions.
- Some of the disadvantages are that it has been known to suffer from poor performance when scaling, open source development has lagged since Oracle has taken control of MySQL, and it does not include some advanced features that developers may be used to.
- PostgreSQL is an open source SQL database that is not controlled by any corporation. It is typically used for web application development.
- PostgreSQL shares many of the same advantages of MySQL. It is easy to use, inexpensive, reliable and has a large community of developers. It also provides some additional features such as foreign key support without requiring complex configuration.
- The main disadvantage of PostgreSQL is that it is slower in performance than other databases such as MySQL. It is also less popular than MySQL which makes it harder to come by hosts or service providers that offer managed PostgreSQL instances.
- Oracle Corporation owns Oracle Database, and the code is not open sourced.
- Oracle DB is for large applications, particularly in the banking industry. Most of the world’s top banks run Oracle applications because Oracle offers a powerful combination of technology and comprehensive, pre-integrated business applications, including essential functionality built specifically for banks.
- The main disadvantage of using Oracle is that it is not free to use like its open source competitors and can be quite expensive.
- Microsoft owns SQL Server. Like Oracle DB, the code is close sourced.
- Large enterprise applications mostly use SQL Server.
- Microsoft offers a free entry-level version called Express but can become very expensive as you scale your application.
- SQLite is a popular open source SQL database. It can store an entire database in a single file. One of the most significant advantages this provides is that all of the data can be stored locally without having to connect your database to a server.
- SQLite is a popular choice for databases in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. The SQL courses on Codecademy use SQLite.
SQL Pracitce
SELECT * FROM celebs;
table name : clebs
column : id, name, age
row : record in a table
data type
- NTEGER, a positive or negative whole number
- TEXT, a text string
- DATE, the date formatted as YYYY-MM-DD
- REAL, a decimal value
INSERT INTO : adds the specified row or rows
ALTER TABLE : make the specified changes
ADD COLUMN : add a new column to a table
UPDATE : edits a row in the table
SET : indicates the column to edit
WHERE : indicates which row(s) to update with the new column value
DELETE FROM : lets you delete rows from a table
WHERE : lets you select which rows you want to delete
IS NULL : a condition in SQL that returns true when the value is NULL and false otherwise
Constraints
- add information about how a column can be used
- can be used to tell the database to reject inserted data that does not adhere to a certain restriction
PRIMARY KEY : can be used to uniquely identify the row
Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.
UNIQUE : have a different value for every row
This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
NOT NULL : must have a value
Attempts to insert arow without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.
DEFAULT : take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column
SQL Pracitce 2
'SQL' 카테고리의 다른 글
03 SQL: Intermediate_Aggregate Functions Projects and Applications (0) | 2023.02.12 |
---|---|
02 SQL: Basics_Writing Queries (3) (0) | 2023.02.11 |
02 SQL: Basics_Writing Queries (2) (0) | 2023.02.10 |
02 SQL: Basics_Writing Queries (1) (0) | 2023.02.05 |
댓글