数据库操作

1.9.2 创建数据库

语法：create database [if not exists] `数据名` [字符编码]
1

创建数据库：

mysql> create database stu;
Query OK, 1 row affected (0.09 sec)
1
2

如果创建的数据库已存在，就会报错

mysql> create database stu;
ERROR 1007 (HY000): Can't create database 'stu'; database exists
1
2

解决：创建数据库的时候判断一下数据库是否存在，如果不存在再创建

mysql> create database if not exists stu;
Query OK, 1 row affected, 1 warning (0.00 sec)
1
2

如果数据库名是关键字和特殊字符要报错

解决：在特殊字符、关键字行加上反引号

mysql> create database `create`;
Query OK, 1 row affected (0.05 sec)
1
2

多学一招：为了创建数据库时万无一失，我们可以在所有的数据库名上加上反引号
1

创建数据库的时候可以指定字符编码

mysql> create database teacher charset=gbk;
Query OK, 1 row affected (0.01 sec)
gbk		简体中文
gb2312：	简体中文
utf8：	通用字符编码
1
2
3
4
5

脚下留心：创建数据库如果不指定字符编码，默认和MySQL服务器的字符编码是一致的。
1

1.9.3 删除数据库

语法：drop database [if exists] 数据库名
1

删除数据库

mysql> drop database teacher;
Query OK, 0 rows affected (0.00 sec)
1
2

如果删除的数据库不存在，会报错

mysql> drop database teacher;
ERROR 1008 (HY000): Can't drop database 'teacher'; database doesn't exist
mysql>
1
2
3

解决：删除之前判断一下，如果存在就删除

mysql> drop database if exists teacher;
Query OK, 0 rows affected, 1 warning (0.00 sec)
1
2

1.9.4 显示创建数据库的SQL语句

语法：show create database 数据库名
1

1.9.5 修改数据库

修改数据库的字符编码

语法：

alter database 数据库名 charset=字符编码
1

例题

mysql> alter database teacher charset=utf8;
Query OK, 1 row affected (0.00 sec)
1
2

1.9.6 选择数据库

语法：

use 数据库名
1

选择数据库

mysql> use stu;
Database changed
1
2

1.10 表的操作

1.10.1 显示所有表

语法：

show tables
1

1.10.2 创建表

语法：

create table [if not exists] 表名(
    字段名 数据类型 [null|not null] [auto_increment] [primary key] [comment],
    字段名 数据类型 [default]…
)engine=存储引擎
1
2
3
4

单词

null | not null   	空|非空
default	          	默认值
auto_increment    	自动增长
primary key       	主键
comment           	备注
engine           	引擎   innodb  myisam  memory  引擎是决定数据存储的方式
1
2
3
4
5
6

创建简单的表

mysql> create database itcast;
Query OK, 1 row affected (0.00 sec)

mysql> use itcast;
Database changed
mysql> show tables;
Empty set (0.05 sec)

# 创建表
mysql> create table stu(
   -> id int,
   -> name varchar(30)
   -> );
Query OK, 0 rows affected (0.13 sec)
# 查看创建的表
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| stu              |
+------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

创建复杂的表

mysql> set names gbk;   # 设置字符编码
Query OK, 0 rows affected (0.05 sec)

mysql> create table if not exists teacher(
   -> id int auto_increment primary key comment '主键',
   -> name varchar(20) not null comment '姓名',
   -> phone varchar(20) comment '电话号码',
   -> `add` varchar(100) default '地址不详' comment '地址'
   -> )engine=innodb;
Query OK, 0 rows affected (0.09 sec)

1
2
3
4
5
6
7
8
9
10
11

多学一招：create table 数据库名.表名，用于给指定的数据库创建表

mysql> create table data.stu(  #给data数据库中创建stu表
   -> id int,
   -> name varchar(10));
Query OK, 0 rows affected (0.00 sec)
1
2
3
4

1.10.3 显示创建表的语句

语法：

show create table 表名
1

显示创建teacher表的语句

mysql> show create table teacher;
+---------+--------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------+
| Table   | Create Table


       |
+---------+--------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `add` varchar(100) DEFAULT '地址不详' COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                         |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

将两个字段竖着排列 show create table 表名\G

mysql> show create table teacher\G;
*************************** 1. row ***************************
      Table: teacher
Create Table: CREATE TABLE `teacher` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(20) NOT NULL COMMENT '姓名',
 `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
 `add` varchar(100) DEFAULT '地址不详' COMMENT '地址',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

1.10.4 查看表结构

语法：

desc[ribe] 表名
1

查看teacher表的结构

mysql> describe teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
+-------+--------------+------+-----+----------+----------------+
4 rows in set (0.08 sec)

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
+-------+--------------+------+-----+----------+----------------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1.10.5 删除表

语法：

drop table [if exists] 表1，表2,… 
1

删除表

mysql> drop table stu;
Query OK, 0 rows affected (0.08 sec)
1
2

如果删除一个不存在的表就会报错，删除的时候可以判断一下，存在就删除。

mysql> drop table stu;
ERROR 1051 (42S02): Unknown table 'stu'

mysql> drop table if exists stu;
Query OK, 0 rows affected, 1 warning (0.00 sec)
1
2
3
4
5

可以一次删除多个表

mysql> drop table a1,a2;
Query OK, 0 rows affected (0.00 sec)
1
2

1.10.6 修改表

语法：alter table 表名
1

1、添加字段：alter table 表名add [column] 字段名 数据类型 [位置] //column===列

例题一：添加字段

mysql> alter table teacher add age int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| age   | int(11)      | YES  |     | NULL     |                |
+-------+--------------+------+-----+----------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

例题二：在第一个位置上添加字段

mysql> alter table teacher add email varchar(30) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| email | varchar(30)  | YES  |     | NULL     |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| age   | int(11)      | YES  |     | NULL     |                |
+-------+--------------+------+-----+----------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

例题三：在指定的字段后添加字段

mysql> alter table teacher add sex varchar(2) after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| email | varchar(30)  | YES  |     | NULL     |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| sex   | varchar(2)   | YES  |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| age   | int(11)      | YES  |     | NULL     |                |
+-------+--------------+------+-----+----------+----------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2、删除字段：alter table 表 drop [column] 字段名

mysql> alter table teacher drop email;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

3、修改字段(改名改类型)：alter table 表 change [column] 原字段名 新字段名 数据类型 …

将字段sex改为xingbie，数据类型为int

mysql> alter table teacher change sex xingbie int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

4、修改字段（不改名）:alter table 表 modify 字段名 字段属性…

将性别的数据类型改为varchar(2)

mysql> alter table teacher modify xingbie varchar(2);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

5、修改引擎：alter table 表名 engine=引擎名----存储数据的方式

mysql> alter table teacher engine=myisam;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

6、修改表名：alter table 表名 rename to 新表名

mysql> alter table teacher rename to stu;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| stu              |
+------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

1.10.5 复制表

语法一：create table 新表 select 字段 from 旧表
1

特点：不能复制父表的主键，能够复制父表的数据

mysql> create table stu1 select * from stu;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from stu1;    # 查看数据复制到新表中
+----+------+------+-------+
| id | name | addr | score |
+----+------+------+-------+
|  1 | rose | 上海    |    88 |
+----+------+------+-------+
1 row in set (0.00 sec)

mysql> desc stu1;    #  主键没有复制
+-------+-------------+------+-----+----------+-------+
| Field | Type        | Null | Key | Default  | Extra |
+-------+-------------+------+-----+----------+-------+
| id    | int(11)     | NO   |     | 0        |       |
| name  | varchar(20) | NO   |     | NULL     |       |
| addr  | varchar(50) | YES  |     | 地址不详        |       |
| score | int(11)     | YES  |     | NULL     |       |
+-------+-------------+------+-----+----------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

语法二：create table 新表 like 旧表
1

特点：只能复制表结构，不能复制表数据

Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu2;   # 数据没有复制
Empty set (0.01 sec)

mysql> desc stu2;   # 主键复制了
+-------+-------------+------+-----+----------+----------------+
| Field | Type        | Null | Key | Default  | Extra          |
+-------+-------------+------+-----+----------+----------------+
| id    | int(11)     | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20) | NO   |     | NULL     |                |
| addr  | varchar(50) | YES  |     | 地址不详        |                |
| score | int(11)     | YES  |     | NULL     |                |
+-------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1.11 数据操作

创建测试表

mysql> create table stu(
    -> id int auto_increment primary key comment '主键',
    -> name varchar(20) not null,
    -> addr varchar(50) default '地址不详',
    -> score int comment '成绩'
    -> );
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7

1.11.1 插入数据

插入一条数据

语法：insert into 表名 (字段名, 字段名,…) values (值1, 值1,…)
1

例题一：插入数据

mysql> insert into stu (id,name,addr,score) values (1,'tom','上海',88);
Query OK, 1 row affected (0.11 sec)
1
2

例题二：插入的字段可以和表的字段顺序不一致。值的顺序必须和插入字段的顺序一致。

mysql> insert into stu (name,score,addr,id) values ('berry',77,'北京',2);
Query OK, 1 row affected (0.00 sec)
1
2

例题三：可以插入部分字段，但是，非空字段必须插入

mysql> insert into stu (id,name,addr) values (3,'ketty','上海');
1

例题四：自动增长字段不用插入，数据库会自动插入增长的数字

mysql> insert into stu (name,addr) values ('rose','北京');
Query OK, 1 row affected (0.00 sec)
1
2

例题五：自动增长列的值插入null即可

mysql> insert into stu (id,name,addr,score) values (null,'李白','上海',66);
Query OK, 1 row affected (0.00 sec)
1
2

例题六：插入值的顺序和个数与表字段的顺序和个数一致，插入的字段可以省略

mysql> insert into stu values (null,'杜甫','北京',null);
Query OK, 1 row affected (0.00 sec)
1
2

例题七：通过default关键字插入默认值

mysql> insert into stu values (null,'李清照',default,66);
1

脚下留心：
1、插入字段的顺序与值的顺序必须一致
1
2

插入多条数据

mysql> insert into stu values (null,'辛弃疾',default,66),(null,'岳飞','河南',77);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
1
2
3

1.11.2 更新数据

语法：

update 表名 set 字段=值 [where 条件] 
1

例题一：将1号学生的地址改成山东

mysql> update stu set addr='山东' where id=1
1

例题二：将ketty的成绩改为99

mysql> update stu set score=99 where name='ketty';
1

例题三：将berry地址改成上海，成绩改成66

mysql> update stu set addr='上海',score=66 where name='berry';
1

例题四：将上海的学生成绩改为60

mysql> update stu set score=60 where addr='上海';
1

例题五：条件可以省略，如果省略，更改所有数据（将所有数据的地址改为湖南，成绩改为70）

mysql> update stu set addr='湖南',score=70;
1

例题六：将2、3的学生成绩改为65

mysql> update stu set score=65 where id=2 or id=3;
1

1.11.3 删除数据

语法

delete from 表名 [where 条件] 
1

例题一：删除学号是1号的学生

mysql> delete from stu where id=1;
1

例题二：删除成绩小于等于65分的

mysql> delete from stu where score<=65;
1

例题三：删除表中所有记录

mysql> delete from stu;
1

1.11.4 清空表

语法：

truncate table 表名
1

例题

mysql> truncate table stu;
Query OK, 0 rows affected (0.00 sec)
1
2

脚下留心：delete from 表和truncate table 表区别？
delete from 表：遍历表记录，一条一条的删除
truncate table：将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高。
1
2
3

1.11.5查询表

语法：

select 列名 from 表
1

例题：

mysql> select name,score from stu;
+------+-------+
| name | score |
+------+-------+
| rose |    88 |
+------+-------+
1 row in set (0.00 sec)

mysql> select id,name,addr,score from stu;
+----+------+------+-------+
| id | name | addr | score |
+----+------+------+-------+
|  1 | rose | 上海    |    88 |
+----+------+------+-------+
1 row in set (0.00 sec)

mysql> select * from stu;  # *表示所有字段
+----+------+------+-------+
| id | name | addr | score |
+----+------+------+-------+
|  1 | rose | 上海    |    88 |
+----+------+------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

1.12 SQL分类

DDL（data definition language）数据库定义语言CREATE、ALTER、DROP、SHOW

DML（data manipulation language）数据操纵语言SELECT、UPDATE、INSERT、DELETE

DCL（Data Control Language）数据库控制语言,是用来设置或更改数据库用户或角色权限的语句

1.13 数据表的文件介绍

一个数据库对应一个文件夹

一个表对应一个或多个文件

引擎是myisam，一个表对应三个文件

引擎是innodb,一个表对应一个表结构文件

所有的innodb引擎的数据统一的存放在data\ibdata1文件中。如果数据量很大，MySQL会自动的创建ibdata2，ibdata3，…，目的就是为了便于管理。

引擎是memory，数据存储在内存中，重启服务数据丢失，但是读取速度非常快。

1.14 字符集

字符集：字符在保存和传输时对应的二进制编码集合。

创建测试数据库

mysql> create table stu(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5

插入中文报错

---

分析原因：

客户端通过GBK发送的命令

但是，服务用utf8解释命令

设置服务器，用gbk字符编码接受客户端发来的命令

测试：插入中文，成功

查询数据，发现数据乱码

原因：以utf返回的结果，客户端用gbk来接受

解决：服务器用gbk返回数据

再次测试，查询数据

总结：客户端编码、character_set_client、character_set_results三个编码的值一致即可操作中文。

多学一招：我们只要设置“set names 字符编码”，就可以更改character_set_client、character_set_results的值。