一、 数据库介绍

• 14245353-06424593b151beb4.png
  

  数据库与excel的区别

• 相同点：
  都可以存储数据
  都可以对数据进行增删改查操作
  都可以对数据进行筛选和统计

• 不同点：
  excel的主要服务对象是人，数据库的主要服务对象是程序；
  excel是本地存储，同时只能有一个人使用，数据库可以多人进行远程访问；

• 常见的数据库类型

• 14245353-0c03eeccf84354ad.png

二、 sql语句

• 条件运算符

  
  14245353-3953516aa36d59f8.png

• 分隔符

  
  14245353-b5a50f5a36b6484f.png

• 通配符

  
  14245353-e44cc8294847e031.png

• 查询语句

  
  14245353-b8a19d788753b263.png

练习题

1.年龄大于20
SELECT * FROM gy_user WHERE age>20;
2.年龄小于20
SELECT * FROM gy_user WHERE age<20;
3.年龄大于等于30
SELECT * FROM gy_user WHERE age>=30;
4.年龄小于等于30
SELECT * FROM gy_user WHERE age<=30;
5.年龄不等于 35
SELECT * FROM gy_user WHERE age<>35;
6.姓名为周仕平
SELECT * FROM gy_user WHERE realname = '周仕平';
7.姓名为周XX
SELECT * FROM gy_user WHERE realname LIKE '周%';
8.查询用户id在100-300之间的用户
SELECT * FROM gy_user WHERE sno BETWEEN 100 AND 300;
9.查询用户年龄在25-35之间的用户
SELECT * FROM gy_user WHERE age BETWEEN 25 AND 35;
10.查询用户姓名包含 令 或者 伟
SELECT * FROM gy_user WHERE realname LIKE '%令%' OR realname LIKE '%伟%';
11.查询用户地址包含闵行区并且编号小于400
SELECT * FROM gy_user WHERE address LIKE '%闵行区%' AND sno <400;
12 查询姓名包含王或年龄在 20-30区间并且班级为 1的
SELECT * FROM gy_user WHERE realname LIKE '%王%' AND (age BETWEEN 20 AND 30) ;
13 查询班级编号为1或学历为1的
SELECT * FROM gy_user WHERE class_type = 1 OR education = 1;
14.查询年龄大于18并且班级类型为2
SELECT * FROM gy_user WHERE age > 18 AND class_type = 2;
15.查询编号不等于100的或者年龄小于35的
SELECT * FROM gy_user WHERE sno !=100 OR age<35;
16.查询学历大于1的学生信息 只显示 编号 ,真实姓名
SELECT sno,realname FROM gy_user WHERE education > 1;
17.查询年龄小于40并且手机号的号段为138的用户,只显示用户名,地址
SELECT sname,address FROM gy_userWHERE age< 40 AND phone LIKE '138%';
18.查询手机号尾数为1,并且地址在上海的学生用户名，编号，地址
SELECT sname,sno,address FROM gy_user WHERE phone LIKE '%1' AND address LIKE '%上海%';
19.查询用户名包含字母为 l 的并且班级类型不为1
SELECT * FROM gy_user WHERE sname LIKE '%l%' AND class_type != 1;
20.查询用户名为5位字符或者真实姓名位5位字符的用户编号,姓名,地址
SELECT sno,realname,address FROM gy_user WHERE sname LIKE '_____' OR realname LIKE '_____';
1 SELECT * FROM gy_user WHERE realname = '马冬梅';
2 SELECT * FROM gy_user WHERE sno>60;
3 SELECT * FROM gy_user WHERE age>20;
4 SELECT * FROM gy_user WHERE realname LIKE '钱璟'；;
5 SELECT * FROM gy_user WHERE sex = '女';
6 SELECT * FROM gy_user WHERE sno<110;
7 SELECT * FROM gy_user WHERE !=23;
8 SELECT sno,sname WHERE gy_user WHERE !='张子栋';
9 SELECT sno,sname,address WHERE gy_user WHERE address='重庆';
10 SELECT * FROM gy_user WHERE realname LIKE '%马%';
11 SELECT * FROM gy_user WHERE realname LIKE '吴%';
12 SELECT * FROM gy_user WHERE realname LIKE '%李%';
13 SELECT * FROM gy_user WHERE realname LIKE '%静%';
14 SELECT * FROM gy_user WHERE realname LIKE '';
15 SELECT * FROM gy_user WHERE realname LIKE '%民';
16 SELECT * FROM gy_user WHERE realname LIKE '春%';
17 SELECT * FROM gy_user WHERE realname LIKE '__';
18 SELECT * FROM gy_user WHERE realname LIKE '马%梅';
19 SELECT * FROM gy_user WHERE realname LIKE '马东%';
20 SELECT * FROM gy_user WHERE realname LIKE '%冬梅';

21 SELECT * FROM gy_user WHERE id = 77 OR id=88 OR id=99 OR id =111;
SELECT * FROM gy_user WHERE id IN (77,88,99,111);

22 SELECT * FROM gy_user WHERE age = 23 OR age =30;
SELECT * FROM gy_user WHERE age IN (23,30);

23 SELECT * FROM gy_user WHERE address = '安徽' AND address ='重庆';
SELECT * FROM gy_user WHERE address IN ('安徽','重庆');

23 SELECT * FROM gy_user WHERE sname NOT LIKE ('%康健');
24 SELECT * FROM gy_user WHERE sno NOT IN (05,10,15,20);
25 SELECT * FROM gy_user WHERE teacher NOT LIKE ('李%');
26 SELECT * FROM gy_user WHERE teacher NOT IN ('李素云','马超','刘程旭');
27 SELECT * FROM gy_user WHERE NAME LIKE'%上海%' OR address LIKE '%上海%';
28 SELECT * FROM gy_user WHERE NAME LIKE '%阿里%' AND address LIKE '%上海金融中心%';
29 SELECT * FROM gy_user WHERE address IN ('重庆','成都','上海');

微信图片_20181024184923.png