用户管理
用户分为两种:
- 超级管理员,即root用户,拥有所有权限;
- 普通用户,由root分配权限。
超级用户在安装MySQL的时候已经创建,通过登录root账户,可以执行创建用户的操作。
用户操作
创建用户
- 创建普通用户:
CREATE USER '用户名'[@'主机名'][INDENTIFIED BY '密码'];
语句中[]包裹的内容为非必须,即创建用户时可以不包含主机名和密码,这样创建出来的用户没有任何权限,不需要密码即可登录。
- 授权方式创建用户
GRANT 权限 on 数据库.表 to '用户名'@'主机' [IDENTIFIED BY '密码'];
授权语句执行时,如果用户不存在会创建用户,可通过此方式直接创建并赋权给用户
删除用户
DELETE FROM MYSQL.USER WHERE USER='用户名' AND HOST='主机名';
删除用户后,需要同步一下权限表的内容,执行:
FLUSH PRIVILEGES
刷新权限后,方可创建刚删除的用户名用户。
修改密码
root用户可以修改任何用户的密码,普通用户只能修改自己的密码。
SET PASSWORD=PASSWORD('新密码'); # 用户登录后修改自己的密码 SET PASSWORD FOR '用户名'@'%'=PASSWORD('密码'); UPDATE mysql.user SET password=PASSWORD('密码') WHERE user='用户名'; # root账户更改自己或其他用户的密码
权限操作
添加权限
通用的权限有:
SELECT, UPDATE, DELETE, INSERT, CREATE, ALTER, DROP, REFERENCES, CREATE TEMPORARY TABLES, INDEX, CREATE VIEW, SHOW VIEW, CREATE routine, ALTER ROUTINE, EXECUTE, ALL, ALL PRIVILEGES
MySQL中使用*表示所有,可以表示所有数据库、所有数据表等。
举个栗子:
GRANT SELECT,DELETE ON dbtest.table_test TO 'oldman'@'localhost';
除了对整张表进行权限控制外,MySQL的权限还可以精确到字段:
GRANT SELECT(id, name),DELETE(title) ON dbtest.table_test TO 'oldman'@'localhost';
这样设置后,oldman用户只能对dbtest下的table_test表下的id和name字段进行查询和删除。
撤销权限
REVOKE 权限 ON 数据库.表名 FROM '用户名'@'主机';
同GRANT,REVOKE对权限的撤销也可以精确到字段级别。
数据库管理
库级操作
连接数据库
在命令行输入:
>> mysql -h主机名 -u用户名 -p密码 或 >> mysql -h主机名 -u用户名 -p密码 password:
第一种密码以明文方式显示,第二种则以密文方式输入。其中-h后跟host参数,-u后跟着的是username,-p后跟的是password。
创建数据库
CREATE DATABASE 数据库名[DEFAULT CHARACTER SET 编码 [COLLATE 编码_ci]];
[]中的语句可不加,只是通过[]中的语句来设定数据库的默认编码,例如utf8的COLLATE后跟utf8_general_ci,gbk的COLLATE为gbk_chinese_ci。
查看数据库
SHOW DATABASES;
删除数据库
DROP DATABASE 数据库名;
删库的下一步就是跑路。
查看数据库编码
SHOW VARIABLES LIKE 'CHARACTER_SET_DATABASE'; SHOW CREATE DATABASE 数据库名; SHOW CREATE SCHEMA 数据库名;
会显示当前数据库的编码类型
修改数据库编码
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码 [COLLATE 编码_bin]; ALTER DATABASE 数据库名 CHARACTER SET=字符类型;
表级操作
切换数据库
USE 数据库名;
通过USE语法来切换当前操作的数据库
查看数据表
SHOW TABLES; # 查看所有表 DESCRIBE 表名; SHOW CREATE TABLE 表名; # 查看某张表的结构
创建数据表
CREATE TABLE 表名(字段1 类型,字段2 类型,....);
创建过程中可以给字段添加属性,常用属性有:
primary key:设置主键
追加主键:
ALTER TABLE 表名 modify 字段名 primary key; ALTER TABLE 表名 ADD PRIMARY KEY(字段名); ALTER TABLE 表名 ADD UNIQUE KEY(字段);
删除主键:
ALTER TABLE 表名 DROP PRIMARY KEY(主键);
复合主键:
primary key(字段1,字段2,...)
default:设置默认值
null/not null:设置空/非空
foreign key:外键
unique key:唯一
auto_increment:自增
修改自增长步长:
SET AUTO_INCREMENT_INCREMENT=2;
comment:注释,用来描述字段
较为完整的举例说明:
CREATE TABLE 'student'( 'id' int NOT NULL AUTO_INCREMENT , 'name' varchar(18), 'age' int DEFAULT 18, PRIMARY KEY('id') )ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
删除数据表
DROP TABLE 表名;
删表需谨慎
修改数据表
- 修改表名
ALTER TABLE 旧表名 rename [to] 新表名
- 添加字段
ALTER TABLE 表名 ADD COLUMN 字段名 类型; # 添加字段到最后 ALTER TABLE 表名 ADD COLUMN 字段名 类型 FIRST; # 添加字段到开头 ALTER TABLE 表名 ADD COLUMN 字段名 类型 AFTER 已存在的某个字段名; # 添加字段到指定字段后
- 修改字段
ALTER TABLE 表名 MODIFY COLUMN 字段名 类型; # 将表中的某个字段更改为给出的类型 ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 DOUBLE;
- 删除字段
ALTER TABLE 表名 DROP 字段名;
数据操作
插入数据
INSERT INTO 表名 VALUES(字段1值,字段2值,字段3值,....) # 插入全部字段的数据,字段不能有缺失,表中有几个字段填写几个字段 INSERT INTO 表名(字段1,字段2,字段3,...) VALUES(值1,值2,值3,...) # 可以用来插入部分字段,其中字段和值时一一对应的
修改数据
UPDATE 表名 SET 字段1='值1',字段2='值2'; # 不带条件的修改,会把所有数据的字段都做更新,谨慎使用 UPDATE 表名 SET 字段1='值1' WHERE id=1; # 准确修改符合条件的数据,也可以多字段修改
删除数据
DELETE FROM 表名; # 删除所有数据 DELETE FROM 表名 where 条件; # 删除表中符合条件的数据 TRUNCATE TABLE 表名; # truncate不允许带查询条件 # delete和truncate的主要区别是:1.truncate不可以带查询条件而delete可以;2.delete删除的数据可以回滚而truncate不可以;3.delete只删除数据,而truncate不光删除数据,还删除约束
查询数据
SELECT * FROM 表名; # 查询表中所有数据 SELECT 字段1,字段2,字段3,... FROM 表名; # 查询表中某些字段 SELECT 字段1,字段2,字段3,...,'常量值' AS '常量字段' FROM 表名 # 查询字段数据,加入常量字段并设置常量值 SELECT DISTINCT 字段1,字段2,字段3,... FROM 表名; # 查询结果并去重
- 数字运算
对于数字类型的字段,可以在查询的同时可以进行运算
SELECT (字段1+字段2)/2 AS '新字段名' FROM 表名;
- where条件查询
MySQL通过WHERE语句来增加数据筛选条件
条件运算 描述 AND, OR, NOT, XOR 逻辑条件,与、或、非、异或。通常用来设置复合条件 >, <, >=, <=, = <> 通常用来设置数字字段的比较条件 is null, is not null, =”“, <>”“ 判断字段数据是否为空(null或空字符) between, like between 与and连用,表示落在某个区间;like用来进行模糊匹配,通常与通配符搭配使用。%表示匹配多个,_表示匹配一个。当需要匹配通配符时,可在通配符前加转义字符\,或通过ESCAPE语句指定一个通配符。 - limit限制
通过limit语句限制返回的数据条数
SELECT 字段... FROM 表名 LIMIT [起始行],查询行数; # 从某行开始查询,并返回想要的数据量。起始行可以缺失,默认从第一行开始,默认值为0 SELECT 字段... FROM 表名 LIMIT 行数 OFFSET 起始行; # 同一种结果不同语法
- order by排序
顺序分为正序和倒序,ASC为正序(0->9, a->z),DESC为倒序(9->0, z->a)
SELECT 字段... FROM 表名 ORDER BY 字段1 ASC/DESC,字段2 ASC/DESC; # 查询的默认顺序为正序,可设置对多个字段进行排序
- group by分组
SELECT ... FROM 表名 GROUP BY 字段1,字段2,...; # 输出结果按字段分组,结果类似聚合 SELECT ... FROM 表名 WHERE 条件 GROUP BY 字段...; # 对符合条件的数据进行分组 SELECT GROUP_CONTACT(字段) FROM 表名 GROUP BY 字段; # GROUP_CONTACT会把聚合后的某些字段里都有哪些数据显示出来
GROUP BY必须在WHERE之后,ORDER BY之前。可以通过HAVING语句给分组加查询条件:
SELECT ... FROM 表名 GROUP BY 字段1,字段2,... HAVING 条件; # 通过HAVING来连接WHERE语句做条件查询
连表操作
普通连表
SELECT 表1.字段1,表2.字段2 FROM 表1,表2 WHERE 表1.id = 表2.nid; # 如果两个表无对应关系则不返回数据
inner join
SELECT 表1.字段1,表2.字段2 FROM 表1 INNER JOIN 表2 ON 表1.id = 表2.nid; # 无对应关系返回空
left join
SELECT 表1.字段1,表2.字段2 FROM 表1 LEFT JOIN 表2 ON 表1.id = 表2.nid; # 左边的表为主表,无对应关系则只显示表1数据,同时表2字段全为null
right join
SELECT 表1.字段1,表2.字段2 FROM 表1 RIGHT JOIN 表2 ON 表1.id = 表2.nid; # 右边的表为主表,无对应关系则只显示表2数据,同时表1字段全为null
union
SELECT 字段1 FROM 表1 UNION SELECT 字段2 FROM 表2; # 合并查询结果集,并去掉重复数据 SELECT 字段1 FROM 表1 UNION ALL SELECT 字段2 FROM 表2; # 合并查询结果集,但不去重