MySQL (1), MySQL (

MySQL (1), MySQL (
Mysql Introduction

1. What is a database?

A Database is a warehouse that organizes, stores, and manages data according to the data structure. It was created more than 60 years ago. With the development of information technology and the market, especially after 1990s, data management is no longer just about data storage and management, but also the various data management methods required by users. There are many types of databases. From the simplest storage of tables with various types of data to the large database systems capable of storing massive data, they have been widely used in various aspects.

Mainstream databases include sqlserver, mysql, Oracle, SQLite, Access, and ms SQL Server. This article mainly describes mysql

2. What is database management?

• A. Save the data to a file or memory
• B. receive specific commands and perform operations on the files

PS: if you have the above management system, you do not need to create files and folders on your own, but directly pass the command to the above software for file operations, they are collectively referred to as Database Management systems (DBMS, Database Management System)

Install mysql

MySQL is an open-source Relational Database Management System (RDBMS). the MySQL database system uses the most common database management language-Structured Query Language (SQL) for database management. In terms of WEB applications, MySQL is one of the best applications of RDBMS (Relational Database Management System.

Mysql must meet the following conditions:

• A. Install MySQL Server
• B. Install the MySQL client
• B. [client] connect to [server]
• C. [client] sends a command to the [server MySQL] service to receive the command and execute the corresponding operation (add, delete, modify, query, etc)

1 ,:http://dev.mysql.com/downloads/mysql/

2. Installation

• Windows Installation see http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html
• Linux installation: http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html

Note: The above two links have a complete installation method. The installation method of the supervisor is also based on its installation. After installation, mysql. server start starts the mysql service.

Mysql operations

1. Connect to the database

Mysql-u user-p example: mysql-u root-p

Common Errors are as follows:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

Exit connection:

QUIT or Ctrl + D

2. view the database, create a database, and use the database to view the database: show databases;

Default Database: mysql-user permission-related data test-used for user test data information_schema-MySQL architecture-related data to create a database:
Create database db1 default charset utf8 COLLATE utf8_general_ci; # utf8 Encoding

Create database db1 default character set gbk COLLATE gbk_chinese_ci; # gbk Encoding

Use Database: use db1;

Display all TABLES in the currently used database: show tables;

Iii. User Management

Create user 'username' @ 'IP address' identified by 'Password'; delete user drop user 'username' @ 'IP address '; modify the user rename user 'username' @ 'IP address'; to 'new username' @ 'IP address ';; change password set Password for 'username' @ 'IP address '= password ('new password ')

Note: user permission-related data is stored in the user table of the mysql database, so you can directly operate on it (not recommended)

Iv. permission management

Mysql has the following restrictions on permissions:

View Code

The database and other internal permissions are as follows:

Database Name. * All Database names in the database. The table specifies a table database name in the database. The Stored Procedure specifies the stored procedure in the database *. * all databases

Permissions for users and IP addresses are as follows:

User name @ IP address the user can only access the user name @ 192.168.1 under the changed IP address. % The user can only access the IP address segment (wildcard % represents any) username @ % the user can access it from any IP address (the default IP address is %)

1. View permissions:

Show grants for 'user' @ 'IP address'

2. Authorization

Grant permission on database. Table to 'user' @ 'IP address'

3. Cancel authorization

Revoke permission on database. Table from 'user' @ 'IP address'

The authorization instance is as follows:

Grant all privileges on db1.tb1 TO 'username '@ 'IP' grant select on db1. * TO 'username' @ 'IP' grant select, insert on *. * TO 'username' @ 'IP' revoke select on db1.tb1 from 'username' @ 'IP'

Mysql table operations

1. View tables

Show tables; # view all database tables

Select * from table name; # view all table content

2. Create a table

Create table Name (whether the column name type can be blank or whether the column name type can be blank) ENGINE = InnoDB default charset = utf8

Let's take a look at an example.

Create table 'tab1' ('nid' int (11) not null auto_increment, # not null indicates that the value cannot be blank, auto_increment indicates auto-incrementing 'name' varchar (255) DEFAULT zhangyanlin, # default indicates the DEFAULT value is 'email 'varchar (255), primary key ('nid') # Set the nid column to the primary key) ENGINE = InnoDB default CHARSET = utf8;

Note:

• Default value. You can specify the default value when creating a column. If the default value is not set during data insertion, the default value is automatically added.
• Auto-increment: if an auto-increment column is set for a column, this column is not required when data is inserted. By default, auto-increment columns are set (only one auto-increment column can be inserted in the Table). Note: 1. for auto-increment columns, it must be an index (including a primary key). 2. You can set the step size and start value for auto-increment.
• Primary Key, a special unique index, does not allow null values. If a primary key uses a single column, its value must be unique. If multiple columns are used, its combination must be unique.

3. delete a table

Drop table Name

3. Clear table content

Delete from table name truncate table name

4. modify a table

Add column: alter table name add column name type delete column: alter table Name drop column name modify column: alter 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 name add primary key (column name); Delete primary key: alter table Name drop primary key; alter table name modify column name int, drop primary key; add foreign key: alter table add constraint foreign key name from table (such: FK _ slave table _ master table) foreign key from table (foreign key field) references main table (primary key field); Delete foreign key: alter table TABLE Name drop foreign key name modify DEFAULT value: alter table testalter_tbl ALTER I set default 1000; delete DEFAULT value: ALTER table testalter_tbl ALTER I DROP DEFAULT;

Are these operations very troublesome and time-consuming? Don't worry! There is a dedicated software that provides these functions, and the operation is very simple. This software is calledNavicat PremiumYou can download and practice the table content on your own. However, we recommend that you write your own commands for table content operations.

5. Basic Data Types

MySQL data types are roughly divided into numerical values, time, and string

View Codemysql table content operations

The operations on table content are nothing more than adding, deleting, modifying, and querying. Of course, the most common operations are queries, and the most difficult to use. Of course, it is so easy for the great gods, it is still very difficult for me to use this White Paper flexibly. Let's take a look at it one by one.

1. Add

Insert into table (column name, column name ...) values (value, value ,...) insert into table (column name, column name ...) values (value, value ,...), (value, value, value ...) insert into table (column name, column name ...) select (column name, column name ...) from table
Example:
Insert into tab1 (name, email) values ('hangyanlin', 'hangyanlin8851 @ 163.com ')

2. Delete

Delete from Table # delete all data in the table delete from table where id = 1 and name = 'hangyanlin' # delete the data in the row ID = 1 and name = 'hangyanlin'

3. Change

Update table set name = 'hangyanlin' where id> 1

4. Query

Select * from Table select * from table where id> 1 select nid, name, gender as gg from table where id> 1

There are too many conditions to check this piece. I will give it a list. As for the combination, it depends on your understanding.

A. Condition judgment where

Select * from table where id> 1 and name! = 'Aylin' 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 (select nid from table)

B. wildcard like

Select * from table where name like 'zhang % '# All (multiple strings) Starting with zhang) select * from table where name like 'zhang _ '# All (one character) Starting with zhang)

C. Restrict limit

Select * from Table limit 5;-select * from Table limit 4, 5 from the first five rows; -select * from Table limit 5 offset 4 from 5 rows starting from row 4th-5 rows starting from row 4th

D. Sort asc and desc

Select * from Table order by column asc-Sort select * from Table order by column desc by column from small to large-Sort select * from Table order by column by Column by Column 1 desc, column 2 asc-sort by column 1 in ascending order. If the value is the same, sort by column 2 in ascending order.

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> 10 group by num, nid order nid desc select num, nid, count (*), sum (score), max (score), min (score) from Table group by num, nid select num from Table group by num having max (id)> 10 Special: group by must be after where, before order

-This article Reprinted from: http://www.cnblogs.com/aylin/p/5744312.html