1、mysql基础操作命令
1.1 数据库操作
| 命令 | 作用 |
|---|---|
| select database(); | 查询当前使用的数据库名称 |
| drop database runoob; | 删除数据库 |
1.2 增删改查操作
| 命令 | 作用 |
|---|---|
| update table set value=num; | 更新数据记录 |
| delete from table; | 删除记录 |
| insert into table(id,name) value(1,’name’); | 插入记录 |
| select * from table; | 查询数据 |
1.3 索引操作
| 命令 | 作用 |
|---|---|
| create index indexname on table_name (column_name) | 创建索引 |
| alter table tablename add index indexname(columnname) | 修改表结构(添加索引) |
| drop index indexname on mytable; | 删除索引的语法 |
| alter table testalter_tbl modify i int not null; alter table testalter_tbl add primary key (i); |
添加主键索引,主键作用于列上（可以一个列或多个列联合主键） |
| alter table testalter_tbl drop primary key; | 删除主键 |
| show index from table_name; \g | 显示索引信息 |
1.4 表操作
| 命令 | 作用 |
|---|---|
| alter table t_user add column user_age int(11) default null comment ‘年龄’ after user_email; | 插入表字段 |
| alter table tbl drop i int; | 删除tbl表的i字段，类型为int |
| alter table tbl modify c char(10); | 字段c的类型从char(1)改为char(10) |
| alter table tbl change i j bigint; | 在 change 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。 |
| create table employee2 like employee; | 复制一张表 |
| insert into employee2 select * from employee; | 将一张表的数据插入另一张表 |
| alter table tbl rename to tbl1; | 修改表名 |
| delete from table; | 记录日志之后清空表 |
| truncate table table; | 不记录日志清空表（不可恢复） |
| drop table table_name; | 删除表（表结构也会清空） |
| alter table table_name auto_increment=1; | 重置表自增字段值序号 |
1.5 mysql函数
| 函数 | 作用 |
|---|---|
| (case field when 1 then ‘下雪’ when 2 then ‘下雨’ else ‘晴天’ end) 天气 |
对查询结果进行判断，显示不同的状态，如果field字段查询结果为1则下雪，如果为2则是下雨，相当于java的switch语句。 |
| group by field | 根据field字段将表查询结果进行分类。 |
| count（filed） | 查询field字段的记录数,如果为*则是所有的记录数。 |
| sum（filed） | 对查询的filed字段求和 |
2、mysql数据导入导出
- 使用 SELECT … INTO OUTFILE 语句导出数据
查看数据库允许的输出路径path：SHOW VARIABLES LIKE "secure_file_priv";
导出查询结果：select * from table into outfile 'path'; - 导出SQL格式的数据
导出表数据：mysqldump -u root -p database table > save.sql
导出指定数据库：mysqldump -u root -p database > save.txt
导出所有数据库：mysqldump -uroot -p'Hello!23456' --events --all-databases --default-character-set=utf8 --hex-blob > /var/www/dbdump/alldb/my.sql - 将备份的数据库导入到MySQL服务器中
导入表数据：source save.txt;
导入数据库：mysql -u root -p database < dump.txt
3、mysql数据账号管理
首次安装mysql没有密码验证，开启密码验证
- 进入mysql数据库：
use mysql; - 更改root用户密码：
update user set password=password('password') where user='root';alter user user() identified by 'jing1995'; - 重启mysql生效：
service mysqld restart
- 进入mysql数据库：
mysql忘记密码免密登录
- 停止mysql：
service mysqld stop - 在
my.cnf中添加skip-grant-tables - 启动mysql可以免密登录
- 停止mysql：
mysql添加用户
- 指定ip连接
create user 'user'@'localhost' identified by 'password'; - 允许所有ip连接（用通配符%表示）
create user 'user'@'%' identified by 'password';
- 指定ip连接
mysql删除用户
DROP USER username@localhost;修改后刷新权限
FLUSH PRIVILEGES;为新用户授权数据库访问权限
- 基本格式如下:
grant all privileges on 数据库名.表名 to '新用户名'@'指定ip' identified by '新用户密码'; - 允许访问所有数据库下的所有表
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码'; - 指定数据库下的指定表
grant all privileges on test.test to '新用户名'@'指定ip' identified by '新用户密码';
- 基本格式如下:
设置用户操作权限
- 设置用户拥有所有权限也就是管理员
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION; - 拥有查询权限
grant select on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION; - 其它操作权限说明,select查询 insert插入 delete删除 update修改
- 设置用户拥有查询插入的权限
grant select,insert on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION; - 取消用户查询的查询权限
REVOKE select ON what FROM '新用户名';
- 设置用户拥有所有权限也就是管理员
4、mysql union 操作符
- 描述
mysql union 操作符用于连接两个以上的 select 语句的结果组合到一个结果集合中。多个 select 语句会删除重复的数据。 - 语法
1
2
3
4
5
6
7
8mysql union 操作符语法格式：
select expression1, expression2, ... expression_n
from tables
[where conditions]
union [all | distinct]
select expression1, expression2, ... expression_n
from tables
[where conditions]; - 参数
expression1, expression2, ... expression_n:要检索的列。tables:要检索的数据表。where conditions:可选， 检索条件。distinct:可选，删除结果集中重复的数据。默认情况下 union 操作符已经删除了重复数据，所以 distinct 修饰符对结果没啥影响。all:可选，返回所有结果集，包含重复数据。