连接到数据库

在上一篇博客：mysql安装配置及卸载中，我提到了设置用户名及用户密码。

现在我们就用以下命令登录到数据库：

mysql -u 用户名 -p密码
#-p与密码之间不能有空格

执行结果：

对数据库操作

数据库的操作主要有创建、显示、选择和删除。

创建数据库-create

#创建一个数据库

CREATE DATABASE 数据库名；

执行结果：

显示数据库-show

#显示所有的数据库

SHOW DATABASES;
#注意 DATABASES必须加s，否则报错

执行结果：

可以看到，我们刚刚创建的college数据库就在表中。

选择数据库-use

在最开始连接到mysql的时候，是没有任何数据库能供你使用的。这个时候，你对表的操作就会报错，因为mysql不知道你要操作的是哪个数据库下的表。

我们需要先选择数据库：

# 选择数据库

USE 数据库名;

执行结果：

删除数据库-drop

#删除数据库

DROP DATABASE 数据库名;

这里我就不执行了，重新建数据库蛮麻烦的。

对表操作

创建表-create table

#创建表

CREATE TABLE 表名(列名,列类型);

这里比较麻烦需要详细举个例子:

mysql> CREATE TABLE IF NOT EXISTS student(
    -> stu_id int auto_increment,
    -> stu_name varchar(10) not null,
    -> stu_class int,
    -> stu_score int,
    -> primary key(stu_id)
    -> );

#第一行 if not exists指只有当表student不存在才创建表，避免重复创建
#第二行 auto_increment自动递增，只适用于整型
#第三行 varchar(10)创建一个根据输入字符串长度而确定的数据，最大长度为10
# not null指的是这样数据必须有输入，不能为空
#第六行 指定主键为stu_id 

执行结果：

主键-primary key

刚刚我们设置了stu_id为主键，主键值必须唯一。

注意：
主键如果使用单个列，则其值必须唯一
如果是多个列组合成主键，则这个组合必须唯一

一般使用如下命令设置主键：

#设置单个列为主键

PRIMARY KEY(列名);

#设置多个列的组合为主键

PRIMARY KEY(列1,列2);

自动递增-auto_increment

auto_increment会高速mysql，本列每增加一行时自动增量。每次执行一个INSERT操作时，mysql自动对该列增量，赋予下一个可用的值。

每个表只允许有一个auto_increment修饰的列，而且其必须被索引。

指定默认值

当我们插入一行数据的时候，如果我们懒得给其赋值，可以在创建表的时候，指定一个默认值，让mysql使用默认值填充。

#给行赋予默认值

...
列名 类型 DEFAULT 默认值
...

例如：
#指定默认分数60
stu_score int not null default 60	
#指定默认名Trump
stu_name varchar(10) default 'trump'

修改表-alter table

为了更新表的定义，我们可以使用ALTER TABLE语句。

但是考虑一个问题：表里面已经有数据了，我们再更新表中的定义，对原有的数据会造成怎样的冲击呢？

所以！一般来说，表一旦创建好，就不要去修改了。设计者应该花费更多的时间在表的设计上！

#更改表结构（表必须存在）

ALTER TABLE 表名 操作;

#操作 ADD
ALTER TABLE 表名 ADD 列名 列属性;
#例子：
ALTER TABLE student ADD stu_chinese varchar(20);

#操作 DROP 
ALTER TABLE 表名 DROP COLUMN 列名;
#例子：
ALTER TABLE student DROP COLUMN stu_chinese;

#操作 FORTIGN KEY... REFERENCES...
ALTER TABLE 表名 ADD CONSTRAINT 列名 FOREIGN KEY(外键列名) REFERENCES 其他表名（外键列名）
#讲解 ADD CONSTRAINT 添加主键约束
#例子：
ALTER TABLE student ADD CONSTRAINT stu_class FOREIGN KEY(class_num) REFERENCES class(class_num);

删除表-drop table

#删除表

DROP TABLE 表名;

重命名表-rename table

#重命名表

RENAME TABLE 表名 TO 新表名;

对数据操作

表中查询数据

想要在表中去检索数据，我们就需要使用SELECT关键字，其必须给出两个信息：想选择什么和从什么地方选择。

一般SELECT用于检索数据有两种方式：

• 检索全部数据
• 检索特定数据

检索全部数据：

#检索全部数据

SELECT * FROM 表名;

检索特定数据：

#检索满足条件的数据

SELECT 列1，列2.... FROM 表名 WHERE 判断条件;

表中插入数据-insert

INSERT是用来添加信息行到数据库表中的。一般有以下几种插入方式：

• 插入完整的行
• 插入行的一部分
• 插入多行
• 插入查询的结果

插入完整的行：

# 插入完整的行

INSERT INTO 表名 VALUES(信息1，信息2，....);
#例子：
mysql> INSERT INTO student VALUES(
    -> 1,
    -> 'trump',
    -> 001,
    -> 89);
Query OK, 1 row affected (0.08 sec)
#注意 字符串必须要有' '

然后我们再查询，执行结果：

插入多行：
INSERT不仅可以单行插入，还可以多行插入：

#INSERT插入多行

INSERT INTO 表名 VALUES
(信息1，信息2，....)，
(信息1，信息2，....);
#例子：
mysql> INSERT INTO student VALUES
    -> (null,'zhangsan',3,96),
    -> (null,'lisi',3,89);
Query OK, 2 rows affected (0.29 sec)
Records: 2  Duplicates: 0  Warnings: 0
#注意 因为第一列设置的auto_increment，所以赋值null就可以实现自动增长

查询执行结果：

插入检索出的数据：
INSERT除了可以给编辑噢插入一个指定列值的行，还可以将一条SELECT语句的结果插入到表中，即INSERT SELECT。

# INSERT INTO

INSERT INTO 表名(列1，列2....)SELECT (列1，列2....) FROM 表名

更新表中信息-update&&delete

更新数据：
为了更新表中数据，可以使用UPDATE语句，一般采用以下两种方式更新：

• 更新表中特定行
• 更新表中所有行

# UPDATA

# 更新一个列
UPDATE 表名  SET 列名 = 新值 WHERE 判断条件;
# 更新多个列
UPDATE 表名  SET 列1 = 值1,列2 = 值2,.... WHERE 判断条件;

注意：
一般来说，在更新多行数据时，一旦一行数据发生错误，则所有更新都将失败。
为了保证发生错误节点前面的数据正常更新，可以使用IGNORE关键字。
UPDATE IGNORE ....;

删除数据：
删除DELETE跟刚刚的更新一样，也有两种方式：

• 更新表中特定行
• 更新表中所有行

# DELETE

# 删除满足条件的行
DELETE 表名 WHERE 判断条件;

注意:
如果想要删除表中所有数据，一般来说使用TRUNCATE关键字比DELETE来的更快。
TRUNCATE 表名;

对用户操作

创建用户及授予权限-create&&grant

但是，root用户是最高权限用户，拥有对mysql的完全控制。

有些时候，我们想要限制用户的权限或者用其他用户来连接到数据库。就需要创建一个用户，并且授予不同权限：

#创建一个用户（必须在root用户或者拥有ALL权限的用户）
CREATE USER '用户名'@'host' IDENTIFIED BY '密码';

#host 指的是用户在哪台主机登录，本地的话可以用localhost，如果是远程连接，使用通配符%

执行结果：

用户创建完之后，处于一个无权限的状态。这个时候就需要给用户赋予相应的权限

# 赋予权限 （必须在root用户或者拥有ALL权限的用户）
GRANT 权限  ON 数据库（表） TO '用户名'@'host';

#权限 有SELECT、UPDATA、INSERT、ALL等
#数据库（表） 把对相应的数据库（表）操作权限授予用户，可以用*.*代表所有数据库和表

执行结果：

现在，用户shenming有了对所有数据库的所有权限了。我们可以登录到shenming用户再对mysql进行操作。

查看用户-select

mysql中用户的信息都保存在mysql数据库的user表中，需要ALL权限才能查看。

#查看用户（必须在root用户或者拥有ALL权限的用户）

SELECT user,host FROM mysql.user;

执行结果：

修改用户信息-update

修改用户名

刚刚说了，用户信息都在mysql数据库的user表中，我们只需要对这个表里的信息进行修改就可以。

#修改用户名（必须在root用户或者拥有ALL权限的用户）

UPDATE USER SET user='新用户名' where user='用户名';

执行结果：

但是这个时候还是没有保存的，需要执行以下语句：

#刷新保存

flush privileges;

执行结果：

这个时候我们再查看user表，shenming用户就会被替换成shenming1

修改用户密码

修改密码的方式有很多，我先介绍的是登录数据库然后修改密码。

#修改密码（必须在root用户或者拥有ALL权限的用户）

UPDATE user SET authentication_string=password('新密码') WHERE user='用户名';
#注意 mysql版本是5.7 版本之前的需要把authentication_string换成password

执行结果：

这时，我们用原来的密码123456已经登录不上去了。

这个时候，我们再把密码改回来。
使用不登录数据库的方法：

#修改密码（必须在root用户或者拥有ALL权限的用户）

mysqladmin -u shenming -p旧密码 password 新密码
#注意 -p和旧密码之间没有空格！

执行结果：

删除用户-drop

#删除用户（必须在root用户或者拥有ALL权限的用户）

DROP USER '用户名'@'host';

执行结果：

这个时候，就删除了用户shenming。

参考文献

[1] Ben Forta.MySQL必知必会.人民邮电出版社.2009.01.