MYSQL入门操作和常规DML、DDL、DQL使用
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL入门操作和常规DML、DDL、DQL使用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6687字，纯文字阅读大概需要10分钟。
内容图文
刷新权限，将某些权限从硬盘刷新到内存中(修改root密码自带隐式刷新权限操作)
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
查看服务端口
mysql> show variables like ‘port‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
查看数据库字符集
mysql> show variables like ‘%char%‘; +--------------------------+----------------------------------+ | 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)
切数据库
mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.01 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> use mysql 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 database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) mysql> status -------------- mysql Ver 14.14 Distrib 5.7.25, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 6 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: ‘‘ Using delimiter: ; Server version: 5.7.25 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 days 16 hours 47 min 41 sec Threads: 1 Questions: 58 Slow queries: 0 Opens: 138 Flush tables: 1 Open tables: 131 Queries per second avg: 0.000 -------------- mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | 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 | +---------------------------+ 31 rows in set (0.00 sec) mysql>
创建允许远程登陆的用户
mysql> select host,user from mysql.user; +-----------+---------------+ | host | user | +-----------+---------------+ | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 3 rows in set (0.00 sec) mysql> grant all privileges on *.* to scott@‘%‘ identified by ‘tiger‘; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>
data目录中每个数据库都创建了一个文件夹，lbdata1、ib_logfile0、ib_logfile1三个是专门为innodb存放数据和日志的共享文件
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table tmp(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> desc tmp;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> insert into tmp value (‘2‘)
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tmp;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> \q
Bye
[root@localhost /usr/local/mysql/data]$ ls
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 localhost.localdomain.err localhost.localdomain.pid mysql performance_schema sys test
[root@localhost /usr/local/mysql/data]$ cd test
[root@localhost /usr/local/mysql/data/test]$ ls
db.opt tmp.frm tmp.ibd
每个数据库目录中的db.opt是数据库的信息,表名.frm是表的元信息,表名.ibd是数据信息,其中innodb_file_per_table参数来控制是否单独存储，5.7默认on，之前版本默认off
mysql> show variables like ‘%per_table%‘; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.01 sec) mysql>
.myd .myi是老的MyISAM存储引擎,myi是索引信息
mysql> create table tmp2(id int); Query OK, 0 rows affected (0.02 sec) mysql> show create table tmp; +-------+-----------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------+ | tmp | CREATE TABLE `tmp` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
MYSQL入门操作和常规DML、DDL、DQL使用
标签：highlight gtid 自带 rman dex err 存储引擎 val ike
本文系统来源：https://www.cnblogs.com/yongestcat/p/11371604.html
内容总结
以上是互联网集市为您收集整理的MYSQL入门操作和常规DML、DDL、DQL使用全部内容，希望文章能够帮你解决MYSQL入门操作和常规DML、DDL、DQL使用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】