连接 MySQL

1
mysql -h 主机地址 -u 用户名 -p 用户密码

连接到本机上的 MySQL

首先打开 DOS 窗口,然后进入目录 mysql\bin ,再键入命令 mysql -u root -p 回车后提示你输密码。注意用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密码。

如果刚安装好 MySQL,超级用户 root 是没有密码的。直接回车即可进入到 MySQL 中了,MySQL 的提示符是: mysql>

连接到远程主机上的 MySQL

假设远程主机的 IP 为:110.110.110.110,用户名为 root,密码为 abcd123。则键入以下命令:

1
mysql -h 110.110.110.110 -u root -p abcd123;

uroot 之间可以不用加空格,其它也一样。

退出 MySQL 命令。

1
exit

修改密码

1
mysqladmin -u 用户名 -p 旧密码 password 新密码

  1. 给 root 加个密码 ab12。

    首先在 DOS 下进入目录 mysql\bin。然后键入以下命令 mysqladmin -u root -password ab12

    :因为开始时 root 没有密码,所以 -p 旧密码 一项就可以省略了。

  1. 再将 root 的密码改为 def345。

    1
    mysqladmin -u root -p ab12 password def345

  2. 增加新用户

    注意:和上面不同,下面的因为是 MySQL 环境中的命令,所以后面都带一个分号作为命令结束符。

    1
    GRANT SELECT ON 数据库.* TO 用户名@登录主机 identified BY "密码"

    1. 增加一个用户 test1 密码为 abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用 root 用户连入 MySQL,然后键入以下命令:

    1
    GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO test1@"%" Identified BY "abc";

    但增加的用户是十分危险的,你想如某个人知道 test1 的密码,那么他就可以在 internet 上的任何一台电脑上登录你的 MySQL 数据库并对你的数据可以为所欲为了,解决办法见 2。

    1. 增加一个用户 test2 密码为 abc,让他只可以在 localhost 上登录,并可以对数据库 mydb 进行查询、插入、修改、删除的操作(localhost 指本地主机,即 MySQL 数据库所在的那台主机)。

    这样用户即使用知道 test2 的密码,他也无法从 internet 上直接访问数据库,只能通过 MySQL 主机上的 web 页来访问了。

    1
    GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO test2@localhost test2@localhost identified BY "abc";

    如果你不想 test2 有密码,可以再打一个命令将密码消掉。

    1
    GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO test2@localhost test2@localhost identified BY "";

数据库

创建数据库

  1. 建立一个数据库

    1
    CREATE DATABASE <数据库名>;

  2. 创建数据库并分配用户

    1
    2
    3
    4
    5
    CREATE DATABASE <数据库名>;

    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON 数据库名.* TO 数据库名@登录主机 IDENTIFIED BY '密码';

    SET PASSWORD FOR '数据库名'@'登录主机' = '密码';

    依次执行 3 个命令完成数据库创建。

    注意:“密码”和“数据库”是用户自己需要设置的。

显示数据库

1
SHOW DATABASES;

注意:为了不再显示的时候乱码,要修改数据库默认编码。以下以 GBK 编码页面为例进行说明:

  1. 修改 MySQL 的配置文件。

    my.ini 文件里面修改 default-character-set=gbk

  2. 代码运行时修改。

  • Java 代码:

    1
    jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk

  • PHP 代码:

    1
    header("Content-Type:text/html;charset=gb2312");

  • C 语言代码:

    1
    int mysql_set_character_set( MySQL * mysql, char * csname);

    该函数用于为当前连接设置默认的字符集。字符串 csname 指定了 1 个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与 SET NAMES 语句类似,但它还能设置 charset 的值,从而影响了由 mysql_real_escape_string() 设置的字符集。

删除数据库

1
DROP DATABASE <数据库名>;

  1. 删除一个已经确定存在的数据库

    1
    2
    mysql> DROP DATABASE drop_database;
    Query OK, 0 rows affected (0.00 sec)

  2. 删除一个不确定存在的数据库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> DROP DATABASE drop_database;
    ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
    //发生错误,不能删除 'drop_database' 数据库,该数据库不存在。

    mysql> DROP DATABASE IF EXISTS drop_database;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    //产生一个警告说明此数据库不存在

    mysql> CREATE DATABASE drop_database;
    Query OK, 1 row affected (0.00 sec)

    mysql> DROP DATABASE IF EXISTS drop_database;
    Query OK, 0 rows affected (0.00 sec)
    //IF EXISTS 判断数据库是否存在,不存在也不产生错误

连接数据库

1
mysql> USE <数据库名>;

屏幕提示:Database changed

USE 语句可以通告 MySQL 把 db_name 数据库作为默认(当前)数据库使用,用于后续语句。

该数据库保持为默认数据库,直到语段的结尾,或者直到发布一个不同的 USE 语句:

1
2
3
4
mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # SELECT COUNT(*) FROM db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable; # SELECT COUNT(*) FROM db2.mytable

使用 USE 语句为一个特定的当前的数据库做标记,不会阻碍您访问其它数据库中的表。

下面的例子可以从 db1 数据库访问作者表,并从 db2 数据库访问编辑表:

1
2
mysql> USE db1;
mysql> SELECT author_name, editor_name FROM author, db2.editor WHERE author.editor_id = db2.editor.editor_id;

当前选择的数据库

1
2
3
4
5
6
7
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)

MySQL 中 SELECT 命令类似于其他编程语言里的 print 或者 write,你可以用它来显示一个字符串、数字、数学表达式的结果等等。

  1. 显示 MySQL 的版本

    1
    2
    3
    4
    5
    6
    7
    mysql> SELECT VERSION();
    +------------+
    | VERSION() |
    +------------+
    | 5.6.45-log |
    +------------+
    1 row in set (0.01 sec)

  2. 显示当前时间

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT NOW(); 
    +---------------------+
    | NOW() |
    +---------------------+
    +---------------------+
    | 2021-01-13 20:05:07 |
    +---------------------+
    1 row in set (0.00 sec)

  3. 显示年月日

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    mysql> SELECT DAYOFMONTH(CURRENT_DATE);
    +--------------------------+
    | DAYOFMONTH(CURRENT_DATE) |
    +--------------------------+
    | 13 |
    +--------------------------+
    1 row in set (0.00 sec)

    mysql> SELECT MONTH(CURRENT_DATE);
    +---------------------+
    | MONTH(CURRENT_DATE) |
    +---------------------+
    | 1 |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> SELECT YEAR(CURRENT_DATE);
    +--------------------+
    | YEAR(CURRENT_DATE) |
    +--------------------+
    | 2021 |
    +--------------------+
    1 row in set (0.01 sec)

  4. 显示字符串

    1
    2
    3
    4
    5
    6
    7
    mysql> SELECT "welcome to my blog!"; 
    +---------------------+
    | welcome to my blog! |
    +---------------------+
    | welcome to my blog! |
    +---------------------+
    1 row in set (0.00 sec)

  5. 当计算器用

    1
    2
    3
    4
    5
    6
    7
    SELECT ((4 * 4) / 10 ) + 25; 
    +----------------------+
    | ((4 * 4) / 10 ) + 25 |
    +----------------------+
    | 26.6000 |
    +----------------------+
    1 row in set (0.00 sec)

  6. 串接字符串

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT CONCAT(f_name, " ", l_name) AS Name 
    FROM employee_data
    WHERE title = 'Marketing Executive';
    +---------------+
    | Name |
    +---------------+
    | Monica Sehgal |
    | Hal Simlai |
    | Joseph Irvine |
    +---------------+
    3 rows in set (0.00 sec)

    注意:这里用到 CONCAT() 函数,用来把字符串连接起来。另外,我们还用到 AS 给结果列 CONCAT(f_name, " ", l_name) 起了个假名。

数据表

创建数据表

1
CREATE TABLE <表名> (<字段名1> <类型1> , ... , <字段名n> <类型n>);

以下为数据表的字段:

字段名 数字类型 数据宽度 是否为空 是否主键 自动增加 默认值
id int 4 PRIMARY KEY AUTO_INCREMENT  
name char 20      
sex int 4     0
degree double 16      

执行的 SQL 语句为:

1
2
3
4
5
mysql> CREATE TABLE MyClass(
> id int(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
> name char(20) NOT NULL,
> sex int(4) NOT NULL DEFAULT '0',
> degree double(16, 2));

删除数据表

1
DROP TABLE <表名>;

例如:删除表名为 MyClass 的表

1
DROP TABLE MyClass;

注意

  • DROP TABLE 用于取消一个或多个表。但必须有表的 DROP 权限。所有的表数据和表定义会被取消,所以使用本语句要小心。
  • 对于一个带分区的表,DROP TABLE 会永久性地取消表定义,取消各分区,并取消储存在这些分区中的所有数据。DROP TABLE 还会取消与被取消的表有关联的分区定义(.par)文件。
  • 对于不存在的表,使用 IF EXISTS 用于防止错误发生。当使用 IF EXISTS 时,对于每个不存在的表,会生成一个 NOTE
  • RESTRICTCASCADE 可以使分区更容易。但目前,RESTRICTCASCADE 不起作用。

插入数据

1
INSERT INTO <表名> (<字段名1>, ..., <字段名n >) VALUES (值1, ...,  值n );

例如:往表 MyClass 中插入二条记录, 这二条记录表示:编号为 1 的名为 Tom 的成绩为 96.45, 编号为 2 的名为 Joan 的成绩为 82.99,编号为 3 的名为 Wang 的成绩为 96.5

1
mysql> INSERT INTO MyClass(id, name, degree) VALUES (1, 'Tom', 96.45),(2, 'Joan', 82.99), (3, 'Wang', 96.59);

查询表中的数据

  1. 查询所有行命令:

    1
    SELECT <字段1, 字段2, ...> FROM <表名> WHERE <表达式>;

    SELECT 一般配合 WHERE 使用,以查询更精确更复杂的数据。

    例如:查看表 MyClassdegree 大于 90 的数据。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT * FROM MyClass WHERE degree > 90;
    +----+------+-----+--------+
    | id | name | sex | degree |
    +----+------+-----+--------+
    | 1 | Tom | 0 | 96.45 |
    | 3 | Wang | 0 | 96.59 |
    +----+------+-----+--------+
    3 rows in set (0.00 sec)

  2. 查询前几行数据

    例如:查看表 MyClass 中前 2 行数据

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT * FROM MyClass ORDER BY id LIMIT 0, 2;
    +----+------+-----+--------+
    | id | name | sex | degree |
    +----+------+-----+--------+
    | 1 | Tom | 0 | 96.45 |
    | 2 | Joan | 0 | 82.99 |
    +----+------+-----+--------+
    2 rows in set (0.00 sec)

删除表中数据

1
DELETE FROM <表名> WHERE <表达式>

例如:删除表 MyClass 中编号为 1 的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SELECT * FROM MyClass;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
| 1 | Tom | 0 | 96.45 |
| 2 | Joan | 0 | 82.99 |
| 3 | Wang | 0 | 96.59 |
+----+------+-----+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM MyClass WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM MyClass;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
| 2 | Joan | 0 | 82.99 |
| 3 | Wang | 0 | 96.59 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

修改表中数据

1
UPDATE <表名> SET <字段> = <新值>, ... WHERE <表达式>

例如:修改表 MyClassid2nameMary

1
mysql> UPDATE MyClass SET name = 'Mary' WHERE id = 2;

  1. 单表的 UPDATE 语句:

    1
    2
    3
    4
    5
    UPDATE [LOW_PRIORITY] [IGNORE] tbl_name 
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

  2. 多表的 UPDATE 语句:

    1
    2
    3
    UPDATE [LOW_PRIORITY] [IGNORE] table_references 
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

注意UPDATE 语法可以用新值更新原有表行中的各列。SET 子句指示要修改哪些列和要给予哪些值。WHERE 子句指定应更新哪些行。如果没有 WHERE 子句,则更新所有的行。如果指定了 ORDER BY 子句,则按照被指定的顺序对行进行更新。LIMIT 子句用于给定一个限值,限制可以被更新的行的数目。

修改数据表

  1. 增加字段

    1
    ALTER TABLE <表名> ADD <字段> <类型> <其他>; 

    1
    mysql> ALTER TABLE MyClass ADD passtest int(4) default '0'

  2. 增加索引

    1
    ALTER TABLE <表名> ADD INDEX <索引名>(<字段名1>, <字段名2>, ...)

    1
    mysql> ALTER TABLE employee ADD INDEX emp_name(name);

  3. 增加主关键字的索引

    1
    ALTER TABLE <表名> ADD PRIMARY KEY (<字段名>);

    1
    mysql> ALTER TABLE employee ADD PRIMARY KEY (id);

  4. 增加唯一索引

    1
    ALTER TABLE <表名> ADD UNIQUE <索引名>(<字段名>);

    1
    mysql> ALTER TABLE employee ADD UNIQUE emp_name2(cardnumber);

  5. 增加全文索引

    1
    ALTER TABLE <表名> ADD FULLTEXT <索引名>(<字段名>);

    1
    mysql> ALTER TABLE employee ADD FULLTEXT emp_name3(name);

    注意:全文索引只能用于数据库引擎为 MyISAM 的数据表,只适用于 VARCHARTEXT 类型的字段,此外,MySQL 自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。

  6. 删除某个索引

    1
    ALTER TABLE <表名> DROP INDEX <索引名>;

    1
    mysql>ALTER TABLE employee DROP INDEX emp_name;

  7. 修改原字段名称及类型

    1
    ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新字段类型> <其他>;

    1
    ALTER TABLE employee CHANGE name full_name char(10) NOT NULL;

  8. 删除字段

    1
    ALTER TABLE <表名> DROP <字段名>;

    1
    ALTER TABLE employee DROP address;

  9. 修改表名

    1
    RENAME TABLE <原表名> TO <新表名>;

    1
    mysql> RENAME TABLE MyClass TO YouClass;

    注意

    • 当你执行 RENAME 时,你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTERDROP 权限,以及对新表的 CREATEINSERT 权限。
    • 如果在多表更名中,MySQL 遭遇到任何错误,它将对所有被更名的表进行倒退更名,将每件事务退回到最初状态。
    • RENAME TABLE 在 MySQL 3.23.23 中被加入。

备份数据库

命令在 DOS\mysql\bin 目录下执行,导出文件默认是存在 mysql\bin 目录下。

导出整个数据库

1
2
3
4
5
6
7
mysqldump -u 用户名 -p'密码' 数据库名 > 导出的文件名.sql

mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql
Enter password: ******

mysqldump -u root -p database_name > outfile.sql
Enter password: ******

导出所有数据库

1
2
mysqldump  -uroot -p --all-databases > outfile.sql
mysqldump -uroot -p -A > outfile.sql

导出一个数据库结构

1
mysqldump -u root -p -d –add-drop-table database_name > outfile.sql

-d 没有数据, –add-drop-table在每个 CREATE 语句之前增加一个 DROP TABLE

带语言参数导出

1
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile.sql

导出数据表

  1. 导出数据表和表结构

    1
    mysqldump -u root -h 登录主机 -p'密码' -P 端口号 数据库名 表名 > 文件名.sql

    将特定数据库特定表中的数据和表格结构和数据全部返回。

    1
    mysqldump -u root -h localhost -p -P3306 database_name table > table.sql

  2. 导出表结构却不导出表数据

    只返回特定数据库特定表格的表格结构,不返回数据,添加 -d 命令参数。

    1
    mysqldump -u root -h localhost -p -P3306 -d database_name table > table.sql

  3. 导出表结构和满足 WHERE 条件的表数据

    只返回特定数据库中特定表的表格结构和满足特定条件的数据。

    1
    mysqldump -u root -h localhost -p -P3306 database_name table -where="time>'2017-01-01'" > table.sql

  4. 导出数据却不导出表结构

    只返回特定数据库中特定表格的数据,不返回表格结构,添加 -t 命令参数。

    1
    mysqldump -u root -h localhost -p -t -P3306 database_name table > table.sql

导入 SQL 文件

1
source database.sql;