mysql常用命令
select命令
help select; 查看select用法
select distinct 列名 from tablename; 检索列中不同的值，可以踢出重复值
select * from table_name where name like "hello%"; 查询name字段以hello开头的数据，hello*也行
select * from table_name where name like "%hello"; 查询name字段以hello结尾的数据
select * from table_name where name like "%hello%"; 查询name字段以包含hello的数据
select prod_id from products limit 3; 查询前3行数据
select prod_id from products limit 3,3; 从第3行开始获取3条数据(不包括第3行)
select prod_id from products order by prod_id; 对列名进行排序，排序多个就用逗号分隔
select prod_id from products order by prod_id desc; 降序，在升序语句后加desc关键字即可
select id,age from abc orderby id desc,age; 对id进行降序，然后对id对应的age进行升序
select price from abc order by price desc limit 1; 获取最大值(先降序，然后获取第一条数据)
select name from abc where id = 10; 获取id=10对应的name的值
select id,name from abc where id > 10 order by id; 获取id大于10的name值并对id进行排序，order by 要放在where 后面
select id,name from abc where id <> 100; 获取id不等于100的值，<>与!=等价
select id,name from abc where id between 10 and 20; 获取id范围在10-20之间的值
select id,name from abc where name is null; 空值检查
select id,name from abc where id > 10 and id <100; 检索同时满足两个条件的数据，优先级高于or
select id,name from abc where id > 10 or id < 5 ; 检索满足其中一个条件的数据，优先级低于and
select id,name from abc where id in (10,5); in关键字与上面的or用法相同，都表示或
select id,name from abc where id not in (10,5); 获取id不匹配10和5的数据
select id,name from abc where name like "gong%"; 获取所有以gong开头的name的值,可匹配多个
select id,name from abc where name like "%gong%"; 获取包含gong的name的值,匹配多个
select id,name from abc where name like "g%n"; 获取以g开头以n结尾的name的值，匹配多个
select id,name from abc where name like "_ong"; 匹配ong前面的任意一个字符的name值
select id,name from abc where name regexp "gong"; 正则匹配包含gong的name的值，不区分大小写
select name from abc where name regexp binary "gong";正则匹配，并区分大小写(加了binary关键字)
select name from abc where name regexp "gong | guan";匹配gong或者guan的行
select name from abc where name regexp "[123]gong"; 匹配单一字符1、2、3开头后面为gong的内容
select name from abc where name regexp "[1-3]gong"; 与上一条命令相同，简化写法[1-3]
select name from abc where name regexp "\\."; 匹配包含.的内容，通过\\转义才能获取到
select name from abc where name regexp "\\([0-9] sticks?\\)"; 匹配(n stick)和 (n sticks),n为0-9
select id from abc where id regexp "[[:digit:]]{3}"; 匹配任意3个数字
select id from abc where id regexp "[0-9][0-9][0-9]";与上条命令效果相同
select id from abc where id regexp "^[0-9]\\."; 匹配以数字或者.开头的行
select name from abc where name regexp "gong$"; 匹配以gong结尾的name的值
select Concat(name,'(',address,')') from abc; 通过Concat()拼接，返回name(address)值
select Concat(name,'(',address,')') as new_namefrom abc; 拼接后的新列名为new_name,as指定别名
select RTrim(name) from abc; 去掉字段右边的空格
select LTrim(name) from abc; 去掉字段左边的空格
select Trim(name) from abc; 去掉字段两边的空格
select Upper(name) AS name_upper from abc; 将获取的结果全部转换为大写
select Lower(name) AS name_lower from abc; 将获取的结果全部转换为小写
select Length(name)AS length_name from abc; 获取字段的长度
select CurTime(); 返回当前时间
select CurDate(); 返回当前日期
select Data(order_date) from orders; 返回日期时间字段中的日期部分
select Day(order_date) from orders; 返回日期时间字段中的天数部分
select Hour(order_date) from orders; 返回日期时间字段中的小时部分
select Minute(order_date) from orders; 返回日期时间字段中的分钟部分
select Month(order_date) from orders; 返回日期时间字段中的月份部分
select Second(order_date) from orders; 返回日期时间字段中的秒部分
select Time(order_date) from orders; 返回日期时间字段中的时间部分
select Year(order_date) from orders; 返回日期时间字段中的年份部分
select Now(); 获取当前日期和时间
select avg(age) as age_avg from abc; 获取年龄的平均值
select count(age)as age_count from abc; 获取年龄列的行数
select min(age) as age_min from abc; 获取年龄的最小值
select max(age) as age_max from abc; 获取年龄的最大值
select sum(age) as age_sum from abc; 获取年龄的和
select database(); 查看当前所在数据库
select id,count(*)as id_count from abc group by id; 对id进行分组后计算每个组的总数
select id,count(*)as id_count from abc group by id having count(*) > 10; 过滤分组
#group by 要放在where之后，order by 之前，否则报错
#having与where区别是：where在分组前过滤行，having在分组后进行过滤
select cust_name from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = "TNT2"));子查询，首先根据产品prod_id来检索出订单信息order_num,然后根据订单信息order_num来检索出客户id为cust_id,最后根据cust_id来获取客户信息cust_name
select vend_name,prod_name from products,vendors where vendors.vend_id = products.vend_id; 等值联结查询，vend_name和prod_name分别位于products和vendors表中，vend_id是vendors表的主键并且是products表的外键，因此可使用联结查询同时获取两个表的字段
select vend_name,vend_address,prod_name from products inner join vendors on vendors.vend_id = products.vend_id; 与上条语句作用相同，where替换为on，使用inner join，这是推荐的用法
#注：联结多个表可以使用AND(在where或者on后面的多个条件之间)，联结会导致性能下降，注意使用
select tb1.column tc1,table2.column tc2 from table1 tb1 inner join table2 tb2 on tb1.column = tb2.column; 通过inner join实现多表查询,tb1和tb2是表table1和table2的别名,tc1和tc2是查询后指定的列别名
select c.* from customers AS c; 获取表customers的所有列,c是表别名
select id,price from where id > 5 union select id,price from where price >10; 组合查询
select current_user(); 查询当前连接MySQL的用户
select decode((select password from test WHERE id = 1),’123456789′); 解密，通过加密字符串，查询后就是加密之前的密码
SHOW VARIABLES LIKE 'server_uuid'; 查看server_uuid
show slave hosts; 查看mysql从库信息
show slave status; 查看从库状态
SHOW TABLE STATUS LIKE 'table_name'; 获取表信息，用Data_length/1024/1024即可得到表大小
SHOW INDEX FROM your_table_name； 查询表中索引intert into 命令
insert into user values (null,'gong','广东',null); 插入数据，没内容使用Null充当(不安全插入)
insert into user (username,address,email)values('gong','广东'，'abc@qq.com') 插入数据(安全插入)
insert into user (username) value('gong'),('guan'); 插入多行数据
insert into user (username) select username from user where id = 10;通过insert和select配合插入数据
insert into test (username,password)values("admin",select encode('test123456','123456789')); 向表test中添加用户名和密码，密码通过encode的形式加密，test123456为设置的密码，123456789为加密解密字符串(注意：password的字段类型需要为blob,存储二进制类型)，通过update更新也可以
update与delete命令
update user set username="guan" where id = 100; 更新单列的值
update user set username="guan",address="深圳" where id = 100; 更新多列的值
update user set username= null where id = 100; 删除某个列，设为null
update user set password = password("passwd") where user = "root"; 修改用户密码
delete from user where id = 100; 删除id为100的整行用户
delete from user; 删除整个表的数据，结构不变
drop table user; 删除整个表create命令
#创建表，主键必须not null，id列设置为自增,age默认值为10,没有默认可不用写
create table if not exists user (
id int not null auto_increment,
username varchar(10) not null,
age int not null default 10,
address varchar(100) null,
email varchar(100) null,
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
#注：主键可以有多个列组成，如果是单个列，那么此列唯一，如果多个列，组合值必须唯一
#本例子中引擎为innodb，还可以使用其余引擎，但要注意一个引擎的表不能引用具有使用不同引擎的表的外键
create user username identified by "passwd"; 创建用户username，设置密码为passwd
create table 表a like 表b； 根据表b的结构创建表a，不包含数据
create table newTable as select * from oldTable; 根据旧表创建新表，包括数据alter命令
alter table user add age int; 给表user添加列age，指定数据类型
alter table user add age int default 10; 给表user添加列age，默认值为10
alter table user drop column age; 删除表user中的列age
alter user username identified by "passwd"; 修改用户密码
alter table oldtable rename to newtable; 修改表名
alter table user modify column age varchar(10) default null;修改age类型为varchar，默认空
alter table tablename change column age agef varchar(50);修改列名从age到agef
alter table tablename auto_increment=4; 重置自增键从4开始
alter table tablename add id int primary key not null auto_increment first;添加id放在开始位置
alter table tablename drop column id; 删除id字段
alter table tablename modify id int auto_increment primary key;修改id为自增并设为主键
alter table tablename add unique(`id`); 添加唯一约束，括号内为反引号
alter table user modify column created_on datetime default CURRENT_TIMESTAMP;修改表名user中的列created_on的类型为datetime，默认值为CURRENT_TIMESTAMP
alter table tablename modify 字段1 字段类型 after 字段2; 将字段1移动到字段2后面
alter table tablename modify 字段1 字段类型 first; 将字段1移动到最前面
其余命令
help create table; 查看创建表语法
rename user oldusername to newusername; 重命名用户名
drop user username; 删除用户
show grants for username; 查看用户具备的权限
grant select on test.* to username; 给用户username查询test库下全部表的权限
grant update on test.* to username; 给用户username更新test库下全部表的权限
grant delete on test.* to username; 给用户username删除test库下全部表的权限
grant insert on test.* to username; 给用户username向test库下全部表插入数据的权限
grant all privileges on test.* to username; 给用户username操作test库下所有表的权限
grant all privileges on `abc_%`.* to user@'%' identified by 'passwd'; #给用于赋予abc_开头的所有库权限,注意abc那里是反引号
revoke select on test.* from username; 回收用户的查询权限
revoke update,insert on test.* from username;回收用户更新插入数据权限
set password for username = password("密码");设置用户密码
analyze table tablename; 检查表键是否正确，如有异常在Msg_text输出
show status; 查看MySQL运行状态
show variables; 查看MySQL服务器的配置参数
show variables like "tran%"; 筛选以tran为开头的参数信息
show processlist; 查看所有活动进程
show slave status\G; 查看主从库的状态
show databases; 查询所有数据库信息
show table status from dbname where name="tablename";查看某个库下指定表的存储引擎
show table status from dbname； 查看库下所有表的存储引擎
show create database dbname; 查看创建库的语句
show create database tablename; 查看创建表的语句
kill id; 杀掉某个进程
mysqldump -u root -p --single-transaction --databases dbname > dbname.sql #导出数据库和里面的表(包括数据,加了--single-transaction后不会锁表，保持数据一致性，降低性能影响)
mysqldump -u root -p --single-transaction dbname tbname > tbname.sql 备份指定表的结构和数据
mysqldump -u root -p --single-transaction --all-databases > all.sql 导出全部数据库
mysqldump -u root -p --single-transaction --databases dbname --tables table1 table2 > two_table.sql 导出多张表(包含表结构与数据，如果表被删除，可以通过此备份恢复)
mysqldump -u root -p --single-transaction -d dbname tablename > table.sql 加了-d参数，只导出表结构不导表数据(如果表删除了，可通过此导出sql重新创建表，但是表中没有数据)
mysqldump -u root -p --single-transaction -t dbname tablename > table.sql 加了-t参数，只导出表数据不导表结构(如果表删除了那么没有结构无法恢复)
mysqldump -u root -p --single-transaction --no-data dbname tablename > dbtable.sql 导出数据库以及表结构，没有数据(如果数据库被删除，通过此sql可恢复数据库以及里面的表，但是表中无数据)
optimize table table_name; 删除表后进行碎片清理(会锁表，空闲时间执行)
mysql -u root -p < a.sql > b.txt #执行a.sql并将结果导入到b.txt中
mysqldump -u root -p --single-transaction --no-data dbname > dbtable.sql
1、mysql执行触发器，如何忽略语句中间的分号？
在MySQL中，触发器（Trigger）是用户定义的SQL语句的集合，它们在指定的数据库操作发生时自动执行。如果你在执行触发器时遇到了分号（;）的问题，通常是因为触发器的定义中包含了多个语句，而这些语句需要正确的分隔符，在MySQL中，触发器默认使用分号作为语句的结束符。但是，如果你的触发器包含多个语句，你需要改变语句的分隔符，通常使用$$或其他自定义的分隔符，如下：
DELIMITER $$
CREATE TRIGGER your_trigger_name
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
-- 你的触发器逻辑
INSERT INTO another_table (column1, column2) VALUES (NEW.column1, NEW.column2);
END $$
DELIMITER ;在这个示例中，我们首先使用DELIMITER $$改变了语句的分隔符，这样我们就可以在触发器定义中使用分号而不会导致命令提前结束。然后，我们定义了触发器，并在结束时使用END $$来标识触发器定义的结束。最后，我们用DELIMITER ;将分隔符改回默认的分号
2、mysql5.7如何禁用ssl？
修改my.cnf配置文件，添加参数ssl=0即可
3、mysql从库设置只读？
read_only=1 #my.cnf中配置，表示可读写
read_only=0 #表示只读如果设置read_only=1，那么普通用户只能执行select查询，不能执行insert update delete create等操作，但是root用户仍然可以执行写入操作，如果要限制超级用户的权限，可以通过如下参数：
super_read_only=1 #超级用户也不能写入配置了super_read_only=1后的注意事项：
- 不会影响复制线程，也就是主从不受影响
- 临时表不受限制：用户仍然可以修改和创建临时表
- 设置了super_read_only=1那么read_only自动为，但是设置read_only=1不会自动设置super_read_only=1