MySQL (a)

About MySQL

1. What is a database?

A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than more than 60 years ago, with the development of information technology and markets, especially after the 1990s, data management is no longer just a way to store and manage data, but to transform it into the kind of data management that users need. There are many types of databases, ranging from the simplest tables with various data to large database systems that can store massive amounts of data, are widely used in all aspects.

The main databases are: Sqlserver,mysql,oracle, SQLite, Access, MS SQL Server, etc., this article mainly describes the MySQL

2. What is database management used for?

• A. Saving data to a file or memory
• B. Receive a specific command, and then perform the appropriate action on the file

PS: If you have the above management system, do not need to create files and folders themselves, but instead of directly passing commands to the above software, let them for file operations, they collectively referred to as the database management system (Dbms,database Management Systems)

MySQL Installation

MySQL is an open-source relational database management system (RDBMS), which uses the most common database management language-Structured Query Language (SQL) for database management. In WEB applications MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software.

Use MySQL must have a condition

• A. Installing the MySQL server
• B. Installing the MySQL Client
• B. "Client" Connection "server Side"
• C. "Client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate operation (increase and deletion of the search, etc.)

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

2. Installation

• For Windows installation Please refer to: http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html
• Linux under Installation: http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html

Note: The above two links have a complete installation method, the main is also reference to his installation, after installation mysql.server start MySQL service

MySQL operation

First, connect 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 sockets '/tmp/mysql.sock ' (2), it means that the MySQL serv Er Daemon (Unix) or service (Windows) is not running.

To exit a connection:

QUIT or Ctrl+d

Second, view the database, create a database, use the database to view the database: show databases;

Default database:          mysql-user rights-related data           test-Creates a database for user test data          information_schema-mysql itself schema-related data:     
               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

Using the database: Use     db1;

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

Third, user management

Create user ' username '    @ ' IP address ' identified by ' password ', delete user drop 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 ')

Note: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)

Iv. Rights Management

MySQL has the following limitations for permissions:

View Code

For 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            . Stored procedures     in the specified database for stored procedures            * *                all databases

The permissions for users and IPs are as follows:

            User name @ip address users can         only access the user name under the change IP            @192.168.1.% The   user is only allowed to access the IP segment (the wildcard% means any)            user name @%             The user may then access under any IP ( Default IP address is%)

1. View Permissions:

2. Authorization

Grant  permissions on the database. Table to   ' user ' @ ' IP address '

3. Cancellation of Authorization

Revoke permissions on the database. Table from ' user ' @ ' IP address '

The authorization examples are as follows:

Grant all privileges the DB1.TB1 to ' username ' @ ' IP ' Grant select on db1.* to ' user name ' @ ' IP ' Grant select,insert on * * to ' username ' @ ' IP ' re Voke Select on DB1.TB1 from ' username ' @ ' IP '

MySQL table operations

1. View Table

Show tables;                    # View all tables in the database

SELECT * from table name;             # View all the contents of the table

2. Create a table

CREATE TABLE table name (    whether the column name  type  can be empty,    whether the column name  type  can be empty) Engine=innodb DEFAULT Charset=utf8

Come up with an example of a good explanation

CREATE TABLE ' tab1 ' (  ' nid ' Int (one) not null auto_increment,                   # NOT NULL means cannot be null, auto_increment indicates self-increment  ' name ' varchar (255) Default Zhangyanlin,                 # Default means  ' email ' varchar (255),  PRIMARY KEY (' nid ')                                      # Set the NID column as the primary key) Engine=innodb DEFAULT Charset=utf8;

Note:

• Default value, you can specify a default value when creating a column, and automatically add a default value when inserting data if it is not actively set
• Self-increment, if you set an auto-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment in the table) Note: 1, for self-increment column, must be index (with primary key) 2, for self-increment can set step and start value
• Primary key, a special unique index that does not allow null values, and if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique.

3. Delete a table

DROP table Name

3. Clear the contents of 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 F  Oreign 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 DEFAULT;

For these operations is not looking very troublesome, a waste of time, don't panic! Have specialized software can provide these functions, operation is very simple, this software name is called Navicat Premium , everyone in the Internet download, practice practiced hand, but the following is about the table content operation or suggest to write commands to do

5. Basic data type

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

View CodeMySQL Table content Operations

Table content operation is nothing more than adding and removing changes, of course, the most is to check, and check this piece of things up, use the most difficult, of course, for the great God that is so easy, for me this small white or very difficult to use flexibly, the following we come one by one operation

1, increase

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
Cases:
    Insert into TAB1 (name,email) VALUES (' Zhangyanlin ', ' [email protected] ')

2. By deleting

Delete from table                                      # Remove table all data delete from table where id=1 and Name= ' Zhangyanlin '   # Delete id = 1 and name= ' Zhangyanlin ' row of data

3, change

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

4. Check

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

There's too many conditions to check this, and I'm going to list it. As for the combination, it depends on how well you understand it.

A, conditional judgment where

    SELECT * FROM table where ID > 1 and name! = ' Aylin ' and num = n;    SELECT * FROM table where ID between 5 and;    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 (sel ECT nid from table)

B, wildcard like

    SELECT * FROM table where name like ' zhang% '  # Zhang starts all (multiple strings)    select * FROM table where name like ' Zhang_ '  # Zhang Open All (one character) of the head

C. Restrict limit

    SELECT * from table limit 5;            -First 5 lines    select * from table limit 4,5;          -5 lines starting from line 4th    select * FROM table limit 5 offset 4    -5 lines starting from line 4th

D, Sort Asc,desc

    SELECT * FROM table ORDER BY column ASC              -arranges from small to large from "column"    select * FROM table ORDER BY column desc             -Select from large to small according to "column"    * FROM table order BY column 1 desc, column 2 ASC    -rank from largest to smallest according to "column 1", if same, sort by column 2 from small to large

E. GROUP BY

    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 has max (ID) >     Special: Group by must be in where, before order by

-this article reprinted-reprinted from: http://www.cnblogs.com/aylin/p/5744312.html

MySQL (a)