select * from personnel LIMIT 10 # 查询前十天数据
select * from personnel LIMIT 10,2 #查询从第十条开始的 两条数据
select * from personnel LIMIT 0,-1# 查询前十天数据
SELECT * FROM personnel WHERE id=(SELECT MAX(id) FROM personnel); #查询id最大的一个数
SELECT Name, MAX(id) AS p_Id FROM Personnel GROUP BY Name ; #查询相同名字人员id最高一个!
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'; #修改语句
select * from perclass where perclass.state not in ('删除') as dfdfdfdfd
select * from percollege,perclass WHERE perclass.percollege = percollege.id; #内连接
select * from percollege INNER JOIN perclass on perclass.percollege = percollege.id; #内连接 必须都符合 才会被找到
select percollege.id,percollege.college_name,perclass.perclass from percollege LEFT JOIN perclass on percollege.id = perclass.percollege ORDER BY percollege.id; #左连接 连接时即使右边空的也会被搜索
ution
select percollege.id,percollege.college_name,perclass.perclass from perclass RIGHT JOIN percollege on percollege.id = perclass.percollege ORDER BY percollege.id; #右连接 连接时即使左边空的也会被搜索
select * from perclass RIGHT JOIN percollege on perclass.percollege = percollege.id; #右同理连接
select * from personnel LEFT JOIN percollege on personnel.personnelCollege = percollege.id
select percollege.id,percollege.college_name,perclass.perclass from percollege,perclass
mysql> select DATABASE();#查询数据库
SHOW TABLES; #所有数据库表
DESCRIBE personnel; #查询某张表的结构
SELECT VERSION(), CURRENT_DATE; #要求服务器告诉它的版本号和当前日期
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE; #能够以大小写输入关键词。下列查询是等价的
SELECT (4+1)*5,5*5; #mysql用作一个简单的计算器
SELECT VERSION(); SELECT NOW(); #你可以在一行上输入多条语句,只需要以一个分号间隔开各语句
SELECT USER() #查询sql用户
select * from paper;
flush tables
select paper_Id, CURDATE(),
(YEAR(CURDATE())-YEAR(exam_Date)) ,RIGHT(CURDATE(),5),DAYOFMONTH((exam_Date)) from paper
SELECT paper_Id, exam_Date FROM paper
WHERE MONTH(exam_Date) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
use 2025kaoshi;
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
select * from personnel
SELECT * from t1;
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
SHOW VARIABLES LIKE '%partition%';
CREATE PROCEDURE proc(OUT id int)
BEGIN
SELECT FROM personnel;
END
#注释:mysql存储过程可使用两种风格的注释双横杠:-- 该风格一般用于单行注释 c风格:/* 注释内容 */ 一般用于多行注释
/*
IN 输入参数
表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT 输出参数
该值可在存储过程内部被改变,并可返回
INOUT 输入输出参数
调用时指定,并且可被改变和返回
*/
create procedure select1()#创建存储过程
begin
select * from personnel;
end
select routine_name from information_schema.routines where routine_schema='2025kaoshi'; #查看存储过程
show create procedure select1 #显示一个存储过程的详细信息
DROP procedure GreetWorld #删除存储过程
SELECT 'Hello World' into @x; #创建变量
SELECT @x; #使用变量
SET @z=1+2+3;
select @z;
CALL select1(); #调用存储函数
# 在存储过程中使用用户变量
create procedure testfun()#创建存储过程
begin
SET @greeting='Hello2';
SELECT CONCAT(@greeting,' 2World');
end
CALL testfun(); #调用存储函数
#在存储过程间传递全局范围的用户变量
CREATE PROCEDURE p1( ) SET @last_procedure='p1';
CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
.
begin
SET @greeting='Hello2';
SELECT CONCAT(@greeting,' 2World');
end
CALL testfun(); #调用存储函数
DROP procedure test2 #删除存储过程
#IN参数例子:
CREATE PROCEDURE test2(IN p_in INT)
BEGIN
SELECT p_in;
SET p_in=2;
select p_in;
END;
set @p_in=1
CALL test2(@p_in); #调用存储函数
select @p_in;
OUT参数例子
创建:
CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
BEGIN
SELECT p_out;/*查看输出参数*/
SET p_out=2;/*修改参数值*/
SELECT p_out;/*看看有否变化*/
END;
SET @p_out=1
CALL sp_demo_out_parameter(@p_out)
SELECT @p_out;
INOUT参数例子:
CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout as p;
select p;
END;
set @p_inout=1
call sp_demo_inout_parameter(@p_inout)
select @p_inout;
插入语句