SQL Structured Query Language and basic Mysql operations, Structured Query Language SQL

SQL Structured Query Language and basic Mysql operations, Structured Query Language SQL

Structured SQL Query Language
Data operation (Management) language (DML, DataManipulationLanguage) (DQL + DML)
DQL query: obtain data.
DML management: add, delete, and modify data.

The Data Definition Language (DDL, DataDefinitionLanguage) defines the format of the stored data.

Database Control Language (DCL) is used to operate database software services.

SQL = DDL, DML (DQL + DML), DCL

** Database operation: ** DDL Create a database

Create database [if not exists] database name [database options]; eg: create database liguodong; create database '000000'; Special string create database 'create'; keyword set names GBK; create database 'chengdu ';

Database Name: It can be any character (the directory can be created successfully), but special characters need to be enclosed by Reverse quotation marks. The case sensitivity of the identifier is different from that of the operating system.
If not exists indicates creation when the database does not exist.
In the database option, you can set the character set (character set utf8) and collate utf8_general_ci) of the database ).

Note:
The statement must use the statement Terminator.;

Naming rules for Identifiers (databases:
The case sensitivity depends on the current operating system)
See the name and meaning. Underline is recommended.

Characters of the identifier:
Use any character, number, symbol or even Chinese. However, some special combinations, such as pure numeric combinations and special symbols, including mysql being internal keywords, should be wrapped using the identifier qualifier.
Qualifier: backquotes.
It can be in Chinese, but is not recommended. (Correct character set encoding)set names GBK;

Every time we create a database, a directory is formed in the mysql data directory. The directory name is the database name (if it is a special character, it is saved in the form of Encoding ). A file exists in the directory to save the options of the database.db.opt

Query a database

Query existing databases:

show databases [like 'pattern']show databases;

like patternDisplays the naming rules that match. None indicates all databases.
Query the statement for creating a database:show create database db_name;

Note:Not only users can create databases, but also mysql maintains its own databases internally.

Delete Database

Drop database [if exists] db_name; Use drop database test with caution;

If existsIndicates that the database exists before it is deleted.
When a database is deleted, the directories related to the database and their contents are also deleted.

Modify Database

Alter database db_name [modify command] alter database test character set gbk;

Command: modify Database attributes.

Modify Name:
Method 1: You can directly modify the directory name. (Not applicable)
Method 2: export the database content, create a new database, import the content, and delete the old database.

Table operations

A database is a table container.
A table must have data in a database.
You can use.The syntax indicates the database to which it belongs. Eg: database. table database. table. If any identifier or special character appears, enclose it in reverse quotation marks. Different identifiers must be packaged separately.
During table operations, the current default database is specified.
use db_name;
Note:If the default database is selected, the default behavior will only be affected. Any database can be operated.

Create a table

Create table [if not exists] tbl_name (column definition) [table options] create table [if not exists] tbl_name like old_tbl_name; create table [if not exists] tbl_name select statement; eg: create table liguodong. classroom (stu_id varchar (20), stu_name varchar (20), stu_data date );

Each time a table is created, the corresponding file is created in the data directory to save the table information.
First, analyze the object data to be saved, what attributes are available, and how these attributes should be saved.
Example: Student Information
Student ID, name, date of birth
Column definition:
Column name: column data type [column attributes (constraints)]

View table

Show tables [from db_name] [like 'pattern']; if no database name exists, the current database is used. If no like exists, all tables are obtained. Eg: use liguodong; show tables from liguodong;

Table name prefix:
To differentiateSame logical table nameOfDifferent applicationsTo add a prefix to the logical table name to form a real table name.

/* Student management system */create table info_student (stu_name varchar (20), stu_no varchar (20 )); /* online examination system */create table exam_student (stu_name varchar (20), stu_no varchar (20 ));

Show tables like 'exam _ % '; % is a wildcard that represents a combination of any number of characters.

show create table exam_student;

When there is a large amount of data, you can use \ G as the statement Terminator.

describe liguodong.exam_student;

The database corresponds to a directory, while the database content correspondsDirectory content, file.

Delete table

Drop table [if exists] tbl_name; eg: drop table exam_student; the table does not exist and cannot be deleted. An error is reported. Drop table if exists exam_student; no error is reported because a judgment has been made.

Modify Table

Rename the table name:rename table tbl_name to new_tbl_name;. YesRename multiple tables at the same time, or even cross-database.
Modify Table Structure:alter table. You can modify table options and column definitions.

The duplicate name is equivalent to the cut operation eg: rename table classroom to stu_class. Multiple table names can be modified at the same time. Rename table classroom to stu_class, info_student to exam_user; supports cross-database rename. Rename table exam_user to '123 '. user; rename the database (the database does not support rename) to create a new database. All the tables in the database are rename to the new database and the old database is deleted.

Modify the column definition:

Add can add multiple columns at the same time, and use parentheses to enclose the definitions of multiple columns. Modify change rename drop Delete

Modify Table Structure:

Alter table exam [add | drop | change | modify] eg: add: alter table info_student add age int; desc info_student; Delete: alter table info_student drop age; desc info_student; modify: alter table info_student modify stu_no varchar (40); desc info_student; rename: alter table info_student change age stu_age int; desc info_student;

Modify Table options

alter table info_student character set gbk;