MySQL数据库的基础操作
- 我使用的是MySQL数据库专用软件MySQL5.7
专用软件还是十分好用的，大家如果没有MySQL5.7，没关系，也可以使用windows自带的数据库操作软件，快捷键 win + R，接着在对话框中输入cmd，就会弹出一个和MySQL5.7相似的软件页面（其中需要提前配置环境变量，你可以在百度上搜索一下，在这里就不累述，重点在基础操作），如下图：
一、创建
- 1.创建数据库
- 查看系统原有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
- 2.创建自己数据库
mysql> create database if not exists huashanzhizai;
Query OK, 1 row affected (0.00 sec)
- 3.查看自己创建的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| huashanzhizai |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
到这里，自己的数据库已经创建好了，接下来就应该在数据库中创建自己的表。就相当于买了杯子，现在可以王水杯里面加水了！
- 1.创建表
- 首先选中自己创建的数据库
mysql> use huashanzhizai;
Database changed
- 2.查看新建的表在原来的数据库中是否存在
mysql> drop table if exists stu_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 3.不存在，则新建表，同时给表里面赋上属性
mysql> create table stu_test(
-> id INT,
-> name varchar(10),
-> password varchar(10),
-> age int ,
-> sex varchar(1),
-> birthday timestamp,
-> amout decimal(6,2),
-> resume text
-> );
Query OK, 0 rows affected (0.04 sec)
- 4.查看自己创建的表
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| stu_test |
+-------------------------+
1 row in set (0.00 sec)
- 5.添加注释
mysql> drop table if exists stu_test;
Query OK, 0 rows affected (0.02 sec)
mysql> create table stu_test(
-> id INT,
-> name varchar(10) comment '姓名',
-> password varchar(10) comment '密码',
-> age int comment '年龄',
-> sex varchar(1),
-> birthday timestamp,
-> amout decimal(6,2),
-> resume text
-> );
Query OK, 0 rows affected (0.04 sec)
- 6.显示字段
mysql> desc stu_test;
+---------+---------------+----------+---------+---------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+----------+---------+---------------------+-------------------+
| id | int(11) | YES | | NULL | |
| name |varchar(10) | YES | | NULL | |
| password|varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex |varchar(1) | YES | | NULL | |
| birthday| timestamp | NO | | CURRENT_TIMESTAMP |on update CURRENT_TIMESTAMP |
| amout |decimal(6,2) | YES | | NULL | |
| resume | text | YES | | NULL | |
+---------+---------------+----------+---------+---------------------+-------------------+
8 rows in set (0.01 sec)
二、操作表（增、删、查、改）
1、增（插入）
- 1.准备工作
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table student(
-> id INT,
-> sn int ,
-> name varchar(20),
-> qq_mail varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
- 2.插入
//单行全列插入
mysql> insert into student values(1,101,'bit','1963599369@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(2,102,'yanghua','123@qq.com');
Query OK, 1 row affected (0.01 sec)
//每次只能插入一行，且每一个字段要和数据库对应
- 3.查看
mysql> select *from student;
+------+------+---------+-------------------+
| id | sn | name | qq_mail |
+------+------+---------+-------------------+
| 1 | 101 | bit | 1963599369@qq.com |
| 2 | 102 | yanghua | 123@qq.com |
+------+------+---------+-------------------+
2 rows in set (0.00 sec)
- 4.只插入指定列
mysql> insert into student (id,name) values(3,'kangxiangkun'),(4,'cuiyulu');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+------+------+--------------+-------------------+
| id | sn | name | qq_mail |
+------+------+--------------+-------------------+
| 1 | 101 | bit | 1963599369@qq.com |
| 2 | 102 | yanghua | 123@qq.com |
| 3 | NULL | kangxiangkun | NULL |
| 4 | NULL | cuiyulu | NULL |
+------+------+--------------+-------------------+
4 rows in set (0.00 sec)
//说明插入成功！！！
- 5.删除指定行
mysql> delete from student where id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+--------------+-------------------+
| id | sn | name | qq_mail |
+------+------+--------------+-------------------+
| 1 | 101 | bit | 1963599369@qq.com |
| 3 | NULL | kangxiangkun | NULL |
| 4 | NULL | cuiyulu | NULL |
+------+------+--------------+-------------------+
3 rows in set (0.00 sec)
- 6.指定列查询
mysql> select id,name from student;
+------+--------------+
| id | name |
+------+--------------+
| 1 | bit |
| 3 | kangxiangkun |
| 4 | cuiyulu |
+------+--------------+
3 rows in set (0.00 sec)
- 7.将id列所有数+10
mysql> select id+10,name from student;
+-------+--------------+
| id+10 | name |
+-------+--------------+
| 11 | bit |
| 13 | kangxiangkun |
| 14 | cuiyulu |
+-------+--------------+
3 rows in set (0.00 sec)
//查询字段为表达式
mysql> select id,name,10 from student;
+------+--------------+----+
| id | name | 10 |
+------+--------------+----+
| 1 | bit | 10 |
| 3 | kangxiangkun | 10 |
| 4 | cuiyulu | 10 |
+------+--------------+----+
3 rows in set (0.00 sec)
//select * from student中*指代所有列的元素。select * from student查询方式同时不改变表的值。
- 8.起别名
mysql> select id,name as 姓名 from student;
+------+--------------+
| id | 姓名 |
+------+--------------+
| 1 | bit |
| 3 | kangxiangkun |
| 4 | cuiyulu |
+------+--------------+
3 rows in set (0.00 sec)
//也可以给表起别名
mysql> select id,name from student as teacher;
+------+--------------+
| id | name |
+------+--------------+
| 1 | bit |
| 3 | kangxiangkun |
| 4 | cuiyulu |
+------+--------------+
3 rows in set (0.00 sec)
2、查
1.准备工作
mysql> DROP TABLE IF EXISTS exam;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE exam (
-> id INT,
-> name VARCHAR(20),
-> chinese DECIMAL(3,1),
-> math DECIMAL(3,1),
-> english DECIMAL(3,1)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO exam (id,name, chinese, math, english) VALUES
-> (1,'唐三藏', 67, 98, 56),
-> (2,'孙悟空', 87.5, 78, 77),
-> (3,'猪悟能', 88, 98.5, 90),
-> (4,'曹孟德', 82, 84, 67),
-> (5,'刘玄德', 55.5, 85, 45),
-> (6,'孙权', 70, 73, 78.5),
-> (7,'宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
2.去重 distinct（数学成绩有重复）
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98.0 |
| 78.0 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+
6 rows in set (0.00 sec)
3.排序 order by（将数学成绩升序）（默认）
mysql> select * from exam order by math asc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
降序 desc
mysql> select * from exam order by math desc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
注意：不要使用关键字当作表名，例如desc；如果非要使用，则将
关键字表名改为 desc（esc下面的那个按键）
- 对排序字段为NULL的时候（升序）
mysql> insert into exam (id,name, chinese, math, english) values
-> (8,'鲁智深', 70, null, 38);
Query OK, 1 row affected (0.01 sec)
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
mysql> select * from exam order by math asc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
- 对排序字段为NULL的时候（降序）
mysql> select * from exam order by math desc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
- 同时对多个字段进行排序（chinese降序，math，english升序）
mysql> select name,chinese,math, english from exam
-> order by chinese desc,math,english;
+-----------+---------+------+---------+
| name | chinese | math | english |
+-----------+---------+------+---------+
| 猪悟能 | 88.0 | 98.5 | 90.0 |
| 孙悟空 | 87.5 | 78.0 | 77.0 |
| 曹孟德 | 82.0 | 84.0 | 67.0 |
| 宋公明 | 75.0 | 65.0 | 30.0 |
| 鲁智深 | 70.0 | NULL | 38.0 |
| 孙权 | 70.0 | 73.0 | 78.5 |
| 唐三藏 | 67.0 | 98.0 | 56.0 |
| 刘玄德 | 55.5 | 85.0 | 45.0 |
+-----------+---------+------+---------+
8 rows in set (0.00 sec)
4.查询总分 ‘+’
mysql> select id,name,chinese+math+english from exam;
+------+-----------+----------------------+
| id | name | chinese+math+english |
+------+-----------+----------------------+
| 1 | 唐三藏 | 221.0 |
| 2 | 孙悟空 | 242.5 |
| 3 | 猪悟能 | 276.5 |
| 4 | 曹孟德 | 233.0 |
| 5 | 刘玄德 | 185.5 |
| 6 | 孙权 | 221.5 |
| 7 | 宋公明 | 170.0 |
| 8 | 鲁智深 | NULL |
+------+-----------+----------------------+
8 rows in set (0.00 sec)
where 条件查询
1.查询数学成绩大于80分的 同学的个人信息及数学成绩
mysql> select id,name,math from exam where math > 80;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 1 | 唐三藏 | 98.0 |
| 3 | 猪悟能 | 98.5 |
| 4 | 曹孟德 | 84.0 |
| 5 | 刘玄德 | 85.0 |
+------+-----------+------+
4 rows in set (0.00 sec)
- 查询数学成绩大于80分的 同学的个人信息及数学成绩，然后根据数学成绩升序排序
mysql> select id,name,math from exam where math > 80 order by math;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 4 | 曹孟德 | 84.0 |
| 5 | 刘玄德 | 85.0 |
| 1 | 唐三藏 | 98.0 |
| 3 | 猪悟能 | 98.5 |
+------+-----------+------+
4 rows in set (0.00 sec)
- 查询数学成绩等于98分的同学及个人信息
mysql> select id,name,math from exam where math = 98;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 1 | 唐三藏 | 98.0 |
+------+-----------+------+
1 row in set (0.00 sec)
- 2.注意NULL: = 不安全 对于NULL 查不到
mysql> select id,name,math from exam where math = NULL;
Empty set (0.00 sec)
- 解决问题：使用 <=> 这个运算符，就可以查到
mysql> select id,name,math from exam where math <=> NULL;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 8 | 鲁智深 | NULL |
+------+-----------+------+
1 row in set (0.00 sec)
- 3.不等于运算符 !=
mysql> select id,name,math from exam where math != 98;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 2 | 孙悟空 | 78.0 |
| 3 | 猪悟能 | 98.5 |
| 4 | 曹孟德 | 84.0 |
| 5 | 刘玄德 | 85.0 |
| 6 | 孙权 | 73.0 |
| 7 | 宋公明 | 65.0 |
+------+-----------+------+
6 rows in set (0.00 sec)
- 除了这个值之外的其他值，与！=效果相同 <>
mysql> select id,name,math from exam where math <> 98;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 2 | 孙悟空 | 78.0 |
| 3 | 猪悟能 | 98.5 |
| 4 | 曹孟德 | 84.0 |
| 5 | 刘玄德 | 85.0 |
| 6 | 孙权 | 73.0 |
| 7 | 宋公明 | 65.0 |
+------+-----------+------+
6 rows in set (0.00 sec)
- 查询数学成绩70-90之间的同学及成绩 A and B
mysql> select id,name,math from exam where math between 70 and 90;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 2 | 孙悟空 | 78.0 |
| 4 | 曹孟德 | 84.0 |
| 5 | 刘玄德 | 85.0 |
| 6 | 孙权 | 73.0 |
+------+-----------+------+
4 rows in set (0.00 sec)
- 5.查询数学成绩在98,85,73 中的 所有同学的数学成绩和个人信息 in（x，y，z，…）
mysql> select id,name,math from exam where math in(98,85,73);
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 1 | 唐三藏 | 98.0 |
| 5 | 刘玄德 | 85.0 |
| 6 | 孙权 | 73.0 |
+------+-----------+------+
3 rows in set (0.00 sec)
- 用or也可以达到精确查询的效果
mysql> select id,name,math from exam where math=98 or math=85 or math=73;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 1 | 唐三藏 | 98.0 |
| 5 | 刘玄德 | 85.0 |
| 6 | 孙权 | 73.0 |
+------+-----------+------+
3 rows in set (0.01 sec)
- 6.NULL和NOT NULL
mysql> select id,name,math from exam where math is NULL;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 8 | 鲁智深 | NULL |
+------+-----------+------+
1 row in set (0.00 sec)
mysql> select id,name,math from exam where math is NOT NULL;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 1 | 唐三藏 | 98.0 |
| 2 | 孙悟空 | 78.0 |
| 3 | 猪悟能 | 98.5 |
| 4 | 曹孟德 | 84.0 |
| 5 | 刘玄德 | 85.0 |
| 6 | 孙权 | 73.0 |
| 7 | 宋公明 | 65.0 |
+------+-----------+------+
7 rows in set (0.00 sec)
- 7.模糊查询：like
- 重新建表
mysql> insert into exam(id,name,chinese,math,english) values(9,'张三丰',10,11,90);
Query OK, 1 row affected (0.01 sec)
mysql> insert into exam values(10,'张三',80,61,50);
Query OK, 1 row affected (0.00 sec)
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)
- 查找名字当中 包含 "三"的 学生的信息
mysql> select * from exam where name like '%三%';
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)
- ’%三’表示以三结尾
mysql> select * from exam where name like '%三';
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
- ’三%’表示以三开头
mysql> select * from exam where name like '三%';
Empty set (0.00 sec)
另一种模糊匹配机制
- ’孙_’表示孙+一个模糊匹配的字
mysql> select * from exam where name like '孙_';
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
- ’孙_’表示孙+两个模糊匹配的字
mysql> select * from exam where name like '孙__';
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
+------+-----------+---------+------+---------+
1 row in set (0.00 sec)
- 分页查询 Limit
- 为什么要分页？因为在一个网页上加载太多数据会影响cpu的效率，分页的话，系统只会加载额定 的数据，效率提高
mysql> select * from exam limit 0,5;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql> select * from exam limit 1,4;
+------+-----------+---------+---------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+---------+---------+
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
+------+-----------+---------+------+------------+
4 rows in set (0.00 sec)
//如果偏移量太大，则只能查到表中有限个数据
mysql> select * from exam limit 5,10;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)
**如果起始数太大则查不到数据
mysql> select * from exam limit 12,5;
Empty set (0.00 sec)
//如果没有写起始数据，则默认从0开始
mysql> select * from exam limit 5;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)
3、改
- 1.数据更新 update
将孙权的数学成绩，更新为99分
//更新前：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 |11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)
//更新代码：
mysql> update exam set math = 99 where name = '孙权';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//更新后：
mysql> select *from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 99.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)
- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为70 分
//更新前：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 99.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)
//更新代码：
mysql> update exam set math = 60,chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//更新后：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 70.0 | 60.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 99.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)
- 2.综合
- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
//更新前：
mysql> select id ,name,chinese+english+math total from exam order by
chinese+english+math asc limit 1,3;
+------+-----------+-------+
| id | name | total |
+------+-----------+-------+
| 9 | 张三丰 | 111.0 |
| 7 | 宋公明 | 170.0 |
| 5 | 刘玄德 | 185.5 |
+------+-----------+-------+
3 rows in set (0.00 sec)
//更新代码：
mysql> update exam set math = math - 30 where chinese + math + english is not
NULL order by chinese + math + english limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
//更新后：
mysql> select * from exam;
+------+-----------+---------+-------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+-------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 70.0 | 60.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 55.0 | 45.0 |
| 6 | 孙权 | 70.0 | 99.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 35.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | -19.0| 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+-------+---------+
10 rows in set (0.00 sec)
4、删
- 删除 delete
- 删除孙悟空的考试成绩
//原表代码：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 70.0 | 60.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 99.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)
//删除代码：
mysql> delete from exam where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
//删除后：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 70.0 | 60.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 99.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 8 | 鲁智深 | 70.0 | NULL | 38.0 |
| 9 | 张三丰 | 10.0 | 11.0 | 90.0 |
| 10 | 张三 | 80.0 | 61.0 | 50.0 |
+------+-----------+---------+------+---------+
9 rows in set (0.00 sec)
//直接删除表：会删除表内的所有的数据，但是这张表还是存在的，只不过里面没有数据了
mysql> delete from exam;
Query OK, 9 rows affected (0.01 sec)
//表中没有数据了
mysql> select * from exam;
Empty set (0.00 sec)
//但是表还在，只是表中没有数据了
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| exam |
| student |
+-------------------------+
2 rows in set (0.00 sec)
- 删除表的本身
mysql> drop table exam;
Query OK, 0 rows affected (0.03 sec)
//现在表已经不存在了
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| student |
+-------------------------+
1 row in set (0.00 sec)
恭喜你，终于学完MySQL基础操作了，如果你还想提高，后面还有MySQL数据库进阶版
转载：https://blog.csdn.net/huashanzhizai/article/details/115315807
查看评论