Mysql

DDL定义数据库表结构

  1. 创建数据库 CREAT DATABASE 数据库名

  2. 删除数据库 DROP DATABASE 数据库名

  3. 查看数据库 SHOW DATABASE 展示已有数据库

  4. 创建表 CREAT TABLE 表名

    1
    2
    3
    4
    CREAT TABLE 表名 (
    	列名      数据类型    主键      自增; 
        customerID int primary key auto_increment
    )
  • 创建后可用DESC 表名查看表的结构

DDL维护数据库表结构

  1. 修改表结构DDL

    1
    ALTER  TABLE 表名 修改句子 (“修改子句”允许指定多个动作，其动作间使用逗号分隔)

    DROP 删除 ADD 添加 RENAME 重命名 ALTER 修改

修改表

  • 修改数据类型 ALTER TABLE 表名 MODIFY COLUMN 修改数据的字段名 数据类型 column表示列
  • 修改字段 ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名
  • 添加字段 ALTER TABLE 表名 ADD COLUMN 字段名 数据类型
  • 删除字段 ALTER TABLE 表名 DROP COLUMN 字段名
  • 删除整表 DROP TABLE 表名

DML 增删改查

  • INSERT 插入数据

    •

    INSERT INTO 表名 (id,name,level,exp,gold) VALUES(1,”张三”,’1’,’1’,’1’ 具体的值)

    ID可设null为空自增 INSERT 可一次性插入多条数据

    INSERT INTO 表名 (id,name,level) VALUES(1,”张三”,2,3,4),
    VALUES(NULL,”张三”,’3’,’5’,’7’),
    VALUES(NULL,”张三”,’3’,’5’,’7’),
    VALUES(NULL,”张三”,’2’,’6’,’9’)

    1
    2
    3
    4
    5
    6
    7
    8
    9

          括号里为column列的值 若不写括号内容 则VALUES里的值会按照默认位置进行填充

    - SELECT 查询数据

        - ```mysql
        SELECT * FORM 表名      //*号代替所有的列
        也可
        SELECT <COLUMN1, COLUMN2, COLUMN3...> FORM 表名 

    注:必须的子句只有 select 子句和 from 子句。

  • UPDATE 修改数据

    •

    UPDATE 表名 SET 修改的列名 值 = WHERE = ‘ ‘;

    UPDATE 表名 SET height 189 = WHERE = ‘李斯特’; //把李斯特同学的身高改为189

    UPDATE 表名 set height = 0,id = 0; //修改多个值去掉WHERE 实际开发中不加 WHERE 是非常危险的行为

    1
    2
    3
    4
    5

    - DELETE 删除数据

        - ```mysql
        DELETE FORM 表名 WHERE XXX = 0;

常用语句 WHERE AND OR NOT IN NULL LIKE

  • WHERE 提取满足指定标准的记录 可同 UPDATE SELECT 一起使用

    •

    SELECT * FORM player WHERE level = 1;
    查找等级为1的玩家

    < = >= <= !=都可使用

    1
    2
    3
    4
    5
    6

    - AND 连接

        - ```mysql
        SELECT * FORM player WHERE level > 1 AND  level < 5;
        查找等级大于1小于5的玩家

  • OR 或者

  • NOT 取反 可以加在任何条件语句当中

同时用优先级NOT>AND>OR

  • IN 多个定值

  • SELECT * FROM player WHERE level IN(1,2,3);
//选取值为 1 2 3的参数

    1
    2
    3
    4
    5
    6
    7

    - NULL 空

        - ```mysql
        SELECT * FROM player WHERE email IS NULL; // 查找email为空的列表
        
        SELECT * FROM player WHERE email IS NOT NULL; // 查找email不为空的列表

  • LIKE 模糊查询

    •

    SELECT * FROM player WHERE name LIKE ‘王%’; //查找name为王的人

    SELECT * FROM player WHERE name LIKE ‘王‘; //查找第二个字为王的name

    SELECT * FROM player WHERE name LIKE ‘王_’ //查找name为王X的两字name

    SELECT * FROM player WHERE name LIKE ‘王_ _’ //查找name为王XX的三字name

    SELECT * FROM player WHERE name LIKE ‘%王%’ //查找名字中包含王的name

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22

          %任意个字符  _任意一个字符

    - ROUND 四舍五入

    - AS 别名

    - UNIQUE 唯一

    - REGEXP 正则表达式 可以使用通配符定义匹配规则

        - ###### 常用通配符

            - .  匹配任意一个字符
            - ^ 匹配开头
            - $ 匹配结尾
            - [abc] 匹配其中任意一个字符
            - [a-z] 匹配范围内的任意一个字符or[0-9]
            - A|B 匹配A或者B

        - ```mysql
        SELECT * FROM player WHERE name REGEXP '^王.$'  //查找name为王X的name

  • BETWEEN…AND… 条件连接

    •

    SELECT * FROM player WHERE level BETWEEN 1 AND 10;
    查找level属性1-10的参数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

    - ORDER BY 排序

        - ```mysql
        SELECT * FROM player ORDER BY level;  //按照level升序排列 默认
        
        SELECT * FROM player ORDER BY level DESC;  //按照level降序排列
        
        SELECT * FROM player ORDER BY level DESC,exp ASC;  //按照level降序排列 exp升序排列 不填为默认
        
        SELECT * FROM player ORDER BY 5 DESC;  //第五列按照降序排列

  • PRIMARY KEY 主键

  • AUTO_INCREMENT

  • COMMENT

  • DEFAULT 默认值

聚合函数

  • AVG() 返回集合的平均数

    •

    SELECT AVG(集合名) FORM player;

    SELECT AVG(level) FORM player; 返回level的平均数

    SELECT * FROM player WHERE level > (SELECT AVG(level) FORM player); //查找大于平均数的数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14

    - `COUNT()` 返回集合中的项目

    - `MAX()` 返回最大值

    - `MIN()` 返回最小值

    - `SUM()` 求和

    #### GROUP BY: 分组（分组查询）

    ```MYSQL
    SELECT sex,COUNT(*) FROM player GROUP BY sex;
    SELECT level,COUNT(level) FROM player GROUP BY lever;

LIMIT:限制数量

1
2
LIMIT 3
LIMIT 3,3//表示偏移量 4-6位

DISTINCT:去重

UNION:合并查询结果集(并集)

  • 合并两个查询结果 会默认去掉重复内容 两者都有 可加ALL UNION ALL

INTERSECT:(交集)

EXCEPT:合并结果集(差集)

索引

用来提升查询效率的数据结构 可以帮助我们快速定位到想要查询的结构

CREATE INDEX 创建索引

  • INDEX前面可加上可选的索引类型 [UNIQUE|FULLTEXT|SPATIAL]唯一 全文 空间
1
2
3
4
SELECT * FROM player;

CREATE INDEX [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名 (index_col_name...) 
                                                      在这张表上   对哪些字段进行索引

查看索引

  • SHOW INDEX FROM fast

    1
    2
    3
    4
    5
    6
    7



    #### 删除索引

    - ```mysql
      DROP INDEX 索引名称 ON 表名

视图

创建视图：CREATE VIEW 视图名

1
2
3
4
CREATE VIEW top10
AS
SELECT * FROM player ORDER BY level DESC LIMIT 10;
查询表player数据库中level数据前10名的数据

表中的数据发生变化时 视图也会变化

数据库导入导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysqldump -h IP -u 用户名 -p -d 数据库名 > 导出的文件名

参数解析：
-h:表示host地址
-u:表示user用户
-p:表示password密码
-d:表示不导出数据

注意：
（1）-p 后面不能加password，只能单独输入数据库名称
（2）mysqldump是在cmd下的命令，不能再mysql下面，即不能进入mysql的（如果进入了mysql，得exit退出mysql后才可以的。）


source C:\Users\Administrator\Desktop\users2.sql


Mysql 数据类型

  • 数值类型
类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 （10，2）10位数，2小数点 依赖于M和D的值 小数值
  • 日期和时间类型
类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳
  • 字符串类型
类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据