Basic Operation of MySQL-- Database (practical part)

Example: establish student score sheet 1, compile and install MySQL1, install environment components [root@localhost] # yum install-y\ # installation environment components > ncurses\ > ncurese-devel\ # # control terminal screen display library > cmake\ # # cmake tool > bison # syntax analysis [root@localhost ~] # useradd-s / sbin/nologin mysql # # Creator user 2 Decompress the source code package to [root@localhost ~] # cd / mnt [root@localhost mnt] # tar zxvf mysql-boost-5.7.20.tar.gz-C / opt # # decompress the source code package to / opt [root@localhost mnt] # cd / opt [root@localhost opt] # lsmysql-5.7.20 nginx-1.12.2 rh3 Cmake configuration [root@localhost opt] # cd mysql-5.7.20/ # # switch to [root@localhost mysql-5.7.20] # cmake\ # # cmake configuration-DCMAKE_INSTALL_PREFIX=/usr/localmysql\ # # installation path-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock\ # # define sock file connection database file-DSYSCONFDIR=/etc\ # # configuration file directory-DSYSTEMD_PID_DIR=/usr/local/mysql\ # # PID file directory-DDEFAULT_CHARSET=utf8\ # # specify character set-DDEFAULT_COLLATION=utf8_general_ci\ # specify character set default- DWITH_INNOBASE_STORAGE_ENGINE=1\ # Storage engine-DWITH_ARCHIVE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\-DMYSQL_DATADIR=/usr/local/mysql/data\ # Database data file directory-DWITH_BOOST=boost\ # # underlying runtime-DWITH_SYSTEMD=1 # # Master-Slave Parameter "4 Compile and install [root@localhost mysql-5.7.20] # make # # compile [root@localhost mysql-5.7.20] # make install # # install 5, configure mysql Adjust the configuration file [root@localhost mysql-5.7.20] # chown-R mysql:mysql / usr/local/mysql/ # # adjust the permissions of the database directory [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] # # Server user = mysql # # user basedir = / usr/local/mysql # # set mysql installation directory datadir = / usr/local/mysql/data # # set mysql data Data storage directory of the library 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## support module > [root@localhost mysql-5.7.20] # chown mysql:mysql / etc/my.cnf # # to the configuration file mysql attribute master group [root@localhost mysql-5.7.20] # echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' > > / etc/profile## write MySQL to the local environment configuration [root@localhost mysql-5.7.20] # echo' export PATH' > > / etc/profile # # set global environment configuration [root@localhost mysql-5.7.20] # source / etc/profile # # restart configuration file 6 Initialize database [root@localhost mysql-5.7.20] # cd / usr/local/mysql/ [root@localhost mysql] # bin/mysqld\ >-- initialize-insecure\ # # initialize >-- user=mysql\ # # user >-- basedir=/usr/local/mysql\ # # installation directory >-- datadir=/usr/local/mysql/data # # database data file directory 7 Copy the MySQL service configuration file to / usr/lib/systemd/system/ for easy management using systemctl [root@localhost mysql] # cp usr/lib/systemd/system/mysqld.service / usr/lib/systemd/system/ # # facilitate systemctl management [root@localhost mysql] # systemctl enable mysqld # # Boot self-boot [root@localhost mysql] # systemctl start mysqld.service # # Open database [root@localhost mysql] # netstat-ntap | grep 3306 # # check if tcp6 port number is enabled. MySQL 0 0: 3306: * LISTEN 59464/mysqld 8 Set the MySQL password [root@localhost mysql] # mysqladmin-u root-p passwordEnter password: # # Space New password: # # New password Confirm new password: # # confirm password 2, enter the database to create 1, create the database and the tables in the database mysql > grant all privileges on *. * to 'root'@'%' identified by' 123123' # # subscriber Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > create database test; # # create database testQuery OK, 1 row affected (0.00 sec) mysql > show databases # # View database +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | +-+ 5 rows in set (0.01sec) mysql > use test # # use database testDatabase changedmysql > create table info (# # create table-> id int (3) not null,-> name varchar (4) not null,-> score decimal default 0,-> primary key (id)); mysql > desc info # # View table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +-- -+-+ | id | int (3) | NO | PRI | NULL | name | varchar (4) | NO | | NULL | | score | decimal (10line 0) | YES | | 0 | | +-+ | -+-+ 3 rows in set (0.01 sec) 2 Insert data into the info table mysql > insert into info values (1 what round of week, 90) # # insert data Query OK into the table, 1 row affected (0.01 sec) mysql > insert into info values (2 row affected 'Wang Feng', default); Query OK, 1 row affected (0. 00 sec) mysql > insert into info values (3'Na Ying', 60); Query OK, 1 row affected (0. 00 sec) mysql > select * from info # # View the data in the table +-+ | id | name | score | +-+ | 1 | Weekly rounds | 90 | 2 | Wang Feng | 0 | | 3 | Na Ying | 60 | +-+- -+-+ 3 rows in set (0.00 sec) 3 Modify the data in the table mysql > update info set score=88 where id=2 # # modify the data in the table Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from info +-+ | id | name | score | +-+ | 1 | Round of the week | 90 | 2 | Wang Feng | 88 | | 3 | Na Ying | 60 | +-+ 3 rows in set (0.00 sec) 3 Use Navicat to connect to the database to view tables

Thank you for reading!