SQL常用语法

阅读数: 次 2021-10-01

数据库的基本操作

显示数据库

1	show databases;

创建数据库

1	create database student;

创建以关键字命名的数据库等

1	create database `database`;

创建前进行判断

1	create database if not exists student;s

删除数据库

1	drop database student;

删除前进行判断

1	drop database if exists student;

查看创建数据库时的设置信息

mysql> show create database teacher;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |      
+----------+------------------------------------------------------------------+      
| teacher  | CREATE DATABASE `teacher` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+

修改数据库的字符编码

1	alter database teacher charset = gbk;

使用数据库

1	use rain_school;

查看字符编码

1	show variables like 'character_set_%';

表的基本操作

创建表

mysql> create table student(
    -> id int (4),
    -> name varchar(30),
    -> age int
    -> );
    /**
    id 的字段宽度为 4 -> 显示的数值宽度，但是只要插入的数据在int的数值范围内，即使数值宽度大于4也可以显示
    name 字段的宽度为30 -> name 字段为字符类型，所以插入的字符宽度不可以超过30 
    **/

显示当前所使用的数据库中的表

mysql> show tables;
+-----------------------+
| Tables_in_rain_school |
+-----------------------+
| student               |
+-----------------------+

更详细滴创建表

mysql>  create table if not exists teacher(    
    ->  id int auto_increment primary key comment '主键id',     
    ->  name varchar(30) not null comment '老师名字非空',    
    ->  phone varchar(20) comment '电话号码',    
    ->  address varchar(100) default '暂时未知' comment '住址'    
    ->  )engine=innodb;
Query OK, 0 rows affected (0.09 sec)
    /**id name phone... 字段
    auto_increment 自动增长
    primary key 主键
    comment 注释
    not null 非空
    default 默认
    engine 存储引擎**/

查看之前创建表时的信息

1	show create table teacher;

查看表结构

mysql> desc teacher;
+---------+--------------+------+-----+----------+----------------+
| Field   | Type         | Null | Key | Default  | Extra          |      
+---------+--------------+------+-----+----------+----------------+      
| id      | int(11)      | NO   | PRI | NULL     | auto_increment |      
| name    | varchar(30)  | NO   |     | NULL     |                |      
| phone   | varchar(20)  | YES  |     | NULL     |                |      
| address | varchar(100) | YES  |     | 暂时未知 |                |      
+---------+--------------+------+-----+----------+----------------+

删除单个表

1	drop table if exists stu;

一次删除多个表

1	drop table if exists stu, teacher, sss;

修改表-添加字段

1	alter table student add phone varchar(20);

在指定位置添加字段

1	alter table student add gender varchar(1) after name;

删除表的某个字段

1	alter table student drop phone;

修改表中字段的类型

1	alter table student modify age int(2);

修改表名字

1	alter table student rename to stu;

数据的更新操作

向表中插入数据

1 2	mysql> insert into teacher (id, name, phone, address) values (1, 'rain', '17824852705', 'DALIAN');

一次性插入多条数据

1	insert into teacher(id, name) values(7, "MIA"),(8, "sss");

删除数据

1	delete from teacher where id = 6;

清空表中的数据

1	delete from student;

1	truncate table student;

truncate 与 delete 清表的区别

truncate 比 delete 更快 -> delete删除会记录到日志中
truncate 是直接销毁表，delete 可以按照条件删除
清空后 truncate 的 id 会从头开始， delete 的 id 会接着删除之前的 id –> delete 清空表底层是通过 where 遍历；

修改数据

1	mysql> update teacher set name = 'rain', address='DALIAN',phone='17824852705' where id = 1;

修改数据时如果没有指定更新条件where -> 将所有数据全部更新

1	mysql> update teacher set name = 'rain', address='DALIAN',phone='17824852705';

查询全部数据

mysql> select * from teacher;
+----+------+-------------+---------+
| id | name | phone       | address |
+----+------+-------------+---------+
|  1 | MIA  | 18192768186 | XIAN    |
|  2 | rain | 17824852705 | DALIAN  |
+----+------+-------------+---------+

查询指定字段的数据

mysql> select id, name, address from teacher;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | MIA  | XIAN    |
|  2 | rain | DALIAN  |
+----+------+---------+

数据的查询操作

单表查询

mysql> select 2*5 as res from dual; #dual 为默认的伪表可以不写, as 定义表的别名
+-----+
| res |
+-----+
|  10 |
+-----+
1 row in set (0.00 sec)

limit 限制条件

mysql> select * from teacher limit 0,2; # 从下标 0 位置开始，共查询两条数据
+----+------+--------+-----+-------------+---------+
| id | name | gender | age | phone       | address |
+----+------+--------+-----+-------------+---------+
|  1 | Tom  | 男     |  15 | 18192768186 | XIAN    |
|  2 | rain | 男     |  16 | 17824852705 | DALIAN  |
+----+------+--------+-----+-------------+---------+
2 rows in set (0.00 sec)

mysql> select * from teacher limit 3; #取前三条数据
+----+------+--------+-----+-------------+---------+
| id | name | gender | age | phone       | address |
+----+------+--------+-----+-------------+---------+
|  1 | Tom  | 男     |  15 | 18192768186 | XIAN    |
|  2 | rain | 男     |  16 | 17824852705 | DALIAN  |
|  3 | 饭饭 | 女     |  18 | 15145124023 | BEIJING |
+----+------+--------+-----+-------------+---------+
3 rows in set (0.00 sec)

distinct ->去重

mysql> select address from teacher;
+----------+
| address  |
+----------+
| XIAN     |
| DALIAN   |
| BEIJING  |
| SHANGHAI |
| SHANGHAI |
| BEIJING  |
| 暂时未知 |
+----------+
7 rows in set (0.00 sec)

mysql> select distinct address from teacher;
+----------+
| address  |
+----------+
| XIAN     |
| DALIAN   |
| BEIJING  |
| SHANGHAI |
| 暂时未知 |
+----------+
5 rows in set (0.00 sec)

聚合函数

mysql> select count(*) from teacher;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select max(id) from teacher;
+---------+
| max(id) |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

条件查询

mysql> select * from teacher where address = 'beijing';
+----+------+-------------+---------+
| id | name | phone       | address |
+----+------+-------------+---------+
|  3 | 饭饭 | 15145124023 | beijing |
+----+------+-------------+---------+

mysql> select * from teacher where address in('beijing', 'DALIAN');
+----+------+-------------+---------+
| id | name | phone       | address |
+----+------+-------------+---------+
|  2 | rain | 17824852705 | DALIAN  |
|  3 | 饭饭 | 15145124023 | beijing |
+----+------+-------------+---------+
2 rows in set (0.00 sec)

mysql> select * from teacher where id not between 2 and 3;
+----+-------+-------------+----------+
| id | name  | phone       | address  |
+----+-------+-------------+----------+
|  1 | Tom   | 18192768186 | XIAN     |
|  4 | Jerry | 13803694825 | CHANGSHA |
|  5 | Frank | 12415452639 | 暂时未知 |
+----+-------+-------------+----------+
3 rows in set (0.00 sec)

mysql> select * from student where age is not null;
+------+------+--------+------+
| id   | name | gender | age  |
+------+------+--------+------+
|    1 | rain | 男     |   23 |
|    2 | MIA  | 女     |   19 |
+------+------+--------+------+
2 rows in set (0.00 sec)

模糊查询

mysql> select * from teacher where address like 'bei%';
+----+----------+-------------+---------+
| id | name     | phone       | address |
+----+----------+-------------+---------+
|  3 | 饭饭     | 15145124023 | beijing |
|  6 | Fiveteen | 15145236589 | beijing |
+----+----------+-------------+---------+

mysql> select * from teacher where name  like 'rai_';
+----+------+-------------+---------+
| id | name | phone       | address |
+----+------+-------------+---------+
|  2 | rain | 17824852705 | DALIAN  |
+----+------+-------------+---------+
1 row in set (0.00 sec)

排序查询

mysql> select * from teacher order by age asc; #升序排列 默认为升序
+----+----------+------+-------------+----------+
| id | name     | age  | phone       | address  |
+----+----------+------+-------------+----------+
|  1 | Tom      |   15 | 18192768186 | XIAN     |
|  2 | rain     |   16 | 17824852705 | DALIAN   |
|  3 | 饭饭     |   18 | 15145124023 | beijing  |
|  4 | Jerry    |   18 | 13803694825 | CHANGSHA |
|  6 | Fiveteen |   20 | 15145236589 | beijing  |
|  5 | Frank    |   26 | 12415452639 | 暂时未知 |
+----+----------+------+-------------+----------+
6 rows in set (0.00 sec)

mysql> select * from teacher order by age desc; #降序排列
+----+----------+------+-------------+----------+
| id | name     | age  | phone       | address  |
+----+----------+------+-------------+----------+
|  5 | Frank    |   26 | 12415452639 | 暂时未知 |
|  6 | Fiveteen |   20 | 15145236589 | beijing  |
|  3 | 饭饭     |   18 | 15145124023 | beijing  |
|  4 | Jerry    |   18 | 13803694825 | CHANGSHA |
|  2 | rain     |   16 | 17824852705 | DALIAN   |
|  1 | Tom      |   15 | 18192768186 | XIAN     |
+----+----------+------+-------------+----------+
6 rows in set (0.00 sec)

分组查询 (格式 -> 聚合函数 + 分组字段）

mysql> select avg(age) as '平均年龄', address as '地区' from teacher group by address;
+----------+----------+
| 平均年龄 | 地区       |
+----------+----------+
|  19.0000 | BEIJING  |
|  16.0000 | DALIAN   |
|  22.0000 | SHANGHAI |
|  15.0000 | XIAN     |
|  23.0000 | 暂时未知  |
+----------+----------+
5 rows in set (0.00 sec)

mysql> select avg(age), address from teacher group by address order by avg(age); #order by要在group by 之后
+----------+----------+
| avg(age) | address  |
+----------+----------+
|  15.0000 | XIAN     |
|  16.0000 | DALIAN   |
|  19.0000 | BEIJING  |
|  22.0000 | SHANGHAI |
|  23.0000 | 暂时未知 |
+----------+----------+
5 rows in set (0.00 sec)

mysql> select avg(age) as age, address from teacher group by address desc having age > 20; #having 分组筛选
+---------+----------+
| age     | address  |
+---------+----------+
| 23.0000 | 暂时未知  |
| 22.0000 | SHANGHAI |
+---------+----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select group_concat(name), gender from teacher group by gender; #聚合显示
+---------------------+--------+
| group_concat(name)  | gender |
+---------------------+--------+
| 饭饭,Frank,Fiveteen | 女     |
| Tom,rain,Jerry,Free | 男     |
+---------------------+--------+
2 rows in set (0.00 sec)

多表查询

数据库的连接方式

内连接
外连接 -> 左外连接、右外连接、全连接 (注意MySQL 已经取消全连接 full join但是可以用 union关键字实现)
交叉连接

内连接 -> inner join -> 在两张表的相同属性上做等值连接，当不使用 on 关键字时，与 cross join 交叉连接(取笛卡尔积) 相同

mysql> select * from student;
+----+---------+--------+------+----------+
| id | name    | gender | age  | dept     |
+----+---------+--------+------+----------+
|  1 | rain    | 男     |   23 | Cs       |
|  2 | MIA     | 女     |   19 | Japanese |
|  3 | free    | 男     |   18 | Math     |
|  4 | sss     | 女     |   18 | Cs       |
|  5 | Frank   | 男     |   28 | Cs       |
|  6 | BigHead | 男     |   23 | Math     |
|  7 | Jashen  | 男     |   22 | Math     |
+----+---------+--------+------+----------+
7 rows in set (0.00 sec)

mysql> select * from course;
+----+----------+---------+
| id | name     | creadit |
+----+----------+---------+
|  1 | 数据库   |       4 |
|  2 | 数学     |       2 |
|  3 | 操作系统 |       4 |
|  4 | 计网     |       3 |
|  5 | 日语     |       2 |
|  6 | 数据结构 |       4 |
+----+----------+---------+
6 rows in set (0.00 sec)

mysql> select * from score;
+----+-----+-------+
| id | cno | grade |
+----+-----+-------+
|  1 |   1 |    98 |
|  1 |   3 |    65 |
|  1 |   6 |    95 |
|  2 |   5 |   100 |
|  3 |   2 |    98 |
|  4 |   1 |    96 |
|  4 |   4 |    85 |
|  5 |   1 |   100 |
|  6 |   2 |    90 |
|  6 |   3 |    80 |
+----+-----+-------+
10 rows in set (0.00 sec)

mysql> select student.id, name, dept, score.cno, grade from student inner join score on student.id = score.id;
+----+---------+----------+-----+-------+
| id | name    | dept     | cno | grade |
+----+---------+----------+-----+-------+
|  1 | rain    | Cs       |   1 |    98 |
|  1 | rain    | Cs       |   3 |    65 |
|  1 | rain    | Cs       |   6 |    95 |
|  2 | MIA     | Japanese |   5 |   100 |
|  3 | free    | Math     |   2 |    98 |
|  4 | sss     | Cs       |   1 |    96 |
|  4 | sss     | Cs       |   4 |    85 |
|  5 | Frank   | Cs       |   1 |   100 |
|  6 | BigHead | Math     |   2 |    90 |
|  6 | BigHead | Math     |   3 |    80 |
+----+---------+----------+-----+-------+
10 rows in set (0.00 sec)

mysql> select student.id, student.name, dept, course.name as cname, score.grade from student inner join score on student.id = score.id inner join course on score.cno = course.id;
+----+---------+----------+----------+-------+
| id | name    | dept     | cname    | grade |
+----+---------+----------+----------+-------+
|  1 | rain    | Cs       | 数据库   |    98 |
|  1 | rain    | Cs       | 操作系统 |    65 |
|  1 | rain    | Cs       | 数据结构 |    95 |
|  2 | MIA     | Japanese | 日语     |   100 |
|  3 | free    | Math     | 数学     |    98 |
|  4 | sss     | Cs       | 数据库   |    96 |
|  4 | sss     | Cs       | 计网     |    85 |
|  5 | Frank   | Cs       | 数据库   |   100 |
|  6 | BigHead | Math     | 数学     |    90 |
|  6 | BigHead | Math     | 操作系统 |    80 |
+----+---------+----------+----------+-------+
10 rows in set (0.00 sec)

自然连接 natural join -> 根据同名字段自动进行连接并且去掉重复列，不需要使用 on关键字

如果两张表没有同名字段返回笛卡尔积

mysql> select * from student natural join score;
+----+---------+--------+------+----------+-----+-------+
| id | name    | gender | age  | dept     | cno | grade |
+----+---------+--------+------+----------+-----+-------+
|  1 | rain    | 男     |   23 | Cs       |   1 |    98 |
|  1 | rain    | 男     |   23 | Cs       |   3 |    65 |
|  1 | rain    | 男     |   23 | Cs       |   6 |    95 |
|  2 | MIA     | 女     |   19 | Japanese |   5 |   100 |
|  3 | free    | 男     |   18 | Math     |   2 |    98 |
|  4 | sss     | 女     |   18 | Cs       |   1 |    96 |
|  4 | sss     | 女     |   18 | Cs       |   4 |    85 |
|  5 | Frank   | 男     |   28 | Cs       |   1 |   100 |
|  6 | BigHead | 男     |   23 | Math     |   2 |    90 |
|  6 | BigHead | 男     |   23 | Math     |   3 |    80 |
+----+---------+--------+------+----------+-----+-------+
10 rows in set (0.00 sec)

左连接 left join - > 以左表为基准连接右表,没有的值用null补全

mysql> select student.id, student.name, score.cno, score.grade from student 
left join score on student.id = score.id;
+----+---------+------+-------+
| id | name    | cno  | grade |
+----+---------+------+-------+
|  1 | rain    |    1 |    98 |
|  1 | rain    |    3 |    65 |
|  1 | rain    |    6 |    95 |
|  2 | MIA     |    5 |   100 |
|  3 | free    |    2 |    98 |
|  4 | sss     |    1 |    96 |
|  4 | sss     |    4 |    85 |
|  5 | Frank   |    1 |   100 |
|  6 | BigHead |    2 |    90 |
|  6 | BigHead |    3 |    80 |
|  7 | Jashen  | NULL |  NULL |
+----+---------+------+-------+
11 rows in set (0.00 sec)

mysql> select score.id, score.cno, course.name, score.grade from score left 
join course on score.cno = course.id;
+----+-----+----------+-------+
| id | cno | name     | grade |
+----+-----+----------+-------+
|  1 |   1 | 数据库   |    98 |
|  1 |   3 | 操作系统 |    65 |
|  1 |   6 | 数据结构 |    95 |
|  2 |   5 | 日语     |   100 |
|  3 |   2 | 数学     |    98 |
|  4 |   1 | 数据库   |    96 |
|  4 |   4 | 计网     |    85 |
|  5 |   1 | 数据库   |   100 |
|  6 |   2 | 数学     |    90 |
|  6 |   3 | 操作系统 |    80 |
+----+-----+----------+-------+
10 rows in set (0.00 sec)

右连接 right join-> 已右排头为基准向右看齐

mysql> select * from course; 
+----+----------+---------+
| id | name     | creadit |
+----+----------+---------+
|  1 | 数据库   |       4 |
|  2 | 数学     |       2 |
|  3 | 操作系统 |       4 |
|  4 | 计网     |       3 |
|  5 | 日语     |       2 |
|  6 | 数据结构 |       4 |
|  7 | C语言    |       2 |
+----+----------+---------+
7 rows in set (0.00 sec)

mysql>  select score.id, score.cno, course.name, score.grade from score right join course on score.cno = course.id;
+------+------+----------+-------+
| id   | cno  | name     | grade |
+------+------+----------+-------+
|    1 |    1 | 数据库   |    98 |
|    4 |    1 | 数据库   |    96 |
|    5 |    1 | 数据库   |   100 |
|    3 |    2 | 数学     |    98 |
|    6 |    2 | 数学     |    90 |
|    1 |    3 | 操作系统 |    65 |
|    6 |    3 | 操作系统 |    80 |
|    4 |    4 | 计网     |    85 |
|    2 |    5 | 日语     |   100 |
|    1 |    6 | 数据结构 |    95 |
| NULL | NULL | C语言    |  NULL |
+------+------+----------+-------+
11 rows in set (0.00 sec)

子查询

in and not in

#找出成绩中有大于 95 的所有学生信息
mysql> select * from student where id in (select score.id from score where grade >= 95); 
+----+-------+--------+------+----------+
| id | name  | gender | age  | dept     |
+----+-------+--------+------+----------+
|  1 | rain  | 男     |   23 | Cs       |
|  2 | MIA   | 女     |   19 | Japanese |
|  3 | free  | 男     |   18 | Math     |
|  4 | sss   | 女     |   18 | Cs       |
|  5 | Frank | 男     |   28 | Cs       |
+----+-------+--------+------+----------+
5 rows in set (0.00 sec)

#返回所学科目任何一科都不大于95 的学生的信息
mysql> select * from student where id not in (select score.id from score where grade >= 95);
+----+---------+--------+------+------+
| id | name    | gender | age  | dept |
+----+---------+--------+------+------+
|  6 | BigHead | 男     |   23 | Math |
|  7 | Jashen  | 男     |   22 | Math |
+----+---------+--------+------+------+
2 rows in set (0.00 sec)

exists and not exists

#如果存在符合子查询条件的返回值则执行父查询 
mysql> select * from student where exists (select score.id from score where 
grade = 100);
+----+---------+--------+------+----------+
| id | name    | gender | age  | dept     |
+----+---------+--------+------+----------+
|  1 | rain    | 男     |   23 | Cs       |
|  2 | MIA     | 女     |   19 | Japanese |
|  3 | free    | 男     |   18 | Math     |
|  4 | sss     | 女     |   18 | Cs       |
|  5 | Frank   | 男     |   28 | Cs       |
|  6 | BigHead | 男     |   23 | Math     |
|  7 | Jashen  | 男     |   22 | Math     |
+----+---------+--------+------+----------+
7 rows in set (0.00 sec)

视图的基本操作

视图的两种设计算法 -> temptable (临时表算法)、 merge

创建视图

1
2
3

create view vw_stu_all as 
select name, phone, score from student inner join score on 
student.id = score.stuId;

查看视图

1	select * from vw_stu_all;

查看视图创建的信息

1	show create view vw_stu_all;

查看视图的结构

1	desc vw_stu_all;

修改视图

1 2	alter view vw_stu_all as select name from student;

删除视图

1	drop view vw_stu_all;

事务

开启事务

1	mysql> start transaction;

事务提交 -> commit –> 事务一旦提交就不可以回滚

mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
|  1 |   10.00 |
|  2 |   19.25 |
|  3 |   66.66 |
+----+---------+
3 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update wallet set balance=balance - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update wallet set balance=balance + 5 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
|  1 |    5.00 |
|  2 |   24.25 |
|  3 |   66.66 |
+----+---------+
3 rows in set (0.00 sec)

事务回滚 -> rollback

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from wallet;
Query OK, 3 rows affected (0.00 sec)

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

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
|  1 |    5.00 |
|  2 |   24.25 |
|  3 |   66.66 |
+----+---------+
3 rows in set (0.00 sec)

设置回滚点 -> savepoint

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values (4,15.00);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint first;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values (5, 25.15);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint second;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to first;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
|  1 |    5.00 |
|  2 |   24.25 |
|  3 |   67.66 |
|  4 |   15.00 |
+----+---------+
4 rows in set (0.00 sec)

存储过程

创建存储过程

mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> update teacher set name = 'Tom' where id = 1;
    -> update student set age= 18;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql>  delimiter ;
mysql> select * from teacher;
+----+------+-------------+---------+
| id | name | phone       | address |
+----+------+-------------+---------+
|  1 | MIA  | 18192768186 | XIAN    |
|  2 | rain | 17824852705 | DALIAN  |
+----+------+-------------+---------+
3 rows in set (0.00 sec)

mysql> call proc();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from teacher;
+----+------+-------------+---------+
| id | name | phone       | address |
+----+------+-------------+---------+
|  1 | Tom  | 18192768186 | XIAN    |
|  2 | rain | 17824852705 | DALIAN  |
+----+------+-------------+---------+
3 rows in set (0.00 sec)

删除存储过程

1	drop procedure proc;

函数

rand() -> 生成随机数

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

mysql> select * from teacher order by rand(); #随机排序
+----+-------+-------------+----------+
| id | name  | phone       | address  |
+----+-------+-------------+----------+
|  5 | Frank | 12415452639 | 暂时未知 |
|  4 | Jerry | 13803694825 | CHANGSHA |
|  3 | 饭饭  | 15145124023 | beijing  |
|  1 | Tom   | 18192768186 | XIAN     |
|  2 | rain  | 17824852705 | DALIAN   |
+----+-------+-------------+----------+
5 rows in set (0.00 sec)

mysql> select * from teacher order by rand() limit 3; #抽奖 -> 随机从teacher表中抽取三条数据
+----+-------+-------------+----------+
| id | name  | phone       | address  |
+----+-------+-------------+----------+
|  2 | rain  | 17824852705 | DALIAN   |
|  4 | Jerry | 13803694825 | CHANGSHA |
|  3 | 饭饭  | 15145124023 | beijing  |
+----+-------+-------------+----------+
3 row in set (0.00 sec)

向上取整

mysql> select ceil(4.2);
+-----------+
| ceil(4.2) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

向下取整

mysql> select floor(4.2);
+------------+
| floor(4.2) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

数据截取

mysql> select truncate(3.1415926, 3);
+------------------------+
| truncate(3.1415926, 3) |
+------------------------+
|                  3.141 |
+------------------------+
1 row in set (0.00 sec)

字符串操作

mysql> select lcase('RAIN');  #将字符串转换成小写
+---------------+
| lcase('RAIN') |
+---------------+
| rain          |
+---------------+
1 row in set (0.00 sec)

mysql> select ucase('rain'); #将字符串转换成大写的
+---------------+
| ucase('rain') |
+---------------+
| RAIN          |
+---------------+
1 row in set (0.00 sec)

mysql> select left('rain', 2); #从左侧截取字符串
+-----------------+
| left('rain', 2) |
+-----------------+
| ra              |
+-----------------+
1 row in set (0.00 sec)

mysql> select right('rain', 2); #从右侧截取字符串
+------------------+
| right('rain', 2) |
+------------------+
| in               |
+------------------+
1 row in set (0.00 sec)

mysql> select substring('rain is well', 3,5); #从指定位置截取字符串
+--------------------------------+
| substring('rain is well', 3,5) |
+--------------------------------+
| in is                          |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select concat('rain', 'figthing');   #拼接字符串
+----------------------------+
| concat('rain', 'figthing') |
+----------------------------+
| rainfigthing               |
+----------------------------+
1 row in set (0.00 sec)

获取当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-10-02 21:45:28 |
+---------------------+
1 row in set (0.00 sec)

获取unix时间戳

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

数据加密函数

mysql> select sha("jack loves rose");
+------------------------------------------+
| sha("jack loves rose")                   |
+------------------------------------------+
| 3247c2efec1e356224bac3044be2b2f6e9d0adee |
+------------------------------------------+
1 row in set (0.00 sec)