--01 MySQL database operation
--Link DatabaseMysql-uroot-pmysql
--Don't show password * * * *Mysql-uroot-p
--Exit the databaseQuit/exit Ctrl+d
--The SQL statement needs a semicolon at the end;
--Show database version versionsSelect version ();
--Show Time nowSelect Now ();
--View the currently used databaseSelect Database ();
--View all databasesshow databases;
--Create a databaseCreate database name charset = UTF8;
--View the statement that created the databaseShow CREATE DATABASE name
--Using the databaseName of the USE database
--delete databasedrop database name;
--02 operation of data tables
--View all tables in the current databaseShow tables;
--Create a table--int unsigned unsigned shaping--auto_increment indicates automatic growth--NOT NULL indicates cannot be empty--primary key means primary key--Default defaults--Create TABLE data table name (field type constraint [, field type constraint]);CREATE TABLE table name (id int unsigned auto_increment primary key,name varchar () not null,age int unsigned);
--Modify table nameALTER TABLE old table name rename new table name; Rename table name to new table name;
--View table structureThe name of the DESC data sheet; --Create students table (ID, name, age, High (decimal), Gender (enum), cls_id) CREATE TABLE students (ID int unsigned auto_increment Primary key,name varchar (+) not NULL, age int Unsigned,high decimal (5,2), gender enum ("Male", "female", "Confidential", "demon") Default "secrecy", Cls_ ID int unsigned);
--View the table creation statementThe Show create table table name;
--Modify table-add field mascot (mascot) * *--ALTER TABLE name add column name type;
--Modify table-Modify field: Do not rename version--ALTER TABLE name modify column name type and constraints; ALTER TABLE classes modify mascot varchar (100);
--Modify table-Modify field: Rename version * * *--ALTER TABLE name change formerly known as the new name type and constraints; ALTER TABLE classes change mascot Jxw varchar (30);
--Modify table-delete field *--ALTER TABLE name drop column name; ALTER TABLE classes drop JXW;
--Delete table--drop table name; -Drop database; drop table classes; * Drop Database Python10; Half
--03 Additions and deletions (curd)
--Increase
--Full column insertion--insert [into] Table name (field name) values--primary key field can be occupied with 0 null default-Insert a class into the classes table insert into Classes (name) VALUES ("Python10"); Insert into classes (name) values (' Python11 '), (' Python12 ');
--Insert allINSERT into students (ID,NAME,AGE,HIGH,GENDER,CLS_ID) VALUES (2, ' Yangyang ', 18, 1.80890, ' secrecy ', 001);
--Partial insertion--INSERT INTO table name (column 1,...) VALUES (value 1,...) INSERT into students (name) VALUES ("Yang3");
--MultiRow InsertINSERT into students (name) VALUES ("Zhang San"), ("John Doe"); Insert into table name (field) VALUES (value) #一一对应
--Modify--Update table name set column 1= value 1, column 2= value 2 ... where condition;
--All modificationsUpdate students set name = "Harry"
--Modified by conditionsUpdate students set name = ' John Doe ' where id = 4;
--Modify multiple values on a per-condition basis--Update students set gender = "", name = "xxx" where; Update students set Name= ' Zhang San ', age = where id = 5;
--Query basic usage
--Query all columns--select * from table name;
---Set condition querySELECT * FROM table name where id = 2;
--Query the specified column--Select column 1, column 2,... from table name; --You can use as to specify an alias for a column or table--select field [As Alias], field [as Alias] from data table; --Order of the fields select Age,name from students;
--Delete
--Physical deletion--Delete from table name where condition;
--Logical deletion--Use a field to indicate whether the message is no longer available--add a is_delete to the students table bit type--alter table name add field type default value;
--database backup and Recovery (learn)--mysqldump–uroot–p database name > python.sql; --Mysql-uroot–p New database name < Python.sql; # Note that you need to create a database before importing
MySQL Statement collation (i)
