This is the third day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Hello, I’m [Bean Dried peanut], this time I brought a full set of SQL, mysql study notes, will be updated later ~

SQL is a very common database language, but for beginners, often can not learn systematically, so I brought a complete set of notes

Today I’m going to share my notes from week 1. In this module, you will learn about relational databases, cloud databases, DDL and DML, SQL operations

This is my study notes to learn SQL /mysql from scratch, and will be updated later, welcome to follow ~

Note: This is a study note for coursera course Databases and SQL for Data Science with Python

In this module, you will explore the fundamental concepts behind databases, tables, and the relationships between them. You will then create an instance of a database, discover SQL statements that allow you to create and manipulate tables, and then practice them on your own live database.

Learning goals

  • Describes basic relational database concepts, including tables, primary keys, and foreign keys.
  • Create database instances on the cloud
  • Distinguish between data definition languages and data processing languages.
  • Explain the syntax of the CREATE TABLE statement.
  • Explain the syntax for ALTER, DROP, and TRUNCATE statements.
  • Write and execute CREATE TABLE, ALTER, DROP, and TRUNCATE statements on a live database.

1. Relational database concept

A relational database is a database built on the basis of a relational database model. It processes the data in the database by means of concepts and methods such as set algebra. At the same time, it is also a formalized descriptive table organized into a group, the essence of which is to load the special collection of data items. Data in these tables can be accessed or reconvened in many different ways without the need to reorganize the database tables.

The definition of a relational database creates a table of metadata or a formal description of tables, columns, scopes, and constraints. Each table (sometimes called a relationship) contains one or more types of data represented by columns. Each row contains a unique data entity, which is the type defined by the column.

When creating a relational database, you can define the range of possible values for a data column and further constraints that may apply to that data value. The SQL language is the standard user and application interface to the relational database. The advantage is that it is easy to scale, and after the initial database creation, a new category of data can be added without modifying all existing applications.

Mainstream relational databases include Oracle, DB2, SQLServer, Sybase, mysql and so on.

Create database instances on the cloud

A cloud database is a database service built and accessed through a cloud platform. It has a lot of additional flexibility with traditional database cloud computing.

Some of the advantages of using the Cloud database include: ease of use; users can access the Cloud database virtually from anywhere using the vendor’s API or network interface, or from your own application (whether on the Cloud or Remote). Scalability. Cloud databases can expand and shrink the capacity of their storage and computing runtimes to accommodate changing requirements and usage, so organizations pay only for what they actually use. Disaster recovery. In the event of natural disasters or device failures or power outages, backup on remote servers ensures that data is secured on the Cloud in a geographically distributed area.

To run a database in the Cloud, you must first configure an instance of the database service on the Cloud platform of your choice. Database as a Service (DBaaS) instances or DBaaS provide users with access to database resources in the cloud without setting up basic hardware, installing database software, and managing the database.

The database service instance will store the data in the related tables. Once the data is loaded into the database instance, you can use the Web interface or API connected to the database instance application. Once connected, your application can send SQL queries across database instances. A database instance parses SQL statements to operate on data and objects in the database. Returns any retrieved data as a result set.

You can also choose to create multiple groups of credentials for different applications and users. After creating a set of service credentials, you can treat them as adjacent code segments. Credentials include the details necessary to establish a connection to the database and include the following; Database name and port number, host name, that is, the name of the server on the server cloud where the database instance resides, user name, that is, the ID and password used by the user for connection. Note that your user name is also the schema name in which the table will be created by default.

It is suggested to establish a cloud database directly in ali Cloud server.

SQL statement types (DDL and DML)

SQL statements fall into two different categories: data Definition Language statements and data Manipulation Language statements.

Data Definition Language (OR DDL) statements are used to define, change, or drop objects such as database tables. Common DDL statement types include CREATE, ALTER, TRUNCATE, and DROP. CREATE: to CREATE a table and define its columns; ALTER: for changing tables, including adding and dropping columns and changing their data types; TRUNCATE: Deletes data in a table but does not delete the table itself. DROP: Used to DROP a table.

Data Manipulation language (OR DML) statements are used to read and modify data in a table. These are sometimes called CRUD operations, which create, read, update, and delete rows in a table. Common DML statement types include INSERT, SELECT, UPDATE, and DELETE. INSERT: Used to INSERT one or more rows of data into a table; SELECT: reads or selects a row from a table; Update: Edit one or more rows in a table; And DELETE: DELETE one or more rows of data from a table. There you have it: DDL or data Definition language statements are used to define or change objects in a database such as a table. DML or Data Manipulation language statements are used to manipulate or use data in a table.

CREATE TABLE statement

Blog.csdn.net/weixin_4437…

1. Create table command

Note: Before using the create table command, you must use the use command to select the database where the table resides. The format of the create table command is as follows:

create table tablename( col_name1 type auto_increment, col_name2 type not null|null default val, col_name3 type unique, . , primary key(col_name), index idx_name(col_name1,col_name2,...) , foreign key (col_name) references tablename (col_name)) engine = innoDB | myISAM default charset = character set;Copy the code

Description:

(1) type: Defines the data type of the field. String type (char, varchar) needs to specify the length. Int, biging, tinyint, etc.) and date types (dateTime, timestamp, etc.) only need to specify the type, not the length. The decimal type requires specifying the precision and the number of decimal places. (2) Primary key specifies the primary key of the table. (3) The auto_increment parameter specifies that a field is incrementing. The field type must be int or BIGint to be incrementing. (4) not null | null parameter setting field can take an empty value. (5) Default Parameter setting field default value. (6) The index parameter is used to create an index. Idx_name specifies the index name. This parameter can be omitted. If omitted, the index name is the field name. (7) The foreign key parameter specifies the foreign key. (8) The engine parameter is used to set the engine type. The common ones are innoDB and myISAM. (9) Default charset parameter Specifies the character set used in the table.

2. Example:

(1) Create dept table:

Create table dept(dept_id int primary key AUTO_increment) create table dept(dept_id int primary key auto_increment, Primary key dept_name char(20) not null default ", --dept_name cannot be null, default is empty string phone char(20));Copy the code

(2) Create emP table:

create table emp( emp_id int auto_increment, emp_name char(20) not null default '', birth datetime not null, phone char(20) not null, addr varchar(200) not null, dept_id int not null default 0, index idx_emp_name(emp_name), index idx_emp_name_birth(emp_name,birth), primary key(emp_id), Foreign Key (dept_ID) REFERENCES Dept (dept_id)) engine=innoDB default charset= UTF8;Copy the code

5. The ALTER, DROP, Truncate table

1.alter

Link: blog.csdn.net/sinat_36053…

1: deletes a column

ALTER TABLE [TABLE name] DROP [column name] 2: Add a column

ALTER TABLE table1 ADD transactor varchar(10) not Null; 3: Renames the column

ALTER TABLE ALTER TABLE ALTER TABLE

You are advised to use rename. Change is prone to errors

ALTER TABLE table_name RENAME TO new_table_name;

4: Modify table fields

Alter table alter table name change field name Field name Field type [whether non-null is allowed]; Alter table Table name modify Field name Field type [whether non-null is allowed]; 5: Queries the field information of the table

Desc table name; 6: Creates an index

ALTER TABLE tbl_name ADD INDEX index_name (column list); 7: deletes an index

ALTER TABLE tbl_name DROP INDEX index_name (column list); 8: Change the table name

ALTER TABLE tbl_name rename new_tbl_name;

Change the type

[C:\Users\YUANMU\AppData\ Folder \Typora\ Typora\ user-images] [C:\Users\YUANMU\AppData\ Folder \Typora\ Typora\ user-images \image-20210418182812142.png)]

2.DROP

DROP TABLE table_name;
Copy the code

3.TRUNCATE

Blog.csdn.net/wangkun_j/a…

Truncate clears or truncates a table. It only works on a table. The syntax of TRUNCate is simple. It is followed by a table name, for example, TRUNCate table TBL_NAME or TRUNCate tBL_name.

To execute the TRUNCate statement, you need to have the drop permission on the table. Logically, the TRUNCate TABLE statement is similar to the delete statement that deletes all rows or the drop TABLE statement followed by the CREATE TABLE statement. For high performance, it bypasses DML methods to delete data, so it cannot be rolled back. Although truncate table is similar to DELETE, it is classified as a DDL statement rather than a DML statement.

Truncate is very similar to delete and drop. In fact, these three are very different.

Truncate and DROP are DDL statements that cannot be rolled back after execution. Delete is a DML statement that can be rolled back. Truncate only works on tables; Delete and drop can be applied to tables and views. Truncate clears all rows in a table, but the table structure, constraints, and indexes remain the same. Drop drops the table structure, its dependent constraints, and indexes. Truncate resets the self-value of the table. The delete will not. Truncate does not activate delete triggers related to the table; Can delete. After truncate, the space occupied by tables and indexes is restored to the original size. The delete operation does not reduce the space occupied by the table or index. The DROP statement releases all the space occupied by the table.

From the previous section, we can easily conclude that the TRUNCate statement can be used when the data in the table is not needed at all. If you want to delete part of the data using delete, be careful to include a WHERE clause; If you want to drop a table, use drop; Truncate (truncate) truncate (truncate) truncate (truncate) truncate (truncate) truncate Delete if it’s related to a transaction or if you want to trigger it. Truncate can be used to truncate and then reinsert the data.

The drop statement releases all the space occupied by the table.

When a table is truncated, the tablespace occupied by the table and index is restored to its original size.

The DELETE operation does not reduce the space occupied by the table or index.

In general, drop > TRUNCate > DELETE

In any case, the TRUNCate table is a high-risk operation, especially in a production environment. The following are some precautions that you should take when using the table.

Truncate cannot be rolled back through binlog. Truncate clears all data and executes quickly. Truncate cannot be used on tables that have foreign key constraint references. The truncate operation requires the drop permission. Do not give the account the drop permission. Before performing truncate, ensure that the data in the following table is backed up.

TRUNCATE TABLE table_name;
Copy the code

6. The Summary & Highlights

  • A database is a data repository that provides the ability to add, modify, and query data.

  • SQL is a language for querying or retrieving data from relational databases.

  • The relational model is the most commonly used data model in a database because it allows data independence.

  • The primary key of a relational table uniquely identifies each tuple or row, preventing data duplication and providing a way to define relationships between tables.

  • SQL statements fall into two categories: Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements.

All see here, not as good as a like oh ~

At the beginning of nuggets, I have a lot of interesting articles, the follow-up will be carried over, welcome to pay attention to oh ~