Day 1 MySQL数据库

MySQL数据库_1

 一、概述

 

  1、数据(DATA)

 

    描述事物的符号记录称为数据,描述事物的符号既可以是数字,也可以是文字、图片,图像、声音、语言等,数据由多种表现形式,它们都可以经过数字化后存入计算机。

 

  2、数据库(DataBase,简称DB)

 

    数据库就是一个文件夹!

    数据库即存放数据的仓库,只不过这个仓库是在计算机存储设备上,而且数据是按一定的格式存放的。

    过去人们将数据存放在文件柜里,现在数据量庞大,已经不再适用。

    数据库是长期存放在计算机内、有组织、可共享的数据即可。

    数据库中的数据按一定的数据模型组织、描述和储存,具有较小的冗余度、较高的数据独立性和易扩展性,并可为各种 用户共享。

 

  3、数据库管理系统(DataBase Management System,简称DBMS)

 

    在了解了Data与DB的概念后,如何科学地组织和存储数据,如何高效获取和维护数据成了关键。

    这就用到了一个系统软件---数据库管理系统。

    如MySQL、Oracle、SQLite、Access、MS SQL Server

    mysql主要用于大型门户,例如搜狗、新浪等,它主要的优势就是开放源代码,因为开放源代码这个数据库是免费的,他现在是甲骨文公司的产品。
    oracle主要用于银行、铁路、飞机场等。该数据库功能强大,软件费用高。也是甲骨文公司的产品。
    sql server是微软公司的产品,主要应用于大中型企业,如联想、方正等。

 

  4、数据库管理人员(Database administrator)

  5、结构化查询语言(Structured Query Language,简称SQL)

 二、MySQL介绍

 

  1、介绍

 

    MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统,在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

 

  2、MySQL是什么?

 

    * mysql就是一个socekt服务端
    * 客户端软件
       mysql自带
       python模块  

 

  3、其它类型的数据库 

 

    关系型:MySQL、sqllite,db2,oracle,access,sql server

    非关系型:mongodb,redis,memcache

 

三、语句概述

 

  SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。由IBM公司开发。

 

  1、SQL规范

 

    #################### 注意事项 ###################

    1)、在数据库系统中,SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写,表名库名小写;

    2)、SQL语句可单行或多行书写,以“;”结尾。关键词不能跨多行或简写。(不见“;”不结束啊)

    3)、用空格和缩进来提高语句的可读性。子句通常位于独立行,便于编辑,提高可读性。

    4)、注释:  单行注释:--

                     多行注释:/*......*/

    5)、sql语句可以折行操作

    6)、DDL,DML和DCL区别:     

      DDL(data definition language)语句 数据库定义语言: 数据库、表、视图、索引、存储过程,

        例如CREATE DROP ALTER

      DML(data manipulation language)语句 数据库操纵语言:

        插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

      DCL(Data Control Language)语句 数据库控制语言:

        例如控制用户的访问权限GRANT、REVOKE

 

  2、MySQL 基本管理 之修改登录密码

 

1 mysqladmin -uroot -p"旧密码" password "新密码"    --注意语句格式
1 -- 登陆MySQL
2 mysql -uroot -p

 

  3、数据库的操作

 

 1 -- 1 创建数据库
 2         CREATE DATABASE chaoxuan(db_name) [CHARACTER SET GBK];
 3 
 4 -- 2 查看数据库
 5         SHOW DATABASES;  -- 查看所有数据库
 6         SHOW CREATE DATABASE chaoxuan(db_name);  -- 查看指定数据库信息
 7 
 8 -- 3 修改数据库
 9         ALTER DATABASE chaoxuan(db_name) CHARACTER SET UTF8;  -- 修改设置
10 
11 -- 4 删除数据库
12         DROP DATABASE chaoxuan(db_name); 
13 
14 -- 5 使用数据库
15         USE oldboy_test2(db_name);  -- 使用数据库 
16         SELECT DATABASE();  -- 查看当前的数据库

 

  4、数据表的操作

 

 

 1 -- 1 创建数据表
 2          CREATE TABLE salary(
 3     ->         ID int primary key auto_increment,
 4     ->         NAME varchar(20),
 5     ->         TIME date,
 6     ->         JOB varchar(20),
 7     ->         SALARY double(5,2) unsigned,
 8     ->         RESUME text  -- 最后一个字段不加逗号
 9     -> );
10 
11 /* 约束:
12        primary key (非空且唯一)  :能够唯一区分出当前记录的字段称为主键!
13        unique         
14        not null
15        auto_increment :用于主键字段,主键字段必须是数字类型
16   */
17 
18 -- 2 查看表结构
19         DESC salary;
20*
21 +--------+----------------------+------+-----+---------+----------------+
22 | Field  | Type                 | Null | Key | Default | Extra          |
23 +--------+----------------------+------+-----+---------+----------------+
24 | ID     | int(11)              | NO   | PRI | NULL    | auto_increment |
25 | NAME   | varchar(20)          | YES  |     | NULL    |                |
26 | TIME   | date                 | YES  |     | NULL    |                |
27 | JOB    | varchar(20)          | YES  |     | NULL    |                |
28 | SALARY | double(5,2) unsigned | YES  |     | NULL    |                |
29 | RESUME | text                 | YES  |     | NULL    |                |
30 +--------+----------------------+------+-----+---------+----------------+
31 *32 
33 -- 3 查看表结构
34         SHOW COLUMNS FROM salary;
35 
36 -- 4 查看当前数据库中的所有表
37         SHOW TABLES;
38 
39 -- 5 查看当前数据库建表语句
40         SHOW CREATE TABLE salary(db_name);
-- 1 增加列字段
        ALTER TABLE salary ADD maxsalary double(5,2);

-- 2 修改一列类型
         ALTER TABLE salary MODIFY JOB int;

-- 3 修改列名
        ALTER TABLE salary CHANGE JOB JOBNB int;

-- 4 删除一列
         ALTER TABLE salary DROP maxsalary;
---- 删除多列,再加一列
         ALTER TABLE salary DROP JOBNB,
    ->                               ADD job int,
    ->                               DROP TIME;

-- 5 修改表名
        RENAME TABLE salary TO lala;

-- 6 修改表用的字符集
        ALTER TABLE lala CHARACTER SET utf8;
-- 删除表格
        DROP TABLE dachao;

 

  5、表记录的操作

 

/*
数据类型:
    int 整型
    varchar(20) 字符串 
    Text    大文本字符串
    float(6,2)  浮点型,小数点后两位,一共6位有效数字最大:9999.99
其它:
    auto_increment 自增 1、2、3……
*/

  

CREATE TABLE teacher(
id int PRIMARY KEY auto_increment,
gender BIT DEFAULT 1,
name VARCHAR(20) NOT NULL,
age int,
salary FLOAT(8,2) unsigned,
work_time DATE,
resume text
);

 
-- 插入表记录 insert

INSERT INTO teacher (gender,name,age,salary,work_time) VALUES (0,"egon",18,10000,"1990-12-12"),
                                                              (1,"yuanhao",78,2000,"1980-1-12"),
                                                              (0,"alex",998,666666,"2000-6-6"),
                                                              (0,"wusir",5,9999,"1212-8-8");
 

-- 修改表记录 update
WHERE (条件) -- 筛选条件

UPDATE teacher SET salary = 9999 WHERE id = 1;

UPDATE teacher SET salary=salary+4444 WHERE name="alex";  -- 在alex的salary的基础上,再加4444。


-- 删除表记录 
    -- deletedelete只能用来删除一行记录
    -- delete语句只能删除表中的内容,不能删除表本身,想要删除表,用drop
DELETE FROM table_name 字句;

DELETE FROM teacher WHERE name="wusir";
TRUNCATE TABLE table_name;  -- 可以删除表中所有数据,词语句首先摧毁表,再新建表。此种方式删除的数据不能在事务中恢复。


-- ***查询表记录***
/*
查询语法 SELECT di,name... FROM teacher
                        WHERE 条件
                        GROUP BY fielg_name
                        HAVING 筛选
                        ORDER BY field_name
                        LIMIT 限制条数
*/


CREATE TABLE shop_menu(
         id int PRIMARY KEY auto_increment,
         p_name VARCHAR (20),
         p_price FLOAT(6,2),
         p_date DATE,
         p_number INT unsigned,
         class VARCHAR (20)         
) CHARACTER SET utf8;

INSERT INTO shop_menu (p_name,p_price,p_date,p_number,class) VALUES ("苹果",5,20170725,100,"水果"),
                                                                   ("香蕉",10,20170725,500,"水果"),
                                                                   ("床单",100,20170725,10,"家具"),
                                                                   ("西瓜",2,20170720,30,"水果"),
                                                                   ("冰箱",1000,20170725,1000,"电器"),
                                                                   ("沙发",800,20170720,20,"家具"),
                                                                   ("电饭煲",200,20170725,100,"电器");

/*
+----+-----------+---------+------------+----------+--------+
| id | p_name    | p_price | p_date     | p_number | class  |
+----+-----------+---------+------------+----------+--------+
|  1 | 苹果      |    5.00 | 2017-07-25 |      100 | 水果   |
|  2 | 香蕉      |   10.00 | 2017-07-25 |      500 | 水果   |
|  3 | 床单      |  100.00 | 2017-07-25 |       10 | 家具   |
|  4 | 西瓜      |    2.00 | 2017-07-20 |       30 | 水果   |
|  5 | 冰箱      | 1000.00 | 2017-07-25 |     1000 | 电器   |
|  6 | 沙发      |  800.00 | 2017-07-20 |       20 | 家具   |
|  7 | 电饭煲    |  200.00 | 2017-07-25 |      100 | 电器   |
+----+-----------+---------+------------+----------+--------+
*/

SELECT id,p_name,p_price,p_number FROM shop_menu;
/*
+----+-----------+---------+----------+
| id | p_name    | p_price | p_number |
+----+-----------+---------+----------+
|  1 | 苹果      |    5.00 |      100 |
|  2 | 香蕉      |   10.00 |      500 |
|  3 | 床单      |  100.00 |       10 |
|  4 | 西瓜      |    2.00 |       30 |
|  5 | 冰箱      | 1000.00 |     1000 |
|  6 | 沙发      |  800.00 |       20 |
|  7 | 电饭煲    |  200.00 |      100 |
+----+-----------+---------+----------+
*/

SELECT id,p_name 姓名,p_price 价格,p_number 数量 FROM shop_menu;  -- 临时指定字段的名字,只在显示时变化
/*
+----+-----------+---------+--------+
| id | 姓名      | 价格    | 数量   |
+----+-----------+---------+--------+
|  1 | 苹果      |    5.00 |    100 |
|  2 | 香蕉      |   10.00 |    500 |
|  3 | 床单      |  100.00 |     10 |
|  4 | 西瓜      |    2.00 |     30 |
|  5 | 冰箱      | 1000.00 |   1000 |
|  6 | 沙发      |  800.00 |     20 |
|  7 | 电饭煲    |  200.00 |    100 |
+----+-----------+---------+--------+
*/

/* -- WHERE
        > < >= <= !=
        between 1 and 100  -- 在1和100之间,含1,100!!!
        in(5,10,100)  -- 含值5,10,100的
        like "a%"  --     开头含a  "a___"  -- 开头含a且后面有3个字符串
        and or not
*/

SELECT * FROM shop_menu WHERE p_price BETWEEN 1 AND 100;
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  1 | 苹果   |    5.00 | 2017-07-25 |      100 | 水果   |
|  2 | 香蕉   |   10.00 | 2017-07-25 |      500 | 水果   |
|  3 | 床单   |  100.00 | 2017-07-25 |       10 | 家具   |
|  4 | 西瓜   |    2.00 | 2017-07-20 |       30 | 水果   |
+----+--------+---------+------------+----------+--------+
*/

SELECT * FROM shop_menu WHERE p_price IN(5,10,100);
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  1 | 苹果   |    5.00 | 2017-07-25 |      100 | 水果   |
|  2 | 香蕉   |   10.00 | 2017-07-25 |      500 | 水果   |
|  3 | 床单   |  100.00 | 2017-07-25 |       10 | 家具   |
+----+--------+---------+------------+----------+--------+
*/

SELECT * FROM shop_menu WHERE p_name LIKE "苹%";
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  1 | 苹果   |    5.00 | 2017-07-25 |      100 | 水果   |
+----+--------+---------+------------+----------+--------+
*/

SELECT * FROM shop_menu WHERE p_name LIKE "苹_";
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  1 | 苹果   |    5.00 | 2017-07-25 |      100 | 水果   |
+----+--------+---------+------------+----------+--------+
*/


-- order by 字段名称 排序查询
    -- 默认从小到大(ASC)从大到小排序
SELECT * FROM shop_menu ORDER BY p_price; 
    -- 后面加(DESC)默认从大到小排序 
SELECT * FROM shop_menu ORDER BY p_price DESC; 
/*
+----+-----------+---------+------------+----------+--------+
| id | p_name    | p_price | p_date     | p_number | class  |
+----+-----------+---------+------------+----------+--------+
|  5 | 冰箱      | 1000.00 | 2017-07-25 |     1000 | 电器   |
|  6 | 沙发      |  800.00 | 2017-07-20 |       20 | 家具   |
|  7 | 电饭煲    |  200.00 | 2017-07-25 |      100 | 电器   |
|  3 | 床单      |  100.00 | 2017-07-25 |       10 | 家具   |
|  2 | 香蕉      |   10.00 | 2017-07-25 |      500 | 水果   |
|  1 | 苹果      |    5.00 | 2017-07-25 |      100 | 水果   |
|  4 | 西瓜      |    2.00 | 2017-07-20 |       30 | 水果   |
+----+-----------+---------+------------+----------+--------+
*/

-- 分组查询 ***group by***

SELECT * FROM shop_menu GROUP BY class;
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  3 | 床单   |  100.00 | 2017-07-25 |       10 | 家具   |
|  1 | 苹果   |    5.00 | 2017-07-25 |      100 | 水果   |
|  5 | 冰箱   | 1000.00 | 2017-07-25 |     1000 | 电器   |
+----+--------+---------+------------+----------+--------+
*/

SELECT class,SUM(p_price) FROM shop_menu GROUP BY class;  -- 按类分组后,再显示每组价格总和
/*
+--------+--------------+
| class  | SUM(p_price) |
+--------+--------------+
| 家具   |       900.00 |
| 水果   |        17.00 |
| 电器   |      1200.00 |
+--------+--------------+
*/

SELECT class,SUM(p_price) FROM shop_menu GROUP BY class HAVING SUM(p_price)>100;  -- 按类分组后,再显示每组价格总和大于100
/*
+--------+--------------+
| class  | SUM(p_price) |
+--------+--------------+
| 家具   |       900.00 |
| 电器   |      1200.00 |
+--------+--------------+
*/

-- 聚合函数就是应用在group by

--<1> 统计表中所有记录

    -- COUNT(列名):统计行的个数
        -- 统计一共有多少行,再用count包上
            SELECT COUNT(*) FROM shop_menu;
        -- 统计价格大于100的商品有多少个?
            SELECT COUNT(p_price) FROM shop_menu WHERE p_price>100;
        -- 统计总价格大于10000的商品有多少?
            SELECT COUNT(p_name) FROM shop_menu WHERE (ifnull(p_price,0)*ifnull(p_number,0))>10000;
        -- 注意:count(*)统计所有行;     count(字段)不统计null值.  ifnull(p_price,0) 如果输入为空,则为0.

    -- SUM(列名):统计满足条件的行的内容和
        -- 统计所有水果p_price的总价?先查出所有的水果,再用sum包上每种水果总价
            SELECT SUM(p_price*p_number) AS 总价格 FROM shop_menu WHERE class="水果";
            SELECT SUM(p_price*p_number) AS 总价格 FROM shop_menu GROUP BY class HAVING class="水果";
        -- 注意:sum仅对数值起作用,否则会报错。

    -- AVG(列名):
        -- 求平均价格?先查出所有的价格,然后用avg包上。
            SELECT AVG(p_price) FROM shop_menu;
     
    -- Max、Min
        -- 求单价最高和最低(数值范围在统计中特别有用)
            SELECT MAX(p_price) AS 单价最高 FROM shop_menu;
            SELECT Min(p_number) AS 数量最少 FROM shop_menu;
 
        -- 注意:null 和所有的数计算都是null,所以需要用ifnull将null转换为0!
        --      -----ifnull(JS,0)


-- having 配合使用

/*
having 和 where两者都可以对查询结果进行进一步的过滤,差别有:
     <1>where语句只能用在分组之前的筛选,having可以用在分组之后的筛选;
     <2>使用where语句的地方都可以用having进行替换
     <3>having中可以用聚合函数,where中就不行。
*/


-- limit 表记录限制

SELECT * FROM shop_menu LIMIT 2,3;  -- 限制显示id2后三行的内容
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  3 | 床单   |  100.00 | 2017-07-25 |       10 | 家具   |
|  4 | 西瓜   |    2.00 | 2017-07-20 |       30 | 水果   |
|  5 | 冰箱   | 1000.00 | 2017-07-25 |     1000 | 电器   |
+----+--------+---------+------------+----------+--------+
*/

-- 正则表达式 REGEXP

SELECT * FROM shop_menu WHERE p_name REGEXP "^床";
/*
+----+--------+---------+------------+----------+--------+
| id | p_name | p_price | p_date     | p_number | class  |
+----+--------+---------+------------+----------+--------+
|  3 | 床单   |  100.00 | 2017-07-25 |       10 | 家具   |
+----+--------+---------+------------+----------+--------+
*/

   6、出现的问题

--  出现的问题 

select * from shop_menu group by class;

/*
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and 
表达式select list 不在group by分句中
contains nonaggregated column 'day_25.shop_menu.id' which is not functionally dependent on columns 
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
*/

SELECT @@sql_mode;  -- 查询,并去掉ONLY_FULL_GROUP_BY
SET @@sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

 

posted @ 2017-07-25 08:34  LiChaoAI  阅读(315)  评论(0编辑  收藏  举报