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

The following content mainly brings you the basic operation of mysql database process, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

First of all, we need to install mysql mkdir chen/mount.cifs / / 192.168.100.23/LNMP chen/ [root@localhost ~] # yum-y install\ gcc\ gcc-c++\ ncurses\ ncurses-devel\ bison\ cmake [root@localhost ~] # useradd-s / sbin/nologin mysql [root@localhost] # cd chen/ [root@localhost chen] # lsmysql-boost-5.7.20.tar.gz nginx-1.12.2.tar.gz Php-7.1.20.tar.gznginx-1.12.0.tar.gz php-7.1.10.tar.bz2 [root@localhost chen] # tar zxvf mysql-boost-5.7.20.tar.gz-C / opt [root@localhost chen] # cd / opt/ [root@localhost opt] # cd mysql-5.7.20/cmake\-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock\ -DSYSCONFDIR=/etc\-DSYSTEMD_PID_DIR=/usr/local/mysql\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\-DMYSQL_DATADIR=/usr/local/mysql/data\-DWITH_BOOST=boost\-DWITH_SYSTEMD= 1 [root @ localhost mysql-5.7.20] # make [root@localhost mysql-5.7.20] # make install [ Root@localhost mysql-5.7.20] # cd / usr/local/ [root@localhost local] # chown-R mysql:mysql mysql/ [root@localhost local] # cd / opt/mysql-5.7.20/ [root@localhost mysql-5.7.20] # vim / etc/my.cnf # # adjust the configuration file [client] # # client port = 3306default-character-set=utf8socket = / usr/local/mysql/mysql .sock [mysql] # # client port = 3306default-character-set=utf8socket = / usr/local/mysql/mysql.sock [mysqld] # # CVM user = mysql # # user basedir = / usr/local/mysql # # set the installation directory of mysql datadir = / usr/local/mysql/data # # set the data storage directory of the mysql database port = 3306 # # set port 3306 character_set_server=utf8 # # Chinese character set pid-file = / usr/local/mysql/mysqld.pid # # pid file path socket = / usr/local/mysql/mysql.sock # # sock file path server-id = 1 # # master-slave parameter

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_ quotes [root @ localhost local] # echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' > > / etc/profile# sets the environment variable Enables the system to recognize all commands under bin and lib [root@localhost local] # echo 'export PATH' > > / etc/profile# global variable [root@localhost local] # source / etc/profile# restart system environment variable # initialize database [root@localhost local] # cd / usr/local/mysql/bin/mysqld\-- initialize-insecure\-- user=mysql\-- basedir=/usr/local/mysql\-- datadir=/usr/local/mysql/data [root@ Localhost mysql] # cp usr/lib/systemd/system/mysqld.service / lib/systemd/system/# put the mysql startup script into the system. Systemctl can manage [root@localhost mysql] # systemctl start mysqld.service [root@localhost ~] # systemctl enable mysqld # setting to enable self-startup mysqlCreated symlink from / etc/systemd/system/multi-user.target.wants/mysqld.service to / usr/lib/systemd/system/mysqld.serv [root@localhost mysql] # netstat-ntap | grep 3306tcp6 0:: 3306:: * LISTEN 73971/mysqld # set the mysql password [root@localhost mysql] # mysqladmin-u root-p password '123123 password # initially empty password You can set abc123Enter password: New password: Confirm new password: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. [root@localhost mysql] # mysql-u root-pEnter password: database basic operation command [root@localhost ~] # mysql-u root-p # enter database Enter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 6Server version: 5.7.20 Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > use mysql # enter the database mysql > show tables # View all tables in the database +-+ | Tables_in_mysql | +-+ | columns_priv | | db | | engine_cost | | event | | func | mysql > desc user | # display the structure of the data table +-- +-+ | Field | | Type | Null | Key | Default | Extra | + -+-+ | Host | char (60) | NO | PRI | User | char () | NO | PRI | | | Select_priv | enum ('N') | |'Y') | NO | | N | Insert_priv | enum ('Nickel camera Y') | NO | | N | | Update_priv | enum (' N') | |'Y') | NO | | N | Delete_priv | enum ('Nickel recording Y') |

Binary search

Take 32 as the benchmark, put the larger ones on the right and the smaller ones on the left.

Four kinds of Operation statement of Database SQL statement Overview of SQL language

Is the abbreviation of Structured Query Language, that is, structured query language.

It is the standard language of relational database.

Used to maintain and manage databases, such as data query, data update, access control, object management, etc.

SQL classification

DDL: data definition language

DML: data manipulation language

DQL: data query language

DCL: data control language

DDL statements operate DDL statements used to create database objects, such as libraries, tables, indexes, etc. Mysql > create databases test;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases test' at line 1mysql > create database test;Query OK, 1 row affected (0.00 sec) mysql > use test Database changedmysql > mysql > create table info (- > id int (3) not null,-> name varchar (10) not null,-> age int (5) not null,-> score decimal default 0,-> primary key (id); Query OK, 0 rows affected (0.12 sec) mysql > desc info +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (3) | NO | PRI | NULL | name | varchar (10) | NO | | NULL | | age | int (5) | NO | | NULL | | score | decimal (10) | YES | | 0 | +-+- -+ 4 rows in set (0.00 sec) DML statement operates mysql > use test # enter test database Database changedmysql > mysql > create table info (# create info data Table-> id int (3) not null,-> name varchar (10) not null,-> age int (5) not null,-> score decimal default 0,-> primary key (id)); Query OK, 0 rows affected (0.12 sec) mysql > desc info # View data table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +-- -+-+ | id | int (3) | NO | PRI | NULL | name | varchar (10) | NO | | NULL | | age | int (5) | NO | | NULL | | score | decimal (10Force 0) | YES | 0 | +-+-| -+ 4 rows in set (0.00 sec) mysql > insert into info values (1 'Xiao Chen', 20,999) # insert data Query OK, 1 row affected (0.00 sec) mysql > insert into info values (2 row affected 'Xiao Wang', 22 sec 88); Query OK, 1 row affected (0 sec) mysql > insert into info values (3 row affected 'Xiao Gao', 25 sec 77); Query OK, 1 row affected (0 sec) mysql > select * from info # View the data in the data table This is the DQL statement +-+ | id | name | age | score | +-+ | 1 | Xiao Chen | 20 | 99 | | 2 | Xiao Wang | 22 | 88 | 3 | Xiao Gao | 25 | 77 | +-- -+-+ 3 rows in set (0.01sec) mysql > update info set score='95' where id=1 # Delete the score column from the info table, from the primary key index id1Query OK, 1 row affected (0.14 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from info +-+ | id | name | age | score | +-+ | 1 | Xiao Chen | 20 | 95 | | 2 | Xiao Wang | 22 | 88 | | 3 | Xiao Gao | 25 | 77 | +-- +-- -+ 3 rows in set (0.00 sec) mysql > delete from info where id = 2 # Delete the line of id2-> Query OK, 1 row affected (0.01sec) mysql > select * from info +-+ | id | name | age | score | +-+ | 1 | Xiao Chen | 20 | 95 | 3 | Xiao Gao | 25 | 77 | + 2 rows in set (0.00 sec) mysql > drop table info # delete info data table Query OK, 0 rows affected (0. 05 sec) mysql > show tables; # View data table Empty set (0. 00 sec) mysql > drop database test; # Delete test database Query OK, 0 rows affected (0. 04 sec) mysql > show databases # View the database There is no test database +-+ | Database | +-+ | information_schema | | chen | | mysql | | performance_schema | | school | | sys | +- -+ 6 rows in set (0.00 sec) DQL statement operation

DQL is a data query statement with only one item: select

Used to find qualified data records from the data table

No conditions can be specified when querying

Mysql > select * from info +-+ | id | name | age | score | +-+ | 1 | Xiao Chen | 20 | 95 | | 3 | Xiao Gao | 25 | 77 | +-+ 2 rows in set ( 0.00 sec) mysql > select name from info where name=' Xiaogao' # # View the specified condition +-+ | name | +-+ | Xiaogao | +-+ 1 row in set (0.00 sec) DCL language operation sets user permissions (if the user does not exist, the new user is created) mysql > grant all privileges on *. * to 'root'@'%' identified by' abc123' Query OK, 0 rows affected, 1 warning (0.00 sec) View user's permissions mysql > show grants for 'root'@'%' +-+ | Grants for root@% | +-- + | GRANT ALL PRIVILEGES ON *. * TO 'root'@' %'| +-- + 1 row in set (0.00 sec) # # revoke the user's permissions mysql > revoke all on *. * from 'root'@'%'

Query OK, 0 rows affected (0.00 sec)

For the above basic operation mysql database process, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and post-sale 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.