首页 > 数据库 >MYSQL语句

MYSQL语句

时间：2023-10-22 21:46:33浏览次数：53  
标签：语句 username age user MYSQL where id select

MySQL常用基础语句

cmd登录

mysql -h localhost -u root -p

SHOW语句

返回可用数据库列表
show databases;

返回当前选择数据库内可用表的列表
show tables；

显示表中的所有列（xxx：表名）
show columns  from xxx; or describe xxx;

显示服务器状态信息
show status;

显示创建特定数据库（xxx：数据库名）
show create database xxx;

显示创建特定表（xxx：表名）
show create table xxx;

显示授予用户（所有用户或特定用户）的安全权限
show grants;

显示服务器错误信息
show errors;

显示服务器警告信息
show warnings;

关于选择数据库进行表的操作（xxx：数据库名）
use xxx;

SELECT语句

检索表中的列 SELECT column... FROM table

select username from user;
select username,age from user;
select * from user;

DISTINCT 去除列中的重复值（必须放在列名的前面）一般用来查询不重复的字段和条数(count(distinct username)) 如果查询不重复的记录 用group by

select distinct addr from user;

需要过滤不止一条记录
select distinct age,addr from user;
过滤表中age和addr都重复的记录

LIMIT限制查询

select username from user limit 2;

表示从第三列开始返回一行
select username from user limit 2,1;
select username from user limit 1 offset 2;

使用完全限定的表名和列名（user为表名 test为数据库名）

select user.username from test.user

ORDER BY排序检索数据 （用非检索的列排序数据是合法的）

select username from user order by username;

多个排序时按顺序进行，如果username都是唯一，则按照age排序
select username,age from user order by username,age;

按指定排序方法 升序（ASC 默认）降序DESC（只用用到直接位于其前面的列名）
select username,age from user order by username,age;

使用order by和limit组合（order by必须位于from之后 limit必须位于order by之后）

eg：找出年龄最大的前三个人
select username,age from user order by age desc limit 3;

WHERE过滤数据

select username,age from user where age=20;
select username,age from user where addr='wuhan';

空值检查 IS NULL

select username,age,addr from user where addr is null;

操作符 AND和OR（and优先级高于or，所以组合使用时优先执行and）

select username,age,addr from user where age=20 and addr='beijing';
select username,age,addr from user where age=10 or addr='wuhan';
select username,age,addr from user where username='zhangsan' or addr='beijing' and age>10;

IN操作符（用来指定条件范围）

select username,age from user where username in ('zhangsan','lisi');

in和or的功能相同，使用in有什么好处

  • 在使用长的合法选项清单时，in操作符的语法更清楚且更直观
  • 计算的次序更容易管理（使用的操作符更少）
  • 一般情况下比or清单执行更快
  • 可以包含其他select语句，使得更动态的建立where语句

NOT（否定它之后所跟的任何条件，mysql支持not对in，between和exists子句取反）

select username,age from user where username not in ('zhangsan','lisi');

LIKE

百分号（%）通配符（可以区分大小写，跟mysql的配置有关，默认是不区分） %不能匹配NULL
select username, age from user where username like 'z%';
select username, age from user where username like '%a%';
select username, age from user where addr like '%';

下划线通配符只能匹配一个字符，功能与%通配符相同

select username,age from user where username like '_hansan'

通配符使用技巧

  • 不要太依赖通配符。如果其他操作符能达到相同的目的，应该使用其他操作符
  • 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的
  • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

REGEXP正则表达式进行搜索

select username,age from user where username regexp 'z';

BINARY 区分大小写
select username,age from user where username regexp binary 'z';

'.'表示匹配任意一个字符
select username,age from user where addr regexp '.';

or匹配
select username,age from user where username regexp 'z|l';

[123]=[1|2|3]
select username,age from user where username regexp '[123] z';

集合中使用^匹配除这些字符以外的，否则指字符串的开始处
select username,age from user where username regexp '^[123] z';

^字符串的开始处
select username,age from user where username regexp '^[0~9]';

范围匹配相当于[123456]
select username,age from user where username regexp '[1-6] z';

匹配特殊字符用\\
select username,age from user where username regexp '\\.'

CONCAT 拼接串

AS：别名
select concat(username,'(',age,')') AS info from user;

文本处理函数

select upper(username),age from user;

常用文本处理函数

函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值 根据发音字符和音节进行比较
SubString() 返回子串的字符
Upper() 将串转换为大写

日期与时间处理函数

按日查询
select username,age,createdate from user where date(createdate)='2004-11-11';
按月查询
select username, age, createdate from user where date_format(createdate,'%Y-%m') = '2004-11'; 
按年查询
select username,age,createdate from user where date_format(createdate,'%Y')='2004'

数值处理函数

函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

聚集函数

函数 说明
AVG() 返回某列的平均值 忽略NULL
COUNT() 返回某列的行数 如果为*则不忽略NULL，为列时忽略
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

数据分组 GROUP BY 和 HAVING
GROUP BY子句经常在聚合函数中使用，而HAVING配合GROUP BY使用

select count(age) AS a_age from user group by age;

select age,count(age) AS a_age from user group by age having count(age)>1; having：过滤分组

子句

子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出顺序排序 否
LIMIT 要检索的行数 否

子查询

select * from user where age=(select age from user_1 where addr='beijing');

联结

联结是一种机制，用来在一条SELECT语句中关联表
内部联结

select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND prod_id = 'TNT2';

INNER JOIN ON

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

INNER JOIN ON 连接三个数据表的用法：

SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：

SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)

INNER JOIN 表4 ON Member.字段号=表4.字段号

INNER JOIN 连接五个数据表的用法：

SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)

INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

自联结（相同的表查询两次）

select p1.prod_id, p1.prod_name from products AS p1, products AS p2 where p1.vend_id = p2.vend_id AND p2.vend_id = "DTNTR";

自然连结

排除多次出现，使每个列只返回一次

外部联结LEFT | RIGHT OUTER JOIN ON 返回包括没有的列

select vendors.vend_name, products.prod_name from vendors left outer join products ON vendors.vend_id = products.vend_id;

带聚集函数的联结
例：检索所有客户及每个客户所下的订单数

select customers.cust_name, customers.cust_id, count(orders.num) AS num_ord FROM customers inner join orders on customers.cust_id = orders.cust_id group by customers.cust_id;

组合函数 union

select vend_id, prod_id, prod_price from products where prod_price <= 5 union select vend_id, prod_id, prod_price from products where vend_id IN (1001, 1002);

UNION使用规则

  • 必须由两条或两条以上的SELECT语句组成，语句之间用UNION分隔
  • 每个查询必须包含相同的列、表达式或聚集函数（次序可以不同）
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换类型
  • UNION默认自动取消重复的行，如果想返回所有匹配的行使用UNION ALL

插入数据 INSERT INTO VALUES

insert into user(username, age, addr) values ('zhangba', 17, 'shanghai');

插入多条
insert into user(username, age, addr) values ('zhangjiu', 18, 'shanghai'), ('lishi', 20, 'beijing'), ('wangyi', 17, 'shanghai');

LOW_PRIORITY 降低INSERT语句的优先级
insert low_priority into user(username, age, addr) values ('zhangba', 17, 'shanghai');

插入数据INSERT INTO VALUES

insert into user(username,age,addr) values ('zhangsan',17,'beijing');

插入多条
insert into user(username,age,addr) values ('zhangjiu', 18, 'shanghai'), ('lishi', 20, 'beijing'), ('wangyi', 17, 'shanghai');

更新数据 UPDATE SET

update user SET age = 20 where username = 'zhangsi';

IGNORE 即使发生错误，也继续进行更新
update ignore user set createdate = '2019-07-15' where addr = 'beijing';

删除数据 DELETE FROM

delete FROM t_user where username = 'liujiu';

删除表中所有数据，删除原来的表并重新创建一个表，而delete是逐行删除，比delete快
TRUNCATE TABLE user;

创建表

 create table if not exists tb_user(          
    -> id int auto_increment primary key,//自增id从0开始
    -> user varchar(15) not null,
    -> password varchar(15) not null);

修改表 ALTER TABLE

向表中添加字段
alter table user add createdate datetime;

从表中删除字段
alter table user drop column createdate;

修改表中列的类型
alter table user modify createdate char(20);

修改表中列名
alter table user change createdate starttime datetime;

添加外键
alter table user add constraint fk_t_user_test_one foreign KEY (外键名) references t_new_user (主键名); 

on delete CASCADE on update CASCADE 联合删除 更新
alter table test_one add constraint fk_t_user_test_one foreign KEY (n_id) references t_new_user (n_id) on delete CASCADE on update CASCADE;

删除外键
alter table test_one drop FOREIGN KEY fk_t_user_test_one;

删除表DROP TABLE

drop table user

重命名表RENAME TABLE 原表名 TO 新表名

rename table user to test;
alter table user rename test

视图

创建视图
create view v_getuser as select t_user.username, t_user.age from t_user;
select * from v_getuser where age > 20;

查看创建视图的语句
show create view v_getuser;

删除视图
drop view v_getuser;

更新视图 也可先drop再create
create or replace view;

存储过程

CREATE PROCEDURE getavg()
  BEGIN
    SELECT AVG(DISTINCT age) AS age_avg FROM t_user;
  END;

调用存储过程
CALL getavg();

删除存储过程
DROP PROCEDURE IF EXISTS getavg;

显示存储过程的语句
SHOW CREATE PROCEDURE getavg;

显示所有存储过程
SHOW PROCEDURE STATUS;

过滤显示所有存储过程
SHOW PROCEDURE STATUS LIKE '%user%';

// 带输出参数
CREATE PROCEDURE procedure_age(OUT age_min DECIMAL, OUT age_max DECIMAL)
  BEGIN
    SELECT MIN(DISTINCT age) INTO age_min FROM t_user;
    SELECT MAX(DISTINCT age) INTO age_max FROM t_user;
  END;

CALL procedure_age(@ageMin, @ageMax);
SELECT @ageMin as minage, @ageMax as maxage;

游标

CREATE PROCEDURE p_username()
  BEGIN
    -- 定义局部变量
    DECLARE u CHAR(20);
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE cur_user CURSOR FOR SELECT username FROM user;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    -- 开启游标
    OPEN cur_user;
    --循环每一行
    REPEAT
    -- 检索当前行的列
    FETCH cur_user INTO u;
      INSERT INTO p_users VALUES(u);
    --结束循环
    UNTIL done END REPEAT;
     -- 关闭游标
    CLOSE cur_user;
  END;

触发器

MySQL响应 DELETE、UPDATE 和 INSERT 而自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语句）
只有表才支持触发器，视图不支持，临时表也不支持

创建触发器
CREATE TRIGGER testtrigger AFTER INSERT ON user FOR EACH ROW SELECT 'add';

删除
DROP TRIGGER testtrigger;

insert触发器
CREATE TRIGGER insertuser AFTER INSERT ON user FOR EACH ROW SELECT NEW.id INTO @insert_id;
INSERT INTO user(username, age, addr) VALUES('zhangyiyi', 23, 'tianjin');
SELECT @insert_id;

delete触发器 
-- begin end 块的好处是触发器能容纳多条sql语句
CREATE TRIGGER deleteuser BEFORE DELETE ON user FOR EACH ROW 
BEGIN
SELECT OLD.id INTO @delete_id;
END;

update触发器 OLD虚拟表访问更新之前的值，NEW访问更新之后的值
CREATE TRIGGER updateuser BEFORE UPDATE ON user FOR EACH ROW SET NEW.username = UPPER(NEW.username);

事务

用来维护数据库的完整性，保证成批的mysql操作要么完全执行，要么完全不执行

回滚ROLLBACK

SELECT * FROM user;
开启事务
START TRANSACTION;
DELETE FROM user;
SELECT * FROM user;
回滚
ROLLBACK;
SELECT * FROM user;

提交COMMIT

开启事务之后，只有都成功才会执行commit，出错都会撤销
START TRANSACTION;
DELETE FROM user WHERE id = 10;
DELETE FROM user WHERE id = 11;
提交
COMMIT;

保留点SAVEPOINT

START TRANSACTION;
INSERT INTO user(username, age, addr) VALUES ('zhangyier', 19, 'tianjin');
SAVEPOINT insesrt_user;
DELETE FROM user WHERE addr = 'tianjin';
ROLLBACK TO insesrt_user;

安全管理

USE mysql;
SELECT user FROM user;

创建账号
CREATE USER zyw IDENTIFIED BY 'zywrxq1224';

重命名
RENAME USER zyw TO zyw;

删除账号
DROP USER zyw1;

显示账号权限
SHOW GRANTS FOR zyw;

授权
GRANT SELECT, INSERT ON test_daily.* TO zyw;

取消授权
REVOKE SELECT ON test_daily.* FROM zyw;

整个服务器
GRANT ALL ON *.* TO zyw;
整个数据库
GRANT ALL ON test_daily.* TO zyw;
整个表
GRANT ALL ON test_daily.t_user TO zyw;

修改账号密码
ALTER user 'root'@'localhost' IDENTIFIED BY '新密码';

标签：语句,username,age,user,MYSQL,where,id,select
From： https://www.cnblogs.com/ZhaoYork/p/17781208.html

相关文章

  • DataWhale DAY5 条件语句
    DataWhaleDAY5条件语句本次学习python中的条件语句。语法博客：https://www.cnblogs.com/hewo/p/17635277.html注意点位：1.减少炫技般的使用特殊方法的判断，从理解方面简化你的代码，对于python，没有必要时不用使用奇技淫巧优化。对于true/false和0/1：​ 首先，bool是int的......
  • win32汇编-分支语句
    分支语句用来根据条件表达式测试的真假执行不同的代码模块，MASM中的分支语句的语法如下： 注意：关键字if/elseif/else/endif的前面有个小数点，如果不加小数点，就变成宏汇编中的条件汇编伪操作了，结果可是天差地别。         ......
  • MYSQL
    什么是事务事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，......
  • oracle查询执行过sql语句
    一、oracle中查找某段时间执行的操作记录selectsql_text,module,first_load_timefromv$sqlareawherefirst_load_time>'2019-02-02/02:02:02'andfirst_load_time<'2019-02-02/02:02:02'orderbyfirst_load_time二、查看某一时间的执行过的所有sqlselect......
  • MySQL分享
    本次分享主要涉及InnoDB如何在磁盘上保存数据InnoDB表空间结构：介绍了InnoDB表文件中的一些组件，比如段、区、页、行记录。除了告诉你怎样存之外，更重要的是希望解释为什么要这样索引页结构：数据以及索引都存在于索引页中，介绍索引页如何组织，当数据删除、更新时会发生啥索引分裂、......
  • 巧用枚举解决业务场景的 Switch 语句—Java 实践
    巧用枚举解决业务场景的Switch语句——Java实践Switch语句是一种常见的流程控制语句，用于根据不同的条件执行不同的代码块。然而，当业务场景变得复杂时，使用大量的Switch语句可能导致代码冗长、难以维护和扩展。本文将介绍如何巧妙地使用枚举类型来优化和简化Switch语句的使......
  • MySQL存储引擎
    showengines;查看支持的存储引擎。mysql表的存储引擎:InnoDBmyISAMMemoryMRG_MYISAMarchivefederated,CSV,BLACKHOLE1、InnoDB支持事务和外键:InnoDB具有事务，支持4个事务隔离级别，回滚，崩溃修复能力和多版本并发的事务安全，包括ACID。如果应用中需要执行大量的INSER......
  • mysql8解决null, message from server: “Host is not allowed to connect to this My
    1.登录mysql（注意：账号密码不包括，-u和-p）mysql-uroot-proot2.切换到mysql库：usemysql;3.查询root用户的host值：selectuser,hostfromuser;如果host的值是localhost，说明只支持本地连接，不能远程登录。4.更改host的值为%:updateusersethost='%'whereuser='root';5.刷......
  • Python教程(15)——Python流程控制语句详解
    Python流程控制是Python编程中非常重要的一部分，它用于控制程序的执行流程。Python提供了多种流程控制语句，包括if语句、while循环、for循环、break和continue语句等。这种流程控制在各个语言中都是大同小异的，如果你已经学过其他的语言，那么这章节就可以直接跳过。if语句if语句用于......
  • MySQL中有哪几种锁，列举一下；意向锁是什么？
    一、MySQL中有哪几种锁如果按锁粒度划分，有以下3种：表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99