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 - GPL2.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)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
