Shulou(Shulou.com)06/01 Report--

This article mainly introduces you to manage the Mysql database of several commonly used simple commands, the contents of the article are carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the management of the Mysql database under several commonly used simple commands.

I. several stages of data management

1. Manual management stage

Features: data can not be saved for a long time, data can not be shared, and the program itself does not manage data independently.

2. File system phase

Features: data can be saved for a long time, data can be managed by file system, data redundancy is large, sharing is poor, data independence is poor.

3. Database system phase

Features: data implementation, structured data, shared data independence, strong data granularity, database

1. Several concepts involved in database system.

DB:Database (database) DBMS:Database Management System database management system DBS: database system computer hardware and software DBDBMSDBA (database administrator)

2. Common database management systems

* Oracle Oracle * MySQL Oracle * Sql Server Microsoft (mainly used in windows environment) * DB2 IBM Corporation * SQLlite for mobile development

Generally use MySQL

3 、 MySQL

Why choose MYSQL?

(1) Open source (2) free (3) cross-platform (4) good performance (5) many users, used in many software companies

4. Installation of MySQL data

(1) obtain the MYSQL installer

Http://www.mysql.com/downloads/

MySQL Community Edition

(2) two installation packages

Msi package (installation version) decompressed version

(3) install and uninstall the decompressed version (it is best for beginners to use the unzipped version)

After decompression, bin/mysql.exe client program bin/mysqld.exe CVM program bin/mysqldump.exe data backup program initialization (only need to execute once) bin/mysqld-- initialize-- console initialization will generate data folder, in which to save the system and user data note to record the temporary password w8efencrypy5nZg to start the database service bin/mysqld-- console port is 3306 you can use Ctrl+C to stop the service program Or close the window directly.

(4) use the client connection (open the server first)

Use client connection bin/mysql-u username-p

Mysql has a root (database administrator user) when initializing

Bin\ mysql-uroot-p

The mysql > prompt appears after the correct login. To exit, use the quit command.

Change the password (only need to be executed once)

Alter user 'root'@'localhost' identified by' root'

(5) configure the bin directory of mysql to the environment variable (only need to be executed once), and configure it in path

(6) install mysql as a system service (only need to be executed once) bin\ mysqld install service name

If the service name is not provided, the default is MySQL

If you want to uninstall the service: sc delete service name

If insufficient permissions occur during service installation or removal, run cmd as an administrator (black window)

5. Directory structure of MYSQL

Bin:mysql application directory Data: generally data directory Docs: document Include: header file Lib: library file Mysql-test: test directory Share: store character sets and different language information Sql-bench: stress testing tool Mydefault.ini: mysql configuration file

6. Basic concepts of database

Database: a database management system (DBMS) can manage multiple database tables: there are multiple rows of data in a database: multiple rows of data in a table (fields): a row of data has multiple column views: virtual table index: used to speed up access to table data primary key: the primary key field is not allowed to be empty and uniquely identifies a row of foreign keys in the table. A relational transaction between two specified tables: a program unit that updates data

7. Build a database

Set the default character encoding

One is to put character coding on the statement of building the database.

The other is to add a configuration file when starting the service

(1) build a library SQL statement (run at the mysql prompt)

Create database database name character set utf8mb4

The utf8 characters in mysql are not complete. Utf8mb4 is a complete version.

Delete database

Drop database database name

View database building information

Show create database database name

(2) to modify the global settings, add the my.ini configuration file to the mysql decompression directory and enter it (recommended)

[mysqld] character-set-server=utf8mb4

Stop the old service, delete the old service, install the new service, start the new service

Net stop mysqlsc delete mysqlmysqld installnet start mysql III. MySQL database operation command 1. Table creation (table)

There are multiple tables in a library, and there are multiple pieces of data in each table

The table is divided into rows (row horizontal) and columns (column vertical)

Student ID, name, gender

1 Zhang Sannan

2 Li Si Nu

...

Selection of library

Use library name

Create table create table table name (column 1 type, column 2 type,...); create table student (id int,name varchar (10), sex char (1))

Integer type: tinyint (1 byte), smallint (2 bytes), int (4 bytes), bigint (8 bytes)

Unsigned digit tinyint unsigned (0,255)

Floating point type: float, double

Fixed point decimal: decimal (total places, decimal places)

Decimal (10,2) has two decimal digits and a maximum of 8 integer digits.

Character type

Char (length) char (10) means to store up to 10 characters with fixed length and high efficiency.

Varchar (length) varchar (10) means to store up to 10 characters and become longer

When storing "abc" and "abc", the length is not enough, fill it up with spaces.

When storing "abc" and "abc", space can be saved according to the actual length of storage.

Date Typ

Datetime

Timestamp

Insert data insert into table name (column 1, column 2,... Column n) values (value 1, value 2,... Value n)

Note: the number of values should be the same as the number of columns

Insert into student (id,name,sex) values (1, 'Zhang San', 'male')

Query data

Syntax: select column 1, column 2,... From table name; select id, name, sex from student

View all libraries

Show databases

View all tables show tables

Unique primary key

There can be only one primary key per table

The value of the primary key must be a unique and non-empty create table table name (column 1 type primary key, column 2 type,.)

Delete table syntax: drop table table name

Create table student (id int primary key, name varchar (10), sex char (1))

Insert into student (id,name,sex) values (null,'Li Si', 'male')

Self-increment column, used to solve the problem of primary key conflict

Add: auto_increment after the primary key column

1 2 3 4....

Because the id column is maintained by the database, there is no need to assign a value to the id column after adding the column

Create table student (id int primary key auto_increment, name varchar (10), sex char (1))

Insert multiple records at a time (unique to mysql)

Insert into student (name,sex) values ('Li Si', 'male'), ('Wang Wu', 'male'), ('Zhao Liu', 'male')

Query all columns

Select from student

Mysql will translate it into: id,name,sex

Delete record (only delete data, not delete table) delete from where condition

Delete table (delete table and data together)

Drop table table

Delete only the records of id=1004

Delete from student where id=6

Create table a (birthday datetime)

Insert into a (birthday) values ('1988-5-4')

Insert into a (birthday) values ('1988-5-4 16bureau 3900')

Two ways to set the self-increment column starting from 1001

1. When creating a table

Create table b (id int primary key auto_increment,name varchar (20)) auto_increment=1001

two。 Modify the table

Create table b (id int primary key auto_increment,name varchar (20)); alter table b auto_increment=1001;2, database syntax

SQL statement

1. DDL data definition language create database database name; create table table name (column definition); (emphasis) drop database database name; drop table table name; alter table table. (add column, modify column, delete column, rename column 8.0 only) alter user user create-create xx definition, drop-delete xx definition, alter-modify xx definition

Add column

Syntax: alter table table name add column name data type

For example: add an age column to student

Alter table student add age tinyint unsigned

Modify column

Syntax: alter table table name modify column name new type

For example, to modify the length definition of a column, the original varchar (10)

Alter table student modify name varchar (20)

Delete column

Syntax: alter table table name drop column name

Rename the column

Syntax: alter table table name rename column old column name to new column name; (mysql8.0 only)

2. DML (data manipulation language) (key) insert

Syntax 1:

Insert into table name (column...) Values (value...); insert a row

Syntax 2:

Insert into table name (column...) Values (value...), (value...); insert multiple lines of create table student2 (id int primary key, name varchar (20), sex char (1))

Syntax 3: query from Table 1 and insert the query results into Table 2

Insert into Table 2 select * from Table 1

If the structure of the two tables is different, you can add a specific column name after the select to match the columns of the new table

For example:

Create table student3 (id int primary key, name varchar (20)); insert into student3 select id,name from student

Load data

You can import the contents of an external text file into a database table

Syntax: load data infile 'file path\ file name' into table table name

Create table hero (id int primary key, name varchar (10), loc varchar (10), sex char (1), birth int, death int, power int)

For the load data command to take effect, you must modify the settings:

[mysqld] character-set-server=utf8mb4secure-file-priv=

Where secure-file-priv defaults to a null value, indicating that loading of files is not allowed

Can be changed to a specific directory name, indicating that files can only be loaded from this directory

If changed to "", the file can be loaded from any directory.

For example, before loading heroes.txt, store the data in hero:

Load data infile'e:\\ heroes.txt' into table hero

If the column delimiter in the file is, not the default\ t key, you need to specify the delimiter with fields TERMINATED BY

Load data infile'e:\\ person.txt' into table person fields TERMINATED BY','; sourcesource file path / file name the file content must be legal the difference between sql statement and load: no quotation marks, recommended / delimited path, file encoding consistent with operating system encoding (gbk)

Update update

Syntax:

Update table name set column name = new value where condition; update person set sex=' male'; / change all recorded gender to male update person set sex=' male 'where id=1; / / only change the gender of id=1 to male

Delete deletion

Syntax:

Delete from table name; / / Delete all records in the table (dangerous operation) delete from table name where condition; / / Delete records that meet the criteria

Select query

Syntax:

Select column name. From table where condition group by grouping condition having grouping filter condition order by sort condition limit; condition where= equivalent match! = unequal match > greater than

< 小于>

= greater than or equal to = value 1 and column = 85 and power from small to large asc

Descending order-> from big to small desc

Select * from hero order by power desc limit 10

Multi-column sorting: sort condition 1, sort condition 2.

First sort according to condition 1, and then sort according to condition 2 if the value in condition 1 is the same.

Limit the number of results returned to limit m; / / return a maximum of m results limit nm; / / return a maximum of m results, where n represents the starting subscript, and the subscript starts at 0

Often used to implement paging applications, assuming 10 pages per page

Page 1, limit 0J10; page 2, limit 10; page 3, limit 20, 10; grouping condition select count (*), max (sal), min (sal), sum (sal), avg (sal), deptno from emp group by deptno

Count (*) means to find the number of each group, max (column), maximum value, min (column), minimum value, sum (column), sum, avg (column), average value.

After grouping

Grouping conditional columns and group functions can only appear in select clause, other columns cannot appear in select, grouping conditional columns and group functions can only appear in order by clause, and other columns cannot appear in order by.

For example:

Select deptno,max (sal), ename from emp group by deptno; / / ename does not comply with the previous regulations

Select deptno,max (sal) from emp order by ename; / / error having is also filtered where > group by > having > select > order by > limit / / sql statement execution order select count (*), deptno from emp where count (*) > = 5 group by deptno; / / because where executes first, there is no grouping at this time, and the number of errors select count (*), deptno from emp group by deptno having count (*) > = 5 is not known.

Sometimes the filter criteria can be written either on where or on having (where preferred)

Select count (*), deptno from emp where deptno=10 or deptno=30 group by deptno;select count (*), deptno from emp group by deptno having deptno=10 or deptno=30; multi-column grouping (understand)

Multiple columns with the same values are divided into one group.

Group by column 1, column 2... select count (*), deptno,job from emp group by job,deptno

When multiple columns are grouped, the order of the columns does not affect the result

Multiple table structure and join query select. From Table 1 inner join Table 2 on connection condition where group by having order by limit;select empno,ename,sal,emp.deptno,dept.deptno,dname,loc from emp inner join dept on emp.deptno = dept.deptno

Table 1 Table 1 alias

Select empno,ename,sal,e.deptno,d.deptno,dname,loc from emp e inner join dept d on e.deptno = d.deptno

+-+

| | empno | ename | sal | deptno | | deptno | dname | loc |

+-+

| | 7369 | SMITH | 800.00 | 20 | | 20 | RESEARCH | DALLAS | |

| | 7499 | ALLEN | 1600.00 | 30 | | 30 | SALES | CHICAGO | |

| | 7521 | WARD | 1250.00 | 30 | | 30 | SALES | CHICAGO | |

| | 7566 | JONES | 2975.00 | 20 | | 20 | RESEARCH | DALLAS | |

| | 7654 | MARTIN | 1250.00 | 30 | | 30 | SALES | CHICAGO | |

| | 7698 | BLAKE | 2850.00 | 30 | | 30 | SALES | CHICAGO | |

| | 7782 | CLARK | 2450.00 | 10 | | 10 | ACCOUNTING | NEW YORK | |

| | 7788 | SCOTT | 3000.00 | 20 | | 20 | RESEARCH | DALLAS | |

| | 7839 | KING | 5000.00 | 10 | | 10 | ACCOUNTING | NEW YORK | |

| | 7844 | TURNER | 1500.00 | 30 | | 30 | SALES | CHICAGO | |

| | 7876 | ADAMS | 1100.00 | 20 | | 20 | RESEARCH | DALLAS | |

| | 7900 | JAMES | 950.00 | 30 | | 30 | SALES | CHICAGO | |

| | 7902 | FORD | 3000.00 | 20 | | 20 | RESEARCH | DALLAS | |

| | 7934 | MILLER | 1300.00 | 10 | | 10 | ACCOUNTING | NEW YORK | |

+-+

+-+

| | deptno | dname | loc | |

+-+

| | 10 | ACCOUNTING | NEW YORK |

| | 20 | RESEARCH | DALLAS |

| | 30 | SALES | CHICAGO |

| | 40 | OPERATIONS | BOSTON |

+-+

Table 1 inner join Table 2 on join conditions (inner join: records of two tables must fully meet the join conditions before they appear in the final result) Table 1 left outer join Table 2 on join conditions (left outer join) Table 1 right outer join Table 2 on join conditions (right outer join) select empno, ename, e.deptno, d.deptno, d.dname, d.loc from emp e left outer join dept d on d.deptno=e.deptno

The table on the left side of the connection with left outer join appears in the result regardless of whether it is connected to a record or not.

Records that meet the connection conditions have the same effect as the internal connection.

For records that do not meet the join conditions, the columns corresponding to another table are null values

The table on the right side of the connection with right outer join appears in the result, regardless of whether it is connected to a record or not.

Outer can be omitted

The equivalent writing of join query the equivalent writing of join in (important) select. From Table 1, Table 2 where connection conditions; select e.empnoree.enameree.deptnored.deptnored.dname from emp e, dept d where e.deptno=d.deptno

Mysql's unique (understanding) select. From Table 1 inner | left join Table 2 using (deptno); / / the join column names of the two tables should be the same select e.empnoree.enamedepartment e.deptnowery d.deptnored.dname from emp e inner join dept d using (deptno); common functions

Select count (*) from emp; / / find the number of rows in the whole table

Select max (sal) from emp; / / find the maximum wage of the whole table

Bit Functions bit operation function

Comparison operators comparison operator

Control flow functions process control

Date and Time Functions date function

Year () intercepts the year month () date () date_add (date time interval); the syntax of the time interval: interval n units select empno,ename,date_add (hiredate, interval 1 month), hiredate from emp; plus one month select empno,ename,date_add (hiredate, interval 3 day), hiredate from emp; plus 3 days SELECT EXTRACT (DAY_MINUTE FROM '2009-07-02 13 date 02date 03') Extract the day-to-minute portion of the date select now () to get the current time

Encryption Functions encryption

Information Functions

Logical operators logical operator

The remaining functions of Miscellaneous Functions

Numeric Functions mathematical function

Rand () generates a random decimal from [0. 0-1. 0], less than 1. 0 floor () rounding off the decimal round ()

String Functions string function

Left (string, n) n represents the character to be intercepted from the left lower () upper () substr (string, subscript, length) the subscript calculates the string length from 1 example: select * from hero where char_length (name) = 4; Note: length can only be used to find the length of the string, not the length of Chinese characters.

Export data cmd > mysqldump-uroot-p library name > > file to save. Sql (reverse operation of source with sql statement inside)

Save the data in the table to a text file (inverse operation of load data infile)

Select * from table into outfile 'file path\ file name'

After reading the above several common simple commands about managing Mysql database, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.