Database, table, field, record: Add, delete, change, check. Classification to learn, the focus is the query.
/*database, table, field, record: Add, delete, change, check*/--1. Database: Adding and deleting changesCreateDatebaseif not existsJkxy;Drop Database if existsJkxy;renameDatabaseJkxy toJikexueyuan;--This statement officially says it's not going to work.show databases; UseJikexueyuan;/*1, CREATE DATABASE new_db_name; Create a new database 2, RENAME TABLE db_name.table1 to New_db_name.table1; Rename all tables 3, DROP DATABASE db_name; Delete the original database*/--2. Table: Adding and deleting changesCreate Table if not exists User();Drop Table if exists User;Alter Table UserRename users;--As can be omitted do notshow tables;descusers;--3. Fields: Additions and deletionsAlter Table User AddTpassChar( +) not NULL default "'After Userpass;Alter Table User DropTpass;Alter TableChange Userpass Tpassvarchar( -) not NULL default "';Alter TableModify TpassChar( +) not NULL default "';desc User;Alter Table UserAuto_increment= 1;--set self-increment to 1Alter Table User Add IndexUsername_index (username);--to add an index to a fieldAlter Table User Drop IndexUsername_index;Alter Table User Add Unique(tel);--still have the primary key don't know how to add and removeAlter Table User Drop IndexTel--ALTER TABLE user add unique Tel_unique (tel); index and unique delete are all with DROP indexShow indexes from User;--4. Record: Adding and deleting changesInsert into User(username, pwd, email)Values('User1', MD5 (123456),'[email protected]'), ('User2', MD (123456),'[email protected]');Delete from User whereId= 2;Update User SetUsername= 'user111', email= '[email protected]' whereId= 1;SelectUsername from User whereId> 1;--5. Focus: QuerySelectUsername from User whereId> 2;SelectUsername as 'User name' from User whereId> 2;Select distinctEmail from User;--only for one fieldSelect * from User whereUsername like '%user%';--content contains user's recordsSelect * from User Order byScoreDESC;Select * from User Order byScoreDESCLimit2;--limit 0, 2; Select * from User Order byScoreDESCLimit2,3;--Subscript starting from 0, starting with 2nd + 1, fetch threeSelect Count(ID) num from User whereId> 2;--Count number of IDs, Num is an aliasSelect avg(score) num from User;--calculate mean, and function: Max () min () sum ()
MySQL database, tables, fields, records: Add, delete, change, check