Mysql series-basic knowledge, mysql-Basic Knowledge

Mysql series-basic knowledge, mysql-Basic Knowledge

Note: This article summarizes the basic knowledge of mysql. If there are many basic points, please search for them by yourself. Continuous update

I. mysql Introduction

 

Database Introduction

A database is a system dedicated to data resource management in computer application systems.

A database is a group of Computer-processed data stored in multiple files. database management software is called Database Management System (DBMS ).

Mysql oracle is a database management system.

Mysql is a database management software for free.

SQL

The SQL (Structured Query Language) database management system uses the SQL language to manage data in the database. SQL is a database query and long-standing design language. It is mainly used to access data, query data, update data, and manage relational database systems. Including the following types

DDL statements: a data definition language is used to define databases, tables, views, indexes, and triggers. Including created, alter, and drop statements

DML statements: data manipulation language is mainly used to insert, query, update, and delete data. Including insert, select, update, and delete statements

DCL statement: the data control language is used to control user access permissions. These include the grant statement and revoke statement.

Ii. mysql command line operations

 

1. Enter mysql

mysql -u root -p

 

2. view all databases

show databases;

 

3. Create a database

Create database name default charset utf8 COLLATE utf8_general_ci;

Use Database

Use feng;

 

4. Show all tables in the selected Database

show tables;

 

5. display a table structure

desc  table;
show column from table;

 

6. Create a table

# Basic syntax: create table name (whether the column name type is null, default value: auto-incrementing primary key, and whether the column name type is null) ENGINE = InnoDB default charset = utf8not null # Not empty default num # DEFAULT auto_incrememt # auto-incrementing primary key # primary key

 

7. delete a table

Drop table Name

 

8. Clear the table

Delete from table name # The table is still in, and the data is cleared. truncate table Name

 

9. 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 alter table name change original column name new column name type;
# Add the primary key alter table name add primary key (column name); # Delete the primary key alter table Name drop primary key; alter table name modify column name int, drop primary key; # modify the DEFAULT value: alter table testalter_tbl ALTER I set default 1000; # Delete the DEFAULT value: alter table testalter_tbl ALTER I DROP DEFAULT; # change the table name rename TABLE original TABLE name to new table name;

 

 

10. Table content operations

Insert content

Insert into Table Name (field) values (Information)

Delete content

Delete from table name [where condition]

Modify content

Update table name set column name = expression where condition update table name set (column name) = (subquery) where Condition

Search content

Select * from table name where Condition
Condition # and multiple conditions select * from table where name = 'feng' and age> 25; # The between range age is 20-25select * from table where age between 20 and 25; # select * from table in (, 21,) among multiple in elements; # not select * from Table no in () among multiple elements ); # select * from table in (select age from table 2) in the subquery results );
Wildcard # % feng the ending nameselect * from table where name like '% feng' # _ f the starting nameselect * from table where name like 'f _'

Limit select * from Table limit 5, // select * from Table limit 0, 5; // select * from Table limit 5 offset 2 from 0; // The fifth line starting from the second line

Sort select * from Table order by column asc // select * from Table order by column desc // from large to small select * from Table order by column 1 desc, column 2 asc // column 1 from large to small column 2 to large

Group select name from Table group by name # select name, time from Table group by name, time # according to name, timselect name, time, id from table where id> 10 group by name, time order by id desc # Get name time id sort by name time group from Avenue small

Select name, age from Table group by age having max (id) <2 // get name age data with the largest id by age group <2


Some functions
Count (*)
Sum (score) // represents sum
Max (score) // indicates the maximum number.
Min (score) // represents the minimum number.
Having # indicates the result of the previous query and uses having for conditional filtering.

#: Group by must be after where, before order

Select. name,. phone, B. name from a, B where. xid = B. if the xid has a ing relationship, all select. name,. phone, B. name from a inner join B on. xid = B. xid
If yes, it is displayed. If B does not exist, the value is nullselect a. name, a. phone, B. name from a left join B on a. xid = B. xid
If a match exists, it is displayed. If a does not exist, the value is nullselect a. name, a. phone, B. name from a right join B on a. xid = B. xid