Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the basic knowledge of mysql". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Chapter 1 installation and configuration of mysql

1 mysql database version: community version, enterprise version

2 mysql database types: standard, max, debug

3 install mysql:noinstall under windows and install it graphically

4 install mysql:rpm package, binary package and source code package under linux

5 mysql service is different from mysql data, mysql service is a series of background processes, while mysql database is a series of data directories and files; mysql database can not be accessed until the mysql service is started. This is similar to oracle.

6. Start and stop mysql service on linux platform:

Command line:

Launch: # cd / usr/bin

#. / mysqld_safe &

Close: # mysqladmin-uroot shutdowm

Mode of service:

Launch: # service mysql start

Close: # service mysql stop

Chapter II Foundation of sql

1 SQL: structure query language

2 sql classification: DDL, DML, DCL

3. Connect mysql:$ mysql-uroot-paired frames *-hlocalhost-P3306

4 your mysql connection id is 7344941: this number records the number of connections to the mysql service so far, and each new connection is automatically added by 1

5 create database: mysql > create database test1

6 check how many databases are currently available: mysql > show databases

7 functions of the default database:

Information_schema: mainly stores some database object information in the system, such as user table information, column information, permission information, character set information, partition information and so on.

Cluster: stores the cluster information of the system

Mysql: stores the user rights information of the system

Test: a test database created automatically by the system, which can be used by any user.

8 Select the database and view the tables in the library:

Mysql > use test1

Mysql > show tables

9 View current database information and user information: mysql > status

10. Delete database: mysql > drop database test1

11 create a table:

Mysql > create table emp (ename varchar (10), hiredata date,sal decimal (10), deptno int (2))

Mysql > desc emp

Mysql > show create table emp\ g

PS:\ g means to enable records to be arranged vertically according to fields to better display records with longer content.

12 delete table: mysql > drop table emp

13 modify the table:

Modify table field properties: alter table emp modify ename varchar (20)

Add table field: alter table emp add column age int (3)

Delete table field: alter table emp drop column age

Rename table field: alter table emp change age age1 int (4)

Modify the order of table fields: alter table emp add birth data after ename;alter table emp modify age int (3) first

The change indicates: alter table emp rename emp1

14 insert record:

Insert into emp (ename,hiredata,sal,deptno) values ('zzx1','2000-01-01-01-01-01-01-01-01-01)

Insert into emp values ('lisa','2003-02-01)

Insert into dept values (5) (5), (6)

15 Update record:

Update emp set sal=4000 where ename='lisa'

PS: be careful with the where clause, or the field will be updated to lisa for all rows in the table, and many people will make this mistake accidentally.

16 Delete the record:

Delete from emp where ename='dony'

Delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3

17 query record:

Select * from emp

Slelect ename,hiredate,sal,deptno from emp

Select distinct deptno from emp

Select * from emp where deptno=1 and sal select id,count (*) from t group by id

+-+ +

| | id | count (*) |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 4 | 4 |

+-+ +

4 rows in set (0.00 sec)

Mysql > select id,count (*) from t group by id with rollup

+-+ +

| | id | count (*) |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 4 | 4 |

| | NULL | 10 | |

+-+ +

5 rows in set (0.00 sec)

Mysql > select id,count (*) from t group by id having count (1) > 2

+-+ +

| | id | count (*) |

+-+ +

| | 3 | 3 |

| | 4 | 4 |

+-+ +

2 rows in set (0.08 sec)

Select ename,deptname from emp,dept where emp.deptno=dept.deptno

Select ename,deptname from emp left join dept on emp.deptno=dept.deptno

Select ename,deptname from dept right join emp on dept.deptno=emp.deptno

Select * from emp where deptno in (select deptno from dept)

Select * from emp where deptno = (select deptno from dept limit 1)

The main difference between union and union all is that union all merges the result sets directly, while union performs a distinct of the results after union all and removes duplicate records.

Table joins are used to optimize subqueries in many cases.

This is the end of the content of "what are the basics of mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.