MySQL Analysis-Basic command (i)

Mainly from the above space to introduce some of MySQL's knowledge points

I. MySQL INTRODUCTION

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications.

Two. Logical architecture

Three. mysql basic commands i. Library 1. Create a database

Syntax: CREATE DATABASE name

#创建数据库ab

CREATE Database AB;

2. View the database

#显示所有的数据库

show databases;

#以行显示所有数据库

Show Databases \g

3. Deleting a database

Syntax: DROP database name

Delete Database ab

Drop Database ab;

Ii. table 1. Creating a Table

Syntax: CREATE TABLE table name (field name, type, field name, type, field name, type);

CREATE TABLE book (Idint), Namechar (+), ageint);

2. View table structure

Desclist;

Explain Food.list;

Show columns from food. List;

Show columns from food. List like '%id ';

#查看表的创建过程, specifying the storage engine, character set

Show CREATE table list;

3.mysql Storage Engine

MySQL's storage engine includes: MyISAM, InnoDB, BDB, Memory, MERGE, EXAMPLE, Ndbcluster, ARCHIVE, CSV, Blackhole, Federated

4. Delete a table

Syntax: DROP table name

drop table list;

5. Modify the table name

Syntax: altertable table name rename new table name;

altertable list rename lists;

6. Modify the field types in the table

Syntax: altertable table name modify new field type for field name fields to modify

altertable lists Modifyid char (40);

7. Modify the field names and types in the table

Syntax: Altertable table name change original field name new field name new field type

altertable lists change id ids int (40);

8. Add a field to the table 1. Add a field to the table

Syntax: Altertable table name Add field name fields type

altertable lists add sum int (50);

2. Add a field to the first row of the table

Syntax: ALTER TABLE table name add field name segment type First

#第一行添加字段

ALTER TABLE lists add sum int (n) First;

3. Add a field after a field

Syntax: ALTER TABLE table name add field First Name field type after Su

#字段su后添加字段

ALTER TABLE lists add so char (+) after Su;

9. Delete a field in a table

Syntax: ALTER TABLE table name drop field name

ALTER TABLE lists drop so;

Iii. record 1. Insert a record in a field

Syntax: INSERT into table name values (1, ' Zhangshan ', 2)

#后面记录指定为空

Insert into lists values (, ' Shanshi ', null,null);

#插入多条记录中间用分号隔开

Insert into lists Valus (, ' Lisi ', null,null), (2,3, ' Siji ', 1, 1);

#指定字段插入

Insert into lists (Su,ids) values (+);

2. Records in the query table

Syntax: SELECT * FROM table name

#* represents all records

select * from Lists;

#查询ids中记录

Select IDs from lists;

#查询ids, records in SU

Select Ids,su from Lists;

#查看指定数据库中表内容

SELECT * from Food.lists; `

3. Delete records from a table

Syntax: Delete from table name where field name =xx

Delete from lists where ids=2;

#删除字段name记录为空的行

Delete from lists where name is null;

4. Update records

Syntax: Update table name set field name 1=xx where field name 2=xx

Update lists set Ids=1 where Name=null;

#所有都变成2

Update lists set ids=2

#同时更新多个字段用分号隔开

Update lists set ids=3,name= ' Lisi ' where su=1;

Four. SQL Basic statement Query 1. Multi-field Query

Syntax: Select field 1, Field 2 from table name

Select Ids,name from Lists;

2. To repeat the query

Syntax: SELECT DISTINCT field 1, Field 2 from table name

Select distinct ids,name from lists;

3. Using and and or multi-criteria queries

Syntax: Select field 1, Field 2 from table name where field 1>3 and field 2<5

Select Ids,name from lists where ids>3 and name <5;

Select Ids,name from lists where ids>3 or name <5;

#and与or同时存在时, the first and the left and right, the logic and the first execution

SELECT * from Lists where ids=3 and (su=1or name =5);

4.mysql Case-sensitive query

Syntax: SELECT * FROM table name where binary field 1= ' xxx '

Binary is case-sensitive

Select *from lists where binary name= ' LK '

5. Sort queries

Syntax: SELECT DISTINCT field 1, Field 2 from table name by field name

#默认是升序排列

Select distinct ids,su from lists to IDSASC;

#降序排列

Select distinct ids,su from lists to Idsdesc;

6. Query Reference aliases

Syntax: SELECT * from old table name new table name

SELECT * from lists S;

Syntax: Select old field name as new field name from table name

#指定字段别名

Select IDs as s from lists;

7.like Query

Syntax: Select field name 1 Field Name 2 ... from table name where field name 1 like '%abc ' or field name 2 like '%abc '

Select ABC abc from ABC1 where ABC like '%ABC ' or ABC like '%ABC '

Five. Common Select query

#打印当前的日期和时间

Selectnow ();

#打印当前的日期

Selectcurdate ();

#打印当前的时间

Selectcurtime ()

#打印当前数据库

Selectdatabase ();

#打印数据库版本

Selectversion ();

#打印当前用户

Selectuser ();

Six. Import and Export database 1. Import Database Method One

Create DATABASE: Mysql-e ' Create library book '-uroot-p123456

Import Database: mysql-uroot-p123456 book

Method Two

Create DATABASE: Mysql-e ' Create library book '-uroot-p123456

Import Database: Source/root/book.sql * *//database path * *

2. Export the database

mysqldump-uroot-p123456 database name > database file name

mysqldump-uroot-p123456 Book>book.sql

#导出包含建库语句

Mysqldump-uroot-p123456-b Book>book.sql

#导出所有数据库

Mysqldump-uroot-p123456-a Book>book.sql

#导出数据库到文件

SELECT * from lists outfile '/tmp/123.txt ';

Seven. Thinking and summarizing

To this main introduction, MySQL basic commands, including libraries, tables, records, SQL queries, data import and export. MySQL in the relational database is a relatively strong database, and the subsequent sharing will be launched, please look forward to!

I am Mikel pan, cloud-computing enthusiasts, regularly updated life sentiment, spiritual evolution in Mikel Pan, like I come to find me!

Blog Park Address: http://www.cnblogs.com/plyx/

Jane Book Address: http://www.cnblogs.com/plyx/

MySQL Analysis-Basic command (i)