MYSQL-mysql基本命令记录

金刚 · 发表于 2020-7-5 11:53:13

马上注册，结交更多好友，享用更多功能^_^

您需要登录才可以下载或查看，没有账号？立即注册

x

# 终端登录mysql（下面有显示内容）
------------------------------------------------------------
$ mysql -h localhost -u root -P 3306 -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
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>
---------------------------------------------------------------
# 查看数据库
----------------------------------------------------------------
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myemployees |
| mysql |
| mystudent |
| performance_schema |
| student |
| sys |
| test |
+--------------------+
8 rows in set (0.14 sec)
-------------------------------------------------------------------
# 创建数据库
-------------------------------------------------------------------
mysql> create database tester;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myemployees |
| mysql |
| mystudent |
| performance_schema |
| student |
| sys |
| test |
| tester |
+--------------------+
9 rows in set (0.02 sec)
--------------------------------------------------------------------
# 删除数据库
--------------------------------------------------------------------
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beijing_house_data |
| flask_sql_demo |
| flaskbooks |
| myemployees |
| mysql |
| mystudent |
| performance_schema |
| student |
| sys |
| test |
| test1 |
| test10 |
| test11 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
| tester |
+--------------------+
23 rows in set (0.00 sec)
mysql> drop database flaskbooks;
Query OK, 0 rows affected (0.50 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beijing_house_data |
| flask_sql_demo |
| myemployees |
| mysql |
| mystudent |
| performance_schema |
| student |
| sys |
| test |
| test1 |
| test10 |
| test11 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
| tester |
+--------------------+
22 rows in set (0.00 sec)
--------------------------------------------------------------------
# 进入数据库
--------------------------------------------------------------------
mysql> use tester;
Database changed
--------------------------------------------------------------------
# 查询表
--------------------------------------------------------------------
mysql> show tables;
Empty set (0.00 sec)
--------------------------------------------------------------------
# 查看其他数据库的表
--------------------------------------------------------------------
mysql> show tables from mysql;
+---------------------------+
| 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.01 sec)
-------------------------------------------------------------------
# 查看当前所在的数据库
-------------------------------------------------------------------
mysql> select database();
+------------+
| database() |
+------------+
| tester |
+------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------
# 创建表
-----------------------------------------------
mysql> create table stuinfo(
-> id int,
-> name varchar(20));
Query OK, 0 rows affected (0.71 sec)
-----------------------------------------------
# 查看表的结构
-----------------------------------------------
mysql> desc stuinfo;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)
-----------------------------------------------
# 表里面查看有哪些数据
-----------------------------------------------
mysql> select * from stuinfo;
Empty set (0.00 sec)
-----------------------------------------------
# 思路清晰化
-----------------------------------------------
登录mysql > 几个数据库(几个excel文件) > 几种表（ecxel文件中的几个sheet表）> 几个数据(excel表中插入的几个数据)
-----------------------------------------------
# 表中插入一行数据
-----------------------------------------------
mysql> insert into stuinfo (id,name) values(1,'john');
Query OK, 1 row affected (0.14 sec)
-----------------------------------------------
# 表中修改一行数据
-----------------------------------------------
mysql> update stuinfo set name='lilei' where id=1;
Query OK, 1 row affected (0.19 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-----------------------------------------------
# 表中删除一行数据
-----------------------------------------------
mysql> delete from stuinfo where id=1;
Query OK, 1 row affected (0.09 sec)
-----------------------------------------------
# 查看mysql版本
-----------------------------------------------
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.10 sec)
-----------------------------------------------
# mysql退出方法
-----------------------------------------------
mysql> exit
Bye
-----------------------------------------------
# mysql总结
-----------------------------------------------
###mysql的常见命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
show table 表名(
列名列类型,
列名列类型,
。。。
)
6.查看表结构
desc 表名;
7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql -V
-----------------------------------------------
# mysql语法
-----------------------------------------------
###mysql的语法规范
1.不区分大小写，但建议关键字大写，表名，列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释
单行注释：注释文字
单行注释：-- 注释文字
多行注释： /* 注释文字 */
-----------------------------------------------
# 查看表的字符集类型
-----------------------------------------------
mysql> show create table stuinfo;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| stuinfo | CREATE TABLE `stuinfo` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
-----------------------------------------------
# 然后通过语句修改数据表编码：
-----------------------------------------------
mysql> alter table stuinfo default character set utf8;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
-----------------------------------------------
# 更改列的编码
-----------------------------------------------
mysql> alter table stuinfo change name name varchar(100) character set utf8;
Query OK, 2 rows affected (1.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
-----------------------------------------------
# 创建数据库时指定默认字符集为utf8
-----------------------------------------------
mysql> create database test9 default character set utf8;
Query OK, 1 row affected (0.00 sec)
-----------------------------------------------
#

复制代码

账号		自动登录	找回密码
密码			立即注册

[技术交流] MYSQL-mysql基本命令记录

马上注册，结交更多好友，享用更多功能^_^

本帖被以下淘专辑推荐: