mysql-简单命令-数据类型（笔记）

我会带着你远行 2023-02-13 08:54 11阅读 0赞

纯个人笔记，方便自己查阅

### 库操作 ###

# 登陆数据库
    mysql -u root -p
    # 创建数据库
    create DATABASE user_data;
    # 选择数据库
    use user_data;
    # 删除数据库
    drop database user_data;

### 数据类型 ###

*  数值类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th style="width:85px;">大小</th> 
   <th style="width:242px;">范围（有符号）</th> 
   <th style="width:325px;">范围（无符号）</th> 
   <th style="width:116px;">用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td style="width:85px;">1 byte</td> 
   <td style="width:242px;">(-128，127)</td> 
   <td style="width:325px;">(0，255)</td> 
   <td style="width:116px;">小整数值</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td style="width:85px;">2 bytes</td> 
   <td style="width:242px;">(-32 768，32 767)</td> 
   <td style="width:325px;">(0，65 535)</td> 
   <td style="width:116px;">大整数值</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td style="width:85px;">3 bytes</td> 
   <td style="width:242px;">(-8 388 608，8 388 607)</td> 
   <td style="width:325px;">(0，16 777 215)</td> 
   <td style="width:116px;">大整数值</td> 
  </tr> 
  <tr> 
   <td>INT或INTEGER</td> 
   <td style="width:85px;">4 bytes</td> 
   <td style="width:242px;">(-2 147 483 648，2 147 483 647)</td> 
   <td style="width:325px;">(0，4 294 967 295)</td> 
   <td style="width:116px;">大整数值</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td style="width:85px;">8 bytes</td> 
   <td style="width:242px;">(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td> 
   <td style="width:325px;">(0，18 446 744 073 709 551 615)</td> 
   <td style="width:116px;">极大整数值</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td style="width:85px;">4 bytes</td> 
   <td style="width:242px;">(-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)</td> 
   <td style="width:325px;">0，(1.175 494 351 E-38，3.402 823 466 E+38)</td> 
   <td style="width:116px;">单精度<br> 浮点数值</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td style="width:85px;">8 bytes</td> 
   <td style="width:242px;">(-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)</td> 
   <td style="width:325px;">0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td style="width:116px;">双精度<br> 浮点数值</td> 
  </tr> 
  <tr> 
   <td>DECIMAL</td> 
   <td style="width:85px;">对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td> 
   <td style="width:242px;">依赖于M和D的值</td> 
   <td style="width:325px;">依赖于M和D的值</td> 
   <td style="width:116px;">小数值</td> 
  </tr> 
 </tbody> 
</table>

*  日期和时间类型

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>大小<br> ( bytes)</th> 
   <th>范围</th> 
   <th>格式</th> 
   <th>用途</th> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>3</td> 
   <td>1000-01-01/9999-12-31</td> 
   <td>YYYY-MM-DD</td> 
   <td>日期值</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
   <td>'-838:59:59'/'838:59:59'</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>1901/2155</td> 
   <td>YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8</td> 
   <td>1000-01-01 00:00:00/9999-12-31 23:59:59</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
   <td> <p>1970-01-01 00:00:00/2038</p> <p>结束时间是第&nbsp;<strong>2147483647</strong>&nbsp;秒，北京时间&nbsp;<strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</p> </td> 
   <td>YYYYMMDD HHMMSS</td> 
   <td>混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

*  字符串类型

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>用途</th> 
  </tr> 
  <tr> 
   <td>CHAR</td> 
   <td>0-255 bytes</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td>0-65535 bytes</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>0-255 bytes</td> 
   <td>不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0-255 bytes</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>0-65 535 bytes</td> 
   <td>二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0-65 535 bytes</td> 
   <td>长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>0-16 777 215 bytes</td> 
   <td>二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0-16 777 215 bytes</td> 
   <td>中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>0-4 294 967 295 bytes</td> 
   <td>二进制形式的极大文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>0-4 294 967 295 bytes</td> 
   <td>极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

### 表基本命令 ###

use user_data
    # 创建数据表
    CREATE TABLE IF NOT EXISTS `user`(
       `runoob_id` INT UNSIGNED AUTO_INCREMENT,
       `runoob_title` VARCHAR(100) NOT NULL,
       `runoob_author` VARCHAR(40) NOT NULL,
       `submission_date` DATE,
       PRIMARY KEY ( `runoob_id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    # 删除表
    DROP TABLE user
    # 插入数据
    INSERT INTO user(runoob_title, runoob_author, submission_date) VALUES("学习 java", "李白", NOW());
    # 查询语句
    select * from user;

###  收索更新 ###

# where 条件判断=，!=,>,<,>=,<=
    SELECT * from runoob_tbl WHERE runoob_author='李白';
    # 区分大小写查询BINARY 
    SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';
    # 修改值
    UPDATE runoob_tbl SET runoob_title='java' WHERE runoob_id=3;
    # 删除值
    DELETE FROM runoob_tbl WHERE runoob_id=3;
    # like模糊查询 %代表一个词组
    SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';

### 查询不同表中相同的数据 ###

# UNION ALL 包括重复数据（将不同表中相同列中查询的数据展示出来）
    SELECT country, name FROM Websites
    WHERE country='CN'
    UNION ALL
    SELECT country, app_name FROM apps
    WHERE country='CN'
    ORDER BY country;
    # UNION 不包括重复数据
    SELECT country FROM Websites
    UNION
    SELECT country FROM apps
    ORDER BY country;

### 排序分组  ###

# 结果 升序排列
    SELECT * from runoob_tbl ORDER BY submission_date ASC;
    # 结果 降序排列
    SELECT * from runoob_tbl ORDER BY submission_date DESC;
    
    # GROUP BY 按照名称分组，每组多少人
    SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
    # WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
    # 先按照名称分类，每个类里，每一项的singin数据相加
    SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
    +--------+--------------+
    | name   | singin_count |
    +--------+--------------+
    | 小丽 |            2 |
    | 小明 |            7 |
    | 小王 |            7 |
    | NULL   |           16 |
    # NULL 表示所有人的登录次数
    # select coalesce(a,b,c);
    # 如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
    mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
    +--------------------------+--------------+
    | coalesce(name, '总数') | singin_count |
    +--------------------------+--------------+
    | 小丽                   |            2 |
    | 小明                   |            7 |
    | 小王                   |            7 |
    | 总数                   |           16 |
    +--------------------------+--------------+

### 多张表关联查询 ###

# tcount_tbl表
    +---------------+--------------+
    | runoob_author | runoob_count |
    +---------------+--------------+
    | java  | 10           |
    | demo    | 20           |
    | Google        | 22           |
    +---------------+--------------+
    # runoob_tbl 表
    +-----------+---------------+---------------+-----------------+
    | runoob_id | runoob_title  | runoob_author | submission_date |
    +-----------+---------------+---------------+-----------------+
    | 1         | 学习 PHP    | java    | 2017-04-12      |
    | 2         | 学习 MySQL  | java    | 2017-04-12      |
    | 3         | 学习 Java   | demo        | 2015-05-01      |
    | 4         | 学习 Python | demo        | 2016-03-06      |
    | 5         | 学习 C      | FK            | 2017-04-05      |
    +-----------+---------------+---------------+-----------------+

两张表区交集

# INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)
    SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
    # 等价于下面
    mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
    +-------------+-----------------+----------------+
    | a.runoob_id | a.runoob_author | b.runoob_count |
    +-------------+-----------------+----------------+
    | 1           | java      | 10             |
    | 2           | java      | 10             |
    | 3           | demo      | 20             |
    | 4           | demo      | 20             |
    +-------------+-----------------+----------------+

读取左边全部数据（即便右边表无对应数据）

# LEFT JOIN
    SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
    +-------------+-----------------+----------------+
    | a.runoob_id | a.runoob_author | b.runoob_count |
    +-------------+-----------------+----------------+
    | 1           | java      | 10             |
    | 2           | java      | 10             |
    | 3           | demo      | 20             |
    | 4           | demo      | 20             |
    | 5           | FK              | NULL           |
    +-------------+-----------------+----------------+

读取右边全部数据

# RIGHT JOIN
    SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
    +-------------+-----------------+----------------+
    | a.runoob_id | a.runoob_author | b.runoob_count |
    +-------------+-----------------+----------------+
    | 1           | java    | 10             |
    | 2           | java    | 10             |
    | 3           | demo      | 20             |
    | 4           | demo      | 20             |
    | NULL        | NULL            | 22             |
    +-------------+-----------------+----------------+

### 判断null ###

# IS NULL
    SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
    # IS NOT NULL
    SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;
    # <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
    SELECT * FROM runoob_test_tbl WHERE runoob_count <=> NULL;

### 正则表达式 ###

<table> 
 <tbody> 
  <tr> 
   <th>模式</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>^</td> 
   <td>匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。</td> 
  </tr> 
  <tr> 
   <td>$</td> 
   <td>匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。</td> 
  </tr> 
  <tr> 
   <td>.</td> 
   <td>匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。</td> 
  </tr> 
  <tr> 
   <td>[...]</td> 
   <td>字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。</td> 
  </tr> 
  <tr> 
   <td>[^...]</td> 
   <td>负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。</td> 
  </tr> 
  <tr> 
   <td>p1|p2|p3</td> 
   <td>匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。</td> 
  </tr> 
  <tr> 
   <td>{n}</td> 
   <td>n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。</td> 
  </tr> 
  <tr> 
   <td>{n,m}</td> 
   <td>m 和 n 均为非负整数，其中n &lt;= m。最少匹配 n 次且最多匹配 m 次。</td> 
  </tr> 
 </tbody> 
</table>

# 查找name字段中以'st'为开头的所有数据：
    mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
    # 查找name字段中以'ok'为结尾的所有数据：
    mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
    # 查找name字段中包含'mar'字符串的所有数据：
    mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
    # 查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
    mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

参考

[https://www.runoob.com/mysql/mysql-tutorial.html][https_www.runoob.com_mysql_mysql-tutorial.html]

[https_www.runoob.com_mysql_mysql-tutorial.html]: https://www.runoob.com/mysql/mysql-tutorial.html