Mysql > mysql

1. Perform operations on the database

• View all databases

  SHOW DATABASES;

• View the database currently in use

  SELECT DATABASE();

• Use a database

  USE [databaseName];

• Create a new database

  CREATE DATABASE [databaseName] CHARACTER SET utf8 COLLATE utf8_unicode_ci;

• Deleting a database

  DROP DATABASE [databaseName];

• Modifying the database encoding

  ALTER DATABASE [databasesName] CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

2. Perform operations on the table

• View all tables

  SHOW TABLES;

• The new table

  CREATE TABLE IF NOT EXISTS user(
  	id int PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(10) NOT NULL,
      age int,
      birthday YEAR,
      tell VARCHAR(20) UNIQUE DEFAULT ' ',
      createTime TIMESTAMP
  );
  Copy the code

• Delete table

  DROP TABLE IF EXISTS [tableName];

• View table structure

  DESC [tableName]

• View the SQL statement that creates the table

  SHOW CREATE TABLE [tableName];

  SHOW CREATE TABLE [tableName] \G;

• Modify the table

  # 1. Change the name of the table
  ALTER TABLE [tableName] RENAME TO [newTableName];
  # 2. Add new columns
  ALTER TABLE [tableName] ADD columnName [type] [position];
  # 3. Change the field name
  ALTER TABLE [tableName] CHANGE [oldColumnName] [newColumnName] type;
  # 4. Change the field type
  ALTER TABLE [tableName] MODIFY [columnName] [type];
  # 5. Delete a field
  ALTER TABLE [tableName] DROP [columnName];
  Copy the code

• supplement

  # 1. Create another table based on the table structure (only table structure, no data added)
  CREATE TABLE [tableName] LIKE [tableName];
  # 2. Create a new table based on the contents of the other table
  CREATE TABLE [tableName] (SELECT * from [tableName]);
  Copy the code

• Add, delete, change and check table data

  # 1. Insert data
  INSERT INTO [tableName] (colum2,colum3...) VALUES(columnValue2,columnValue3...) ;INSERT INTO [tableName] VALUES(columnValue1,columnValue2,columnValue3...) ;# 2. Delete data
  DELETE FROM [tableName]; # delete all data
  DELETE FROM [tableName] where [condition];
  # 3. Modify data
  UPDATE [tableName] SET [condition]; All data will be modified
  UPDATE [tableName] SET [condition] where [condition];
  # 4. Find the data
  	# Full data
  	SELECT * FROM [tableName];
  	# specify field
  	SELECT column1,column2 FROM [tableName];
  	Alias the result of the field
  	SELECT column1 newColumn1,column2  AS newColumn2 FROM [tableName];
      # conditions> < = || BETTWEN AND LIKE % _ <> ! =# pagingLIMIT OFFSET LIMIT of 20, 10# 5. Aggregate functions
   	SUM AVG COUNT GROUP BY HAVING MAX MIN
  Copy the code