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

The following content mainly brings you MySQL database classification, system functions, create a database and other detailed explanation, the knowledge mentioned here, slightly different from books, are professional and technical personnel in contact with users, summed up, has a certain experience sharing value, hope to bring help to the majority of readers.

1) Database classification

2) System function

3) Creating a database

4) Select database

5) Types of data

6) Table-building statements

7) Insert data

8) Delete users, delete data, delete database tables

9) Modify table data

10) Query data

11) User authorization

12) show View all current library tables, library table creation statements, user permissions

13) desc view table structure

14) Copy table structure

15) Add, delete and modify fields

16) View Help

[Database classification]

In MySQL software, database can be divided into two categories: system database and user database.

1. System database

System database refers to some data attached after MySQL Cloud Virtual Machine is installed, as shown in the following figure

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| ryzc_data |

| test |

+--------------------+

5 rows in set (0.00 sec)

information_schema: Some database object information in the main storage system, such as user information, column information, permission information, character set information, partition information, etc.

performance_schema: primary storage database server performance parameters

mysql: User permission information for the primary storage system

test: This database is a test database automatically created by MySQL database management system and can be used by any user.

2. User database

User database is a database created by users according to actual requirements, as shown in the figure below, where userdatabase belongs to user database.

[System Function]

version() #Returns the version number of the system library

databasee() #Returns the current system library name

user() #Returns the current user

#Query multiple

mysql> select user(),database(),version();

#Query a single

mysql> select user();

Create a database

create database database_name

The database_name parameter in the above statement indicates the name of the database to be created

mysql> create database databasetest;

Query OK, 1 row affected (0.00 sec)

Query OK: indicates successful execution of SQL statement

1 row affected : indicates that the operation affects only one row of records in the data

0.00 sec: indicates the time taken for the operation to be performed

create database wsyht_gbk default character set gbk collate gbk_chinese_ci; #Create library wsyht_gbk and set default character set gbk

create database wsyht_utf8 default character set utf8 collate utf8_general_ci; #Similarly specify utf8 character set

Select Database

mysql> use mysql

[Data Type]

Common types:

integer type

int

floating-point types

float

string type

char

varchar

[Table-building statement]

#Create table stdent

The id field must be filled with at least a four-digit integer and cannot be blank.

The name field is a string, at least 20 digits, and cannot be empty.

use wsyht

create table student(

id int(4) not null,

name char(20) not null,

age tinyint(2) NOT NULL default '0',

dept varchar(16) default NULL

);

[Insert Data]

insert into student(id,name) values(1,'wsyht');

insert into student values(4,'jack', 25,' abc'); #interpolate in order

insert into student values(5,'dock', 25,' abc'),(6,'tom', 26,' cad'); #Insert two values

[Delete Users, Delete Data, Delete Database Tables]

#Delete Library

drop database wsyht; #Delete wsyht database

drop user 'root'@'localhost' #Delete redundant system accounts, delete user table root user host name is localhost

Delete users

delete from mysql.user where user='root' and host='localhost';#Delete root user

flush privileges; #Delete user requires flushing privileges

#delete table

drop table student;

#Delete data

delete from test where id=1; #Delete rows where id = 1 in test table

delete from test where id>3; #Delete rows with id equal to 3 in test table

delete from test; #Delete all data in the test table

truncate table test; #Empty the entire table directly, test is the table name

truncate empty physical file delete logical clear delete by line

[Modify Table Data]

update t1 set name='jack' where id=1; #update t1 name field is jack, if id=1

rename table test to t1; #rename table test to t1;

alter table t1 rename to test; rename table t1 to test

[Query Data]

select *from student; #Query the values of all data in the table

select id, name from student; #Query the value of the id name field

select id,name from student limit 2; #Query only the value of 2 rows of data in the id,name field

select id,name from student where id=1; #conditional query, query id=5 value

select id,name from student where name ='tom '; #character query in quotes

select id,name from student where name='tom' and id='6';#Both sides must be true

select id,name from student where name='tom' or id='8';#One side can be established

select id,name from student where id>3 and id help show

mysql> help select

mysql> help alter

mysql> help insert

mysql> help update

For the above detailed explanation of MySQL database classification, system functions, database creation, etc., if you still have more to know, you can continue to pay attention to our industry push, if you need to get professional answers, you can contact the pre-sales and after-sales on the official website, I hope this article can bring you some knowledge updates.

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.