常见mysql语句（中文注释版）

最新推荐文章于 2023-03-19 15:21:04 发布

weixin_34124939

最新推荐文章于 2023-03-19 15:21:04 发布

阅读量182

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/sprouting/blog/744135

版权

2019独角兽企业重金招聘Python工程师标准>>>

-- 查看所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE dy_13;

-- 创建数据库并指定字符集
CREATE DATABASE dy_12 CHARACTER SET utf8;

-- 查看数据库字符集
SHOW CREATE DATABASE dy_13;

-- 修改数据库字符集，需要在根目录上才能有效执行
ALTER DATABASE dy_13 DEFAULT CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE dy_13;

-- ---------------------------------------------------------------------------

-- 进入表
USE dy;

-- 查看表,需要先进入数据库
SHOW TABLES;

-- 创建表
CREATE TABLE student(
   id INT,
   NAME VARCHAR(20),
   gendenr VARCHAR(20),
   age INT
);

-- 查看表结构
DESC student;

-- 添加字段
ALTER TABLE dy ADD COLUMN qq INT;

-- 修改字段类型
ALTER TABLE student MODIFY COLUMN qq VARCHAR(10);

为已经添加好的数据表添加外键:
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名

-- 修改字段名称旧名字新名字
ALTER TABLE student CHANGE COLUMN gendenr sex VARCHAR(20);

-- 删除字段
ALTER TABLE student DROP COLUMN qq;

-- 修改表名
ALTER TABLE student RENAME TO students;

-- 删除表
DROP TABLE test;

-- 数据管理-------------------------------------------------------------------------------------------------------

-- 查看所有数据
SELECT * FROM dy;

-- 增加数据
INSERT INTO dy VALUES(1,'宝贝','男',20);

-- 在特定字段加入数据
INSERT INTO dy(id,NAME)VALUES(5,'徐渭');

-- 修改数据,一次对一个字段进行修改，修改多个的话逗号隔开即可
UPDATE dy SET qq='123142';

-- 修改局部数据,要修改的数据增加一个标识位，可以有多个标识位
UPDATE dy SET qq='0' WHERE id=2;

-- 删除数据(全局删除，等于删除了表中的全部数据，但是表没有删除)
DELETE FROM dy;

-- 局部删除,要操作的表删除的条件，即删除的标识位
DELETE FROM dy WHERE id=2;

-- 插入时间（时间类型）
CREATE TABLE test(
   插入日期 DATE,
   插入时间 DATETIME,
   自动插入时间 TIMESTAMP -- 该时间是自动生成的
);

-- 查询数据---------------------------------------------------------------------------------------------------

-- 查询所有列数据
SELECT * FROM dy;

-- 查询指定列
SELECT id,NAME FROM dy;

-- 给字段指定一个别名（列别名）
SELECT NAME AS '姓名',qq AS '腾讯qq' FROM dy;

-- 对数值类型的数据进行合并并得到值
SELECT age+qq FROM dy;
-- 对合并的数据进行指定别名
SELECT age AS '年龄',qq AS '腾讯',(age+qq) AS '得到' FROM dy;

-- 查询去重，将重复数据去掉,显示不重复的
SELECT DISTINCT qq FROM dy;

-- 条件查询----------------------------------------------------------------------------------------------
-- 查询，where后面是条件，全部的数据id为1的
SELECT * FROM dy WHERE id=1;

-- 与条件查询年龄为20 与性别为男
SELECT * FROM dy WHERE age=20 AND gender='男';

SELECT * FROM dy;

-- 或查询
SELECT * FROM dy WHERE age=20 OR age=40;

-- 比较条件 <小于 >大于 <=小于等于 >=大于等于 =等于 <>不等于，可配合条件查询

-- 比较查询大于某数值
SELECT * FROM dy WHERE age>20;

-- 并且年龄在 20 与 40直接的
SELECT * FROM dy WHERE age BETWEEN 20 AND 40;

-- 判空查询--------------------------------------------------------------------------------

-- 查询列中是null的数据
SELECT * FROM dy WHERE age IS NULL;

-- 查询列中是非null的数据
SELECT * FROM dy WHERE age IS NOT NULL;

-- 查询列中空字符串
SELECT * FROM dy WHERE age ='';

-- 查询列中非空字符串的数据
SELECT * FROM dy WHERE age <>'';

-- 模糊查询-------------------------------------------------

-- %表示任意字符 _一个下划线代表一个

-- 查询含有某个字的数据
SELECT * FROM dy WHERE NAME LIKE '%贝%';

-- 查询开头为某个字的数据
SELECT * FROM dy WHERE NAME LIKE '王%';

-- 查询有3个字，并且含某字的数据 __表示2个，只会得到含有2个字的数据
SELECT * FROM dy WHERE NAME LIKE '__贝';

-- 聚合查询--------------------------------------------------------------------------------------

-- 查找最高分从某字段查询最高数值
SELECT MAX(age) FROM dy;

-- 查询最低分
SELECT MIN(age) FROM dy;

-- 查询某个字段的总分数
SELECT SUM(age) FROM dy;

-- 查询某个字段的平均分，会有小数位
SELECT AVG(age) FROM dy;

-- 统计总数(对空不包含，如果是*号则包含)
SELECT COUNT(*) FROM dy;

-- 分页查询--------------------------------------------------------------------------
-- limit起始行，查询几行，注意从0开始

-- 从第一行查询2条数据，第一个参数是从第几行开始，查询几条
SELECT * FROM dy LIMIT 0,2;
SELECT * FROM dy LIMIT 3,5;

-- 查询排序-------------------------------------------------------------------------------
-- 默认情况下按照插入数据的顺序排序

-- 按照id的降序排序(从大到小)
SELECT * FROM dy ORDER BY id DESC;

-- 按照id升序排序（从小到大）
SELECT * FROM dy ORDER BY id ASC;
-- 按照age升序排序
SELECT * FROM dy ORDER BY age ASC; -- 空会排在前面
-- 按照name升序排序，排序方式是根据字典来排，字典是abcdefg这样的形式，中文的还不知道排序的原则
SELECT * FROM dy ORDER BY NAME ASC;

-- 分组查询------------------------------------------------------------------------------------------------
-- 查询各个性别的人数，先对数据进行分组，然后对数据进行统计查询,所谓的分组就是对数据去重
SELECT gender FROM dy GROUP BY gender; -- 进行了分组
SELECT * FROM dy GROUP BY gender;
SELECT gender,COUNT(*) FROM dy GROUP BY gender;-- 分组后并将数据进行了统计

-- where分组前查询，having分组后查询
-- 关键字的顺序 from -> where -> group by -> having -> order by -> limit
SELECT gender,COUNT(*) FROM dy WHERE age>3 GROUP BY gender;

-- 数据库的约束-----------------------------------------------------------------------------------------------

-- 默认值 default，在字段的后面添加

-- 非空 not null(有些数据库会赋值一个空字符串)

-- 唯一 unique

-- 主键唯一并且非空 primary key

-- 自增长 auto_increment 自增长的话数据会一直增加，并不会因为你删除了之前的数据重新开始增长，自增长数据不需要你添加数据

TRUNCATE TABLE dy; -- 删除整个表，我们需要注意一点，使用这个删除会使自增长也全部删除，等于重新开始从1分配，操作不能回滚
DELETE FROM dy; -- 这样删除整个表，自增长不会归零，这个删除可以进行条件删除，不能影响表的约束，同时该操作可以回滚

-- 外键约束--------------------------------------------------------------------------------------------------------
-- 使用外键，我们建立外键表，主键只要添加外键表名与id即可，外键即受外部约束的字段，至于数据管理，参考树木的
-- 外键约束的作用是约束外键，比如外键没有部门 id = 3 的，那么我们就不应该能添加一个外键为3的存在

ALTER TABLE dy ADD CONSTRAINT sa FOREIGN KEY(fk_two) REFERENCES two(id);-- 追加外键约束
-- 修改表表名添加声明外键名哪一个字段关联外键外键（主键）
SELECT * FROM dy;
SHOW TABLE dy;

-- 级联操作----------------------------------------------------------------------------------------------------------
-- 修改主表，影响副表的数据

-- 级联更新
ON UPDATE CASCADE;-- (在建立表的时候在后面添加该属性即可)

-- 级联删除
ON DELETE CASCADE;

-- 多表查询---------------------------------------------------------------------------------------------------------------
-- 内连接查询，不满足连接条件的不会被显示，即null不会被显示
SELECT dy.age,two.name -- 第二步：确定查询的字段，注意这里的字段是根据表名来得到，表名可以使用别名
   FROM dy,two -- 第一步：确定要查询的表，这里表可以给一个别名，即表名 AS 别名，甚至可以省略 AS关键字，在其他位置直接使用别名即可
       WHERE dy.fk_two=two.id; -- 第三步：确定连接条件，这里的关系即主键与外键

SELECT dy.gender,two.name -- 此种查询会出现笛卡尔积，没有连接条件导致其直接将两张表中无论是否重复的字段显示出来
   FROM dy,two;

-- 外连接查询，如果有null元素也会被显示出来（左外连接）
SELECT dy.gender,two.name -- 要查询的表的字段
   FROM dy            -- 在left outer join左边的表被称为左表，左表无论是否null全部会被显示
   LEFT OUTER JOIN two -- 右表会匹配左表数据，如果满足连接条件就显示数据，没有满足条件的则会显示null，如果没有和左表有关系的不会被显示
   ON dy.fk_two=two.id; -- 连接条件
   -- 如果有业务条件，那么最后面填写业务条件，一般使用and进行连接

-- 右外连接其实就是将左表与右表交换即可。一般情况下使用内连接

-- 权限管理-----------------------------------------------------------------------------------------------------

SELECT * FROM USER;-- 进入了mysql数据库，查询到user表，其中host字段指登录方式

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE USER='root';-- 修改数据库的密码，注意一点，一定要对数据库的密码进行加密后再修改

GRANT SELECT ON dy.dy TO 'guest'@'localhost' IDENTIFIED BY '1234'; -- 创建新用户并且分配权限
-- grant 给予权限
-- select 查询权限 update 修改权限 delete 删除权限 create 可以建表权限 all 代表全部权限，基本上需要什么权限加上这个关键字，逗号分隔
-- on
-- 需要授权的数据库.表
-- to
-- 用户名
-- @
-- 登录方式 localhost 表示只能本地登录，也可以是ip
-- identified by '密码' 登录密码

REVOKE SELECT ON dy.dy FROM 'guest'@'localhost'; -- 回收权限

UPDATE USER SET USER='guest' WHERE USER='admin';-- 修改用户名

-- 删除用户
DROP USER 'guest'@'host';

-- 存储过程----------------------------------------------------------------------------------------
-- 存储过程执行效率高，移植效果差

DELIMITER $ -- 声明开始位置，一般使用$
CREATE PROCEDURE aa() -- aa是这个存储过程的名字，相当于一个函数名
BEGIN
SELECT * FROM dy; -- 这里是要执行的sql语句，可以有很多，甚至可以使用if等
END $ -- 结束位置

-- 调用存储过程
CALL a1(); -- 执行该存储过程

-- ----------------------------------------------------------------------------------------------------------------------

-- 带参数的存储过程01 in是传入的参数
DELIMITER $ -- 声明开始位置，一般使用$
CREATE PROCEDURE a1(IN a INT) -- aa是这个存储过程的名字，相当于一个函数名,a是参数的名字，传入的参数其实是一个字段，还要放入参数的类型
BEGIN
SELECT * FROM dy WHERE id=a; -- 这里是要执行的sql语句，可以有很多，甚至可以使用if等(这里是得到id=xx的值)
END $ -- 结束位置

CALL a1(1); -- 执行该存储过程并且传入参数

-- -----------------------------------------------------------------------------------------------------------------------

-- 带参数的存储过程02 out是传出去的参数
DELIMITER $ -- 声明开始位置，一般使用$
CREATE PROCEDURE a2(OUT a INT) -- aa是这个存储过程的名字，相当于一个函数名,a是参数的名字，传入的参数其实是一个字段，还要放入参数的类型
BEGIN
SET a=100; -- 对传入的参数进行赋值
END $ -- 结束位置

SET @a=0; -- 定义一个变量
CALL a2(@a); -- 将变量传入其中
SELECT @a; -- 查看该变量
-- ----------------------------------------------------------------------------------------------

-- 第三种参数 inout方式不常用，即传入数据又输出数据
DELIMITER $       -- 声明开始位置，一般使用$
CREATE PROCEDURE a3(INOUT a INT) -- aa是这个存储过程的名字，相当于一个函数名,a是参数的名字，传入的参数其实是一个字段，还要放入参数的类型
BEGIN
   SELECT a;-- 得到传入的参数
   SET a=100; -- 对传入的参数进行赋值
END $ -- 结束位置

SET @a=10; -- 定义一个变量
CALL a3(@a); -- 将变量传入其中
SELECT @a; -- 查看该变量

-- ---------------------------------------------------------------------------------------------------------------------------
-- 1.4 带有判断的存储过程
DELIMITER $
CREATE PROCEDURE a4(IN num INT,OUT str VARCHAR(20))
BEGIN
   IF num=1 THEN
       SET str = '星期一';
   ELSEIF num=2 THEN
       SET str = '星期二';
   ELSEIF num=3 THEN
       SET str = '星期三';
   ELSE
       SET str = '错误';
   END IF;
END $

CALL a4(3,@str);
SELECT @str;

-- ------------------------------------------------------------------------------------------------------------------
-- 1.5 带有循环的存储过程
DELIMITER $
CREATE PROCEDURE a5(OUT result INT)
BEGIN
   -- int sum=0; for(int i=1;i<=100;i++){ sun+=i }
   DECLARE asum INT DEFAULT 0;
   DECLARE i INT DEFAULT 1;
   WHILE i<=100 DO
       SET asum=asum+i;
       SET i=i+1;
   END WHILE;
   SET result=asum;
END $

CALL a5(@result);
SELECT @result;

-- -----------------------------------------------------------------------------------------------------------
-- 1.6 带出数据库的数据(into)
DELIMITER $
CREATE PROCEDURE a7(IN eid INT,OUT vname VARCHAR(20))
BEGIN
SELECT NAME INTO vname FROM employee WHERE id=eid;
END $

CALL a7(4,@vname);

SELECT @vname;
-- ------------------------------------------------------------------------------------------------------------------------------
-- mysql的三种变量类型
-- 全局变量（内置对象），内置对象是所有用户都能共享的
-- character_set_client:msql数据库接收数据的编码类型
-- character_set_results:数据库输出数据的编码类型
-- 修改全局变量 set @@变量名=值
-- 查看全局变量 select @@变量名

-- 会话变量，只能在当前登录用户使用
-- 定义会话变量 set @变量名=值
-- 查看会话变量 select @变量名

-- 在存储过程里面的变量就是局部变量

转载于:https://my.oschina.net/sprouting/blog/744135