sql语句的使用&mysql单表练习（小白专用版之二）

2.1简单数据表内容的增删改

DML - 增删改

DQL - 查询

对数据表的常见操作简称：CRUD。

create(增) read(查) update(更改) delete(删除)

insert语句----向数据表插入数据

方式一：全写

语法：insert into 表名 (列名1，列名2，列名3…) values (值1，值2，值3…);

语法：insert into 表名 values (值1，值2，值3…);

注意事项：

1、值与列一一对应。有多少个列，就需要写多少个值。如果某一个列没有值。可以使用null。表示插入空。

2、值的数据类型，与列被定义的数据类型要相匹配。并且值的长度，不能够超过定义的列的长度。

3、字符串：插入字符类型的数据，建议写英文单引号括起来。在mysql中，使用单引号表示字符串。

4、date 时间类型的数据也得使用英文单引号括起来： 如’yyyy-MM-dd’,’yyyy/MM/dd’,’yyyyMMdd’ 。

5、如果主键是自动增长的，可以直接插入数值null。

注意：使用 select * from 表名 —查看该表的所有信息。

练习一：给person表中的所有列插入数据。

插入语句：

insert into person(id,username,password,sex,birthday,salary)

values(null,‘zhangsan’,‘123’,‘nan’,‘1992-8-19’,12000);

注意啦：当插入含有中文的数据的时候，出现如下错误。

插入语句：(注意:乱码问题存在于 5.5 之前的版本 5.6已经解决了这个问题!!)

insert into person(id,username,password,sex,birthday,salary)

values(null,‘柳岩’,‘123’,‘女’,‘1992-8-19’,12000);

错误原因：上述错误是乱码错误，主要是因为编码和解码使用的编码表不一致导致的。

即dos窗口进行编码的时候使用的编码表和数据库解码的时候使用的编码表不一致导致的。

分析错误：

1.首先我们来查看下当前数据库表person的编码表：

person表使用的gbk编码表。

2.接下来查看dos窗口的编码表：

dos窗口的编码表也是gbk。

问题：既然person表的编码表是gbk，而dos窗口也是gbk，两个编码表是一样的，那么怎么会发生乱码呢？

在对数据表中的数据进行插入的时候，使用的dos窗口，这时dos窗口需要对插入的中文进行编码，然后把数据插入到数据库中。而dos窗口中使用的编码表是gbk。但是在mysql数据库中针对客户端控制的设置的编码表是utf8.

解决方案：这时只能修改数据库中默认的客户端的编码表。

修改mysql/my.ini 的配置文件。

my.ini 在mysql的安装目录：将客户端的字符集修改为gbk。

修改为：

注意：修改完mysql的配置文件之后需要重新连接数据库。

1）退出数据库，重新登录

2）使用数据库mydb2,向person表中插入数据，重新查询person表。

方式二：省略部分列

insert into 表名(列名，列名，列名。。。。) values（值，值，值。。。。）

注意：

可以省略部分列名。某一列 有默认值，或者允许为空，才可以省略。

主键是自增长的认为是有默认值的。也可以省略

注意：在插入数据的时候，如果某些列可以为null，或者是自动增长的列，或者有默认值的，在插入的时候可以省略。

练习：给表中不为空的列插入数据。

插入语句：insert into person(username,password,birthday) values(‘suoge’,‘123’,‘1995-10-19’);

方式三：省略所有的列(掌握)

insert into 表名 values（值，值，值，值）；

1、表中有多少列，就有多个值。

2、按照表的结构，列和值去对应。 desc 表名 得到列的顺序。 从上倒下，对应的值，从左到右插入数据。

注意：如果给表中的所有列插入数据，这时可以省略表名后面的列名，直接写values。

插入语句：

insert into person values(null,‘wangwu’,‘123’,‘nv’,‘1987-8-9’,13000);

2.2总结：

1）如果主键：是自增长。不需要人工赋值（可以给值null）。数据库会按照自己的算法，为主键填充值。

2）通常遇到主键自增长，人工赋值的时候，赋值null。mysql会自己把null替换成新的值。

数据的插入:

两种方式 – 根据列名插入对应的值

直接插入所有的列 - 将列名省略掉;

2.6.2.update语句—数据记录修改操作

语法: update 表名 set 列名=值,列名=值… [ where条件语句 ];

注意事项：

1、如果不加条件语句，将会修改某一列的所有值。

2、一般修改数据时，都需要增加条件。（重点）

练习一：修改person表中的所有用户的password 为 abcdef。

分析：因为要修改所有的用户，所以这里不需要添加条件。

更新语句：update person set password=‘abcdef’;

练习二：修改姓名为zhangsan的这个用户的薪水 88888。

分析：由于姓名要求是zhangsan，所以这里条件就是username=’zhangsan’

更新语句：update person set salary=88888 where username=‘zhangsan’;

练习三：把id为 3 的用户的username和password修改为 zhaoliu。

修改语句：

update person set username=‘zhaoliu’,password=‘zhaoliu’ where id=3;

2.6.3delete语句-----删除表中数据的语句

语法：delete from 表名 [where条件语句]

注意：

1）如果删除表中的记录时，没有添加where条件，这时会把表中的所有数据删除。表依然存在，是空的表。

2）delete是删除行的数据。

练习1：删除person表中username为zhaoliu的用户记录。

删除语句： delete from person where username=‘zhaoliu’;

练习2：删除表中所有记录。

删除语句：delete from person;

练习3：向person表中在添加一个新的数据。

插入语句：insert into person(username,password,birthday) values(‘tianqi’,‘123’,‘1990-9-7’);

问题：我们发现使用delete语句将数据库表中所有的数据删除之后，在新添加的数据之后，id是在原来基础上继续递增，而不是1。那么我们想让新添加的数据id从1开始，怎么办呢？

truncate 语句----删除数据

truncate table 表名：

先删除表,再创建表，就等于将数据全部删除了。也就是说上述语句执行时会把表的所有内容都删除掉，然后在创建一个新的表。

性能问题： truncate table的性能相对来说更好，尤其删除的数据越多，越明显。

练习1：向person表中插入数据，然后使用：truncate table 表名。 来删除所有的数据。

插入语句：

删除语句：

如果要删除一张表中的所有数据，也可以使用 truncate table 表名;

执行时会把表的所有内容都删除掉，然后在创建一个新的表。

truncate 缺点：不能有条件的删除。不能恢复数据

delete 是逐行删除，并没有恢复初始化值。 可以恢复数据

面试题1：

delete删除表中的数据时没有加where 条件，会删除表中的所有数据，它与truncate 有什么区别？

1.DELETE

・DML语言

・数据还可以找回来，恢复回来

逐条删除 删除速度要慢一些

・可以有条件的删除。DELETE FROM 表名 WHERE 条件

2.TRUNCATE TABLE

・DDL语言

・数据不可以恢复

・先将整个表删除，再重新创建

・删除速度比delete快。

语法： TRUNCATE TABLE 表名 或者 TRUNCATE 表名

面试题2：

delete from person; 和 drop table person; 有什么区别：

delete from person; 把person表中的所有数据全部删除，但是person的表还存在。还可以向表中继续添加数据。

drop table person; 把person数据表从数据库中删除。表已经不存在，不能再向表中添加数据。

2.3可视化工具的介绍

在dos窗口中使用sql语句操作mysql数据库相对来说，太麻烦了，我们在实际开发中不会使用dos窗口来操作的，一定要使用可视化工具来操作mysql数据库，而会有多种可视化工具来操作mysql数据库，比如：Navicat、SQLyog等。我们这里使用Navicat可视化工具。

1）解压和安装：

双击 安装软件一路下一步安装(注意要记住自己的安装路径!!!)

安装完成后 双击注册机

找到Nacicat下的启动软件

点击打开后,显示OK 激活成功!

2）如果是第一次使用，会弹出一个提示框,开机检查更新,我们不需要更细。

3）关闭之后,点击软件左上角的连接按钮,选中MySql：

然后就会弹出如下界面：

4）简单熟悉下可视化工具。

5）一般的可视化工具，都能从界面看到 数据库-》数据库表-》表数据。

6）在可视化工具中可以编写sql语句运行

查询结果:

7）在可视化工具中给内容添加注释快捷键是：ctrl+/

Ctrl + shift + r 运行选中的

这样我们就简单熟悉了下可视化工具，接下来我们所有的操作都会在该可视化工具中进行操作。

2.4查询

数据表记录的查询

在企业中数据库运用最多的就是数据库中对于表记录的查询。

准备工作:

创建数据库，并向数据库表中插入数据。

create database day02;

use day02;

create table student(

id int primary key auto_increment,

name varchar(32) not null,

age int ,

gender varchar(10) not null,

score double not null,

birthday date

);

insert into student (id,name,age,gender,score,birthday) values(null,‘zhangsan’,23,‘male’,98.99,‘1990-09-09’);

insert into student (id,name,age,gender,score,birthday) values(null,‘lisi’,23,‘男’,56.99,‘1990-02-09’);

insert into student (id,name,age,gender,score,birthday) values(null,‘王五’,24,‘女’,75.99,‘1988-01-01’);

insert into student (id,name,age,gender,score,birthday) values(null,‘赵六’,25,‘男’,80.99,‘1980-11-12’);

insert into student (id,name,age,gender,score,birthday) values(null,‘柳岩’,null,‘女’,84,null);

语法一：查询数据库中的某张表的所有数据

语法：select * from 表名;

表示查询表的所有信息。会把表的所有的列，所有的行都列出来。

说明：

1）select 表示选择的意思；

2）* 表示所有的数据；

3）from 表示从哪个表中选择；

需求：查询学生的所有信息。

查询操作和结果：

4.1.2语法二: 查询某张表中指定的列

语法： select 列名,列名… from 表名;

说明：显示指定列数据。列出所有行信息。

需求：查询所有学生的姓名和成绩。

4.1.3语法三：按条件查询

语法：select 列名,列名… from 表名 where 条件;

说明：查找符合where条件的数据。

作用：过滤，只有符合条件的，才去列出相应的信息。

需求：查询表中年龄大于等于24岁的学生信息。

4.1.4运算符：

1)>(大于) <(小于) >=(大于等于) <=(小于等于) =(相等) <>或者!= (不相等)

需求：查询年龄不是25岁的学生。

2）

and 逻辑与 ，多个条件同时成立。&&

or 逻辑或 ，多个条件任意一个成立。||

not 逻辑非 ，相反的意思。!

需求一：查询年龄>23，并且成绩>80的同学信息。

需求二：查询成绩在80~100(包含)之间的学生信息。

3）区间：between … and … 在两者之间取值。 格式：列名 between 开始值 and 结束值;

注意：前面那个数即开始值要比后面那个数即结束值要小 。

例如：between 70 and 80 在70和80之间。

等价于 >=70 and <=80

age >=24 and age<=50 等价于 age between 24 and 50

需求：查询成绩在80~100(包含)之间的学生信息。

4）in(值1,值2,值3) 在指定值中任意取。

举例： in(70,80,90) 值可以是70、80或者90

使用格式：

where 列名 in (值，值，值…);

等价于

where 列名=值 or 列名=值 or 列名=值 …

需求：查询年龄为18，23，25的同学信息。

5）模糊查询：like ‘模糊查询部分’

表达式有两个占位符：

A: % 表示零或任意多个字符 ;

B: _ 任意单个字符;

例如： name like ‘张%’ 所有姓张学员。

name like ‘%张%’ 只要有张就可以。

name like ‘张_’ 所有姓张名字为两个字学员。

name like ‘张’ 只有中间是张，前面一个字，后面一个字。

需求：查询所有含有 岩 的学生信息。

注意：含有某个值: %xx%

6）is null 判断该列值是否为空。

sql中对null的判断，不能写 = null 在sql 中 null = null 结果不成立。 正确格式：is null

不是空 ： is not null。

需求1：查询没有生日学员信息。就是生日是null。

需求2：查询有年龄学员信息。就是年龄不是空。

语法四 ：过滤重复数据：

查询排重：select distinct 列名 from 表名 [where 条件];

说明：distinct： 去重复。显示distinct后面列的内容，并且过滤掉重复的数据。

需求：显示不重复的年龄。

语法五 ：对查询的结果进行排序：

使用order by 子句排序查询结果。

语法：select * from 表名 order by 列名1 asc|desc，列名2（asc|desc），列名3（asc|desc） 。。。。。;

按照列名1，列名2，列名3 进行排序输出。

asc是升序排列，desc是降序排列。默认是asc升序。

按照第一列进行排序，如果第一列相同，按照第二列在进行排序。

注意： order by a asc,b asc

a, b

1, 2

1, 3

4, 5

3, 1

结果：

1,2

1,3

3,1

4,5

需求：

1、对成绩排序后输出。

2、对年龄排序按从高到低（降序）的顺序输出。

3、对学生年龄按照降序排序，年龄相同按照成绩降序。

1、对成绩排序后输出。

2、对年龄排序按从高到低（降序）的顺序输出。

3、对学生年龄按照降序排序，年龄相同按照成绩降序。

4.1.7别名：可以对查询出来的列名起别名

语法：select 列名 as 别名,列名 as 别名,列名 as 别名… from 表名 where 条件;

注意：在使用别名的时候，as 关键字可以省略。

需求1、给年龄和分数起别名。

需求2、省略关键as 再次查询。

4.1.8面试题：

1、select age , score from student;

2、select age score from student;

上述SQL语句有什么区别？

第一个Sql语句查询结果如下图所示：

查询结果有两列，分别是age和score。

第二个Sql语句查询结果如下图所示：

查询结果只有一列，其实显示的数据是age列，数据上面的名字是别名score。