mysql常用操作

发表于 更新于 分类于

　　关于mysql数据库的一些常用语句

1.数据库

1
2
3
4
5
6
7
8
# 查看所有的数据库
SHOW DATABASES ;
# 创建一个数据库
CREATE DATABASE k;
# 删除一个数据库
DROP DATABASE k;
# 使用这个数据库
USE k;

2.表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 查看所有的表
SHOW TABLES ;
# 创建一个表
CREATE TABLE n(id INT, name VARCHAR(10));
CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
CREATE TABLE m(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的表
CREATE TABLE n SELECT * FROM m;
# 新创建的表与一个存在的表的数据结构类似
CREATE TABLE m LIKE n;
# 创建一个临时表
# 临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。
CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的临时表
CREATE TEMPORARY TABLE tt SELECT * FROM n;
# 删除一个存在表
DROP TABLE IF EXISTS m;
# 更改存在表的名称
ALTER TABLE n RENAME m;
RENAME TABLE n TO m;
# 查看表的结构(以下五条语句效果相同）
DESC n;   # 因为简单，所以建议使用
DESCRIBE n;
SHOW COLUMNS IN n;
SHOW COLUMNS FROM n;
EXPLAIN n;
# 查看表的创建语句
SHOW CREATE TABLE n;

2.1表的结构

1
2
3
4
5
6
7
8
9
10
11
#添加字段
alter table n add age varchar(2);
#删除字段
alter table n drop age;
#更改字段属性和属性
alter table n change age a int;
#只更改字段属性
alter table n modify age varchar(7)
#已有字段设置默认值
alter table n add column_name drop default;#若已有默认值，先删除
alter table n alter column column_name set default(x);

2.2表的数据

1
2
3
4
5
6
7
8
9
10
11
#增加数据
insert into n values (1,'tom','23'),(2,'johin','22')
inster into n select * from n;
#删除数据
delete from n where id = 2;
#更改数据
update n set name = 'tom' where id = 2;
#数据查找
select * from n where name like '%h%';
#数据排序（反序）
select * from n order by name, id desc;

3.键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#添加主键
alter table n add primary key (id);
alter table n add constraint pk_n primary key (id);
#删除主键
alter table n drop primary key;
#添加外键
alter table m add foreign key (id) references n (id)#自动生成键名m_ibfk_1
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id);   # 使用定义的键名fk_id
#删除外键
alter table m drop foreign key 'fk_id'
#修改外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id);    # 删除之后从新建
#添加唯一键
ALTER TABLE n ADD UNIQUE (name);
ALTER TABLE n ADD UNIQUE u_name (name);
ALTER TABLE n ADD UNIQUE INDEX u_name (name);
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
CREATE UNIQUE INDEX u_name ON n(name);
# 添加索引
ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);
# 删除索引或唯一键
DROP INDEX u_name ON n;
DROP INDEX i_age ON n;

4.视图

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建视图
CREATE VIEW v AS SELECT id, name FROM n;
CREATE VIEW v(id, name) AS SELECT id, name FROM n;
# 查看视图(与表操作类似)
SELECT * FROM v;
DESC v;
# 查看创建视图语句
SHOW CREATE VIEW v;
# 更改视图
CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n ;
# 删除视图
DROP VIEW IF EXISTS v;

5.联接

1
2
3
4
5
6
7
8
9
10
11
12
13
# 内联接
SELECT * FROM m INNER JOIN n ON m.id = n.id;
# 左外联接
SELECT * FROM m LEFT JOIN n ON m.id = n.id;
# 右外联接
SELECT * FROM m RIGHT JOIN n ON m.id = n.id;
# 交叉联接
SELECT * FROM m CROSS JOIN n;   # 标准写法
SELECT * FROM m, n;
# 类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;

6.用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 增加用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test'));    # 在用户表中插入用户信息，直接操作User表不推荐
# 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 用户授权
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
# 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
FLUSH PRIVILEGES ;   # 刷新系统权限表,使授予权限生效
# 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost';   # 取消该用户的删除权限

生成数据字典

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    COLUMN_NAME AS '字段名',
    COLUMN_TYPE AS '字段类型',
    ( CASE WHEN IS_NULLABLE = 'YES' THEN '是' ELSE '否' END ) AS '是否可空',
    ( CASE WHEN COLUMN_KEY = 'PRI' THEN '是' ELSE '否' END ) AS '是否主键',
		COLUMN_DEFAULT AS '默认值',
    COLUMN_COMMENT AS '注释' 
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    TABLE_SCHEMA = '数据库' 
    AND TABLE_NAME = '表';

image-20220624194734237

查询某个数据库的所有表名

1
2
3
4
5
6
7
8
9
10
SELECT
	TABLE_NAME 表名,
	TABLE_COMMENT 表注释,
	TABLE_ROWS 数据量 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'prop' 
ORDER BY
	TABLE_NAME;

image-20220624194643163

7 mysql常用关键字

distinct去重

limit 0,2

8 内连接

1
2
3
4
select ...
from 表名
inner join 被连接表
on 连接条件

如：查询每个学生及其班级的详情信息

1
2
select * from 学生表
inner join 班级表 on 学生表.班号=班级表.班号