Basic operations of MySQL

Take an Excel file as an example: Database: can be viewed as an entire Excel file. Data sheet: Think of it as a worksheet in an Excel file.

  • Row: Can be thought of as a row in a worksheet
  • Column (field) : Can be viewed as a total column of a worksheet (COL)

Library operation

SHOW DATABASES; MySQL > CREATE DATABASE; DROP DATABASE name; Delete database USE database name; Switching databasesCopy the code

Table operation

SHOW TABLES; Display all the TABLE names in the current database. CREATE TABLE TABLE name CREATE a TABLE (column name) in the current database1Data type [NOT NULL AUTO_INCREMENT], column name2Data type, column name3The data type is.... .PRIMARY KEY(Primary key field name)
); CREATE TABLE creates a TABLE (column name) under the current database1Data type PRIMARY KEY [NOT NULL AUTO_INCREMENT], column name2Data type, column name3The data type is.... ,); The other columns default todefault null, so there is no need to write common data types:int(integer),char(fixed-length characters), VARCHAR (indefinite characters). The primary key is usually the column in which the ordinal number resides (the primary key cannot be repeated). DESC table name; DROP TABLE [IF EXISTS] Specifies the name of a TABLE. Delete a tableCopy the code

SQL > create table xg; create table xg; create table xg;

The Id field,11Varchar = vARCHAR; vARCHAR = vARCHAR;20Length Password field,charType,32Length Standard Query Language (SQL) statements:Create table xg(
	Id int(11) not null auto_increment,
	Username varchar(20),
	Password char(32),
	Primary key(id)
);
Copy the code



View table structure



Delete table

Record/field operations

Add records

grammar1: INSERT INTO table name VALUES1And the value2,...). ; grammar2: INSERT INTO table name (column1Column,2,...). VALUES (VALUES1And the value2,...). ;Copy the code

Case studies: to add a record in data table xg table username to zhangsan, the password is 123456 (encryption results E10ADC3949BA59ABBE56E057F20F883E)

SqlStatement:insert into xg (username,password) values(" zhangsan ", "E10ADC3949BA59ABBE56E057F20F883E ')Copy the code



Requires a one-to-one correspondence between the preceding column names and values.

Update record

Syntax: UPDATE the table nameSETColumn name1 =The new value1The column name2 =The new value2...WHEREColumn name=A certain value;Copy the code

Case: use the update statement to update id greater than or equal to 2 records, the password is changed to: 25 f9e794323b453885f5181f1b624d0b

SQLStatement: Update Xgset password = ‘25F9E794323B453885F5181F1B624D0B 'where id > = 2;
Copy the code



In the future, when performing SQL operations that affect the number of rows, you must pay attention to whether the condition is incorrectly written or missing.

Query log

SELECTColumn name1The column name2...FROMThe name of the tableWHEREConditions;SELECT * FROMThe name of the tableWHEREConditions;Copy the code

Example: Query the newly added record

Select * from user where id=2;

Select username,password from xg where id = 2;



Query all:

Delete records

DELETE FROMThe name of the tableWHEREColumn name=Value;Copy the code

Delete from xg where id = 2; Delete from xg where id = 2;

Enter current password for root (Enter for None): Enter the password of the current root user. If you do not press Enter, the root user is not the root user of Linux.

Set root password? Is the root password set? Password to be set: User-defined

Remove anonymous users? To remove anonymous users, select Remove (Y).

Disallow root login remotely? Disable root remote login (default not allowed)

Remove test database and access to it? Whether to remove the test database (not recommended)

Reload privilege tables now? Whether to reload the permissions table (we recommend to reload the permissions after we change the information related to mysql user)

Issue to be resolved: allow mysql remote login

A. Access the database and select the mysql database.

B. Execute SQL statement:select host,user from user;



C. Change the host value of one of the records to %, indicating that login is allowed anywhere



D. Refresh the permission table or restart mysql

Refresh permission: mysql> flush privileges;
Copy the code



E. Navicat Login succeeds