command line operation MySQL database, the use of various commands to get started example. Drawings

Log in to MySQL:
MYSQL-H host name-u user name-P
-H: This command is used to specify the MySQL hostname that the client wants to log on, and the parameter can be omitted when logging on to the current machine;
-U: The name of the user to log in;
-P: Tells the server that a password will be used to log in, ignoring this option if the user name password you want to log in is blank.


Create a database:
Create database name [other options];
Hint: You can use show databases; command to see which databases have been created.

Select the database you want to manipulate:
Use Lxk


To create a database table:
CREATE TABLE table name (column declaration);
To create the students table, for example, the table will hold the number (ID), name, Gender (sex), Age, contact phone (tel) content:
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 xxx.sql file, executing the script through the file redirection at the command prompt.
Open a command prompt, enter: mysql-d lxk-u root-p < Student.sql
directly into the command-line mode, that is, when the database is not logged in.
Note that only uppercase D and lowercase d fail. The following lxk is the database name.
Tips:
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.

Statement Explanation:
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 NULL when 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.
The following char (8) indicates that the stored character length is 8, the tinyint value range is 127 to +, and the default property specifies a value when the column value is empty.


inserting data into a table
The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:
Insert [into] table name [(column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...);
where [] The content is optional, for example, to insert a record into the students table in the SAMP_DB database, execute the statement:
INSERT into students values (NULL, "Wang Gang", "male", 20, "13811371377");

querying data in a table
Select statements are commonly used to obtain data from a database based on certain query rules, with the following basic usage:
Select Column name from table name [query condition];
For example:
To inquire about the names and ages of all students in the students table,
Input statement select name, age from students;
You can also use the wildcard character * to query all the contents of a table,
Statement: SELECT * from students;


Query by specific criteria:
The WHERE keyword is used to specify the query condition, in the form of the Select Column name from the table name where condition;
To query all gender-based information as an example, enter a query statement: SELECT * from students where sex= "female";
1. Query for all people older than 21 years: SELECT * from students where age >= 21;
2. Query everyone with the word "King" in the name: SELECT * from students where name is like "% king";
3. Query ID less than 5 and age greater than 20 for all information: SELECT * from students where id<5 and age>20;


updating data in a table
The UPDATE statement can be used to modify the data in the table, using the following basic form:
Update table name set column name = new value where update condition;
1. Change the phone number with ID 2 to the default "-": Update students set tel=default where id=2;
2. Increase the age of all 1:update students set age=age+1;
3. Change the name of mobile phone number 13288097888 to "Zhang Weipeng" and change the age to 19:update students set name= "Zhang Weipeng", age=19 where tel= "13288097888";


Delete data from a table
The DELETE statement is used to delete data from a table, with the following basic usage:
Delete from table name where delete condition;
1. Delete the line with ID 2: Delete from students where id=2;
2. Delete all data older than 21 years: Delete from students where age=22;
3. Delete all data from the table: delete from students;



modifications to the table after creation
The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:

Adding Columns
Basic form: ALTER TABLE name add column list data type [after insertion position];
1. The last appended column of the table Address:alter table students add address char (60);
2. Insert the column after the column named Age birthday:alter table students the Add birthday date after age;

Modifying Columns
Basic form: ALTER TABLE name change column Name column new name new data type;
1. Rename the table Tel column to Telphone:alter table students Change Tel telphone char (+) Default "-";
2. Change the data type of the Name column to char (+): ALTER TABLE students changes name name char (+) not null;


Delete Column
Basic form: ALTER TABLE name drop column name;
Example: Delete birthday column: ALTER TABLE students drop birthday;


Renaming a table
Basic: ALTER TABLE name rename new table name;
Example: Renaming the students table for Workmates:alter table students rename workmates;


Delete entire table
Basic form: drop table name;
Example: Deleting a workmates table: drop-table workmates;


Delete Entire database
Basic form: drop database name;
Example: Delete a samp_db database: Drop the DB samp_db;


finally: Create a new database user and password, and set all permissions.
Create user ' lxk ' @ ' localhost ' identified by ' lxk ';
Grant all privileges on lxk.* to ' lxk ' @ ' localhost ';
Flush privileges;
I don't know exactly how to explain it, but that's how it's used. Keep up with my personal understanding.
Above is a new user, @ Identify the local database, identified by followed by the password.
The grant word is the meaning of authorization, all, full, privileges, privilege. Lxk.* estimates are all the databases under the LXK user,

Create user ' [email protected] ' [Identified by ' PASSWORD '] where the password is optional;
Description: The method created by the user is only connected to the database permissions, need to follow-up authorization;
Note: The user and the @ after the host address is one, with a semicolon connection, or will error.
ERROR 1396 (HY000): Operation create user failed for ' remote ' @ '% '
Use example: CREATE user ' [email protected] ' Identi Fied by "123";

Grant all privileges the lxk.* to ' lxk ' @ ' localhost ';
The user lxk to localhost is assigned the ability to perform all operations on the database lxk all tables, and the password is set to 123. After the

completes the user's creation, please remember to refresh the System permissions table;
Flush privileges;

Here are the links I refer to:
MySQL three ways to create users
http://blog.csdn.net/huaishu/article/details/50540814
MySQL problem solution: Access denied for user ' root ' @ ' localhost ' (using Password:yes)
http://blog.csdn.net/skywalker_leo/ article/details/47274441

To change the password method, the MySQL installation article in my MySQL classified article has been introduced.

Complements thedata type of the MySQL database. Here's a detailed look at my jdbc, JDBC full concept, which tells the various data types of MySQL database.
MySQL has three major classes of data types, number, date \ Time, string, and more detailed sub-types in the three categories:

Number Type
Integers: tinyint, smallint, mediumint, int, bigint
floating-point numbers: float, double, real, decimal
dates and times: date, Time, DateTime, timestamp, year
String type
String: char, varchar
text: tinytext, Text, Mediumtext, Longtext
Binary (can be used to store pictures, music, etc.): Tinyblob, Blob, Mediumblob, Longblob

command line operation MySQL database, the use of various commands to get started example. Drawings