brew install mysql
# to start
brew services start mysql
# to secure it run:
mysql_secure_installation
# to connect
mysql -u root -p
# execute an SQL script file
mysql> source file_name
# creating and selecting a Database
mysql> CREATE DATABASE xxx;
# set user password
mysql> ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password';
mysql> FLUSH PRIVILEGES;

mysql -u root -p # 指定账户密码

# 设置免密登录
# vim ~/.my.cnf
[client]
user=root
password=888888

mysql # 直接登录

一般针对每个应用创建一个 database, 为了保存实体数据，在 database 中创建多个表 table

mysql -h hostname -u user_name -p
mysql> show databases;
mysql> exit;
mysql> desc mysql.user;
mysql> select user, select_priv from mysql.user;    // 查询用户权限
mysql> create database movietalk charset=utf8;
mysql> use movietalk;
mysql> show tables;
mysql> create table film (
    -> file_name varchar(255)   // 创建数据栏 varchar: 字符串类型
    -> file_data date
    -> )
// SHOW COLUMNS Statement
mysql> show columns from film;
mysql> describe film;
// 修改表
mysql> alter table film add id INT(10) first;
mysql> alter table film add film_content TEXT after file_name;  // 在 film_name 下方创建 TEXT 类型 film_content
mysql> alter table film add PRIMARY KEY (id);   // id 作为表的主键
mysql> alter table film change id film_id INT(10); // 更改 id 栏的名称/类型
mysql> alter table film rename to movie;
mysql> create table people(
    -> people_id INT(10) unsigned not null auto_increment, // id 自增
    -> people_name VARCHAR(100),
    -> people_birth DATE,
    -> people_location VARCHAR(100),
    -> primary key(people_id)
    -> ) default charset=utf8
    -> ;
mysql> use movietalk;
mysql> insert into people values(null, '丹泽尔华盛顿', '1954-12-28', 'USA');
mysql> insert into people (people_name, people_location) values ('PTA', 'USA');
mysql> select * from people;    // 显示所有栏
mysql> select people_name, people_birth from people;
mysql> select * from people order by people_birth desc where people_location= 'USA';
mysql> drop database movietalk;

SELECT prod_name
FROM products;
// 检索多列
SELECT prod_id,prod_name,prod_price
FROM products;
// 检索所有列
SELECT *
FROM products;
// 检索不同的行，只返回唯一的 vend_id
SELECT distinct vend_id
FROM products;
// 限制结果，返回从行5开始的5行
SELECT prod_name
FROM products
LIMIT 5,5;
// 排序
SELECT prod_name
FROM products
ORDER by prod_name;
// 按多列排序,先按价格后按名称排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER by prod_price, prod_name
// 指定排序方向,desc-降序排序，最贵最前
SELECT prod_id, prod_price, prod_name
FROM products
ORDER by prod_price desc;
// ORDER by & LIMIT 组合，找出最昂贵的值
SELECT prod_price
FROM products
ORDER by prod_price desc
LIMIT 1;
// 过滤数据，ORDER by 应位于 WHERE 之后
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
// 不匹配检查，返回不是由供应商1003制造的所有产品
SELECT vend_id, prod_name
FROM products
WHERE vend-id <> 1003;
// 范围值检查
SELECT prod_name, prod_price
FROM products
WHERE prod_price between 5 and 10;
// 空值检查
SELECT prod_name
FROM products
WHERE prod_price is NULL;
// 组合 WHERE 子句，优先级 () > and > or
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 and prod_price <= 10;
SELECT prod_name. prod_price
FROM products
WHERE vend_id = 1002 or vend_id = 1003;
SELECT prod_name, prod_price
FROM products
WHERE(vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
// 指定条件范围
SELECT prod_name, prod_price
FROM products
WHERE vend_id in (1002, 1003)
ORDER by prod_name;
// 否定条件
SELECT prod_name. prod_price
FROM products
WHERE vend_id not in (1002,1003)
ORDER by prod_name;
// 通配符过滤
// 百分号 % 表示任何字符出现任意次数
// 找出所有以词 jet 起头产品
SELECT prod_id, prod_name
FROM products
WHERE prod_name like 'jet%';
// 下划线_通配符只匹配单个字符而不是多个
SELECT prod_id, prod_name
FROM products
WHERE prod_name like '_ tON anvil';
// 正则表达式，like 匹配整列，regexp 在列值内匹配
// 基本字符匹配,检索列 prod_name 包含文本 1000 的所有行
SELECT prod_name
FROM products
WHERE prod_name regexp '1000'
ORDER by prod_name;
// . 表示匹配任意一个字符，1000和2000都匹配返回
SELECT prod_name
FROM products
WHERE prod_name regexp '.000'
ORDER by prod_name;
// OR　匹配
SELECT prod_ame
FROM products
WHERE prod_name regexp '1000|2000'
ORDER by prod_name;
// 匹配特定字符
// [123] = [1|2|3],[]定义OR语句查询内容
SELECT prod_name
FROM products
WHERE prodname regexp '[123] Ton'
ORDER by prod_name;
// 匹配范围
SELECT prod_name
FROM products
WHERE prod_name regexp '[1-5] Ton'
ORDER by prod_name;
// 匹配特殊字符
SELECT vend_name
FROM vendors
WHERE vend_name regexp '\\\\.'
ORDER by vend_name;

Untitled Database

匹配字符类

Untitled Database

匹配多个实例

Untitled Database

\\\\(\\\\) 匹配()，[0-9] 匹配任意数字，sticks? 匹配 stick 和 sticks (s 后的 ？使 s 可选)

SELECT prod_name
FROM products
WHERE prod_name regexp '\\\\([0-9] sticks?\\\\)'
ORDER by prod_name;

匹配连在一起的4位数字

SELECT prod_name
FROM products
WHERE prod_name regexp '[[:digit:]]{4}'
ORDER by prod_name;

Untitled Database

查找以一个数（包括以小数点开始的数）开始的所有产品

SELECT prod_name
FROM products
WHERE prod_name regexp '^[0-9\\\\.]'
ORDER by prod_name

计算字段

计算字段不实际存在于数据表中，计算字段是运行时在 SELECT 语句内创建的

// 拼接 Concat,SELECT 语句返回包含上述 4 个元素的单个列（计算字段）
SELECT Concat(vend_name, ' (',vend_country, ')')
FROM vendors
ORDER by vend_name;
// RTrim 去掉值右边的所有空格
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
FROM vendors
ORDER by vend_name;

Concat(RTrim(vend_name), ‘(’, RTrim(vend_country), ‘)’)