mysql数据库表的管理（2）

目录

一：表的字段类型二：表的字段约束三：表的创建、删除3.1 创建表3.2 删除表 四：修改表的结构4.1 查看表结构4.2 修改表结构4.2.1 增加一列字段4.2.2 删除一列字段4.2.3 修改字段类型4.2.4 修改字段名 五：表的重命名方法一：方法二： 六：表的复制6.1 只复制表结构6.2 表结构和数据一起复制 七：表数据的常规操作：7.1 insert语句7.1.1：部分列插入的方法7.1.2：全部列插入的方法7.1.3：将查询结果插入的方法 7.2 delete语句7.2.1: 删除所有记录7.2.2: 删除满足指定条件的记录 7.3 update语句7.4 select语句7.4.1: select语句的基本结构7.4.2: limit 限制显示结果7.4.3: 操作符7.4.4: group by 分组7.4.4.1 常用函数7.4.4.2 having用法 7.4.5: order by 排序7.4.6:多表查询

一：表的字段类型

二：表的字段约束

字段约束用来确保数据的完整性（可靠性和准确性）

unique 唯一性约束 值不可重复；

not null 非空约束 值不可为空；

default 默认值约束 当增加数据时没有插⼊值时，会自动插⼊默认值；

primary key 主键约束 主键约束 = 唯一性约束 + 非空约束

是一张表的代表性字段，一张表只能有一个主键。

主键可以是一个字段，也可以是多个字段（联合主键，复合主键）。

整形主键字段可以使用auto_increment（自动增长）修饰，插入时不写主键字段值，值 = 上一列值 + 1；

foreign key 外键约束 外键是另一表的主键，常用来和其他表建立联系。

外键与主键的引用类型必须一致，如果主键是int外键是char则不行。 一定要匹配主表中 引用的列 ( 所要创建的外键是主表中的主键 )。 主键和外键的字符编码必须一致，如果主表为utf8，则此表也要为utf8。

auto_increment 自增约束 标识该属性的值会自动增长。 一个表中只能有一个字段使用该约束，而且该字段必须是主键。

三：表的创建、删除

3.1 创建表

create table 表名( 字段名1 类型 [字段约束], 字段名2 类型 [字段约束], … );

例子：

mysql> create table s1(id int primary key auto_increment,name varchar(50) not null,sex varchar(10)); Query OK, 0 rows affected (0.09 sec)

3.2 删除表

方法1：drop table 表名;

举例：

显示所有的表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | s1 | | s2 | +----------------+ 2 rows in set (0.00 sec) 删除表：S2 mysql> drop table s2; Query OK, 0 rows affected (0.00 sec)

方法2：drop table if exists 表名;

四：修改表的结构

4.1 查看表结构

desc 表名;

mysql> desc s1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | sex | varchar(10) | YES | | 男 | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

4.2 修改表结构

4.2.1 增加一列字段

alter table 表名 add 字段名 字段类型 [after 列名];

举例：

MariaDB [test]> alter table s2 add sex enum('男','女'); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> desc s1; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | enum('?','?') | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

中文乱码解决：

vim /etc/my.cnf [client] default-character-set=utf8 # [ˈkærəktər] [mysqld] character-set-server=utf8 collation-server=utf8_general_ci mysql> alter table s1 add age int after name; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc s1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | age | int(11) | YES | | NULL | | | sex | varchar(10) | YES | | 男 | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)

4.2.2 删除一列字段

alter table 表名 drop 字段名;

举例：

mysql> alter table s1 drop name; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0

4.2.3 修改字段类型

alter table 表名 modify字段名 新字段类型;

举例： 将sex字段类型有varchar 改为char

mysql> alter table s1 modify sex char; Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc s1; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | age | int(11) | YES | | NULL | | | sex | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)

4.2.4 修改字段名

alter table 表名 change 旧字段名 新字段名 字段类型;

举例：

mysql> alter table s1 change sex wg_sex varchar(20); Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc s1; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | age | int(11) | YES | | NULL | | | wg_sex | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

五：表的重命名

方法一：

alter table 表名 rename [to] 新表名； 举例：

mysql> alter table student rename to students; Query OK, 0 rows affected (0.05 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | students | +----------------+ 1 row in set (0.01 sec)

方法二：

rename table 旧表名 to 新表名;

mysql> rename table students to s1; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | s1 | +----------------+ 1 row in set (0.00 sec)

六：表的复制

6.1 只复制表结构

create table 新表名 like 旧表名;

举例：

mysql> create table s2 like s1; Query OK, 0 rows affected (0.06 sec) mysql> desc s2; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | age | int(11) | YES | | NULL | | | wg_sex | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from s2; Empty set (0.00 sec)

6.2 表结构和数据一起复制

create table 新表名 select * from 旧表名;

举例：

mysql> create table s3 select * from s1; Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from s3; +----+------+--------+ | id | age | wg_sex | +----+------+--------+ | 1 | NULL | 男 | | 2 | NULL | 女 | | 3 | NULL | 女 | +----+------+--------+ 3 rows in set (0.00 sec)

七：表数据的常规操作：

7.1 insert语句

7.1.1：部分列插入的方法

insert into 表名(字段名1,字段名2,…) values(字段值1,字段值2,…) ;

举例：

mysql> insert into s1 (name,sex) values ('呵呵','女'); Query OK, 1 row affected (0.00 sec) mysql> select * from s1; +----+--------+------+ | id | name | sex | +----+--------+------+ | 1 | 华仔 | 男 | | 2 | 呵呵 | 女 | +----+--------+------+ 2 rows in set (0.00 sec)

7.1.2：全部列插入的方法

insert into 表名 values(字段值1,字段值2,字段值3,…);

举例：

mysql> insert into s1 values (3,'哈哈','女'); Query OK, 1 row affected (0.01 sec) mysql> select * from s1; +----+--------+------+ | id | name | sex | +----+--------+------+ | 1 | 华仔 | 男 | | 2 | 呵呵 | 女 | | 3 | 哈哈 | 女 | +----+--------+------+ 3 rows in set (0.00 sec)

7.1.3：将查询结果插入的方法

insert into 表名1 select 字段 from 表名2

举例：

mysql> select * from s2; Empty set (0.00 sec) mysql> insert into s2 select * from s1; Query OK, 3 rows affected, 1 warning (0.07 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql> select * from s2; +----+------+--------+ | id | age | wg_sex | +----+------+--------+ | 1 | NULL | 男 | | 2 | NULL | 女 | | 3 | NULL | 女 | +----+------+--------+ 3 rows in set (0.00 sec)

7.2 delete语句

7.2.1: 删除所有记录

delete from 表名;

举例：

mysql> delete from s1; Query OK, 3 rows affected (0.04 sec) mysql> select * from s1; Empty set (0.00 sec)

7.2.2: 删除满足指定条件的记录

delete from 表名 where 条件;

举例：

mysql> select * from s2; +----+------+--------+ | id | age | wg_sex | +----+------+--------+ | 1 | NULL | 男 | | 2 | NULL | 女 | | 3 | NULL | 女 | | 4 | 18 | NULL | +----+------+--------+ 4 rows in set (0.00 sec) mysql> delete from s2 where wg_sex='女'; Query OK, 2 rows affected (0.05 sec) mysql> select * from s2; +----+------+--------+ | id | age | wg_sex | +----+------+--------+ | 1 | NULL | 男 | | 4 | 18 | NULL | +----+------+--------+ 2 rows in set (0.00 sec)

7.3 update语句

update 表名 set 字段名=‘新的值’ [where 条件];

举例：

mysql> select * from s2; +----+------+--------+ | id | age | wg_sex | +----+------+--------+ | 1 | NULL | 男 | | 4 | 18 | NULL | +----+------+--------+ 2 rows in set (0.00 sec) mysql> update s2 set age='28' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from s2; +----+------+--------+ | id | age | wg_sex | +----+------+--------+ | 1 | 28 | 男 | | 4 | 18 | NULL | +----+------+--------+ 2 rows in set (0.00 sec)

7.4 select语句

7.4.1: select语句的基本结构

select 查询字段 from 表名 where 条件;

其中 * 代表所有字段

举例：

mysql> select * from student; +----+-----------+------+------+ | id | name | sex | age | +----+-----------+------+------+ | 1 | huazai | 男 | 18 | | 2 | huazai007 | 男 | 28 | | 3 | 007 | 女 | 38 | | 4 | hehe | 女 | 48 | +----+-----------+------+------+ 4 rows in set (0.00 sec)

7.4.2: limit 限制显示结果

举例： 显示2条数据

mysql> select * from student limit 2; +----+-----------+------+------+--------+ | id | name | sex | age | market | +----+-----------+------+------+--------+ | 1 | huazai | 男 | 18 | 晋南 | | 2 | huazai007 | 男 | 28 | 晋南 | +----+-----------+------+------+--------+ 2 rows in set (0.00 sec)

7.4.3: 操作符

操作符描述实例=等号，检测两个值是否相等，如果相等返回true(A = B) 返回false。<>,!=不等于，检测两个值是否相等，如果不相等返回true(A != B) 返回 true。>大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true(A > B) 返回false。<小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true(A < B) 返回 true。>大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true(A >= B) 返回false。<=小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true(A <= B) 返回 true。like模糊匹配like ‘张%’and表示和id=2 and name=‘张三’or表示或id=2 or name=‘张三’between选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用WHERE 字段1 BETWEEN value1 AND value2in匹配到相同值所在的行显示出来age in (18,28,38)not in取反age not in (18,28)

举例：（=）

mysql> select * from student where name='huazai'; +----+--------+------+------+ | id | name | sex | age | +----+--------+------+------+ | 1 | huazai | 男 | 18 | +----+--------+------+------+ 1 row in set (0.00 sec)

举例：（<）

mysql> select * from student where id<3; +----+-----------+------+------+ | id | name | sex | age | +----+-----------+------+------+ | 1 | huazai | 男 | 18 | | 2 | huazai007 | 男 | 28 | +----+-----------+------+------+ 2 rows in set (0.00 sec)

举例：（like模糊匹配）

mysql> select * from student where name like 'he%'; +----+------+------+------+ | id | name | sex | age | +----+------+------+------+ | 4 | hehe | 女 | 48 | +----+------+------+------+ 1 row in set (0.00 sec)

举例：（and 多个条件都符合才会显示！）

mysql> select * from student where name like 'hua%' and age=18; +----+--------+------+------+ | id | name | sex | age | +----+--------+------+------+ | 1 | huazai | 男 | 18 | +----+--------+------+------+ 1 row in set (0.00 sec)

举例：（or 或；只要符合其中 一个条件就显示）

mysql> select * from student where name='hehe' or age=18; +----+--------+------+------+ | id | name | sex | age | +----+--------+------+------+ | 1 | huazai | 男 | 18 | | 4 | hehe | 女 | 48 | +----+--------+------+------+ 2 rows in set (0.00 sec)

举例：（between and）

mysql> select * from student where age between 18 and 38; +----+-----------+------+------+ | id | name | sex | age | +----+-----------+------+------+ | 1 | huazai | 男 | 18 | | 2 | huazai007 | 男 | 28 | | 3 | 007 | 女 | 38 | +----+-----------+------+------+ 3 rows in set (0.00 sec)

举例： （in 匹配到相同值 所在的行显示出来）

mysql> select * from student where age in (18,28); +----+-----------+------+------+ | id | name | sex | age | +----+-----------+------+------+ | 1 | huazai | 男 | 18 | | 2 | huazai007 | 男 | 28 | +----+-----------+------+------+ 2 rows in set (0.00 sec)

7.4.4: group by 分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

7.4.4.1 常用函数

函数描述max()最大值min()最小值sum()求和avg()平均数count()统计个数

表student 新增字段：market（市场部）

mysql> alter table student add market varchar(20); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | sex | varchar(10) | YES | | 男 | | | age | int(11) | YES | | NULL | | | market | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)

原始数据如下：

mysql> select * from student ; +----+-----------+------+------+--------+ | id | name | sex | age | market | +----+-----------+------+------+--------+ | 1 | huazai | 男 | 18 | 晋南 | | 2 | huazai007 | 男 | 28 | 晋南 | | 3 | 007 | 女 | 38 | 河北 | | 4 | hehe | 女 | 48 | 河南 | | 5 | 张三 | 女 | 20 | 晋南 | | 6 | 李四 | 男 | 20 | 河南 | | 7 | 王五 | 男 | 21 | 河北 | +----+-----------+------+------+--------+ 7 rows in set (0.00 sec)

举例1：[group by+count()] 统计每个市场部的男生有多少个？

mysql> select market,count(*) as num from student where sex='男' group by market; +--------+-----+ | market | num | +--------+-----+ | 晋南 | 2 | | 河北 | 1 | | 河南 | 1 | +--------+-----+ 3 rows in set (0.00 sec)

举例2：[group by+max()] 显示每个市场部男学员的最大年龄

mysql> select market,max(age) as max,name from student where sex='男' group by market; +--------+------+--------+ | market | max | +--------+------+--------+ | 晋南 | 28 | | | 河北 | 21 | | | 河南 | 20 | | +--------+------+--------+ 3 rows in set (0.00 sec)

举例3:[group by+min()]

显示每个市场部女学员的最小年龄

mysql> select market,min(age) as min,name from student where sex='女' group by market; +--------+------+--------+ | market | min | | +--------+------+--------+ | 晋南 | 20 | | | 河北 | 38 | | | 河南 | 48 | | +--------+------+--------+ 3 rows in set (0.00 sec)

举例4：[group by+avg()] 显示每个市场部男学员的平均年龄。

mysql> select market,avg(age) as avg from student where sex='男' group by market; +--------+---------+ | market | avg | +--------+---------+ | 晋南 | 23.0000 | | 河北 | 21.0000 | | 河南 | 20.0000 | +--------+---------+ 3 rows in set (0.00 sec)

扩展（子查询） 查询每个市场部年龄最大的学员名称

MariaDB [test]> select t1.name,t2.max_age,t1.market from s2 as t1,(select market,max(age) as max_age from s2 group by market) as t2 where t1.age=t2.max_age and t1.market=t2.market order by max_age desc; +-----------+---------+--------+ | name | max_age | market | +-----------+---------+--------+ | 老大 | 100 | 内蒙 | | 老色皮 | 88 | 春峰 | | 李狗蛋 | 38 | 黑北 | | 乔治 | 38 | 苏皖 | | 佩佩 | 30 | 晋南 | | 橙子 | 18 | 鄂中 | +-----------+---------+--------+ 6 rows in set (0.00 sec)

7.4.4.2 having用法

关键字having，用来对分组的结果进行筛选

where 和having的区别： where：是聚合前做筛选 having：是聚合后做筛选

举例：[group by+having] 显示学员平均年龄大于30岁的市场部名称。

mysql> select market,avg(age) as avg from student group by market having avg>30; +--------+---------+ | market | avg | +--------+---------+ | 河南 | 34.0000 | +--------+---------+ 1 row in set (0.00 sec)

举例：[group by+having ] 显示男学员人数大于1的市场部名称。

mysql> select market,count(1) as num from student where sex='男' group by market having num >1; +--------+-----+ | market | num | +--------+-----+ | 晋南 | 2 | +--------+-----+ 1 row in set (0.00 sec)

7.4.5: order by 排序

举例： 显示晋南市场部的学员名称，性别，年龄，并按照年龄排序（默认升序）

mysql> select name,sex,age from student where market='晋南' order by age; +-----------+------+------+ | name | sex | age | +-----------+------+------+ | huazai | 男 | 18 | | 张三 | 女 | 20 | | huazai007 | 男 | 28 | +-----------+------+------+ 3 rows in set (0.00 sec)

举例：

显示晋南市场部的学员名称，性别，年龄，并按照年龄排序（desc降序）

mysql> select name,sex,age from student where market='晋南' order by age desc; +-----------+------+------+ | name | sex | age | +-----------+------+------+ | huazai007 | 男 | 28 | | 张三 | 女 | 20 | | huazai | 男 | 18 | +-----------+------+------+ 3 rows in set (0.00 sec)

举例：

统计每个市场部的人数，并排序

mysql> select market,count(1) as num from student group by market order by num; +--------+-----+ | market | num | +--------+-----+ | 河北 | 2 | | 河南 | 2 | | 晋南 | 3 | +--------+-----+ 3 rows in set (0.00 sec)

7.4.6:多表查询