MySql 应用语句

【1】MySQL基础语句

  1 -- 查询mysql版本号
  2 SELECT VERSION();
  3 
  4 -- 创建数据库
  5 DROP DATABASE IF EXISTS study; -- 如果存在先删除
  6 CREATE DATABASE study;
  7 
  8 -- 切换至数据库study
  9 USE study;
 10 
 11 -- 创建表
 12 CREATE TABLE students(
 13    sId INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 14    sName VARCHAR(100) NOT NULL,
 15    sAge INT(10) NOT NULL,
 16    sAddr VARCHAR(200),
 17    sGrade INT(10),
 18    sStuId VARCHAR(20),
 19    PRIMARY KEY (sId)
 20 );
 21 
 22 -- 插入数据
 23 INSERT INTO students(sId, sName, sAge, sAddr, sGrade, sStuId)
 24 VALUES 
 25 (1,  'liuyang',      16, 'weinan',     5, '180503311'),
 26 (2,  'zhaosi',       16, 'shenyang',   5, '180503312'),
 27 (3,  'qinchuan',     16, 'hancheng',   5, '180503323'),
 28 (4,  'liyang',       16, 'tongchuan',  5, '180504324'),
 29 (5,  'lihongbin',    17, 'sanqiao',    6, '180604335'),
 30 (6,  'liuyan',       17, 'pucheng',    6, '180604336'),
 31 (7,  'huangzhuoyue', 17, 'heyang',     6, '180604347'),
 32 (8,  'weiyijun',     17, 'dali',       6, '180605348'),
 33 (9,  'houshuyue',    18, 'chengcheng', 7, '180605359'),
 34 (10, 'sunqi',        18, 'xian',       7, '180605351'),
 35 (11, 'leiyang',      18, 'weinan',     7, '180505362'),
 36 (12, 'zhaosi',       18, 'shenyang',   7, '180507363'),
 37 (13, 'xuchuan',      19, 'hancheng',   8, '180507374'),
 38 (14, 'baiyang',      19, 'tongchuan',  8, '180507375'),
 39 (15, 'wanghongbin',  19, 'sanqiao',    8, '180607386'),
 40 (16, 'huyan',        19, 'pucheng',    8, '180608387'),
 41 (17, 'lizhuoyue',    20, 'heyang',     9, '180608398'),
 42 (18, 'haiyijun',     20, 'dali',       9, '180608399'),
 43 (19, 'liushuyue',    20, 'chengcheng', 9, '180608301'),
 44 (20, 'wangqi',       20, 'baishui',    9, '180609301');
 45 
 46 -- 查询表中数据
 47 SELECT * FROM students;
 48 
 49 -- 查询表的结构
 50 DESC students;
 51 
 52 -- 根据已有的表创建新表
 53 CREATE TABLE students_new LIKE students; # 注意仅仅只是创建表，不牵涉数据
 54 
 55 -- 删除表
 56 DROP TABLE students_new;
 57 
 58 -- 表中增加一列
 59 ALTER TABLE students ADD COLUMN sex INT UNSIGNED;
 60 
 61 -- 添加唯一性索引
 62 ALTER TABLE students ADD UNIQUE INDEX name_Age_Index(sName, sAge);
 63 
 64 -- 检验唯一性索引作用
 65 REPLACE INTO students(sId, sName, sAge, sAddr, sGrade, sStuId)
 66 VALUES (21, 'wangqi', 20, 'beijing', 8, '180608321')
 67 
 68 -- 删除一条数据
 69 DELETE FROM students WHERE sName = 'wangqi' AND sStuId = '180608321';
 70 
 71 -- 清空全部数据，不写日志，不可恢复，速度极快
 72 TRUNCATE TABLE students;
 73  
 74 -- 清空全部数据，写日志，数据可恢复，速度慢
 75 DELETE FROM students;
 76 
 77 -- 查询数据库连接
 78 SHOW FULL PROCESSLIST;
 79 
 80 -- 查询表的创建语句
 81 SHOW CREATE TABLE dat_bill_201811;
 82 
 83 -- 选择
 84 SELECT * FROM students WHERE sGrade = 8;
 85 
 86 -- 删除
 87 DELETE FROM students WHERE sGrade = 8;
 88 
 89 -- 插入
 90 INSERT INTO students(sId, sName, sAge, sAddr, sGrade, sStuId, sex)
 91 VALUES 
 92 (13, 'xuchuan',      19, 'hancheng',   8, '180507374', 1),
 93 (14, 'baiyang',      19, 'tongchuan',  8, '180507375', 0),
 94 (15, 'wanghongbin',  19, 'sanqiao',    8, '180607386', 1),
 95 (16, 'huyan',        19, 'pucheng',    8, '180608387', 1),
 96 (21, 'wangqi',       18, 'myself',     8, '180608321', 0);
 97 
 98 -- 更新
 99 UPDATE students SET sGrade = 9 WHERE sName = 'wangqi';
100 
101 -- 查找
102 SELECT * FROM students WHERE sStuId LIKE '%050%';
103 
104 -- 排序
105 SELECT * FROM students ORDER BY sGrade;
106 
107 -- 总数
108 SELECT COUNT(sGrade) AS totalCount FROM students WHERE sGrade = 5;  
109 
110 -- 求和
111 SELECT SUM(sAge) AS sumAge FROM students;
112 
113 -- 平均
114 SELECT AVG(sAge) AS avgAge FROM students;
115 
116 -- 最大
117 SELECT MAX(sAge) AS maxAge FROM students;
118 
119 -- 最小
120 SELECT MIN(sAge) AS minAge FROM students;

【2】where in 语法

1 SELECT * FROM view_dat_sdr_201811 WHERE product_id = '110' OR product_id = '120' OR product_id = '119';
2 SELECT * FROM view_dat_sdr_201811 WHERE product_id IN('110', '120', '119');

功能一致，注意两种语法的区别。

【3】字段

【4】待续......

Good Good Study, Day Day Up.

顺序选择循环总结