MySQL 基础操作

2017-09-08 约 759 字预计阅读 2 分钟

连接数据库

连接本地数据库：

1
mysql -u root -p

连接远程数据库：

1
mysql -h HOSTNAME -u root -p

数据库操作

显示所有数据库：

1
show databases;

建立新数据库，并设置默认编码：

1
create database dbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;;

选择一个数据库：

1
use dbname;

删除数据库：

1
drop database dbname;

用户和权限操作

新建用户

1
create user '[USERNAME]'@'[HOST]' identified by '[PASSWORD]';

分配权限

1
grant all on [DB.TABLES] to '[USERNAME]'@'[HOST]';

新建用户并分配权限

1
2
grant [PRIVILEGE_LIST] on [DB.TABLES] to '[USERNAME]'@'[HOST]' identified by '[PASSWORD]';
flush privileges;

[PRIVILEGE_LIST]：权限列表见官方文档，如果赋予所有权限使用 ALL；
[DB.TABLE]：如果赋予该用户操作该数据库所有表的权限，table 可以使用*代替，数据库也可以用*代替；
[HOST]：HOST 可以是具体的地址；也可以是localhost，表示只监听本地；或者使用%表示监听所有地址；

举个例子：新建用户aaa，密码为sccc@123，可以从其他所有主机登录，赋予对 dbname 所有表的 CRUD 权限：

1
2
grant insert, delete, update, select on dbname.* to 'aaa'@'%' identified by "sccc@123";
flush privileges;

用户密码管理

更改用户密码：

1
alter user '[USERNAME]'@'[HOST]' identified by '[PASSWORD]';

更改用户密码过期时间：

1
2
3
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE DEFAULT;

删除用户

1
DROP USER 'username'@'localhost';

表操作

创建表

1
2
3
4
5
6
CREATE TABLE `topic` (
  `token` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `followers` int(11) NOT NULL,
  `description` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修改表

重命名：

1
ALTER TABLE t1 RENAME t2;

修改字段，modify 不改变字段名，只修改字段属性；change 既可改变字段名，也可修改字段属性；

因此 change 可以用来重命名字段。

1
2
3
ALTER TABLE t2
MODIFY a TINYINT NOT NULL,
CHANGE b c CHAR(20);

增加字段：

1
ALTER TABLE t2 ADD d TIMESTAMP;

删除字段：

1
ALTER TABLE t2 DROP COLUMN c;

删除表

1
DROP TABLE t2;

数据（记录）操作

CRUD(Create Read Update Delete)，是软件系统中数据库或者持久层的基本功能操作。

查询：

1
SELECT * FROM t1 WHERE a=3 AND b like '%foo%';

更新：

1
UPDATE t1 set a=3 where a=4 and b like '%foo%';

新建：

1
INSERT INTO t1 (`a`, `b`) VALUES (33, 'foo');

删除：

1
DELETE FROM t1 WHERE a=3 and `b` in (1, 2, 3);

索引操作

使用 ALTER 语法

添加索引

1
ALTER TABLE table_name ADD INDEX index_name (column_list);

添加唯一索引

1
ALTER TABLE table_name ADD UNIQUE index_name (column_list);

添加主键索引

1
ALTER TABLE table_name ADD PRIMARY KEY index_name (column_list);

使用 ADD 语法