设为首页 收藏本站
查看: 678|回复: 0

[经验分享] 操作MySQL数据库

[复制链接]
发表于 2018-10-7 13:18:19 | 显示全部楼层 |阅读模式
  SQL是什么?
  SQL是结构化查询语言,这是一种计算机语言,用于存储,操纵和检索存储在关系数据库中的数据。
  SQL是关系数据库系统的标准语言。所有关系型数据库管理系统,如MySQL, MS Access, Oracle, Sybase, Informix, postgres 和SQL Server使用SQL作为标准数据库语言。
  此外,它们也使用不同的方言,如:

  •   MS SQL Server 使用 T-SQL,
  •   Oracle 使用 PL/SQL,
  •   MS Access 的SQL版本叫 JET SQL (本地格式) 等
  为什么使用SQL?

  •   允许用户访问在关系数据库管理系统的数据。
  •   让用户来描述数据。
  •   允许用户定义数据库中的数据和处理数据。
  •   允许使用SQL模块,库和预编译器的其他语言中嵌入。
  •   允许用户创建和删除数据库和表。
  •   允许用户创建视图,存储过程,函数在数据库中。
  •   允许用户设置表,过程和视图的权限
  历史:

  •   1970 -- Dr. Edgar F. "Ted"  IBM的科德被称为关系数据库之父,是他描述了数据库的关系模型。
  •   1974 -- 结构化查询语言出现。
  •   1978 -- IBM合作开发Codd的想法并发布了名为System/R的产品。
  •   1986 -- IBM开发了关系型数据库的第一台样机,并通过ANSI标准化。第一个关系型数据库是由关系型软件及其后来成为甲骨文发布。
  SQL处理:
  当你对任何RDBMS执行SQL命令,系统决定开展您的要求的最佳途径和SQL引擎计算出如何解析任务。
  有包括在过程中的各种组件。这些组件查询调度,优化引擎,经典查询引擎和SQL查询引擎等等。经典查询引擎处理所有非SQL查询,但SQL查询引擎不会处理逻辑文件。
  以下是显示SQL架构一个简单的图表:
sql命令
  标准的SQL命令进行互动使用在关系型数据库有:CREATE, SELECT, INSERT, UPDATE, DELETE 和 DROP。这些命令可分为基于其性质组。
  DDL - 数据定义语言
命令描述CREATE创建一个新的表,表的视图,或者在数据库中的对象ALTER修改现有的数据库对象,例如一个表DROP删除整个表,数据库中的表或其他对象或视图  DML - 数据操纵语言
命令描述SELECT从一个或多个表中检索特定的记录INSERT创建记录UPDATE修改记录DELETE删除记录  DCL - 数据控制语言
命令描述GRANT授予用户权限REVOKE收回用户授予的权限  MySQL数据类型
  1、整型
MySQL数据类型含义(有符号)tinyint(m)1个字节  范围(-128~127)smallint(m)2个字节  范围(-32768~32767)mediumint(m)3个字节  范围(-8388608~8388607)int(m)4个字节  范围(-2147483648~2147483647)bigint(m)8个字节  范围(+-9.22*10的18次方)  取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。
  int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个m有什么用。
  2、浮点型(float和double)
MySQL数据类型含义float(m,d)单精度浮点型    8位精度(4字节)     m总个数,d小数位double(m,d)双精度浮点型    16位精度(8字节)    m总个数,d小数位  设一个字段定义为float(5,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。
  3、定点数
  浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
  decimal(m,d) 参数m

  使用MySQL>  假设您希望在任务表中插入新行时,task_id列的值会自动增加1。那么可以使用ALTER TABLE语句将task_id列的属性设置为AUTO_INCREMENT,如下所示:
ALTER TABLE tasks  
CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;
  可以通过在tasks表中插入一些行数据来验证更改。
INSERT INTO tasks(subject,  
                  start_date,
  
                  end_date,
  
   description)
  
VALUES('Learn MySQL ALTER TABLE',
  
       Now(),
  
       Now(),
  
      'Practicing MySQL ALTER TABLE statement');
  

  
INSERT INTO tasks(subject,
  
                  start_date,
  
                  end_date,
  
           description)
  
VALUES('Learn MySQL CREATE TABLE',
  
       Now(),
  
       Now(),
  
      'Practicing MySQL CREATE TABLE statement');
  您可以查询数据以查看每次插入新行时task_id列的值是否增加1:
SELECT  
    task_id, description
  
FROM
  
    tasks;
  使用MySQL>
  由于新的业务需求,需要添加一个名为complete的新列,以便在任务表中存储每个任务的完成百分比。 在这种情况下,您可以使用ALTER TABLE将新列添加到tasks表中,如下所示:
ALTER TABLE tasks  
ADD COLUMN complete DECIMAL(2,1) NULL
  
AFTER description;
  使用MySQL>
  假设您不想将任务的描述存储在tasks表中了,并且必须将其删除。 以下语句允许您删除tasks表的description列:
ALTER TABLE tasks  
DROP COLUMN description;
  使用MySQL>
  可以使用ALTER TABLE语句重命名表。请注意,在重命名表之前,应该认真考虑以了解更改是否影响数据库和应用程序层,不要因为重命名表之后,应用程序因未找到数据库表而出错。
  以下语句将tasks表重命名为work_items表:
ALTER TABLE tasks  
RENAME TO work_items;
表记录操作
  1.简单的MySQL INSERT语句
  MySQL INSERT语句允许您将一行或多行插入到表中。下面说明了INSERT语句的语法:
INSERT INTO table(column1,column2...)  
VALUES (value1,value2,...);
  首先,在INSERT INTO子句之后,在括号内指定表名和逗号分隔列的列表。
  然后,将括号内的相应列的逗号分隔值放在VALUES关键字之后。
  在执行插入语句前,需要具有执行INSERT语句的INSERT权限。
  让我们创建一个名为tasks的新表来练习INSERT语句,参考以下创建语句 -
USE testdb;  
CREATE TABLE IF NOT EXISTS tasks (
  
    task_id INT(11) AUTO_INCREMENT,
  
    subject VARCHAR(45) DEFAULT NULL,
  
    start_date DATE DEFAULT NULL,
  
    end_date DATE DEFAULT NULL,
  
    description VARCHAR(200) DEFAULT NULL,
  
    PRIMARY KEY (task_id)
  
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  例如,如果要将任务插入到tasts表中,则使用INSERT语句如下:
INSERT INTO tasks(subject,start_date,end_date,description)  
VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');
  执行该语句后,MySQL返回一条消息以通知受影响的行数。 在这种情况下,有一行受到影响。
  现在使用以下语句查询 tasks 中的数据,如下所示 -
SELECT * FROM tasks;  执行上面查询语句,得到以下结果
+---------+--------------------+------------+------------+------------------+  
| task_id | subject            | start_date | end_date   | description      |
  
+---------+--------------------+------------+------------+------------------+
  
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
  
+---------+--------------------+------------+------------+------------------+
  
1 row in set
  2. MySQL INSERT - 插入多行
  想要在表中一次插入多行,可以使用具有以下语法的INSERT语句:
INSERT INTO table(column1,column2...)  
VALUES (value1,value2,...),
  
       (value1,value2,...),
  
...;
  在这种形式中,每行的值列表用逗号分隔。 例如,要将多行插入到tasks表中,请使用以下语句:
INSERT INTO tasks(subject,start_date,end_date,description)  
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
  
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
  
       ('任务-3','2017-01-01','2017-01-02','Description 3');
  执行上面语句后,返回
Query OK, 3 rows affected  
Records: 3  Duplicates: 0  Warnings: 0
  现在查询tasks表中的数据,如下所示
select * from tasks;  执行上面查询语句,得到以下结果
  如果为表中的所有列指定相应列的值,则可以忽略INSERT语句中的列列表,如下所示:
INSERT INTO table  
VALUES (value1,value2,...);
  
或者
  
INSERT INTO table
  
VALUES (value1,value2,...),
  
       (value1,value2,...),
  
...;
  请注意,不必为自动递增列(例如taskid列)指定值,因为MySQL会自动为自动递增列生成值。
  3. 具有SELECT子句的MySQL INSERT
  在MySQL中,可以使用SELECT语句返回的列和值来填充INSERT语句的值。 此功能非常方便,因为您可以使用INSERT和SELECT子句完全或部分复制表,如下所示:
INSERT INTO table_1  
SELECT c1, c2, FROM table_2;
  假设要将tasks表复制到tasks_bak表。
  首先,通过复制tasks表的结构,创建一个名为tasks_bak的新表,如下所示:
CREATE TABLE tasks_bak LIKE tasks;  第二步,使用以下INSERT语句将tasks表中的数据插入tasks_bak表:
INSERT INTO tasks_bak  
SELECT * FROM tasks;
  第三步,检查tasks_bak表中的数据,看看是否真正从tasks表复制完成了。
mysql> select * from tasks;  
+---------+--------------------+------------+------------+------------------+
  
| task_id | subject            | start_date | end_date   | description      |
  
+---------+--------------------+------------+------------+------------------+
  
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
  
|       2 | 任务-1             | 2017-01-01 | 2017-01-02 | Description 1    |
  
|       3 | 任务-2             | 2017-01-01 | 2017-01-02 | Description 2    |
  
|       4 | 任务-3             | 2017-01-01 | 2017-01-02 | Description 3    |
  
+---------+--------------------+------------+------------+------------------+
  
4 rows in set
  4. MySQL INSERT与ON DUPLICATE KEY UPDATE
  如果新行违反主键(PRIMARY KEY)或UNIQUE约束,MySQL会发生错误。 例如,如果执行以下语句:
INSERT INTO tasks(task_id,subject,start_date,end_date,description)  
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority');
  MySQL很不高兴,并向你扔来一个错误消息:
Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec  因为表中的主键task_id列已经有一个值为 4 的行了,所以该语句违反了PRIMARY KEY约束。
  但是,如果在INSERT语句中指定ON DUPLICATE KEY UPDATE选项,MySQL将插入新行或使用新值更新原行记录。
  例如,以下语句使用新的task_id和subject来更新task_id为4的行。
INSERT INTO tasks(task_id,subject,start_date,end_date,description)  
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
  
ON DUPLICATE KEY UPDATE
  
   task_id = task_id + 1,
  
   subject = 'Test ON DUPLICATE KEY UPDATE';
  执行上面语句后,MySQL发出消息说2行受影响。现在,我们来看看tasks表中的数据:
mysql> select * from tasks;  
+---------+------------------------------+------------+------------+------------------+
  
| task_id | subject                      | start_date | end_date   | description      |
  
+---------+------------------------------+------------+------------+------------------+
  
|       1 | Learn MySQL INSERT           | 2017-07-21 | 2017-07-22 | Start learning.. |
  
|       2 | 任务-1                       | 2017-01-01 | 2017-01-02 | Description 1    |
  
|       3 | 任务-2                       | 2017-01-01 | 2017-01-02 | Description 2    |
  
|       5 | Test ON DUPLICATE KEY UPDATE | 2017-01-01 | 2017-01-02 | Description 3    |
  
+---------+------------------------------+------------+------------+------------------+
  
4 rows in set
  新行没有被插入,但是更新了task_id值为4的行。上面的INSERT ON DUPLICATE KEY UPDATE语句等效于以下UPDATE语句:
UPDATE tasks  
SET
  
    task_id = task_id + 1,
  
    subject = 'Test ON DUPLICATE KEY UPDATE'
  
WHERE
  
    task_id = 4;
  修改表数据
  1. MySQL UPDATE语句简介
  我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行,一组行或所有行的列值。
  下面说明了MySQL UPDATE语句的语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_name  
SET
  
    column_name1 = expr1,
  
    column_name2 = expr2,
  
    ...
  
WHERE
  
    condition;
  在上面UPDATE语句中:
  首先,在UPDATE关键字后面指定要更新数据的表名。
  其次,SET子句指定要修改的列和新值。要更新多个列,请使用以逗号分隔的列表。以字面值,表达式或子查询的形式在每列的赋值中来提供要设置的值。
  第三,使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句,则UPDATE语句将更新表中的所有行。
  请注意,WHERE子句非常重要,所以不应该忘记指定更新的条件。 有时,您可能只想改变一行; 但是,可能会忘记写上WHERE子句,导致意外更新表中的所有行。
  MySQL在UPDATE语句中支持两个修饰符。
  LOW_PRIORITY修饰符指示UPDATE语句延迟更新,直到没有从表中读取数据的连接。 LOW_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM,MERGE,MEMORY)生效。
  即使发生错误,IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。
  2. MySQL UPDATE示例
  我们使用MySQL示例数据库(yiibaidb)中的一些表来练习使用UPDATE语句。
  2.1 MySQL UPDATE一个单列示例
  在这个例子中,我们将把 Mary Patterson 的电子邮件更新为新的电子邮件mary.patterso@yiibai.com。
  首先,为了确保更新电子邮件成功,使用以下SELECT语句从employees表查询Mary的电子邮件:
SELECT  
    firstname, lastname, email
  
FROM
  
    employees
  
WHERE
  
    employeeNumber = 1056;
  执行上面的查询语句,得到以下结果
+-----------+-----------+----------------------+  
| firstname | lastname  | email                |
  
+-----------+-----------+----------------------+
  
| Mary      | Patterson | mpatterso@yiibai.com |
  
+-----------+-----------+----------------------+
  
1 row in set
  第二步,使用UPDATE语句将Mary的电子邮件更新为新的电子邮件:mary.new@yiibai.com,如下查询所示:
UPDATE employees  
SET
  
    email = 'mary.new@yiibai.com'
  
WHERE
  
    employeeNumber = 1056;
  因为上面语句中,只想更新一行,所以使用WHERE子句来指定更新的是员工编号1056的行。SET子句将电子邮件列的值设置为新的电子邮件。
  第三,再次执行SELECT语句来验证更改。
SELECT  
    firstname, lastname, email
  
FROM
  
    employees
  
WHERE
  
    employeeNumber = 1056;
  再次执行上面的查询语句,得到以下结果
+-----------+-----------+---------------------+  
| firstname | lastname  | email               |
  
+-----------+-----------+---------------------+
  
| Mary      | Patterson | mary.new@yiibai.com |
  
+-----------+-----------+---------------------+
  
1 row in set
  2.2 MySQL UPDATE多列
  要更新多列中的值,需要在SET子句中指定分配。例如,以下语句更新了员工编号1056的姓氏和电子邮件列:
UPDATE employees  
SET
  
    lastname = 'Hill',
  
    email = 'mary.hill@yiibai.com'
  
WHERE
  
    employeeNumber = 1056;
  在执行上面语句之后,查询员工编号为:1056的记录,如下所示 -
+-----------+----------+----------------------+  
| firstname | lastname | email                |
  
+-----------+----------+----------------------+
  
| Mary      | Hill     | mary.hill@yiibai.com |
  
+-----------+----------+----------------------+
  
1 row in set
  2.3 使用SELECT语句的MySQL UPDATE示例
  可以使用SELECT语句查询来自其他表的数据来提供给SET子句的值。
  例如,在customers表中,有些客户没有任何销售代表。 salesRepEmployeeNumber列的值为NULL,如下所示:
mysql> SELECT  
    customername, salesRepEmployeeNumber
  
FROM
  
    customers
  
WHERE
  
    salesRepEmployeeNumber IS NULL;
  
+--------------------------------+------------------------+
  
| customername                   | salesRepEmployeeNumber |
  
+--------------------------------+------------------------+
  
| Havel & Zbyszek Co             | NULL                   |
  
| Porto Imports Co.              | NULL                   |
  
| Asian Shopping Network, Co     | NULL                   |
  
| Natrlich Autos                 | NULL                   |
  
| ANG Resellers                  | NULL                   |
  
| Messner Shopping Network       | NULL                   |
  
| Franken Gifts, Co              | NULL                   |
  
| BG&E Collectables              | NULL                   |
  
| Schuyler Imports               | NULL                   |
  
| Der Hund Imports               | NULL                   |
  
| Cramer Spezialitten, Ltd       | NULL                   |
  
| Asian Treasures, Inc.          | NULL                   |
  
| SAR Distributors, Co           | NULL                   |
  
| Kommission Auto                | NULL                   |
  
| Lisboa Souveniers, Inc         | NULL                   |
  
| Stuttgart Collectable Exchange | NULL                   |
  
| Feuer Online Stores, Inc       | NULL                   |
  
| Warburg Exchange               | NULL                   |
  
| Anton Designs, Ltd.            | NULL                   |
  
| Mit Vergngen & Co.             | NULL                   |
  
| Kremlin Collectables, Co.      | NULL                   |
  
| Raanan Stores, Inc             | NULL                   |
  
+--------------------------------+------------------------+
  
22 rows in set
  我们可以为这些客户提供销售代表和更新。
  为此,需要从employees表中随机选择一个职位为Sales Rep的雇员,并将其更新到employees表中。
  下面的查询语句是从employees表中随机选择一个其职位是Sales Rep的员工。
SELECT  
    employeeNumber
  
FROM
  
    employees
  
WHERE
  
    jobtitle = 'Sales Rep'
  
ORDER BY RAND()
  
LIMIT 1;
  要更新customers表中的销售代表员工编号(employeeNumber)列,我们将上面的查询放在UPDATE语句的SET子句中,如下所示:
UPDATE customers  
SET
  
    salesRepEmployeeNumber = (SELECT
  
            employeeNumber
  
        FROM
  
            employees
  
        WHERE
  
            jobtitle = 'Sales Rep'
  
        LIMIT 1)
  
WHERE
  
    salesRepEmployeeNumber IS NULL;
  如果在执行上面更新语句后,查询employees表中的数据,将看到每个客户都有一个销售代表。 换句话说,以下查询不返回任何行数据。
SELECT  
     salesRepEmployeeNumber
  
FROM
  
    customers
  
WHERE
  
    salesRepEmployeeNumber IS NULL;
  删除表数据
  1. MySQL DELETE语句介绍
  要从表中删除数据,请使用MySQL DELETE语句。下面说明了DELETE语句的语法:
DELETE FROM table_name  
WHERE condition;
  在上面查询语句中
  首先,指定删除数据的表(table_name)。
  其次,使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件,这些行记录将被删除。
  请注意,WHERE子句是可选的。如果省略WHERE子句,DELETE语句将删除表中的所有行。
  除了从表中删除数据外,DELETE语句返回删除的行数。
  要使用单个DELETE语句从多个表中删除数据,请阅读下一个教程中将介绍的DELETE JOIN语句。
  要删除表中的所有行,而不需要知道删除了多少行,那么应该使用TRUNCATE TABLE语句来获得更好的执行性能。
  对于具有外键约束的表,当从父表中删除行记录时,子表中的行记录将通过使用ON DELETE CASCADE选项自动删除。
  2. MySQL DELETE的例子
  我们将使用示例数据库(yiibaidb)中的employees表进行演示。
+-------------+--------------+------+-----+---------+----------------+  
| Field       | Type         | Null | Key | Default | Extra          |
  
+-------------+--------------+------+-----+---------+----------------+
  
| emp_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
  
| emp_name    | varchar(255) | NO   |     | NULL    |                |
  
| performance | int(11)      | YES  | MUL | NULL    |                |
  
| salary      | float        | YES  |     | NULL    |                |
  
+-------------+--------------+------+-----+---------+----------------+
  
4 rows in set
  请注意,一旦删除数据,它就会永远消失。 因此,在执行DELETE语句之前,应该先备份数据库,以防万一要找回删除过的数据。
  假设要删除officeNumber为4的员工,则使用DELETE语句与WHERE子句作为以下查询:
DELETE FROM employees  
WHERE
  
    officeCode = 4;
  要删除employees表中的所有行,请使用不带WHERE子句的DELETE语句,如下所示:
DELETE FROM employees;  在执行上面查询语句后,employees表中的所有行都被删除。
  MySQL DELETE和LIMIT子句
  如果要限制要删除的行数,则使用LIMIT子句,如下所示:
DELETE FROM table  
LIMIT row_count;
  请注意,表中的行顺序未指定,因此,当您使用LIMIT子句时,应始终使用ORDER BY子句,不然删除的记录可能不是你所预期的那样。
DELETE FROM table_name  
ORDER BY c1, c2, ...
  
LIMIT row_count;
  考虑在示例数据库(yiibaidb)中的customers表,其表结构如下:
mysql> desc customers;  
+------------------------+---------------+------+-----+---------+-------+
  
| Field                  | Type          | Null | Key | Default | Extra |
  
+------------------------+---------------+------+-----+---------+-------+
  
| customerNumber         | int(11)       | NO   | PRI | NULL    |       |
  
| customerName           | varchar(50)   | NO   |     | NULL    |       |
  
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
  
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
  
| phone                  | varchar(50)   | NO   |     | NULL    |       |
  
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
  
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
  
| city                   | varchar(50)   | NO   |     | NULL    |       |
  
| state                  | varchar(50)   | YES  |     | NULL    |       |
  
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
  
| country                | varchar(50)   | NO   |     | NULL    |       |
  
| salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       |
  
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
  
+------------------------+---------------+------+-----+---------+-------+
  
13 rows in set
  例如,以下语句按客户名称按字母排序客户,并删除前10个客户:
DELETE FROM customers  
ORDER BY customerName
  
LIMIT 10;
  类似地,以下DELETE语句选择法国(France)的客户,按升序按信用额度(creditLimit)进行排序,并删除前5个客户:
DELETE FROM customers  
WHERE country = 'France'
  
ORDER BY creditLimit
  
LIMIT 5;
  类似地,以下DELETE语句选择法国(France)的客户,按升序按信用额度(creditLimit)进行排序,并删除前5个客户:
DELETE FROM customers  
WHERE country = 'France'
  
ORDER BY creditLimit
  
LIMIT 5;
  查询表记录(select)
  查询语法:
SELECT *|field1,filed2 ...   FROM tab_name  
               WHERE 条件
  
               GROUP BY field
  
               HAVING 筛选
  
               ORDER BY field
  
               LIMIT 限制条数
  
Mysql在执行sql语句时的执行顺序:
  
             -- from  where  select  group by  having order by
  准备数据
CREATE TABLE emp(  
    id       INT PRIMARY KEY AUTO_INCREMENT,
  
    name     VARCHAR(20),
  
    gender   ENUM("male","female","other"),
  
    age      TINYINT,
  
    dep      VARCHAR(20),
  
    city     VARCHAR(20),
  
   salary    DOUBLE(7,2)
  
);
  

  

  
INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
  
                ("yuan","male",24,"教学部","河北省",8000),
  
                ("egon","male",34,"保安部","山东省",8000),
  
                ("alex","male",28,"保洁部","山东省",10000),
  
                ("景丽阳","female",22,"教学部","北京",9000),
  
                ("张三", "male",24,"教学部","河北省",6000),
  
                ("李四", "male",32,"保安部","北京",12000),
  
                ("王五", "male",38,"教学部","河北省",7000),
  
                ("赵六", "male",19,"保安部","河北省",9000),
  
                ("猪七", "female",24,"保洁部","北京",9000);
  

  
SELECT  * FROM emp;
mysql> SELECT  * FROM emp;  
+----+-----------+--------+------+-----------+-----------+----------+
  
| id | name      | gender | age  | dep       | city      | salary   |
  
+----+-----------+--------+------+-----------+-----------+----------+
  
|  1 | yuan      | male   |   24 | 教学部    | 河北省    |  8000.00 |
  
|  2 | egon      | male   |   34 | 保安部    | 山东省    |  8000.00 |
  
|  3 | alex      | male   |   28 | 保洁部    | 山东省    | 10000.00 |
  
|  4 | 景丽阳    | female |   22 | 教学部    | 北京      |  9000.00 |
  
|  5 | 张三      | male   |   24 | 教学部    | 河北省    |  6000.00 |
  
|  6 | 李四      | male   |   32 | 保安部    | 北京      | 12000.00 |
  
|  7 | 王五      | male   |   38 | 教学部    | 河北省    |  7000.00 |
  
|  8 | 赵六      | male   |   19 | 保安部    | 河北省    |  9000.00 |
  
|  9 | 猪七      | female |   24 | 保洁部    | 北京      |  9000.00 |
  
+----+-----------+--------+------+-----------+-----------+----------+
  
rows in set (0.00 sec)
  where子句: 过滤查询
where字句中可以使用  
    比较运算符:
  
        > < >= 24;  查询教学部的男老师信息
SELECT * FROM emp WHERE dep="教学部" AND gender="male";  order:排序
  按指定的列进行,排序的列即可是表中的列名,也可以是select语句后指定的别名。
  语法:
select *|field1,field2... from tab_name order by field [Asc|Desc]  Asc 升序、Desc 降序,其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
  示例:
  按年龄从高到低进行排序
SELECT * FROM emp ORDER BY age DESC ;  按工资从低到高进行排序
SELECT * FROM emp ORDER BY salary;  group by:分组查询(*****)
  GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。
  语法:
SELECT column_name, function(column_name)  
      FROM table_name
  
      WHERE column_name operator value
  
      GROUP BY column_name;
  示例:
  -- 查询男女员工各有多少人
SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender;  -- 查询各个部门的人数
SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep;  -- 查询每个部门最大的年龄
SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep;  -- 查询每个部门年龄最大的员工姓名
SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep);  -- 查询每个部门的平均工资
SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep;  --  查询教学部的员工最高工资:
  SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部";
  -- 查询平均薪水超过8000的部门
  SELECT dep,AVG(salary) FROM  emp GROUP BY dep HAVING avg(salary)>8000;
  --  查询每个组的员工姓名
  SELECT dep,group_concat(name) FROM emp GROUP BY dep;
  -- 查询公司一共有多少员工(可以将所有记录看成一个组)
  SELECT COUNT(*) 员工总人数 FROM emp;
  -- KEY: 查询条件中的每个后的词就是分组的字段
  limit记录条数限制
SELECT * from ExamResult limit 1;  
SELECT * from ExamResult limit 2,5;        --  跳过前两条显示接下来的五条纪录
  
SELECT * from ExamResult limit 2,2;
  正则表达式
SELECT * FROM employee WHERE emp_name REGEXP '^yu';  
SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
  
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
  多表查询
  创建表
CREATE TABLE emp(  
    id INT PRIMARY KEY AUTO_INCREMENT,
  
    name VARCHAR(20),
  
    salary DOUBLE(7,2),
  
    dep_id INT
  
);
  
INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2),
  
                                            ("李四",12000,1),
  
                                            ("王五",5000,2),
  
                                            ("赵六",8000,3),
  
                                            ("猪七",9000,1),
  
                                            ("周八",7000,4),
  
                                            ("蔡九",7000,2);
  
CREATE TABLE dep(
  
      id INT PRIMARY KEY AUTO_INCREMENT,
  
      name VARCHAR(20)
  
);
  
INSERT INTO dep (name) VALUES ("教学部"),
  
                              ("销售部"),
  
                              ("人事部");
  mysql> select * from emp;
  +----+--------+----------+--------+

  |>  +----+--------+----------+--------+
  |  1 | 张三   |  8000.00 |      2 |
  |  2 | 李四   | 12000.00 |      1 |
  |  3 | 王五   |  5000.00 |      2 |
  |  4 | 赵六   |  8000.00 |      3 |
  |  5 | 猪七   |  9000.00 |      1 |
  |  6 | 周八   |  7000.00 |      4 |
  |  7 | 蔡九   |  7000.00 |      2 |
  +----+--------+----------+--------+
  7 rows in set (0.00 sec)
  mysql> select * from dep;
  +----+-----------+

  |>  +----+-----------+
  |  1 | 教学部    |
  |  2 | 销售部    |
  |  3 | 人事部    |
  +----+-----------+
  3 rows in set (0.00 sec)
  1.笛卡尔积查询
  select * from emp,dep;
  mysql> select * from emp,dep;
  +----+--------+----------+--------+----+-----------+

  |>  +----+--------+----------+--------+----+-----------+
  |  1 | 张三   |  8000.00 |      2 |  1 | 教学部    |
  |  1 | 张三   |  8000.00 |      2 |  2 | 销售部    |
  |  1 | 张三   |  8000.00 |      2 |  3 | 人事部    |
  |  2 | 李四   | 12000.00 |      1 |  1 | 教学部    |
  |  2 | 李四   | 12000.00 |      1 |  2 | 销售部    |
  |  2 | 李四   | 12000.00 |      1 |  3 | 人事部    |
  |  3 | 王五   |  5000.00 |      2 |  1 | 教学部    |
  |  3 | 王五   |  5000.00 |      2 |  2 | 销售部    |
  |  3 | 王五   |  5000.00 |      2 |  3 | 人事部    |
  |  4 | 赵六   |  8000.00 |      3 |  1 | 教学部    |
  |  4 | 赵六   |  8000.00 |      3 |  2 | 销售部    |
  |  4 | 赵六   |  8000.00 |      3 |  3 | 人事部    |
  |  5 | 猪七   |  9000.00 |      1 |  1 | 教学部    |
  |  5 | 猪七   |  9000.00 |      1 |  2 | 销售部    |
  |  5 | 猪七   |  9000.00 |      1 |  3 | 人事部    |
  |  6 | 周八   |  7000.00 |      4 |  1 | 教学部    |
  |  6 | 周八   |  7000.00 |      4 |  2 | 销售部    |
  |  6 | 周八   |  7000.00 |      4 |  3 | 人事部    |
  |  7 | 蔡九   |  7000.00 |      2 |  1 | 教学部    |
  |  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部    |
  |  7 | 蔡九   |  7000.00 |      2 |  3 | 人事部    |
  +----+--------+----------+--------+----+-----------+
  21 rows in set (0.00 sec)
  2、内连接
  查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
  SELECT * FROM emp,dep WHERE emp.dep_id=dep.id;
  OR
  SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id;
  查询结果:
  +----+--------+----------+--------+----+-----------+

  |>  +----+--------+----------+--------+----+-----------+
  |  1 | 张三   |  8000.00 |      2 |  2 | 销售部     |
  |  2 | 李四   | 12000.00 |      1 |  1 | 教学部     |
  |  3 | 王五   |  5000.00 |      2 |  2 | 销售部     |
  |  4 | 赵六   |  8000.00 |      3 |  3 | 人事部     |
  |  5 | 猪七   |  9000.00 |      1 |  1 | 教学部     |
  |  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部     |
  +----+--------+----------+--------+----+-----------+
  6 rows in set (0.00 sec)
  这时,我们就可以利用两张表中所有的字段进行查询了
  示例:
  -- 查询李四所在的部门名称
  SELECT emp.name,dep.name FROM  emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四";
  -- 查询销售部所有员工姓名以及部门名称

  -- SELECT name FROM  emp WHERE dep_id in (SELECT>  SELECT emp.name,dep.name FROM  emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";
  3、外连接
  (1)左外连接:在内连接的基础上增加左边有右边没有的结果
  SELECT * FROM emp LEFT JOIN dep ON dep.id=emp.dep_id;
  +----+--------+----------+--------+------+-----------+

  |>  +----+--------+----------+--------+------+-----------+
  |  2 | 李四   | 12000.00 |      1 |    1 | 教学部    |
  |  5 | 猪七   |  9000.00 |      1 |    1 | 教学部    |
  |  1 | 张三   |  8000.00 |      2 |    2 | 销售部    |
  |  3 | 王五   |  5000.00 |      2 |    2 | 销售部    |
  |  7 | 蔡九   |  7000.00 |      2 |    2 | 销售部    |
  |  4 | 赵六   |  8000.00 |      3 |    3 | 人事部    |
  |  6 | 周八   |  7000.00 |      4 | NULL | NULL      |
  +----+--------+----------+--------+------+-----------+
  7 rows in set (0.00 sec)
  (1)外右连接:在内连接的基础上增加右边有左边没有的结果
  SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;
  mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;
  +------+--------+----------+--------+----+-----------+

  |>  +------+--------+----------+--------+----+-----------+
  |    1 | 张三   |  8000.00 |      2 |  2 | 销售部    |
  |    2 | 李四   | 12000.00 |      1 |  1 | 教学部    |
  |    3 | 王五   |  5000.00 |      2 |  2 | 销售部    |
  |    4 | 赵六   |  8000.00 |      3 |  3 | 人事部    |
  |    5 | 猪七   |  9000.00 |      1 |  1 | 教学部    |
  |    7 | 蔡九   |  7000.00 |      2 |  2 | 销售部    |
  +------+--------+----------+--------+----+-----------+
  6 rows in set (0.00 sec)



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-614372-1-1.html 上篇帖子: 数据库MySQL简单操作(三) 下篇帖子: 第9章 mysql
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表