自从做完一个简单的登陆注册页面，发现数据库知识真的很重要，所以要详细的学习一下数据库的知识。
一、数据库基本概念
数据库可以理解为用来存储信息的仓库
表:表是数据的矩阵，在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键,可以使用主键来查询据。
表头(header): 每一列的名称;
基本概念可以在菜鸟教程中学习
菜鸟教程
二、DDL语句
对数据库的操作
1).创建数据库
1 | create database 数据库名; |
例如:1
2mysql> use bss;/使用这个名为bss的数据库
Database changed
2)选择要操作的数据库
1 | use 数据库名; |
3)查看数据库中所有的数据表
1 | show tables; |
4)删除数据库
1 | drop database 数据库名称; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> create database xiao;
Query OK, 1 row affected (0.00 sec)/这样就新建一个数据库，我们也可以在查看一下。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bss |
| myhtml |
| mysql |
| performance_schema |
| score |
| test |
| xiao |
+--------------------+
8 rows in set (0.00 sec)/这样数据库中便有了这个xiao数据库，接下来再删除它。
mysql> drop database xiao;
Query OK, 0 rows affected (1.85 sec)
/这样xiao数据库就被删除了。
对表的操作
1）创建表
1 | create table 表名( |
例如：1
2create table Q(id1 int,id2 int);
/这样就创建好一个名为Q的表
2）查看表
1 | show tables; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> desc user;/查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| pass | varchar(30) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| ttt | varchar(40) | YES | | NULL | |
| sss | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> show tables;/查看表数据
+---------------+
| Tables_in_bss |
+---------------+
| user |
| zzh |
+---------------+
2 rows in set (0.00 sec)
3）查看创建表的SQL语句
1 | show create table 表名 \G |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13mysql> show create table user;//查看建表命令
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`pass` varchar(30) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`ttt` varchar(40) DEFAULT NULL,
`sss` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4)删除表
1 | drop table 表名; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22mysql> create table hello(id int,you varchar(30),me varchar(30));
Query OK, 0 rows affected (1.78 sec)/接下来我们查看一下
mysql> show tables;
+---------------+
| Tables_in_bss |
+---------------+
| hello |
| user |
| zzh |
+---------------+
3 rows in set (0.00 sec)/这样bbs数据库中便有这个hello的数据表了
mysql> drop table hello;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_bss |
+---------------+
| user |
| zzh |
+---------------+
2 rows in set (0.00 sec)
/这样就删除了刚才所创建的数据表
5)修改表的字段类型
1 | alter table 表名 modify 字段名 字段类型; |
例如：1
alter table Q modify id1 varchar;
6)增加表字段
1 | alter table 表名 add 字段名 字段类型; |
例如：1
alter table Q add id3 int;
7)删除表字段
1 | alter table 表名 drop 字段名; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14mysql> alter table user drop pass;
Query OK, 3 rows affected (1.81 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc user;/再次查询
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
/这样的话就将新建的pass字段给删除了
8)字段改名
1 | alter table 表名 change 旧字段名 新字段名 字段类型; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24mysql> desc zzh;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| he | varchar(20) | YES | | NULL | |
| me | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
/这里我们想把字段he改为you
mysql> alter table zzh change he you varchar(20);
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
/再次进行查询
mysql> desc zzh;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| you | varchar(20) | YES | | NULL | |
| me | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
/可以看到he已经改为了you
9)修改字段排列排序
例如：
1 | alter table Q modify id2 tinyint first; |
10)更改表名
1 | alter table 表名 rename 新的表名; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22mysql> use bss;
Database changed
mysql> show tables;
+---------------+
| Tables_in_bss |
+---------------+
| user |
| zzh |
+---------------+
2 rows in set (0.00 sec)
/接下来我们把数据表user的名字改为username
mysql> alter table user rename username;
Query OK, 0 rows affected (1.75 sec)
/这些便实现了改表名称
mysql> show tables;
+---------------+
| Tables_in_bss |
+---------------+
| username |
| zzh |
+---------------+
2 rows in set (0.00 sec)
DML语句
这里我使用test数据库中的welcome表来进行练习
1)查询语句
1 | select * from 表名; |
2)插入记录
1 | insert into 表名(字段1,字段2,字段3,...,字段n) values(值1,值2,值3,...,值n); |
注意：values后面的顺序应该和字段的排序一致
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> insert into welcome(username,password) values('WMM',123);
Query OK, 1 row affected (0.00 sec)
/注意values中有字符的话，用单引号括起来。
mysql> insert into welcome(username,password) values(2,123);
Query OK, 1 row affected (0.31 sec)
/这里插入两条数据
mysql> select * from welcome;/查询语句
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 123 |
| 2 | 123 |
| WMM | 123 |
+----------+----------+
3 rows in set (0.00 sec)
3)一次插入多条记录
1 | insert into 表名(字段1,字段2,字段3,...,字段n) |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20mysql> insert into welcome(username,password) values
-> (1,123),
-> (2,123),
-> (3,123),
-> (4,123);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from welcome;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 123 |
| 2 | 123 |
| WMM | 123 |
| 1 | 123 |
| 2 | 123 |
| 3 | 123 |
| 4 | 123 |
+----------+----------+
7 rows in set (0.00 sec)
4)更新记录
1.更新一个表
1 | update 表名 set 字段1=值1,字段2=值2,...字段n=值n [where 条件]; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> update welcome set password=1234 where username='QTFY';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from welcome;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 1234 |
| 2 | 123 |
| WMM | 123 |
| 1 | 123 |
| 2 | 123 |
| 3 | 123 |
| 4 | 123 |
+----------+----------+
7 rows in set (0.00 sec)
/可以看到QTFY的密码已经从123改为1234
2.更新多个表中数据1
update 表1,表2,...表n set 表1.字段1=表达式1,表n.字段n=表达式n [where 条件];
例如：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/首先再创建一个表Q
mysql> create table Q(id int,age int);
Query OK, 0 rows affected (0.08 sec)
mysql> desc Q;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into Q(id,age) values(1,20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from Q;
+------+------+
| id | age |
+------+------+
| 1 | 20 |
+------+------+
1 row in set (0.00 sec)
/更新语句
mysql> update welcome,Q set welcome.password=2000,Q.age=2000 where welcome.username='QTFY' and Q.age=20;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
/更新结果：
| QTFY | 2000
1 | 2000 |
5)删除记录
1.删除单表中的数据
1 | delete from 表名 [where 条件]; |
例如：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
29mysql> select * from welcome;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 2000 |
| 2 | 123 |
| WMM | 123 |
| 1 | 123 |
| 2 | 123 |
| 3 | 123 |
| 4 | 123 |
+----------+----------+
7 rows in set (0.00 sec)
/这里删除WMM记录
mysql> delete from welcome where username='WMM';
Query OK, 1 row affected (0.00 sec)
/删除成功了
mysql> select * from welcome;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 2000 |
| 2 | 123 |
| 1 | 123 |
| 2 | 123 |
| 3 | 123 |
| 4 | 123 |
+----------+----------+
6 rows in set (0.00 sec)
2.删除多个表中的数据、
delete 表1,表2,…表n from 表1,表2,…表n [where 条件];
不管是单表还是多表，不加where条件将会把表中的所有记录删除，所以操作时一定要小心。
这里的操作和更新多表大致一样。
6)查询记录
1 | select * from 表名; |
1 | select 字段名 from 表名； |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21mysql> desc welcome;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
2 rows in set (1.75 sec)
/这里查username字段的记录
mysql> select username from welcome;
+----------+
| username |
+----------+
| QTFY |
| 2 |
| 1 |
| 2 |
| 3 |
| 4 |
+----------+
6 rows in set (0.00 sec)
7)查询不重复的记录
1 | select distinct field1,field2 from 表名; |
例如：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24mysql> select username from welcome;
+----------+
| username |
+----------+
| QTFY |
| 2 |
| 1 |
| 2 |
| 3 |
| 4 |
+----------+
6 rows in set (0.00 sec)
/采用查询不重复记录语句
mysql> select distinct username from welcome;
+----------+
| username |
+----------+
| QTFY |
| 2 |
| 1 |
| 3 |
| 4 |
+----------+
5 rows in set (1.63 sec)
8)条件查询
1 | = < > >= <= != 比较运算符 |
例如：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
37mysql> select * from welcome where username=4;
+----------+----------+
| username | password |
+----------+----------+
| 4 | 123 |
+----------+----------+
1 row in set, 1 warning (0.00 sec)
/ or的用法
mysql> select * from welcome where username=4 or username=3;
+----------+----------+
| username | password |
+----------+----------+
| 3 | 123 |
| 4 | 123 |
+----------+----------+
2 rows in set, 2 warnings (0.00 sec)
/and的用法
mysql> select * from welcome where username>1 and username<4 ;
+----------+----------+
| username | password |
+----------+----------+
| 2 | 123 |
| 2 | 123 |
| 3 | 123 |
+----------+----------+
3 rows in set, 1 warning (0.00 sec)
mysql> select * from welcome where username>=1 and username<=4 ;
+----------+----------+
| username | password |
+----------+----------+
| 2 | 123 |
| 1 | 123 |
| 2 | 123 |
| 3 | 123 |
| 4 | 123 |
+----------+----------+
5 rows in set, 1 warning (0.00 sec)
9)排序和限制
1 | 排序: |
例如：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/asc:由低到高
mysql> select * from welcome order by username asc;
+----------+----------+
| username | password |
+----------+----------+
| 1 | 123 |
| 2 | 123 |
| 2 | 123 |
| 3 | 123 |
| 4 | 123 |
| QTFY | 2000 |
+----------+----------+
6 rows in set (1.69 sec)
/desc:由高到底
mysql> select * from welcome order by username desc;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 2000 |
| 4 | 123 |
| 3 | 123 |
| 2 | 123 |
| 2 | 123 |
| 1 | 123 |
+----------+----------+
6 rows in set (0.00 sec)
/多个字段排序
mysql> select * from welcome order by username desc,password desc;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 2000 |
| 4 | 123 |
| 3 | 123 |
| 2 | 123 |
| 2 | 123 |
| 1 | 123 |
+----------+----------+
6 rows in set (0.00 sec)
限制:1
2在语句的最后面 加上limit 数字1,数字2 来进行查询数量的限制。
limit 数字1,数字2 数字1代表从第几条记录开启取(是从0开始的)，数字2代表取几条！
例如：1
2
3
4
5
6
7
8
9/从0条开始取一条
mysql> select * from welcome order by username desc limit 0,1
-> ;
+----------+----------+
| username | password |
+----------+----------+
| QTFY | 2000 |
+----------+----------+
1 row in set (0.00 sec)
10)聚合
sum求和
1 | select sum(字段名) from 表名; |
例如;1
2
3
4
5
6
7mysql> select sum(password) from welcome;
+---------------+
| sum(password) |
+---------------+
| 2615 |
+---------------+
1 row in set (1.66 sec)
count记录总数1
select count(*|字段名) from 表名;
例如：1
2
3
4
5
6
7mysql> select count(*) from welcome;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (1.56 sec)
max最大值1
select max(字段名) from 表名;
例如：1
2
3
4
5
6
7mysql> select max(password) from welcome;
+---------------+
| max(password) |
+---------------+
| 2000 |
+---------------+
1 row in set (0.00 sec)
min最小值1
select min(字段名) from 表名;
GROUP BY分类聚合1
select department,sum(字段名) from 表名 group by 字段名;
WITH ROLLUP分类聚合后的结果进行再汇总1
select sum(字段名) from 表名 group by 字段名 with rollup;
HAVING
1 | 注意：having和where的区别在于，having是对聚合后的结果进行条件过滤，而where是在聚合前就对记录进行过滤 ，应该尽可能的对记录进行先过滤！ |
11)表连接
显示多个表中的字段的时候即可使用表连接
内连接：选取两张表中相互匹配的记录
例如：
1 | //这样的语句可以把两张表中互相匹配的记录放在一起 |
1 | 内连接：select 表.字段名,.... from 表1名,表2名,... where 表1.字段=表2.字段; |
外连接：
仅仅选取两张相互匹配的记录，并且会选出其他不匹配的记录
左连接
概念：包含左边表中的所有记录(包括右表中没有和它匹配的记录)
例如：1
2
3
4
5
6
7
8
9
10
11
12
13mysql> select * from welcome left join Q on welcome.username=Q.id;
+----------+----------+------+------+
| username | password | id | age |
+----------+----------+------+------+
| 1 | 6 | 1 | 6 |
| 6 | 1 | 6 | 1 |
| 5 | 2 | 5 | 2 |
| 2 | 5 | 2 | 5 |
| 3 | 4 | 3 | 4 |
| 4 | 3 | 4 | 3 |
+----------+----------+------+------+
6 rows in set (0.00 sec)
/查出左边表的所有记录，不管右边有没有相匹配的
右连接
概念：包含右边表中的所有记录(包括左表中没有和它匹配的记录)
左连接和右连接是可以相互转换的！
例如：1
2
3
4
5
6
7
8
9
10
11
12mysql> select * from Q right join welcome on welcome.username=Q.id;
+------+------+----------+----------+
| id | age | username | password |
+------+------+----------+----------+
| 1 | 6 | 1 | 6 |
| 6 | 1 | 6 | 1 |
| 5 | 2 | 5 | 2 |
| 2 | 5 | 2 | 5 |
| 3 | 4 | 3 | 4 |
| 4 | 3 | 4 | 3 |
+------+------+----------+----------+
6 rows in set (0.00 sec)
12)子查询
一个查询需要另外一个查询的结果参与的时候
用于子查询的关键字:
in
语法：select from 表名 where id in(select 字段名 from 表名);
in 在..里面
注意点 in后面的子语句必须只返回一个字段
若查询结果唯一(只有一条)可以使用=代替in
not in
与in相反
exists
语法：select语句 where exists(select 语句);
exists：后面那个子语句有没有查询出记录来，如果查询出记录来返回true,否则就是false
并且查询出来的记录的具体的值是NULL也是没有关系,也是返回true.
not exits
与exists相反
1)select from 表名 where deptno in(select deptno from 表名);
2)若查询结果唯一可以使用=代替in
select * from 表名 where deptno=(select deptno from 表名 limit 限制条件);
13)记录联合
需要将两个表或者多个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示这是就需要用到记录联合
多个select 语句用
UNION或者UNION ALL隔开即可实现
区别： 前者 会将多个查询结果合并后并且进行去除重复后返回
后者 则直接合并并不去除重复
联合的条件：查询的列个数要相等
例如：
1 | //第一种去除了重复的 |
mySQL中的DDL语句和DML语句都是常用的语句，总结下来，多回顾回顾。