Basic usage of mysql (1) _ MySQL

Basic usage of mysql (1 ). 1. Install and start the database: 1. Install the database: sudoapt-getinstallmysql-serversudoapt-getinstallmysql-client 1. Install and start the database

1. Install the database:

Sudo apt-get install mysql-server
Sudo apt-get install mysql-client
2. Enable the database:

Sudo service mysql start

3. log on to the database:
Mysql-u root (this is to log on without a password in the database)

Mysql-u root-p (this is to log on when the database has a password)

2. Create a database (pay attention to the use of semicolon-";" when entering the mysql command !!!)

1. Create a database:

Create database hello; (indicates creating a DATABASE named hello)

(Note that commands in mysql are not case sensitive. Therefore, you can also create database, create database, create DATABASE, and CREATE daTabase)

If Query OK, 1 row affected appears after the preceding command is entered
It indicates the creation is successful.
You can use the command: show databases; To View


2. Connect to the database
Eg: use hello
If: Database changed, the Database connection is successful.


View the table commands in the current database: show tables;
If the prompt is: Empty set (0.00 sec)
The current database is empty.


3. Create a data table
The created format is:
Create table Name
(
Column name 1 data type (Data Length ),
Column name 2 data type (Data Length ),
Column name 3 data type (Data Length)
);
Eg:
Create table zhuce
(
Id int (10 ),
Name char (20 ),
Phone int (10)
);
When the prompt is: Query OK, 0 rows affected (0.10 sec)
The instance is created successfully!


You can use the show tables command to View tables in the secondary database.


4. Data Type
The data types in databases are similar to those in common programming languages. Many of them are used in the same way. The following lists the differences:
Enum usage: single choice, comparison gender eg: enum ('A', 'B', 'C ')
Set purpose: multiple choice eg: set ('1', '2', '3 ')
Date purpose: date eg: YYYY-MM-DD 3 bytes
Time usage: time point or duration. For example: HH: MM: SS, 3 bytes.
Year usage: The year value. For example, YYYY, 1 byte.
Varchar usage: variable-length string 0 ~ 255 bytes
Text purpose: long text DATA 0 ~ 65535 bytes

Differences between char and varchar:
Char: char (10) indicates that the stored characters occupy 10 bytes (whether or not they are full of 10 characters ).
Varchar: varchar (10) indicates that a maximum of 10 bytes can be stored. However, when the number of characters you store is less than 10, the storage is based on the actual length.


5. Insert data
Run the following command to view the table: select * from nihao;
Appears: Empty set
The table is still empty ~~~
Insert data to the table in the following format:
Insert into Table Name (column name 1, column name 2, column name 3) values (value 1, value 2, value 3 );
Eg:
Insert into nihao (id, name, phone) values (01, 'Tommy ', 101010 );
If Query is OK, the insertion is successful!


Note that single quotation marks are required for varchar, char, date, time, enum, text, set, and other types, while single quotation marks are not required for int, float, and double.


After insertion, use select * from nihao to view the nihao table.

6. Exit mysql connection

Enter exit, quit, or \ q to exit.

Iii. Constraints in mysql

The constraints and their keywords are as follows:

1. Primary Key constraints

The primary key constraint is used to constrain a row in the table. As the identifier of this row, a row can be accurately located in a table using the primary key. The primary keys in the row cannot be repeated and cannot be blank !!!

Eg: create table example1

(Id int (10) primary key

Name char (20 ),

Constraint dpt_pk primary key (dpt_name)

);

IndicatesPrimary KeyConstraint: the constraint dpt_pk primary key (dpt_name) Code defines the primary key, and the dpt_pk is the custom primary key name.

AndComposite primary keyDefine eg: constrint proj_pk primary key (proj_num, proj_name)

2. Default Value Constraints

The default value constraint specifies that when the inserted data is null for a column with a default value constraint, this position will be filled by the default value.

The settings are as follows:

Code analysis: the default '10' in the Code indicates that when dpt_num is not input, the default output is 10.

Eg: insert into example (dpt_name, dpt_num) value ('nihao', 11 );

Insert into example (dpt_name) values ('wohenhao ');

Code Analysis: dpt_num is not input in the second line of code.

After entering the preceding code, enter select * from example to view the table:

3. unique constraints

The unique constraint is that the values of a specified column in the table must not have duplicate values, that is, each value in this column is unique.

The table is created as follows:

Code analysis: the unique (stu_class) in the Code is the unique constraint on stu_class.

The input data is as follows:

When you enter the second line of code, an error is displayed, because stu_class is set to unique when the table is created, that is, the unique value!

4. Foreign key constraints

The foreign key ensures data integrity and represents the relationship between tables. A table can have multiple foreign keys. Each foreign key must refer to the primary key of another table, the value of a column restricted by a foreign key must have a corresponding value in its reference column.

5. Non-empty Constraint

Columns with non-null constraints must be non-empty during entry.

Create a table as follows:

Code analysis: the id cannot be blank.

Data insertion:

Code Analysis: You can see that no id is entered in the Code. After the input is complete, there is a warning of 1 warning.

View the table:

Id is not input, but its explicit value in the table is 0, that is, when the id is not null, when the input is empty, it is recorded as 0.

Iv. Modify and delete

1. delete a database

Command example: drop database name;

Eg: drop database test;

2. rename a table

Format: rename table (formerly called;

Or: alter table, formerly called rename, with a new name;

Or: alter table, formerly rename to new name;

3. delete a table

Format: drop table name;

4. Modify the table structure

① Add a column:

Format: alter table name add column Name Data Type constraint;

Or: alter table name add column name data type constraints;

② Delete a column:

Format: alter table Name drop column name;

Or: alter table Name drop column name;

③ Rename a column:

Format: alter table name change original column name new column name data type constraints;

(Note: The "Data Type" after this rename statement cannot be omitted; otherwise, the rename fails)

④ Change the data type

Format: alter table name modify column name new data type;

(Careful consideration may cause data loss)

5. Modify Table content

① Modify a value in the table

Format: update table name set column 1 = value 1, column 2 = value 2 where condition;

(Note: You must have the where condition; otherwise, the consequences will be disastrous !)

② Delete a row of records

Format: where condition for the name of the delete from table;

Eg: delete form test where name = 'nihao ';