34、MySQL学习教程与学习记录

最新推荐文章于 2024-05-19 18:23:31 发布

Bao@Ting

最新推荐文章于 2024-05-19 18:23:31 发布

阅读量472

点赞数 1

分类专栏： MySQl 文章标签： mysql 数据库 sql 1024程序员节

本文链接：https://blog.csdn.net/BaoTTing/article/details/120337182

版权

MySQl 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、MySQL安装教程

1、版本选择和MySQL安装包说明

基于市面上版本对于5.5、5.6、5.7、5.8都有涉及，选取一个折中的版本5.7.35进行安装，因为5.8的版本太新，可能会不太稳定，5.5的虽然稳定，但对某些新属性可能不支持。所以我选择了MySQL5.7.35进行安装和学习。(大家可根据自己的需求自行选择适合自己的版本安装)

官网下载界面提供了不同平台和不同安装方式的下载。基本如以下两种安装包:
mysql-5.7.35-winx64.zip
mysql-installer-community-5.7.35.0.msi
ZIP类型的安装包由用户解压后放在某目录下，然后手动配置参数、系统变量等等。
MSI类型的安装包在用户点击之后，由界面安装，跟平时安装软件一样。

为了详细了解MySQL的参数配置方法，我这里选择ZIP包的形式安装。

2、ZIP安装包和MSI安装包的官网下载

MySQL :: MySQL Community Downloads

2.1 下载ZIP安装包

2.2 下载MSI安装包

3、ZIP包安装教程和参数配置

3.1 解压 mysql-5.7.35-winx64.zip 到自己要安装的目录下

3.2 在安装目录下创建my.ini配置文件(文件内容如下)

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\software\MySQL\MySQL_IDE
# 设置mysql数据库的数据的存放目录
datadir=D:\\software\MySQL\MySQL_IDE\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

3.3 配置系统环境变量(把2.2中的bin目录添加到环境变量)

3.4 用CMD命令安装mysql（右击以管理员身份启动CMD控制台）

依次执行如下命令

1、mysqld --initialize-insecure --user=mysql
2、mysqld --install
3、net start mysql     #启动mysql服务
4、mysql -u root -p    #登录mysql,第一次未设密码，直接回车即可
5、直接按回车
6、alter user user() identified by "123456";  #设置登录密码为123456,注意分号；

说明：
    第1、2步属于不设置登录密码的安装方式，所以第6步设置了登录密码

至此、，MySQL安装完成。

4、MSI包安装教程和参数配置

后续抽空补上。

5、Ububtu上安装MySQL

后续抽空补上。

二、SQL语言学习前的准备

1、数据库的图形化客户端介绍

DBeavr、SQLyog、Navicat Premium 12、MySQL Workbench

以上工具按热度排名，其中第二个仅适用MySQL，第四个工具是MySQL官方的。

2、创建表及其表的结构参数(MySQL Workbench 8.0 CE)

三、数据库的SQL语言

1、基础语句

USE myemployees;      #进入myemployees这个数据库,类似cd。
SELECT DATABASE();    #查看当前在哪个数据库中
SELECT VERSION();     #查看数据库的版本
SHOW DATABASES;       #查看所有数据库
SHOW TABLES FROM myemployees; #查看指定数据库所拥有的表。

#表:表<结构>的增删改查
CREATE TABLE newtable(#增:创建一个名为newtable的表
id INT(8),
姓名 VARCHAR(20)
);
DESC newtable;        #查:显示表的结构部,即只显示列的结构。
DROP TABLE newtable;  #删:删除名为newtable的表。

ALTER TABLE newtable CHANGE id idx INT(8);#改:修改列名。
RENAME TABLE newtable TO mytable;         #改:重命名表。

INSERT INTO newtable(id,姓名) VALUES(1,'张三'); #添:往表中添加数据
INSERT INTO newtable(id,姓名) VALUES(2,'赵四'); #添:往表中添加数据
DELETE FROM newtable WHERE id=1;                 #删:删除newtable中id=1的一行数据。
DELETE FROM newtable WHERE id=2;                 #删:删除newtable中id=2的一行数据

SELECT * FROM newtable;                      #显示表中的数据
UPDATE newtable SET namex='ztt' WHERE idx=2; #根据一个数据更新另一个同行数据

2、基础查询

/*
语法:
select 查询列表 from 表名;

类似于：`employees`System.out.println(打印东西)

特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
#1.查询表中的单个字段

SELECT last_name FROM employees; #或 SELECT `last_name` FROM employees; <- 可用于与关键字的区分

#2.查询表中的多个字段

SELECT last_name,salary,email FROM employees;

#3.查询表中的所有字段

SELECT * FROM employees;

#4.查询常量值
SELECT 100;

SELECT 'john';

#5.查询表达式
SELECT 100*99; #作简单计算

#6.查询函数
SELECT VERSION();

#7.为字段取别名
SELECT 100*99 AS 结果; #给查询结果取一个别名,提高可读性,如果查询字段有重名,使用别名可以区分开
SELECT last_name AS 姓,first_name AS 名 FROM employees;
SELECT last_name 姓,first_name 名 FROM employees;
#案例
SELECT salary AS "out put" FROM employees; #别名有空格和关键字时,用双引号

#8.去重

#案例：查询员工表中涉及到的所有的部门编号
SELECT department_id FROM employees; #非去重
SELECT DISTINCT department_id FROM employees; #去重

/*
mysql中的+号仅仅只有一个功能,那就是运算符

SELECT 100+90;   两个都是数值,做加法运算
SELECT '123'+90; 字符型+数字型,尝试将字符转换为数字,转换成功=数值,转换失败=0,即0+90
select 'john'+90;
select null+10;  只要其中一方为null,结果都为null
*/

#9.+号的作用
#案例：查询员工名和姓连接成一个字段,并显示为->姓名
SELECT "last_name" + "first_name" AS 姓名 FROM employees; #错误

SELECT CONCAT('a','b','c') AS 结果;

SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;     #拼接只能用函数
SELECT CONCAT(`last_name`,`first_name`) AS 姓名 FROM employees; #拼接只能用函数
#当某个元素为null时,CONCAT()连接的结果就会变成null

#10.IFNULL()函数的用法

IFNULL(`commission_pct`,0);#if果查询到的结果有NULL,则返回0
#举例:显示出表employees中的全部列,各个列之间用逗号连接,列头显示成OUT_PUT

SELECT CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',`commission_pct`) AS out_put
FROM   employees;   #结果全为null

SELECT CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(`commission_pct`,0)) AS out_put
FROM   employees;   #结果全为null

3、条件查询

/*
在基础查询的基础上添加条件

语法：(找表->筛选->查询)
SELECT 
	查询列表
FOM
        表名
WHERE
	筛选条件;
分类：
	一、按条件表达式筛选
	    条件运算符：> < = != <> >= <=
	二、按逻辑表达式筛选
	    逻辑运算符：&& || ！ and or not
	三、模糊查询
	    like 
	    between and
	    in
	    is null
*/
#11.按条件表达式查询

#案例1：查询工资>12000的员工信息
SELECT *
FROM   employees
WHERE  salary>12000;
#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT `last_name`,`first_name`,`department_id`
FROM   employees
WHERE  department_id!=90; #<>也是不等号,或 department_id<>90;
#12.按逻辑表达式查询
    # &&和and ->两个条件都为true,结果为true。
    # ||和or  ->只要有一个为true,结果为true。
    #  !和not ->取反。
    #案例:查询工资在10000-20000之间的员工名和奖金。
    SELECT first_name,salary,commission_pct
    FROM   employees
    WHERE  salary>=10000&&salary<=20000;
    #案例:查询部门编号不是在90到110之间,或工资高于15000的员工信息。
    SELECT *
    FROM   employees
    WHERE  #department_id<90 or department_id>110 or salary>15000;
           NOT(department_id>=90 AND department_id<=110) OR salary>15000;
#13.模糊查询
  #<1、like关键字的使用 ：一般和通配符( % _ )搭配使用  
    #案例1:查询员工名中包含字符a的员工信息
    SELECT *
    FROM   employees
    WHERE  last_name LIKE '%a%';# % <-代表通配符,类似linux的-> *a*
    #案例2:查询员工名中第三个字符为n,第五个字符为l的员工名和薪水
    SELECT last_name,salary
    FROM   employees
    WHERE  last_name LIKE '__n_l%';
    #案例3:查询员工名中第二个字符为_的员工名(注意查询的字符是通配符)
    SELECT last_name
    FROM   employees
    WHERE  last_name LIKE '_\_%';#其中\为转义字符    
    #或：类如下你可以指定转义字符是谁     
    SELECT last_name
    FROM   employees
    WHERE  last_name LIKE '_$_%' ESCAPE '$';#指定$为转义字符
  #<2、between and 关键字的使用
    #案例1:查询员工编号在100-120之间的员工信息
    SELECT *
    FROM  employees
    WHERE employee_id>=100 AND employee_id<=120;#按逻辑表达式   
    #或：使用 between and 关键字
    SELECT *
    FROM   employees
    WHERE  employee_id BETWEEN 100 AND 120;#按逻辑表达式(包含临界值) 
  #<3、in 关键字的使用
    #注1：判断某字段的值是否属于in列表中的某一项,比使用OR提高了语言简洁度)
    #注2：in列表的值的类型必须统一或者兼容，且不支持通配符
    #案例1:查询员工的工种编号(job_id)是IT_PROG、AD_VP、AD_PRES中的一个 的员工名和工种
    SELECT last_name,job_id
    FROM   employees
    WHERE  job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES';#逻辑表达式的做法
    #或：使用 in 关键字
    SELECT last_name,job_id
    FROM   employees
    WHERE  job_id IN('IT_PROG','AD_VP','AD_PRES');#逻辑表达式的做法
  #<4、is null和is not null 关键字的使用
    #注1：=或<>不能用于判断null值,而 is null和is not null可以判断NULL值
    #注2：is null和is not null只能判断null,而不能判断其它类型
    #案例1:查询没有奖金的员工名和奖金率
    SELECT last_name,commission_pct
    FROM   employees
    WHERE  commission_pct = NULL; #这种写法是不行的，因为 = 号不支持NULL的
    #--------得用 IS NULL
    SELECT last_name,commission_pct
    FROM   employees
    WHERE  commission_pct IS NULL;
    #案例2:查询有奖金的员工名和奖金率
    SELECT last_name,commission_pct
    FROM   employees
    WHERE  commission_pct IS NOT NULL;
  #<5、<=> 安全等于关键字的使用(和=不一样,其不仅支持NULL,还支持=所支持的类型)
    #注：其只有一个小缺点，就是在可读方面容易和<=,>
    #案例1:查询没有奖金的员工名和奖金率 
    SELECT last_name,commission_pct
    FROM   employees
    WHERE  commission_pct <=> NULL;
    #案例2:查询工资为12000的员工信息
    SELECT *
    FROM   employees
    WHERE  salary <=> 12000;
    #IS NULL:仅仅可以判断NULL值,可读性高,可读性高(建议用)
    #<=>    :仅可以判断NULL值,又可以判断普通的数值,可读性低

4、排序查询

 /*语法：
	SELECT 查询列表
    FROM   表
    【WHERE 筛选条件】
    ORDER BY 排序列表 【ASC|DESC】
    特点：ASC代表升序，DESC代表降序，如果不写，默认是升序
          ORDER BY子句可以支持单个字段、多个字段、表达式、函数、别名alter
          ORDER BY子句一般是放在查询语句的最后面，limit子句除外
  */
  #<1、查询员工信息，要求工资升序或者降序
   SELECT * FROM myemployees.employees ORDER BY salary ASC;  #升序查询
   SELECT * FROM myemployees.employees ORDER BY salary DESC; #降序查询
  #<2、案例2：查询部门编号>=90的员工信息,按入职时间的先后进行排序
   SELECT * 
   FROM myemployees.employees
   WHERE department_id>=90
   ORDER BY hiredate ASC;
  #<3、案例3:按年薪的高低显示员工的信息和年薪【按升序】
   SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
   FROM myemployees.employees
   ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
   #-----------------------------------------------------#
   SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
   FROM myemployees.employees
   ORDER BY 年薪 DESC; #ORDER BY 支持别名
  #<4、案例4:按姓名的长度显示员工的姓名和工资  
   SELECT LENGTH(last_name) AS 字节长度,last_name,salary
   FROM myemployees.employees
   ORDER BY 字节长度 ASC;
  #<5、案例5:查询员工信息，要求先按工资升序，工资相等的再按员工编号降序
   SELECT *
   FROM myemployees.employees
   ORDER BY salary ASC,employee_id DESC;
  #<6、案例6:查询员工中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序alter
   SELECT *
   FROM myemployees.employees
   WHERE email LIKE '%e%'
   ORDER BY LENGTH(email) DESC,department_id ASC;
  #<7、案例7:将员工姓名按首字母排序，并写出姓名的长度
   SELECT last_name,LENGTH(last_name) 长度,SUBSTR(last_name,1,1) AS 首字符
   FROM myemployees.employees
   ORDER BY 首字符;
  #<8、案例8:将员工姓名按全称排列，并写出姓名首字母，姓名长度
   SELECT last_name,SUBSTR(last_name,1,1) AS 首字符,LENGTH(last_name) 长度
   FROM myemployees.employees
   ORDER BY last_name;

5、常见函数

   /*概念：类似java的方法，将一组逻辑语句封装在方法体中，对外暴漏方法名
     好处：隐藏了实现细节、提高了代码的重用性
     调用：SELECT 函数名(实参) 【FROM 表】; 用到了表的字段时要加FROM 表，没用到可不加。
     分类：单行函数(concat、length、ifnull等)(字符函数、数学函数、日期函数、其他函数、流程控制函数等)
           分组函数(做统计使用)、使用方法参考第8小节
  */  
  #<1、字符函数
   #length 获取当前参数值的字节个数
      SHOW VARIABLES LIKE '%char%';#显示当前字符集
      SELECT LENGTH('john');
      SELECT LENGTH('张三a');
   #concat 拼接字符串
      SELECT concat(last_name,'_',first_name) AS 姓名 FROM myemployees.employees;
   #upper、lower 变大写/变小写
      SELECT UPPER('john');
      SELECT LOWER('john');
      #示例：将姓名变大写，名变小写，然后拼接
      SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名 FROM myemployees.employees;
   #substr、substring  截取字符串
      SELECT substr('杭州电子科技大学',3) AS 结果; #mysql的索引是从1开始的，截取从索引3开始后面的字符。
      SELECT substr('杭州电子科技大学',3,4) AS 结果; #从索引3开始，截取4个字符长度(注不是字节)。    
      #示例：将姓名中首字符大写，其他字符小写然后用_拼接，显示出来
      SELECT CONCAT(UPPER(substr(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS 结果 FROM myemployees.employees;
   #instr 返回子串第一次出现的索引，找不到则返回0
      SELECT INSTR('杭州电子科技大学','电子科技') AS 结果;
   #trim 去掉字符串首尾的字符
      SELECT TRIM('    电子科技    ') AS 结果;
      SELECT TRIM('a' FROM 'aaa电子aa科技aaaa') AS 结果;
   #lpad 用指定的字符实现左填充，并指定填充后整体的长度
      SELECT LPAD('电子科技大学',10,'*') AS 结果;
   #rpad 用指定的字符实现左填充，并指定填充后整体的长度
      SELECT RPAD('电子科技大学',10,'as') AS 结果;
   #replace 替换
      SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS 结果;
	  SELECT REPLACE('张无忌爱上了周芷若周芷若','周芷若','赵敏') AS 结果;     
  #<2、数学函数
   #round 四舍五入
      SELECT ROUND(1.652);   #默认保留整数
      SELECT ROUND(1.452);   #默认保留整数
	  SELECT ROUND(-1.652);  #默认保留整数(先取绝对值再四舍五入)
      SELECT ROUND(-1.452);  #默认保留整数
	  SELECT ROUND(1.452,1); #保留1位小数
      SELECT ROUND(1.455,2); #保留2位小数
   #ceil 向上取整,返回>=该参数的最小整数
      SELECT CEIL(1.02);
      SELECT CEIL(-1.02);      
    #FLOOR 向下取整,返回<=该参数的最小整数
      SELECT FLOOR(1.02);     
	  SELECT FLOOR(-1.02);   
	#truncate 截断
	  SELECT TRUNCATE(1.66); #保留整数
	  SELECT TRUNCATE(1.06,1); #保留一位小数
	#mod 取余数,结果的符号和第一个参数(被余数)的符号相同  
      #取余运算实质：a%b = a - a/b*b。
      SELECT MOD(10,3);   #结果为1
	  SELECT 10%3;
      SELECT MOD(-10,3);  #结果为-1
      SELECT MOD(10,-3);  #结果为1
	  SELECT MOD(-10,-3); #结果为-1
  #<3、日期函数
	#now 返回当前系统日期+时间
	  SELECT NOW() AS 日期时间;
	#curdate 返回当前系统日期，不包含时间
      SELECT CURDATE()  AS 日期;
	#curtime 返回当前系统时间，不包含时日期
      SELECT CURTIME() AS 时间;
    #获取指定的部分，如年、月、日、时、分、秒
      SELECT YEAR(NOW()) AS 年;
	  SELECT MONTH(NOW()) AS 月;
      SELECT MONTHNAME(NOW()) AS 月; #返回月的英文表示
 	  SELECT DAY(NOW()) AS 日; 
 	  SELECT HOUR(NOW()) AS 时; 
	  SELECT MINUTE(NOW()) AS 分; 
	  SELECT SECOND(NOW()) AS 秒; 
      SELECT YEAR('1998-1-1') AS 年;
      SELECT YEAR(hiredate) AS 年 FROM myemployees.employees; #函数参数中有字段，要用FEOM指明是哪个表中的字段
    /*
     * 格式符      功能              | 格式符      功能              
     *   %Y     四位的年份           |   %H      小时(24小时制)
     *   %y     二位的年份           |   %h      小时(12小时制)
     *   %m     月份(01、02...11,12) |   %i      分钟(00、01...59)
     *   %c     月份(1、2...11,12)   |   %s      秒(00、01...59)
     *   %d     日(01、02...)        |
     */
    #str_to_date 将不同格式的字符串转换成日期(注意日期是年月日的形式，如：1992-4-3)
      SELECT str_to_date('1998-3-2','%Y-%c-%d') AS 结果; 
	  SELECT STR_TO_DATE('4-3 1992','%c-%d %Y') AS 结果; #把不规则的字符串格式化为年月日的形式 
	  #示例：查询入职日期为1992-4-3的员工信息
      SELECT *
      FROM myemployees.employees
      WHERE hiredate = '1992-4-3';
      
	  SELECT *
      FROM myemployees.employees
      WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');     
    #date_format 将日期转换成字符
	  SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 结果;
      #示例：查询有奖金的员工名和入职日期(xx月/xx日 xx年)
      SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 结果
      FROM myemployees.employees
      WHERE commission_pct IS NOT NULL;

6、其他函数

SELECT USER();        #查看当前用户
SELECT VERSION();     #查看数据库的版本
SELECT DATABASE();    #查看当前在哪个数据库中

7、流程控制函数

  #<1、if函数：if else 的结果
	SELECT IF(10>5,'大','小') AS 结果;  #参数一成立，返回第二参数，否则返回第三参数
    
    SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金 呵呵','有奖金 嘻嘻') AS 备注
	FROM myemployees.employees;
  #<2、case函数
    #使用一：switch case 的效果(但和java使用的关键字不同)
	  /*
        case 要判断的字段或表达式
        when 常量1 then 要显示的值1语句1;  #是语句加分号，是值不加分号
        when 常量1 then 要显示的值2语句2;
        .......
        else 要显示的值n或语句n;
        end
	   */
    #案例：查询员工的工资，要求:
    /* 部门号=30，显示的工资为1.1倍
     * 部门号=40，显示的工资为1.2倍
     * 部门号=50，显示的工资为1.3倍
     * 其他的部门，显示的工资为原工资
     */
     SELECT salary AS 原始工资,department_id,
     case department_id
     WHEN 30 THEN salary*1.1
     WHEN 40 THEN salary*1.2
	 WHEN 50 THEN salary*1.3
     ELSE salary
     END AS 新工资
     FROM myemployees.employees;
    #使用二：类似与多重 if
	 /*与使用一不同的是case后是否有语句
	   case
       when 条件1 then 要显示的值1 或 语句1
	   when 条件1 then 要显示的值2 或 语句2
	   .......
       else 要显示的值n或语句n
       end
      */
    #案例：查询员工的工资的情况,如果:
    /* 如果工资>20000，显示A级别
     * 如果工资>15000，显示B级别
     * 如果工资>10000，显示C级别
     * 否则，显示D级别
     */
     SELECT last_name,salary,
     case
     WHEN salary>20000 THEN 'A'
     WHEN salary>15000 THEN 'B'
     WHEN salary>10000 THEN 'C'
     ELSE 'D'
     END AS 工资级别
     FROM myemployees.employees;

8、分组函数

/* 分组函数：返回值是单个值
 * 功能：用作统计使用，又称为聚合函数或统计函数或组函数
 * 分类：sum、avg、max、min、count
 * 注意：单行函数的返回值是一列值
 * 特点：
 *     1、sum、avg一般用于处理数值类型
 *        max、min、count可以处理任何类型
 *     2、sum、avg、max、min、count都忽略null
 *     3、可以和distinct搭配实现去重的运算
 *     4、COUNT函数的详细介绍，一般COUNT(*)统计行数
 *     5、和分组函数一同查询的字段要求是group by 后的字段
 */
  #1、简单实用
    SELECT SUM(salary) AS 员工工资总值 FROM myemployees.employees;   #sum 求和 
    SELECT AVG(salary) AS 员工工资平均值 FROM myemployees.employees; #avg 求平均
    SELECT MAX(salary) AS 员工工资最大值 FROM myemployees.employees; #max 求最大值
    SELECT MIN(salary) AS 员工工资最小值 FROM myemployees.employees; #min 求最小值
    SELECT COUNT(salary) AS 字段值非空的个数 FROM myemployees.employees; #max 求字段值的个数
    SELECT SUM(salary) AS 员工工资总值,AVG(salary) AS 员工工资平均值,MAX(salary) AS 员工工资最大值,MIN(salary) AS 员工工资最小值,COUNT(salary) AS 字段值非空的个数 
    FROM myemployees.employees; 
  #2、和distinct搭配使用->去重
    SELECT SUM(DISTINCT salary),COUNT(DISTINCT salary),sum(salary),COUNT(salary) 
    FROM myemployees.employees; #注意sum、avg、max、min、count都忽略NULL
  #3、COUNT函数的详细介绍  
    SELECT COUNT(salary) FROM myemployees.employees; #统计salary列非NULL元素个数
    SELECT COUNT(*) FROM myemployees.employees;      #统计表的行数,任何行只要有一个非NULL,都能统计上
    SELECT COUNT(1) FROM myemployees.employees;      #统计表的行数,等于加了一列1,并统计这列的元素(即使某行全为NULL,也能统计上)
    #效率：MYISAM存储引擎下，COUNT(*)的效率高;INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多
    #案例：查询部门编号为90的员工个数
    SELECT COUNT(*) 
    FROM myemployees.employees
    WHERE department_id = 90;

9、分组查询

更新日期：2021-10-20

执行如下命令，便可将所笔记 - 所用 - 数据导入到数据库中

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;

USE `myemployees`;

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) DEFAULT NULL,
  `email` varchar(25) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `job_id` varchar(10) DEFAULT NULL,
  `salary` double(10,2) DEFAULT NULL,
  `commission_pct` double(4,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id_fk` (`department_id`),
  KEY `job_id_fk` (`job_id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;

/*Data for the table `employees` */

insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL,
  `job_title` varchar(35) DEFAULT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

/*Data for the table `jobs` */

insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);

/*Table structure for table `locations` */

DROP TABLE IF EXISTS `locations`;

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(40) DEFAULT NULL,
  `postal_code` varchar(12) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `state_province` varchar(25) DEFAULT NULL,
  `country_id` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;

/*Data for the table `locations` */

insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Bao@Ting

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
34、MySQL学习教程与学习记录

#进阶1:基础查询/*语法:select 查询列表 from 表名;类似于：`employees`System.out.println(打印东西)特点：1、查询列表可以是：表中的字段、常量值、表达式、函数2、查询的结果是一个虚拟的表格*/USE myemployees;DESC `departments`; #显示表的结构部#1.查询表中的单个字段SELECT last_name FROM employees; #或 SELECT `last_name` FROM emp.
复制链接

扫一扫