• 接口自带的功能
    1. \h 或 help 或 ?
    mysql> \h
    
    For information about MySQL products and services, visit:
    List of all MySQL commands:
    Note that all text commands must be first on line and end with ';'
    ?         (\?) Synonym for `help'.
    clear     (\c) Clear the current input statement.
    connect   (\r) Reconnect to the server. Optional arguments are db and host.
    delimiter (\d) Set statement delimiter.
       .
       .
       .
       .
    warnings  (\W) Show warnings after every statement.
    nowarning (\w) Don't show warnings after every statement.
    
    For server side help, type 'help contents'
    
    mysql>

    2. \G       #以json格式输出结果
    mysql> select user,host,password from mysql.user\G;
    *************************** 1. row ***************************
        user: root
        host: localhost
    password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
    *************************** 2. row ***************************
        user: root
        host: 10.0.0.%
    password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
    *************************** 3. row ***************************
        user: zabbix
        host: 10.0.0.%
    password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
    3 rows in set (0.02 sec)
    mysql> 
    

    3. \T 或 tee    #输出日志(自定义到那个文件)
    mysql> tee /tmp/test.log
    Logging to file '/tmp/test.log'
    mysql> select user,host,password from mysql.user;
    +--------+-----------+-------------------------------------------+
    | user   | host      | password                                  |
    +--------+-----------+-------------------------------------------+
    | root   | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | root   | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | zabbix | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    +--------+-----------+-------------------------------------------+
    3 rows in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@db01 ~]# cat /tmp/test.log 
    mysql> select user,host,password from mysql.user;
    +--------+-----------+-------------------------------------------+
    | user   | host      | password                                  |
    +--------+-----------+-------------------------------------------+
    | root   | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | root   | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | zabbix | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    +--------+-----------+-------------------------------------------+
    3 rows in set (0.00 sec)
    
    mysql> exit
    [root@db01 ~]# 


    4. \c 结束行程序 (相当于shell中的CTRL + c)
    mysql> select asjdjdsjk \c
    mysql> 

    5. \s 或 status    #查看数据库的基本状态
    mysql> \s
    --------------
    mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper
    
    Connection id:		2
    Current database:	
    Current user:		root@localhost
    SSL:			Not in use
    Current pager:		stdout
    Using outfile:		''
    Using delimiter:	;
    Server version:		5.6.36 Source distribution
    Protocol version:	10
    Connection:		Localhost via UNIX socket
    Server characterset:	utf8
    Db     characterset:	utf8
    Client characterset:	utf8
    Conn.  characterset:	utf8
    UNIX socket:		/application/mysql-5.6.36/tmp/mysql.sock
    Uptime:			19 min 47 sec
    
    Threads: 1  Questions: 9  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.007
    --------------
    
    mysql> 

    6. \. 或 source     #执行外部SQL脚本:二进制日志截取,备份出来的SQL脚本
    7. \u 或 use      #切换到某个数据库
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | testdb             |
    +--------------------+
    5 rows in set (0.02 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> 


     

  • 服务器端命令
    1. SQL:结构化的查询语言,mysql接口程序只负责接收SQL,传送给SQL层。
    2. SQL种类:
        DDL:数据库(对象)定义语言
        DCL:数据库控制语言(grant revoke)
        DML:数据(行)操作语言(update delete insert)
        DQL:数据库查询语言(show,select)

     

  • DDL操作
    1.定义库(库名,库基本属性)
    create database fxw;   (创建数据库)
    create schema fw;   (与上等效)
    show databases;      (查看数据库列表)
    create database fxw1 character set utf8;
    show create database fxw1;  (查看数据库创建过程)
    drop database fxw1;   (删除数据库)
    help  create database;
    字符集:  CHARACTER SET [=] charset_name  (可简写为charset)
    排序规则:COLLATE [=] collation_name

    改库的字符集:
    ALTER DATABASE [db_name] CHARACTER SET  charset_name COLLATE collation_name

    mysql> alter database fxw1 charset utf8mb4;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show create database fxw1;
    +----------+------------------------------------------------------------------+
    | Database | Create Database                                                  |
    +----------+------------------------------------------------------------------+
    | fxw1     | CREATE DATABASE `fxw1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
    +----------+------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> 



    2.定义表
    表名,表属性--列名,列定义--数据类型、约束、特殊列属性,表的索引信息。
    use fxw;
    创建:create table t1 (id int ,name varchar(20));
    查询:show tables;
              show create table t1;
              desc t1;    查看列属性
    删除:drop table t1;
    修改:
       (1)在表中添加一列
               alter table t1 add age int;
       (2)添加多列
               alter table t1 add bridate datetime, add gender enum('M','F');
       (3)在指定列后添加一列
               alter table t1 add stu_id int after id;
       (4)在表中最前添加一列
               alter table t1 add sid int first;
       (5)删除列
               alter table t1 drop sid;
       (6)修改列名
              alter table t1 change name stu_name varchar(20);
       (7)修改列属性
              alter table t1 modify stu_id varchar(20);
       (8)修改表名
              rename table t1 to student;
              alter table student rename  to stu;
     

  • DML语句:数据操作语言
    insert:
        use  lufei
        create table t1 (id int ,name varchar(20));
        insert into t1 values(1,'zhang3');
        select * from t1;
        insert into t1 values (2,'li4'),(3,'wang5'),(4,'ma6');
        insert into t1(name) values ('xyz');
    update:
        update  t1  set name='zhang33' ;   ----会更新表中所有行的name字段,比较危险。
        update  t1  set name='zhang55' where id=1;   ----update在使用时一般都会有where条件去限制。
    delete:
        delete from t1 ;  --删除表中所有行,比较危险。一行一行删除表中数据。
        delete from t1   where  id=2;
    DDL:
        truncate table t1;  ---在物理上删除表数据,速度比较快。
     

最后修改于 2019-08-05 09:01:34
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇