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

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 用来创建数据表的存储引擎的名字，
-- 默认是使用 InnoDB

-- 查看 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';