如何通过MySQL进行数据库基本操作

-- 建立数据库，把create替换为drop即删除数据库
create database if not exists WNB;
use WNB #使用数据库/表
create table if not exists student(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL COMMENT '姓名',
    `gradeid` INT(1) NOT NULL COMMENT '年级号',
    `sex` VARCHAR(2) NOT NULL COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8 
-- 建立表，INNOBE是引擎，CHARSET是字体形式，一般这种配置显示汉字比较好

-- 显示表结构
DESC student;
-- 修改表
ALTER table student RENAME AS student1;
-- 添加字的字段
ALTER TABLE student ADD age INT(11);
-- 修改表的字段
ALTER TABLE student MODIFY age VARCHAR(11); -- 修改约束和字段类型
ALTER TABLE student CHANGE age age1 INT(1); -- 重命名字段
-- 删除字段
ALTER TABLE student DROP age1;
-- 删除表
DROP TABLE IF EXISTS student;

-- 插入操作：需要一一对应，需要以“；”结尾
INSERT INTO `student`(`id`,`name`,`gradeid`,`sex`,`birthday`,`address`,`email`) 
VALUES (1000,'张三',3,'男','2004-8-9','宿舍楼A101','WNB@qq.com'),
       (1001,'李四',3,'女','2005-4-9','宿舍楼C101','WFR@qq.com'),
       (1002,'王五',3,'男','1999-12-9','宿舍楼A201','ZBN@qq.com');
-- 更新数值操作
UPDATE `student` SET `name` = '赵六' WHERE id =1001; -- where表示条件判断
-- 清空指定信息
DELETE FROM `student` WHERE id=1000;  -- 清空指定信息，但不会影响计数器
TRUNCATE `student`  -- 清空student表，计数器会归零，不会影响事务

-- 查询全部学生
SELECT * FROM student;
-- 查询指定字段
SELECT `id` AS 学号,`name` AS 姓名 FROM student;
-- 查询全部成绩,distinct去重，每个学号只显示一条
SELECT DISTINCT `id` FROM result;
-- 查看版本
SELECT VERSION();
-- 考试成绩+1分显示
SELECT `id`,`mark`+1 AS '提分后' FROM result;
-- where选择查询
SELECT `id`,`subid`,`mark` FROM result
WHERE id !=1000;
-- Like模糊查询
SELECT `id`,`name` FROM student
WHERE `name` LIKE '%牛%'; -- %表示N位数，一个“_”表示一个字母

-- 联合查询
-- 格式：联立subject表和result表，课程id号是联合的媒介，
-- 注意书写格式，对于媒介字段需要特别标明是选自哪个表的。
SELECT id,s.subid,subname,mark
FROM `subject` AS s
INNER JOIN result AS r
ON s.subid=r.subid


-- 左右联表
-- 注意：与联合查询不同之处在于命令，左Left，右Right，
-- 左联表查询会从左表返回所有值即使右表没有匹配，
-- 右联表同理，左右联表和联合查询可以同时使用。
SELECT id,s.subid,subname,mark
FROM `subject` AS s
LEFT JOIN result AS r
ON s.subid=r.subid
-- 注意：由于是左联表查询，返回左表的值即subject，如果
-- result表里内容发生改变，得到的结果不会出现右表的改变。

-- 执行命令：
SELECT s.id,`name`,`subname`,mark
FROM `student` s
         INNER JOIN `result` r
                    ON s.id=r.id
         INNER JOIN `subject` sub
                    ON sub.subid=r.subid
WHERE subname='数据库' AND mark>=90 -- mark是分数字段，可自己设置
ORDER BY mark DESC
LIMIT 0,5

--（1）基本函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0，负数-1，正数1
-- 字符串函数
SELECT CHAR_LENGTH('Hello world！') -- 字符串长度
SELECT CONCAT('Hello',' world','！') -- 拼接字符
SELECT INSERT('我爱美食世界',1,2,'超级热爱') -- 查询，
-- 从某个位置开始代替某个长度，本例子是从“我”字符开始替换，2个字节长度，
-- 即把“我爱”替换成“超级喜欢”
SELECT UPPER('wangshen') -- 大写字母
SELECT LOWER('WANGSHEN') -- 小写字母
SELECT INSTR('Wangshen','n') -- 返回第一次出现子串的索引
SELECT REPLACE('A说你很厉害','你','他')  -- 替换出现的指定字符串，你换成他
SELECT SUBSTR('AB说他可是特别牛逼',4,6) -- 返回指定的字符子串（源字符串，截取位置，长度）截取出来“他可是特别牛”
SELECT REVERSE('猫吃鱼') -- 反转，得到鱼吃猫的结果
-- 查询姓 王-->李的同学
SELECT DISTINCT REPLACE(`name`,'王','李') FROM student
WHERE `name` LIKE '王%'
-- 获取时间
SELECT NOW() -- 获取当下时间
SELECT CURDATE() -- 获取当前日期，等同于 SELECT CURRENT_DATE(); SELECT LOCALTIME() -- 获取本地时间;SELECT SYSDATE(); -- 获取系统时间
SELECT DAY(NOW()) -- 获取当下天数
--（2）聚合函数
聚合函数用途非常广泛，最常用的来说，分为count，sum,avg,max,min五种。
用法：
SELECT COUNT(*) FROM student; -- 不会忽略NULL
SELECT COUNT(id) FROM student; -- 会忽略NULL值
SELECT COUNT(1) FROM student; -- 本质计算行数
SELECT SUM(`mark`) AS 总和 FROM result
SELECT AVG(`mark`) AS 平均分 FROM result
SELECT MAX(`mark`) AS 最高分 FROM result
SELECT MIN(`mark`) AS 最低分 FROM result
-- 获取成绩单中成绩信息