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

First, install MariaDB and its configuration steps

1) create a data directory and its users

Mkdir-pv / mydata/data

Groupadd-r mysql

Useradd-g mysql-r mysql

2) Authorization data storage directory

Chown-R / mydata/data

3) decompress mariadb-5.5.48-linux-x86_64.tar.gz

Tar xfmariadb-5.5.48-linux-x86_64.tar.gz-C / usr/loca/mysql

4) provide configuration files and startup scripts for the database

Cd / usr/local/mysql

Cp support-files/my-large.cnf / etc/my.cnf

Cp support-files/mysql.server / etc/init.d/mysqld

Chmod + x / etc/init.d/mysqld

Chkconfig-add / etc/init.d/mysqld

Chkconfig mysqld on

5) initialize the database

Cd / usr/local/mysql

. / configure-- user=mysql--datadir=/mydata/data

6) add environment variables

Echo "exportPATH=/usr/local/mysql/bin:$PATH" > / etc/profile.d/mysql.sh

Source / etc/profile.d/mysql.sh

7) start the database

/ etc/init.d/mysqld start or service mysqld start

II. Server command

1.DDL (data definition language) is mainly used to manage database components, such as indexes, views, users, stored procedures: create alter drop, etc.

DDL command description: create,alter,drop

Create: create

Create {DATABASES | SCHEMA} [IF NOT EXISTS] db_name [create_spacification]....

For example: create database if notexists testdb

Get help: help or help command

View all databases: show databases

View supported character sets: show character set

View all supported sorting rules: show collation

Modify: alter

ALTER {DATABASE | SCHEMA} [db_name] alter_specification...

Delete: drop

DROP {DATABASE | SCHEMA} [db_name] for example: drop database testdb

Table operation:

Create a tabl

Create [temporary] talbe [if not exists] tbl_name (create_definition,....)

For example: create table if not exists students (id int unsigned not null,name varchar (20), age tinyint unsigned,gender enum ('fame and id'), primary key (id))

View table structure: desc students

View the table creation process: show create tablestudents\ G

View the status information of the table: show table status like 'students'; show table status like'% stu%'

View the storage engine supported by the table database: show engines

Modify the table:

Alt [online | OFFLINE] [IGNORE] TABLE tbl_name [alter_spcification]

Field:

Add field: ADD [column] col_namecolumn_definition

Alter table students add number int (13) not null

Alter table students add number int (13) not null after age

Delete: DROP [COLNUM] col_name

Alter table students drop number

Modify: CHANGE [COLNUM] old_col_name new_col_name column_definition

MODIFY [COLNUM] old_col_name new_col_name column_definition

Alter table students modify name varchar (30) after age

Add keys:

Add {primarykey | unique key | foreign key} (col1,col2....)

Alter table students add primary key (number,id)

Delete key:

Drop primarykey

Alter table students drop primary key

Index:

Add: add {index | key} {index_name}

Delete: drop {index | key} {index_name}

Alter table add index age (age)

View index information on the table: show indexes from tbl_name

Create:

Create [online | OFFLINE] [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_option]...

Create index name_and_class on students (name,age)

Delete:

Drop index name_and_class on students

DML (data manipulation language): mainly used to manage the data in the table, to add, delete, modify and query (insert,delete,update,select)

Inset into:

Insert into:insert into table_name [cols.] Value (val1,val2,val3....)

For example: insert into studentsvalues (1), 25, 25, 16, 9, 9, 9, 9, 4, 4, 4, 5, 4, 4, 4, 6, 4, 4, 4, 4, 6, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,

Insert into students (id,name) values (1), (2)

Note: when using character data, you need to use quotation marks, numeric values do not need to use quotation marks, you can use them.

Delete:

Delete from students where age is null

Delete from studnets order by age desc limit 20

Update

Update students set age=age+15 where name like'%ren%'

Select:

Select * fromtbl_name

Select col1,col2,col3,....from tbl_name

Select id,namefrom students

Select col1,col2... From tbl_name where clause

Select name,age from students where age > 11 and age routine_name: specify a stored procedure or function on the library

Example: grant selct,updateon mydb.students to 'testuser'@'192.168.%.%' identified by' paswd'

Grant all *. * to'root'@'localhost' identified by 'password'

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.