In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Production system environment:
[sky@sky9896 ~] $cat / etc/redhat-release
CentOS release 6.8 (Final)
1. Log in to the database:
[sky@sky9896] $mysql-uroot-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 151757
Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.
Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.
Mysql >
two。 Check what is the current login user of the database version
Mysql > select version (); # View database version
+-+
| | version () |
+-+
| | 5.5.49-cll-lve |
+-+
1 row in set (0.00 sec)
Mysql > select user (); # View the currently logged in user
+-+
| | user () |
+-+
| | root@localhost |
+-+
1 row in set (0.02 sec)
3. Create the database skyboy of the GBK character set and view the complete statement of the built library.
Mysql > create database skyboy character set gbk collate gbk_chinese_ci
Query OK, 1 row affected (0.03 sec)
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | a |
| | back20150625ultrax |
| | cacti |
| | cacti20151220 |
| | cacti20160104 |
| | feifeicms |
| | mysql |
| | performance_schema |
| | phpcom |
| | skyboy |
| | study |
| | syslog |
| | test |
| | test1 |
| | tt |
| | ultrax |
+-+
17 rows in set (0.04 sec)
Mysql > show create database skyboy\ G # View the complete statement of the built library
* * 1. Row *
Database: skyboy
Create Database: CREATE DATABASE `skyboy` / *! 40100 DEFAULT CHARACTER SET gbk * /
1 row in set (0.00 sec)
4. Create a user skyboy to manage the database skyboy.
Mysql > grant all on skyboy.* to skyboy@'localhost' identified by '123456'
Query OK, 0 rows affected (0.09 sec)
5. See which permissions are caused by the created user skyboy.
Mysql > show grants for skyboy@'localhost'\ G
* * 1.row*
Grants for skyboy@localhost: GRANT USAGE ON *. * TO 'skyboy'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
* * 2.row*
Grants for skyboy@localhost: GRANT ALL PRIVILEGES ON `skyboy`. * TO 'skyboy'@'localhost'
2 rows in set (0.00 sec)
6. See which users are in the current database.
Mysql > select user,host from mysql.user
+-+ +
| | user | host |
+-+ +
| | cacti |% | |
| | cactiuser |% | |
| | root |% | |
| | cacti | * | |
| | root | * | |
| | root | 115.151.218.186 | |
| | cacti | 117.40.239.9 | |
| | root | 127.0.0.1 | |
| | root |:: 1 |
| | localhost |
| | A1 | localhost |
| | a2 | localhost |
| | cactiuser | localhost |
| | root | localhost |
| | sky9896 | localhost |
| | skyboy | localhost |
| | sky9896 |
| | root | sky9896 |
+-+ +
18 rows in set (0.00 sec)
7. Access to skyboy database
Mysql > use skyboy
Database changed
Mysql > select database ()
+-+
| | database () |
+-+
| | skyboy |
+-+
1 row in set (0.00 sec)
8. Create an innodb engine character set as GBK table test, fields as id and name varchar (16), view the table structure and SQL statements.
Mysql > create table test (
-> id int (4)
-> name varchar (16)
->) ENGINE=innodb default charset=gbk
Query OK, 0 rows affected (0.35 sec)
Mysql > show tables
+-+
| | Tables_in_skyboy |
+-+
| | test |
+-+
1 row in set (0.00 sec)
Mysql > desc test; # to view the table structure, method 1
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | YES | | NULL |
| | name | varchar (16) | YES | | NULL |
+-+ +
2 rows in set (0.00 sec)
Mysql > show columns from test; # View the table structure, method 2
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | YES | | NULL |
| | name | varchar (16) | YES | | NULL |
+-+ +
2 rows in set (0.00 sec)
Mysql > show create table test\ G # View table structure
* * 1. Row *
Table: test
Create Table: CREATE TABLE `test` (
`id`int (4) DEFAULT NULL
`name` varchar (16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
9. Insert a piece of data 1dyskyboy
Mysql > insert into test values (1)
Query OK, 1 row affected (0.06 sec)
Mysql > select * from test
+-+ +
| | id | name |
+-+ +
| | 1 | skyboy |
+-+ +
1 row in set (0.00 sec)
10. Insert data in batch 2, keep learning MySQL,3 and prepare for exams. It is required that Chinese should not be garbled.
Mysql > insert into test values (2 'keep learning MySQL''), (3 'take the item tube exam'); # punctuation marks in English
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
Mysql > select * from test
+-+ +
| | id | name |
+-+ +
| | 1 | skyboy |
| | 2 | persist in learning MySQL |
| | 3 | take the item management exam |
+-+ +
3 rows in set (0.00 sec)
11. Query all inserted records, query records with the name skyboy. Query records with id greater than 1.
Mysql > select * from test
+-+ +
| | id | name |
+-+ +
| | 1 | skyboy |
| | 2 | persist in learning MySQL |
| | 3 | take the item management exam |
+-+ +
3 rows in set (0.00 sec)
Mysql > select * from test; # query all inserted records
+-+ +
| | id | name |
+-+ +
| | 1 | skyboy |
| | 2 | persist in learning MySQL |
| | 3 | take the item management exam |
+-+ +
3 rows in set (0.00 sec)
Mysql > select * from test where name='skyboy'; # query the record named skyboy
+-+ +
| | id | name |
+-+ +
| | 1 | skyboy |
+-+ +
1 row in set (0.00 sec)
Mysql > select * from test where id > 1; # query records with id greater than 1
+-+ +
| | id | name |
+-+ +
| | 2 | persist in learning MySQL |
| | 3 | take the item management exam |
+-+ +
2 rows in set (0.00 sec)
twelve。 Change the name skyboy where the data id equals 1 to sky9890.
Mysql > update test set name='sky9890' where id=1
Query OK, 1 row affected (0.05sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql > select * from test
+-+ +
| | id | name |
+-+ +
| | 1 | sky9890 |
| | 2 | persist in learning MySQL |
| | 3 | take the item management exam |
+-+ +
3 rows in set (0.00 sec)
13. Insert the age field, type tinyint (2), before the field name.
Mysql > alter table test add age tinyint (2) after id
Query OK, 3 rows affected (0.34 sec)
Records: 3 Duplicates: 0 Warnings: 0
Mysql > desc test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | YES | | NULL |
| | age | tinyint (2) | YES | | NULL |
| | name | varchar (16) | YES | | NULL |
+-+ +
3 rows in set (0.00 sec)
14. Backup skyboy Library
Root@sky9896 ~] # mysqldump-uroot-p skyboy > / opt/bak.sql
Enter password:
[root@sky9896 ~] # ll / opt/bak.sql
-rw-r--r-- 1 root root 1923 August 13 15:38 / opt/bak.sql
[root@sky9896 ~] # cat / opt/bak.sql
-- MySQL dump 10.13 Distrib 5.5.49, for Linux (x86 / 64)
--
-- Host: localhost Database: skyboy
-
-- Server version 5.5.49-cll-lve
/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /
/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /
/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /
/ * 40101 SET NAMES utf8 * /
/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /
/ * 40103 SET TIME_ZONE='+00:00' * /
/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /
/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /
/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /
/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`
/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /
/ *! 40101 SET character_set_client = utf8 * /
CREATE TABLE `test` (
`id`int (4) DEFAULT NULL
`age`tinyint (2) DEFAULT NULL
`name` varchar (16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
/ * 40101 SET character_set_client = @ saved_cs_client * /
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE
/ *! 40000 ALTER TABLE `test` DISABLE KEYS * /
INSERT INTO `test` VALUES (1), (2) "keep on learning MySQL'", (3) "take the exam")
/ *! 40000 ALTER TABLE `test` ENABLE KEYS * /
UNLOCK TABLES
/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /
/ * 40101 SET SQL_MODE=@OLD_SQL_MODE * /
/ * 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS * /
/ * 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS * /
/ * 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /
/ * 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS * /
/ * 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /
/ * 40111 SET SQL_NOTES=@OLD_SQL_NOTES * /
-- Dump completed on 2016-08-13 15:38:31
15. Delete all data in the table and view it.
Mysql > delete from test; # logically delete the data in the table, column by column, slow
Query OK, 3 rows affected (0.07 sec)
Mysql > truncate table test; # physically delete the data in the table and empty it all at once, which is very fast.
Query OK, 0 rows affected (0.07 sec)
Mysql > select * from test; # View the result
Empty set (0.00 sec)
Mysql > show tables
+-+
| | Tables_in_skyboy |
+-+
| | test |
+-+
1 row in set (0.00 sec)
Mysql > drop table test
Query OK, 0 rows affected (0.07 sec)
Mysql > show tables
Empty set (0.00 sec)
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | a |
| | back20150625ultrax |
| | cacti |
| | cacti20151220 |
| | cacti20160104 |
| | feifeicms |
| | mysql |
| | performance_schema |
| | phpcom |
| | skyboy |
| | study |
| | syslog |
| | test |
| | test1 |
| | tt |
| | ultrax |
+-+
17 rows in set (0.00 sec)
Mysql > drop database skyboy
Query OK, 0 rows affected (0.04 sec)
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | a |
| | back20150625ultrax |
| | cacti |
| | cacti20151220 |
| | cacti20160104 |
| | feifeicms |
| | mysql |
| | performance_schema |
| | phpcom |
| | study |
| | syslog |
| | test |
| | test1 |
| | tt |
| | ultrax |
+-+
16 rows in set (0.00 sec)
The 17.Linux command line restores the deleted data above
When restoring, create an skyboy empty data first, and then restore it.
[root@sky9896] # mysql-uroot-p skyboy use skyboy
Database changed
Mysql > show tables
+-+
| | Tables_in_skyboy |
+-+
| | test |
+-+
1 row in set (0.00 sec)
Mysql > select * from test
+-+
| | id | age | name | |
+-+
| | 1 | NULL | sky9890 |
| | 2 | NULL | persist in learning MySQL |
| | 3 | NULL | take the item management exam |
+-+
3 rows in set (0.00 sec)
18. Change the GBK character set to UTF8.
Mysql > show variables like 'character_set%'
+-+
| | Variable_name | Value |
+-+
| | character_set_client | utf8 |
| | character_set_connection | utf8 |
| | character_set_database | latin1 |
| | character_set_filesystem | binary |
| | character_set_results | utf8 |
| | character_set_server | latin1 |
| | character_set_system | utf8 |
| | character_sets_dir | / usr/share/mysql/charsets/ |
+-+
8 rows in set (0.00 sec)
Mysql > set global character_set_database=gbk
Query OK, 0 rows affected (0.00 sec)
Mysql > exit
Bye
[root@sky9896] # mysql-uroot-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 152566
Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.
Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.
Mysql > show variables like 'character_set%'; # character set for database service
+-+
| | Variable_name | Value |
+-+
| | character_set_client | utf8 |
| | character_set_connection | utf8 |
| | character_set_database | gbk |
| | character_set_filesystem | binary |
| | character_set_results | utf8 |
| | character_set_server | latin1 |
| | character_set_system | utf8 |
| | character_sets_dir | / usr/share/mysql/charsets/ |
+-+
8 rows in set (0.00 sec)
19. Modify mysql password
Mysql > update mysql.user set password=PASSWORD ('skyboy') where user='root'
And host='localhost'
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql > flush privileges
Query OK, 0 rows affected (0.00 sec)
The principle of Chinese data garbled in 20.MySQL and how to prevent garbled code? (optional).
# utf8 is required for client software character set
[root@sky9896 ~] # cat / etc/sysconfig/i18n # modify character configuration file
LANG= "zh_CN.UTF-8"
21. Create a normal index on the Name field after setting the id column as the primary key.
Mysql > alter table skyboy.test add primary key (id)
Query OK, 3 rows affected (0.32 sec)
Records: 3 Duplicates: 0 Warnings: 0
Mysql > desc skyboy.test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | NO | PRI | 0 | |
| | age | tinyint (2) | YES | | NULL |
| | name | varchar (16) | YES | | NULL |
+-+ +
3 rows in set (0.00 sec)
Mysql > create index index_name on skyboy.test (name)
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > desc skyboy.test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | NO | PRI | 0 | |
| | age | tinyint (2) | YES | | NULL |
| | name | varchar (16) | YES | MUL | NULL |
+-+ +
3 rows in set (0.00 sec)
twenty-two。 Insert the mobile number field (shouji), type char (11), after the field name.
Mysql > alter table skyboy.test add shouji char (11) after name
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
Mysql > desc skyboy.test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | NO | PRI | 0 | |
| | age | tinyint (2) | YES | | NULL |
| | name | varchar (16) | YES | MUL | NULL |
| | shouji | char (11) | YES | | NULL |
+-+ +
4 rows in set (0.00 sec)
23. Insert 2 records on all fields (set data by yourself)
Mysql > insert into test values (20160813'), (5pr 98)), (20160810')
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
Mysql > select * from test
+-- +
| | id | age | name | shouji | |
+-- +
| | 1 | NULL | sky9890 | NULL | |
| | 2 | NULL | MySQL | NULL |
| | 3 | NULL |? | NULL |
| | 4 | 21 | sky | 20160813 | |
| | 5 | 98 | skyboy | 20160810 | |
+-- +
5 rows in set (0.00 sec)
The garbled code is shown above, and the solution is as follows:
[root@sky9896 ~] # vi / etc/sysconfig/i18n
LANG= "zh_CN.UTF-8"
Remote exit
Re-login will not take effect
Mysql > use skyboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
Mysql > select * from test
+-- +
| | id | age | name | shouji | |
+-- +
| | 1 | NULL | sky9890 | NULL | |
| | 2 | NULL | persist in learning MySQL | NULL |
| | 3 | NULL | take the item management examination | NULL |
| | 4 | 21 | sky | 20160813 | |
| | 5 | 98 | skyboy | 20160810 | |
+-- +
5 rows in set (0.00 sec)
24. Create a normal index on the first 8 characters on the phone field.
Mysql > alter table test add index index_shouji (shouji (8))
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > desc test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | NO | PRI | 0 | |
| | age | tinyint (2) | YES | | NULL |
| | name | varchar (16) | YES | MUL | NULL |
| | shouji | char (11) | YES | MUL | NULL |
+-+ +
4 rows in set (0.00 sec)
25. View information such as the index created and the type of index.
Mysql > show index from skyboy.test\ G
* * 1. Row *
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
* 2. Row * *
Table: test
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
* 3. Row * *
Table: test
Non_unique: 1
Key_name: index_shouji
Seq_in_index: 1
Column_name: shouji
Collation: A
Cardinality: 5
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.01sec)
twenty-six。 Delete the index of the Name,shouji column.
Mysql > alter table test drop index index_name
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > drop index index_shouji on test
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > show index from test\ G
* * 1. Row *
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
twenty-seven。 Join the first six characters of the Name column and the first eight characters of the mobile phone column
Index.
Mysql > alter table test add index index_name_shouji (name (6), shouji (8))
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > show index from test\ G
* * 1. Row *
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
* 2. Row * *
Table: test
Non_unique: 1
Key_name: index_name_shouji
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: 6
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
* 3. Row * *
Table: test
Non_unique: 1
Key_name: index_name_shouji
Seq_in_index: 2
Column_name: shouji
Collation: A
Cardinality: 5
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
twenty-eight。 Query the records whose mobile phone number starts with 159and the name is skybboy.
Mysql > select * from test where name='skyboy' and shouji like '159%'
+-- +
| | id | age | name | shouji | |
+-- +
| | 5 | 98 | skyboy | 15907999899 | |
+-- +
1 row in set (0.00 sec)
twenty-nine。 Query the execution plan of the above statement (whether to use a federated index, etc.).
Mysql > explain select * from test where name='skyboy' and shouji like '159%'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | test | range | index_name_shouji | index_name_shouji | 32 | NULL | 1 | Using where |
+-- +
1 row in set (0.00 sec)
Mysql > explain select * from test where name='skyboy' and shouji like '159%'\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: test
Type: range
Possible_keys: index_name_shouji
Key: index_name_shouji
Key_len: 32
Ref: NULL
Rows: 1
Extra: Using where
1 row in set (0.00 sec)
thirty。 Change the engine of the test table to MyISAM.
Prior to version 5.1 of MySQL database, the default engine was MyISAM
InnoDB is the default engine for MySQL database after version 5.5.
Mysql > show create table test\ G # View test table engine
* * 1. Row *
Table: test
Create Table: CREATE TABLE `test` (
`id`int (4) NOT NULL DEFAULT'0'
`age`tinyint (2) DEFAULT NULL
`name` varchar (16) DEFAULT NULL
`roomji` char (11) DEFAULT NULL
PRIMARY KEY (`id`)
KEY `index_name_ roomji` (`name` (6), `roomji` (8))
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
Mysql > alter table test ENGINE=MyISAM; # modify the default engine
Query OK, 5 rows affected (0.14 sec)
Records: 5 Duplicates: 0 Warnings: 0
Mysql > show create table test\ G
* * 1. Row *
Table: test
Create Table: CREATE TABLE `test` (
`id`int (4) NOT NULL DEFAULT'0'
`age`tinyint (2) DEFAULT NULL
`name` varchar (16) DEFAULT NULL
`roomji` char (11) DEFAULT NULL
PRIMARY KEY (`id`)
KEY `index_name_ roomji` (`name` (6), `roomji` (8))
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
