MySQL's entry and use, SQLyog management of databases, tables and data

Getting started with MySQL

1. Download to MySQL website.

2. Install the MySQL software (be sure to put it in the English path)

3. Use

Verify success

Add the MySQL bin path to the system environment variable path

Open DOS Command Window

Window+r Open Run input cmd-input mysql-u root-p Enter password return

Example: C:\users\apple>mysql-u root-p

Enter Password: * * *

If welcome to the MySQL Monitor is displayed, the verification succeeds!

SQL (Structured Query Language, Structured Query Language) Language: the language that interacts with the database, and the language of database management.

The purpose of the SQL statement:

1. Managing Databases
2. Manage tables
3. Manage data

Build databases, tables, fields, fill data

Database Management

Querying all databases

1 show databases;

Create a database

Create Database db_name (database name); -- For example Create database dopt;

View Database

Create Database  db_name (database name); -- For example Create database dopt;

Specifying a default character set to create a database

Create Database db_name (database name) default character Set character set; -- Example Create Database dopt default character set UTF8;

Modify Database

-- For example Alter Database  defaultcharacterset GBK (character set);

Deleting a database

Drop Database database name; -- For example Drop database dopt;

Table Management

View all the tables

Show tables;

Create a table

Note (using the use DB before creating the table　　 statement using the library, or click to select the database)

CREATE Table table_name (the name of the table) (

Field1 (column name) datatype (column type),

Field2 datatype,

FIELD3 datatype

)

-for example

CREATE TABLE Dept (    INT,    varchar()    ,varchar(  - ),    DATETIME)

View table Structure

desc the name of the table; -- For example desc dopt;

Delete a table

Drop Table table name; -- For example Drop table dopt;

Modify Table

1. Add a field

Alter Table Add column field name type; -- Example Alter Table Add column TNT;

2. Delete a field

Alter Table Drop column field; -- Example Alter Table Drop column JF;

3. Modify the field type

Alter Table column field name type; -- For example Alter Table Drop column varchar (a);

4. Modify the field name

Alter  table Name column Table name of the    Original table name modified type; -- Example Alter Table column int;

5. Modify the table name

Alter Table  to The name of the new table; -- Example Alter Table  to TP;

Delete and change data

Add data

Insert fields in sequence

--Insert all fieldsInsert  intoTable nameValues(field data);--For exampleInsert  intoStudentValues(1,'Zhang San','male', -);--Insert a partial fieldInsert  intoTable name (field name)Values(data);--ExampleInsert  intoStudent (Id,name)Values(2,'John Doe');

modifying data

--modified with a conditionUpdateTable nameSetmodifying fields=ValuewhereField name=value;--ExampleUpdateStudent Sex='male' whereId=1;--Modify multiple fieldsUpdateTable nameSetModify field names=Value, field name=value, ...whereField name=value;--ExampleUpdateStudent Sex='male', age= - whereId=2;

Delete data

--Conditional DeleteDelete  fromTable namewhereField name=value;--ExampleDeleteStudentwhereId=2;--Delete Full table, can be deleted with conditionalDelete  fromtable name;--ExampleDelete  fromstudent;--Delete whole table, not conditional deletetruncate Tabletable name;--Exampletruncate TableStudent

Querying data

-- Querying all Columns Select *  from table name; -- Example Select *  from student;

Vocabulary

　　Database databases

　　Column columns

　　 Insert Insertion

Modify Modification

　　truncate Abridged

　　

MySQL's entry and use, SQLyog management of databases, tables and data