[toc]

mysql基础 mysql基本操作

Mysql 关系型数据库，表跟表之间可以建立关系

库-->表：列(字段 faield)

行(记录 record)

1.连接mysql

$ mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2.设置root密码

方法一命令行设置

mysqladmin -uroot password '密码'

方法二进入mysql设置

set password = password('密码');

3.mysql初始安全设置

$ mysql_secure_installation
Enter current password for root (enter for none):			# 输入root密码
Change the root password? [Y/n] 					            # 是否改变root密码
Remove anonymous users? [Y/n]					            # 是否移除匿名用户
Disallow root login remotely? [Y/n]					        # 是否允许root远程登陆
Remove test database and access to it? [Y/n] 			    # 是否移除test库并且不能访问
Reload privilege tables now? [Y/n]					        # 是否重新加载权限表

Enter current password for root (enter for none):			# 输入root密码

4.mysql文件(yum安装)

$ cd /var/lib/mysql/
$ ls
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock

文件	说明
ibdata1	InnoDB存储引擎的系统表空间，存放InnoDB表的数据、回滚段
ib_logfile0、ib_logfile1	InnoDB日志文件组
mysql	数据库库名字
mysql.sock	mysql的socket文件，用于本机用户登陆mysql

5.库的管理

5.1 创建数据库

create database 数据库名;

mysql> create database DB1;
Query OK, 1 row affected (0.00 sec)

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

5.2 删除数据库

drop database 数据库名;

mysql> drop database DB1;
Query OK, 0 rows affected (0.00 sec)

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

5.3 查看数据库

show databases;

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

5.4 使用数据库

use 数据库名

mysql> use DB1
Database changed

mysql> select database();		# 查看当前使用哪个数据库
+------------+
| database() |
+------------+
| DB1        |
+------------+
1 row in set (0.00 sec)

6.表的管理

6.1 创建表

创建表

create table 表名(列名1 数据类型,列名2 数据类型);

mysql> create table t1(id int(3),name char(30),sex enum('M','F'),hobby set('a','b','c'));
Query OK, 0 rows affected (0.03 sec)

描述表

desc 表名;

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(3)           | YES  |     | NULL    |       |
| name  | char(30)         | YES  |     | NULL    |       |
| sex   | enum('M','F')    | YES  |     | NULL    |       |
| hobby | set('a','b','c') | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.13 sec)

向表中插入数据

insert into 表名 values(...);

mysql> insert into t1 values(1,'xiaoming','M','a,b,c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+----------+-----+-------+
| id | name     | sex | hobby |
+----+----------+-----+-------+
|  1 | xiaoming | M   | a,b,c |
+----+----------+-----+-------+
1 row in set (0.12 sec)

6.2删除表

删除一个表

drop table 表名

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.13 sec)
 
mysql> drop table t1;
Query OK, 0 rows affected (0.12 sec)

删除多个表

drop table 表1,表2,...表n;

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.16 sec)
 
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.20 sec)

6.3 修改表

6.3.1 增加列

:::tip说明

修改列中不支持before，只有after和first

:::

alter table 表名 add 列名数据类型;

# 查看t1表，此时表中只有一个列id，现在想增加一个列name
mysql> desc t1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(3) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.10 sec)

# 给t1表增加列name
mysql> alter table t1 add name char(10);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
# 查看t1表，已经增加name列 
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(3)   | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.10 sec)


add增加列默认是在最后边添加，如果需要指定追到的位置需要做以下操作

# t2表内容如下，现在要追加一个phone列，追加到name列的后边
mysql> desc t2;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| address | char(30) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

# 需要用到after关键字，此语句表明在name列后追加phone列
mysql> alter table t2 add phone char(11) after name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| phone   | char(11) | YES  |     | NULL    |       |
| address | char(30) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 如果要追加到第一列，需要用到first关键字
mysql> alter table t2 add sex enum('F','M') first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| sex     | enum('F','M') | YES  |     | NULL    |       |
| id      | int(11)       | YES  |     | NULL    |       |
| name    | char(10)      | YES  |     | NULL    |       |
| phone   | char(11)      | YES  |     | NULL    |       |
| address | char(30)      | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

6.3.2 删除列

alter table 表名 drop 列名;

# 查看t1表，表中有两个列id和name,现在要删除name列
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(3)   | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.10 sec)

# 删除name列
mysql> alter table t1 drop name;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
# 查看t1表name列已经删除 
mysql> desc t1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(3) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.12 sec)

6.3.3 修改列名

alter table 表名 change 旧列名新列名数据类型;

:::tip说明

change既可以修改列名，又可以修改列类型

:::

# 查看t1表，表中有id和name两个列，现在要将name列修改为address列
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(3)   | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.10 sec)

# 修改name列
mysql> alter table t1 change name address varchar(30);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
# 查看t1表，原name列已经修改为address，列类型已由char修改为varchar  
mysql> desc t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(3)      | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.17 sec)

6.3.4 修改列的数据类型

alter table 表名 modify 列名新列数据类型;

# t1表中有id列和address列，现在要把address列的数据类型由varchar改为char
mysql> desc t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(3)      | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.16 sec)

# 修改address列数据类型为char
mysql> alter table t1 modify address char(20);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
# 查看t1表，address列的数据类型已经修改为char
mysql> desc t1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(3)   | YES  |     | NULL    |       |
| address | char(20) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.19 sec)

6.3.5修改表名

rename table 旧表名 to 新表名;

# 查看表，现在要将表t1修改为table1
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.14 sec)

# 修改表t1为table1
mysql> rename table t1 to table1;
Query OK, 0 rows affected (0.16 sec)
 
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| table1        |
+---------------+
1 row in set (0.18 sec)

6.4 查看表

show tables

# 先进入一个库
mysql> use db1;
Database changed

# 查看库中所有的表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.18 sec)

7.数据管理

7.1 增加数据

insert into 表名 values(......);

方式一直接插入值

# 查看表t1，现在是一张空表
mysql> desc t1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(3)   | YES  |     | NULL    |       |
| address | char(20) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.17 sec)

mysql> select * from t1;
Empty set


# 向表中插入数据，插入一条
mysql> insert into t1 values(1,'北京');
Query OK, 1 row affected (0.19 sec)

# 向表中插入数据，插入多条
mysql> insert into t1 values(2,'上海'),(3,'广州'),(4,'深圳');
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 查看t1表中的数据
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京  |
|  2 | 上海  |
|  3 | 广州  |
|  4 | 深圳  |
+----+---------+
4 rows in set (0.16 sec)

方式二从别的表中选择数据插入

# 创建t1表
mysql> create table t1(id int primary key auto_increment,address char(10));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入数据
mysql> insert into t1(address) values('北京'),('杭州'),('深圳');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
   
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京    |
|  2 | 杭州    |
|  3 | 深圳    |
+----+---------+
3 rows in set (0.00 sec)  
      
# 创建t2表
mysql> create table t2(id int primary key auto_increment,address char(10));
Query OK, 0 rows affected (0.02 sec)

# 将t1表中的内容插入到t2表中
mysql> insert into t2(select * from t1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
      
mysql> select * from t2;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京    |
|  2 | 杭州    |
|  3 | 深圳    |
+----+---------+
3 rows in set (0.00 sec)  



如果两个表中的字段不一致，从另一张表中插入数据的时候需要手动指定字段
# 创建t3表
mysql> create table t3(id int primary key auto_increment,address char(10),qnum tinyint);
Query OK, 0 rows affected (0.02 sec)

# 此时想插入t1表的数据，需要手动指定一下两张表中的共同字段
mysql> insert into t3(id,address) (select * from t1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+----+---------+------+
| id | address | qnum |
+----+---------+------+
|  1 | 北京    | NULL |
|  2 | 杭州    | NULL |
|  3 | 深圳    | NULL |
+----+---------+------+
3 rows in set (0.00 sec)

7.2 删除数据

delete from 表名 where 条件;

# 查看t1表中的数据
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京  |
|  2 | 上海  |
|  3 | 广州  |
|  4 | 深圳  |
+----+---------+
4 rows in set (0.16 sec)

# 删除表中地址为上海的数据
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.16 sec)
 
# 查看t1表中内容 
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京  |
|  3 | 广州  |
|  4 | 深圳  |
+----+---------+
3 rows in set (0.16 sec)


# 再次新建t1表
ysql> create table t1(id int primary key auto_increment,address char(10));
Query OK, 0 rows affected (0.02 sec)

# 向t1表中插入数据
mysql> insert into t1(address) values('北京'),('上海'),('广州');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京    |
|  2 | 上海    |
|  3 | 广州    |
+----+---------+

# delete方式清空t1表
mysql> delete from t1;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

# 向表中插入数据
mysql> insert into t1(address) values('杭州'),('深圳');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

# 发现并没有清空自增字段，如果需要清空自增字段，需要用到truncate语句
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  4 | 杭州     |
|  5 | 深圳     |
+----+---------+
2 rows in set (0.00 sec)

清空表并重置自增字段

truncate table 表名;

# 创建一个t1表
mysql> create table t1(id int,address char(10));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入数据
mysql> insert into t1 values(1,'北京');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+---------+
| id   | address |
+------+---------+
|    1 | 北京    |
+------+---------+
1 row in set (0.00 sec)

# truncate清空表，两种写法，truncate后边的table可以不加
mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.03 sec)

# 查看表t1，已经清空
mysql> select * from t1;
Empty set (0.00 sec)


# 再创建一个t2表，表中有自增字段
mysql> create table t2(id int primary key auto_increment,address char(10));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入数据
mysql> insert into t2(address) values('杭州'),('深圳');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+---------+
| id | address |
+----+---------+
|  1 | 杭州    |
|  2 | 深圳    |
+----+---------+
2 rows in set (0.00 sec)

# truncate清空表
mysql> truncate t2;
Query OK, 0 rows affected (0.02 sec)

# 向表中插入数据
mysql> insert into t2(address) values('杭州'),('深圳');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

# truncate清空表的方式会将表中的自增字段同时删除，而delete from 表名的方式不可以删除自增      
mysql> select * from t2;
+----+---------+
| id | address |
+----+---------+
|  1 | 杭州    |
|  2 | 深圳    |
+----+---------+
2 rows in set (0.00 sec)

7.3 修改数据

update 表名 set 旧值=新值 where 条件;

# 查看表t1，现在要将表中的上海修改为杭州
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京  |
|  2 | 上海  |
|  3 | 广州  |
|  4 | 深圳  |
+----+---------+
4 rows in set (0.12 sec)

# 修改表t1中地址为上海的列为杭州
mysql> update t1 set address='杭州' where id=2;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  
# 查看表t1，上海已经修改为杭州 
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京  |
|  2 | 杭州  |
|  3 | 广州  |
|  4 | 深圳  |
+----+---------+
4 rows in set (0.13 sec)

7.4 查询数据

select 列名 from 表名 where 条件;

# 查询表中所有内容
mysql> select * from t1;
+----+---------+
| id | address |
+----+---------+
|  1 | 北京  |
|  2 | 杭州  |
|  3 | 广州  |
|  4 | 深圳  |
+----+---------+
4 rows in set (0.14 sec)

# 根据条件查询	查询t1表中id=1的address列
mysql> select address from t1 where id=1;
+---------+
| address |
+---------+
| 北京    |
+---------+
1 row in set (0.18 sec)

8.mysql常用函数

函数名	说明
user()	查看当前用户
database()	查看当前所属库
version()	查看MySQL版本
now()	系统时间
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
count()	统计数量

8.1 user() 查看当前用户

# 当前登陆用户是root，登陆的主机是本机
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

8.2 database() 查看当前所在库

mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.12 sec)

8.3 version() 查看mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.11 sec)

8.4 now() 查看当前系统时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-10-25 21:10:18 |
+---------------------+
1 row in set (0.11 sec)

8.5 sum() 求和

# 查看student表
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 80    |
|    3 | 小丽   | 70    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 求学生表中所有人的总成绩
mysql> select sum(score) as total_points from student;
+--------------+
| total_points |
+--------------+
|          374 |
+--------------+

# 求学生表中成绩大于70分的总和
mysql> select sum(score) as total_points from student where score>=70;
+--------------+
| total_points |
+--------------+
|          249 |
+--------------+
1 row in set (0.00 sec)

8.6 avg() 平均值

# 查看student表
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 80    |
|    3 | 小丽   | 70    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 求所有人分数的平均值
mysql> select avg(score) as avg_points from student;
+------------+
| avg_points |
+------------+
|       74.8 |
+------------+
1 row in set (0.00 sec)

# 求70分以上的人的分数平均值
mysql> select avg(score) as avg_points from student where score>=70;
+------------+
| avg_points |
+------------+
|         83 |
+------------+
1 row in set (0.00 sec)

8.7 max() 最大值

# 查看student表
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 80    |
|    3 | 小丽   | 70    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 查询student表中分数最高的
mysql> select max(score) from student;
+------------+
| max(score) |
+------------+
| 99         |
+------------+
1 row in set (0.00 sec)

8.8 min() 最小值

# 查看student表
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 80    |
|    3 | 小丽   | 70    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 查询student表中分数最低的
mysql> select min(score) from student;
+------------+
| min(score) |
+------------+
| 59         |
+------------+
1 row in set (0.00 sec)

8.9 count() 统计数量

# 查看student表
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 80    |
|    3 | 小丽   | 70    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 查询student表中分数大于60的人数
mysql> select count(*) from student where score>60;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

9.mysql数据查询操作

9.1 数学运算

+ 加法

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

- 减法

mysql> select 100-1;
+-------+
| 100-1 |
+-------+
|    99 |
+-------+
1 row in set (0.00 sec)

* 乘法

mysql> select 100*3;
+-------+
| 100*3 |
+-------+
|   300 |
+-------+
1 row in set (0.00 sec)

/ 除法

mysql> select 100/3;
+---------+
| 100/3   |
+---------+
| 33.3333 |
+---------+
1 row in set (0.00 sec)

% 取余

mysql> select 7%3;
+------+
| 7%3  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

pow 幂运算

:::tip说明

mysql中不支持 ** 方式的幂运算，需要用到 pow 方法

:::

mysql> select pow(3,3);
+----------+
| pow(3,3) |
+----------+
|       27 |
+----------+
1 row in set (0.00 sec)

9.2 比较运算

> 大于

# 返回值为0表示结果为假
mysql> select 3>6;
+-----+
| 3>6 |
+-----+
|   0 |
+-----+
1 row in set (0.01 sec)

# 返回值为1表示结果为真
mysql> select 6>3;
+-----+
| 6>3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

< 小于

# 返回值为1表示结果为真
mysql> select 6>3;
+-----+
| 6>3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

# 返回值为0表示结果为假
mysql> select 6<3;
+-----+
| 6<3 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

>= 大于等于

# 返回值为0表示结果为假
mysql> select 6<=3;
+------+
| 6<=3 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

# 返回值为1表示结果为真
mysql> select 6>=3;
+------+
| 6>=3 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

<= 小于等于

# 返回值为1表示结果为真
mysql> select 6<=6;
+------+
| 6<=6 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

# 返回值为0表示结果为假
mysql> select 6<=3;
+------+
| 6<=3 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

= 等于

# 返回值为1表示结果为真
mysql> select 5=5;
+-----+
| 5=5 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

# 返回值为0表示结果为假
mysql> select 5=6;
+-----+
| 5=6 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

!= 不等于

# 返回值为1表示结果为真
mysql> select 3!=2;		
+------+
| 3!=2 |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

# 返回值为0表示结果为假
mysql> select 3!=3;
+------+
| 3!=3 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

9.3 逻辑运算

&& 与

# 返回值为1表示结果为真
mysql> select 3>1 && 5>1;
+------------+
| 3>1 && 5>1 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

# 返回值为0表示结果为假
mysql> select 3>1 && 5<1;
+------------+
| 3>1 && 5<1 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

|| 或

# 返回值为1表示结果为真	或关系中有一个为真结果就为真
mysql> select 3>1 || 5<1;
+------------+
| 3>1 || 5<1 |
+------------+
|          1 |
+------------+
1 row in set (0.01 sec)

# 返回值为0表示结果为假	或关系中都为假结果才为假
mysql> select 3<1 || 5<1;
+------------+
| 3<1 || 5<1 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

not 非

# 此写法不正确
mysql> select 3>6 not 3>5;		
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3>5' at line 1

# 返回值为1表示结果为真
mysql> select not 3>5;
+---------+
| not 3>5 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

# 返回值为0表示结果为假
mysql> select not 5>3;
+---------+
| not 5>3 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

9.4 排序 order by

:::tip说明

默认为升序

order by 列名;

使用 desc 为降序

order by 列名 desc;

:::

# student表内容如下
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 79    |
|    3 | 小丽   | 88    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 按照成绩升序排序
mysql> select * from student order by score;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
|    2 | 小红   | 79    |
|    3 | 小丽   | 88    |
|    1 | 小明   | 99    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 按照成绩降序排序
mysql> select * from student order by score desc;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    3 | 小丽   | 88    |
|    2 | 小红   | 79    |
|    5 | 小洲   | 66    |
|    4 | 小强   | 59    |
+------+--------+-------+
5 rows in set (0.00 sec)

9.5 限制 limit

limit用于限制查询结果的条数

# student表内容如下
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 79    |
|    3 | 小丽   | 88    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 限制查询结果显示3条
mysql> select * from student limit 3;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 79    |
|    3 | 小丽   | 88    |
+------+--------+-------+
3 rows in set (0.00 sec)

9.6 分组 group by

9.6.1 `group by` 简单使用示例

# t1表内容如下
mysql> select * from t1;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   |  99.9 |
|    2 | 小明   |  60.6 |
|    3 | 小红   |    70 |
|    4 | 小洲   |  88.5 |
|    5 | 小明   |  77.5 |
|    6 | 小洲   |    59 |
+------+--------+-------+
6 rows in set (0.00 sec)

# 根据姓名分组，group by会将列中值相同的行合并
mysql> select name from t1 group by name;
+--------+
| name   |
+--------+
| 小明   |
| 小洲   |
| 小红   |
+--------+
3 rows in set (0.00 sec)

来个错误示例

:::tip错误原因

group by 语句会将列中值相同的行合并，例如上边的 t1 表中，有多个名字相同的人小明，group by 已经将多个小明的值合并为一个，但是小明是姓名相同的不同的人，成绩也不同，因此查询的时候会报错

:::

mysql> select * from t1 group by name;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.t1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

9.6.2 `group by+having` 分组后再过滤

# 创建score表
mysql> create table score(sname char(10),
     cname char(10),
     grade int);
Query OK, 0 rows affected (0.03 sec)

# 向表中插入数据
mysql> insert into score(sname,cname,grade)
    values  ('张三','数学',80),
            ('张三','语文',90),
            ('张三','英语',70),
            ('张三','物理',60),
            ('李四','数学',66),
            ('李四','语文',60),
            ('李四','英语',80),
            ('李四','物理',90),
            ('刘五','语文',99),
            ('刘五','数学',50),
            ('刘五','英语',50),
            ('刘五','物理',89),
            ('罗六','语文',99),
            ('罗六','数学',80),
            ('罗六','物理',78),
            ('罗六','英语',96),
            ('许七','数学',96),
            ('许七','语文',96),
            ('许七','英语',96),
            ('许七','物理',96);
Query OK, 20 rows affected (0.03 sec)
Records: 20  Duplicates: 0  Warnings: 0
      
# 查询平均成绩大于90分并且语文课95分以上的学生名和平均成绩
mysql> select sname,avg(grade) from score where sname in (select sname from score where cname='语文' and grade > 95) group by sname;
+--------+------------+
| sname  | avg(grade) |
+--------+------------+
| 刘五   |    72.0000 |
| 罗六   |    88.2500 |
| 许七   |    96.0000 |
+--------+------------+
3 rows in set (0.00 sec)

mysql> select sname,avg(grade) from score where sname in (select sname from score where cname='语文' and grade > 95) group by sname having avg(grade) > 90;
+--------+------------+
| sname  | avg(grade) |
+--------+------------+
| 许七   |    96.0000 |
+--------+------------+
1 row in set (0.00 sec)

9.6.3 `group by+group_concat` 函数使用示例

# 创建book表
mysql> create table book(书名 char(20) not null,
       作者 char(10) not null,
       出版社 char(20) not null,
       价格 int unsigned,
       出版日期 date);
Query OK, 0 rows affected (0.02 sec)

# 向book表中插入数据
mysql> insert into book values(
      '那个女孩','小明','工业出版社',80,'2016-07-01'),
      ('阿三传说','小洲','人民出版社',10,'2019-09-09'),
      ('奔跑的草泥马','小明','盗版出版社',60,'2017-07-12'),
      ('上课必备三件套','小颖','人民出版社',250,'2018-11-11'),
      ('童话故事','','工业出版社',50,'2019-09-01');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
      
mysql> select * from book;
+-----------------------+--------+-----------------+--------+--------------+
| 书名                  | 作者   | 出版社          | 价格   | 出版日期     |
+-----------------------+--------+-----------------+--------+--------------+
| 那个女孩              | 小明   | 工业出版社      |     80 | 2016-07-01   |
| 阿三传说              | 小洲   | 人民出版社      |     10 | 2019-09-09   |
| 奔跑的草泥马          | 小明   | 盗版出版社      |     60 | 2017-07-12   |
| 上课必备三件套        | 小颖   | 人民出版社      |    250 | 2018-11-11   |
| 童话故事              |        | 工业出版社      |     50 | 2019-09-01   |
+-----------------------+--------+-----------------+--------+--------------+
5 rows in set (0.00 sec)

# 查询各出版社出版的所有图书，这里需要用到group_concat()函数
mysql> select 出版社,group_concat(书名) from book group by 出版社;
+-----------------+------------------------------------+
| 出版社          | group_concat(书名)                 |
+-----------------+------------------------------------+
| 人民出版社      | 阿三传说,上课必备三件套            |
| 工业出版社      | 那个女孩,童话故事                  |
| 盗版出版社      | 奔跑的草泥马                       |
+-----------------+------------------------------------+
3 rows in set (0.00 sec)

9.7 检索区间 `between...and...`

# student表内容如下
mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 小明   | 99    |
|    2 | 小红   | 79    |
|    3 | 小丽   | 88    |
|    4 | 小强   | 59    |
|    5 | 小洲   | 66    |
+------+--------+-------+
5 rows in set (0.00 sec)

# 查询成绩在60分到90分之间的学生姓名
mysql> select name,score from student where score between 60 and 90;
+--------+-------+
| name   | score |
+--------+-------+
| 小红   | 79    |
| 小丽   | 88    |
| 小洲   | 66    |
+--------+-------+
3 rows in set (0.00 sec)

9.8 判断是否在范围 `in/not in`

# in表示在一个范围内
# 查看t1表
mysql> select * from t1;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | 小明    |   20 |
|    2 | 大B哥   |   30 |
|    3 | 小B哥   |   25 |
|    4 | 龙哥    |   38 |
|    5 | 小颖    |   18 |
|    6 | 小洲    |   19 |
+------+---------+------+
6 rows in set (0.00 sec)

# 查询年龄是18、20、30岁之间的人的信息
mysql> select * from t1 where age in(18,19,30);
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    2 | 大B哥   |   30 |
|    5 | 小颖    |   18 |
|    6 | 小洲    |   19 |
+------+---------+------+
3 rows in set (0.00 sec)

# not in表示不在一个范围内
# 查询年龄不在18、20、30岁之间的人的信息
mysql> select * from t1 where age not in(18,19,30);
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | 小明    |   20 |
|    3 | 小B哥   |   25 |
|    4 | 龙哥    |   38 |
+------+---------+------+
3 rows in set (0.00 sec)

10.mysql的API及接口自带命令

10.1 mysql API

:::tip说明

mysql API就是能够不进入mysql而在命令行中执行sql语句，在命令行中使用-e选项

:::

mysqlAPI示例1，在命令行中使用SQL语句

$ mysql -uroot -p123 mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DB1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+

mysqlAPI示例2，php连接mysql

<?php
		$a=mysql_connet('主机IP','用户名','密码')
		if($a)
			echo "connection success!"
		else
			echo "connection failed!"
?>

10.2mysql接口自带命令

以下命令在mysql中或者命令行执行都可以

命令	说明
`help` 或`？`	查看帮助
`\G`	格式化查看数据（key：value）
`\T` 或 `tee`	记录日志
`\c`（5.7可以ctrl+c）	结束命令
`\s` 或 `status`	查看状态信息
`\.` 或 `source`	导入SQL数据
`\u`或 `use`	使用数据库
`\q` 或 `exit` 或 `quit`	退出

1.连接mysql​

2.设置root密码​

3.mysql初始安全设置​

4.mysql文件(yum安装)​

5.库的管理​

5.1 创建数据库​

5.2 删除数据库​

5.3 查看数据库​

5.4 使用数据库​

6.表的管理​

6.1 创建表​

6.2删除表​

6.3 修改表​

6.3.1 增加列​

6.3.2 删除列​

6.3.3 修改列名​

6.3.4 修改列的数据类型​

6.3.5修改表名​

6.4 查看表​

7.数据管理​

7.1 增加数据​

7.2 删除数据​

7.3 修改数据​

7.4 查询数据​

8.mysql常用函数​

8.1 user() 查看当前用户​

8.2 database() 查看当前所在库​

8.3 version() 查看mysql版本​

8.4 now() 查看当前系统时间​

8.5 sum() 求和​

8.6 avg() 平均值​

8.7 max() 最大值​

8.8 min() 最小值​

8.9 count() 统计数量​

9.mysql数据查询操作​

9.1 数学运算​

9.2 比较运算​

9.3 逻辑运算​

9.4 排序 order by​

9.5 限制 limit​

9.6 分组 group by​

9.6.1 group by 简单使用示例​

9.6.2 group by+having 分组后再过滤​

9.6.3 group by+group_concat 函数使用示例​

9.7 检索区间 between...and...​

9.8 判断是否在范围 in/not in​

10.mysql的API及接口自带命令​

10.1 mysql API​

10.2mysql接口自带命令​

1.连接mysql

2.设置root密码

3.mysql初始安全设置

4.mysql文件(yum安装)

5.库的管理

5.1 创建数据库

5.2 删除数据库

5.3 查看数据库

5.4 使用数据库

6.表的管理

6.1 创建表

6.2删除表

6.3 修改表

6.3.1 增加列

6.3.2 删除列

6.3.3 修改列名

6.3.4 修改列的数据类型

6.3.5修改表名

6.4 查看表

7.数据管理

7.1 增加数据

7.2 删除数据

7.3 修改数据

7.4 查询数据

8.mysql常用函数

8.1 user() 查看当前用户

8.2 database() 查看当前所在库

8.3 version() 查看mysql版本

8.4 now() 查看当前系统时间

8.5 sum() 求和

8.6 avg() 平均值

8.7 max() 最大值

8.8 min() 最小值

8.9 count() 统计数量

9.mysql数据查询操作

9.1 数学运算

9.2 比较运算

9.3 逻辑运算

9.4 排序 order by

9.5 限制 limit

9.6 分组 group by

9.6.1 `group by` 简单使用示例

9.6.2 `group by+having` 分组后再过滤

9.6.3 `group by+group_concat` 函数使用示例

9.7 检索区间 `between...and...`

9.8 判断是否在范围 `in/not in`

10.mysql的API及接口自带命令

10.1 mysql API

10.2mysql接口自带命令