mysql使用

最新推荐文章于 2022-10-02 22:27:29 发布

藍胖子胖

最新推荐文章于 2022-10-02 22:27:29 发布

阅读量830

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_72898921/article/details/125986552

版权

3.mysql的程序组成

客户端

[root@132 ~]# mysql
mysql                      mysqld-debug               mysql_secure_installation
mysqladmin                 mysqld_pre_systemd         mysqlshow
mysqlbinlog                mysqldump                  mysqlslap
mysqlcheck                 mysqldumpslow              mysql_ssl_rsa_setup
mysql_config               mysqlimport                mysql_tzinfo_to_sql
mysql_config-64            mysql_install_db           mysql_upgrade
mysql_config_editor        mysql_plugin               
mysqld                     mysqlpump

mysql：CLI交互式客户端程序
mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令

[root@132 ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 50 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

然后用新密码登录
[root@132 ~]# mysql -uroot -pHuangxiaodong000..
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>

mysqldump：mysql备份工具
mysqladmin
- 服务器端
mysqld

[root@132 ~]# ps -ef|grep mysqld
mysql       1070       1  0 20:52 ?        00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root        1705    1680  0 21:18 pts/0    00:00:00 grep --color=auto mysqld

3.1 mysql工具使用

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址

[root@132 ~]# mysql -uroot -pHuangxiaodong000.. -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> 

另一种
[root@132 ~]# mysql -uroot -pHuangxiaodong000.. -hlocalhost

-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

默认端口就是3306的话就不用-P 如果是端口是其他数字比如说3308就得指定新的端口
[root@132 ~]# mysql -uroot -pHuangxiaodong000.. -P3308

-V //查看当前使用的mysql版本

[root@132 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

-e //不登录mysql执行sql语句后退出，常用于脚本

用于写脚本的话比较方便，因为写脚本近数据库很麻烦
[root@132 ~]# mysql -uroot -pHuangxiaodong000.. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@132 ~]#

3.2 服务器监听的两种socket地址


[root@132 ~]# mysql -uroot -pHuangxiaodong000.. -S /var/lib/mysql/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

/创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database if not exists hxd;（if not exists适用于数据库里有很多数据库，不敢确定有没有重名的话，就多加这个命令！意思是如果原本数据库里有hxd数据库就会退出创建，如果没有hxd数据库就会直接创建！）

Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hxd                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//查看当前实例有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hxd                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;

mysql> drop database if exists hxd;（if exists和上方创建可选命令一样的意思，也可以不加这则命令，命令的意思是如果hxd数据库在就删除，不在就退出！）

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

4.1.2 表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=’存储引擎类型’;

mysql> create table linux(id int not null,name varchar(10) not null,age tinyint(3));
Query OK, 0 rows affected (0.05 sec)

//查看当前数据库有哪些表

mysql> show tables;
+---------------+
| Tables_in_hxd |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql>

查看表的结构信息，创建好的表定义的每个字段的名称

mysql> desc linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(3)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//删除表
//语法：DROP TABLE [ IF EXISTS ] ‘table_name’;

mysql> show tables;
+---------------+
| Tables_in_hxd |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql> drop table linux;（删除了表并不能删掉表的结构信息）
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>

4.1.3 用户操作

mysql用户帐号由两部分组成，如’USERNAME’@’HOST’，表示此USERNAME只能从此HOST上远程登录

这里（’USERNAME’@’HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：172.16.12.129
通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符

//数据库用户创建
//语法：CREATE USER ‘username’@’host’ [IDENTIFIED BY ‘password’];

mysql> create user 'lpz'@'localhost' identified by 'Huangxiaodong000..';
Query OK, 0 rows affected (0.00 sec)

mysql>

//使用新创建的用户和密码登录

再开一个终端看能否登录进去
[root@132 ~]# mysql -ulpz -pHuangxiaodong000..
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> select * from mysql.user\G
*************************** 4. row ***************************
                  Host: localhost
                  User: lpz
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *A580C8A676DDA7ADA6B189E869A9970CDAE12C0B
      password_expired: N
 password_last_changed: 2022-07-25 22:27:33
     password_lifetime: NULL
        account_locked: N
4 rows in set (0.00 sec)

mysql>

//删除数据库用户
//语法：DROP USER ‘username’@’host’;

mysql> drop user 'lpz'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>

4.1.4 查看命令SHOW

//查看支持的所有字符集

//查看当前数据库支持的所有存储引擎

//查看数据库信息

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hxd                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//不进入某数据库而列出其包含的所有表

mysql> show tables from lpz;
ERROR 1049 (42000): Unknown database 'lpz'
mysql> show tables from hxd;
+---------------+
| Tables_in_hxd |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql>

//查看表结构
//语法：DESC [db_name.]table_name;

mysql> desc hxd.linux; 绝对路径
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(3)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)



mysql> use hxd; 相对路径
Database changed
mysql> desc linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(3)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

//查看某表的创建命令，创建表的过程

mysql> show create table linux;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| linux | CREATE TABLE `linux` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` tinyint(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

//查看某表的状态

mysql> show tables status like 'linux'\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'status like 'linux'' at line 1
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> show table status like 'linux'\G
*************************** 1. row ***************************
           Name: linux
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-07-25 22:33:13

4.1.5 获取帮助

//获取命令使用帮助
//语法：HELP keyword;

mysql> help create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html



mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | CHECK (expr)
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
}

data_type:
    (see https://dev.mysql.com/doc/refman/5.7/en/data-types.html)

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

query_expression:
    SELECT ...   (Some valid select or union statement)

CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.

By default, tables are created in the default database, using the
InnoDB storage engine. An error occurs if the table exists, if there is
no default database, or if the database does not exist.

MySQL has no limit on the number of tables. The underlying file system
may have a limit on the number of files that represent tables.
Individual storage engines may impose engine-specific constraints.
InnoDB permits up to 4 billion tables.

For information about the physical representation of a table, see
https://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

4.2 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

4.2.1 INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,…)] {VALUES | VALUE} (value1,…),(…),…

表中插入数据
mysql> insert into linux(id,name,age) values(1,'zhangsan',20),(2,'lisi',21),(3,'wangwu',22),(4,'lanpangzi',23),(5,'jiqimao',24);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


查看“linux”表里的5条记录，*号表示id,name,age的所有，所有，所有字段。
mysql> select * from linux;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangsan  |   20 |
|  2 | lisi      |   21 |
|  3 | wangwu    |   22 |
|  4 | lanpangzi |   23 |
|  5 | jiqimao   |   24 |
+----+-----------+------+
5 rows in set (0.00 sec)


也可以只显示你想要看到的字段
mysql> select id,name,age from linux;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangsan  |   20 |
|  2 | lisi      |   21 |
|  3 | wangwu    |   22 |
|  4 | lanpangzi |   23 |
|  5 | jiqimao   |   24 |
+----+-----------+------+
5 rows in set (0.00 sec)

mysql> 


还可以修改字段的名字，临时显示；别名
mysql> select name from linux;
+-----------+
| name      |
+-----------+
| zhangsan  |
| lisi      |
| wangwu    |
| lanpangzi |
| jiqimao   |
+-----------+
5 rows in set (0.00 sec)

mysql> select name as 姓名 from linux;
+-----------+
| 姓名      |
+-----------+
| zhangsan  |
| lisi      |
| wangwu    |
| lanpangzi |
| jiqimao   |
+-----------+
5 rows in set (0.00 sec)

mysql> select name as 姓名,age as 年龄 from linux;
+-----------+--------+
| 姓名      | 年龄   |
+-----------+--------+
| zhangsan  |     20 |
| lisi      |     21 |
| wangwu    |     22 |
| lanpangzi |     23 |
| jiqimao   |     24 |
+-----------+--------+
5 rows in set (0.00 sec)

4.2.2 SELECT语句

字段column表示法

条件判断语句WHERE

操作符：
=
mysql> select * from linux where name = 'jiqimao';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  5 | jiqimao |   24 |
+----+---------+------+
1 row in set (0.00 sec)



>=
mysql> select * from linux where age >= 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangsan  |   20 |
|  2 | lisi      |   21 |
|  3 | wangwu    |   22 |
|  4 | lanpangzi |   23 |
|  5 | jiqimao   |   24 |
+----+-----------+------+
5 rows in set (0.00 sec)



<=
mysql> select * from linux where age <= 20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
+----+----------+------+
1 row in set (0.00 sec)



>
mysql> select * from linux where age > 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | lisi      |   21 |
|  3 | wangwu    |   22 |
|  4 | lanpangzi |   23 |
|  5 | jiqimao   |   24 |
+----+-----------+------+
4 rows in set (0.00 sec)



<
mysql> select * from linux where age < 23;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | lisi     |   21 |
|  3 | wangwu   |   22 |
+----+----------+------+
3 rows in set (0.00 sec)



!=
mysql> select * from linux where age != 23;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | lisi     |   21 |
|  3 | wangwu   |   22 |
|  5 | jiqimao  |   24 |
+----+----------+------+
4 rows in set (0.00 sec)



between 包括20-23的全部列出
mysql> select * from linux where age between 20 and 23;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangsan  |   20 |
|  2 | lisi      |   21 |
|  3 | wangwu    |   22 |
|  4 | lanlipangzi |   23 |
+----+-----------+------+
4 rows in set (0.00 sec)



like模糊匹配
mysql> select * from linux where name like 'z%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
+----+----------+------+
1 row in set (0.00 sec)


mysql> select * from linux where name like '%ma%';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  5 | jiqimao |   24 |
+----+---------+------+
1 row in set (0.00 sec)



is not null 非空
mysql> select * from linux where name is not null;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangsan  |   20 |
|  2 | lisi      |   21 |
|  3 | wangwu    |   22 |
|  4 | lanpangzi |   23 |
|  5 | jiqimao   |   24 |
+----+-----------+------+
5 rows in set (0.00 sec)

表里面加一个“工资”的字段
mysql> alter table linux add salary float;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from linux;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
|  5 | jiqimao   |   24 |   NULL |
+----+-----------+------+--------+
5 rows in set (0.00 sec)

mysql> select * from linux where salary is null;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
|  5 | jiqimao   |   24 |   NULL |
+----+-----------+------+--------+
5 rows in set (0.00 sec)




条件逻辑操作
and 以两个条件为基准，必须满足两个条件才能显示出来
mysql> select * from linux;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
|  5 | jiqimao   |   24 |   NULL |
+----+-----------+------+--------+
5 rows in set (0.00 sec)

mysql> select * from linux where name = 'jiqimao' and id = '5';
+----+---------+------+--------+
| id | name    | age  | salary |
+----+---------+------+--------+
|  5 | jiqimao |   24 |   NULL |
+----+---------+------+--------+
1 row in set (0.00 sec)

mysql> select * from linux where name = 'lanpangzi' and age = '23';
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  4 | lanpangzi |   23 |   NULL |
+----+-----------+------+--------+
1 row in set (0.00 sec)


or或者的意思，两个条件只需要满足一个就可以显示出来
mysql> select * from linux where id = '10' or age = '23';
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  4 | lanpangzi |   23 |   NULL |
+----+-----------+------+--------+
1 row in set (0.00 sec)

mysql> select * from linux where id = '10' or age = '23' or name = 'xxxx';
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  4 | lanpangzi |   23 |   NULL |
+----+-----------+------+--------+
1 row in set (0.01 sec)



not取反，例如把jiqimao以外的全部列出
mysql> select * from linux where not name = 'jiqimao';
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
+----+-----------+------+--------+
4 rows in set (0.00 sec)

ORDER BY：排序，默认为升序（ASC）

插入记录

mysql> insert linux(id,name,age,salary) values(6,'yasuo',60,1000),(7,'yongen',61,1100),(8,'wuqi',62,1200),(9,'jinkesi',26,1000),(10,'wei',27,1200);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from linux;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
|  5 | jiqimao   |   24 |   NULL |
|  6 | yasuo     |   60 |   1000 |
|  7 | yongen    |   61 |   1100 |
|  8 | wuqi      |   62 |   1200 |
|  9 | jinkesi   |   26 |   1000 |
| 10 | wei       |   27 |   1200 |
+----+-----------+------+--------+
10 rows in set (0.00 sec)



order by升序 这里我按照年龄去排序
mysql> select * from linux order by age;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
|  5 | jiqimao   |   24 |   NULL |
|  9 | jinkesi   |   26 |   1000 |
| 10 | wei       |   27 |   1200 |
|  6 | yasuo     |   60 |   1000 |
|  7 | yongen    |   61 |   1100 |
|  8 | wuqi      |   62 |   1200 |
+----+-----------+------+--------+
10 rows in set (0.00 sec)



倒叙后面加一个desc
mysql> select * from linux order by age desc;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  8 | wuqi      |   62 |   1200 |
|  7 | yongen    |   61 |   1100 |
|  6 | yasuo     |   60 |   1000 |
| 10 | wei       |   27 |   1200 |
|  9 | jinkesi   |   26 |   1000 |
|  5 | jiqimao   |   24 |   NULL |
|  4 | lanpangzi |   23 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  1 | zhangsan  |   20 |   NULL |
+----+-----------+------+--------+
10 rows in set (0.00 sec)



升序并只取前2个结果
mysql> select * from linux order by age limit 2;
+----+----------+------+--------+
| id | name     | age  | salary |
+----+----------+------+--------+
|  1 | zhangsan |   20 |   NULL |
|  2 | lisi     |   21 |   NULL |
+----+----------+------+--------+
2 rows in set (0.00 sec)



升序并且掠过第1个结果去后面的2个结果
mysql> select * from linux order by age limit 1,2;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  2 | lisi   |   21 |   NULL |
|  3 | wangwu |   22 |   NULL |
+----+--------+------+--------+
2 rows in set (0.00 sec)

4.2.3 update语句

//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,…] [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];

例如把lanpangzi的工资改成10000
mysql> update linux set salary = 10000 where name = 'lanpangzi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from linux;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   21 |   NULL |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |  10000 |
|  5 | jiqimao   |   24 |   NULL |
|  6 | yasuo     |   60 |   1000 |
|  7 | yongen    |   61 |   1100 |
|  8 | wuqi      |   62 |   1200 |
|  9 | jinkesi   |   26 |   1000 |
| 10 | wei       |   27 |   1200 |
+----+-----------+------+--------+
10 rows in set (0.00 sec)



更改lisi的年龄以及工资
mysql> update linux set age = 30,salary = 300 where name = 'lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from linux;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   30 |    300 |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |  10000 |
|  5 | jiqimao   |   24 |   NULL |
|  6 | yasuo     |   60 |   1000 |
|  7 | yongen    |   61 |   1100 |
|  8 | wuqi      |   62 |   1200 |
|  9 | jinkesi   |   26 |   1000 |
| 10 | wei       |   27 |   1200 |
+----+-----------+------+--------+
10 rows in set (0.00 sec)

4.2.4 delete语句

//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];

删除其中的记录
mysql> delete from linux where name = 'wei';
Query OK, 1 row affected (0.01 sec)

mysql> select * from linux;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangsan  |   20 |   NULL |
|  2 | lisi      |   30 |    300 |
|  3 | wangwu    |   22 |   NULL |
|  4 | lanpangzi |   23 |  10000 |
|  5 | jiqimao   |   24 |   NULL |
|  6 | yasuo     |   60 |   1000 |
|  7 | yongen    |   61 |   1100 |
|  8 | wuqi      |   62 |   1200 |
|  9 | jinkesi   |   26 |   1000 |
+----+-----------+------+--------+
9 rows in set (0.00 sec)

4.2.5 truncate语句

truncate与delete的区别：

delete删除只会删除里面的内容，但是不会删除表和结构，好处是通过日志可以恢复数据，坏处是非常占用资源空间
mysql> delete from linux;
Query OK, 5 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_hxd |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql> desc linux;表的结构也在
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(10) | NO   |     | NULL    |       |
| age    | tinyint(3)  | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)



truncate删除的区别是数据无法恢复，删除的也比delete快，有外键约束引用的表删除不了
mysql> truncate linux;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_hxd |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql> desc linux;表的结构也在
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(10) | NO   |     | NULL    |       |
| age    | tinyint(3)  | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4.3 DCL操作

4.3.1 创建授权grant

权限类型(priv_type)

all所有权限
mysql> grant all on *.* to 'hxd'@'localhost' identified by 'Huangxiaodong000..



root用户可以在192.168.92.132上远程登陆
mysql> grant all on *.* to 'root'@'192.168.92.132' identified by 'Huangxiaodong000..';
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权了之后要刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

然后在132关闭防火墙
[root@132 ~]# systemctl stop firewalld
[root@132 ~]# setenforce 0











GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqingge         |
+--------------------+
5 rows in set (0.00 sec)

//授权wangqing用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'wangqing'@'localhost' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL ON *.* TO 'wangqing'@'127.0.0.1' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权wangqing用户在172.16.12.129上远程登录访问wangqingge数据库
mysql> GRANT ALL ON wangqingge.* TO 'wangqing'@'172.16.12.129' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权wangqing用户在所有位置上远程登录访问wangqingge数据库
mysql> GRANT ALL ON *.* TO 'wangqing'@'%' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

打开Navicat Premium点击连接并选中MySQL

4.3.2 查看授权

取消授权REVOKE

mysql> revoke all on *.* from 'root'@'192.168.92.132';

一样刷新一下授权
flush privileges;

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

实战案例

1.搭建mysql服务
2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

mysql> create table aa(id int not null primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)

mysql> desc aa;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql数据类型

数值：

数值类型	大小（bytes）	范围（有符号）	范围（无符号）	用途
tinyint	1	(-128,127)	(0,255)	小整数值
smallint	2	(-32768,32767)	(0,65535)	大整数值
mediumint	3	(-8388608,8388607)	(0,16777215)	大整数值
int 或 integer	4	(-2147483648,2147483647)	(0,4294967295)	大整数值
bigint	8	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
float	4	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38))	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度，浮点数值
double	8	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度，浮点数值
decimal	对 decimal(M,D) ，如果 M>D ，为 M+2 否则为 D+2	依赖于 M 和 D 的值	依赖于 M 和 D 的值	小数值

日期和时间：

数值类型	大小（bytes）	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
year	1	1901/2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串：

数值类型	大小（bytes）	用途
char	0~255	定长字符串
varchar	0~65535	变长字符串
tinyblob	0~255	不超过 255 个字符的二进制字符串
tinytext	0~255	短文本字符串
blob	0~65535	二进制形式的长文本数据
text	0~65535	长文本数据
mediumblob	0~16777215	二进制形式的中等长度文本数据
mediumtext	0~16777215	中等长度文本数据
longblob	0~4294967295	二进制形式的极大文本数据
longtext	0~4294967295	极大文本数据