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 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 textENUM(“男”, “女”)枚举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)