MySQL Basic operation

Source: Internet
Author: User
Tags abs symlink create database

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.