MySQL Basic operation

Database operations:

---View warnings content
Show Warnings

---View the current database in the
Select Database ();

---see those databases
show databases;

---View database creation information
Show CREATE database S3;

---Create a database
CREATE database if not exists S3;

---Create a database setup encoding
CREATE database if not EXISTS S4 character set UTF8;

---modifying database encoding
ALTER DATABASE S4 character set GBK;

---Delete a database
drop database S4;

Create a table

 /////////////table processing (primary key: Non-null and unique) non-null: not Null unique: Unique float (4,2) represents 6 digits, 2 bits maximum 99.99  
 ---CREATE table  
 create table emp ( 
  ID INT primary KEY auto_increment,  
  
  age TINYINT DEFAULT,  
  salary FLOAT (9,2)  
 );  

---Multi-field federated primary key
CREATE TABLE Users2 (
    ID INT,
    Name varchar (20),
    City varchar (20),
    Primary KEY (Name,id)
    );

View table Information

---view table creation information
Desc tab_name View Table structure
Show columns from Tab_name  view table structure
Show tables View all tables in the current database
Show CREATE TABLE Tab_name    view current database table-building statements

Modify table Information

----Modify Table structure
--Add column (field) ALTER TABLE tab_name add [column] column name type [integrity constraint][first|after field name];
ALTER TABLE emp ADD address TEXT;
#添加多个字段
ALTER TABLE emp ADD Date date not null,add work VARCHAR (n) not NULL;

--Delete columns
alter TABLE EMP DROP work; Delete Work fields
#删除多个字段
alter TABLE EMP DROP Date,drop address;

--Modify field Properties
ALTER TABLE emp MODIFY age SMALLINT NOT NULL, #age字段类型修改成SMALLINT NOT NULL
#修改到那个字段后面 [first|after field name] to the first after put to that field
ALTER TABLE emp MODIFY age SMALLINT not NULL after ID; #AFTER ID is placed behind the ID

--Modify column names
ALTER TABLE TAB_NAME change [column] columns name new column name type [integrity constraint][first|after field name];
alter TABLE EMP change age uuid VARCHAR (n) not null #age modified to UUID


--modifications indicate
RENAME TABLE emp to EMPPP;

--Add primary key, delete primary key
ALTER TABLE Tab_name add primary key (field name,...)
ALTER TABLE users drop primary key;

eg:
mysql> CREATE TABLE test5 (num int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can is only one auto column and it must is defined as a key< /c4>
CREATE TABLE test (num int primary key auto_increment);
--thinking, how do I delete a primary key? 
ALTER TABLE test modify ID int; --Auto_increment is gone, but the primary key is still there, so add the following sentence
ALTER TABLE test drop primary key;--Only use this sentence to delete the primary key directly

--Unique index
ALTER TABLE tab_name add unique [index|key] [index name] (field name,...)

ALTER TABLE users add unique (name)--index value defaults to field name show create table users;
ALTER TABLE users add unique key user_name (name);--index value is user_name

--Add a federated index
ALTER TABLE users add unique index name_age (name,age); #show create table users;

--delete unique index
ALTER TABLE Tab_name drop {Index|key} index_name

--Insert Table data
INSERT into EMP (name,salary) VALUES ("Zhang Sanfeng", 1900.99);

#插入多行
INSERT into EMP (name,salary) VALUES ("Zhang Sanfeng", 1900.99), ("Zhang Mowgli", 2900.99);

MySQL Basic operation