mysql学习笔记

常见命令

查看当前所有的数据库

1	show databases;

打开指定的库

use name;

查看当前库的所有表

1	show tables;

查看其它库的所有表

1	show tables form name;

创建表

create table name(
    列名 列类型,
  	。。。
)

查看表结构

1	desc name;

插入一个表

1 2	insert into tablename values ('A', 'B');

查询数据

1	select * from tablename;

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

注意:

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

from: 菜鸟教程:MySQL 数据类型

增删改查

insert into tablename values('xx','xxx');# 增加
delete from tablename where name='xxx'; # 删除
update tablename set name='xxx' where owner = 'xxx' # 修改
select * from tablename; #查询

对列的修改

增加一个列

1	alter table tablename add column name type after name;

删除一个列

1	alter table tablename drop column name;

更改一个列

1	alter table tablename change name type

建表约束

主键约束

一个表只能有一个主键，联合主键只是一个主键。

-- 主键约束
-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 添加主键约束
-- 如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE user drop PRIMARY KEY;

-- 建表时创建唯一键
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一键
-- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一键
ALTER TABLE user DROP INDEX name;

唯一键和主键的辨析:

唯一键不是主键, 主键有一个特点是不能重复, 但是唯一键不等于主键
一张表中只能有一个主键, 但是一张表中可以有多个唯一键

非空约束

-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

默认约束

默认约束指定某列的默认值，不特殊指定的话值为默认。

-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

外键约束

-- 班级
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- 这里的 class_id 要和 classes 中的 id 字段相关联
    class_id INT,
    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的记录被副表引用时，主表不可以被删除。

三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循–定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式

第一范式

确保每列的原子性如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元)

第一范式的合理遵循需要根据系统的实际需求来定

例如:学生户籍表(姓名、学号、地址、…其中”地址"列还可以细分为省、市、区等。
“地址”这个属性，本来直接将“地址”属性设计成个数据库表的字段就行。但是如果经常会访问”地址”属性中的“城市”部分，那么就要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。

第二范式

前提：满足第一范式

除主键外的每一列都必须完全依赖于主键

学生选课表(学号，姓名，专业，课程号，课程名，成绩)，该表中一个学生可以选多门课，一门课有多个学生。学号和课程号可以唯一确定一条记录，因此用学号和课程号做主键。
表中的姓名、专业通过主键中的学号就能唯一确定，而课程名通过课程号唯一确定，这就是部分依赖，这样的设计不符合第二范式。因此可以拆分为三个表

学生信息表（学号，姓名，专业）

课程信息表（课程号,课程名）

选课关系表（学号，课程号，成绩）

不符合第三范式会带来的问题: 数据信息冗余
增删改会出现问题，比如有一门《数据库原理与设计》没有人选，那么由于缺少学号(主键之一)那么这门课就不会出现在一开始的学生选课表里

第三范式

前提：满足第二范式

除开主键外的其它列不能有传递依赖关系，即确保每列都和主键列直接相关,而不是间接相关

比如学生表(学号，姓名，年龄，性别，学院，学院地址，学院电话)
这样一个表结构，就存在上述关系

学号–> 学院–> (学院地址，学院电话)
这样的表结构，我们应该拆开来，如下。
(学号，姓名，年龄，性别，学院) - (学院，学院地址，学院电话)

总结

第一范式1NF：字段不可分割

第二范式2NF：1NF+除主键外其他列完全依赖主键

第三范式3NF：2NF+除主键外其它列不能有传递依赖关系

一些操作

查询

-- 查询 student 表的所有行
SELECT * FROM student;

-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;

-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;

-- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);

-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';

-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序，从高到低
-- ASC（默认）: 升序，从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;

-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';

-- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
-- (SELECT MAX(degree) FROM score): 子查询，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

--  排序查询
-- LIMIT r, n: 表示从第r行开始，查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

分组计算平均成绩

查询每门课的平均成绩

-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

分组条件与模糊查询

查询 `score` 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。

1 2	SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';

多表查询 -1

查询所有学生的 `name`，以及该学生在 `score` 表中对应的 `c_no` 和 `degree`

-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score 
WHERE student.no = score.s_no;

多表查询 -2

查询所有学生的 `no` 、课程名称 ( `course` 表中的 `name` ) 和成绩 ( `score` 表中的 `degree` ) 列

-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name          | degree |
+------+-----------------+--------+
| 103  | 计算机导论      |     92 |
| 105  | 计算机导论      |     88 |
| 109  | 计算机导论      |     76 |
| 103  | 操作系统        |     86 |
| 105  | 操作系统        |     75 |
| 109  | 操作系统        |     68 |
| 103  | 数字电路        |     85 |
| 105  | 数字电路        |     79 |
| 109  | 数字电路        |     81 |
+------+-----------------+--------+

子查询加分组求平均分

查询 `95031` 班学生每门课程的平均成绩。

select cName, avg(sco) from score, course
where ( score.sNum in (select sNum from student where sClass = '95031') ) and ( score.cNum = course.cNum )
GROUP by course.cName;
+-----------------+----------+
| cName           | avg(sco) |
+-----------------+----------+
| 计算机导论      |  82.0000 |
| 操作系统        |  71.5000 |
| 数字电路        |  80.0000 |
+-----------------+----------+

注意此处group by用法。按照course.cName分组后，对于同一分组使用avg函数计算平均值

UNION 和 NOTIN 的使用

查询 `计算机系` 与 `电子工程系` 中的不同职称的教师。

select * from teacher where sCourse = '计算机系' and sRank not in (
    select sRank from teacher where sCourse = '电子工程系'
)
UNION
select * from teacher where sCourse = '电子工程系' and sRank not in (
    select sRank from teacher where sCourse = '计算机系'
);
+------+--------+------+------------+-----------+-----------------+
| tNum | tName  | tSex | sDate      | sRank     | sCourse         |
+------+--------+------+------------+-----------+-----------------+
|  804 | 李诚   | 男   | 1958-12-02 | 副教授    | 计算机系        |
|  856 | 张旭   | 男   | 1969-03-12 | 讲师      | 电子工程系      |
+------+--------+------+------------+-----------+-----------------+

查询 student 表中至少有 2 名男生的 class 。

1	select sClass fromm student where sex = '男' GROUP by sClass where count(*) > 1; -- 带group by的查询还是有些迷

NOTLIKE 模糊查询取反

略，同like

连接查询

现在有两张表

+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+

内连接

-- INNER JOIN: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

-- 将 INNER 关键字省略掉，结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;

左外连接

-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

右外链接

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

全外链接

-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误：
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全连接语法，使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

golang使用mysql

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

type studentInf struct {
	sNum int
	sName string
	sSex string
	sDate string
	sClass int
}

func initDB() (err error) {
	dsn := "root:123456@tcp(localhost:3306)/test1"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	
	err = db.Ping()
	if err != nil {
		return
	}

	db.SetMaxOpenConns(10)
	return
}

func queryOne(sNum int) {
	var s studentInf
	sqlStr := "select * from student where sNum = ?;"
	db.QueryRow(sqlStr, sNum).Scan(&s.sNum, &s.sName, &s.sSex, &s.sDate, &s.sClass)
	fmt.Println(s)
}

func queryMore(sNum int) {
	var s studentInf
	sqlStr := "select * from student where sNum >= ?;"
	rows, err := db.Query(sqlStr, sNum)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		err := rows.Scan(&s.sNum, &s.sName, &s.sSex, &s.sDate, &s.sClass)
		if err != nil {
			fmt.Println(err)
		}
		fmt.Println(s)
	}
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("连接成功")

	//queryOne(109)

	// for i := 101; i <= 109; i++ {
	// 	queryOne(i)
	// }

	queryMore(101)
}

func updateDB(db *sql.DB,name string,id int)  {
    stmt, err := db.Prepare("update test set name = ? where id = ?",name,id)
    if err != nil{
        log.Fatal(err)
    }
    stmt.Exec();
}

func selectDB(db *sql.DB)  {
    stmt, err := db.Query("select * from test;")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()
    for stmt.Next(){
        var id int
        var name string
        //var name sql.NullString
        err := stmt.Scan(&id,&name)
        if err != nil {my
            log.Fatal(err)
        }
        fmt.Println(id,name)
    }

}

其它的操作基本都是换汤不换药=_=

mysql目前就学到这里吧，学了好多东西但是没有应用，感觉手好生啊