mysql学习记录

--mysql 学习

--约定
--dbname 数据库名字
--tabname 数据表名字

--------------ddl语句------------
--展示数据库
show database;

--创建数据库
create table dbbane;

--进入数据库
use dbname;

--展示数据库
show tables;

--删除数据库
drop dbname;

--创建数据表 (模板)
create table tabname(
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
)
--demo
create table test(
id int(10),
name varchar(32),
age int(3)
);

--查看表的定义
desc tabname;

--查看详细的表的定义
--可以查看到 表的引擎 和 字符集
show caeate table tabname;

--删除表
drop table tabname;

--修改表 --alter table
alter table tabname modify [column] column_definition [first|after col_name]
--demo
alter table test modify name varchar(10) after id;


--增加表字段
alter table table add [column] colunm_name [first|after col_name]
--demo
alter table test add addr varchar(32) after name;

--删除表的字段
alter table tabname drop [column] col_name;
--demo
alter table tabname drop column addr;

--字段改名
alter table tabname change [column] old_col_name new_col_definition [first|after col_name];
--demo
alter table test change column age userage int(3) after id;

--change modify 都可以修改表的定义,不同的是 change 后面需要填写2次列名,不方便 ,但是 change 的优点是可以修改列名称; modify 不能


--更改表名
alter table tabname rename [to] new_name;


--------------dml语句------------
--单条插入语句
INSERT INTO tabname (field1,field2..) VALUES (val1,val2);
insert into test ('id','name') values ('id','name');

insert into test values(1,2,3);

--多条插入语句 节省网络开销 提高插入效率
insert into tabname (field1,field2)
values
(value1,value2,value3),
(value1,value2,value3),

insert into test
values
(1,20,'test'),
(1,20,'test'),

--更新记录
update tabname
set
field=value , field1=value1
where
condition

update test set name='chenshuai' where id = 1;


--删除语句
delete from tabname where condition;

--查询记录
select * from tabname [where condition]
select f1,f2 from tabname [where condition]

--查询不重复记录 distinct
select distinct field1 from tabname;

--条件查询
-- = > < >= <=
-- and or
select * from tabname where condition;

--排序和限制
select * from tabname [where condition] [order by field[desc|asc], field1[desc|asc] ];
--desc 降序 asc 升序(默认)

--limit
select * from tabname [where condition] [order by field1[asc|desc] ] [limit offset_start,row_count]


--聚合操作
select [field,field1,field2] fun_name
from tabname
[where where_contition]
[group by field1]
[with rollup]
[having where_contition]

--fun_name 聚合函数 sum() count(*) max() min()
--group by 关键字是进行分类聚合的字段,比如要按照部门分类统计员工数量, 部门就该写在 group by 后面
--with rollup 可选,表明是否对分类聚合后的结果进行再汇总
--having 关键字表示对分类后的结果在进行条件的过滤


--表连接--
--内连接 和 外连接
--内连接: 选出两张表中的互相匹配的记录
--外链接: 选出其他不匹配的记录
--外链接: 左链接 右链接
--左链接: 包含所有的左边表中的记录甚至是右边表中没有和他匹配的记录
--右链接: 包含所有的右边表中的记录甚至是左边表中没有和他匹配的记录
select * from a [left|right] join b on a.field = b.field;

--子查询 >mysql4.1 (子查询效率没有表连接高)
--某些情况下,进行查询的时候,需要的条件是两一个条件select语句的结果 这叫子查询
--关键字包括 in ,not in, =, !=, exists, not exists

--记录联合 union union all
--union all 俩表的记录直接联合 union 俩表记录进行一次 (去重)distinct


--DCL 语句, dcl用来主要是用来管理系统中的对象权限时候 一般是DBA


--使用命令行 mysql帮助中心
-- ? 查询全部

--查询元数据

--数据类型--

--数字类型--
TINYINT SMALLINT


--字符类型 char varchar
名称 长度 特点
char 固定长度(0-255) 检索的时候自动删除尾部的空格
varchar 可变长度 <5.3(0-255) >5.3(0-65535) char varchar 存储结构不同 变长


binary 二进制字符串 hex(c)


--enum 枚举类型
enum 枚举(0-255,0-65535) 一个字节|2个字节



--mysql引擎类型--
mysql
> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine
(anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

--第二种--
mysql
> show variables like 'have%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_compress | YES |
| have_crypt | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | YES |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | YES |
| have_statement_timeout | YES |
| have_symlink | YES |
+------------------------+-------+


--myisam--
--不支持事物 标级别锁 有点是访问速度快 适合以 select insert为主的应用

/* .frm (表定义)
.myd (mydata 存储数据)
.myi (myindex 存储索引) */


/* check table ; 检查表
repair table ; 修复表
optimize table; 优化表
mysiamchk-r
myisampack 压缩表
*/

--innodb引擎--
--innodb 具有提交,回滚,和崩溃恢复能力的事物安全, 但是比myisam相比, innodb的处理效率会差一些,并且会占用更多的磁盘空间以保留数据和索引


--查询最后一个插入id--
--SELECT LAST_INSERT_ID();

约束 级联
restrict cascade set null not action


---sql mode
-- show variables like 'sql_mode';
-- select @@sql_mode;

--设置sql模式
-- set [session|global] sql_mode='modes';



--mysql 分区
mysql
> show plugins;
partition
| ACTIVE | STORAGE ENGINE | NULL | GPL |






--hash 分区
--常规分区 取模
--线性分区 这个好



--查看数据在那个分区
-- explain partitions select * from tab_name where id = xxx;
mysql
> explain partitions
select count(1)
from ids
where id = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | ids | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+


---null 字段的处理


--分区管理 range list



--sql优化---
--show [session|global] status 命令

-show global status like 'xxx';

--con_xxx 表示xxx语句执行的次数,我们通常比较关心的是一下几个参数
--每个存储引擎都会记录
--com_select 执行select的操作 每一次查询 累计一次
--com_insert 执行insert的操作 累计一次,批量操作 累计一次
--com_update 执行update的次数
--com_delete 执行delete的次数

--以下这些针对innodb数据库
-- innodb_rows_read select 查询返回的行数
-- innodb_rows_insertd 执行insert操作更新的行数
-- innodb_rows_updated 执行update操作更新的行数
-- innodb_rows_deleted 执行delete操作更新的行数

--根据以上的参数 可以很容易的了解当前数据库应用是以插入更新为主 还是以查询操作为主,
--以及各种类型的sql大致的执行比例
--对于更新操作,是对执行次数的技术,不论提交还是回滚都会进行累加

--connections 试图链接mysql数据库的次数
--uptime 服务器工作时间
--slow_queries 慢查询的次数


--定位执行效率较低的sql语句
--慢查询日志
mysql
> show variables like '%slow%';
+---------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/var/mysql/chen-slow.log |
+---------------------------+------------------------------------+

-- show processlist 命令 查询当前mysql在进行的线程,包括 线程状态,是否锁表 实时查看sql执行情况
mysql
> show processlist;
+-----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| 37 | event_scheduler | localhost | NULL | Daemon | 95094 | Waiting on empty queue | NULL |
| 986 | root |
localhost:
62536 | NULL | Sleep | 2695 | | NULL |
| 987 | root |
localhost:
62593 | mgtest | Sleep | 2687 | | NULL |
| 988 | root |
localhost:
62652 | sakila | Sleep | 2672 | | NULL |
| 989 | root | localhost | sakila | Query | 0 | starting | show processlist |
+-----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
tags: Mysql