数据库练习

use m416;
database CHANGED
SELECT*FROM studentinfo;

SELECT id,studentname,gender,age from studentinfo;

SELECT id as 学号,studentname as 姓名,gender as 性别,age as 年龄 from studentinfo;

SELECT id 学号,studentname 姓名,gender 性别,age 年龄 from studentinfo;

SELECT id,studentname,gender,age from studentinfo WHERE age > 21;

SELECT id,studentname,gender,age from studentinfo WHERE gender = ‘女’;

SELECT id,studentname,gender,age from studentinfo WHERE age < 21;

SELECT id,studentname,gender,age,city from studentinfo WHERE city ! = ‘郑州’;

SELECT id,studentname,gender,age from studentinfo WHERE age = 20 AND gender = ‘女’;

SELECT id,studentname,gender,age,city from studentinfo WHERE age < 21 OR city <> ‘郑州’;

SELECT id,studentname,gender,age,classid from studentinfo WHERE NOT(classid = 1);

SELECT id,studentname,gender,age,classid from studentinfo WHERE classid <> 1;

SELECT DISTINCT classid FROM studentinfo;

SELECT id,studentname,gender,age from studentinfo LIMIT 4;

SELECT id,studentname,gender,age from studentinfo LIMIT 10,5;

SELECT id,studentname,gender,age from studentinfo WHERE age is null;

SELECT id,studentname,gender,age from studentinfo ORDER BY age ASC;

SELECT id,studentname,gender,age from studentinfo ORDER BY age DESC;

SELECT id,studentname,gender,age,birthday from studentinfo ORDER BY age DESC,birthday ASC;

SELECT concat(substring(title,1,5),’…’) as 新闻标题 FROM newsinfo;

select NOW();

select CURDATE();

SELECT CURTIME();

SELECT MONTH(NOW());

SELECT monthname(CURDATE());

SELECT DAYNAME(CURDATE());

SELECT DAYOFWEEK(CURDATE());

SELECT WEEKDAY(CURDATE());

SELECT WEEK(CURDATE());

SELECT WEEKOFYEAR(CURDATE());

SELECT DAYOFYEAR(CURDATE());

SELECT DAYOFMONTH(CURDATE());

SELECT now();

sel @dt = NOW();

SELECT DATE_ADD(@dt,INTERVAL 1 day);

SELECT DATE_ADD(@dt,INTERVAL 1 HOUR);

SELECT DATE_sub(@dt,INTERVAL 1 hour);

SELECT DATE_diff(‘2022-12-25′,’2022-12-22’);

SELECT FORMAT(12345678.12345,4);

SELECT FORMAT(12345678.12344,4);

SELECT ABS(3),ABS(-3),SQRT(10,3);

SELECT CEIL(2.3),CEIL(2.6),FLOOR(2.3),FLOOR(2.6);

SELECT RAND(),RAND(),RAND(1),RAND(1);

SELECT TRUNCATE(3.14159,2),TRUNCATE(3.14159,3);

SELECT SIGN(-3),SIGN(0),SIGN(3);

SELECT POWER(3,2),power(3,3);

SELECT DATABASE(),VERSION(),USER();