Common operations for MySQL

Basic operations

#1 operating folders (libraries)Add CREATE Database db1 charset UTF8; --the specified format is UTF8 check show databases; --View all databases show create database db1;--View the database information that has already been built change alter DB1 charset GBK;--Modify the character encoding of the database delete the DB1;  other use database_name; ---use a database, which is equivalent to entering the database (folder) select databases (); ---Displays the database that is currently located#2 Operation files (table)Switch to Folder: Use DB1 add CREATE TABLE t1 (id int,name char ()) engine=innodb;--Add a table, appended with his data Engine create TABLE t2 (ID int,name char () Engine=innodb Default CharSet utf8;--add a table, followed by his data engine and check show tables;--View all tables in the table show create table T1;--look at the types of existing tables and the DESC function is similar, that is, the form is not good. desc T1;---view table structure change ALTER TABLE T1 add age int;-----------------Add a field (emphasis) column to an existing table: Alter TABLE TI add name type; ALTER TABLE T1 modify name char (12); --------Modify the type of the field. Rename table name to new table name-------------Modify Table name---------------------------------------------------------------2. Modify the field (take the T2 table as an example) ALTER TABLE t2 Modify salary int notNull#change the data type of the Salary field and add a constraint that is not NULL. ALTER TABLE T2 change salary salaries int;#change the name of the salary field to salaries, notice changes the constraint, need to add;In fact, the equivalent of deleting the salary field, recreating a salaries field. Delete the drop table T1; --deletes a table directly. #3 Line contents of the action file (record)add insert into DB1.T1 values (1,'Egon1'), (2,'Egon2'), (3,'Egon3'); Insert into DB1.T1 (name) VALUES ('Egon1'),('Egon2'),('Egon3'); Check Select* fromT1; Select Name fromT1 、、、、、、; Select Name,id fromT1; Change update t1 set name='SB'where id=4;+ Update T1 set name='SB'where name='Alex'; Delete Delete fromT1 where id=4; #There are two ways to clear table records, but the latter is recommendedDelete fromT1; Truncate T1; #when the amount of data is larger, the deletion speed is used in this way    #self-increment IDCREATE TABLE t5 (ID int primary key auto_increment,name char (10)); CREATE table t4 (id int notNull Unique,name char (10) ); INSERT into T5 (name) VALUES ('Egon5'),('Egon6'),('egon7'),('Egon8'),('Egon9'),('Egon10'),('egon11'),('Egon12'),('Egon13');#Copy table StructureCREATE TABLE T7 SELECT * fromT5 where 1=2ALTER TABLE T7 modify ID int primary key Auto_increment;insert into T7 (name) VALUES ('Egon1'),('Egon2'),('Egon3'),('Egon4'),('Egon5'),('Egon6'),('egon7'),('Egon8'),('Egon9'),('Egon10'),('egon11'),('Egon12'),('Egon13');d elete fromT7 where Id=1;#Delete RecordUpdate T7 Set Name="';#Modify the value of a field

Practice

Syntax:1. Modify the table name alter the table name RENAME a new table name;2Add field ALTER table name add field name data type [integrity constraint ...], add field name data type [FULL    Conditions of sexual restraint ...];  ALTER table name ADD field name data type [integrity constraint ...]    First;  ALTER table name ADD field name data type [integrity constraint ...]                            After field name; 3Delete field ALTER table name drop field name;4. Modify field ALTER TABLE name MODIFY field name data type [integrity constraint ...];      ALTER Table table name change old field name new field name old data type [integrity constraint condition ...]; ALTER Table name change old field name new data type [integrity constraint ...]; Example:1. Modify the storage engine MySQL>ALTER TABLE serviceEngine=InnoDB;2. add Field MySQL>ALTER TABLE student10Add name varchar (20) notNULL,-Add age int (3) notNull default 22; MySQL>ALTER TABLE student10Add Stu_num varchar (10) notNull after name; //after adding the name field MySQL>ALTER TABLE student10Add Sex enum ('male','female') Default'male'First //add to the front3. Delete field MySQL>ALTER TABLE student10-Drop Sex;mysql>ALTER TABLE service-drop mac;4. Modify the field type Modifymysql>ALTER TABLE student10-Modify Age Int (3); MySQL>ALTER TABLE student10-Modify ID int (11) notNULL primary key auto_increment; //Modify the primary key5add constraints (increase auto_increment for existing primary keys) MySQL> ALTER TABLE student10 modify ID int (11) notNULL primary key auto_increment; ERROR1068 (42000): Multiple primary key Definedmysql> ALTER TABLE student10 modify ID int (11) notNULL auto_increment; Query OK, 0 rows affected (0.01sec) records:0 duplicates:0 warnings:06Add a composite primary key to a table that already exists MySQL>ALTER TABLE Service2-Add primary key (Host_ip,port); 7. Increase the primary key MySQL>ALTER TABLE Student1-Modify name varchar (10) notNULL primary key;8add primary keys and auto-grow MySQL>ALTER TABLE Student1-Modify ID int notNULL primary key auto_increment;9Remove the primary key A. Remove the self-increment constraint MySQL> ALTER TABLE student10 modify ID int (11) notnull; b. Delete primary key MySQL>ALTER TABLE student10--drop primary key;

Commonly used query statements: 1, the fuzzy query string:

SELECT * from tb_stu where sname = ' Xiao Liu ' select * from Tb_stu where sname like ' Liu% ' select * from Tb_stu WHERE sname like '% path Sequencer ' SELECT * from Tb_stu WHERE sname like '%php% '

2, query the first n records

SELECT * FROM Class LIMIT 2, 4;

The limit is followed by, where it started, followed by a query of several data

3 . Query n records starting at the specified position

SELECT * from Tb_stu ORDER by ID ASC LIMIT $n


$n is the index position of the countdown

4 . Query n records starting at the specified position

SELECT * from Tb_stu ORDER by ID ASC LIMIT $_post[begin], $n

5. querying for non-empty data

SELECT * from Tb_name WHERE address <> "ORDER BY addtime Desc

6. Displays the number of duplicate records and records in the data table

SELECT Name,age,count (*), age from tb_stu WHERE age = ' + ' GROUP by date

What group BY IS grouped

7. 18 Descending/Ascending query of data 　

Select field name from Tb_stu Where Condition order by field DESC     descending SELECT field name from Tb_stu Where Condition order by field ASC       Ascending

Order BY IS sort

Desc     Descending

ASC      Ascending

　　

　

Common operations for MySQL