MySQL的知识点有什么
发布时间：2021-12-21 11:12:24 所属栏目：MySql教程 来源：互联网
导读：本篇内容主要讲解MySQL的知识点有哪些，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习MySQL的知识点有哪些吧! 1.在CentOS6 上 MySQL重新部署 1.1 先删除 日志文件和数据文件 rm -rf arch/* data/* 1.2 再进行安
|
本篇内容主要讲解“MySQL的知识点有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL的知识点有哪些”吧! 1.在CentOS6 上 MySQL重新部署 1.1 先删除 日志文件和数据文件 rm -rf arch/* data/* 1.2 再进行安装 scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 2. 空密码修改 show databases; -- 查看数据库列表 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 5 rows in set (0.00 sec) use mysql; -- 使用mysql数据库 mysql> use mysql; Database changed show tables; -- 查看mysql数据库里面的table列表 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; -- 查看user表的表结构 mysql> desc user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | 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 | | | plugin | char(64) | YES | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+- show create table user; -- 查看user表的表结构，包含user表的创建脚本 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('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','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_subject` blob NOT NULL, `max_questions` 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, `password_expired` enum('N','Y') 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) -- 查询 user表的允许访问的地址，登录名和登录密码 mysql> select Host,user,password from mysql.user; +-----------+------+----------+ | Host | user | password | +-----------+------+----------+ | localhost | root | | | hadoop000 | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | hadoop000 | | | +-----------+------+----------+ 6 rows in set (0.00 sec) -- 修改root账号的密码 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 -- 查询修改后的信息 mysql> select Host,user,password from mysql.user; +-----------+------+-------------------------------------------+ | Host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | hadoop000 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | | | | hadoop000 | | | +-----------+------+-------------------------------------------+ 6 rows in set (0.00 sec) -- 使修改生效 mysql> flush privileges; mysql用户的权限操作，最后加 flush privileges; 3. 修改% 任意机器可访问 -- 将 hadoop000 修改为'%'后，表示任意机器都可访问 mysql> update mysql.user set Host='%' where Host='hadoop000'; 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) 4. mysql命令参数 mysql -uroot -p123456 -- 可以正常登录 mysql -u root -p123456 -- 可以正常登录 mysql -u root -p 123456 -- 不可以正常登录 -p后面有空格 mysql -u root -p123456 -h292.168.137.251 -- 可以正常登录 带IP登录 mysql -u root -p123456 -hlocalhost mysql -- 可以正登录 指定登录的数据库 5. 创建新DB和用户 -- 创建数据库 mysql> create database testdb; Query OK, 1 row affected (0.09 sec) -- 授权登录数据库的用户名和密码 mysql> grant all privileges on testdb to root@'%' identified by '123456'; Query OK, 0 rows affected (0.16 sec) -- 生效命令 mysql> flush privileges; Query OK, 0 rows affected (0.05 sec) 6. 查看当前mysql链接数 （在工作中，可以适当kill连接，注意：慎用！！！） mysql> show processlist; +----+------+----------------------+-------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------------+-------+---------+------+-------+------------------+ | 7 | root | localhost | mysql | Query | 0 | init | show processlist | | 11 | root | 192.168.90.157:63149 | NULL | Sleep | 13 | | NULL | +----+------+----------------------+-------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) -- 如果哪个process的卡，夯住，锁死，可以相关技术人员或相关责任人沟通好后kill掉 kill Id; 7. MySQL字符类型 char 和 varchar 的对比 相同点： char 和 varchar都是存储字符串的数据类型 差异点： 1 存数据时的区别 char定义的是固定长度，长度范围为0-255，存储时，如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中。 varchar是变长长度，长度范围为0-65535，存储时，如果字符没有达到定义的位数，也不会在后面补空格。 2 占用字节差别 以latin编码为便，一个字符占用一个字节。 MySQL的知识点有哪些 可以用上表来表示，当定义char时，不管你存入多少字符，都会占用到你定义的字符数，而用varchar时，则和你输入的字符数有关，会多一到两个字节来记录字节长度，当数据位占用的字节数小于255时，用1个字节来记录长度，数据位占用字节数大于255时，用2个字节来记录长度，还有一位来记录是否为nul值。 8. MySQL 库名，表名和表数据的大小写问题 8.1 库名和表名 默认是区分大小写的，列名不区分大小写： -- 列出数据库列表 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 5 rows in set (0.00 sec) -- 更改数据库，数据库名大小写，和数据库里面的数据库名大小写不一致 mysql> use TestDB; ERROR 1049 (42000): Unknown database 'TestDB' -- -- 更改数据库，数据库小写，和数据库里面的数据库名大小写一致 mysql> use testdb; Database changed mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | user | +------------------+ 1 row in set (0.00 sec) -- 查询大写的表名，和数据库里面的表名大小写不一致 mysql> select * from USER; ERROR 1146 (42S02): Table 'testdb.USER' doesn't exist -- 查询小写的表名，和数据库里面的表名大小写一致 mysql> select * from user; +------+----------+ | id | name | +------+----------+ | 1 | ZHANGSAN | | 2 | zhangsan | +------+----------+ 2 rows in set (0.00 sec) -- 查询user表结构 mysql> desc user; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) -- 查询user表数据，列名大写，不影响查询，说明表列名不区分大小写。 mysql> select ID,NAME from user; +------+----------+ | ID | NAME | +------+----------+ | 1 | ZHANGSAN | | 2 | zhangsan | +------+----------+ 2 rows in set (0.00 sec) 解决库名和表名 区分大小写的方法：在 /etc/my.cnf 文件里面添加 lower_case_table_names=1 配置。 8.2 表数据默认是不区分大小写的。可以通过binary关键字加以解决区分大小写。 8.2.1. 在查询的sql语句后面加上 binary关键字。 -- 不加关键字查询 mysql> select * from user where name='zhangsan'; +------+----------+ | id | name | +------+----------+ | 1 | ZHANGSAN | | 2 | zhangsan | +------+----------+ 2 rows in set (0.00 sec) -- 加关键字查询 mysql> select * from user where binary name='zhangsan'; +------+----------+ | id | name | +------+----------+ | 2 | zhangsan | +------+----------+ 1 row in set (0.03 sec) 8.2.2. 在建表时上 binary关键字 -- 将user表删除后再重建 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) -- 查询 user表的数据 mysql> select * from user; +------+----------+ | id | name | +------+----------+ | 1 | ZHANGSAN | | 2 | zhangsan | +------+----------+ 2 rows in set (0.00 sec) -- 不带 binary关键字的条件查询 mysql> select * from user where name='zhangsan'; +------+----------+ | id | name | +------+----------+ | 2 | zhangsan | +------+----------+ 1 row in set (0.02 sec) 9. 修改mysql客户端及服务器的字符集 9.1 修改数据库客户端字符集 修改/etc/my.cnf配置文件如下： [client] port = 3306 socket = /usr/local/mysql/data/mysql.sock default-character-set=utf8 9.2 查看参数文件 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 修改服务器参数文件 [mysqld] port=3306 character-set-server=utf8 -- 设置MySQL服务器编码 init-connect='SET NAMES utf8' -- 设置connectioin 的编码 socket = /usr/local/mysql/data/mysql.sock 到此，相信大家对“MySQL的知识点有哪些”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！ （编辑：海南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐