mysql 入门

mysql 采用客户/服务器体系结构，MySQL服务器程序指的是mysqld，运行在数据库机器上。负责在网络上监听处理客户请求，根据请求访问数据库，再把相关信息回传给客户。

另一个程序是mysql客户程序，负责连接到数据库服务器，并向服务器发出命令，告知它们需要的信息。

mysql并发控制由服务器提供，不会出现两个用户同时修改一条记录的情况，来自客户的请求全部经过服务器，由服务器安排它们的处理先后顺序.

windows 重启mysql

C:UsersAdministrator>net stop mysql

MySQL 服务正在停止.....

MySQL 服务已成功停止。

C:UsersAdministrator>net start mysql

MySQL 服务正在启动 .

MySQL 服务已经启动成功。

查看存储过程

show procedure status where name like 'Pay%';

select *from MySQL.proc

select *from information_schema.ROUTINES

查看正在执行的进程

show processlist;

语句的终止符除了;以外还有g

G 竖排显示结果

mysql> select now(),user() g
+---------------------+----------------+
| now() | user() |
+---------------------+----------------+
| 2018-02-26 17:07:27 | root@localhost |
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select now(),user();
+---------------------+----------------+
| now() | user() |
+---------------------+----------------+
| 2018-02-26 17:07:38 | root@localhost |
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select now(),user() G
*************************** 1. row ***************************
now(): 2018-02-26 17:07:45
user(): root@localhost
1 row in set (0.00 sec)

如果写了多行代码，不想执行它，可以c

mysql> select now() select user()
-> select a
-> c

describe 表名查看表结构；可以简写成desc

explain 表名可以达到同样的效果

create table NAME
(id int(10),
name varchar(50))
ENGINE=MYISAM

创建表后一个ENGINE子句的作用，它用来指定创建的数据表的存储引擎的名字,一种存储引擎就是一种管理某种特定类型数据表的处理器，如果省略引擎，mysql会替你选择一个默认引擎.

ISAM 索引化顺序访问方法的缩写

MYISAM 与InnoDB引擎的区别：

MYISAM 不支持事务，InnoDB支持事务；

MYISAM 不支持外键，InnoDB支持外键；

MYISAM 表级锁，DELETE 表时，先drop表，然后重建表;InnoDB支持行级别锁，DELETE 表时，是一行一行的删除；

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM；

MyISAM管理非事务表，它提供高速存储和检索，以及全文搜索能力，查询多，写入少可以使用；读少，写入多，尤其是并发写入高的时候，选择InnoDB；

查前10条记录：

select daynum,count(distinct accid)
from MonitorServer.user_account_9041
where daynum between 20180201 and 20180226 and platid=0
group by daynum
order by daynum desc limit 10

结果如下：

20180226 326
20180225 116
20180224 120
20180223 122
20180222 126
20180221 135
20180220 115
20180219 103
20180218 114
20180217 121

查询跳过前10条查询的前5条记录：

select daynum,count(distinct accid)
from MonitorServer.user_account_9041
where daynum between 20180201 and 20180226 and platid=0
group by daynum
order by daynum desc limit 10,5

结果如下：

20180216 148
20180215 84
20180214 87
20180213 70
20180212 91

随机读取5条记录：

select daynum,count(distinct accid)
from MonitorServer.user_account_9041
where daynum between 20180201 and 20180226 and platid=0
group by daynum
order by rand() desc limit 5

结果如下：

20180201 28
20180208 124
20180224 120
20180222 126
20180226 326

show character set; 查看有哪些字符集可以使用

show collaction;查看有哪些排序方式可以使用

show CHARACTER set like 'utf8%'

查看mysql服务器当前字符集以及排序方式：

show variables;

创建数据库并指定数据集以及排序方式：

create DATABASE gamelog CHARSET utf8 COLLATE utf8_general_ci;

mysql> create database gamelog charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.03 sec)

mysql> show create database gamelog G
*************************** 1. row ***************************
Database: gamelog
Create Database: CREATE DATABASE `gamelog` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

show engines; 可以查出服务器所知道的存储引擎.

mysql> show engines G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
6 rows in set (0.00 sec)

select * from information_schema.engines G 同样的效果

每创建一个表，mysql就会创建一个磁盘文件保存该数据表的格式，这个格式的文件基本名和表名一样，扩展名为.frm.

level表的表类型为InnoDB，只有一个文件level.frm

name表的表类型为MyISAM，除了文件name.frm外，还有name.MYD,name.MYI两个文件

memory引擎的数据表存在于内存，掉电后，表内容会清空，特点是对其他用户可见，这一点区别于create temporary table,temporary 表只对创建它的连接可见。

merge引擎 merge数据表提供了把多个myisam表合并为一个逻辑单元的手段；用来构成merge表的所有表必须有同样的结构，意味着列名，类型，排序，以及索引都要有相同的顺序。

mysql> create table log1(id int(10),message varchar(100));
Query OK, 0 rows affected (0.08 sec)

mysql> create table log2(id int(10),message varchar(100));
Query OK, 0 rows affected (0.03 sec)

mysql> create table log_merge(id int(10),message varchar(100)) engine=merge union=(log1,log2);
Query OK, 0 rows affected (0.07 sec)

分区表

类似merge表，但分区表每个分区都存放的货真价实的数据。

分区优点：存储存放在不同设备上，i/o并行，缩短访问时间；同时访问多个分区；

create table gamelog
(createtime datetime,
message varchar(100),
index(createtime))
PARTITION by RANGE(YEAR(createtime))
(
PARTITION p1 VALUES less than (2015),
PARTITION p2 VALUES less than (2016),
PARTITION p3 VALUES less than (2017),
PARTITION p4 VALUES less than (2018),
PARTITION p5 VALUES less than MAXVALUE
);

mysql> create table gamelog
-> (createtime datetime,
-> message varchar(100),
-> index(createtime))
-> PARTITION by RANGE(YEAR(createtime))
-> (
-> PARTITION p1 VALUES less than (2015),
-> PARTITION p2 VALUES less than (2016),
-> PARTITION p3 VALUES less than (2017),
-> PARTITION p4 VALUES less than (2018),
-> PARTITION p5 VALUES less than MAXVALUE
-> );
Query OK, 0 rows affected (0.05 sec)

修改分区：

alter table gamelog reorganize PARTITION p5
into
( PARTITION p5 VALUES less than (2019),
PARTITION p6 VALUES less than MAXVALUE
);

如果想把分区分配到其他位置硬盘，则需要data_directory和index_directory分区选项

查看表索引：

mysql> show index from role;
Empty set (0.00 sec)

子句返回结果必须唯一：

select * from role where

(roleid,`level`)=(
select roleid,`level` from role
where roleid=40)

all，any是比较常见的比较操作符

select * from role
where `level`>=ALL(SELECT 5 UNION SELECT 10);

select * from role
where `level`>=ANY(SELECT 5 UNION SELECT 10);

select * from role where
(roleid,`level`)= ANY(
select roleid,`level` from role
where sex=0)

序列

一个表只能有一列具有AUTO_INCREMENT属性

last_insert_id() 可以返回最后生成的AUTO_INCREMENT值

查看当前数据库：

mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)

mysql> create table name_test(id int(10) auto_increment,name varchar(100),primary key(id));
Query OK, 0 rows affected (0.08 sec)

mysql> show create table name_test;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name_test | CREATE TABLE `name_test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into name_test(name) select 'tian';select last_insert_id();
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id()
-> ;
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

数据类型的选用

数据列容纳什么样的数据，数据范围，效率，空间等

存储程序，泛指各种类型的存储对象（存储函数，存储过程，触发器，事件）

存储例程，特指存储函数，存储过程

存储函数直接用select 调用，存储过程使用call调用

使用事件必先启用事件调度器

show VARIABLES like 'event%' 查看事件调度器是否启用

索引，在加快查询的同时，会降低插入，删除，修改数据的速度，还会占用磁盘空间

尽量使用搜索，分类或者分组的数据列编制索引，不要增加无用的索引。

综合考虑数据列的维度势。重复率越低，索引使用的效果越好，当某个值在数据表中出现的概率超过30%，查询优化程序通常会跳过索引，进行全表扫描。

对短小的数据进行索引，选用尽量小的数据类型。

复合索引，充分利用最左边的前缀。

尽量是用数据类型相同的列进行比较

不要在like模式开始位置使用通配符

避免过多使用类型自动转换功能

使用explain语句检查优化器操作

是否支持查询缓存

mysql数据库系统由若干组件组成

mysql服务器，服务器端主程序mysqld是没一个mysql数据库的核心，对数据库和数据表的一切操作都通过它来完成。

mysql客户程序和实用工具

1.mysql. 一个用来向mysql服务器发送sql并查看结果的交互式程序.

2.mysqladmin.这个系统管理程序用来完成很多任务，如关停服务器，检查它的配置

等

mysql的数据目录

[root@host test]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
federated

分组

mysql> select * from name1 where id=5;

+------+------+

| id | name |

+------+------+

| 5 | ggga |

+------+------+

6 rows in set (0.00 sec)

mysql> select id,group_concat(name) from name1 where id=5 group by id;

+------+-------------------------------+

| id | group_concat(name)

| +------+-------------------------------+ |

5 | ggga,ggga,ggga,ggga,ggga,ggga |

+------+-------------------------------+ 1

row in set (0.00 sec)