mysql 基础操作
连接：
1、mysql管理人默认为root，没有设置密码则直接登录
mysql -h host -u root -p 不用输入密码按回车自动进入
2、如果想设置mysql密码
mysqladmin -u root password 123456
3、如果你的root现在有密码了（123456），那么修改密码为abcdef的命令是：
mysqladmin -u root -p password abcdef
退出：
QUIT 或者 Control+D
今天安装mysql8 使用navicat 连接时报错 1251
这个错误出现的原因是在mysql8之前的版本中加密规则为mysql_native_password，而在mysql8以后的加密规则为caching_sha2_password
解决此问题的方法:(修改加密规则)
- ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #修改加密规则
- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码
- FLUSH PRIVILEGES; #刷新权限
三、数据库操作
1、显示数据库
|
1
|
SHOW DATABASES; |
默认数据库：
mysql - 用户权限相关数据
test - 用于用户测试数据
information_schema - MySQL本身架构相关数据
2、创建数据库
|
1
2
3
4
5
|
# utf-8CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;# gbkCREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci |
3、使用数据库
|
1
|
USE db_name; |
显示当前使用的数据库中所有表：SHOW TABLES;
4、用户管理
|
1
2
3
4
5
6
7
8
9
10
|
创建用户 create user '用户名'@'IP地址' identified by '密码';删除用户 drop user '用户名'@'IP地址';修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;修改密码 set password for '用户名'@'IP地址' = Password('新密码') PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议） |
5、授权管理
|
1
2
3
|
show grants for '用户'@'IP地址' -- 查看权限grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限 |
权限
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用 对于权限
对于目标数据库以及内部其他： 数据库名.* 数据库中的所有 数据库名.表 指定数据库中的某张表 数据库名.存储过程 指定数据库中的存储过程 *.* 所有数据库
用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
grant all privileges on db1.tb1 TO '用户名'@'IP' grant select on db1.* TO '用户名'@'IP' grant select,insert on *.* TO '用户名'@'IP' revoke select on db1.tb1 from '用户名'@'IP'
特殊的：
|
1
|
flush privileges，将数据读取到内存中，从而立即生效。 |
# 启动免授权服务端 mysqld --skip-grant-tables # 客户端 mysql -u root -p # 修改用户名密码 update mysql.user set authentication_string=password('666') where user='root'; flush privileges;
1；创建表（类似于excel）
create table tab_name(
field1 typee[完整性约束条件],
field type,
....
field type
) [character set xxx];
----创建一个员工表employee
create table employee(
id int primary key atuo_increment, // 非空且唯一 主键；
name varchar(20),
gender bit default 1,
birthday date,
entry_data date,
job varchar(20),
salary double(6,2) unsigend, （unsigend-->无符号）
resume text //这里作为最后一个字段不加逗号
);
约束：
primary key
unique
not null
auto_increment 主键字段必须是数字类型
外键约束 foreign key
外键，一个特殊的索引，只能是指定内容 creat table color( nid int not null primary key, name char(16) not null ) create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid) ) // ADD CONSTRAINT char FOREIGN KEY (char_id) REFERENCES class_char(id)
---------------增加外键 和删除------
ALTER TABLE student ADD CONSTRAINT abc
FOREIGN KEY(char_id)
REFERENCES class_char(id);
ALTER TABLE student DROP FOREIGN KEY abc;
----------级联删除----
FOREIGN KEY (char_id) REFERENCES tab_name(id) ON DELETE CASCADE // 后面加 SET NULL 删除后变null
2,查看表信息
desc tab_name 查看表结构
show columns from tab_name 查看表结构
sgiw tables 查看当前数据库中的所有的表
show create table tab_name 查看当前数据库表建表语句
3，修改表结构
alter table employee add A int, (add--增加字段,DROP删除字段）
【修改主键- alter tablie tab_name add primary key (字段名称，....)】
alter table user add addr varchar(20) not null unique first/after username;
alter table users2
add addr varchar(20),
add age int first,
add birth varchar(20) after name;
2，修改列表
alter table tab_name modify 列名 类型【完整约束条件】[first |after 字段名];
类型 默认值
alter table users2 modify age tinyint default 20;
位置
alter table users2 modify age int after id;
3，修改列名
alter table tab_name change [column] [new column] type 【约束性条件】
4,修改列表名
rename table 表名 to 新表名 (drop table tab_name 删除表)
5，修改字符集
alter table tab_name character set utf8;
1、增
|
1
2
3
|
insert into 表 (列名,列名...) values (值,值,值...)insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)insert into 表 (列名,列名...) select (列名,列名...) from 表 |
2、删
|
1
2
|
delete from 表delete from 表 where id＝1 and name＝'alex' |
3、改
|
1
|
update 表 set name ＝ 'alex' where id>1 |
4、查
|
1
2
3
|
select * from 表 （select distinct 去重）select * from 表 where id > 1select nid,name,gender as gg from 表 where id > 1 |
5、其他
|
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
a、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表)b、通配符 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） select * from 表 where name like 'ale_' - ale开头的所有（一个字符）c、限制 select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行d、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序e、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10 特别的：group by 必须在where之后，order by之前f、连表 无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nidg、组合 组合，自动处理重合 select nickname from A union select name from B 组合，不处理重合 select nickname from A union all select name from B |
索引查询
---CREATE 在已经存在的表上创建索引
CREATE [UNIQE |FULLTEXT |SPATITL] INDEX 索引名
ON 表名(字段名[(长度)] [ASC|DESC]);
-----ALTER TABLE 在已经存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQE |FULLTEXT |SPATITL] INDEX
索引名(字段名[(长度)] [ASC|DESC]);
CREATE INDEX index_emp_name ON emp1(name);
ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);
---删除
DROP INDEX 索引名 ON 表名
浙公网安备 33010602011771号