MySQL database, tables, fields, records: Add, delete, change, check

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.