MySQL Operation statement

Log in to MySQL

MySQL - h - u - P

Quit MySQL

Exit
Or
quit;

Create a database

Create Database [ other options ];

CREATE database article character set UTF8;   Create a database named article, and set the Utf-8 encoding for him

Select the database you want to manipulate 1. Specify when logging in to the database

- D - H - u - P

2. Specify after Login

 use database name;

Create a data table

Create  table name (column declaration);

eg:  Create a students table in which the number (ID), name, Gender (sex), Age, contact telephone (tel) are stored in the table:
CREATE TABLE Students (
ID int unsigned NOT NULL Auto_increment primary key,
Name Char (8) is not NULL,
Sex char (4) NOT NULL,
Age tinyint unsigned is not NULL,
Tel char (+) NULL default "-"
);

　

For some longer statements at the command prompt it may be easy to get wrong, so we can write the statement through any text editor and save it as a createtable.sql file, executing the script through the file redirection at the command prompt.

Open a command prompt, enter: mysql-d samp_db-u root-p < Createtable.sql

(Hint: 1. If connecting to a remote host, add the-H command; 2. The Createtable.sql file must specify the full path of the file if it is not in the current working directory. )

　　

　　 explanation of words:

　　Create Table tablename (columns) is the command that creates a database table, the name of the column and the data type of the column will be completed in parentheses;

In parentheses, 5 columns are declared, ID, name, sex, age, and Tel are the names of each column followed by the data type description, separated by commas (,) between the columns and column descriptions;

The "ID int unsigned NOT NULL auto_increment primary key" line is described:

• • "id" is the name of the column;
  • "int" Specifies that the column is of type int (with a value range of 8388608 to 8388607), which is then decorated with "unsigned" to indicate that the type is unsigned, at which time the column has a value ranging from 0 to 16777215;
  • "Not NULL" indicates that the value of the column cannot be empty and must be filled, and the default can be null if the property is not specified;
  • The "auto_increment" needs to be used in an integer sequence, and the effect is that if the column is NULLwhen inserting the data, MySQL will automatically produce a unique identifier value that is larger than the existing values. Only one such value can be in each table and the column must be an indexed column.
  • "PRIMARY key" means that the column is the primary key of the table, the value of this column must be unique, and MySQL will automatically index the column.

Inserting data into a table

Insert [into] [(column name 1, column name 2, column name 3, ...) ] VALUES (value 1, value 2, value 3, ...);

Eg:insert into students values (NULL, "Wang Gang", "Male", "13811371377"); //Omit column names, insert all values directly sequentially
Insert partial value, cannot omit list

Querying data in a table

Select  from [ query conditions ];
Eg:select name, age from students; Name and age of all students in the enquiry form
　　select*from students; Querying all fields in a table

The WHERE clause does not only support the "where Column name = value" Query form, which is named equal to the value, and is supported for the operators of general comparison operations such as =, >, <, >=, <,! =, and some extension operators are [not] null, in, and like Wait a minute. You can also combine queries with OR and and for query criteria.

Example:

Find information for everyone over the age of 21: SELECT * from students where ages > 21;

Query everyone with the word "King" in the name: SELECT * from students where name is like "% king";

Information for anyone with a query ID of less than 5 and older than 20: SELECT * from students where id<5 and age>20;

Updating data in a table

Update set column name =where update condition;

Update students set Tel=default where id=5;

Update students set age=age+1;

Update students set name= "Zhang Weipeng", age=19 where tel= "13288097888";

Delete data from a table

Delete  from where delete condition;


Delete from students where id=2;

Delete all data older than 21 years: Delete from students where age<20;

Delete all data from the table: delete from students;

modifications to the table after creation

    add column   alter  add  column list data type Span style= "COLOR: #ff0000" >[ after insert position       ; 
  eg: append the last column to the table address:     alter table students add address char (60 　　　　  Insert column after column named Age birthday:   alter table Students add birthday date after age; 
 
    

modifying columns
　　ALTER TABLE  Change column Name column new name new data type;

　　ALTER TABLE students Change Tel telphone char (+) Default "-";
ALTER TABLE students change name name char (+) not null;

Delete Column
　　ALTER TABLE drop column name;

　ALTER TABLE students drop birthday;

　 Renaming a table

　　alter table name rename new table name;

　　Eg: Rename the students table to workmates: ALTER TABLE students rename workmates;

　 Delete entire table

　　drop table name;

　　Eg: delete workmates tables: drop table workmates;

　 Delete Entire Database

　　drop database name;

　　Eg: delete samp_db databases: drop database samp_db;

To modify the root user password

Mysqladmin-uroot-p Password

MySQL Operation statement