MySQL数据库的基础操作

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数据库进阶版

已标记关键词 清除标记
第1部分 基础篇  第1章 MySQL的安装与配置    1.1 MySQL的下载     1.1.1 在Windows平台下下载MySQL     1.1.2 在Linux平台下下载MySQL    1.2 MySQL的安装     1.2.1 在Windows平台下安装MySQL     1.2.2 在Linux平台下安装MySQL    1.3 MySQL的配置     1.3.1 Windows平台下配置MySQL     1.3.2 Linux平台下配置MySQL    1.4 启动和关闭MySQL服务     1.4.1 在Windows平台下启动和关闭 MySQL服务     1.4.2 在Linux平台下启动和关闭MySQL服务    1.5 小结 显示全部信息第1部分 基础篇  第1章 MySQL的安装与配置    1.1 MySQL的下载     1.1.1 在Windows平台下下载MySQL     1.1.2 在Linux平台下下载MySQL    1.2 MySQL的安装     1.2.1 在Windows平台下安装MySQL     1.2.2 在Linux平台下安装MySQL    1.3 MySQL的配置     1.3.1 Windows平台下配置MySQL     1.3.2 Linux平台下配置MySQL    1.4 启动和关闭MySQL服务     1.4.1 在Windows平台下启动和关闭MySQL服务     1.4.2 在Linux平台下启动和关闭MySQL服务    1.5 小结   第2章 SQL基础    2.1 SQL简介    2.2 (My)SQL使用入门     2.2.1 SQL分类     2.2.2 DDL语句     2.2.3 DML语句     2.2.4 DCL语句    2.3 帮助的使用     2.3.1 按照层次看帮助     2.3.2 快速查阅帮助     2.3.3 常用的网络资源    2.4 小结   第3章 MySQL支持的数据类型    3.1 数值类型    3.2 日期时间类型    3.3 字符串类型     3.3.1 CHAR和VARCHAR类型     3.3.2 BINARY和VARBINARY类型     3.3.3 ENUM类型     3.3.4 SET类型    3.4 小结   第4章 MySQL中的运算符    4.1 算术运算符    4.2 比较运算符    4.3 逻辑运算符    4.4 位运算符    4.5 运算符的优先级    4.6 小结   第5章 常用函数    5.1 字符串函数    5.2 数值函数    5.3 日期和时间函数    5.4 流程函数    5.5 其他常用函数    5.6 小结   第6章 图形化工具的使用    6.1 MySQLAdministrator     6.1.1 连接管理     6.1.2 健康检查     6.1.3 备份管理     6.1.4 Catalogs    6.2 MySQLQueryBrower    6.3 phpMyAdmin     6.3.1 数据库管理     6.3.2 数据库对象管理     6.3.3 权限管理     6.3.4 导入导出数据    6.4 小结  第2部分 开发篇  第7章 表类型(存储引擎)的选择    7.1 MySQL存储引擎概述    7.2 各种存储引擎的特性     7.2.1 MyISAM     7.2.2 InnoDB     7.2.3 MEMORY     7.2.4 MERGE    7.3 如何选择合适的存储引擎    7.4 小结   第8章 选择合适的数据类型    8.1 CHAR与VARCHAR    8.2 TEXT与BLOB    8.3 浮点数与定点数    8.4 日期类型选择    8.5 小结   第9章 字符集    9.1 字符集概述    9.2 Unicode简述    9.3 汉字及一些常见字符集    9.4 怎样选择合适的字符集    9.5 MySQL支持的字符集简介    9.6 MySQL字符集的设置     9.6.1 服务器字符集和校对规则     9.6.2 数据库字符集和校对规则     9.6.3 表字符集和校对规则     9.6.4 列字符集和校对规则     9.6.5 连接字符集和校对规则    9.7 字符集的修改步骤    9.8 小结   第10章 索引的设计和使用    10.1 索引概述    10.2 设计索引的原则    10.3 BTREE索引与HASH索引    10.4 小结 
相关推荐
内容简介 · · · · · · 本书从数据库基础、开发、优化、管理维护4个方面对MySQL进行了详细的介绍,其中每一部分都独立成篇。本书内容实用,覆盖广泛,讲解由浅入深,适合于各个层次的读者。 基础篇主要适合于MySQL的初学者,内容包括MySQL的安装与配置、SQL基础MySQL支持的数据类型、MySQL中的运算符、常用函数、图形化工具的使用等。开发篇主要适合于MySQL的设计和开发人员,内容包括表类型(存储引擎)的选择、选择合适的数据类型、字符集、索引的设计和使用、视图、存储过程和函数、触发器、事务控制和锁定语句、SQL中的安全问题、SQL Mode及相关问题等。优化篇主要适合于开发人员和数据库管理员,内容包括常用SQL技巧和常见问题、SQL优化、优化数据库对象、锁问题、优化 MySQL Server、磁盘I/O问题、应用优化等。管理维护篇主要适合于数据库管理员,内容包括MySQL高级安装和升级、MySQL中的常用工具、MySQL 日志、备份与恢复、MySQL权限与安全、MySQL复制、MySQL Cluster、MySQL常见问题和应用技巧等。 本书的作者都是MySQL方面的资深DBA。本书不但融入了他们丰富的工作经验和多年的使用心得,还提供了大量来自工作现场的实例,具有很强的实战性和可操作性。 本书适用于数据库管理人员、数据库开发人员、系统维护人员、数据库初学者及其他数据库从业人员,也可以作为大中专院校相关专业师生的参考用书和相关培训机构的培训教材。 作者简介 · · · · · · 网易技术部DBA组员工
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页