Jimmy那些事儿

MySQL梳理_基本语法

发表 | 更新 | 分类 ， ， | 阅读次数

1. 数据库

1.1 创建

1
2
3
4
5
6
CREATE DATABASE gregs_list;
USE gregs_list;
CREATE TABLE my_contact;
CREATE VIEW view_c;

1.2 删除

1
2
3
DROP DATABASE gregs_list;
DROP TABLE my_contacts;
DROP VIEW view_c

1.3 展示

1
2
3
SHOW DATABASE;  # 显示所有数据库
SHOW TABLES;  # 显示当前数据库所有的表
SHOW TABLES FROM geregs_list;  # 显示该数据库中的所有表

1.4 创建过程

1
SHOW CREATE TALBE my_contacts;

展示表的结构

DESC my_contacts


2. 表的基本操作

2.1 创建

2.1.1 直接创建

1
2
3
4
5
6
7
8
9
10
CREATE TABLE my_contacts(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY  # 非NULL,自动填充数字，并设为主键
  name VARCHAR(10),  # VARCHAR 可变动字符串，长度最大为10，若有abc，则只占用3个字符
  sex CHAR(2)，  # CHAR,固定字符串，长度为2；若只有a，则另一个用空格来表示
  age INT,
  birthday DATA,  # DATA,日期
  pirce1 DEC(3,2),  # Decimal数字，数字共3位，其中2为位是小数点后的
  price2 DEC(3,2) DEFAULT 1.00,  # DEFAULT 默认的格式
  interests BOLG,  # BOLG 大文本
);

其他文本属性

1
2
3
4
5
6
7
8
其他
PRIMARY KEY (id)  # 创建主键
UNIQUE  # 唯一值
时间格式
TIME  # 时间
DATETIME  # 日期与时间
TIMESTAMP  # 当下时刻

2.1.2 AS SELECT 创建

1
2
CREATE TABLE table AS 
  SELECT col_name FROM table GROUP BY col_name ORDER BY col_name;  # 直接复制原表单的格式


2.2 新增

2.2.1 值

INSERT INTO 插入

1
2
3
4
5
6
INSERT INTO my_contacts
(name,sex)
VALUES
("小明","男"),  # 用逗号分隔
("小红","女"), 
();

2.2.2 列(值)

ADD COLUMN

1
ALTER TABLE my_contacts ADD COLUMN name VARCHAR(30);  # 新增列

UPDATE…SET… 直接新增

1
UPDATE TABLE my_contacts SET interset1 = LEFT(interset,4);

SELECT 多表返回

1
SELECT mc.*, projcet.color FROM mc CROSS JOIN projcet;  # 返回原表格所有的值 + 其他表单中的列值

1
SELECT mc.*, '100' AS c FROM mc;  # 插入固定值为100的列，并命名为c；


2.3 删除

2.3.1 删除行

1
2
3
DELETE FROM my_contacts;  # 删除所有行
DELETE FROM my_contacts WHERE age = 30 AND/OR sex="男";  # 删除特定行

2.3.2 删除列

1
ALTER TABLE my_contacts DROP COLUMN name;


2.4 格式

2.4.1 结构展示

1
DESC my_contacts;

2.4.2 改变表的名称

1
ALTER TABLE my_contacts RENAME TO new;

2.4.3 改变列的格式

CHANGE 改变列的 名称、格式

1
ALTER TALBE my_contacts CHANGE name new_name VARCHAR(30);  # CHANGE 改变列的 名称、格式

MODIFY 改变列的 格式、位置

1
2
ALTER TALBE my_contacts MODIFY name VARCHAR(30) BEFORE id;  # MODIFY 改变列的 格式、位置
  位置属性可为 BEFOR/AFTER/FIRST/SECOND


2.5 替换

UPDATE…SET

1
2
UPDATE my_contacts SET price = 3
  WHERE projcet = 'ball'  AND/OR sex = '男';

UPDATE…SET…CASE WHEN…THEN…

1
2
3
4
5
6
UPDATE my_contacts SET price = 
  CASE 
    WHEN projcet ='ball' THEN 3
    WHEN project = 'sing' THEN 5
  ELSE '0'
END;


3. 查询

3.1 单元格属性

3.1.1 行数-count()

1
SELECT COUNT(name) FROM my_contacts;

3.1.2 字符串长度-length()

1
SELECT LENGTH(name) FROM my_contacts;


3.2 单元格的值

3.2.1 左部分 -left()

1
SELECT LEFT(name,3) FROM my_contacts;

3.2.2 右部分-right()/substr()

1
2
SELECT SUBSTR(name,3) FROM my_contacts;  
# SBUSTR(strings,length_num) 返回移除前3个长度的值之后，余下右侧的值

3.2.3 指定位置的值-substring()

1
2
SELECT SUBSTRING(name,1,2) FROM my_contacts;  
# SUBSTRING(strings,start_num,length_num) 返回从指位置1开始，长度2的值

3.2.4 特定符号之前的值-substring_index()

1
2
SELECT SUBSTRING_INDEX(name,'x',2) FROM my_contacts;  
# SUBSTRING_INDEX(string,'x',num) 返回单元格中x第二次出现之前的值

3.2.5 去除空格的值-trim()

1
SELECT TRIM(name) FROM my_contacts;  # TRIM 去除两边的空格； LTRIM/RTRIM 去除左侧/右侧的空格


示例1：提取 Kadet(102)AD, Cide(31)DF 两侧的值

1
2
3
UPDATE my_contacts SET 
side = SUBSTRING_INDEX(name,"(",1)  # 返回第一个左括号（之前的值
city = SUBSTR(name,LENGTH(SUBSTRING_INDEX(name,")",1)+1)  #返回第一个右括号）之前的长度，加上右括号的长度

示例2：分列，提取同一列football, sing, reading 中的值

1
2
3
4
5
UPDATE my_contacts SET
  interest1 = SUBSTRING_INDEX(interset, ",", 1)  # 返回第一个逗号之前的值
  interest = SUBSTR(interest, LENGTH(interest1)+1)  # 移除第一个兴趣的字符串 + 逗号 长度的值
  interset2 = SUBSTRING_INDEX(interest,",", 1)  
  interest3 = SUBSTR(interest, LENGHT(interest2)+1);


3.3 特定条件的值

3.3.1 特定范围(含日期)

1
2
3
4
5
6
SELECT name FROM my_contacts
  WHERE name >= "L" AND name <"N" ;  # 名字开头为L-M的人
---
SELECT name FROM my_contacts
  WHERE BETWEEN "L" AND "M";  # 大于等于L，小于等于M

3.3.2 通配符-like

1
2
SELECT name FROM my_contacts
  WHERE [NOT] name LIKE "A%" / "_A%";  # 返回名字以A为开头字母的人/ 第二个字母为A的人

3.3.3 相等&不相等的值

小范围

1
2
SELECT name FROM my_contacts
  WHERE name [NOT] IN LIKE ("A%","B%","C%")

两个表之间

查询选修了c02号课程的学生的姓名和所在系，学生表(student)，课程表(sc)

1
2
3
# 子查询
SELECT Sname, Sdept FROM student
  WHERE Sno IN (SELECT Sno FROM sc WHERE Cno = 'C02');  # 可在IN之前加NOT；即 WHERE sno NOT IN ...
1
2
3
4
# 联接
SELECT st.Sname, st.Sdept FROM student AS st
  INNER JOIN sc ON st.Sno = sc.Sno
  WHERE sc.Cno = 'C02';

两个表之间 - 不相等：not exists

查询所有未讲课的教师的Tname和Depart

1
2
SELECT TNAME,DEPART FROM teacher   # 一种常用于外层查询结果不存在于关联表里的方法
  WHERE NOT EXISTS (SELECT * FROM course WHERE teacher.TNO = course.TNO);


4. 查询：表格相关

4.1 分组、排序

分组 GRUOP BY

排序 ORDER BY name, degree DESC, SUM(age) DESC # 默认为升序排序（ASC）

1
2
GROUP BY name
ORDER BY name

where 用于分组之前

having 用于分组之后

4.2 显示数量-limit()

LIMIT

  • LIMIT n1 # 返回前n1行，并且MySQL是从0开始的

  • LIMIT n1,n2 # 返回从第n1+1行(MySQL从0开始)，之后的n2行

  • LIMIT n1,n1 # 返回特定行的值

Limit结合排序Order by 往往有不错的效果


5. 数字处理

5.1常规统计函数

  • rand() [0,1]之间的随机值,
  • round(x,[y]) 取整；y为小数位数，y可以为负数, floor(x) 向下取整，小于x的最大整数, ceiling(x) 向上取整
  • truncate(x,[y]),截断至y指定的小数位数后的值
  • mod(x,y) x/y的余数
  • sum(), avg(), sqrt()平方根, exp()指数, ln(x) / log(x,y) 对数,
  • power(x,y) x的y次方, abs() 绝对值, pi() π


6. 数据清洗

6.1 缺失值

删除缺失值

1
2
DELETE FROM my_contacts 
  WHERE name IS NULL OR sex IS NULL;

6.2 重复值/唯一值

返回唯一值：distinct()

1
SELECT DISTINCT(name) FROM my_contacts;


7. 日期

SELECT NOW(); 返回当前日期与时间

SELECT MONTH(NOW()); 返回当前月份

SELECT YEAR(NOW()); 返回当前年份

计算年龄

1
SELECT YEAR(NOW())-YEAR(birthday) AS age FROM my_contacts;


8. 账户与安全