本文详细介绍了如何在Linux系统中安装MySQL5.7，包括创建仓库文件、安装服务器、启用服务、设置初始密码和安全初始化。接着，文章展示了数据库和表的创建、删除、查看及用户管理等操作，包括DDL和DML语法，以及授权和撤销权限的方法。内容涵盖了MySQL的基本管理和使用。
mysql 安装和数据库/表 的操作
安装mysql5.7的版本
目前还没有EL8版本的MySQL仓库，所以我们这里用EL7的代替，创建一个新的仓库文件
[root@czh ~]# vi /etc/yum.repos.d/mysql-community.repo
将以下内容粘贴到新建的仓库文件中
[root@czh ~]# sudo vi /etc/yum.repos.d/mysql-community.repo
[mysql57-community]
name=MySQL
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
[mysql-connectors-community]
name=MySQL
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=0
[mysql-tools-community]
name=e
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/
enabled=1
gpgcheck=0
[root@czh ~]# sudo dnf --enablerepo=mysql57-community install mysql-community-server
开始安装
[root@czh ~]# dnf module reset mysql && sudo dnf module disable mysql
上次元数据过期检查：1 day, 9:05:28 前，执行于 2021年04月27日 星期二 02时04分32秒。
依赖关系解决。
无需任何处理。
完毕！
上次元数据过期检查：1 day, 9:05:30 前，执行于 2021年04月27日 星期二 02时04分32秒。
依赖关系解决。
=========================================================================================
软件包 架构 版本 仓库 大小
=========================================================================================
禁用模块:
mysql
事务概要
=========================================================================================
确定吗？[y/N]： y
完毕！
安装，可以看到安装的是mysql 5.7的包
[root@czh ~]# dnf --enablerepo=mysql57-community install mysql-community-server
MySQL 5.7 Community Server 532 kB/s | 2.2 MB 00:04
MySQL Connectors Community 46 kB/s | 107 kB 00:02
MySQL Tools Community 381 kB/s | 630 kB 00:01
上次元数据过期检查：0:00:01 前，执行于 2021年04月28日 星期三 11时10分57秒。 依赖关系解决。
=========================================================================================
软件包 架构 版本 仓库 大小
=========================================================================================
安装:
mysql-community-server x86_64 5.7.34-1.el7 mysql57-community 173 M
安装依赖关系:
mysql-community-client x86_64 5.7.34-1.el7 mysql57-community 25 M
mysql-community-common x86_64 5.7.34-1.el7 mysql57-community 310 k
mysql-community-libs x86_64 5.7.34-1.el7 mysql57-community 2.4 M
ncurses-compat-libs x86_64 6.1-7.20180224.el8 baseos 331 k
开启服务，并且设置服务自动启动
[root@czh ~]# sudo systemctl enable --now mysqld.service
获取初始的密码
[root@czh ~]# grep 'A temporary password' /var/log/mysqld.log |tail -1
2021-04-28T16:02:16.144741Z 1 [Note] A temporary password is generated for root@localhost: YwCzZzuIb1%q
安全初始化
[root@czh ~]# sudo mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
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: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y //更改root的密码((按y | y表示是，按其他键表示否）
New password:
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y //是否继续使用提供的密码？（按y | 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 //删除匿名用户(按y | 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 //不允许根用户远程登录(按y | y表示是，其他任何键表示否）
... 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.
修改成功登录进去看看
[root@czh ~]# mysql -uroot -pCzh1.com
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 5
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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
### mysql的程序组成
客户端
mysql：CLI交互式客户端程序
mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
mysqldump：mysql备份工具
mysqladmin
服务器端
mysqld
### mysql工具使用
常用的OPTIONS：
-u USERNAME //指定用户名，默认为root
-h HOST //指定服务器主机，默认为localhost，推荐使用ip地址
-p PASSWORD //指定用户的密码
-P # //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
-V //查看当前使用的mysql版本
[root@czh ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using EditLine wrapper`
指定用户和密码
[root@czh ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
服务器监听的两种socket地址
| socket类型 | 说明 |
|---|---|
| ip socket | 默认监听在tcp的3306端口，支持远程通信 |
| uinx sock | 监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock） 仅支持本地通信 server地址只能是：localhost，127.0.0.1 |
## mysql数据库操作
### DDL操作
#### 数据库操作
在数据库里面输入的每一条命令都需要最后加上; 不过后面也可以在补上，不过记得要加上；才生效
一般数据库里面不区分大小写，写大写小都可以，写大是为了区分。
5 7版本的mysql进去数据库的时候，不会显示到底进没进去，只有Database changed,表示成功了
```shell
添加数据库
mysql> CREATE DATABASE IF NOT EXISTS czh;
当前有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| czh |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
删除数据库
mysql> DROP DATABASE IF EXISTS czh;
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> CREATE DATABASE czh
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> use czh
Database changed /表示进去成功了
创建数据表
mysql> CREATE TABLE czh (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> desc czh
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除数据表
mysql> show tables;
+---------------+
| Tables_in_czh |
+---------------+
| czh |
+---------------+
1 row in set (0.00 sec)
mysql> drop table czh;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
用户操作
mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
IP地址，如：172.16.12.129
通配符
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符
数据库用户chenzihan的创建
mysql> create user 'chenzihan'@'192.168.31.128' IDENTIFIED BY 'Czh1.com'
-> ;
Query OK, 0 rows affected (0.05 sec)
mysql> quit
Bye
//用新创建的用户密码登录
[root@czh ~]# mysql -uchenzihan -pCzh1.com -h192.168.31.128
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 3
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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@czh ~]# mysql -uroot -pCzh1.com
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 4
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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> DROP USER 'chenzihan'@'192.168.31.128'
-> ;
Query OK, 0 rows affected (0.00 sec)
//删除成功了
//登出去
[root@czh ~]# mysql -uchenzihan -pCzh1.com -h192.168.31.128
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'czh' is not allowed to connect to this MySQL server
再次用chenzihan用户登录，发现不能登录
查看命令show
mysql> SHOW CHARACTER SET; //查看支持的所有字符集
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
mysql> SHOW ENGINES; //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> SHOW DATABASES; //查看当前数据库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenzihan |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> SHOW TABLES FROM chenzihan; //不进入某数据库而列出其包含的所有表
+---------------------+
| Tables_in_chenzihan |
+---------------------+
| chenzihan |
+---------------------+
1 row in set (0.00 sec)
mysql> DESC chenzihan.chenzihan; //查看表结构
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> SHOW CREATE TABLE chenzihan.chenzihan; //查看某表的创建命令
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| chenzihan | CREATE TABLE `chenzihan` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use chenzihan ；
Database changed
mysql> SHOW TABLE STATUS LIKE 'chenzihan'\G //查看表的状态
*************************** 1. row ***************************
Name: chenzihan
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: 2021-04-29 08:01:53
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
获取帮助
语法help keyword
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]
。。。。
。。。。
DML语法
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
insert 增加
mysql> insert chenzihan(id,name,age) values(7,'lisi',100),(9,'wangwu',3),(8,'wangwu',20);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from chenzihan;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 20 |
| 4 | jerry | 23 |
| 5 | zhangsan | 15 |
| 6 | zhangsan | 20 |
| 7 | lisi | 100 |
| 9 | wangwu | 3 |
| 8 | wangwu | 20 |
+----+-----------+------+
9 rows in set (0.00 sec)
select语法
字段column表示法
| 表示符 | 代表什么 |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | >，<，>=，<=，=，!= between [column]and[column] like：模糊匹配 rlike：基于正则表达式进行模式匹配 is not null非空 is null：空 |
| 条件逻辑操作 | and or not |
order by：排序，默认为升序（ASC）
| oeder by语句 | 含义 |
|---|---|
| order by [column] | 根据字段进行升序排序 |
| order by[column] | 根据字段进行降序排序 |
| order by [column] limit 2 | 根据字段进行升序并只取前两行 |
| order by [column] limit 1,2 | 更加字段进行升序排序并且略过第一个结果取后面的2个结果 |
DML操作之查操作select
mysql> select * from chenzihan; //查询chenzihan 表
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 20 |
| 4 | jerry | 23 |
| 5 | zhangsan | 15 |
| 6 | zhangsan | 20 |
| 7 | lisi | 100 |
| 9 | wangwu | 3 |
| 8 | wangwu | 20 |
+----+-----------+------+
9 rows in set (0.00 sec)
mysql> SELECT NAME FROM chenzihan; //根据查询姓名
+-----------+
| NAME |
+-----------+
| chenzihan |
| han |
| tom |
| jerry |
| zhangsan |
| zhangsan |
| lisi |
| wangwu |
| wangwu |
| czh |
+-----------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM chenzihan ORDER BY age; //排序
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 10 | czh | NULL |
| 9 | wangwu | 3 |
| 5 | zhangsan | 15 |
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 20 |
| 6 | zhangsan | 20 |
| 8 | wangwu | 20 |
| 4 | jerry | 23 |
| 7 | lisi | 100 |
+----+-----------+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM chenzihan ORDER BY age desc; //由大到小
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 100 |
| 4 | jerry | 23 |
| 3 | tom | 20 |
| 6 | zhangsan | 20 |
| 8 | wangwu | 20 |
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 5 | zhangsan | 15 |
| 9 | wangwu | 3 |
| 10 | czh | NULL |
+----+-----------+------+
10 rows in set (0.00 sec)
mysql> select * from chenzihan order by age limit 2 //查询倒数两个
-> ;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 10 | czh | NULL |
| 9 | wangwu | 3 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from chenzihan order by age limit 1,2;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 9 | wangwu | 3 |
| 5 | zhangsan | 15 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> select * from chenzihan where age >=20 //年龄大于等于25 的
-> ;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 3 | tom | 20 |
| 4 | jerry | 23 |
| 6 | zhangsan | 20 |
| 7 | lisi | 100 |
| 8 | wangwu | 20 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> select * from chenzihan where age between 18 and 23;//年龄在18到23
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 20 |
| 4 | jerry | 23 |
| 6 | zhangsan | 20 |
| 8 | wangwu | 20 |
+----+-----------+------+
6 rows in set (0.00 sec)
不包括null 和只看null
mysql> select * from chenzihan where age is null;
+----+------+------+
| id | name | age |
+----+------+------+
| 10 | czh | NULL |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from chenzihan where age is not null;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 20 |
| 4 | jerry | 23 |
| 5 | zhangsan | 15 |
| 6 | zhangsan | 20 |
| 7 | lisi | 100 |
| 9 | wangwu | 3 |
| 8 | wangwu | 20 |
+----+-----------+------+
9 rows in set (0.00 sec)
update语句
将chenzihan表里的tom的年龄改为12
mysql> update chenzihan set age =12 where name = 'tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from chenzihan
-> ;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 12 |
| 4 | jerry | 23 |
| 5 | zhangsan | 15 |
| 6 | zhangsan | 20 |
| 7 | lisi | 100 |
| 9 | wangwu | 3 |
| 8 | wangwu | 20 |
| 10 | czh | NULL |
+----+-----------+------+
10 rows in set (0.00 sec)
delete语法
mysql> delete from chenzihan where name = 'zhangsan' and id = 6 ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from chenzihan;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | chenzihan | 18 |
| 2 | han | 18 |
| 3 | tom | 12 |
| 4 | jerry | 23 |
| 5 | zhangsan | 15 |
| 7 | lisi | 100 |
| 9 | wangwu | 3 |
| 8 | wangwu | 20 |
| 10 | czh | NULL |
+----+-----------+------+
9 rows in set (0.00 sec)
删除表的话就是后面就不跟东西了
delete from chenzihan
DCL语法
创建授权grant
权限类型
| 权限类型 | 含义 |
|---|---|
| all | 所有权限 |
| select | 读取内容的权限 |
| insert | 插入内容的权限 |
| update | 修改内容的权限 |
| delete | 删除内容的权限 |
指定要操作的对象
| 表示方式 | 含义 |
|---|---|
| . | 所有库的所有表 |
| database | 指定库的所有表 |
| database.table | 指定库的指定表 |
授权root用户在所有位置上远程登录访问所有数据库，并设置登录密码
mysql> grant all on *.* to 'root'@'%' identified by 'Czh1.com';
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看当前用户的信息
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看指定用户的授权信息
mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
取消授权REVOKE
mysql> revoke all on *.* from 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
