进入数据库(win下)( mysql -u root -p)

mysql -hlocalhost -uroot -p12345 //-p后面跟的是密码

显示数据库

SHOW DATABASES;

打开数据库

USE db_name;

显示数据表

show tables;

创建库

语法

CREATE DATABASES db_name;

删除数据库

语法

DROP DATABASE db_name;

创建表

语法:

CREATE TABLE tb_name ( 
column_name_1 column_type_1 constraints,
 column_name_2 column_type_2 constraints,
  column_name_3 column_type_3 constraints, 
  ... 
  column_name_n column_type_n constraint
  s)

示例:

CREATE TABLE `hz_chat_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '账号',
  `head_img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '头像',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '账号状态,1:启用,0:禁用',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '密码',
  `tel` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '电话',
  `wechat` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '微信',
  `qq` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'QQ',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '邮箱',
  `sex` tinyint(2) NOT NULL DEFAULT '-1' COMMENT '性别,-1:保密,0:男,1:女',
  `delete_time` int(10) NOT NULL DEFAULT '0' COMMENT '删除时间戳',
  `create_time` int(10) NOT NULL DEFAULT '0',
  `update_time` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE,
  KEY `account_delete` (`account`,`delete_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户基本信息表';

查看表定义

语法

DESC tb_name;

示例

查看创建表的SQL语句

语法

SHOW CREATE TABLE db_name;

删除表

语法

DROP TABLE tb_name

增加表字段

语法

ALTER TABLE tb_name 
ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name];

实例

ALTER TABLE `hz_chat_user` 
ADD COLUMN `job` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '职业' AFTER `city`,
ADD COLUMN `age` int(4) NOT NULL DEFAULT 0 COMMENT '年龄' AFTER `job`

ALTER TABLE `hz_chat_shop` 
CHANGE COLUMN `end_time` `close_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '02:00' COMMENT '结束营业时间' AFTER `open_time`,
ADD COLUMN `chat_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '聊天室状态,0:关闭,1:开启' AFTER `status`

修改索引

ALTER TABLE `gameduncs`.`app_gamedunmonitor` 
ADD INDEX `create_time_str`(`create_time_str`) USING BTREE

ALTER TABLE  `hz_chat_user_relation` 
DROP INDEX `from_id`,
ADD INDEX `from_to_id`(`from_id`, `to_id`) USING BTREE

删除表字段

语法

ALTER TABLE tb_name DROP [COLUMN] column_name;

修改表(字段)

语法

ALTER TABLE tb_name 
MODIFY [COLUMN] column_name column_definition [FIRST | AFTER column_name];

示例

ALTER TABLE `hz_chat_info` 
MODIFY COLUMN `group_id` int(11) NOT NULL DEFAULT 0 COMMENT '聊天室id(一般情况下都是chat_shop表的id)' AFTER `chat_user_id`,
MODIFY COLUMN `chat_relation` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:商户聊天室' AFTER `group_id`,
ADD INDEX `chat_user_id`(`chat_user_id`) USING BTREE,
COMMENT = '聊天室内容'

修改表名

语法

ALTER TABLE tb_name RENAME [TO] new_tb_name;

示例

RENAME TABLE `hz_chat_info` 
TO `hz_chat_group_info`;

插入记录

语法

INSERT INTO tb_name 
(field1, field2, ..., fieldn) 
VAULES(value1, value2, ...,valuen);

插入多条

INSERT INTO tb_name (field1, field2, ..., fieldn) 
VAULES
(record1_value1, record1_value2, ...,record1_valuen),
(record2_value1, record2_value2, ...,record2_valuen),
(recordn_value1, recordn_value2, ...,recordn_valuen);

更新记录

语法

UPDATE tb_name 
SET field1=value1, field2=value2, ..., fieldn=valuen [WHERE condition];

更新多个表中的数据

UPDATE tb_name1, tb_name2, ..., tb_namen 
SET tb_name1.field=value, ..., tb_namen.field=value [WHERE condition];

删除记录

语法

DELETE FROM tb_name [WHERE condition];

删除多个表中的数据

DELETE tb_name1, tb_name2, ..., tb_namen 
FROM tb_name1, tb_name2, ..., tb_namen [WHERE condition];

查询记录

语法

SELECT [field1, field2, ..., fieldn] function_name 
FROM tb_name 
[WHERE condition] 
[ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ..., fieldn [DESC|ACS]]
[GROUP BY field1, field2, ..., fieldn 
[WITH ROLLUP] 
[HAVING condition]]
[LIMIT offset_start, row_count];

注释
function_name 表示要做的聚合操作，又称聚合函数。常用的有 sum()、 count(*)、 max() 和 min()。
GROUP BY 关键字表示要进行分类聚合的字段。
WITH ROLLUP 表明是否对分类聚合后的结果进行再汇总。
HAVING 关键字表示对分类后的结果再进行条件的过滤。
注意：HAVING 和 WHERE 的区别在于，HAVING 是对聚合后的结果进行条件的过滤，而 WHERE 是对聚合前的记录进行过滤。

示例

select sum(`port`) as port_com,sum(`https_origin_off`)
from  gf_hostpool_domainrules where `https_origin_off`=2  GROUP BY  `hostpool_id` HAVING `hostpool_id`=4;

表连接

语法

SELECT left_field, right_field 
FROM left_tb_name
[INNER|LEFT|RIGHT] JOIN right_tb_name 
ON condition;

子查询

查询的时候，condition 需要的条件是另外一个 SELECT 语句的结果，称为子查询。
子查询的关键字主要包括：IN、 NOT IN、 =、 !=、 EXISTS 和
NOT EXISTS 等。
记录联合
将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来。关键字是 UNION 和 UNION ALL。
UNION ALL 是表示全部记录，包括了多个查询结果的重复记录。如果希望去掉多个查询结果的重复记录，使用 UNION 关键字。

SELECT * FROM tb_name1 [WHERE condition]
UNION | UNION ALL
SELECT * FROM tb_name2 [WHERE condition]
...
UNION | UNION ALL
SELECT * FROM tb_namen [WHERE condition]

权限控制

语法

GRANT all ON db_name.tb_name TO 'user_name'@'host_name';

REVOKE all ON db_name.tb_name FROM 'user_name'@'host_name';

这些语句主要是 DBA 用于管理系统中的对象权限

执行顺序

from… where…group by…having….  select … order by…

HAVING

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

判断字符串中是否存在某个字符串

"FIND_IN_SET($port,port)" //判断port字段是否有$port 如 12是否在(12,23,24,25)当中
//laravel写法
->whereRaw("FIND_IN_SET($port,port)")

实例

用到关联、合并名称、判断流程等

SELECT
    o.order_sn AS 订单号,
    ( SELECT group_concat( g.goods_name ) FROM zsp_order_goods AS g WHERE o.id = g.main_order_id ) AS 商品名称
    ,(
        SELECT
            group_concat( g.goods_cat_name ) 
        FROM
            zsp_order_goods AS g 
        WHERE
            o.id = g.main_order_id 
            ) AS 商品分类
            ,(
        SELECT
            group_concat( g.brand_name ) 
        FROM
            zsp_order_goods AS g 
        WHERE
            o.id = g.main_order_id 
        ) AS 品牌, 
    (
    CASE

            WHEN o.order_status BETWEEN 0 
            AND 14 THEN
                '待付款' 
                WHEN o.order_status BETWEEN 15 
                AND 24 THEN
                    '待发货' 
                    WHEN o.order_status BETWEEN 25 
                    AND 79 THEN
                        '待收货' 
                        WHEN o.order_status BETWEEN 80 
                        AND 82 THEN
                            '已完成' 
                            WHEN o.order_status = 84 THEN
                            '已关闭' 
                            WHEN o.order_status = 90 THEN
                            '已删除' 
                        END 
                        ) AS 订单状态,
                        o.pay_price AS 订单金额,
                        FROM_UNIXTIME(o.create_time, '%Y-%m-%d') as 下单时间,
                        o.user_id AS 会员ID,
                        u.nick_name AS 会员名称,
                        l.level_name AS 会员等级,
                        u.parent_id as 上级ID,
                        p.nick_name as 上级名称

                    FROM
                        zsp_order AS o
                        left JOIN zsp_users AS u ON u.uid = o.user_id
                        left JOIN zsp_users_level AS l ON l.id = u.user_level_id 
                        left JOIN zsp_users AS p ON u.parent_id = p.uid
                    WHERE
                        o.shop_uid = 3 
                    ORDER BY
                        o.id

shell 脚本中执行mysql命令

#/bin/sh

HOSTNAME="127.0.0.1"
PORT="3306"
USERNAME="mylinux"
PASSWORD="mylinux"
DBNAME="mylinux"
TABLENAME="test"

#创建数据库
create_db_sql="create database IF NOT EXISTS ${DBNAME}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e"${create_db_sql}"
#创建表
create_table_sql="create table IF NOT EXISTS ${TABLENAME} (name varchar(20),id int(11) default 0 )"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create_table_sql}"
#插入数据
insert_sql="insert into ${TABLENAME} values('billchen',2)"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${insert_sql}"