我的小站

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后面的子查询可以返回多条记录

注:非原创!

本内容为合法授权发布,文章内容为作者独立观点,不代表开发云立场,未经允许不得转载。

CSDN开发云