--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)