MySQL语句

最新推荐文章于 2024-10-09 22:03:37 发布

麦客子

最新推荐文章于 2024-10-09 22:03:37 发布

阅读量401

点赞数 1

分类专栏：数据库文章标签： mysql

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

CREATE DATABASE schools;
USE schools;
CREATE TABLE student(
	sid TINYINT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(16) NOT NULL,
	age TINYINT NOT NULL,
	sex ENUM('男','女') NOT NULL,
	love SET('shopping','music','sport','eat','sleep') NOT NULL,
	money DECIMAL(12,2) NOT NULL
		
);
DESC student;
INSERT INTO student(sname,age,sex,love,money) VALUES('二哈',18,1,3,10000000.11);
INSERT INTO student(sname,age,sex,love,money) VALUES('二狗',16,2,4,22222222.22);
SELECT * FROM student ORDER BY sid DESC;
SELECT * FROM student ORDER BY money DESC,sid ASC;
SELECT * FROM student LIMIT 2,3;
SELECT * FROM student LIMIT 5,3;
ALTER TABLE student ADD CONSTRAINT sname_key UNIQUE KEY student(sname);
SHOW VARIABLES LIKE 'storage_engine%';
#字符串替换
SELECT INSERT('这是数据库sql server',5,11,'mysql');
#字符串插入
select insert('这是数据库sql server',5,0,'mysql');

USE school;
CREATE TABLE st(
	studentNo INT(4) PRIMARY KEY COMMENT '学号',
	loginPwd VARCHAR(20) NOT NULL COMMENT '密码',
	studentName VARCHAR(50) NOT NULL COMMENT '姓名',
	sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	gradeId INT COMMENT '年级编号',
	phone VARCHAR(20) COMMENT '电话',
	address VARCHAR(255) DEFAULT '地址不详' COMMENT '地址',
	bornDate DATETIME COMMENT '出生日期',
	email VARCHAR(50) COMMENT '邮箱',
	identityCard VARCHAR(18) UNIQUE COMMENT '身份证号',
	FOREIGN KEY(gradeId) REFERENCES grade(gradeId)


)COMMENT '学生表';
#无符号
UNSIGNED
#查看数据库中的所有存储引擎
SHOW ENGINES;
#查看默认的存储引擎
SHOW VARIABLES LIKE 'storage_engine%';

SHOW DATABASES;
USE schools;
CREATE TABLE person(
	personNo INT(4) PRIMARY KEY  COMMENT '人类编号',
	personName VARCHAR(16) NOT NULL,
	indentityCard CHAR(20) UNIQUE COMMENT '身份证',
	age INT(3) UNSIGNED  ZEROFILL NOT NULL,
	sex ENUM('男','女') NOT NULL
);
DESC person;
# 可以不写值的情况  1. 主键自增 2.有默认值 3.可以为null
ALTER TABLE person ADD address VARCHAR(100) DEFAULT '地址不详';

INSERT INTO person(personNo,personName,indentityCard,age,sex) VALUES(1,'小明','324235435436',12,2);
/*注释内容*/
# 查看挡墙mysql存储引擎 innoDb 支持事务 行锁 对修改的效率高 MyISAM 不支持事务 表锁 对数据库查询效率高
SHOW ENGINES;
SHOW VARIABLES LIKE 'storage_engine%';
CREATE TABLE p(
	id INT(4),
	NAME VARCHAR(12)

)ENGINE=MYISAM;

#创建myschool库
CREATE DATABASE myschool;
#使用库
USE myschool;
#获取数据库表数据的个数
SELECT COUNT(*) AS COUNT FROM student;
SELECT COUNT(0) FROM student;
SELECT COUNT(studentNo) FROM student;
#获取最大值
SELECT MAX(gradeId) AS MAX FROM student;
#获取最小值
SELECT MIN(gradeId) AS MIN FROM student;
#获取平均值
SELECT AVG(studentNo) AS AVG FROM student;
#获取和
SELECT SUM(studentNo) AS SUM FROM student;
#字符串链接
SELECT CONCAT('s','q','l');
#字符串的替换
SELECT INSERT('这是sql server系统',3,10,'mysql');
#字符串的截取
SELECT SUBSTRING('这是mysql数据库',3,5);
#字符串转换小写
SELECT LOWER('MySQL');
#字符串转换大写
SELECT UPPER('mysql');
#获取当前的日期
SELECT CURDATE();
#获取当期的时间
SELECT CURTIME();
#获取当前的时间和日期
SELECT NOW();
#获取当前年的第几周
SELECT WEEK(NOW());
#返回哪一年
SELECT YEAR(NOW());
#返回当天的第几个小时
SELECT HOUR(NOW());
# 返回当前时间的第几分钟
SELECT MINUTE(NOW());
# 返回两个时间之间相差多少天
SELECT DATEDIFF(NOW(),'1997-8-8');
# 在当前的时间上面添加多少天
SELECT ADDDATE(NOW(),5);
#向上取整
SELECT CEIL(2.3);
#向下取整
SELECT FLOOR(5.9);
#随机0-1 包含0不包含1
SELECT RAND();



CREATE TABLE IF NOT EXISTS student(
	studentNo INT(4) PRIMARY KEY COMMENT '学生学号',
	loginPwd VARCHAR(16) NOT NULL COMMENT '登陆密码',
	studentName VARCHAR(50) NOT NULL COMMENT '姓名',
	sex CHAR(4) NOT NULL DEFAULT '男' COMMENT '性别',
	gradeId INT COMMENT '年级编号',
	phone VARCHAR(20) NOT NULL COMMENT '手机号',
	address VARCHAR(100) DEFAULT '地址不详' COMMENT '详细住址',
	bornDate DATETIME COMMENT '出生时间',
	email VARCHAR(20) COMMENT '邮箱',
	identityCard CHAR(20) UNIQUE NOT NULL COMMENT '身份证号',
	FOREIGN KEY(gradeId) REFERENCES grade(gradeId)
)COMMENT '学生信息表';

ALTER TABLE student ADD CONSTRAINT student_key FOREIGN KEY(gradeId) REFERENCES grade(gradeId);

DESC grade;

DROP TABLE student;
CREATE TABLE IF NOT EXISTS student(
	studentNo INT(4) PRIMARY KEY COMMENT '学生学号',
	loginPwd VARCHAR(16) NOT NULL COMMENT '登陆密码',
	studentName VARCHAR(50) NOT NULL COMMENT '姓名',
	sex CHAR(4) NOT NULL DEFAULT '男' COMMENT '性别',
	gradeId INT COMMENT '年级编号',
	phone VARCHAR(20) NOT NULL COMMENT '手机号',
	address VARCHAR(100) DEFAULT '地址不详' COMMENT '详细住址',
	bornDate DATETIME COMMENT '出生时间',
	email VARCHAR(20) COMMENT '邮箱',
	identityCard CHAR(20) UNIQUE NOT NULL COMMENT '身份证号',
	FOREIGN KEY(gradeId) REFERENCES grade(gradeId)
)COMMENT '学生信息表';

USE myschool;
DESC student;
SELECT * FROM student;
SELECT bornDate FROM student WHERE studentName='小明';

#以sql语句查询的结果作为另一个语句查询的条件 就叫做子查询
SELECT studentName,bornDate FROM student WHERE bornDate<(SELECT bornDate FROM student WHERE studentName='小明');

#1.声明变量
SET @num=100;
SET @num1:=200;
SET @str='长沙';
#2.select声明变量
SELECT @born:=bornDate FROM student WHERE studentName='小明';
SELECT * FROM student WHERE bornDate<@born;

USE myschool;
CREATE TABLE SUBJECT(
	subjectNo INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '科目编号',
	subjectName VARCHAR(10) COMMENT '科目的名称',
	classHour INT(4) COMMENT '学时',
	gradeId INT(4) COMMENT '年级'
)COMMENT '科目表';

CREATE TABLE result(
	studentNo INT(4) NOT NULL COMMENT '学号',
	subjectNo INT(4) NOT NULL COMMENT '科目号',
	examDate DATETIME NOT NULL COMMENT '考试时间',
	studentResult INT(4) NOT NULL COMMENT '考试成绩'

)COMMENT '学生成绩表';


#查询java科目编号
SELECT subjectNo FROM SUBJECT WHERE subjectName='java';
#查询最近一次java考试
SELECT MAX(examDate) FROM result WHERE subjectNo=1;
#根据java的科目号和最近的一次考试查最大成绩和最小成绩
SELECT MAX(studentResult) AS '最大值',MIN(studentResult) AS '最小值' FROM result 
WHERE examDate=(SELECT MAX(examDate) FROM result WHERE subjectNo=1) AND 
subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java');

USE myschool;
#查询java成绩为90的学生信息
#1.查询java编号
SELECT subjectNo FROM SUBJECT WHERE subjectName='java';
#2.查询成绩
SELECT studentNo FROM result WHERE studentResult=90;
#查询学生信息
SELECT * FROM student WHERE studentNo=
(SELECT studentNo FROM result WHERE studentResult=90 AND subjectNo=
(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'));
#使用in查询多个学生信息  not in
SELECT * FROM student WHERE studentNo IN
(SELECT studentNo FROM result WHERE studentResult=90 AND subjectNo=
(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'));
# not in
SELECT * FROM student WHERE studentNo NOT IN
(SELECT studentNo FROM result WHERE studentResult=90 AND subjectNo=
(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'));

#通过性别分组查学生信息表
SELECT * FROM student GROUP BY sex;
#通过count() 统计每一组有多少个
SELECT COUNT(sex),sex FROM student GROUP BY sex;

#查询没有在指定时间(2017-09-05 14:43:14)里面考试java的学生信息
SELECT * FROM student WHERE studentNo NOT IN
(SELECT studentNo FROM result WHERE examDate='2017-09-05 14:43:14' AND subjectNo=
(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'));

#查询获取年级编号'一年级' 根据年级编号查询科目表里面的科目名称
SELECT subjectName FROM SUBJECT WHERE gradeId IN 
(SELECT gradeId FROM grade WHERE gradeName='一年级');

#exists 子查询 返回结果为true或false    not exists
SELECT * FROM student WHERE EXISTS(SELECT gradeId FROM grade WHERE gradeName='四年级');
#查询java最近一次考试成绩在60分以上，并且显示成绩前2名的学生信息(作业)

#查询java最近一次考试成绩在60分以上，并且显示成绩前2名的学生信息(作业)
#java编号
SELECT subjectNo FROM SUBJECT WHERE subjectName='java';
#最近一次java考试
SELECT MAX(examDate) FROM result WHERE 
subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java');
#成绩在60分以上
 SELECT studentNo FROM result WHERE  studentResult>60 
 AND subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java')
 AND examDate IN (SELECT MAX(examDate) FROM result WHERE 
subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'));
#查最大成绩
SELECT studentResult FROM result WHERE examDate IN(SELECT MAX(examDate) FROM result WHERE 
subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java')) ORDER BY studentResult DESC;
#通过排序，取前两名
SELECT * FROM student WHERE studentNo 
IN(SELECT studentNo FROM result WHERE  studentResult IN(SELECT studentResult FROM result WHERE examDate IN(SELECT MAX(examDate) FROM result WHERE 
subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'))AND studentResult>60 ORDER BY studentResult DESC) 
 AND subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java')
 AND examDate IN (SELECT MAX(examDate) FROM result WHERE 
subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'))) LIMIT 0,2;





#俩个表查询，结合两个表一起查 笛卡尔乘积
SELECT studentName,gradeName FROM student,grade;
#需要给条件 隐式内连接 where条件
SELECT studentName,gradeName FROM 
student AS s,grade AS g WHERE s.gradeId=g.gradeId;
# 显示内连接 [inner/cross]可写可不写
SELECT studentName,gradeName FROM 
student AS s INNER JOIN grade AS g ON s.gradeId=g.gradeId;

# 三个表连接 查学生的姓名 科目成绩 科目名称

SELECT studentName,studentResult,subjectName FROM student AS s 
INNER JOIN 
result AS r ON s.studentNo=r.studentNo 
INNER JOIN 
SUBJECT AS sub ON sub.subjectNo=r.subjectNo;


`goods`#创建数据库jd
CREATE DATABASE jd;
USE jd;
#创建数据库表
CREATE TABLE USER(
	uid INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
	uname VARCHAR(16) NOT NULL COMMENT '用户名字',
	gid INT COMMENT '用户购买商品ID'
);
CREATE TABLE goods(
	gid INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
	gname VARCHAR(16) NOT NULL COMMENT '商品名字',
	price INT NOT NULL COMMENT '商品的价格'
);

#左外链接 查没有购买过商品的用户
SELECT * FROM USER LEFT JOIN goods ON user.gid=goods.gid WHERE user.gid IS NULL;

# 作为主表的数据都显示
SELECT * FROM USER LEFT JOIN goods ON user.gid=goods.gid;
# 右链接 查询没有被购买过的商品
SELECT * FROM USER RIGHT JOIN goods ON user.gid=goods.gid WHERE user.uid IS NULL;
# 右链接
SELECT * FROM USER RIGHT JOIN goods ON user.gid=goods.gid

#联合查询
SELECT * FROM USER LEFT JOIN goods ON user.gid=goods.gid UNION
SELECT * FROM USER RIGHT JOIN goods ON user.gid=goods.gid;





USE school;
#删除表数据，主键不会重置
DELETE FROM grade;
USE schools;
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(16) 

);
# truncate 删除表重置主键
TRUNCATE TABLE USER;
3.DCL
	创建用户：create user '用户名'@'主机名' identified by '密码';
	授权：grant all on 数据名.表名 to '用户名'@'主机名';
	取消权限：revoke all on *.* from '用户名'@'主机名';
	删除用户：drop user '用户名'@'主机名';
	查看用户的权限
		如果for后面的内容不写，默认查看当前用户，写的话查看指定用户的权限
		show grants [for '用户名'@'主机名'];
		说明：
			all：所有的权限
			*：代表所有的库或表
			%：模糊查询
4.DTL
	1.set autocommit=0;		开启事务
	2.rollback;		回滚
	3.commit;		手动提交事务
5.通过命令窗口导入和导出数据库
	导出：mysqldump -uroot -p 数据库名 > 文件名.sql
	导入：mysql -uroot -p 数据库名 < 文件名.sql
		说明：
			1.需要在本地磁盘下或者根目录下操作
			2.导入之前先创建一个空的数据库