Basic knowledge of MySQL

I. Introduction to the Database

What is a database?

Data warehouses, such as: In an instance of ATM we create a DB directory called a database

What is MySQL, Oracle, SQLite, Access, MS SQL Server, and more?

They are database software, the main function is two parts: 1, to save data to memory or file. 2. Accept specific commands to manipulate the data

What is SQL?

The above question says that the database accepts a specific command, which is written in SQL, which is the abbreviation for the Structured Query Language (structured Query Language), a language specifically for interacting with the database.

Second, the installation of the database

Windows version

1. Download

MySQL Community Server 5.7.16 http://dev.mysql.com/downloads/mysql/

2. Decompression

If you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: C:\mysql-5.7.16-winx64

3. Initialization

After MySQL extracted the bin directory there is a lot of executable files, execute the following command to initialize the data:

CD C:\mysql-5.7.16-winx64\bin mysqld--initialize-insecure

4. Start the MySQL service

Execute the command to start the MySQL service

# Go to executable directory CD C:\mysql-5.7.16-winx64\bin # start MySQL service mysqld

5. Start the MySQL client and connect to the MySQL service

The default password is not set to the root account due to the "mysqld--initialize-insecure" command used during initialization

# Enter the executable directory CD C:\mysql-5.7.16-winx64\bin # connection MySQL server mysql-u root-p # Prompt Please enter the password, go directly

Enter the entry to see that the installation was successful:

、

To this end, the MySQL server has been successfully installed and the client is ready to connect, and in the future to operate MySQL, it is only necessary to repeat the 4, 5 steps. However, in the 4, 5 steps of repeated access to the executable directory is cumbersome, if you want to be easy to operate later, you can do the following operations.

Adding environment variables

Add the MySQL executable to the environment variable to execute the command

"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box to find the variable named path of a row, double-click"-"to append the MySQL bin directory path to the variable value, and split" such as: \ C Program Files (x86) \parallels\parallels tools\applications;%systemroot%\system32;%systemroot%;%systemroot%\ System32\wbem;%systemroot%\system32\windowspowershell\v1.0\; C:\Python27; C:\Python35; C:\mysql-5.7.16-winx64\bin

This way, when you start the service and connect later, you only need to:

# to start the MySQL service, enter Mysqld # on the terminal to connect the MySQL service in terminal input: Mysql-u root-p

. Making a MySQL service into a Windows service

The previous step solves some problems, but is not exhaustive, because the current terminal will be stuck while executing "MYSQD" to start the MySQL server, then do the setup to resolve the problem:

# make MySQL Windows service, execute this command at Terminal: "C:\mysql-5.7.16-winx64\bin\mysqld"--install # Remove the MySQL Windows service and execute this command at the terminal: "C \ Mysql-5.7.16-winx64\bin\mysqld "--remove

After registering as a service, you only need to execute the following command when you start and close the MySQL service later:

# Start MySQL service net start MySQL # close MySQL service net stop MySQL

Linux version

Installation

Yum Install Mysql-server

Start

Mysql.server start

Link

Connection:    mysql-h host-u user-p     Common errors: Error        2002 (HY000): Can ' t connect to local MySQL server through socket '/TM P/mysql.sock ' (2), it means that the MySQL server daemon (Unix) or service (Windows) was not running. Quit: Quit    or Contro L+d

Third, the operation of the database

1. Display Database

SHOW DATABASES;

Default database: MySQL-user rights-related data test-for user test data information_schema-mysql itself schema-related data

2. Create a database

# utf-8create database name DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # gbkcreate database name DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;

3. Using the Database

Use db_name;

4. User Management

Create user ' username '    @ ' IP address ' identified by ' password ', delete user ' username '    @ ' IP address ', modify user    rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';; Modify password    set password for ' username ' @ ' IP address ' = password (' new password ')  PS: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)

5. Authorization Management

Show grants for ' user ' @ ' IP address '                  --View permissions grant  permissions on database. Table to   ' user ' @ ' IP address '      --Authorize revoke permissions on database. Table from ' user ' @ ' IP Address '      --Cancel permission

for Permissionsfor Databasefor IPExample

Note: Flush privileges, which reads the data into memory so that it can take effect without restarting.

Iv. Processing of data sheets

1. Create a table

View Codeparameter description When creating a table

2. Delete a table

DROP table Name

3. Clear the Table

Delete from table name TRUNCATE TABLE table name

4, modify the table

Add Column: ALTER TABLE table name add column name type Delete column: ALTER TABLE table name drop Column name Modify column:        ALTER TABLE table name modify column name type;  --type ALTER TABLE name change original column name        new column name type;--column name, type  add primary key:        ALTER TABLE table name add primary key (column name); Delete PRIMARY key:        alter TABL e table Name drop primary key;        ALTER TABLE name  modify  column name int, drop primary key;  Add foreign key: ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field); Delete foreign key: ALTER TABLE name drop for Eign key FOREIGN Key name  modifies default value: ALTER TABLE TESTALTER_TBL alter I SET default 1000; Delete defaults: ALTER TABLE TESTALTER_TBL alter I DROP D Efault;

5. Basic data type

MySQL data types are broadly divided into: numeric, time, and string

View Code

For more information:

• Http://www.runoob.com/mysql/mysql-data-types.html
• Http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

V. Operation of table contents

1, increase

Insert into table (column name, column name ...) values (value, value, Value ...) Insert into table (column name, column name ...) values (value, value, Value ...), (value, value, Value ...) Insert into table (column name, column name ...) select (column name, column name ...) from table

2. By deleting

Delete from table delete from table where id=1 and Name= ' Alex '

3, change

Update table Set name = ' Alex ' where id>1

4. Check

SELECT * FROM Table SELECT * from table where ID > 1select nid,name,gender as GG from table where ID > 1

5. Other

A, condition select * from table where ID > 1 and name! = ' Alex ' and num = 12;     SELECT * FROM table where ID between 5 and 16; SELECT * FROM table where ID in (11,22,33) select * FROM table where ID not in (11,22,33) select * FROM table where ID in (Selec   T nid from table) b, wildcard select * from table where name like ' ale% '-ale begins with all (multiple strings) select * FROM table where name like ' Ale_ '            -Ale begins with all (one character) c, restricts select * from table limit 5;          -First 5 lines select * FROM table limit 4, 5; -5 rows starting from line 4th select * FROM table Limit 5 offset 4-5 rows starting from line 4th D, sort select * from table ORDER BY column ASC-according to column  "From small to large arrange select * from table ORDER BY column desc-rank from largest to smallest according to" column "SELECT * from Table order BY column 1 desc, column 2 ASC-according to "Column 1" is arranged from large to small, if same column 2 from small to large sort e, group Select Num from table GROUP by NUM Select Num,nid from table GROUP by Num,nid Select Num  , nid from table where nid > Group by num,nid Order Nid desc Select num,nid,count (*), SUM (score), Max (score), Min (score) From table GROUP by Num,nid SelectNum from table GROUP by NUM has max (ID) > 10 Special: Group by must be in where, before order by F, no correspondence of the list does not display select A.num, A . Name, b.name from A, Where A.nid = B.nid No correspondence does not show select A.num, A.name, b.name from A inner JOIN B o     n A.nid = B.nid A table All display, if there is no correspondence in B, then the value is NULL select A.num, A.name, B.name from a left join B on a.nid = B.nid    b table all display, if there is no correspondence in B, then the value is NULL select A.num, A.name, b.name from A right join B on a.nid = B.nid g, combined combination, automatic processing coincident Select nickname from a union select name from B combination, do not process coincident select nickname from a UNION ALL Sele CT name from B

-Reprinted from Www.3cscool

Basic knowledge of MySQL