The second day of Mysql learning, the second day of mysql

The second day of Mysql learning, the second day of mysql

DDL database Definition Language (Database Operations) (read and understand, rarely used at work)
1. Create a database
Create database name [character set table name collate character verification set name]
Create database day15;
1> Query OK. 1 row affected (0.00 sec) indicates that the execution is successful.
2> SQL statement Syntax: ";" should be used at the end.
3> character collation: used to sort data.
2. display the databases in mysql.
Show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Day13 |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
We can see that there are some other libraries besides the self-created libraries. Do not tamper with other libraries (except test. This is because it stores mysql configuration information, account information, and so on.
Test Library: automatically created for testing.
3. delete a data
Drop database name;
Drop database day15;
4. Modify the database code table and character verification (not commonly used)
Alter database name character set database table [collate checklist name];
Alter database day15 character set utf8 collate utf8_bin;
5. The database to be used (important)
Use Database Name
Use day15;
If the Database changed, the switchover is complete.
6. view the selected Database
Select database ();
+ ------------ +
| Database () |
+ ------------ +
| Day15 |
+ ------------ +
7. display the statement for creating a database.
Show create database name;
Show create database day15;
| Day15 | create database 'day15 '/*! 40100 default character set utf8 COLLATE
Utf8_bin */|
// Certificate //-------------------------------------------------------------------------------------------------------------------------------------------------------
Data Type in the database (understand and know under what circumstances the data type should be used)
I. Number Type
Integer
TINYINT 1 byte
SMALLINT 2-byte short
MEDIUMINT 3 bytes
(Commonly used) INT 4-byte int
BIGINT 8-byte long
Floating Point Type
FLOAT, with a single precision of 4 bytes, float
** DOUBLE 8-byte double
* DECIMAL has no loss of precision.
========================================================== ====
What is the difference between DOUBLE and DECIMAL?
The DOUBLE type has a missing precision During computation.
DECIMAL is used to solve the problem of missing precision. (The underlying layer uses strings to save numbers)
Double is used to represent the decimal attribute.
Decimal is the number of decimals that need to be frequently involved in the operation.
========================================================== ====
II. String type
Note: The string type must be enclosed in single quotes.
Short string type
CHAR/VARCHAR (up to 255 bytes)
========================================================== ================
Q: What is the difference between char and varchar?
Char fixed-length string. varchar indicates a variable-length string.
The length is also set to 10. When abc is stored
Char => 'abc'
Varchar => 'abc'
Conclusion: varchar is used most frequently during development. Char is only used in enumeration indicating a fixed length. Example: Gender (expressed)
========================================================== ================
Long string type (stream type)
* TEXT/CLOB save TEXT (TEXT Stream) --> use the writer character in. java when the content to be saved exceeds 255 bytes
BLOB storage byte (byte stream) --> stream byte in java is not used in development
Character Large Object
Binary Large Object
Differences:
Text: only character data can be stored.
BLOB: stores character and multimedia information (image sound images)
Iii. Date and Time types
Date only records the date
Time only records time 11:36:25
Year records only 2015 of the year
Datatime record date and record time 11:36:25
** Timestamp: Same as above 11:36:25
Question: What is the difference between datatime and timestamp?
The data of these two types of records is exactly the same.
The difference is that when inserting a data type, if the data type is not inserted, the default value of this type is null;
If no value is input when the timestamp type is inserted, the default value of this type is the current time;
//--------------------------------------------------------------------------;----------------------------------------
Statements related to table creation (DDL) (Remember, hand-written)
Create table table_name
(
Field1 datatype constraint/primary key constraint auto_increment,
Field2 ype constraints,
Field3 ype Constraints
) Character set collate verification rules
1. Create a table
Create table t_user (
Name varchar (10 ),
Password varchar (20 ),
Age int (3), -- the length can be ignored when the number type is declared.
Birthday datetime,
Hiredate timestamp
);
It is best to specify the length of varchar.
Integer is not specified.
2. View tables in the current database
Show tables;
3. view the table structure
Desc table name;
Desc t_user;
4. delete a table
Drop table name;
Drop table t_user;
5. Add a column
Alter table name add column name type;
Alter table t_user add photo blob;
6. Modify the column type
Alter table name modify column name type;
Alter table t_user modify photo varchar (20 );
7. Modify the column name
Alter table name change old column name new column name data type;
Rename the photo column to image.
Alter table t_user change photo image varchar (20 );
8. delete a column
Alter table Name drop column name;
Alter table t_user drop image;
9. Modify the table name
Rename table old table name to new name;
Rename table t_user to user;
10 (rarely used) modify the character set of the table (if not specified during table creation, the character set of the database is used by default)
Alter table name character set collate check set;
Alter table t_user character set utf8 collate utf8_bin;
// Configure //-------------------------------------------------------------------------------------------------
Column constraints (master)
To ensure data integrity.
1. A non-null constraint (not null) specifies a non-null constraint column. It must contain a value when inserting a record.
2. unique constraint (unique) the content of this column is in the table. The value is unique.
3. when you want to use the value of a column as the unique identifier of the column, you can specify the primary key constraint (including non-empty constraints and unique constraints ). only one primary key constraint column can be specified in a table.
Primary key constraint, which can be understood as non-null + unique.
Note: A table can only have one primary key constraint.
Constraints reflect the integrity of the database.
For example, create a table with Constraints
Create table t_user2 (
Id int primary key auto_increament, -- employee id
Name varchar (10) not null, -- employee name
Loginname varchar (10) not null unique, -- Login Name
Password varchar (20) not null, -- password
Age int (3) not null, -- age
Birthday datetime not null, -- birthday
Hiredate timestamp not null -- entry date
);
// Configure //----------------------------------------------------------------------------------------------------------------------------
Automatic growth of primary keys (master)

Note:
1. If the primary key of a table is a number, we can set the primary key to auto-increment.
2. Primary Key auto-increment may cause primary key faults.
3. mysql and sqlserver databases have this function.
4. Primary Key auto-increment can only be applied to columns with primary key constraints.
Auto-increment means that you do not need to specify a value each time you insert a record. This field maintains its own value.
The maintenance method is to add 1 each time;
Syntax:
Create table t_user (
Id int primary key auto_increment,
Password varchar (30) not null,
Ageint not null,
Birthday datetime not null,
Hiredate timestamp not null,
Number int unique
);
// ---------------------------------- Create and modify a table ---------------------------------------------------
Create table employee (
Id INT (10 ),
Name varchar (10 ),
Gender VARCHAR (10 ),
Birthday DATETIME,
Entry_date TIMESTAMP,
Job VARCHAR (5 ),
Salary DOUBLE (5, 3 ),
RESUME TEXT
);

1. Add an image Column Based on the employee table above.

Alter table employee add image blob;

2. Modify the job column to 60 characters in length.

Alter table employee modify job varchar (60 )'

3. Delete the gender column.

Alter table employee drop gender;

4. Change the table name to user.

Rename table employee to user;

5. Change the table character set to utf8.
6. Change the column name to username.
Alter table employee change name username varchar (20 );

// ---------------------------------- Add, delete, and modify data in the table (DML )-------------------------------------------------------------------
Create table t_user (
Id int primary key auto_increment,
Name varchar (20) not null,
Email varchar (20) unique
)