mysql基础语句大全

最新推荐文章于 2024-08-14 22:03:11 发布

花乐晴

最新推荐文章于 2024-08-14 22:03:11 发布

阅读量2.8k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_44195615/article/details/107468078

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、数据库操作

1、删除数据库

DROP DATABASE IF EXISTS db_test; -- [IF EXISTS]可不写，不写数据库不存在会报错，写了数据库不存在不进行任何操作

2、创建数据库

CREATE DATABASE IF NOT EXISTS db_test -- [IF NOT EXISTS]可不写，不写数据库存在会报错，写了数据库存在不进行任何操作
DEFAULT CHARACTER SET = gb2312 -- 可选项：设置数据库默认字符集
DEFAULT COLLATE gb2312_chinese_ci ;-- 可选项：设置数据库字符集的校对规则

3、修改数据库

ALTER DATABASE db_test
DEFAULT CHARACTER SET = gb2312 -- 可选项：设置数据库默认字符集
DEFAULT COLLATE gb2312_chinese_ci; -- 可选项：设置数据库字符集的校对规则

4、查看数据库

SHOW DATABASES
LIKE '%db_test%'; -- 可选项：用于匹配指定数据库名称，也可使用[WHERE]从句指定数据库名称查询范围

5、选择数据库

USE db_test;

-- 所有[DATABASE]都可替换为[SCHEMA]，同样的效果

二、表操作

-- 测试用
DROP TABLE IF EXISTS db_test.tb_user;

CREATE TABLE tb_user (
  -- [TEMPORARY TABLE]表示创建的是临时表，创建正式表时可不用
  user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  user_name CHAR(50) NOT NULL
) ;
-- 测试用区域结束

-- 删除表(P96)
DROP TABLE IF EXISTS db_test.tb_customers; -- 删除表，可同时操作多张表，还有未知属性[RESTRICT|CASCADE]

-- 创建表(P92)
CREATE TABLE tb_customers (
  -- [TEMPORARY TABLE]表示创建的是临时表，创建正式表时可不用
  cust_no INT NOT NULL AUTO_INCREMENT  /*PRIMARY KEY --创建主键1*/,
  cust_name CHAR(50) NOT NULL,
  sex CHAR(1) NOT NULL DEFAULT 0,
  cust_address CHAR(50) NULL,
  user_id INT COMMENT '用户编号',
  p_user_id INT COMMENT '用户父编号',
  cust_test CHAR(50) NULL,
  PRIMARY KEY (cust_no),
  -- 创建主键2，
  INDEX index_customers (cust_name),
  -- 创建普通索引,第二种方法，也可在前面加上[UNIQUE]标识创建唯一索引，[INDEX]可替换成[KEY]
  CONSTRAINT `customers_fkey` FOREIGN KEY (user_id) -- 创建外键,可多个列
  REFERENCES tb_user(user_id) ON DELETE RESTRICT -- 删除父表中的记录时，子表中记录发生的情况。默认值[RESTRICT|ACTION]:拒绝删除，[SET NULL]:将子表中的行中的值重置为值NULL
   ON UPDATE RESTRICT-- 同理
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 更新表(P94)
ALTER TABLE db_test.tb_customers
ADD COLUMN cust_city CHAR(10) NOT NULL DEFAULT 'Wuhan' AFTER cust_sex,-- 添加新列，[AFTER]指定添加到指定列之后，也可用[FIRST]指定添加到第一列，不写默认添加到末尾
CHANGE COLUMN sex cust_sex CHAR(1) NULL DEFAULT 'M', -- 修改列将sex改为cust_sex
ALTER COLUMN cust_address SET DEFAULT 'Beijing', -- 修改列默认值
MODIFY COLUMN cust_name CHAR(20) FIRST, -- 修改列的类型及位置
DROP COLUMN cust_test, -- 删除指定列
RENAME TO tb_customershty, -- 对表进行重命名
ADD INDEX index_customers2(cust_no,cust_name), -- 创建普通索引,第三种方法
ADD CONSTRAINT `customers_fkey2` FOREIGN KEY(user_id) REFERENCES tb_user(user_id), -- 创建外键,可多个列
DROP INDEX index_customers, -- 删除索引，可改为[FOREIGN KEY]删除外键
DROP FOREIGN KEY customers_fkey, -- 删除外键
DROP PRIMARY KEY;-- 删除主键

-- 重命名表(P96)
RENAME TABLE db_test.tb_customershty TO db_test.tb_customers; -- 对表进行重命名，可同时操作多张表[,tb2 TO tb2_new]

-- 查看表(96)
SHOW FULL TABLES FROM db_test -- 查看表，[FULL]：显示更详细信息？[FROM db_test]：指定数据库，不写查出所有有权限的数据库表，也可写成[IN db_test]
LIKE '%tb_customers%'; -- 可选项：用于匹配指定表名称，也可使用[WHERE]从句指定表名称查询范围

-- 显示表结构(97)
-- 方法1
SHOW FULL COLUMNS FROM db_test.tb_customers; -- 显示表结构，可使用[LIKE|WHERE],[FROM db_test.tb_customers]也可写成[FROM tb_customers FROM db_test]的形式，同样可替换[IN]

-- 方法2
DESCRIBE db_test.tb_customers; -- 显示表结构，[DESCRIBE]可简写成[DESC]，表名后可跟具体的列，貌似只能跟一列

-- 创建索引,方法1(P98)，这种方式可创建普通索引及唯一性索引，但不能创建主键
CREATE UNIQUE INDEX index_customers ON db_test.tb_customers(cust_name(3) ASC); -- 根据cust_name字段前3个字母升序创建唯一性索引，[UNIQUE]唯一性索引标识，可没有，[cust_name(3)]：不包含括号代表整个字段，包含括号代表指定字符个数，可同时维护多个列，用逗号隔开[ASC]：升序，可选[DESC]:降序

-- 查看索引(P101)
SHOW INDEX FROM db_test.tb_customers; -- 查看索引，[INDEX]可替换为[INDEXES|KEYS]，[FROM]可替换成[IN]可同时查询多个表,可使用[WHERE]子句

-- 删除索引，方法1(P101)
DROP INDEX index_customers ON db_test.tb_customers;

-- 插入数据，方法1(P102)
INSERT INTO db_test.tb_customers (cust_name,cust_sex,cust_city) -- 可不指定明确的列列表，但这样写入数据时的数据列顺序必须与表结构完全一致
VALUES ( '客户1', 'W', '北京'), ('客户2', 'M', '上海') ; -- [VALUES]单行数据可替换为：[VALUE]

-- 插入数据，方法2(P103)
INSERT INTO db_test.tb_customers
SET cust_name='客户1',cust_sex='W',cust_city='北京'; -- 这种方法貌似只能用于插入一行数据

-- 插入数据，方法3(P104)
INSERT INTO db_test.tb_customers (cust_name,cust_sex,cust_city)
SELECT user_name,user_id,user_name FROM db_test.tb_user ;

-- 删除数据(P104)
DELETE FROM db_test.tb_customers
WHERE cust_name='客户1' -- 按客户名查找
ORDER BY cust_no,cust_name -- 按客户编号及客户名称排序
LIMIT 1; -- 第一条数据

-- 修改数据(P105)
UPDATE db_test.tb_customers
SET cust_name='客户3',cust_city='深圳' -- 修改指定字段
WHERE cust_name='客户1' -- 按客户名查找
ORDER BY cust_no,cust_name -- 按客户编号及客户名称排序
LIMIT 5; -- 限制5条数据

-- 查询语句(P106)
SELECT cust_name,(CASE WHEN cust_sex='M' THEN '男' ELSE '女' END) cust_sex -- 可以是具体列、聚合函数、或者*
FROM db_test.tb_customers t
CROSS JOIN tb_user t1 -- 交叉连接，即两个表不用连接条件，直接连接，数据量等于两表数据的乘积
INNER JOIN tb_user t2 -- 内连接，使用连接条件得出两边都符合条件的数据，可简写成[JOIN]
ON t.user_id = t2.user_id
LEFT JOIN tb_user t3 -- 左外连接，匹配左表中每一行及右表中符合条件的行，右表如未找到符合条件的行，对应字段则为空，右外连接则相反，也可写作[LEFT OUTER JOIN]
ON t.user_id = t3.user_id
WHERE cust_city='深圳'
AND cust_no IN(1,2) -- 标定值列表，可以是一个子查询
AND cust_no BETWEEN 1 AND 100 -- 标定范围，包括所指定的值
AND cust_city IS NOT NULL -- 不为空判断
AND cust_city <> ALL (select user_name from tb_user) -- 使用运算符结合子查询判断数据，[ALL]:结果集中每个值都会进行比较，全满足则返回true,可替换:[SOME|ANY]:只要满足一个就返回true
AND EXISTS(SELECT user_name FROM tb_user) -- 如果对应子查询有数据则返回true
GROUP BY cust_name,(CASE WHEN cust_sex='M' THEN '男' ELSE '女' END)
HAVING COUNT(*)>1
ORDER BY cust_name DESC
LIMIT 5,10; -- 检索记录行 6-15 

-- 创建视图(P120)
CREATE OR REPLACE VIEW db_test.tb_customers_view AS 
SELECT * FROM db_test.tb_customers -- select语句
WITH CHECK OPTION; -- 用于指定可更新视图上所进行的修改都需要符合select语句所指定的限制条件[CASCADED]为默认值，对所有视图进行检查，[LOCAL]只对定义的视图进行检查

-- 修改视图(P121)
ALTER VIEW db_test.tb_customers_view AS
SELECT * FROM db_test.tb_customers
WITH CHECK OPTION;

-- 查看视图(P121)
SHOW CREATE VIEW db_test.tb_customers_view; -- 查看已有视图的定义(结构)

-- 查询视图数据(P122)
SELECT * FROM db_test.tb_customers_view WHERE cust_no=1;

-- 删除视图(P121)
DROP VIEW IF EXISTS db_test.tb_customers_view;

-- 更新视图数据插入、修改、删除(P122)
-- 语法与正常的表操作一致
-- 当视图依赖多个基本表时，无法向该视图插入/删除数据
-- 当视图依赖多个基本表时，则一次视图数据修改操作只能改变一个基本表中的数据
-- 新增、修改、删除的数据必须满足视图定义时的sql的[WHERE]条件的定义，且在定义时必须加上[WITH CHECK OPTION]

-- 创建存储过程(P126)
USE db_test; -- 使用指定数据库
DROP PROCEDURE IF EXISTS sp_uodate_sex;-- 删除存储过程
DELIMITER $$ -- 改变结束标志
CREATE  PROCEDURE sp_uodate_sex(IN csex CHAR(1),INOUT cid INT,OUT msg CHAR(100)) 
BEGIN
    DECLARE cname CHAR(50) DEFAULT '默认客户名'; -- 声明一个局部变量(只能在存储过程体的开头处声明)，变量名前加@即为用户变量，局部变量只在当前[BEGIN END]区间内生效，用户变量生效于整个会话中,[DEFAULT]为可选项，定义默认展，可没有
    DECLARE is_found BOOLEAN DEFAULT TRUE;
    DECLARE cur_cid CURSOR FOR SELECT cust_no FROM tb_customers WHERE cust_no=cid; -- 定义游标，这时未查询数据
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_found = FALSE; -- 定义异常
    SET msg='SUCCESS'; -- 为变量赋值
    
    UPDATE tb_customers SET cust_sex=csex WHERE cust_no=cid; -- 用指定条件更新指定表
    SELECT cust_name INTO cname FROM tb_customers WHERE cust_no=cid; -- 查询数据并存储到指定局部变量中，语句返回结果只能有一条数据
    
    OPEN cur_cid; -- 打开游标，同时查询数据，只有打开游标才能使用，可多次打开，每次打开都会重新查询数据
    FETCH cur_cid INTO cid; -- 从游标中取出数据
    WHILE is_found DO
        FETCH cur_cid INTO cid; -- 从游标中取出数据
    END WHILE;
    CLOSE cur_cid; -- 关闭游标，如果没有明确关闭游标，将会在到达[END]语句时自动关闭它
END $$
DELIMITER ;

-- 调用存储过程(P131)
CALL sp_uodate_sex('男',@cid,@msg);

-- 删除存储过程(P131)
DROP PROCEDURE IF EXISTS sp_uodate_sex;

-- 创建存储函数(P132)
DROP FUNCTION IF EXISTS fn_search;-- 删除存储函数
DELIMITER $$
CREATE  FUNCTION fn_search(cid INT)
    RETURNS CHAR(100)
    DETERMINISTIC -- 标识为确定性函数，由于此函数时根据传入的cid去读取返回性别数据的，所以算是一个确定性函数，标识此项能对函数进行相关优化，标识后可在基于函数的索引中及物化视图中调用
BEGIN
    DECLARE sex CHAR(2);
    SELECT  cust_sex INTO sex FROM tb_customers
        WHERE cust_no=cid;
    IF sex IS NULL THEN
        RETURN(SELECT '没有该客户');
    ELSE
        IF sex='F' THEN
            RETURN(SELECT '女');
        ELSE
            RETURN(SELECT '男');
        END IF;
    END IF;
END $$
DELIMITER ;

-- 调用存储函数(P133)
SELECT fn_search(1);

-- 删除存储函数(P133)
DROP FUNCTION IF EXISTS fn_search;

-- 删除触发器(P141)
DROP TRIGGER IF EXISTS db_test.customers_insert_trigger; -- 删除一个表时同时会删除对应触发器，触发器不能修改，只能删除后再创建

-- 创建触发器(P140)
CREATE TRIGGER db_test.customers_update_trigger before UPDATE -- [AFTER|BEFORE]:触发时间，插入前触发还是插入后触发，插入前可用于数据验证修改，插入后可用于验证删除或操作其他表，[INSERT|UPDATE|DELETE]:触发事件，每个表触发事件及方式的组合不能重复，即一个表共可创建6个触发器(2*3)
ON db_test.tb_customers FOR EACH ROW -- [FOR EACH ROW]:如果一次性批量插入多条数据，则触发多次(每条触发一次)
SET NEW.cust_name=OLD.cust_name ;-- 可使用[BEGIN END]复合语句
-- set @str='one customer added!'; 
-- select @str;
-- NEW虚拟表
-- NEW虚拟表可用在[INSERT|UPDATE]中，在[INSERT]中[BEFORE]时，NEW可修改且同步更新对应数据，且这时的自动索引列的值为0,[BEFORE]时自动索引列为对应数据,
-- OLD虚拟表可用于[UPDATE|DELETE]中，表内数据为只读，不允许修改
-- 当触发器涉及对触发表本身的更新操作时，只能使用[BEFORE UPDATE]而不能使用[AFTER UPDATE]


-- 查看数据库的使用者账户(P144)
SELECT USER FROM mysql.`user`;

-- 查询指定明文的加密后密码(P145)
SELECT PASSWORD(456);

-- 创建用户(P145)
CREATE USER user1@localhost IDENTIFIED BY '123', -- 使用明问密码创建用户(会自动加密)，操作用户需拥有[mysql]数据库的[INSERT]权限或全局[CREATE USER]权限，同名不同主机名视为不同用户，未指定权限的用户只能使用一些无需权限的命令
	    user2 IDENTIFIED BY PASSWORD '*531E182E2F72080AB0740FE2F2D689DBE0146E04', -- 使用加密后密码创建用户，@后为指定主机名，不写默认使用'%'
	    user3; -- 创建无密码用户

-- 用户重命名(P146)
RENAME USER user2 TO user2_new; -- 可同时操作多个,操作用户需拥有[mysql]数据库的[UPDATE]权限或全局[CREATE USER]权限

-- 修改用户口令(P147)
SET PASSWORD FOR user2_new = PASSWORD('123'); -- 不写[FOR user2]则修改当前操作用户，用户名都必须写完整，包括@后的，[PASSWORD('123')]可替换为加密后的密码

-- 赋予用户权限(P148)
GRANT SELECT(cust_no,cust_name) -- [SELECT]:权限名称，可选项为[SELECT|UPDATE|DELETE]等数据库操作，可用逗号分隔指定多个，后面的具体列名可省略，省略即代表所有列，不省略则代表只拥有指定列权限
ON db_test.tb_customers -- [db_test.tb_customers]:指定设置权限的表，可选项为:[*|*.*|db_test.*|db_test.tb_customers],分别表示当前数据库所有表、所有数据库所有表、指定数据库下所有表、指定数据库下指定表
TO user1@localhost IDENTIFIED BY '123' -- 赋权限的目标用户，[IDENTIFIED]之后(包括)可省略，未省略则用户已存在，口令会被更改成指定口令，不存在则自动创建，可用逗号分隔同时操作多个用户
WITH GRANT OPTION; -- 可选项允许用户将自身拥有的权限授予给其他用户
/*-- 权限名称：
    -- 表权限
        SELECT:查询指定表数据
        INSERT:新增指定表数据
        DELETE:删除指定表数据
        UPDATE:更新指定表数据
        REFERENCES:允许创建指向指定表外键的权限
        CREATE:允许用户用特定名称创建表
        ALTER:允许用户用[ALTER TABLE]语句修改指定表
        INDEX:允许用户为指定表创建/删除索引
        DROP:允许用户删除指定表
        ALL|ALL PRIVILEGES:以上所有权限
    -- 列权限
        SELECT:查询指定表指定列数据
        INSERT:新增指定表指定列数据
        UPDATE:更新指定表指定列数据
    -- 数据库权限
        SELECT:查询指定数据库中所有表与视图数据
        INSERT:新增指定数据库中所有表与视图数据
        DELETE:删除指定数据库中所有表与视图数据
        UPDATE:更新指定数据库中所有表与视图数据
        REFERENCES:允许创建指向指定数据库中的表外键的权限
        CREATE:允许用户在指定数据库中创建表
        ALTER:允许用户在指定数据库中用[ALTER TABLE]语句修改表
        INDEX:允许用户为指定数据库中所有表创建/删除索引
        DROP:允许用户在指定数据库中删除表
        CREATE TEMPORARY TABLES:允许用户在指定数据库创建临时表
        CREATE VIEW:允许用户在指定数据库创建新视图
        SHOW VIEW:允许用户可查看指定数据库中已有视图的视图定义
        CREATE ROUTINE:允许用户在指定数据库中创建存储过程或存储函数
        ALTER ROUND:允许用户在指定数据库中修改或删除存储过程或存储函数
        EXECUTE ROUND:允许用户可调用指定数据库中的存储过程或存储函数
        LOCK TABLES:允许用户锁定指定数据库中的数据表
        ALL|ALL PRIVILEGES:以上所有权限
    -- 用户权限
        CREATE USER:允许用户创建和删除新用户
        SHOW DATABASES:允许用户使用[SHOW DATABASES]语句查看所有已有数据库*/
        
-- 查看用户拥有的权限(P148)
SHOW GRANTS FOR user1@localhost; -- [*.*]表示用户未拥有任何权限

-- 撤销权限(P151),需拥有[mysql]数据库的[UPDATE]权限或全局[CREATE USER]权限
REVOKE SELECT(cust_no,cust_name)
ON db_test.tb_customers
FROM user1@localhost;

-- 撤销所有权限(P151)
REVOKE ALL PRIVILEGES,GRANT OPTION FROM user1@localhost; -- 可用逗号分隔指定多个用户

-- 删除用户(P146)
DROP USER user3,user2_new,user1@localhost; -- 可同时删除多个,操作用户需拥有[mysql]数据库的[DELETE]权限或全局[CREATE USER]权限

-- 事务操作(P152),在mysql中只有[InnoDB]引擎的数据库或表才支持事务
    start TRANSACTION; -- 开启事务
    ROLLBACK; -- 事务回滚
    COMMIT; -- 事务提交

更多知识分享请看下图
在这里插入图片描述