My way to study _ the 18th Chapter _sql statement

SQL statements
Startup database: net start MySQL shutdown database: net stop MySQL

Login database: cmd--> command line-->mysql-->mysql-u account-p password

"Create and delete Databases"

Use keyword: Create (created) database

Format: Create database name;

Create database name character set character set;

--View all the databases in MySQL

Show database;

--View information about the definition of a database

Show create database name;

--Switch database

Use database name;

--View the database being used

Select Database ();

--Delete Database

drop database name;

"Create SQL statement for table"

Create a database table using the Keyword CREATE TABLE (table)
[]: The database is optional meaning, can have, can not

Format:
CREATE TABLE Table name (
Field name data type [length] [constraint],
Field name data type [length] [constraint],
...
Field name data type [length] [constraint] (last one cannot have a comma)
);

PRIMARY KEY constraint: Primary key field cannot be null, cannot be duplicated

--View all tables in the current database

Show Table;

--View table structure

DESC table name;

--Delete Table

drop table name;

"Modify table Structure format"

Keyword: alter (modify) modify (change) changes (change)

--Modify table to add columns

ALTER TABLE name add column name type [length] [constraint];

--Modify the type length and constraints of table modified columns

ALTER TABLE name modify column name type [length] [constraint];

Note: If you have data, you must be aware of the data type varchar--and int prone to error data

--Modify column names and data types and constraints

ALTER TABLE name change original column name new column name type [length] [constraint];

--Delete Columns

ALTER TABLE name drop column name;

Note: If there is data in the column, the data will be deleted together, so be careful

--Modify Table name

Rename table name to the new name;

--Modifying the character set of a table

ALTER TABLE name character set character set;

Note: It is not recommended to execute, it is possible to generate garbled

"Insert Table Data"

Use keyword: insert (insert) [into]

Format:
Include primary key: INSERT into table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...);
Primary key increment, omit primary key: INSERT into table name (without primary key) values (not including primary key);

Precautions:
1. Fields and values must correspond to each other (number, data type)
2. In addition to the numeric type (int, double) all other data types need to be wrapped in quotation marks
You can use double quotation marks, or single quotation marks, we recommend using single quotes

--Contains primary key

Insert into table name (Field 1, Field 2 ...) value (value 1, value 2 ...);

--Create TABLE, primary key self-increment auto_increment (primary key to add itself, grow yourself)

CREATE TABLE Table name (

field data type [length] primary key auto_increment,
field data type [length] [constraint],
...
);

--primary key self-increment, omit primary key:

Insert into table name (without primary key) values (not including primary key);

--BULK INSERT data

Format:
Include primary key: INSERT into table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...)
Primary key increment, omit primary key: INSERT into table name (without primary key) values (not including primary key), (value 1, value 2 ...)

--Omit Field name format: The value of all fields must be given (including primary key)

Format:
Insert into table name values (value of all fields);
Insert into table name values (value of all fields), (values for all fields) ...

--cannot remember the primary key, you can use Null,sql will automatically calculate the primary key

Insert into table name values (null, field);

"Update table Data"

Use keyword: update (update) set (set)
Format:
Modify all data in a column at once without conditional filtering (use caution)

Update table name set field name = field value, field name = field Value ...

Conditional filtering, using the keyword where

Update table name set field name = field value. field name = field Value ... where filter conditions

"Delete table Data"

Use keyword: delete (delete) from (from)

Format:
Delete from table name [where filter condition]

Delete from table name: Removes all data that is marked, but does not remove the primary key increment

TRUNCATE TABLE name: Removes all data from the table, removes the primary key increment, and lets the primary key self-increment, starting with 1

"PRIMARY KEY constraint"

Use keyword: primary can
Role:
Constraint primary key column cannot be null
Cannot repeat
Each table must have a primary key and only one primary key
Primary key cannot use business data

--the first way to add a primary key, add it directly after the column name (add it directly when creating the table)

--The second way to add a primary key, using the constraint area

[constraint name] primary key (field list);

If you do not give the name of the primary key, you can omit the keyword constraint

--The third way to add a primary key, after creating the table, by modifying the table structure, adding a primary key

ALTER TABLE name ADD [constraint name] primary key (field list);

--Delete primary key

ALTER TABLE name drop PRIMARY key;

--Federated primary key: Use more than 2 fields as the primary key

CREATE TABLE Table name (
field data type [length],
field data type [length],
...
Primary key (field, field)
);

"Non-null constraint"

Use keyword: NOT null

Role: Force constraint a column cannot be null (null value not accepted)

--Create a non-null constraint the first format, when you create a table, directly after the field

field data type [length] not null;

--Create a non-null constraint second format, modify table structure

ALTER TABLE name modify column name data type [length] [constraint];

--Delete non-empty constraints

ALTER TABLE name modify column name data type [length];

"Unique constraint"

Use Key sub: unique

Function: A field with a unique constraint added, cannot be duplicated

--The first format for creating a unique constraint, when the table is created, given directly after the field

field data type [length] unique;

--second format for creating unique constraints, using [constraint name] unique when creating a table (Field list)

CREATE TABLE Table name (

Field 1 data type [length],
Field 2 data type [length],
...
Constraint unique (field 2)

);

--Create a third format for the unique constraint, and after creating the table, modify the table data

ALTER TABLE table name modify column name type [length] unique;

--Delete Unique constraint

ALTER TABLE name DROP INDEX name
When defining a constraint, if no name is created, the name is called a string (field), and if a name is created, the name of the created

"Default constraint"

Add a default value to the field, or use the default value if the field does not have an Insert value

Use keyword: default value

--Create DEFAULT constraint mode One, CREATE TABLE, default ' defaults ' after column data type

--Create DEFAULT constraint mode Two, modify table structure

ALTER TABLE name modify column name data type [length] default ' defaults ';

--Delete Default constraints

ALTER TABLE name modify column name data type [length];

My way to study _ the 18th Chapter _sql statement