MySQL 入门基础语句（增删改查）

创建用户 & 授权

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'tinydolphin'@'localhost';

创建数据库 & 数据表

-- 创建数据库
CREATE DATABASE sampdb;
-- 查询当前选定的数据库
SELECT database();
-- 选择数据库
USE sampdb;

-- 创建数据表
CREATE TABLE president(
  last_name VARCHAR(15) NOT NULL COMMENT '名',
  first_name VARCHAR(15) NOT NULL COMMENT '姓',
  suffix VARCHAR(5) NULL COMMENT '姓名后缀',
  city VARCHAR(20) NOT NULL COMMENT '出生地（城市）',
  state VARCHAR(2) NOT NULL COMMENT '出生地（州）',
  brith DATE NOT NULL COMMENT '出生日期',
  death DATE NULL COMMENT '逝世日期'
) COMMENT='总统信息表';
-- comment '注释'

CREATE TABLE member(
  member_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (member_id), -- 设置主键
  last_name VARCHAR(15) NOT NULL COMMENT '名',
  first_name VARCHAR(15) NOT NULL COMMENT '姓',
  suffix VARCHAR(5) NULL COMMENT '姓名后缀',
  expiration DATE NULL COMMENT '失效日期',
  email VARCHAR(100) NULL COMMENT '电子邮箱地址',
  street VARCHAR(50) NULL COMMENT '邮政地址（街道地址）',
  city VARCHAR(50) NULL COMMENT '邮政地址（城市名）',
  state VARCHAR(2) NULL COMMENT '邮政地址（州）',
  zip VARCHAR(10) NULL COMMENT '邮政编码',
  phone VARCHAR(20) NULL COMMENT '电话号码',
  interests VARCHAR(255) NULL COMMENT '会员兴趣关键字'
) COMMENT='会员信息表';
-- unsigned 不允许出现负数 & auto_increment 递增
-- auto_increment 工作原理：如果没有给出 member_id 的值（或者给出的值为 null），MySQL 将自动生成下一个编号并赋值给它
-- primary key 表示需要对 member_id 数据列创建索引以加快查找速度，同时也要求该数据列里的各个值必须是唯一的

-- 查看表结构四种方式
DESCRIBE president '%name';
DESC president;
EXPLAIN president;
SHOW COLUMNS FROM president LIKE '%name';

-- 列出当前数据库里的数据表
SHOW TABLES ;
-- 列出当前连接的服务器上的数据库
SHOW DATABASES ;

CREATE TABLE student(
  name VARCHAR(20) NOT NULL COMMENT '姓名',
  sex ENUM('F','M') NOT NULL COMMENT '性别（F：女 M：男）',
  student_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  PRIMARY KEY (student_id)
)ENGINE = InnoDB COMMENT='学生表';
-- ENUM('F','M')：枚举类型
-- engine 子句是指定 MySQL 用来创建数据表的存储引擎的名字，
-- 默认是使用 MyISAM（indexed sequential access method：索引化顺序访问方法）

-- 查看 student 的 sex 字段
DESCRIBE student 'sex';

CREATE TABLE grade_event(
  data DATE NOT NULL COMMENT '日期',
  category ENUM('T','Q') NOT NULL COMMENT '分数的类型',
  event_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '事件ID',
  PRIMARY KEY (event_id)
) ENGINE = InnoDB COMMENT='年级事件表';

CREATE TABLE score(
  student_id INT UNSIGNED NOT NULL COMMENT '学生ID',
  event_id INT UNSIGNED NOT NULL COMMENT '事件ID',
  score INT NOT NULL COMMENT '分数',
  PRIMARY KEY (event_id,student_id),
  INDEX (student_id),   -- 为什么定义一个索引？因为不满足条件②，只能满足条件①。
  FOREIGN KEY (event_id) REFERENCES grade_event(event_id), 
  FOREIGN KEY (student_id) REFERENCES student(student_id) 
) ENGINE = InnoDB COMMENT='分数表';
-- foreign key 定义它遵循的约束条件
-- references 指定主外键对应
-- 对于 foreign key 中出现的数据列，只有两种：①、本身就是索引；②、多数据列索引里面的第一个被列出来的数据列(event_id)。
-- 此处，虽然 InnoDB 存储引擎会自动给出现在外键定义里的数据列（student_id） 创建索引，但是自动创建的不一定是你想要的。

CREATE TABLE absence(
  student_id INT UNSIGNED NOT NULL COMMENT '学生ID',
  date DATE NOT NULL COMMENT '缺勤日期',
  PRIMARY KEY (student_id,date),
  FOREIGN KEY (student_id) REFERENCES student(student_id)
) ENGINE = InnoDB COMMENT='缺勤表';

添加新的数据行

-- 添加新的数据行
-- 1、利用 INSERT 语句添加数据
-- (1)、一次性地列出全部数据列的值
INSERT INTO student VALUES ('kyle','M',NULL );
INSERT INTO grade_event VALUES ('2017-12-03','Q',NULL );
INSERT INTO student VALUES ('Avery','F',NULL),('Nathan','M',NULL);
-- 字符串和日期值必须放在 '' 或者 "" 里才能被引用，放在 '' 里更加标准。
-- 在一个 auto_increment 数据列里插入一个表示"无数据"的 NULL 值时，MySQL 会为这个数据列自动生成下一个序号。

-- (2)、直接对数据列进行赋值
INSERT INTO member (last_name,first_name) VALUES ('Stein','Waldo');
INSERT INTO student (name, sex) VALUES ('Abby','F'),('Joseph','M');

-- (3)、包含 col_name = value（而非values()列表）的 SET 子句对数据列赋值
INSERT INTO member SET last_name='Stein',first_name='Waldo';
-- 不允许一次插入多个数据行

-- 2、通过从文件中读取来添加新行（后续详解）
LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member;

检索信息

-- 检索信息
SELECT * FROM president;
-- 注意：数据库和数据表的名字可能区分字母的大小写，取决于服务器主机上所使用的文件系统，以及 MySQL 的配置情况
-- Windows 文件名不区分大小写，Unix 文件区分大小写

-- 1、指定检索条件
SELECT * FROM score WHERE score > 95;
-- 以下两句相同效果
SELECT last_name,first_name,state FROM president
WHERE state='VA' OR state='MA';
SELECT last_name,first_name,state FROM president
WHERE state IN ('VA','MA');

-- 2、NULL 值
-- 完成 NULL 值与 NULL 值之间的比较
SELECT * FROM president WHERE death IS NULL ;
SELECT * FROM president WHERE death <=> NULL;
SELECT * FROM president WHERE suffix IS NOT NULL;
SELECT * FROM president WHERE NOT (suffix<=> NULL);

-- 3、对查询结果进行排序默认升序：  ASC升序  DESC降序
SELECT * FROM president ORDER BY last_name DESC,first_name ASC;
-- 对于 NULL 值的数据行，升序在开头，降序在末尾。
SELECT * FROM president ORDER BY if(death IS NULL ,0,1),death DESC;
-- IF()：对第一个参数进行求值。如果 death 为 NULL，返回 0，如果 death 不为 NULL，返回 1

-- 4、限制查询结果中的数据行数
SELECT * FROM president ORDER BY brith LIMIT 5; -- 查询结果的前 5 条
SELECT * FROM president ORDER BY brith DESC LIMIT 10,5; -- 查询结果的第 11-15 条。
SELECT * FROM president ORDER BY rand() LIMIT 1; -- 随机取出一条

-- 5、对输出列进行求值和命名
SELECT concat(first_name,' ',last_name) AS 'Name', concat(city,',',state) AS 'Brithplace' FROM president;
-- concat()：合并多列; AS：设置别名

-- 6、日期有关
-- 日期中的年、月、日三部分可以用函数 year()、month()、dayofmonth() 分离出来
SELECT * FROM president WHERE month(brith) = 3;
SELECT * FROM president WHERE timestampdiff(YEAR,brith,death) = 21; -- 查询出 death、brith 相差的年数
-- to_days() 将日期转换为天数
SELECT date_add('2017-1-1',INTERVAL 10 MONTH); -- 返回 2017-11-01
SELECT date_sub('2017-12-20',INTERVAL 10 MONTH); -- 返回 2017-02-20

-- 7、模式匹配
SELECT * FROM president WHERE last_name LIKE 'W%';
SELECT * FROM president WHERE last_name LIKE '____'; -- 查询出姓氏仅由4个字母构成的

-- 8、设置和使用 SQL 变量
-- 变量命名：@变量名 := 值
SELECT @brith := brith FROM president -- 查询出来的结果保存在 brith 变量中，供今后查询
WHERE last_name='Jackson' AND first_name = 'Andrew';
-- 调用变量：@变量名
SELECT * FROM president WHERE brith < @brith ORDER BY brith;
-- set 语句也能用来对变量赋值，此时，'=' 和 ':=' 都可以用做赋值操作符
SET @today = curdate();
SET @one_week_ago := date_sub(@today , INTERVAL 7 DAY);
SELECT @today,@one_week_ago;

-- 9、生成统计信息
-- distinct：清除查询结果中重复出现的数据行
SELECT DISTINCT state FROM president ORDER BY state;
-- count(*) 返回查询结果的行数（非 NULL）
SELECT count(*) FROM member;
SELECT count(*),count(email),count(expiration) FROM member;
SELECT count(DISTINCT state) FROM president;
SELECT sex,count(*) FROM student GROUP BY sex;
SELECT month(brith) AS Month,monthname(brith) AS Name, count(*) AS count
FROM president
GROUP BY Name
ORDER BY Month;
-- 上一条语句不符合 MySQL 对于 ONLY_FULL_GROUP_BY 语义的判断规则，所以需要去除 ONLY_FULL_GROUP_BY 语义规则
SET GLOBAL SQL_MODE='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- 查询全局变量 SQL_MODE 的值
SELECT @@global.SQL_MODE;
-- count() 之类的汇总函数的计算结果允许在 having 子句中出现
SELECT state,count(*) AS count FROM president
GROUP BY state HAVING count > 1 ORDER BY count DESC;
-- 如果想输出"统计结果"，那就增加一条 with rollup 子句
-- 作用：分组统计结果的汇总统计值
SELECT sex,count(*) FROM student GROUP BY sex WITH ROLLUP;

-- 10、多个数据表里检索信息
-- 联结：join && 子查询：select 语句嵌套一个 select 语句
-- ①、联结查询
SELECT st.name,g.data,s.score,g.category
FROM grade_event g INNER JOIN score s INNER JOIN student st
ON g.event_id = s.event_id
AND s.student_id = st.student_id
WHERE g.data = '2008-09-23';
-- 左外联结：left join 输出左边表数据表中的每一条记录
SELECT s.student_id,s.name,count(a.date) AS absences
FROM student s LEFT JOIN absence a
ON s.student_id = a.student_id
GROUP BY s.student_id;
-- ②、子查询：查出全勤的学生
SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence);

删除或更新数据行

DELETE FROM president WHERE state = 'OH';
UPDATE member SET email='123@qq.com',street='123 Elm St',city='Anytown',state='NY',zip='01003'
WHERE last_name = 'York' AND first_name='Jerome';
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,591评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,448评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,823评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,204评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,228评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,190评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,078评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,923评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,334评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,550评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,727评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,428评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,022评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,672评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,826评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,734评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,619评论 2赞 354

推荐阅读更多精彩内容

  • Spring Cloud
    Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具（例如配置管理，服务发现，断路器，智...
    卡卡罗2017阅读 134,652评论 18赞 139
  • Android - 收藏集
    Android 自定义View的各种姿势1 Activity的显示之ViewRootImpl详解 Activity...
    passiontim阅读 172,078评论 25赞 707
  • 《MySQL技术内幕：InnoDB存储引擎(第2版)》书摘
    MySQL技术内幕：InnoDB存储引擎(第2版) 姜承尧 第1章 MySQL体系结构和存储引擎 >> 在上述例子...
    沉默剑士阅读 7,410评论 0赞 16
  • 6/11 日食记
    6/11 霾 早餐 · 宿舍 「Aji苏打饼干」 今天早上起床好晚，看到外面被雾霾染得灰黄色的天，瞬间失去出门的欲...
    正小飞呀呀呀阅读 206评论 0赞 0
  • 杭州冬行记
    记得汪国真在他的诗《远方》中说过：“凡是遥远的地方，对我们来说，都有一种诱惑，不是诱惑于美丽，就是诱惑于传...
    日未央阅读 271评论 0赞 0