---------创建数据库
CREATE DATABASE zhcz_oa DEFAULT CHARACTER SET utf8mb4;
---------查询存在的数据库
show databases;
---------选择数据库
use zhcz_oa;
---------删除数据库
drop database zhcz_oa;
---------创建表
create table emp(
id bigint(20) not null auto_increment comment 'id',
created_by varchar(36) not null comment '创建者',
create_date datetime not null comment '创建时间',
primary key (id) using btree
) engine=innodb auto_increment=140 default charset=utf8 row_format=dynamic comment='用户群组表';
---------查看表定义
desc emp;
---------查看建表语句
show create table emp G
---------删除表
drop table emp;
---------修改表类型
alter table emp modify created_by varchar(20);
---------增加表字段
alter table emp add column age int(3);
---------删除表字段
alter table emp drop column age;
---------修改字段名
alter table emp change age agele int(4);
---------修改表名
alter table emp rename empel;
---------插入数据
insert into emp (id,created_by,create_date,age) values(1,'zzy','2020-08-06','1000');
insert into emp values (2,'zzy','2020-08-06','10001');
insert into emp (created_by,create_date,age) values('zzy','2020-08-06',1002);
---------更新数据
update emp set id=4 where id=140;
update emp set create_date='2020-08-06',age='1003' where id=3;
---------删除数据
delete from emp where id=2,id=3;
---------查询数据
select * from emp;
---------查询不重复数据
select distinct age from emp;
---------条件查询
select * from emp where create_date='2020-08-06';
---------排序查询
select * from emp order by age;
---------限制查询
select * from emp order by age limit 1,3;
---------聚合查询
select age,count(1) from emp group by age having count(1) >1;
---------表连接查询
select created_by,deptname from emp,dept where emp.depton=dept.depton;
select created_by,deptname from emp left join dept on emp.depton=dept.depton;
select created_by,deptname from emp right join dept on emp.depton=dept.depton;
---------子查询
select * from emp where depton in(select depton from dept);
---------联合查询
select depton from emp union all select depton from dept;
select depton from emp union select depton from dept;
---------授权
grant select,insert on zzy.* to 'zzy'@'localhost' identified by '123';
---------收回授权
revoke insert on zzy.* from 'zzy'@'localhost';
---------获取帮助
? contents
? show
---------查询字符长度
select length(v),length(c) from vc;
---------字段追加字符
select concat(v, '+'), concat(c, '+') from vc;
---------查询插入的json数据类型
select json_type('"abc"') js1,json_type('[1,2,"abc"]') js2,json_type('{"k1":"value"}') js3;
---------判断json数据是否合法
select json_valid('null') n1,json_valid('NULL') n2,json_valid('false') f1,json_valid('FALSE') f2;
---------显示当前日期,年、月、日
select curdate();
---------显示当前时间,时、分、秒
select curtime();
---------显示当前日期和时间,包含年、月、日、时、分、秒
select now();
---------计算两个日期之间相差的天数
select datediff('2054-11-08',now());
---------查询当前登录数据库名
select DATABASE();
---------查询当前数据库版本
select VERSION();
---------查询当前登录用户名
select USER();
---------查询默认存储引擎
show variables like 'default_storage_engine';
---------查询表的存储引擎
select table_name,engine from information_schema.tables where table_name='dept';
---------修改表的存储引擎
alter table dept engine='myisam';
---------查询外键约束状态
show variables like 'foreign_key_checks';
---------暂时关闭外键约束
set foreign_key_checks=0;
---------查看数据库所有字符集
show character set;
select * from information_schema.character_sets;
---------查看字符集排序规则
show collation like 'utf8%';
select * from information_schema.collations;
---------查看服务器默认字符集
show variables like 'character_set_server';
---------查看服务器默认排序规则
show variables like 'collation_server';
---------查看数据库默认字符集
show variables like 'character_set_database';
---------查看数据库默认排序规则
show variables like 'collation_database';
---------修改字符集
mysqldump -uroot -p --default_character-set=utf8 -d databasename > createtab.sql ---导出表结构
手工修改createtab.sql表结构定义中的字符集为新字符集
mysldumo -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql ---导出表数据
手工修改data.sql表SET NAMES latin1为SET NAMES UTF8
create database databasename default charset utf8 ---创建新数据库
mysql -uroot -p databasename < createtab.sql < createtab.sql ---导入表结构
mysql -uroot -p databasename < data.sql ---导入表数据
----------查询表的索引
show index from dept;
----------创建普通索引
create index index_dept on dept (deptname);
alter table dept add index idx_dept(deptname);
----------删除索引
drop index index_dept on dept;
----------添加不可见索引
create index invisible_idx on emp(age) invisible;
alter table emp add index invisible_idx(age) invisible;
----------修改索引是否可见
alter table emp index invisible_idx invisible;
alter table emp index invisible_idx visible;
----------查看索引使用情况
show status like 'handler_read%';
----------创建视图
create or replace view depton_list_view as select d.depton,d.deptname,e.id,e.create_date,e.depton as e_depton from dept as d,emp as e where d.depton=e.depton;
----------查看视图
show tables;
----------查看视图定义
show create view depton_list_view G;
select * from information_schema.views where table_name='depton_list_view'G;
----------删除视图
drop view depton_list_view;
----------查看事件调度器状态
show variables like 'event_scheduler';
----------创建事件调度器
create event test_event_1 on schedule every 5 second do insert into zzy.test1(create_time) values (now());
----------查看事件调度器
show events G;
----------禁用或删除事件调度器
alter event test_event_1 disable;
drop event trunc_test_1;
----------锁定表 >
lock tables dept read; ---read锁
----------释放表锁
unlock tables;
----------查看表锁
show open tables where in_use > 0;
----------查询表级锁争用情况
show status like 'table%';
----------查询慢查询
pt_query_digest --since '2020-08-27 03:40:00' --until '2020-08-27 04:00:00' --limit 100% prd-138-0020-slow.log > /Data/backup/slow_report.log
----------分析表
analyze table dept;
----------检查表
check table w_dept;
----------优化表
optimize table mt_meeting;

标签: none

添加新评论