常见命令

  • 查看当前所有的数据库
1
show databases;
  • 打开指定的库
1
use name;
  • 查看当前库的所有表
1
show tables;
  • 查看其它库的所有表
1
show tables form name;
  • 创建表
1
2
3
4
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 数据类型

增删改查

1
2
3
4
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

建表约束

主键约束

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 主键约束
-- 使某个字段不重复且不得为空,确保表内所有数据的唯一性。
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 建表时创建唯一键
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;

唯一键和主键的辨析:

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

非空约束

1
2
3
4
5
6
7
8
9
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);

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

默认约束

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

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

外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 班级
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+除主键外其它列不能有传递依赖关系

一些操作

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 查询 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;

分组计算平均成绩

查询每门课的平均成绩

1
2
3
4
5
6
7
-- 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_nodegree **
1
2
3
4
-- 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 ) 列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 增加一个查询字段 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 班学生每门课程的平均成绩。

1
2
3
4
5
6
7
8
9
10
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 的使用

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

连接查询

现在有两张表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+------+-----------+
| id | name |
+------+-----------+
| 1 | 饭卡 |
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 工商卡 |
| 5 | 邮政卡 |
+------+-----------+
+------+--------+--------+
| id | name | cardId |
+------+--------+--------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+------+--------+--------+

内连接

1
2
3
4
5
6
7
8
9
10
11
12
-- 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;

左外连接

1
2
3
4
5
6
7
8
9
-- 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 |
+------+--------+--------+------+-----------+

右外链接

1
2
3
4
5
6
7
8
9
10
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 | 邮政卡 |
+------+--------+--------+------+-----------+

全外链接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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)
}

1
2
3
4
5
6
7
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();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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目前就学到这里吧,学了好多东西但是没有应用,感觉手好生啊