MySQL Basic operation

One: Basic operations

1. Database creation

Create Database JMDb;　　　　　　　　 Create a database named JMDb show databases; Show current Database list
Show Database JM;　　　　　　View how the database was created drop databases JMDb; To delete a database named JMDb
Use JM; Select Database

2. Remote connection

1.mysql> use MySQL;
2.mysql> select User,host from user;

+---------------+-----------+
| user | Host |
+---------------+-----------+
| mysql.session | localhost |
| Mysql.sys | localhost |
| Root | localhost |
+---------------+-----------+

3.mysql> Update user set host= '% ' where user= ' root '; Modify the host column to '% '

4.mysql> select User,host from user;
+---------------+-----------+
| user | Host |
+---------------+-----------+
| Root | % |
| mysql.session | localhost |
| Mysql.sys | localhost |
+---------------+-----------+

---------------------------------------Split Line---------------------

Remote connection requires a firewall off

[[email protected] ~]# systemctl Stop FIREWALLD # temporary shutdown

[[email protected] ~]# systemctl disable FIREWALLD # Disable boot start

Removed Symlink/etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed Symlink/etc/systemd/system/dbus-org.fedoraproject.firewalld1.service.

3. Table creation

Use JMDb; Select a database named JMDb

# Create a table

CREATE TABLE Student (
ID int primary KEY auto_increment, # ID, decorated as primary key, self-increment
Name varchar Unique, # indicates that the column data cannot have duplicates
Age int #int类型
) Engine=innodb default Charset=utf8; # Specify Database engine InnoDB, encoded as UTF8

Constraint: Primary KEY (non-null and unique): A field that uniquely distinguishes the current record is called a primary key

Unique: Only, the data can not be duplicated, once repeated will be an error

Not NULL: The column data cannot be empty, otherwise an error

Auto_increment: The primary key field must be a numeric type

FOREIGN key: FOREIGN KEY constraint

-----------------------------------------------Split Line-----------------------------

To view table information:

Desc Student View Table structure

Show columns from student view table structure

Show tables View all tables for current data

Show CREATE TABLE student view creation statement for table

-----------------------------Split Line-----------------------------------------------

To modify a table structure:

1. Add a field (column):

ALTER TABLE student add [column name] type [constraint condition] [first|after column name]

ALTER TABLE student add ABD int after name; Add an ABD column, specifying that it is behind the name column

ALTER TABLE student add ABA int first; Add an ADA column, specifying that the column is in the front

ALTER TABLE student Add ABS int, adding multiple columns

Add ACC varchar (20),

Add CAA varchar (10);

-------------------------------Split Line-------------------------------------

2. Modify the type of a column

ALTER TABLE student Modify column name type [constraint] [first|after field name]

ALTER TABLE student Modify ABA varchar (a) unique; To decorate an ABA column as a unique field

3. Modify column names

ALTER TABLE table change column name new column name type [constraint] [first|after field name]

ALTER TABLE student Change ABA ABA varchar (10); To change ABA into ABA

4. Delete Columns

ALTER TABLE table drop [column] column name

ALTER TABLE student drop aBa;

ALTER TABLE student drop column Abd,drop column abs; To delete multiple columns, you must have column

5. Modify the table name

Rename table name to the new name;

6. Modifying the character Set

ALTER TABLE student default CHARACTER SET UTF8 COLLATE utf8_general_ci;

7. Delete the table:

drop table name;

-----------------------------------Split Line----------------------------------------

8. Add a PRIMARY key

ALTER TABLE student Add primary key (field name ...)

9. Delete primary Key

ALTER TABLE student modify ID int; Delete Auto_increment First

Alter TABEL student drop PRIMARY key; To remove the primary key

-----------------------------------Split Line---------------------------------------

Index:

Normal index: ALTER TABLE student Add (index|key) [index name] (field ...)

1.alter Table Student Add index (name) #为name创建索引, index name defaults to field name

2.alter Table Student Add index username (name) #为name创建索引 with index name username

Note: Key and index are used the same way

Unique index: ALTER TABLE studnet add unique (index|key) [index name] (field name ...)

1.alter table studnet Add unique index username (name) #为字段name创建唯一索引 with index name username

　　

Federated Index:

1.alter Table Student Add index User_age (name, age)

2.alter table Student Add unique index User_age (name, age)

To delete an index:

ALTER TABLE student Drop (index|key) field name

such as: ALTER TABLE student DROP INDEX username #删掉索引username

4. Primary key

#单列字段主键
Create TableStudent (IDint Primary Keyauto_increment, namevarchar( -)Unique, Ageint) Engine=InnoDBdefaultCharSet=UTF8;
# Multi-field Federated primary keyCreate TableStudent (IDintauto_increment, namevarchar( -), ageint,                       Primary Key(id,name)) engine=InnoDBdefaultCharSet=UTF8;
#一张表只能有一个主键
#主键类型不一定得是整型

MySQL Basic operation