创建数据库

image.pngimage.png
utf8_bin区分大小写(也就是查询的话Tom和tom不相同)，而utf8_general_ci不区分大小写。
image.pngimage.png 调用语句的方法：选中语句，点击左上角的“选中查询”。注意创建完成后还需要刷新一下左边才能看到，可以一次选中多条语句。
image.png
创建数据库时设置参数：FILENAME是数据库路径，SIZE是初始大小，MAXSIZE是最大大小，FILEGROWTH是每次增长大小。
image.png

查看、删除数据库

image.pngimage.png
注：删除数据库前一定要三思！

  1. # 演示数据库的操作
  2. # 使用指令创建数据库
  3. CREATE DATABASE shang_01;
  4. CREATE DATABASE shang_02;
  6. #查看当前数据库服务器中的所有数据库
  7. SHOW DATABASES
  8. #查看前面创建数据库的定义信息
  9. SHOW CREATE DATABASE shang_01
  10. #在创建数据库表时，为了规避关键字，我们需要加上反引号(即使不是关键字也可以加)
  11. CREATE DATABASE `CREATE`
  12. #删除库
  13. DROP DATABASE shang_03

备份、恢复数据库

image.png

备份数据库

image.png
image.png

备份表

image.png
可以接很多表，注意没有-B。

恢复数据库

第一种方法：source 文件名.sql （source不区分大小写），必须在sql管理终端中运行。参考第八条。
image.pngimage.png
第二种方法：直接把beifen.sql文件复制到SQLyog，然后整体执行。
image.pngimage.png

表的各种操作

创建表

image.png
注：在SQLyog中创建表非常容易，这里仅展示DOS的（可以在SQLyog的代码区使用）。只有在右括号后面结束后加分号，其它用逗号隔开，即使是最后的数据也不加分号。

  1. CREATE TABLE `user`(
  2.     id INT,
  3.     `name` VARCHAR(255),
  4.     `password` VARCHAR(255),
  5.     `birthday` DATE)
  6.     CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

创建表的时候还可以标明主键和外键等等：
image.png

删除表

操作很简单：DROP TABLE 表名

  1. DROP TABLE tableName

修改表(Alter语句等)

image.pngimage.png

插入列

  1. ALTER TABLE acc 
  2.     ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME
  3. #插入一个image列，varchar类型，不为空，初始值为''，在resume后面

显示表结构

  1. DESC acc #显示表结构，可以查看表的所有列

修改列的属性

注意有两种方法，MODIFY不能修改名字，但CHANGE可以

  1. ALTER TABLE acc
  2.     MODIFY image VARCHAR(60) NOT NULL DEFAULT '' #修改image列，使其长度为60
  4. ALTER TABLE acc
  5.     CHANGE `resume` `rrr` VARCHAR(64) NOT NULL DEFAULT '' 
  6.     #修改列的属性：名字 长度 是否为空 初始值

删除列

  1. ALTER TABLE acc DROP image  #删除acc表的image列

修改表名

  1. RENAME TABLE acc TO qqq 
  2. #修改表名为class DESC qqq
  3. #表名修改后，对应的操作也需要修改

修改表的字符集

  1. ALTER TABLE acc CHARACTER SET utf8 #修改表的字符集为utf8

表复制

  1. #把emp表复制到my_tab01中
  3. INSERT INTO my_tab01
  4.     (id,`name`,sal,job,deptno)
  5.     SELECT empno,ename,sal,job,deptno 
  6.     FROM emp
  1. #自我复制
  2. INSERT INTO my_tab01
  3.     SELECT * FROM mytab_01

表去重

  1. #创建一个临时表和要去重的表结构相同
  2. CREATE TABLE my_tmp LIKE my_tab02 
  4. #把tab02的记录通过distinct处理后 复制到tmp
  5. INSERT INTO my_tmp
  6.     SELECT DISTINCT * FROM my_tab02
  8. #清除掉tab02的记录
  9. DELETE FROM my_tab02
  11. #把tmp表的记录复制到tab02
  12. INSERT INTO my_tab02
  13.     SELECT * FROM my_tmp
  15. #drop掉临时表tmp
  16. DROP TABLE my_tmp;

Insert语句

image.png

  1. INSERT INTO `goods` (id, goods_name, price)      #对应列
  2.     VALUES(1,'华为手机',2000);    #对应值

注意！一定不要带括号。
细节说明：
1. 插入的数据应与字段的数据类型相同。 但是如果能相互转化那就是可以的。比如： ‘abc’ 放到int里是不行的（’a’也不行），但是’30’放到int里就是可以的。
2. 数据的长度应在列的规定范围内。比如：不能把一个长度为80的字符串加入到长度为40的列中
3. 字符和日期型数据应包含在单引号中。
4. 列可以插入null，前提是该字段允许为空(不写 NOT NULL，参见插入列)
5. insert into 列名 values (),(),()… 这样的形式可以添加多条记录。

  1. INSERT INTO goods (id,goods_name,price)     
  2.     VALUES(60,'三星手机',2300),(60,'小米手机',3000)

6. 如果是给表中的所有字段添加数据，可以不写()里的字段名称。

  1. INSERT INTO goods     
  2.     VALUES(60,'三星手机',2300),(60,'小米手机',3000)

7. 当不给某个字段值时，如果有默认值就会添加(也就是 default 关键字对应的值)，否则会报错。

Update语句

image.png
image.png

  1. #把所有员工薪水修改为5000
  2. UPDATE employee SET salary = 5000
  4. #将姓名为 小天才 的员工薪水修改为3000元
  5. UPDATE employee 
  6.     SET salary = 3000
  7.     WHERE user_name = '小天才'
  9. #在原有基础上增加薪水
  10. UPDATE employee 
  11.     SET salary = salary + 1000
  12.     WHERE user_name = '小天才'
  14. #修改多个列
  15. UPDATE employee
  16.     SET salary = salary + 1000 , job = '策划'
  17.     WHERE user_name = '小天才'

Delete语句

image.png
image.png

  1. #删除表中所有名称为 '一方通行' 的记录
  2. DELETE FROM employee
  3.     WHERE user_name = '一方通行'
  5. #删除表中所有记录    
  6. DELETE FROM employee

Select语句

image.png

  1. #查询表中学生的所有信息
  2. SELECT * FROM student;
  3. #查询表中所有学生的姓名和对应的英语成绩
  4. SELECT `name`,english FROM student;
  5. #过滤表中重复数据 distinct 注：要查询的记录，每个字段都相同，才会去重
  6. SELECT DISTINCT english FROM student;
  8. SELECT DISTINCT `name`,english FROM student;
  9. #比如 english相同，但name不同，这样的就不会去重

image.pngimage.png

  1. #统计每个学生的总分
  2. SELECT `name`,(chinese + english + math) FROM student;
  4. #假设要显示总分+10的内容
  5. SELECT `name`,(chinese + english + math + 10) FROM student;
  7. #使用别名表示学生分数
  8. SELECT `name`,(chinese + english + math) AS total_score FROM student;

where语句

image.png

  1. #查询姓赵的同学的成绩
  2. SELECT * FROM student
  3.     WHERE `name` LIKE '赵%'; #这里 %表示名字以赵开头的都行('赵'也行)
  5. #查询总分大于200的所有同学
  6. SELECT * FROM student
  7.     WHERE (chinese + english + math) > 200;
  9. #查询math大于60，并且id大于4的学生成绩
  10. SELECT * FROM student
  11.     WHERE math>60 AND id>4;
  13. #查询英语分数在 80-90 之间的同学
  14. SELECT * FROM student
  15.     WHERE english BETWEEN 80 AND 90;  #注意都是闭区间
  17. #查询数学分数为89，90，91的同学
  18. SELECT * FROM student
  19.     WHERE math IN (89,90,91);
  21. --查询当前学生的年龄
  22. SELECT Sname,2020-year(Birthday) AS Age
  23. FROM Students; --Birthday是Date类型，year就是获取对应年的函数

注：where不能和计量函数一起使用，如果一起使用的话要用 having（having和where可以替换）

order by语句

image.png

  1. #对数学成绩排序后输出(升序) 啥也不用写，默认的
  2. SELECT * FROM student
  3.     ORDER BY math;
  5. #对总分按从高到低的顺序输出 (降序)  
  6. SELECT `name`,(chinese + math + english) AS total_score FROM student
  7.     ORDER BY total_score DESC;
  9. #对姓赵的学生成绩(总分)排序输出
  10. SELECT `name`,(chinese + english + math) AS total_score FROM student
  11.     WHERE `name` LIKE '赵%'
  12.     ORDER BY total_score;
  14. #当然也可以这样写，但是这样写不显示总分
  15. SELECT * FROM student
  16.     WHERE `name` LIKE '赵%'
  17.     ORDER BY (chinese + english + math);

select增强

  1. #使用where语句 ：查找 1992.1.1后入职的员工
  3. SELECT * FROM emp
  4.     WHERE hiredate>'1992-01-01';
  1. #使用 like操作符
  2. #    %: 表示0到多个任意字符
  3. #    _: 表示单个任意字符
  5. #显示首字母为S的员工姓名和工资
  6. SELECT ename,sal FROM emp
  7.     WHERE ename LIKE 'S%';
  9. #显示第三个字符为大写H的员工的姓名和工资
  10. SELECT ename,sal FROM emp
  11.     WHERE ename LIKE '__H%'; #前面两个_ 代表两个任意字符
  13. -- 查询以 "C_" 开头的课程
  14. -- ESCAPE标识哪个符号为转义符号，此时 '_' 就为本身的意义，因为它位于转义符号之后
  15. -- 但是 % 仍然不表示本意，因为 \ 的作用效果只有它后面的一个字符
  16. SELECT * FROM Courses WHERE Cname LIKE 'C\_%' ESCAPE '\';
  1. #显示没有上级的雇员
  2. SELECT * FROM emp
  3.     WHERE mgr IS NULL;
  1. #使用order by 子句
  2. #按照工资从低到高的顺序，显示雇员信息
  3. SELECT * FROM emp
  4.     ORDER BY sal;
  6. #按照部门号升序，但雇员的工资降序排列，显示雇员信息
  7. SELECT * FROM emp
  8.     ORDER BY deptno ASC,sal DESC;

分组与过滤(group by)

image.png

  1. #deptno为部门号
  2. #显示每个部门的平均工资和最高工资
  3. SELECT AVG(sal),MAX(sal), deptno
  4.     FROM emp GROUP BY deptno;
  1. #显示每个部门的每种岗位的平均工资和最低工资 (多种分组方式)
  2. SELECT AVG(sal),MAX(sal),deptno,job
  3.     FROM emp 
  4.     GROUP BY deptno,job;
  1. #显示平均工资低于2000的部门号和它的平均工资 (过滤)
  2. #分析: 1. 显示各个部门的平均工资和部门号
  3. #            2. 在1结果的基础上进行过滤
  5. SELECT AVG(sal),deptno
  6.     FROM emp GROUP BY depto
  7.         HAVING AVG(sal) < 2000; #用having过滤,因为where无法和合计函数一起使用
  9. #也可以使用别名        
  10. SELECT AVG(sal) AS sal_avg,deptno
  11.     FROM emp GROUP BY depto
  12.         HAVING sal_avg < 2000;

group by增强 

  1. # 1. 显示每种岗位的雇员总数，平均工资
  2. SELECT COUNT(*),AVG(sal),job
  3.     FROM emp
  4.     GROUP BY job;
  6. # 2.显示没有获得补助的雇员数
  7. SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
  8.     FROM emp;
  10. #获得补助的员工 comm不是null，而没有获得补助的员工为null，因此用if语句
  11. #如果comm是null就返回1
  13. #第二种写法
  14. SELECT COUNT(*),COUNT(*)-COUNT(comm)
  15.     FROM emp;
  17. #显示管理者的总人数(去重)
  18. SELECT COUNT(DISTINCT mgr)
  19.     FROM emp;
  21. #显示雇员工资的最大差额
  22. SELECT MAX(sal) - MIN(sal)
  23.     FROM emp;

分页查询

image.png

  1. #每页显示三行记录
  2. #第一页
  3. SELECT * FROM emp
  4.     ORDER BY empno
  5.     LIMIT 0,3; //表示从 第一行开始取，取出3行
  7. #第二页
  8. SELECT * FROM emp
  9.     ORDER BY empno
  10.     LIMIT 3,3; //从第四行开始取，取出3行

查询加强

image.pngimage.png