【MySQL】安装 ｜ 常用操作 ｜ 常用命令 ｜常用函数

到达安宁

1、Mysql 安装 | 链接

【ubantu 安装 mysql 】linux 安装mysql 和环境配置
【MySQL数据库】pymysql连接MySQL数据库
2、Mysql常用操作

2.1 查看MySQL服务是否启动、启动MySQL服务、关闭MySQL服务
# 查看MySQL服务是否启动
ps -ef | grep mysqld

# 启动MySQL服务
cd /usr/bin
./mysqld_safe &

# 关闭MySQL服务
cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******2.2 MySQL 用户（在 mysql 数据库中的 user 表添加新用户即可）
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user
          (host, user, password,
           select_priv, insert_priv, update_priv)
           VALUES ('localhost', 'guest',
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+2.3 显示数据库和当前表信息
show databases            # 显示数据库信息
show table                # 显示数据表的属性
show column from table    # 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
show table status from databases like 'abc%';     # 表名以abc开头的表的信息3、MySQL数据类型

3.1 数值类型

类型	大小
tinyint	1 Bytes
smallint	2 Bytes
mediumint	3 Bytes
int或integer	4 Bytes
bigint	8 Bytes
float	4 Bytes
double	8 Bytes

3.2 日期

类型	大小	格式	用途
date	3 Bytes	YYYY-MM-DD	日期值
time	3 Bytes	HH:MM:SS	时间值或持续时间
year	1 Bytes	YYYY	年份值
datetime	8 Bytes	YYYY-MM-DD hh:mm:ss	混合日期和时间值
timestamp	4 Bytes	YYYY-MM-DD hh:mm:ss	混合日期和时间值时间戳

[/table]3.3 字符串类型

类型大小用途char0-255 bytes定长字符串varchar0-65535 bytes变长字符串tinyblob0-255 bytes不超过 255 个字符的二进制字符串tinytext0-255 bytes短文本字符串blob0-65 535 bytes二进制形式的长文本数据text0-65 535 bytes长文本数据mediumblob0-16 777 215 bytes二进制形式的中等长度文本数据mediumtext0-16 777 215 bytes中等长度文本数据longblob0-4 294 967 295 bytes二进制形式的极大文本数据longtext0-4 294 967 295 bytes极大文本数据4、MySQL常用命令

4.1 创建数据库和删除数据集

create database 数据库名;

drop database 数据库名;4.2 创建数据表和删除数据表

create table table_name (column_name column_type);

# 例子：创建table1表

create table if not exists table1(

`id` int unsigned auto_increment,

`title` varchar(100) not null,

`author` varchar(40) not null,

`date` date,

primary key ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table table_name ;     # 删除数据表4.3 insert插入数据

insert into table_name ( field1, field2,...fieldN )

values

( value1, value2,...valueN );

# 例子，向table1表里插入数据

insert into table1

(title, author, date)

values

("mysql", "wenjtop", NOW());4.4 select查询数据

select column_name,column_name

from table_name

[where Clause]

[limit N][ offset M]

# 例子：查询table1里title等于‘mysql’行的数据。

select * from table1 WHERE title='mysql';limit 属性来设定返回的记录数,offset指定select语句开始查询的数据偏移量。

4.5 update 更新

update table_name set field1=new-value1, field2=new-value2

[where Clause]

# 例子：把table1表里id等于3行的title更新为‘mysql’。

update table1 set title='mysql' where id=3;4.6 delete 删除

delete from table_name [where Clause]

# 例子：删除table1表里id等于3的行。

delete from table1 WHERE id=3;4.7 where 子句

where 子句运用delect 或者 update 命令，使用 and 或者 or 指定一个或多个条件，类似于程序语言中的 if 条件

操作 描述 = (A = B) 返回false。 <>, != (A != B) 返回 true。 > (A > B) 返回false。 < (A < B) 返回 true。 >= (A >= B) 返回false。 <= (A <= B) 返回 true。 where binary 比较时区分大小写 4.8 like 子句匹配

select field1, field2,...fieldN

from table_name

where field1 like condition1 [AND [OR]] filed2 = 'somevalue'

# 例子：查看tb1表里field1 以abc结尾的行。

select * from tbl  where field1 LIKE '%acb';4.9 union 操作符

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
select expression1, expression2, ... expression_n
from tables1
[where conditions]
union [all | distinct]
select expression1, expression2, ... expression_n
from tables2
[where conditions];

• DISTINCT: 删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据。
  
• ALL: 可选，返回所有结果集，包含重复数据。
  

4.10 order by 排序

select field1, field2,...fieldN from table1, table2...
order by field1 [asc|DESC], [field2...] [asc|DESC]

# 例子：查询table1里按时间升序
select * from table1 order by date asc;asc：升序排列，desc 降序排列。 默认情按升序排列。
4.11 group by 分组

select column_name, function(column_name)
from table_name
where column_name operator value
group by column_name;

# 统计name相同行的数量
select name, count(*) from table1 group by name;

# 按名字进行分组，再统计每个人登录的次数，名字相同，signin属性相加。总数行name显示成null。
select name, SUM(signin) as signin_count from  table1 group by name with rollup;

# 按名字进行分组，再统计每个人登录的次数，名字相同signin属性相加，把name属性名显示成coalesce(name, '总数')，null显示成总数
select coalesce(name, '总数'), SUM(signin) as signin_count from  table1 group by name with rollup;with rollup 可以实现在分组统计数据基础上再进行相同的统计
4.12 连接的使用(把两个表的信息，一起输出)

• inner jion（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  
• left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录，没有对应的属性就显示NULL。
  
• right join（右连接）： 获取右表所有记录，即使左表没有对应匹配的记录，没有对应的属性就显示NULL。
  

select a.id, a.author, b.count
from table1 a
inner jion table2 b
ON a.author = b.author;

# 等价于
select a.id, a.author, b.count
from table1 a, table2 b
where a.author = b.author;4.13 NULL 值处理

• IS NULL: 当列的值是 NULL,此运算符返回 true。
  
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
  

# 查询count属性不为NULL的行
select * from table1 where count IS NOT NULL;4.14 正则表达式

模式描述^匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ' ' 或 '\r' 之后的位置。$匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ' ' 或 '\r' 之前的位置。.匹配除 " " 之外的任何单个字符。要匹配包括 ' ' 在内的任何字符，请使用像 '[. ]' 的模式。[...]字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。[^...]负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。p1

p2

p3匹配 p1 或 p2 或 p3。例如，'z

food' 能匹配 "z" 或 "food"。'(z

f)ood' 则匹配 "zood" 或 "food"。*匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。+匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。{n}n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。{n,m}m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

4.15 事务（transaction，作用要么全部执行，要么全部不执行）

事务是必须满足4个条件：

• 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
  

事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
  
• ROLLBACK 事务回滚
  
• COMMIT 事务确认
  

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
  
• SET AUTOCOMMIT=1 开启自动提交
  

完整的插入事务：
begin;
insert into table1 id value(5);
insert into table1 id value(5);
commit;完整的插入回滚事务（因为回滚所以数据没有插入，表信息不变）：
begin;
insert into table1 id value(7);
rollback;4.16 alter 删除，添加或修改表字段

alter table table1  drop id;           # 删除id属性
alter table table1  add id int;        # 增加id属性，int型
alter table table1 modify id CHAR(10); # 修改id属性的类型
alter table table1 change id name INT char(100);           # 修改id属性名改为name，类型改为char
alter table table1 modify id bigint NOT NULL default 100;  # 把id为NULL的，设置为100.
alter table table1 alter id set default 1000;    # 把id属性设置为默认1000。创建新数据显示1000。
alter table table1 alter id drop default;        # 删除id属性默认设置1000。创建新数据显示NULL。
alter table table1 rename to table2              # 把表名字table1改为table24.17 index 索引(提高的检索速度)

create index indexName ON table1 (column_name)   # 创建索引
drop index [indexName] ON table1;                # 删除索引

alter table table1 add primary key (column_name);  # 增加主键（索引）
alter table table1 drop primary key (column_name);  # 删除主键（索引）5 mysql常用函数

[table]函数名作用Max()最大值Min()最小值Count()统计行数量Sum()列和值Avg()列平均值curdate()返回当前日期curtime()返回当前时间now()返回当前日期、时间coincat(s1,s2,s3)串连字符串abs(x)取绝对值floor(x)返回小于x的最大整数geiling(x)返回大雨x的最小整数mod(x,y)x%y取余数round(x, y)返回参数x的四舍五入y位小数的值