Basic mysql knowledge Review

Basic mysql knowledge Review
Create a database
Creat table test (
# An integer usually uses an int.
Test_id int,
# Decimal is usually used for decimals.
Test_price decimal,
# Common text is usually used and Default is used to specify the Default value
Test_name varchar (255) default "Xxx ",
# Use test for large text types
Test_desc text,
# Use blob for images
Test_img blob,
# Use DateTime for date type
Test_date datetime,
);
-----------------------------------------------------------------
Column types supported by mysql
1. tinyint, smallint, mediumint, int, bigint
2. float, double
3. decimal (dec)
4. date
5. time
6. datetime
7. timestamp
8. year
9. char
10. varchar
11. binary (a fixed-length binary string type that stores strings in binary format)
12. varbinary
13. tinyblob, blob, mediumblob, longblob
14. tinytext, text, mediumtext, longtext
15. enum ('value1', 'value2'...) // Enumeration type (only one of them)
16. set ('value1', 'value2'...) // set type (several of them can be)
--------------------------------------------------------------------
# Create a data table. The data table and user_info are identical.
Create table hehe
As
Select * from user_info;
---------------------------------------------------------------------
# Modify the table structure syntax
Alert table name
Add (
# Multiple column definitions can be defined.
Colum_name datatype [default expr],
...
);
---------------------------------------------------------------------
# Add a hehe_id field to the hehe data table. The field type is int.


Alter table hehe
Add hehe_id int;
# Add the aaa and bbb fields to the hehe data packet. Both fields are of the varchar (25) type)
Alter table hehe
Add aaa varchar (25), bbb varchar (25 );
----------------------------------------------------------------------
# Change the hehe_id column of the hehe table to varchar (255) type.
Alter table hehe
Modify hehe_id varchar (255 );
# Modify the bbb column of the hehe table to the int type.
Alter table hehe
Modify bbb int;
----------------------------------------------------------------------
# Deleting a specified Column
Alter table hehe
Drop column_name
# Rename a data table
Alter table hehe
Rename to wawa;
----------------------------------------------------------------------
# Rename the bbb field of the wawa table to ddd
Alter table wawa
Change bbb ddd int;
# Deleting a table
Drop table Name
----------------------------------------------------------------------
Database Constraints
Not null
Unique
Primary key
Foreign key
Check
# Not null Constraint
Create table hehe (
# A non-null constraint is created, which means hehe_id cannot be null.
Hehe_id int not null,
# Names cannot be specified if mysql is empty.
Hehe_name varchar (25) default 'xyz' not null,
# The column below can be blank. The default value is null.
Hehe_gender varchar (28) null
);


---------------------------------------------------------------------
# Add a non-empty Constraint
Alter table hehe
Modify hehe_gender varchar (30) not null
# Cancel non-empty Constraints
Alter table hehe
Modify hehe_name varchar (3) null;
# Cancel non-null constraints and specify the default value
Alter table hehe
Modify hehe_name varchar (255) default 'abc' null;
-------------------------------------------------------------------
Unique constraints
# Create a unique constraint when creating a table and use the column-level constraint syntax to create a constraint
Create table unique_test (
# A non-null constraint is created, which means test_id cannot be null.
Test_id int not null,
# Create a unique constraint
Test_name varchar (30) unique
);
# When creating a table, use the table-level constraint syntax to create a constraint
Create table unique_test (
Test_id int not null,
Test_name varchar (30 ),
Test_pass varchar (30 ),
# Use table-level constraints to create unique constraints
Unique (test_name ),
Constraint test_uk unique (test_pass)
# Constrain test1_uk unique (test_name, test_pass)
);
# Modifying unique constraints
Alter table unique_test
Add unique (test_name, test_pass );
# Add constraints to the table
Alter table unique_test
Modify test_name varchar (30) unique;
-------------------------------------------------------------------
Primary key constraint
Create table primaryKey_test (
PrimaryKey_id int primary key,
Test_name varchar (255)
);
Create table primaryTest (
Primary_id int not null,
Primary_name varchar (29 ),
Constraint pk_test primary key (primary_id)
);


# Deleting a primary key constraint
Alter table test
Drop primary key;
# Adding primary key constraints using table-level syntax
Alter table test
Add primary key (test_id, test_name );
# Add a primary key constraint using the column-level constraint syntax
Alter table test
Modify test_name varchar (30) primary key;
------------------------------------------------------------------
# To ensure that the master table referenced by the slave table exists, you must first create a master table.
Create table teacher_table (
# Auto_increment
Teacher_id int auto_increment,
Teacher_name varchar (255 ),
Primary key (teacher_id)
);
Create table student_table (
Student_id int auto_increment primary key,
Student_name varchar (255 ),
# Specify java_teacher to refer to the teacher_id column of teacher_table
Java_teacher int references teacher_table (teacher_id)
# Java_teacher int
# Foreign key (java_teacher) references teacher_table (teacher_id)
# Constraint student_teacher_fk foreign key (java_teacher) references
Teacher_table (teacher_id)
);
Bytes ---------------------------------------------------------------------------------------------
# Check Constraints
Create table test (
Test_id int auto_increment primary key,
Test_age int not null,
Check (test_age> 0 and test_age <120)
)
Knowledge about MYSQL

The jar package downloaded above should be a C # jar package connecting to MySQL,
The programming language provides an interface that the database vendor must implement.
So that your database can support this programming language, just like Microsoft's SqlServer
To support Java, you still need to honestly write a sqljdbc. jar file.
Mssql is a Microsoft SqlServer database and has a graphical SQL Server manager studio. It can be downloaded from the official website for free.

How to get started with mysql?

1. When I create a Database and use it again, the Database changed and I don't know what to do.
Database changed indicates that you can now operate the data in the Database to be moved to. Next we can use select query, delete with delete, and update with update, you can also write and call more powerful stored procedures and triggers ~ A lot of things can be done ~ But it will take 1.1 ~
2. I have never understood. Is the database created on my own computer?
The database is built on your computer ~ Of course, the data structure should be used to organize and store the data. You don't have to worry about how to store data. You should first start with the application.
3. How to establish data connection with other computers
If you are learning C, you can use ODBC for connection (MicroSoft's development tools and languages basically use this). If you are using java later, you can use jdbc. This should not be too urgent.
4. What is the use of the sample database?
The sample database can be used to test whether the database is successfully loaded.
For beginners, it can also be used for exercises (you can use these existing tables to practice DML before you have mastered the DDL language ).

This may be a bit messy.
I remember when we started talking about relational databases, entities, relationships, relational algebra, and functional dependencies .... After talking about a lot of basic knowledge about biased principles, you can start to operate on the machine.
However, if you are self-taught, we recommend that you operate it first and then see why.
I don't know if your "Mysql technology insider" is in the fourth version. I just got a copy of it online. It's a good book. But I suggest you follow the instructions in Chapter 2 to learn how to operate the database first, in this way, you can get started quickly, establish a sense of accomplishment, and be more interested in learning.
In combination with this, you can find a video on the Internet (the kind of project promotion), with a fast gesture.
There are not many young people who love reading books or playing games. Good luck ~