MySQL基本使用备忘录（for mac）

作者: android_ls | 来源:发表于2017-12-25 21:49 被阅读17次

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

MySQL是开源免费的数据库，现已经被Oracle收购了，从MySQL6.x版本也开始收费。下载地址：https://dev.mysql.com/downloads/mysql/

Sequel Pro 是Mac用户常用的MySQL管理软件，经亲自体验非常容易上手。下载地址：http://www.sequelpro.com

1、配置环境变量，在终端命令行输入

ls -all

open .bash_profile

在.bash_profile 文件的末尾添加下面这行，之后保存文件

export PATH=${PATH}:/usr/local/mysql/bin

2、查看MySQL版本号（在终端命令行输入）

mysql --version

3、使用root账户登录mysql（若出现问题，自行度娘）

mysql -u root -p

需要输入你自己设置的登录密码

exit; // 退出当前操作

4、通过MySQL的命令行检查编码

show variables like '%char%';

编辑MySQL的配置文件，把数据库默认的编码全部改为UTF-8。

cd /usr/local/mysql

open support-files

复制my-default.cnf文件到桌面，并重命名为my.cnf，打开修改如下内容

[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

将修改后的文件my.cnf复制到/etc目录下，重启mysql
注：如果MySQL的版本≥5.5.3，可以把编码设置为utf8mb4，utf8mb4和utf8完全兼容，但它支持最新的Unicode标准，可以显示emoji字符。（这个我目前没试过，在网上看到的）

SQL分类
1、数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，数据表，列等。关键字：create，alter，drop等
2、数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
3、数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
4、数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

SQL通用语法
1、SQL语句可以单行或多行书写，以分号结尾
2、可使用空格和缩进来增强语句的可读性
3、MySQL数据库的SQL语句不区分大小写，建议使用大写，例如：

SELECT * FROM user。

4、可以使用/**/的方式完成注释

MySQL中的我们常使用的数据类型如下：

整数类型    
tinyInt 很小的整数
smallint    小的整数
mediumint   中等大小的整数
int(integer)    普通大小的整数

小数类型    
float   单精度浮点数
double  双精度浮点数
decimal（m,d）压缩严格的定点数

日期类型    
year    YYYY  1901~2155
time    HH:MM:SS  -838:59:59~838:59:59
date    YYYY-MM-DD 1000-01-01~9999-12-3
datetime    YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestamp YYYY-MM-DD HH:MM:SS  1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC

文本、二进制类型    
CHAR(M)         M为0~255之间的整数
VARCHAR(M)      M为0~65535之间的整数
TINYBLOB    允许长度0~255字节
BLOB        允许长度0~65535字节
MEDIUMBLOB  允许长度0~167772150字节
LONGBLOB    允许长度0~4294967295字节
TINYTEXT    允许长度0~255字节
TEXT        允许长度0~65535字节
MEDIUMTEXT  允许长度0~167772150字节
LONGTEXT    允许长度0~4294967295字节
VARBINARY(M)允许长度0~M个字节的变长字节字符串
BINARY(M)   允许长度0~M个字节的定长字节字符串

一、创建数据库操作

1、创建数据库

CREATE DATABASE db_liehuo; // 数据库中数据的编码采用的是安装数据库时指定的默认编码utf8

CREATE DATABASE db_liehuo CHARACTER SET utf8; // 创建数据库并指定数据库中数据的编码

2、查看数据库

 show databases; // 查看数据库MySQL服务器中的所有的数据库

show create database db_liehuo; // 查看某个数据库的定义的信息

3、删除数据库

drop database db_liehuo;

4、切换数据库（我想使用那个就切换到那个）

use db_liehuo;

5、查看正在使用的数据库

select database();

二、创建数据表

1、创建数据表结构

格式：
create table 表名(
     列名1 数据类型 约束,
     列名2 数据类型 约束,
     列名3 数据类型 约束
);

 CREATE TABLE tab_users (
     uid INT PRIMARY KEY AUTO_INCREMENT,
     uname VARCHAR(20),
     passwd VARCHAR(6)
 ) AUTO_INCREMENT = 100000;

注：a、创建用户表，用户编号，姓名，密码
b、将编号列，设置为主键约束，保证列的数据唯一性，非空性PRIMARY KEY，
让主键列数据实现自动增长AUTO_INCREMENT，从指定数字开始自增长AUTO_INCREMENT = 100000

2、显示所有数据表

show tables;

3、查看表中结构

desc tab_users;

4、删除数据表

drop table tab_users;

5、添加列（添加字段）

格式：alter table 表名 add 列名 数据类型 约束

ALTER TABLE tab_users ADD phone varchar(11);

修改后可通过下面的sql语句进行查看表结构

desc tab_users;

6、修改列名，在原有的列上修改

格式：alter table 表名 change 旧列名 新列名 数据类型 约束

ALTER TABLE tab_users CHANGE phone tel int;

7、数据类型约束，在原有的列上修改

格式：alter table 表名 modify 列名 数据类型约束

ALTER TABLE tab_users MODIFY tel VARCHAR(50);

8、删除列

格式：alter table 表名 drop 列名

ALTER TABLE tab_users DROP tel;

9、修改表名

格式：rename table 表名 to 新表名

RENAME TABLE tab_users TO users;

三、向数据表中添加数据

CREATE TABLE product(
   -- 主键列，自动增长
   id INT PRIMARY KEY AUTO_INCREMENT,
   -- 商品名字，可变字符,非空
   pname VARCHAR(100) NOT NULL,
   -- 商品的价格，double
   price DOUBLE
);

1、向数据表中添加数据（新增）

格式：insert into 表名(列名1,列名2,列名3) values (值1,值2,值3)

INSERT INTO product (id,pname,price) VALUES (1,'笔记本',5555.99);
INSERT INTO product (id,pname,price) VALUES (2,'智能手机',9999);

2、不考虑主键（主键自增长的）

INSERT INTO product (pname,price) VALUES('洗衣机',800);

3、所有值全给出

INSERT INTO product VALUES (4,'微波炉',300.25);

4、批量添加数据

格式：insert into 表名 (列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3)

INSERT INTO product (pname,price) VALUES 
('智能机器人',25999.22),
('索尼电视',1250.36),
('mix2全面屏手机',3200);

四、修改数据

1、对数据进行更新操作（重新赋值）

格式：update 表名 set 列1=值1,列2=值2 where 条件

update product set price=4999.9 where id = 2;

UPDATE product SET pname='智能电视机', price=10000 WHERE id = 6;

2、修改条件的写法

id=6
id<>6 // 不等于
id<=6

&& --> and
|| --> or 
!  --> not

id in (1,3,4,5,6) // 包含

UPDATE product SET price = 2000 WHERE id = 1 OR id = 7;

3、删除表中的数据

格式：delete from 表名 where 条件

delete from product where id=7;

4、删除整个数据表

删除表中的所有数据，保留表结构

delete from product;

表结构和数据都被删除了

drop table product;

五、查询数据

CREATE TABLE zhangwu (
  id INT PRIMARY KEY AUTO_INCREMENT, -- 账务ID
  zname VARCHAR(200), -- 账务名称
  zmoney DOUBLE -- 金额
);

INSERT  INTO zhangwu(id,zname,zmoney) VALUES (1,'吃饭支出',247);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (2,'工资收入',12345);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (3,'服装支出',1000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (4,'吃饭支出',325);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (5,'股票收入',8000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (6,'打麻将支出',8000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (7,NULL,5000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (8,'买bit币',3500);
INSERT  INTO zhangwu (zname) VALUES ('彩票收入');

/**查询所有列的数据*/
select * from zhangwu;

/**查询指定列的数据*/
SELECT zname,zmoney FROM zhangwu;

/*
 查询去掉重复记录
 DISTINCT 关键字 跟随列名
*/
SELECT DISTINCT zname FROM zhangwu;

/*
  查询重新命名列
  as 关键字
*/
SELECT zname AS 'name' FROM zhangwu;

/*
  查询数据中，直接进行数学计算
  对指定列数字进行计算
*/
SELECT zname,zmoney+1000 AS'sum' FROM zhangwu;

-- 查询所有的吃饭支出
SELECT * FROM zhangwu WHERE zname='吃饭支出';

-- 查询金额大于1000
SELECT * FROM zhangwu WHERE zmoney>1000;

-- 查询金额在2000到5000之间 
SELECT * FROM zhangwu WHERE zmoney >= 1000 AND zmoney <= 5000;

-- 改造成between and 方式
SELECT * FROM zhangwu WHERE zmoney BETWEEN 1000 AND 5000;

-- 查询金额是1000、3500、5000
SELECT * FROM zhangwu WHERE zmoney = 1000 OR zmoney=3500 OR zmoney=5000;

-- 改造成in方式
SELECT * FROM zhangwu WHERE zmoney  IN (1000,3500,5000);

-- like 模糊查询 配合通配符
-- 查询所有的支出
SELECT * FROM zhangwu WHERE zname LIKE '%支出%';

-- 查询账务名字，五个字符的
SELECT * FROM zhangwu WHERE zname LIKE'_____';

-- 查询账务名，不为空的
SELECT * FROM zhangwu WHERE zname IS NOT NULL;

SELECT * FROM zhangwu WHERE NOT (zname IS NULL);


/*
   使用聚合函数查询计算
*/

-- count 求和,对表中的数据的个数求和  count(列名)
-- 查询统计账务表中,一共有多少条数据
SELECT COUNT(*)AS'count' FROM zhangwu;

-- sum求和，对一列中数据进行求和计算 sum(列名)
-- 对账务表查询，对所有的金额求和计算
SELECT SUM(zmoney) FROM zhangwu;

-- 求和，统计所有支出的总金额
SELECT SUM(zmoney) FROM zhangwu WHERE zname LIKE'%支出%';

-- 求和，统计所有收入的总金额
SELECT SUM(zmoney) FROM zhangwu WHERE zname LIKE'%收入%';

-- max 函数，对某列数据获取最大值
SELECT MAX(zmoney) FROM zhangwu;

-- avg 函数，计算一个列所有数据的平均数
SELECT AVG(zmoney)FROM zhangwu;


/*
  查询，对结果集进行排序
  升序、降序，对指定列排序
  order by 列名 [desc][asc]
  desc 降序
  asc  升序排列，可以不写
*/
-- 查询账务表，价格进行升序
SELECT * FROM zhangwu ORDER BY zmoney ASC;

-- 查询账务表，价格进行降序
SELECT * FROM zhangwu ORDER BY zmoney DESC;

-- 查询账务表，查询所有的支出，对金额降序排列
-- 先过滤条件where查询的结果再排序
SELECT * FROM zhangwu WHERE zname LIKE'%支出%' ORDER BY zmoney DESC;

-- 查询所有的收入，对金额进行降序排列
select * from zhangwu where zname like'%收入%' order by zmoney desc;

/*
    查询所有的数据
    吃饭支出 共计多少
    工资收入 共计多少
    服装支出 共计多少
    股票收入 共计多少
    打麻将支出 共计多少钱
    
    分组查询:  group by 被分组的列名
    必须跟随聚合函数
    select 查询的时候，被分组的列，要出现在select选择列的后面
*/
SELECT SUM(zmoney) as 'sum',zname FROM zhangwu GROUP BY zname;
  
-- 对zname内容进行分组查询求和，但是只要支出
SELECT SUM(zmoney)AS 'sum',zname FROM zhangwu WHERE zname LIKE'%支出%'
GROUP BY zname
ORDER BY sum DESC;

-- 对zname内容进行分组查询求和，但是只要支出，显示金额大于5000
-- 结果集是分组查询后，再次进行筛选，只能使用关键字 having
SELECT SUM(zmoney)AS 'sum',zname FROM zhangwu WHERE zname LIKE'%支出%'
GROUP BY zname 
HAVING sum>5000;

MySQL基本使用备忘录（for mac）

一、创建数据库操作

二、创建数据表

三、向数据表中添加数据

四、修改数据

五、查询数据

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读