What are the knowledge points of MySQL? 03/28 Update SLTechnology News&Howtos

What are the knowledge points of MySQL?

2025-03-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points of MySQL". Friends who are interested might as well take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the knowledge points of MySQL"?

1. MySQL redeployment on CentOS6

1.1 delete log files and data files first

Rm-rf arch/* data/*

1.2 install again

Scripts/mysql_install_db-user=mysqladmin-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data

two。 Empty password modification

Show databases;-View the list of databases

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | testdb |

+-+

5 rows in set (0.00 sec)

Use mysql;-using the mysql database

Mysql > use mysql

Database changed

Show tables;-View the list of table in the mysql database

Mysql > show tables

+-+

| | Tables_in_mysql |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | innodb_index_stats |

| | innodb_table_stats |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | proxies_priv |

| | servers |

| | slave_master_info |

| | slave_relay_log_info |

| | slave_worker_info |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

28 rows in set (0.00 sec)

Desc user;-View the table structure of the user table

Mysql > desc user

+-+

+-+

| | Host | char (60) | NO | PRI | |

| | User | char (16) | NO | PRI | |

| | Select_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Insert_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Update_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Delete_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Drop_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Reload_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Shutdown_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Process_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | File_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Grant_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | References_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Index_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Show_db_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Super_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_tmp_table_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Lock_tables_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Execute_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Repl_slave_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Repl_client_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Show_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_user_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Event_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Trigger_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_tablespace_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | max_questions | int (11) unsigned | NO | | 0 | |

| | max_updates | int (11) unsigned | NO | | 0 | |

| | max_connections | int (11) unsigned | NO | | 0 | |

| | max_user_connections | int (11) unsigned | NO | | 0 | |

| | password_expired | enum ('Nickel Magazine Y') | NO | | N | |

Show create table user;-View the table structure of the user table, including the creation script for the user table

Mysql > show create table user\ G

* * 1. Row *

Table: user

Create Table: CREATE TABLE `user` (

`Host`char (60) COLLATE utf8_bin NOT NULL DEFAULT''

`User`char (16) COLLATE utf8_bin NOT NULL DEFAULT''

`Password` char (41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT''

`Select_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Insert_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Update_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Delete_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Create_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Drop_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Reload_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Shutdown_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Process_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`File_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Grant_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`References_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Index_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Alter_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Show_db_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Super_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Create_tmp_table_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Lock_tables_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Execute_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Repl_slave_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Repl_client_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Create_view_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Show_view_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Create_routine_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Alter_routine_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Create_user_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Event_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Trigger_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`Create_tablespace_ priv`enum ('Numeric Magazine Y') CHARACTER SET utf8 NOT NULL DEFAULT' N'

`ssl_ type`enum ('', 'ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT''

`ssl_ cipher` blob NOT NULL

`x509 _ issuer` blob NOT NULL

`x509 _ roomt` blob NOT NULL

`max_ peos`int (11) unsigned NOT NULL DEFAULT'0'

`max_ updates`int (11) unsigned NOT NULL DEFAULT'0'

`max_ connections` int (11) unsigned NOT NULL DEFAULT'0'

`max_user_ connections` int (11) unsigned NOT NULL DEFAULT'0'

`plugin` char (64) COLLATE utf8_bin DEFAULT 'mysql_native_password'

`authentication_ string` text COLLATE utf8_bin

`Numered`enum ('password_ Magnum') CHARACTER SET utf8 NOT NULL DEFAULT'N'

PRIMARY KEY (`Host`, `User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

1 row in set (0.00 sec)

-- query the allowed access address, login name and login password of the user table

Mysql > select Host,user,password from mysql.user

+-+

+-+

| | localhost | root |

| | hadoop000 | root |

| | 127.0.0.1 | root |

|:: 1 | root | |

| | localhost |

| | hadoop000 |

+-+

6 rows in set (0.00 sec)

-- change the password of the root account

Mysql > update mysql.user set password=password ('123456') where user='root'

Query OK, 4 rows affected (0.03 sec)

Rows matched: 4 Changed: 4 Warnings: 0

-- query the modified information

Mysql > select Host,user,password from mysql.user

+-- +

+-- +

| | localhost |

| | hadoop000 |

+-- +

6 rows in set (0.00 sec)

-- make the changes effective

Mysql > flush privileges

Mysql user's permission operation, and finally add flush privileges

3. Modify% any machine can access

After changing hadoop000 to'%', it means that any machine can access it.

Mysql > update mysql.user set Host='%' where Host='hadoop000'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

-- make the changes effective

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

4. Mysql command parameters

Mysql-uroot-p123456-can log in normally

Mysql-u root-p123456-can log in normally

Mysql-u root-p 123456-- cannot log in normally-p is followed by a space

Mysql-u root-p123456-h292.168.137.251-can log in normally with IP login

Mysql-u root-p123456-hlocalhost mysql-you can log in to the specified database

5. Create new DB and users

-- create a database

Mysql > create database testdb

Query OK, 1 row affected (0.09 sec)

-- user name and password to authorize login to the database

Mysql > grant all privileges on testdb to root@'%' identified by '123456'

Query OK, 0 rows affected (0.16 sec)

-- effective order

Mysql > flush privileges

Query OK, 0 rows affected (0.05 sec)

6. Check the current number of mysql links (in your work, you can make appropriate kill connections, note: use with caution! )

Mysql > show processlist

+-+

+-+

+-+

2 rows in set (0.00 sec)

-- if a process card is rammed or locked, the kill can be dropped after the relevant technicians or responsible persons have communicated.

Kill Id

7. Comparison of MySQL character types char and varchar

The same thing: char and varchar are both data types for storing strings

Differences:

1 the difference when storing data

Char defines a fixed length with a length range of 0-255. when storing, if the number of characters does not reach the defined number of digits, it will be stored in the database later with space completion.

Varchar is of variable length, with a length range of 0-65535. When storing, if the character does not reach the defined number of digits, it will not fill in the space.

2 occupancy byte difference

With latin encoding, one character takes up one byte.

You can use the above table to show that when defining char, no matter how many characters you store, it will take up the number of characters you define, while when using varchar, it will be related to the number of characters you enter, and one or two more bytes will be used to record the byte length. When the number of bytes occupied by data bits is less than 255, use 1 byte to record the length. When the number of bytes occupied by data bits is greater than 255, use 2 bytes to record the length. There is another person to record whether it is a Nul value.

8. The case of MySQL library name, table name and table data

8.1 Library and table names are case-sensitive by default, and column names are not case-sensitive:

-- list the databases

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | testdb |

+-+

5 rows in set (0.00 sec)

-- change the database name case, which is not consistent with the database name case in the database

Mysql > use TestDB

ERROR 1049 (42000): Unknown database 'TestDB'

-change the database, the database is lowercase, and the case of the database name in the database is the same

Mysql > use testdb

Database changed

Mysql > show tables

+-+

| | Tables_in_testdb |

+-+

| | user |

+-+

1 row in set (0.00 sec)

-- query the uppercase table name, which is not the same as the table name in the database

Mysql > select * from USER

ERROR 1146 (42S02): Table 'testdb.USER' doesn't exist

-query the lowercase table name, which is the same as the table name in the database

Mysql > select * from user

+-+ +

| | id | name |

+-+ +

| | 1 | ZHANGSAN |

| | 2 | zhangsan |

+-+ +

2 rows in set (0.00 sec)

-- query user table structure

Mysql > desc user

+-+ +

+-+ +

| | id | int (11) | YES | | NULL |

+-+ +

2 rows in set (0.02 sec)

Query user table data. Column names are uppercase and do not affect the query, indicating that table column names are not case-sensitive. Mysql > select ID,NAME from user

+-+ +

| | ID | NAME |

+-+ +

| | 1 | ZHANGSAN |

| | 2 | zhangsan |

+-+ +

2 rows in set (0.00 sec)

The solution to the case sensitivity of library and table names is to add the lower_case_table_names=1 configuration to the / etc/my.cnf file.

8.2 Table data is case-insensitive by default. Case sensitivity can be solved with the binary keyword.

8.2.1. Add the binary keyword after the sql statement of the query.

-- query without keywords

Mysql > select * from user where name='zhangsan'

+-+ +

| | id | name |

+-+ +

| | 1 | ZHANGSAN |

| | 2 | zhangsan |

+-+ +

2 rows in set (0.00 sec)

-- add keywords to query

Mysql > select * from user where binary name='zhangsan'

+-+ +

| | id | name |

+-+ +

| | 2 | zhangsan |

+-+ +

1 row in set (0.03 sec)

8.2.2. Use the binary keyword when creating a table

Delete the user table and then rebuild it

Mysql > drop table user

Query OK, 0 rows affected (0.04 sec)

Mysql > CREATE TABLE `user` (

-> `id` int (11) DEFAULT NULL

-> `name` varchar (200) binary DEFAULT NULL

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

Query OK, 0 rows affected (0.04 sec)

-- query the data of the user table

Mysql > select * from user

+-+ +

| | id | name |

+-+ +

| | 1 | ZHANGSAN |

| | 2 | zhangsan |

+-+ +

2 rows in set (0.00 sec)

Conditional query without binary keyword

Mysql > select * from user where name='zhangsan'

+-+ +

| | id | name |

+-+ +

| | 2 | zhangsan |

+-+ +

1 row in set (0.02 sec)

9. Modify the character set of mysql client and server

9.1 modify the database client character set

Modify the / etc/my.cnf configuration file as follows:

[client]

Port = 3306

Socket = / usr/local/mysql/data/mysql.sock

Default-character-set=utf8

9.2 View parameter file

Mysql > show variables like 'character%'

+-+

| | 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/local/mysql/share/charsets/ |

+-+

8 rows in set (0.01 sec)

9.3 modify server parameter file

[mysqld]

Port=3306

Character-set-server=utf8-sets the MySQL server code

Init-connect='SET NAMES utf8'-sets the encoding of the connectioin

Socket = / usr/local/mysql/data/mysql.sock

9.4 restart the mysql service

Hadoop001:mysqladmin:/usr/local/mysql: > service mysql restart

Shutting down MySQL.. [OK]

Starting MySQL. [OK]

9.5 View character set parameters

Mysql > show variables like 'character%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | utf8 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/local/mysql/share/charsets/ |

+-+

8 rows in set (0.00 sec)

Note: the character set has become utf-8.

9.6 View database information

-- View the information of mysql database

Mysql > show create database mysql

+-+

| | Database | Create Database |

+-+

+-+

1 row in set (0.00 sec)

-- View the information of testdb database

Mysql > show create database testdb

+-+ +

| | Database | Create Database |

+-+ +

+-+ +

1 row in set (0.00 sec)

Note: at this time, the character set of the database also becomes utf-8.

At this point, I believe you have a deeper understanding of "what are the knowledge points of MySQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.

*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.