MySQL Library table operation (FAT Teacher)

Sql
Concept: Structured Query Language (SQL = Structured query Language),
is also a programming language (database query and programming language), can be used for data access and query, update, management of relational database system
PS: SQL between different database systems can not be completely common with each other;

Classification
Can be divided into different languages for different objects of operation
1: Data manipulation (data management) language DML (Management Language)
1): Query data DQL
2): Increase, delete, change DML
2: Data Definition language DDL (Language)--such as the definition of a table
3: Data Control Language DCL (Language)

*************************************************************************************************************** *

Relationships between databases, tables, and data
The table is the carrier of the data, the database is the container of the table
*************************************************************************************************************** *




Database operations
mysql> show databases; --View all the databases
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
+--------------------+

Create a database
Syntax: CREATE DATABASE [if not EXISTS] db_name [data options]
Example: Create database Student_system;
Example: Create database if not exists student_system; --will first determine if the Student_system is already present and will not be created if it exists, avoiding exceptions

Example: C:\windows\system32>mysqladmin-uroot-p Create bbbb--can be created by mysqladmin
*********************************************************************

Naming rules for databases
1: See the name of the idea, the suggestion is to use an underscore way
2: Can use any characters, such as numbers, symbols, Chinese, etc.
Create database chubby;
3: If the name is very special, such as a pure number or a keyword to name, it is necessary to use the qualifier wrapper (the qualifier refers to the anti-quote ");
Create database ' 123456 ';
4: Whether case-sensitive (this current operating system is concerned);
5: Database names can be created with the use of anti-quotes
***********************************************************************

PS: The database is created as a directory, the directory name is the database name, if the database name is a special character, then the file name will be encoded form the table
The directory below will have a db.opt file that holds the selection information for the database;
***********************************************************************

Related operations of the database
1:show databases; --View all the databases
2:drop [if exists] database bbbb; --Delete the specified database
3:show CREATE DATABASE Student_system; --View database (Student_system) creation information
+----------------+------------------------------------------------------------------------+
| Database | Create Database |
+----------------+------------------------------------------------------------------------+
| Student_system | CREATE DATABASE ' Student_system '/*!40100 DEFAULT CHARACTER SET GBK */| |
+----------------+------------------------------------------------------------------------+
4:alter database db_name [specified action]--Modify the information of the databases
Example: Alter DATABASE Student_system character set ' UTF8 ';
*************************************************************************************************************** *****




Related Operations for tables
Creation of tables
Creation syntax: CREATE TABLE [if not EXISTS] tbl_name (column structure) [options]
The table is the carrier of the data, and the database is the container of the table, so before you create the table, you need to decide which database it belongs to.
A table must attribute a database to a

1: You can specify the owning database before the table name when creating the table
CREATE TABLE ' Student_system '. Student (
Name varchar (20),
Sex varchar (3),
Age int
);
2: You can first specify the current default database by using the use db_name and then create the table
Use Student_system
CREATE TABLE Teacher (
Name varchar (20),
Sex varchar (3),
Age int
);

3:show tables; --View all the tables, and first indicate the current default database

4:show CREATE TABLE teacher; --View CREATE TABLE (teacher) creation information
Show CREATE TABLE Teacher\g

5:describe teacher; --View the structure of the table (teacher)
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar (20) |     YES | |       NULL | |
| sex | varchar (3) |     YES | |       NULL | |
| Age | Int (11) |     YES | |       NULL | |
+-------+-------------+------+-----+---------+-------+
DESC teacher; --can be abbreviated describe teacher;

6:drop table [if exists] tbl_name; --Delete table (parcel table structure)
Example: drop table student;
Example: Drop table if exists student;
***************************************************************************************************************

Modify Table
Modify Table Name
Syntax: Rename table Old_table_name to New_table_name
Example: Rename table student to student_1;
Example: Rename table Student_2 to student_1, teacher to teacher_1; --Can modify multiple table names at the same time
Example: Rename table student_1 to ' test '. student_2; --can be renamed across databases, you can rename the database by renaming the table



Modify the definition of a column
New plus column (add)
ALTER TABLE student_1 add ID int;

Delete Column (drop)
ALTER TABLE student_1 drop ID;

Modifying a column definition (modify)
ALTER TABLE student_1 modify name varchar (10);

Rename column (change)
ALTER TABLE student_1 change age student_age int (3);
*******************************************************************************************************


Table Data Operations (add and revise)
Inserting data (creating data Create)
Syntax: INSERT into table name (field list) VALUES (Value list)
Example: INSERT into teacher_1 (name,age) VALUES (' chubby ', 18);
Example: INSERT into teacher_1 values (' chubby ', ' male ', 16); --If no field list is specified, the value to be inserted is the same as the order of the fields in the column
Insert into teacher_1 (name,age) VALUES (' small not ', 19);
INSERT into teacher_1 values (' aha ', ' female ', 18);


Querying data (reading data read)
Syntax: Select field List from table name where query condition
Example: Select Name,age from teacher_1;
Example: SELECT * from teacher_1; --If the field list is replaced with an * number, it means that all the fields in the query
Example: SELECT * from teacher_1 where name = ' Chubby '; --may use query conditions for data filtering, get the desired data;
Example: SELECT * from teacher_1 where 1; --where 1 means the condition is always set
SELECT * from teacher_1 where 0;


Modify Data (update)
Syntax: Update table name SET field = new value,... WHERE condition
Example: Update teacher_1 set sex= ' female ' WHERE name = ' Chubby ';
Update teacher_1 Set sex = ' confidential ', age = [+], name = ' Ah ' WHERE name = ' aha ';


Deleting data (delete)
Syntax: Delete from table name where condition
Example: Delete from teacher_1 where age = ' 18 ';
Example: Delete from teacher_1; --Delete the entire table if no conditions are removed (different from drop table teacher_1)
PS: When deleting data, must give a strict logic to judge the condition, otherwise it is easy to cause data mistakenly deleted, resulting in loss of data



Curd (Create update read delete)--Delete and change

MySQL Library table operation (FAT Teacher)