Linux运维:MySQL的SQL语句基本使用
Liunx笔记：SQL语句的基本使用
Ago linux运维群： https://hankbook.cn 笔者QQ：578843228
sql语句：一种结构化操作语言
类别 说明
DDL #DDL全拼Data Definition Language，中文：数据定义语言，主要关键字为CREATE(创建)，ALTER(修改)，DROP（删除）等，负责管理数据库的基础数据（不会对表的内容修改），例如：增删库，表，索引，用户等。
DCL #DCL全拼Data Control Language，中文数据控制语言，主要关键字为GRANT(授权用户)，REVOKE（权限回收），COMMIT（提交），ROLLBACK（回滚）。
DML # DML全拼Data Manipulation Language，中文数据操作语言，主要关键字为SELECT（查）、INSERT（增）、DELETE（删）、UPDATE（改），主要针对数据库里的数据进行操作。
DDL（数据定义语言）（create,alter,drop） 运维 create，alter，drop
show Data Definition
DML（数据操作语言）（select,insert,delete,update） 开发 select，insert，delete，updata
help Data Manipulation
DCL（数据控制语言）（grant,revoke,commit,rollback） 运维 grant，revoke，commit，rollback
help Account Management
查看帮助：
? contents
查看库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | # 此表为系统表，存储数据库内置对象的信息。
| mysql | # 此表为系统表，存储用户授权和权限相关信息，授权时会用到
| performance_schema | # 这是mysql第二条产品线增加的系统表，存储和性能相关数据。
+--------------------+
3 rows in set (0.25 sec)
创建库
help create database; -- 查看创建库的帮助
show character set; -- 查看支持的字符集
默认字符集库创建
create database aige;
show databases;
指定字符集创建
create databases aige_gbk character set gbk collate gbk_chinese_ci;
show database;
show create database aige;
几个函数的使用
select version();
select user();
use aige
select database();
select now();
system ls -l
删库：
help drop;
drop database aige;
授权用户：（三种方法）
help create user;
create user 'aige'@'localhost' identified by 'aige';
grant all on aige.* to 'aige'@'localhost';
show grants for 'aige'@'localhost';
grant all on *.* to 'system'@'localhost' identified by 'system123' with grant option; -- 相当于root
权限部分说明
权限 说明
SELECT -- 查询（表内容）
INSERT -- 插入（表内容）
UPDATE -- 修改（表内容）
DELETE -- 删除（表内容）
CREATE -- 创建（库表）
DROP -- 丢弃（库表）
REFERENCES -- 参考资料
INDEX -- 索引
ALTER -- 修改（库表）
CREATE TEMPORARY TABLES -- 创建临时表
LOCK TABLES -- 锁表
EXECUTE -- 执行
CREATE VIEW -- 创建视图
SHOW VIEW -- 查看视图
CREATE ROUTINE -- 创建存储过程
ALTER ROUTINE -- 修改存储过程
EVENT -- 事件
TRIGGER -- 触发器
GRANT -- 授权
授权，一种是百分号的方法，一种掩码是255.255.255.0 （授权主机）
查看权限
select * from mysql.db where user='aige' and host='localhost'\G
web授权安全红线
权限不能用all，而因公select，insert，update，delete，等具体权限
库不能用.，而应用专库方式 aige.*
主机不能用%，而应用内网IP段，即172.16.1.0/255.255.255.0
回收权限：
help revoke;
revoke insert on aige.* from 'aige'@'localhost';
show grants for 'aige'@'localhost';
select * from mysql.user\G
创建表：
help create table;
create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default ault null );
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table:
CREATE TABLE `student` ( # CREATE TABLE是创建表的固定关键字，student为表名
`id` int(4) NOT NULL, # 学号列，数字类型，长度为4，不能为空
`name` char(20) NOT NULL, # 名字列，定常
`age` tinyint(2) NOT NULL DEFAULT '0',# 年龄列，很小的数字类型，长度为2，不能为空，默认0
`dept` varchar(16) DEFAULT NULL # 系别列，变长字符类型，长度为16，默认为空
) ENGINE=InnoDB DEFAULT CHARSET=utf8
查看表结构
show full columns from student from aige;
show full columns from student;
show columns from student; -- 和desc等价
desc student;
ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- char是定长 varchar是变长
生产场景
use sns;
set names gbk;
CREATE TABLE `subject_comment_manager` (
`subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键',
`subject_type` tinyint(2) NOT NULL COMMENT '素材类型',
`subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键',
`subject_title` varchar(255) NOT NULL COMMENT '素材的名称',
`edit_user_nick` varchar(64) default NULL COMMENT '修改人',
`edit_user_time` timestamp NULL default NULL COMMENT '修改时间',
`edit_comment` varchar(255) default NULL COMMENT '修改的理由',
`state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',
PRIMARY KEY (`subject_comment_manager_id`),
KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)), #<==括号内的32表示对前32个字符做前缀索引。
KEY `IDX_SUBJECT_TITLE` (`subject_title`(32))
KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)#<==联合索引，此行为新加的，用于给大家讲解的。实际表语句内没有此行。
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
更改表名
rename （工作基本不用）
rename table student to test;
alter table test rename to student;
添加字段
alter table 表名 add 字段 类型 其他;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
添加到末尾
alter table test add sex char(4);
指定的列
alter table test add age int(4) after name;
第一列
alter table test add qq varchar(15) first;
添加两个字段
alter table test add age tinyint(2) first,add qq varchar(15);
改字段，用change或者modify
alter table test change age aigeage char(4) after name;
alter table test modify age char(4) after name;
索引是优化的工作之一
两种方法添加索引 主键索引和普通索引。主键索引是唯一的
当数据量以及访问量很大的时候，不适合临时建立索引，建立索引需要消耗资源。
普通索引
alter table test add index index_name(name);
create index index_qq on test(qq);
主键索引
alter table test modify id int primary key;
查看索引
show index from test\G
索引的删除
DROP INDEX [ONLINE|OFFLINE] index_name ON tbl_name
删表
drop table student;
use aige
drop table test;
CREATE TABLE test (
id int(4) NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
PRIMARY KEY (id)
) ;
desc test;
插表
insert into test(id,name) values(1,'aige');
select * from test;
insert into test(name) values('can');
按顺序插，不指定字段
insert into test values('can-H');
插两行
insert into test values(4,'zuma'),(5,'kaka');
INSERT INTO `test` VALUES (1,'aige'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
备份
shell：mysqldump -uroot -paige123 -B aige >/opt/bak.sql
mysql：source /opt/bak.sql;
查询
select id,name from aige.test;
select * from test limit 2; -- 前两行
select * from test limit 2,3; -- 从第二行取三行，不包括第二行
select * from test where name='kaka';
排序查询
select id,name from test where id>1 order by id asc; -- 正序
select * from test where id>1 order by id desc; -- 反序
修改表内容
update test set name='Can' where id=2;
mysql -U 登录，防止误删。
至于防止误操作导致上述数据库故障案例的方法之一，请读者到老男孩的博客http://oldboy.blog.51cto.com/2561410/1321061查看。
删除
delete from test where id=1; -- 不加条件会清空表
慎用删除。 用uptate伪删除
alter table test add state tinyint(2) not null default 1;
update test set state=0 where name='aige';
清空表
truncate table test;
truncate和delete的区别
- truncate不带where子句的delete语句功能相同：二者均删除表中的全部行，但truncate比delete速度快
- truncate通过释放存储表数据所用的数据页来删除数据，并且只在事物日志中记录页的释放，因此使用的系统和事物日志资源少。
- delete语句每次删除一行，并在事物日志中为所删除的每行记录一项。
