乐趣区

关于sql:MySQL基础合集

jiezi


我的小站

1、MySQL 的劣势

  • 运行速度快
  • 应用成本低
  • 可移植性强
  • 实用用户广

2、MySQL 的运行机制

一个 SQL 语句，如 select * from tablename，从反对接口进来后，进入连接池后做权限、验证等环节，而后判断是否有缓存，有则间接放回后果，否则进入 SQL 接口，在查问之前查问优化器进行优化，最初进行解析，查问。并通过存储引擎与文件交互。

3、MySQL 的配置

端口号：3306
默认字符集：utf8
将 bin 目录写入
环境变量
root 明码设置

注：utf8: 一种 Unicode 编码，解决国内上字符不对立的多字节编码，通用性强
my.ini：记录 MySQL 配置信息，可批改配置内容，如端口号，字符集等

4、命令行连贯 MySQL

首先查看是否启动服务

** 属性窗口 **
**DOS 命令：**

​ 启动：net start mysql57

​ 进行：net stop mysql57

57 代表是 5.7 的版本，以下的不需接 57

注：如果批改了配置文件，必须重启 MySQL 服务能力失效
命令行连贯MySQL 语法：mysql –h 服务器主机地址 –u 用户名 –p 明码

l –h 服务器主机地址（如果是本机能够省略这名句）明码可省略，按回车再输出

5、零碎数据库和用户数据库

零碎数据库：
information_schema:次要存储系统中的一些数据库对象信息，如用户表信息、字段信息、权限信息、字符集信息和分区信息等。
performance_schema:次要存储数据库服务器性能参数

mysql:次要存储系统的用户权限信息
test:MySQL 数据库管理系统主动创立的测试数据库，任何用户都能够应用
用户数据库：
用户数据库是用户依据理论需要创立的数据库。本章前面的解说次要针对用户数据库。

6、创立数据库

​ set names utf8;
语法： create database 数据库名
示例：create database myschool;

7、查看数据库列表

语法：show databases;

8、抉择数据库

语法：use 数据库名;
注：应用数据前必须先抉择该数据库！

9、删除数据库

语法：drop database 数据库名；

10、MySQL 数据类型

11、创立表

语法：CREATE TABLE [IF NOT EXISTS] 表名 (

    字段 1 数据类型 [字段属性 | 束缚][索引][正文],
           ……
  字段 n 数据类型 [字段属性 | 束缚][索引][正文]        
         )[表类型][表字符集][正文];

例：

   CREATE TABLE `student`
  (`studentNo` INT(4)  PRIMARY KEY auto_increment not null,
        ` name` CHAR(10),
     ……
  );

12、字段的束缚及属性

名称 关键字 阐明
非空束缚 NOT NULL 字段不容许为空
默认束缚 DEFAULT 赋予某字段默认值
惟一束缚 UNIQUE KEY(UK) 设置字段的值是惟一的 容许为空，但只能有一个空值
主键束缚 PRIMARY KEY(PK) 设置该字段为表的主键 可惟一标识该表记录
外键束缚 FOREIGN KEY(FK) 用于在两表之间建设关系
主动增长 AUTO_INCREMENT 设置该列为自增字段，默认每条自增 1，通常用于设置主键

主键：

 CREATE TABLE student（`studentNo` INT(4)  PRIMARY KEY,
      ……）;

正文：

 CREATE TABLE test (`id` int(11) UNSIGNED COMMENT‘编号’)COMMENT=' 测试表’;

设置字符集编码

 CREATE TABLE [IF NOT EXISTS] 表名（#省略代码）CHARSET = 字符集名;

创立学生示意例：

CREATE TABLE `student`(`studentNo` INT(4) NOT NULL COMMENT '学号' PRIMARY KEY,
    `loginPwd` VARCHAR(20) NOT NULL COMMENT '明码',  
    `studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
    `sex` CHAR(2) DEFAULT '男' NOT NULL  COMMENT '性别', 
    `gradeId` INT(4)  UNSIGNED COMMENT '年级编号', 
    `phone` VARCHAR(50)  COMMENT '联系电话',
    `address` VARCHAR(255)  DEFAULT '地址不详'COMMENT '地址',
    `bornDate` DATETIME  COMMENT '出世工夫',
    `email` VARCHAR(50) COMMENT'邮件账号',
     `identityCard` VARCHAR(18)  UNIQUE KEY COMMENT '身份证号'
) COMMENT='学生表';

13、查看表

查看表是否存在

查看表定义

注：防止 DOS 窗口乱码，可执行 SET NAMES gbk;

14、删除表

语法：DROP TABLE [IF EXISTS] 表名;
示例：

注：在删除表之前，先应用 IF EXISTS 语句验证表是否存在

15、MySQL 的存储引擎

1）存储引擎的类型
MyISAM、InnoDB、Memory、CSV 等 9 种
2）MyISAM 与 InnoDB 类型次要区别

教训：
实用场合

  应用 MyISAM:   不需事务，空间小，以查问拜访为主
  应用 InnoDB:   多删除、更新操作，安全性高，事务处理及并发管制

3）查看以后默认存储引擎

SHOW VARIABLES LIKE‘storage_engine%’;

4）批改存储引擎

批改 my.ini 配置文件 default-storage-engine= InnoDB

5）设置表的存储引擎
语法：CREATE TABLE 表名(

# 省略代码

)ENGINE= 存储引擎;

16、数据表的存储地位

1）MyISAM 类型表文件

*.frm：表构造定义文件
*.MYD：数据文件
*.MYI：索引文件

2）InnoDB 类型表文件

*.frm：表构造定义文件
ibdata1 文件

留神：存储地位 因操作系统而异，可查 my.ini

datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
innodb_data_home_dir=”D:/MySQL Datafiles/”

17、MySQL 零碎帮忙

HELP 查问内容;
HELP contents;
HELP Data Types;
HELP INT;

补充：mysql 数值类型 中带符号的 / 无带符号的 区别

带符号和无符号, 顾名思义, 就是是否有正负之分:
比方 8 为二进制, 如果带符号, 须要用 1 位示意符号(1 示意正数,0 示意正), 剩下 7 位示意数据.
那么示意范畴是 -127—127(包含 - 0 和 +0).
如果不带符号,8 位全副示意数据,
那么示意范畴是 0–256

补充：mysql 有没有 nvarchar nchar

​ 有，然而，mysql 采纳 utf- 8 编码, 而传统的数据库采纳 unicode, 一个汉字要用两个 unicode 的 char, 而在 mysql 中因为应用了 utf-8, 所以无论汉字还是字母, 都是一个长度的 char, 所以就不必分 nvarhcar 和 varchar 了, 一律作 varchar

SQL 高级（一）

1、批改表

1）# 批改表名

ALTER TABLE 旧表名 RENAME  新表名;

2）# 增加字段

ALTER TABLE 表名 ADD 字段名  数据类型  [属性];

3）# 批改字段

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

4）# 删除字段

ALTER TABLE 表名 DROP 字段名;

2、增加主键

语法：
ALTER TABLE 表名 ADD CONSTRAINT 主键名
PRIMARY KEY 表名（主键字段）;

例：设置 grade 表中 gradeId 字段为主键

 alter table `grade` add constraint `pk_grade` primary key `grade`(`gradeId`);

3、增加外键

语法：

 ALTER TABLE 表名 ADD CONSTRAINT 外键名  FOREIGN KEY（外键字段）

REFERENCES 关联表名（关联字段）;

例：将 student 表的 gradeId 字段和 grade 表的 gradeId 字段建设外键关联

alter table `student`  add  constraint fk_student_grade foreign key(`gradeId`) 
references `grade` (`gradeId`);

4、DML 语句 – 插入单条数据记录

语法：INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

留神：

  字段名是可选的，如省略则顺次插入所有字段
  多个列表和多个值之间应用逗号分隔
  值列表和字段名列表一一对应
  如插入的是表中局部数据，字段名列表必填

示例：

INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)
VALUES('123','黄小平',1,'13956799999','1996-5-8');

5、DML 语句 – 插入多条数据记录

语法：INSERT INTO 新表（字段名列表）VALUES(值列表 1),(值列表 2),……,(值列表 n);
示例：

 INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

教训：

  为防止表构造发生变化引发的谬误，倡议插入数据时写明具体字段名！

6、DML 语句——将查问后果插入新表

CREATE TABLE `phoneList`(
    SELECT `studentName`,`phone`
    FROM `student`);

注：如新表已存在，将会报错！

7、DML 语句——数据更新、删除

1）更新数据记录

update 表名 set 字段 1 = 值 1, 字段 2 = 值 2,..., 字段 n = 值 n [where 条件];
2)删除数据记录

DELETE FROM 表名 [WHERE 条件];
 TRUNCATE TABLE 表名;
TRUNCATE 语句删除后将重置自增列，表构造及其字段、束缚、索引放弃不变，执行速度比 DELETE 语句快

8、DQL 语句

语法：

SELECT < 字段名列表 >
FROM < 表名或视图 >
[WHERE < 查问条件 >]
[GROUP BY < 分组的字段名 >]
[HAVING < 条件 >]
[ORDER BY < 排序的字段名 > [ASC 或 DESC]]
示例：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo;

9、LIMIT 子句

MySQL 查问语句中应用 LIMIT 子句限度后果集
语法 ：
SELECT < 字段名列表 >
FROM < 表名或视图 >
[WHERE < 查问条件 >]
[GROUP BY < 分组的字段名 >]
[ORDER BY < 排序的列名 > [ASC 或 DESC]]
[LIMIT [地位偏移量,] 行数];

示例：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo
 LIMIT 4,4; #(从第 5 条开始显示 4 条)

注：应用 LIMIT 子句时，留神第 1 条记录的地位是 0！

10、罕用函数——聚合函数

AVG()返回某字段的平均值

COUNT() 返回某字段的行数

MAX()返回某字段的最大值

MIN() 返回某字段的最小值

SUM() 返回某字段的和

11、罕用函数——字符串函数

CONCAT(str1,str1…strn) 字符串连贯 SELECT CONCAT(‘My’,’S’,’QL’); 返回：MySQL
INSERT(str,pos,len,newstr) 字符串替换 SELECT INSERT(‘ 这是 SQL Server 数据库 ’,3,10,’MySQL’); 返回：这是 MySQL 数据库
LOWER(str) 将字符串转为小写 SELECT LOWER(‘MySQL’); 返回：mysql
UPPER(str) 将字符串转为大写 SELECT UPPER(‘MySQL’); 返回：MYSQL
SUBSTRING(str,num,len) 字符串截取 SELECT SUBSTRING(‘JavaMySQLOracle’,5,5); 返回：MySQL

12、罕用函数——工夫日期函数

CURDATE() 获取以后日期 SELECT CURDATE(); 返回：2016-08-08
CURTIME() 获取以后工夫 SELECT CURTIME(); 返回：19:19:26
NOW() 获取以后日期和工夫 SELECT NOW(); 返回：2016-08-08 19:19:26
WEEK(date) 返回日期 date 为一年中的第几周 SELECT WEEK(NOW());
YEAR(date) 返回日期 date 的年份 SELECT YEAR(NOW());
HOUR(time) 返回工夫 time 的小时值 SELECT HOUR(NOW());
DATEDIFF(date1,date2) 返回日期参数 date1 和 date2 之间相隔的天数 SELECT DATEDIFF(NOW(),’2008-8-8′); 返回：2881

13、罕用函数——数学函数

CEIL(x)返回大于或等于数值 x 的最小整数 SELECT CEIL(2.3) 返回：3
FLOOR(x) 返回小于或等于数值 x 的最大整数 SELECT FLOOR(2.3) 返回：2
RAND() 返回 0~1 间的随机数 SELECT RAND() 返回：0.5525468583708134

14、子查问

1）子查问是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其余子查问中的查问
2）子查问在 WHERE 语句中的个别用法
语法：SELECT … FROM 表 1 WHERE 字段 1 比拟运算符 (子查问)
注：将子查问和比拟运算符联结应用，必须保障子查问返回的值不能多于一个
示例：

SELECT `studentNo`,`studentName`,`sex`,`bornDate`,`address`
 FROM `student` 
 WHERE `bornDate` > (SELECT `bornDate` FROM `student` WHERE `studentName`='李斯文');

15、应用子查问替换表连贯

1）实现办法一：采纳表连贯

SELECT `studentName` FROM `student` stu 
 INNER JOIN `result` r ON stu.studentNO = r.studentNo         
 INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo     
WHERE `studentResult` = 60 AND `subjectName` = 'Logic Java';

2）实现办法二：采纳子查问

SELECT `studentName` FROM `student` WHERE `studentNo` = (
SELECT `studentNo` FROM `result` 
INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo 
 WHERE `studentResult`=60 AND `subjectName`='Logic Java'
);

子查问比拟灵便、不便，常作为增删改查的筛选条件，适宜于操纵一个表的数据
表连贯更适宜于查看多表的数据

16、IN 子查问

SELECT `studentName` FROM `student` 
WHERE `studentNo` IN(
    SELECT `studentNo` FROM `result` 
    WHERE `subjectNo` =  (
        SELECT `subjectNo` FROM `subject`
        WHERE `subjectName`='Logic Java'
    )AND `studentResult` = 60  
);

罕用 IN 替换等于（=）的子查问
IN 前面的子查问能够返回多条记录

注：非原创！

退出移动版