MySQL基础(续)
文章目录
- MySQL基础(续)
- 实战案例
- 1.搭建mysql服务
- 2.创建一个以你名字为名的数据库,并创建一张表student,该表包含三个字段(id,name,age),表结构如下:
- 3.查看下该新建的表有无内容(用select语句)
- 4.往新建的student表中插入数据(用insert语句),结果应如下所示:
- 5.修改lisi的年龄为50
- 6.以age字段降序排序
- 7.查询student表中年龄最小的3位同学跳过前2位
- 8.查询student表中年龄最大的4位同学
- 9.查询student表中名字叫zhangshan的记录
- 10.查询student表中名字叫zhangshan且年龄大于20岁的记录
- 11.查询student表中年龄在23到30之间的记录
- 12.修改wangwu的年龄为100
- 13.删除student中名字叫zhangshan且年龄小于等于20的记录
DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的操作。
INSERT语句
mysql> use runtime;
Database changed
mysql> create table jiang(id int not null,name varchar(10) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| jiang |
+-------------------+
1 row in set (0.00 sec)
mysql> desc jiang;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
//一次插入多条记录
mysql> insert into jiang(id,name,age) values(1,'li',20),(2,'zhang',21),(3,'wang',22),(4,'liu',25);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
4 rows in set (0.00 sec)
//一次插入一条记录
mysql> insert into jiang(id,name,age) values(5,'tom',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
| 5 | tom | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
SELECT语句
字段column表示法
表示符 | 代表什么? |
---|---|
* | 所有字段 |
as | 字段别名,如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句WHERE
操作类型 | 常用操作符 |
---|---|
操作符 | >,<,>=,<=,=,!= BETWEEN column# AND column# LIKE:模糊匹配 RLIKE:基于正则表达式进行模式匹配 IS NOT NULL:非空 IS NULL:空 |
条件逻辑操作 | AND OR NOT |
ORDER BY:排序,默认为升序(ASC)
ORDER BY语句 | 意义 |
---|---|
ORDER BY ‘column_name’ | 根据column_name进行升序排序 |
ORDER BY ‘column_name’ DESC | 根据column_name进行降序排序 |
ORDER BY ’column_name’ LIMIT 2 | 根据column_name进行升序排序 并只取前2个结果 |
ORDER BY ‘column_name’ LIMIT 1,2 | 根据column_name进行升序排序 并且略过第1个结果取后面的2个结果 |
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
4 rows in set (0.00 sec)
mysql> select name from jiang;
+-------+
| name |
+-------+
| li |
| zhang |
| wang |
| liu |
+-------+
4 rows in set (0.00 sec)
//升序排序
mysql> select * from jiang order by age
-> ;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
4 rows in set (0.00 sec)
//降序排序
mysql> select * from jiang order by age desc;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 4 | liu | 25 |
| 3 | wang | 22 |
| 2 | zhang | 21 |
| 1 | li | 20 |
+----+-------+------+
4 rows in set (0.00 sec)
//升序排序并只取前2个结果
mysql> select * from jiang order by age limit 2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
+----+-------+------+
2 rows in set (0.00 sec)
//升序排序并且略过第1个结果取后面的2个结果
mysql> select * from jiang order by age limit 1,2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | zhang | 21 |
| 3 | wang | 22 |
+----+-------+------+
2 rows in set (0.00 sec)
mysql> select * from jiang where age >=22;
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+------+------+
2 rows in set (0.00 sec)
mysql> select * from jiang where age >=22 and name = 'wang';
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | wang | 22 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from jiang where age between 21 and 25;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
3 rows in set (0.00 sec)
mysql> select * from jiang where age is not null;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
4 rows in set (0.00 sec)
mysql> select * from jiang where age is null;
+----+------+------+
| id | name | age |
+----+------+------+
| 5 | tom | NULL |
+----+------+------+
1 row in set (0.00 sec)
update语句
//更新
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
| 5 | tom | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
mysql> update jiang set age = 28 where name = 'tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from jiang where name = 'tom';
+----+------+------+
| id | name | age |
+----+------+------+
| 5 | tom | 28 |
+----+------+------+
1 row in set (0.00 sec)
delete语句
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
| 5 | tom | 28 |
+----+-------+------+
5 rows in set (0.01 sec)
//删除某条记录
mysql> delete from jiang where id = 5;
Query OK, 1 row affected (0.00 sec)
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
4 rows in set (0.00 sec)
//删除整张表的内容
mysql> delete from jiang;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from jiang;
Empty set (0.00 sec)
mysql> desc jiang;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
truncate语句
truncate与delete的区别:
语句类型 | 特点 |
---|---|
delete | DELETE删除表内容时仅删除内容,但会保留表结构 DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
truncate | 删除表中所有数据,且无法恢复 表结构、约束和索引等保持不变,新添加的行计数值重置为初始值 执行速度比DELETE快,且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放 对于有外键约束引用的表,不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
mysql> select * from jiang;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | li | 20 |
| 2 | zhang | 21 |
| 3 | wang | 22 |
| 4 | liu | 25 |
+----+-------+------+
4 rows in set (0.00 sec)
mysql> truncate jiang;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from jiang;
Empty set (0.00 sec)
mysql> desc jiang;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
DCL操作
创建授权grant
权限类型(priv_type)
权限类型 | 代表什么? |
---|---|
ALL | 所有权限 |
SELECT | 读取内容的权限 |
INSERT | 插入内容的权限 |
UPDATE | 更新内容的权限 |
DELETE | 删除内容的权限 |
指定要操作的对象db_name.table_name
表示方式 | 意义 |
---|---|
. | 所有库的所有表 |
db_name | 指定库的所有表 |
db_name.table_name | 指定库的指定表 |
WITH GRANT OPTION:被授权的用户可将自己的权限副本转赠给其他用户,说白点就是将自己的权限完全复制给另一个用户。不建议使用。
查看授权
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
实战案例
1.搭建mysql服务
[root@localhost ~]# yum -y install mariadb*
Failed to set locale, defaulting to C.UTF-8
CentOS Stream 8 - AppStream 8.2 MB/s | 24 MB 00:02
CentOS Stream 8 - BaseOS 6.1 MB/s | 25 MB 00:03
CentOS Stream 8 - Extras 26 kB/s | 18 kB 00:00
Last metadata expiration check: 0:00:01 ago on Tue Jul 26 17:32:02 2022.
Dependencies resolved.
================================================================
Package Arch Version Repo Size
================================================================
Installing:
mariadb x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
appstream 6.0 M
mariadb-backup x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
appstream 6.1 M
mariadb-common x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
................................
[root@localhost ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password = password('123456');
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
2.创建一个以你名字为名的数据库,并创建一张表student,该表包含三个字段(id,name,age),表结构如下:
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> create database lijiang;
Query OK, 1 row affected (0.00 sec)
mysql> use lijiang;
Database changed
mysql> create table student(id int(11) primary key auto_increment not null,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.00 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
3.查看下该新建的表有无内容(用select语句)
mysql> select * from student;
Empty set (0.00 sec)
4.往新建的student表中插入数据(用insert语句),结果应如下所示:
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
mysql> > insert into student(id,name,age) values(1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',null),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
5.修改lisi的年龄为50
mysql> update student set age=50 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
6.以age字段降序排序
mysql> select * from student order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.00 sec)
7.查询student表中年龄最小的3位同学跳过前2位
mysql> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
3 rows in set (0.00 sec)
8.查询student表中年龄最大的4位同学
mysql> select * from student order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
9.查询student表中名字叫zhangshan的记录
mysql> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.01 sec)
10.查询student表中名字叫zhangshan且年龄大于20岁的记录
mysql> select * from student where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
11.查询student表中年龄在23到30之间的记录
mysql> select * from student where age > 23 and age < 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
3 rows in set (0.00 sec)
12.修改wangwu的年龄为100
mysql> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
13.删除student中名字叫zhangshan且年龄小于等于20的记录
mysql> delete from student where name = 'zhangshan' and age <= 20;
Query OK, 1 row affected (0.00 sec)
mysql> select *from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)