Linux入门之——Linux下数据库的管理操作（MySQL）

最新推荐文章于 2023-10-09 15:37:12 发布

置顶雪千颜

最新推荐文章于 2023-10-09 15:37:12 发布

阅读量782

点赞数

分类专栏：大学课程知识 Linux入门文章标签：数据库 mysql sql 运维 linux

本文链接：https://blog.csdn.net/qq_42452450/article/details/108390745

版权

大学课程知识同时被 2 个专栏收录

30 篇文章 2 订阅

订阅专栏

Linux入门

26 篇文章 1 订阅

订阅专栏

Linux下的数据库操作

数据库的基本管理操作

数据库登录

mysql 【-u用户名】【-p密码】

[root@localhost ~]# mysql -uroot -pWww.ly1.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 9
Server version: 8.0.21 MySQL Community Server - GPL

数据库查看

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

注：以分号结束

MySQL数据信息

数据表	数据信息
information_schema	元数据(数据库名称、数据表名称、字段名称)信息
mysql	用户信息、用户权限信息
performance_schema	数据库性能信息
sys	对information_schema数据库的简化，方便管理员查看

创建数据库

create 【database】【数据库名称】;

mysql> create database game;
Query OK, 1 row affected (0.05 sec)

查看数据库创建信息

show 【create】【database】【数据库名称】;

mysql> show create database game;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| game     | CREATE DATABASE `game` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

导入数据库

1.创建数据库

mysql> create database jiaowu;
Query OK, 1 row affected (0.01 sec)

2.导入数据库

[root@localhost ~]# mysql -uroot -pWww.ly1.com jiaowu < ./jiaowu.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

定制数据库编码方式

charset 【编码方式】；

mysql> create database game2 charset utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show create database game2 ;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                   |
+----------+---------------------------------------------------------------------------------------------------+
| game2    | CREATE DATABASE `game2` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

drop database 【数据库名称】;

mysql> drop database game;
Query OK, 0 rows affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| game2              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据库的常用管理操作

使用数据库

use 【数据库名称】;

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

查看数据库内的数据表

show 【数据表名】;

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| 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                      |
+---------------------------+
33 rows in set (0.00 sec)

查看表结构

desc 【数据表名】;

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(255)     | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

创建数据表表

create table 【表名】
（
【字段名1】【数据类型】【属性】，
【字段名2】【数据类型】【属性】
）；

mysql> create table user(
    -> id int primary key auto_increment,
    -> username char(15) not null,
    -> password char(25) not null,
    -> sex char(6) not null);
Query OK, 0 rows affected (0.09 sec)

mysql> desc user;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int      | NO   | PRI | NULL    | auto_increment |
| username | char(15) | NO   |     | NULL    |                |
| password | char(25) | NO   |     | NULL    |                |
| sex      | char(6)  | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

数据表属性规则

数据类型：

数字

格式	作用
int, int unsigned tiny int, small int, int, medium int, big int	整数
float(n, m)	单精度浮点数float(5,3)
double(n, m)	双精度浮点数
char(10)	定长的字符串
varchar(20)	变长的字符串
text	文本 tiny text, medium text, text, long text
ENUM(“男”, “女”)	枚举
date	日期时间YYYY-MM-DD time hh:mm:ss datetime YYYY-MM-DD HH:MM:SS

属性:

格式	作用
not null	不允许为空
default “值”	设置默认值
primary key	主键
auto_increment	自动增长

查看创建数据表信息

show create table 【数据表名称】;

mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` char(15) NOT NULL,
  `password` char(25) NOT NULL,
  `sex` char(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.21 sec)

查看存储引擎

show engines;

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

数据引擎特点

InnoDB引擎

特点：支持事务、行级锁、外键

后缀名	属性
*.frm	表结构文件
*.ibd	数据文件、索引

MyISAM 引擎

特点：支持表级锁、查询性能好

后缀名	属性
*.frm	表结构文件
*.MYD	数据文件
*.MYI	索引文件

MEMORY引擎

特点：在内存中存储数据

BLACKHOLE 引擎

特点：黑洞存储存储, 在特殊主从复制架构

修改MySQL数据库默认使用的存储引擎

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=MyISAM

删除数据表

mysql> drop table tb01;

修改表结构

修改表结构（修改表名）

alter table 【原属性】 rename 【新属性】;

mysql> alter table user rename player;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-----------------+
| Tables_in_game2 |
+-----------------+
| player          |
+-----------------+
1 row in set (0.01 sec)

修改表结构（添加字段）

alter table 【原属性】【操作】;

mysql> desc player;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int      | NO   | PRI | NULL    | auto_increment |
| username | char(15) | NO   |     | NULL    |                |
| password | char(25) | NO   |     | NULL    |                |
| sex      | char(6)  | NO   |     | NULL    |                |
| descripe | char(30) | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table player add NO int FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc player;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| NO       | int      | YES  |     | NULL    |                |
| id       | int      | NO   | PRI | NULL    | auto_increment |
| username | char(15) | NO   |     | NULL    |                |
| password | char(25) | NO   |     | NULL    |                |
| sex      | char(6)  | NO   |     | NULL    |                |
| descripe | char(30) | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table player add age int after password;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc player;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| NO       | int      | YES  |     | NULL    |                |
| id       | int      | NO   | PRI | NULL    | auto_increment |
| username | char(15) | NO   |     | NULL    |                |
| password | char(25) | NO   |     | NULL    |                |
| age      | int      | YES  |     | NULL    |                |
| sex      | char(6)  | NO   |     | NULL    |                |
| descripe | char(30) | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

修改表结构（删除字段）

alter table 【原属性】【操作】;

mysql> alter table player drop descripe;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc player;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| NO       | int      | YES  |     | NULL    |                |
| id       | int      | NO   | PRI | NULL    | auto_increment |
| username | char(15) | NO   |     | NULL    |                |
| password | char(25) | NO   |     | NULL    |                |
| age      | int      | YES  |     | NULL    |                |
| sex      | char(6)  | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

修改表结构（修改字段）

alter table 【原属性】【操作】;

mysql> alter table player modify NO char(5);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc player;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| NO       | char(5)  | YES  |     | NULL    |       |
| id       | int      | NO   | PRI | NULL    |       |
| username | char(15) | NO   |     | NULL    |       |
| password | char(25) | NO   |     | NULL    |       |
| age      | int      | YES  |     | NULL    |       |
| sex      | char(6)  | NO   |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)

数据管理操作

查看数据

select * from 【表名】【条件】;

mysql> select * from player;
+----+----------+----------+------+-------+
| id | username | password | age  | sex   |
+----+----------+----------+------+-------+
|  1 | tom      | 456      |   15 | man   |
|  2 | ly       | 123      |   22 | man   |
|  3 | ma       | 123      |   21 | woman |
+----+----------+----------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from player where username="ma";
+----+----------+----------+------+-------+
| id | username | password | age  | sex   |
+----+----------+----------+------+-------+
|  3 | ma       | 123      |   21 | woman |
+----+----------+----------+------+-------+
1 row in set (0.00 sec)

添加数据

insert into 【表名】(属性1，属性2) values(属性值1，属性值2);

mysql> insert into player(id,username,password,age,sex) values(0001,"tom","123",15,"man");
Query OK, 1 row affected (0.01 sec)
mysql> select * from player;
+----+----------+----------+------+-----+
| id | username | password | age  | sex |
+----+----------+----------+------+-----+
|  1 | tom      | 123      |   15 | man |
+----+----------+----------+------+-----+
1 row in set (0.00 sec)

删除数据

delete from 【表名】【条件】;

mysql> select * from player;
+----+----------+----------+------+-------+
| id | username | password | age  | sex   |
+----+----------+----------+------+-------+
|  1 | tom      | 123      |   15 | man   |
|  2 | ly       | 123      |   22 | man   |
|  3 | ma       | 123      |   21 | woman |
|  4 | eee      | 6555     |   55 | man   |
+----+----------+----------+------+-------+
4 rows in set (0.00 sec)
mysql> delete from player where username="eee";
Query OK, 1 row affected (0.01 sec)
mysql> select * from player;
+----+----------+----------+------+-------+
| id | username | password | age  | sex   |
+----+----------+----------+------+-------+
|  1 | tom      | 123      |   15 | man   |
|  2 | ly       | 123      |   22 | man   |
|  3 | ma       | 123      |   21 | woman |
+----+----------+----------+------+-------+
3 rows in set (0.00 sec)

修改数据

update 【表名】 set 【属性名】=【属性值】【条件】;

mysql> select * from player;
+----+----------+----------+------+-------+
| id | username | password | age  | sex   |
+----+----------+----------+------+-------+
|  1 | tom      | 123      |   15 | man   |
|  2 | ly       | 123      |   22 | man   |
|  3 | ma       | 123      |   21 | woman |
+----+----------+----------+------+-------+
3 rows in set (0.00 sec)

mysql> update player set password="456" where username="tom";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from player;
+----+----------+----------+------+-------+
| id | username | password | age  | sex   |
+----+----------+----------+------+-------+
|  1 | tom      | 456      |   15 | man   |
|  2 | ly       | 123      |   22 | man   |
|  3 | ma       | 123      |   21 | woman |
+----+----------+----------+------+-------+
3 rows in set (0.00 sec)

数据单表查询

select * from 【表名】【条件】;

按照固定格式显示

mysql> select username,age from player where id=1;
+----------+------+
| username | age  |
+----------+------+
| tom      |   15 |
+----------+------+
1 row in set (0.00 sec)

mysql> select username as 姓名,age as 年龄 from player where id=1;
+--------+--------+
| 姓名   | 年龄   |
+--------+--------+
| tom    |     15 |
+--------+--------+
1 row in set (0.00 sec)

数据库逻辑查询

1.数据库中的“与”查询

mysql> select username as 姓名,age as 年龄 from player where age>21 and age<30;
+--------+--------+
| 姓名   | 年龄   |
+--------+--------+
| ly     |     22 |
+--------+--------+
1 row in set (0.01 sec)


mysql> select username as 姓名,age as 年龄 from player where age between 21 and 30;
+--------+--------+
| 姓名   | 年龄   |
+--------+--------+
| ly     |     22 |
+--------+--------+
1 row in set (0.01 sec)

2.数据中的“或”查询

mysql> select * from tutors where Tname="YiDeng" or Tname="HuYidao";
+-----+---------+--------+------+
| TID | Tname   | Gender | Age  |
+-----+---------+--------+------+
|   5 | YiDeng  | M      |   90 |
|   8 | HuYidao | M      |   42 |
+-----+---------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from tutors where Tname in ("YiDeng","HuYidao");
+-----+---------+--------+------+
| TID | Tname   | Gender | Age  |
+-----+---------+--------+------+
|   5 | YiDeng  | M      |   90 |
|   8 | HuYidao | M      |   42 |
+-----+---------+--------+------+
2 rows in set (0.00 sec)

3.数据中的“非”查询

mysql> select * from tutors where not Age < 80;
+-----+------------+--------+------+
| TID | Tname      | Gender | Age  |
+-----+------------+--------+------+
|   1 | HongQigong | M      |   93 |
|   5 | YiDeng     | M      |   90 |
+-----+------------+--------+------+
2 rows in set (0.00 sec)

数据库中的模糊查询（like）

查询名字中间带ing的人的名字

mysql> select * from students where Name like "%ing";
+-----+---------+------+--------+------+------+------+---------------------+
| SID | Name    | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+---------+------+--------+------+------+------+---------------------+
|   1 | GuoJing |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
+-----+---------+------+--------+------+------+------+---------------------+
1 row in set (0.00 sec)


mysql> select * from students where Name like "%ing%";
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
4 rows in set (0.00 sec)

数据库中的模糊正则查询（rlike）

查询以H或者J开头的人的名字

mysql> select * from students where Name rlike "^[HJ]";
+-----+-----------+------+--------+------+------+------+---------------------+
| SID | Name      | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-----------+------+--------+------+------+------+---------------------+
|   4 | HuFei     |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
+-----+-----------+------+--------+------+------+------+---------------------+
2 rows in set (0.34 sec)

查询名字中间带ing的人的名字

mysql> select * from students where Name rlike "ing";
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
4 rows in set (0.00 sec)

针对数据表中的空字段查询

mysql> select * from students where TID is null;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
5 rows in set (0.00 sec)

数据排序管理

mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.01 sec)


mysql> select * from students order by Age desc;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)


mysql> select * from students where Age > 20 order by Age desc;
+-----+-------------+------+--------+------+------+------+---------------------+
| SID | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
4 rows in set (0.00 sec)

限制数据显示行数

mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.01 sec)


mysql> select * from students limit 2;
+-----+---------+------+--------+------+------+------+---------------------+
| SID | Name    | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+---------+------+--------+------+------+------+---------------------+
|   1 | GuoJing |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
+-----+---------+------+--------+------+------+------+---------------------+
2 rows in set (0.00 sec)



mysql> select * from students limit 2, 5;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
5 rows in set (0.00 sec)

函数管理

函数	作用
max()	求最大值
min()	求最小值
sum()	求和
avg()	求均值
count()	求人数

mysql> select avg(Age) as 平均年龄 from students;
+--------------+
| 平均年龄     |
+--------------+
|      21.3000 |
+--------------+
1 row in set (0.02 sec)

mysql> select count(*) as 人数 from students;
+--------+
| 人数   |
+--------+
|     10 |
+--------+
1 row in set (0.06 sec)

mysql> select count(*) as 人数 from students where Age > 18;
+--------+
| 人数   |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

数据分组操作

mysql> select count(*) as 人数 ,gender as 性别 from students group by gender;
+--------+--------+
| 人数   | 性别   |
+--------+--------+
|      7 | M      |
|      3 | F      |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select avg(Age) as 平均年龄 ,gender as 性别 from students group by gender;
+--------------+--------+
| 平均年龄     | 性别   |
+--------------+--------+
|      22.8571 | M      |
|      17.6667 | F      |
+--------------+--------+
2 rows in set (0.00 sec)

mysql> select avg(Age) as 平均年龄 ,gender as 性别 from students group by gender having 平均年龄 > 18;
+--------------+--------+
| 平均年龄     | 性别   |
+--------------+--------+
|      22.8571 | M      |
+--------------+--------+
1 row in set (0.00 sec)

数据去重操作

mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.04 sec)

mysql> select distinct Age from students;
+------+
| Age  |
+------+
|   19 |
|   17 |
|   25 |
|   31 |
|   16 |
|   18 |
|   20 |
|   26 |
|   22 |
+------+
9 rows in set (0.00 sec)

嵌套查询操作

mysql> select * from students where Age > (select avg(Age) from students);
+-----+-------------+------+--------+------+------+------+---------------------+
| SID | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
4 rows in set (0.04 sec)

mysql> select name,Age,Gender from students where name in (select name from students);
+--------------+------+--------+
| name         | Age  | Gender |
+--------------+------+--------+
| GuoJing      |   19 | M      |
| YangGuo      |   17 | M      |
| DingDian     |   25 | M      |
| HuFei        |   31 | M      |
| HuangRong    |   16 | F      |
| YueLingshang |   18 | F      |
| ZhangWuji    |   20 | M      |
| Xuzhu        |   26 | M      |
| LingHuchong  |   22 | M      |
| YiLin        |   19 | F      |
+--------------+------+--------+
10 rows in set (0.01 sec)

数据多表查询

内连接

只有相关联字段存在相同的值时，才会显示结果

1.查询学生的指导老师

ysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | F      |   49 |
+-----+--------------+--------+------+
9 rows in set (0.03 sec)

mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

mysql> select students.name,students.age,students.gender.tutors.Tname
    -> from students inner join tutors
    -> on students.TID=tutors.TID;
+-----------+------+--------+--------------+
| name      | age  | gender | tname        |
+-----------+------+--------+--------------+
| GuoJing   |   19 | M      | Miejueshitai |
| YangGuo   |   17 | M      | HongQigong   |
| DingDian  |   25 | M      | Jinlunfawang |
| HuFei     |   31 | M      | YiDeng       |
| HuangRong |   16 | F      | NingZhongze  |
+-----------+------+--------+--------------+
5 rows in set (0.00 sec)

2.查询学生的课程

mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

mysql> select * from courses;
+-----+------------------+-----+
| CID | Cname            | TID |
+-----+------------------+-----+
|   1 | Hamagong         |   2 |
|   2 | TaiJiquan        |   3 |
|   3 | Yiyangzhi        |   6 |
|   4 | Jinshejianfa     |   1 |
|   5 | Qianzhuwandushou |   4 |
|   6 | Qishangquan      |   5 |
|   7 | Qiankundanuoyi   |   7 |
|   8 | Wanliduxing      |   8 |
|   9 | Pixiejianfa      |   3 |
|  10 | Jiuyinbaiguzhua  |   7 |
+-----+------------------+-----+
10 rows in set (0.03 sec)

mysql> select students.Name ,students.CiD1,courses.Cid,courses.Cname 
    -> from students inner join courses
    -> on students.CID1=courses.CID;
+--------------+------+-----+------------------+
| Name         | CiD1 | Cid | Cname            |
+--------------+------+-----+------------------+
| GuoJing      |    2 |   2 | TaiJiquan        |
| YangGuo      |    2 |   2 | TaiJiquan        |
| DingDian     |    6 |   6 | Qishangquan      |
| HuFei        |    8 |   8 | Wanliduxing      |
| HuangRong    |    5 |   5 | Qianzhuwandushou |
| YueLingshang |    8 |   8 | Wanliduxing      |
| ZhangWuji    |    1 |   1 | Hamagong         |
| Xuzhu        |    2 |   2 | TaiJiquan        |
+--------------+------+-----+------------------+
8 rows in set (0.01 sec)

3.查询学生课程及任课老师

mysql> select students.name,students.age,courses.Cname,tutors.Tname 
    -> from students inner join courses inner join tutors
    -> on students.cID1=courses.cid and courses.tid=tutors.tid;
+--------------+------+------------------+--------------+
| name         | age  | Cname            | Tname        |
+--------------+------+------------------+--------------+
| GuoJing      |   19 | TaiJiquan        | Miejueshitai |
| YangGuo      |   17 | TaiJiquan        | Miejueshitai |
| DingDian     |   25 | Qishangquan      | YiDeng       |
| HuFei        |   31 | Wanliduxing      | HuYidao      |
| HuangRong    |   16 | Qianzhuwandushou | OuYangfeng   |
| YueLingshang |   18 | Wanliduxing      | HuYidao      |
| ZhangWuji    |   20 | Hamagong         | HuangYaoshi  |
| Xuzhu        |   26 | TaiJiquan        | Miejueshitai |
+--------------+------+------------------+--------------+
8 rows in set (0.00 sec)

左外连接

以左表为主，显示左表中所有的数据，相关联字段存在相同的值时，显示对应的数据；否则显示为NULL

mysql> select students.name,students.age,courses.Cname 
    -> from students left join courses
    -> on students.CID1=courses.cid;
+--------------+------+------------------+
| name         | age  | Cname            |
+--------------+------+------------------+
| GuoJing      |   19 | TaiJiquan        |
| YangGuo      |   17 | TaiJiquan        |
| DingDian     |   25 | Qishangquan      |
| HuFei        |   31 | Wanliduxing      |
| HuangRong    |   16 | Qianzhuwandushou |
| YueLingshang |   18 | Wanliduxing      |
| ZhangWuji    |   20 | Hamagong         |
| Xuzhu        |   26 | TaiJiquan        |
| LingHuchong  |   22 | NULL             |
| YiLin        |   19 | NULL             |
+--------------+------+------------------+
10 rows in set (0.00 sec)

右外连接

以右表为主，显示左表中所有的数据，相关联字段存在相同的值时，显示对应的数据；否则显示为NULL

mysql> select students.name,students.age,courses.Cname 
    -> from students right join courses
    -> on students.CID1=courses.cid;
+--------------+------+------------------+
| name         | age  | Cname            |
+--------------+------+------------------+
| ZhangWuji    |   20 | Hamagong         |
| Xuzhu        |   26 | TaiJiquan        |
| YangGuo      |   17 | TaiJiquan        |
| GuoJing      |   19 | TaiJiquan        |
| NULL         | NULL | Yiyangzhi        |
| NULL         | NULL | Jinshejianfa     |
| HuangRong    |   16 | Qianzhuwandushou |
| DingDian     |   25 | Qishangquan      |
| NULL         | NULL | Qiankundanuoyi   |
| YueLingshang |   18 | Wanliduxing      |
| HuFei        |   31 | Wanliduxing      |
| NULL         | NULL | Pixiejianfa      |
| NULL         | NULL | Jiuyinbaiguzhua  |
+--------------+------+------------------+
13 rows in set (0.00 sec)

数据库用户管理

用户管理操作

用户名格式: 用户名@主机地址

作用	格式
单个IP地址	admin@192.168.1.1
主机名	admin@node01.linux.com
网段	admin@192.168.2.%
所有	admin@%

存储用户信息的数据表——mysql.user表

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

创建本地登录用户

create user 【‘用户名’】@【‘认证方式’】 identified by 【‘密码’】;

mysql> create user 'ly'@'localhost' identified by 'Www.ly1.com';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| ly               | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
[root@localhost ~]# mysql -uly -pWww.ly1.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 10
Server version: 8.0.21 MySQL Community Server - GPL

查看当前用户信息

mysql> select user();
+--------------+
| user()       |
+--------------+
| ly@localhost |
+--------------+
1 row in set (0.03 sec)

创建远程用户登录

1.创建对应远程主机IP的用户

mysql> create user 'lll'@'192.168.122.10' identified by 'Www.ly1.com';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------------------+-----------------+
| user             | host            |
+------------------+-----------------+
| lll              | 192.168.122.10 |
| ly               | localhost       |
| mysql.infoschema | localhost       |
| mysql.session    | localhost       |
| mysql.sys        | localhost       |
| root             | localhost       |
+------------------+-----------------+
6 rows in set (0.00 sec)

2.打开相关主机登录

[root@localhost ~]# mysql -ulll -pWww.ly1.com -h 192.168.122.10
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 15
Server version: 8.0.21 MySQL Community Server - GPL

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

删除用户

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| ly               | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> drop user 'ly'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改用户密码

1.mysqladmin

[root@localhost ~]# mysqladmin -uly -p password "Www.ly2.com"
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost ~]# mysql -uly -pWww.ly2.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 10
Server version: 8.0.21 MySQL Community Server - GPL

2.set password for

mysql> set password for 'ly'@'localhost' = 'Www.2.com';
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysql -uly -pWww.2.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 14
Server version: 8.0.21 MySQL Community Server - GPL

用户授权管理

授予用户相关权限

语法： grant 权限 on 库名.表名 to 用户名 [identified by ‘密码’]
权限: create, drop, select, update, insert, delete all

[root@localhost ~]# mysql -uly -pWww.2.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 18
mysql> use jiaou;
ERROR 1044 (42000): Access denied for user 'ly'@'localhost' to database 'jiaou'
mysql>

[root@localhost ~]# mysql -uroot -pWww.ly1.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 17
Server version: 8.0.21 MySQL Community Server - GPL
mysql> grant select on jiaowu.tutors to 'ly'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from students;
ERROR 1142 (42000): SELECT command denied to user 'ly'@'localhost' for table 'students'
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | F      |   49 |
+-----+--------------+--------+------+
9 rows in set (0.09 sec)

查看用户相关权限

mysql> show grants for 'ly'@'localhost';
+-------------------------------------------------------+
| Grants for ly@localhost                               |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `ly`@`localhost`                |
| GRANT SELECT ON `jiaowu`.`tutors` TO `ly`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

回收用户相关

mysql> show grants for 'ly'@'localhost';
+-------------------------------------------------------+
| Grants for ly@localhost                               |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `ly`@`localhost`                |
| GRANT SELECT ON `jiaowu`.`tutors` TO `ly`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke select on jiaowu.tutors from 'ly'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'ly'@'localhost';
+----------------------------------------+
| Grants for ly@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO `ly`@`localhost` |
+----------------------------------------+
1 row in set (0.00 sec)

用户事务管理

事务 transaction 保证多个操作同时成功、失败
启动事务

修改操作
修改操作提交事务；
回滚事务

创建事务（提交）

start transaction；
【操作】；
commit；

mysql> start transaction
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> use jiaowu;
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 * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.03 sec)
mysql> insert into students(name,age,gender,cid1,cid2,tid) values('ly','22','M','2','3','5');
Query OK, 1 row affected (0.04 sec)
mysql> select * from students;
+------+--------------+------+--------+------+------+------+---------------------+
| SID  | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+------+--------------+------+--------+------+------+------+---------------------+
|    1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|    2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|    3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|    4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|    5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|    6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|    7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|    8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|    9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|   10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
| 3907 | ly           |   22 | M      |    2 |    3 |    5 | 2012-04-06 10:00:00 |
+------+--------------+------+--------+------+------+------+---------------------+
11 rows in set (0.00 sec)
mysql> delete from students where name='ly';
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

创建事务（回滚）

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
mysql> insert into students(name,age,gender,cid1,cid2,tid) values('ly','22','M','2','3','5');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+--------------+------+--------+------+------+------+---------------------+
| SID  | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+------+--------------+------+--------+------+------+------+---------------------+
|    1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|    2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|    3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|    4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|    5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|    6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|    7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|    8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|    9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|   10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
| 3908 | ly           |   22 | M      |    2 |    3 |    5 | 2012-04-06 10:00:00 |
+------+--------------+------+--------+------+------+------+---------------------+
11 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

用户视图管理

将频繁使用的多表查询结果，保存到视图

创建视图

mysql> select students.name,students.age,students.gender,tutors.tname 
    -> from students inner join tutors 
    -> on students.tid=tutors.tid;
+-----------+------+--------+--------------+
| name      | age  | gender | tname        |
+-----------+------+--------+--------------+
| GuoJing   |   19 | M      | Miejueshitai |
| YangGuo   |   17 | M      | HongQigong   |
| DingDian  |   25 | M      | Jinlunfawang |
| HuFei     |   31 | M      | YiDeng       |
| HuangRong |   16 | F      | NingZhongze  |
+-----------+------+--------+--------------+
5 rows in set (0.03 sec)
mysql> create view studentstutors
    -> as
    -> select students.name,students.age,students.gender,tutors.tname
    -> from students inner join tutors
    -> on students.tid=tutors.tid;
Query OK, 0 rows affected (0.04 sec)

查看视图


mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses          |
| scores           |
| students         |
| studentstutors   |
| tutors           |
+------------------+
5 rows in set (0.00 sec)
mysql> select * from studentstutors;
+-----------+------+--------+--------------+
| name      | age  | gender | tname        |
+-----------+------+--------+--------------+
| GuoJing   |   19 | M      | Miejueshitai |
| YangGuo   |   17 | M      | HongQigong   |
| DingDian  |   25 | M      | Jinlunfawang |
| HuFei     |   31 | M      | YiDeng       |
| HuangRong |   16 | F      | NingZhongze  |
+-----------+------+--------+--------------+
5 rows in set (0.00 sec)

删除视图

mysql> drop view studentstutors;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses          |
| scores           |
| students         |
| tutors           |
+------------------+
4 rows in set (0.00 sec)

用户索引管理

作用：加速数据库查询速

查看索引

mysql> show index from tutors;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tutors |          0 | TID      |            1 | TID         | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

创建索引

mysql> create index stuindex on students(name);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| students |          0 | SID      |            1 | SID         | A         |          11 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| students |          1 | stuindex |            1 | Name        | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

删除索引

mysql> drop index stuindex on students;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| students |          0 | SID      |            1 | SID         | A         |          11 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

触发器管理

作用: 当检测到数据表数据发生变化时，其他的表自动更新基于insert, update, delete操作创建

语法:
1 create trigger trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} on tb_n ame FOR EACH ROW
2 BEGIN
3 执行的操作;
4 执行的操作;
5 END

触发器创建

1.创建数据自动增加触发器

mysql> create table stu(
    -> name char(10) not null,
    -> age int not null);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu(name,age) values("lll","555");
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;
+------+-----+
| name | age |
+------+-----+
| lll  | 555 |
+------+-----+
1 row in set (0.00 sec)
mysql> create table num ( nu int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into num(nu) values("1");
Query OK, 1 row affected (0.00 sec)
mysql> select * from num;
+----+
| nu |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> \d !
mysql> create trigger stuadd after insert on stu for each row
    -> begin
    -> update num set nu=nu+1;
    -> end!
Query OK, 0 rows affected (0.01 sec)
mysql> select * from stu!
+------+-----+
| name | age |
+------+-----+
| lll  | 555 |
+------+-----+
1 row in set (0.00 sec)
mysql> select * from num!
+----+
| nu |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
mysql> insert into stu(name,age) values("mmm","22")!
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu!
+------+-----+
| name | age |
+------+-----+
| lll  | 555 |
| mmm  |  22 |
+------+-----+
2 rows in set (0.00 sec)
mysql> select * from num!
+----+
| nu |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

2.创建插入更改数据触发器


mysql> select * from name!
+------+
| name |
+------+
| lll  |
| mmm  |
+------+
2 rows in set (0.00 sec)
mysql> create trigger namadd after insert on stu for each row
    -> begin
    -> insert into name(name) values(new.name);
    -> end!
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger namadd after insert on stu for each row
    -> begin
    -> insert into name(name) values(new.name);
    -> end!
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu(name,age) values("hhh","22")!
Query OK, 1 row affected (0.01 sec)
mysql> select * from num!
+----+
| nu |
+----+
|  3 |
+----+
1 row in set (0.00 sec)
mysql> select * from name!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
+------+
3 rows in set (0.00 sec)

3.创建自动删除更改信息触发器

mysql> create trigger delnam after delete on stu for each row
    -> begin
    -> delete from name where name=old.name;
    -> end!
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu(name,age) values("whh","66")!
Query OK, 1 row affected (0.00 sec)
mysql> select * from name!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
| whh  |
+------+
4 rows in set (0.00 sec)
mysql> delete from stu where name="whh"!
Query OK, 1 row affected (0.00 sec)
mysql> select * from name!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
+------+
3 rows in set (0.00 sec)

查看触发器

mysql> show triggers！
+---------+--------+-------+----------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                                          | Timing | Created                | sql_mode                                                                                                              | Definer        | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+----------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| stuadd  | INSERT | stu   | begin
update num set nu=nu+1;
end                  | AFTER  | 2020-09-12 05:39:30.95 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| namadd  | INSERT | stu   | begin
insert into name(name) values(new.name);
end | AFTER  | 2020-09-12 05:47:41.28 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| delnam  | DELETE | stu   | begin
delete from name where name=old.name;
end    | AFTER  | 2020-09-12 05:55:25.64 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------+--------+-------+----------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
3 rows in set (0.03 sec)

删除触发器

mysql> drop trigger stuadd!
Query OK, 0 rows affected (0.01 sec)
mysql> drop trigger namadd!
Query OK, 0 rows affected (0.01 sec)
mysql> show triggers!
+---------+--------+-------+-------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                                       | Timing | Created                | sql_mode                                                                                                              | Definer        | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+-------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| delnam  | DELETE | stu   | begin
delete from name where name=old.name;
end | AFTER  | 2020-09-12 05:55:25.64 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------+--------+-------+-------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.06 sec)

存储过程

作用: 将频繁使用的操作定义为存储过程，方便后续调用

创建存储过程

create procedure sp_name(参数1, 参数2)
begin
执行的操作;
执行的操作;
end

mysql> \d !!
mysql> create procedure stucon()
    -> begin
    -> select count(*) as 用户数据 from students;
    -> end!!
Query OK, 0 rows affected (0.07 sec)

调用存储过程

mysql> call stucon!!
+--------------+
| 用户数据     |
+--------------+
|           10 |
+--------------+
1 row in set (0.06 sec)
Query OK, 0 rows affected (0.06 sec)

创建批量插入存储过过程

mysql> create procedure insdata()
    -> begin
    -> declare i int default 1;
    -> while(i<=10) do
    -> insert into name(name) values(ly);
    -> set i=i+1;
    -> end while;
    -> end!!
Query OK, 0 rows affected (0.00 sec)
mysql> call insdata()!!
Query OK, 1 row affected (0.04 sec)
mysql> select * from name!!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
+------+
13 rows in set (0.00 sec)

创建输入参数存储过程

mysql> delete from name where name="ly"!!
Query OK, 10 rows affected (0.01 sec)
mysql> select * from name!!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
+------+
3 rows in set (0.00 sec)
mysql> drop procedure insdata!!
Query OK, 0 rows affected (0.01 sec)

mysql> create procedure insdata(IN num INT)
    -> begin
    -> declare i int default 1;
    -> while(i<=num) do
    -> insert into name(name) values(ly);
    -> set i=i+1;
    -> end while;
    -> end!!
Query OK, 0 rows affected (0.00 sec)
mysql> call insdata()!!
Query OK, 1 row affected (0.04 sec)

mysql> call insdata(5)!!
Query OK, 1 row affected (0.02 sec)
mysql> select * from name!!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
| ly   |
| ly   |
| ly   |
| ly   |
| ly   |
+------+
8 rows in set (0.00 sec)

创建参数拼接sql语句存储过程

mysql> create procedure insnam(IN num INT,IN name char(10))
begin
set @sql=concat("insert into name(name) values(","'",name,"'",")");
prepare tra from @sql; execute tra; 
end!!
Query OK, 0 rows affected (0.01 sec)
mysql> call insnam("2","llyy")!!
Query OK, 1 row affected (0.01 sec)
mysql> select * from name!!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
| llyy |
+------+
4 rows in set (0.00 sec)

创建输出参数存储过程

mysql> select * from name!!
+------+
| name |
+------+
| lll  |
| mmm  |
| hhh  |
| llyy |
+------+
4 rows in set (0.00 sec)
mysql> create procedure coun(OUT num INT)
    -> begin
    -> select count(*) into num from name;
    -> end!!
Query OK, 0 rows affected (0.00 sec)
mysql> call coun(@number)!!
Query OK, 1 row affected (0.00 sec)
mysql> select @number as 人数!!
+--------+
| 人数   |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

雪千颜

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
Linux入门之——Linux下数据库的管理操作（MySQL）

Linux下的数据库操作数据库的基本管理操作数据库登录mysql 【-u用户名】【-p密码】[root@localhost ~]# mysql -uroot -pWww.ly1.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 connec
复制链接

扫一扫