MySQL学习笔记

常用指令

数据库

• SHOW DATABASES;
  显示所有数据库

• CREATE DATABASE test;
  创建一个名为 test 的数据库

• DROP test_database;
  删除名为 test_database 的数据库

• USE test_database;
  将操作数据库切换至 test_database

表

• CREATE TABLE t_test (id VARCHAR(20) NOT NULL, test1 VARCHAR(20) NOT NULL, PRIMARY KEY (id));
  创建一个名为 test 的表，并规定表的字段内容，id, test1 分别为可变长度文本最大长度都为20，都不能为空，并将 id 设置为主键。

• 增：INSERT INTO t_test(test1, test2) VALUES ('0', '1');
  在表t_test 中插入 test1=‘0’, test2= ‘1’ 的行

• 删：DELETE FROM t_test WHERE id=1;
  删除表 t_test 中 id=1 的行。

• 改：UPDATE t_test SET test1=0, test2=1 WHERE id=1;
  更新表 t_test 中 id=1 的行中列 test1=0, test2=1

• 查：SELECT test FROM t_test WHERE id=1 ORDER BY name ASC LIMIT 0,3;
  选择表 t_test 中 id=1 的行并将其按 name 进行排序【ASC 为升序，DESC 为降序】默认为升序，输出从第0位开始的三个数据【不包括0】中的 test 列

• DESCRIBE t_test;
  显示表 t_test 的各字段信息。

代码举例

例：

     SELECT a.name,
     CASE
     WHEN a.gender = '1' THEN 'Male'
     ELSE 'Female'
     END gender
     FROM t_singer a;

输出 t_singer 表中的 name 和 gender，并且 gender 为 1 对应 Male ，反之为 Female，将其填入新的 gender 列中并显示出来。（表中原来的 gender 数据没有改变，仍然为 0 或 1，只是显示出来的 gender 变化）

例：

SELECT
 a.stu_id studentId,
 a.stu_name name,
 b.cls_name,
 b.grade
FROM t_student a
INNER JOIN t_class b ON a.cls_id=b.cls_id;

将查询的表 stu_id 重命名为 studentId, stu_name 同理。最后一行为外联表等值连接，能把两个表通过关系连接起来，以上通过 cls_id 连接。

例：

CREATE VIEW students
AS
SELECT
 a.stu_id studentId,
 a.stu_name name,
 b.cls_name,
 b.grade
FROM t_student a
INNER JOIN t_class b ON a.cls_id=b.cls_id;

这串代码只比上一个代码多了 CREATE VIEW students AS 这个语句用于创建一个视图。视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

例：

SELECT 
  SUM(b.course_credit),
  COUNT(*),
  COUNT(DISTINCT b.course_name)
FROM t_mark a
INNER JOIN t_course b ON a.course_id = b.course_id
WHERE 
  a.stu_id = '20107072'
  AND
  a.scroll<60

对结果表中的 course_credit 进行求和操作， COUNT(*) 用于返回所有列的记录数，即数据的行数。 COUNT(DISTINCT b.course_name) 返回 course_name 的记录数，前面的 DISTINCT 表示不记录重复数据，例如如果课程名有多次出现，只记录一次。** WHERE 必须在后面,否则会语法错误!**

集合运算

例：

SELECT
  b.stu_id,
  b.stu_name
FROM t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
WHERE
  a.subject = '会计'
  AND
  a.grade = 2012
  AND
  EXISTS (
    SELECT 1 FROM t_mark c
    WHERE c.stu_id = b.stu_id AND c.scroll < 60
  )

c 表中的 stu_id 与 b 表中的进行交集处理，结果表中只会留下 scroll 小于60的数据。

另例：
此例和上例结果相同但不是集合操作，仅是本人最初的代码，这种代码效率较上面的很低。再此只为比较：

SELECT
  b.stu_id,
  b.stu_name
FROM t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
WHERE
  a.subject = '会计'
  AND
  a.grade = 2012
  AND
  b.stu_id IN(
    SELECT c.stu_id FROM t_mark c
    WHERE c.scroll < 60
  )

或者：

SELECT
  b.stu_id, 
  b.stu_name,
FROM t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
INNER JOIN t_mark c ON c.stu_id = b.stu_id
WHERE
  a.subject = '会计'
  AND
  a.grade = 2012
  AND
  c.scroll < 60

#数据库用户权限管理
##创建用户
GRANT ALL ON [DB].[TABLE]

例如：
GRANT ALL ON chat.* TO 'Sunnycee'@'192.168.%'IDENTIFIED BY '123456';

• 创建名为 Sunnycee，密码为 123456 的用户，此用户只能由IP地址为192.168.xxx.xxx的主机访问，且只能访问 chat 数据库下的数据。
• ALL 表示开启所有权限(DELETE, UPDATE, INSERT, SELECT)，如果不想为所有权限，可以将 ALL 改为对应的权限，如：'GRANT DELETE, UPDATE ON ~'
• ‘%’ 表示通配任意N个字符，‘192.168.%’ 意思是只要前缀为 192.168 的主机都能访问，’*’ 表示全部的意思。

Tip:
如果想要修改用户权限需要用户退出数据库后才能修改成功，因为权限是在用户最初进数据库的时候分配的，若不退出权限将不会变化。
##删除用户

• DROP USER 'Sunnycee'@'192.168.%'
  #删除名为 Sunnycee，IP 为192.168.% 的用户。

Tip:
删除用户也需要用户退出数据库后才能完成操作，若未退出进行删除后，用户仍能对数据库进行操作，但退出将无法登陆数据库。

#数据库常用函数

• SUM() //用于求和
• AVG() //用于求平均值
• MIN() //用于求最小值
• MAX() //用于求最大值
• STD_DEV() //用于标准差
• NOW()　　　　　 //当前时间
• CURDATE() 　　　//当前日期
• TIMESTAMPDIFF()　　//两个时间点的时间差

#小知识点

• 数据库的 ACID 特性：

1. Atomic–原子性：整个事务的全部过程要么都执行，要么都不执行。
2. Consistent–一致性：一个约束条件会在所有操作中生效（例如定义账户余额不能小于0，则在所有操作中余额都不能小于0）
3. isolated–隔离性（序列性）：事务操作序列化，同一时间仅有一个请求用于同一数据。
4. Durable–持久性：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚，只要数据库成功操作后，数据一定被保存。

• 数据库只能用单引号(’ ')包含文本。
• Windows系统 在终端框输入 ipconfig 可以查看本机的 IP 地址。
• having的用法
  having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。