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

MySQL is a relational database management system developed by MySQL AB in Sweden, while MySQL AB is acquired by Oracle, so MySQL now belongs to Oracle. MySQL is an associated database management system that stores data in different tables instead of all data in a large warehouse, which increases speed and flexibility. MySQL is open source, so you can use it for free. MySQL supports large databases and can handle large databases with tens of millions of records. However, according to the actual production experience, when the amount of data in a single table reaches 30 million, the performance of the database drops sharply, and even the whole database is paralyzed and unable to log in. MySQL uses the standard SQL data language form, structured query language (Structured Query Language) referred to as SQL, is a special purpose programming language, is a database query and programming language, used to access data and query, update and manage relational database systems, but also an extension of database script files. Once MySQL is installed, you can log in and use it. The most important thing is to use SQL to add, delete, modify and check.

1. View the database

\ # mysql-u root-paired packs sw0rd'

Mysql > show databases

2. Switch the database

Mysql > use mysql

Note:

1:information_schema # this database holds information about all databases on the MySQL server. Such as the database name, the table of the database, the data type of the table column does not have access rights and so on.

2:performance_schema # MySQL 5.5 begins to add a new database: PERFORMANCE_SCHEMA, which is mainly used to collect database server performance parameters. And the storage engine of the library table is PERFORMANCE_SCHEMA, and the user cannot create a table with the storage engine of PERFORMANCE_SCHEMA.

3:mysql library is a system library, which contains account information, permission information and so on.

4:mysql5.7 adds sys system database, through which you can quickly understand the metadata information of the system. Metadata is data about data information, such as database or table names, data types of columns, or access rights.

3. Create a database

Syntax: create database database name

Considerations for creating a database:

1. In the file system, the datastore of MySQL will represent the MySQL database as a directory. Therefore, the database name in the above command must match the directory name of the operating system's constraints. For example,\, /,:, *,?, ", | these symbols are not allowed in file and directory names, and these letters are automatically deleted in the MySQL database name.

2. The name of the database cannot exceed 64 characters. Names containing special characters or names composed entirely of numbers or reserved words must be enclosed in backquotes.

3. The database cannot have the same name.

Mysql > create databases' YF-test'; # failed to build the database using single quotation marks

Mysql > create databases YF-test; # uses backquotes to build the database successfully

\ # ls / var/lib/mysql/ # View the database storage directory

4. Check your location and default location

Mysql > select database ()

+-+

| | database () |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

If no database is selected, the default display is NULL,Null, which means that no database is selected

Mysql > use mysql

Database changed

Mysql > select database ()

+-+

| | database () |

+-+

| | mysql |

+-+

1 row in set (0.00 sec)

5. Delete the database

There is no hint to delete the database, so you should operate it cautiously; when it is best to operate, someone is watching you.

Method 1:mysql > drop database create

Method 2: go directly to the database to store the directory and move it out.

6. Check which tables are in the library. Check the tables. Go to the database and check again.

7. Create tables:

Syntax: create table table name (field name type, field name type, field name type)

Mysql > create database test

Mysql > use test

Mysql > create table student (id int 20), name char 40, age int)

8. View the structure of the table

9. Delete the table

Mysql > drop table student

10. Change the table name alter

Syntax: alter table table name rename new table name

11. Modify the field type in the table

Syntax: alter table table name modify the type of field name to be modified

Mysql > alter table students modify id int (10)

12. Modify the field type and field name in the table

Syntax: alter table table name change original field name new field name new field type

Note: mysql does not support modifying multiple fields at the same time

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

Note: the difference between CHANGE and MODIFY:

CHANGE renames the column and changes the column type by giving the old column name, the new column name, and the current type. MODIFY can change the type of column without renaming (without giving a new column name).

13. Add fields to the table

Syntax: alter table table name add field name field type

Mysql > alter table students add sex enum ('Maureen')

14. Specify the location to add a field, and add a field in the first column

Mysql > alter table students add uid int (10) first

Mysql > alter table students add address char (40) after age

15. Delete the fields in the table:

Syntax: alter table table name drop field name

Mysql > alter table students drop address

16. Insert field insert

Syntax: insert into table name values (field value 1, field value 2, field value 3)

Mysql > create table student (id int 20), name char 40, age int)

Mysql > insert into student values (1 recordzhangszhangzhangzhangzhangjia 21)

Insert a record to correspond to the relative type

Mysql > insert into student values (2) (2), (3) 3)

Insert multiple strips at the same time, use, separate

Mysql > insert into student (id,name) values

17. Record in the query table:

Syntax: select from table name

Mysql > select from student; # * indicates all

We can also use\ G when there are more fields.

Mysql > select * from student\ G

Query only the contents of a field in the table:

Mysql > select name from student

18. View tables in other databases or not on this database

Syntax: SELECT field FROM database name. Table name

Mysql > select * from HA.student

# View the table content and database name specified under a database. Table name

19. Delete records

Delete the row with id 3

Mysql > delete from students where id=3

Delete a line whose age is empty

Mysql > delete from students where age is null

20. Update the record

Mysql > update students set sex='M' where id=2

Mysql > update students set id=2; # all become 2

Mysql > update students set stname='zhangsan',age=21 where uid=1

# when updating multiple fields at the same time, separated by commas

21. SQL basic condition query statement

Syntax: select field name 1, field name 2 from table name [where condition]

(1) query the name,age in the students table

Mysql > select name,age from student

(2) to repeatedly query distinct

Mysql > select distinct name,age from student

Mysql > select distinct id,name,age from student where id=3

Distinct of mysql > select distinct from students; # mysql can be used for

(3) use and and or for multi-conditional query

When or and and exist at the same time, the two boundary values of and are calculated first, logic and execution first.

Mysql > select id,name,age from student where id > 3 and age > 25

Mysql > select id,name,age from student where id > 3 or age > 25

Mysql > select * from students where stname='zhangsan' and (age=21 or age=24)

Notice the logical relationship when both and and or are used

(4) MySQL case-sensitive query

MySQL queries are case-insensitive by default. The comparison is as follows:

Mysql > select name from student where name='jk'

Mysql > select * from student where binary name='jk'

\ # BINARY is a type conversion operator that is used to force the string after it to be a binary string, which can be understood to be case-sensitive when comparing strings.

(5) sorting of MySQL queries:

Syntax: select distinct field 1, field 2 from table name order by field name

Default is ascending asc

Mysql > select distinct id from student order by id asc

Mysql > select distinct id from student order by id desc

Welcome to follow the official Wechat account and learn and communicate together!

My official account!

Https://mp.weixin.qq.com/s/KqlrX13DSQdZb3BVT2dqfA

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.