MySql安装配置、DDL DML DCL语法解析及函数扩展

MySql

---

安装

​ 双击即可,大部分都是一路next

​ 关键节点注意事项:

1. 选中Typical,典型安装模式

2. 选中Detailed Configuration（详细配置）单选按钮

3. 选中Developer Machine（开发者机器）单选按钮

4. 选中Multifunctional Database（多功能数据库）单选按钮

5. 服务端口3306，不建议更改

6. 字符集注意要选Manual Selected Default Character Set/Collation单选按钮，设置字符集编码为utf8

7. 设置密码尽量简单，注意不要忘了，我们只是来做实验

   用户名：root 密码：root

启动MySql服务

• “我的电脑”→“管理”→“服务”在服务器的列表中找到mysql服务并右键单击，在弹出的快捷菜单中，完成MySQL服务的各种操作（启动、重新启动、停止、暂停和恢复）

• 也可以通过命令行的方式来管理

  Window键+R

  net start mysql(mysql服务名)
  net stop mysql
  

命令行连接

mysql -u usernname -p password

SQL

DDL

数据定义语言(DDL ，Data Defintion Language)语句：数据定义语句，用于定义不同的数据对象、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等。

• 数据库操作

  create database dbNaem;--创建数据库
  	show datebases;--显示所有数据库
  	use dbName;--使用数据库
  	show tables;--显示数据库中所有表
  

• 表操作

  create table tName(
      fName fieldtype [COMMENT 'note'],
      ...
      );--创建数据表
  	--字段类型:
  	INT:整型,
  	float:浮点,
  	字符串:char(32) -最多只能存储32个个字符,没有存满也占32个字符空间
  		  varchar(32) -最多存储32个字符,没有存满只占实际存储的字符空间
  		注意:	两种字符串存储没有设置指定存储大小时,最多存储255字符
  	datetime,timestamp:时间
  
  drop table tName;--删除表
  
  alter table old_tName rename new_tName;--修改表名
  
  --扩展
  CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;
  CREATE TABLE 新表 LIKE 旧表 ;--只复制表结构
  --注意
  上面两种方式，前一种方式是不会复制时的主键类型和自增方式是不会复制过去的，而后一种方式是把旧表的所有字段类型都复制到新表。
  
  CREATE TABLE 新表 SELECT * FROM 旧表;--复制表结构以及数据
  
  INSERT INTO 新表 SELECT * FROM 旧表;--复制表数据(表结构相同)
  
  INSERT INTO 新表(字段1,字段2,...) SELECT 字段1,字段2,... FROM 旧表;--复制表数据(表结构不相同)
  

• 数据字段操作

  alter table tName modify fName fieldtype;--修改字段类型 
  
  alter table tName add column fName fieldtype;--添加字段
  
  alter table tName change column old_fName new_fName fieldtype;--修改字段名
  

DML

数据操作语言(DML ， Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和select等。

（DML:添加，修改，删除，DQL:查询)

• 插入（INSERT）

  insert into tName values(value,...);
  insert into tName(fName,...) values(value,...); --插入数据
  
  --两种语法的区别：
  式一中,表中有多少个字段就必须要插入多少个值。若有默认值，不想传，可以写上null。
  式二中,除非有必填字段必须要写入值外。如果有默认值的不想写可以忽略不写。mysql会自动补主默认值
  
  --扩展
  INSERT INTO 新表 SELECT * FROM 旧表;--复制表数据(表结构相同)
  INSERT INTO 新表(字段1,字段2,...) SELECT 字段1,字段2,... FROM 旧表;--复制表数据(表结构不相同)
  

• 查询（SELECT)

  select [distinct][method(params)] *|(fName,...) from tName;
  --注:如果是distinct 多列 是以多列一起判断是否重复
  
  select fName from tName where condition and condition or condition;
  
  select fName from tName where fName like '%content%';--模糊查询
  
  select fName from tName order by fName,... asc|desc;--关键词asc顺序查询,默认.desc倒序查询
  
  --多字段排序
  order by 后面可以跟多个不同的字段排序，并且排序字段的不同结果集的顺序也不同，如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序。
  --注意：
  只有在第一个字段排序后，出现相同值才会比较第二个字段，否则第二个比较字段无效，主排序字段和次排序字段
  
  select fName from tName limit [offset,]size;--分页查询,取前size个数据
  
  select fName from tName order by fName asc|desc limit size;--限制结果集并排序
  
  --扩展
  select * from tName order by rand() limit size;--随机取数据
  

  • 子查询

  • 分组查询

    select fName,... from tName group by fName having(conditon|method(params));
    --注:如果是分组 多列 是以多列一起判断是否相同分组
    

  • 多表联合查询

    表连接

    当需要查询多个表中的字段时，就可以使用表连接来实现。表联接分为内连接和外连接。

    内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

    

    外连接：会选出其他不匹配的记录，分为外左联结和外右联结。

    

    

    --内连接
    select tName1.fName,tName2.fName from tName1,tName2  where condition;
    select tName1.fName,tName2.fName from tName1 [INNER] JOIN tName2 on condition;
    
    --外连接
    select 表1.字段 [as 别名],表n.字段 from 表1 LEFT JOIN 表n on 条件;
    select 表1.字段 [as 别名],表n.字段 from 表1 right JOIN 表n on 条件;
    
    左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
    右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录
    

  • UNION操作符

    MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

    --语法
    SELECT expression1, expression2, ... expression_n
    FROM tables
    [WHERE conditions]
    UNION [ALL | DISTINCT]--UNION ALL去重
    SELECT expression1, expression2, ... expression_n
    FROM tables
    [WHERE conditions];
    

  • 聚合函数（统计类函数）：

    函数	说明
    sum	求和
    count	统计总数
    max	最大值
    min	最小值
    avg	平均值

• 更新(UPDATE)

  update tName set fName = value,...;--更新一个或多个字段值
  

• 删除(DELETE)

  delete from tName [where condition]
  
  1. drop table tName
  2. truncate table tName 
  --三者区别(加上delete三个,这是考点需要掌握):
  	drop:删除表所有数据与表的数据结构，也就是表之间不存在了(无法回滚)
  	truncate: 会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表(一旦提交不可回滚)
  当然，涉及到删除数据delete语句也是删除数据，这儿就一起说了
  	delete: 逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据
  	
  --总结:
  速度:一般来说: drop> truncate > delete
  安全性:小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及
  使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.
  想删除表,当然用 drop；想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,用delete
  

DCL

数据控制语言(DCL， Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

• 添加权限

  首先，需创建用户:

  create user username identified by password;
  --可以针对一个用户增加多条权限。
  

  类别	详细解示
  基本语法	grant 权限 on dbName.tName to ‘username’@‘host’ identified by ‘password’;
  示例	grant select, insert on test.* to ‘huangguizhao’@‘localhost’ identified by ‘4311’;
  示例说明	给予huangguizhao用户，在本机连接test库所有表的权限。操作的这些表具有查询和写入权限

• 删除权限

  类别	详细解示
  基本语法	revoke 权限 on dbName.tName from ‘username’@‘host’;
  示例	revoke select, insert on test.* from ‘huangguizhao’@‘localhost’;
  示例说明	给予huangguizhao用户，在本机连接test库所有表的权限。操作的这些表具有查询和写入权限

  参数说明

  符号	说明
  grant all	在grant后接all说明给予所有权限
  revoke all	在revoke后接all说明删除所有权限
  权限 on .	. 所明给予所有库所有表的操作权限
  ‘用户’@‘主机’	主机里面若为%。任意来源的主机均可以使用这个用户来访问

  解决编码问题：

  Set charset gbk；

数据库备份和恢复

• --备份：
  在未登录状态
  mysqldump -u root -p  huangguizhao>d:\hgz.sql
  密码过后输入
  mysqldump -u root -proot  huangguizhao>d:\hgz.sql
  
  --恢复：
  前提：必须先创建数据库
  需先登录mySQL,进入指定数据库后执行SQL
  Source d:\hgz.sql
  
  还有一种方式
  mysql -uroot -proot huangguizhao<d:\hgz.sql
  

扩展

数据类型

• 整型

  MySQL数据类型	所占字节	值范围
  tinyint	1字节	-128~127
  smallint	2字节	-32768~32767
  mediumint	3字节	-8388608~8388607
  int	4字节	范围-2147483648~2147483647
  bigint	8字节	±9.22*10的18次方

  UNSIGNED（无符号） 主要用于整型和浮点类型，使用无符号。即，没有前面面的-（负号）。
  存储位数更长。tinyint整型的取值区间为，-128~127。而使用无符号后可存储0-255个长度。

  创建时在整型或浮点字段语句后接上：Unsigned

• 浮点类型

  MySQL数据类型	所占字节	值范围
  float(m, d)	4字节	单精度浮点型，m总个数，d小数位
  double(m, d)	8字节	双精度浮点型，m总个数，d小数位
  decimal(m, d)		decimal是存储为字符串的浮点数

• 字符类型

  MySQL数据类型	所占字节	值范围
  CHAR	0-255字节	定长字符串
  VARCHAR	0-255字节	变长字符串
  TINYBLOB	0-255字节	不超过255个字符的二进制字符串
  TINYTEXT	0-255字节	短文本字符串
  BLOB	0-65535字节	二进制形式的长文本数据
  TEXT	0-65535字节	长文本数据
  MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
  MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
  LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
  LONGTEXT	0-4 294 967 295字节	极大文本数据
  VARBINARY(M)	允许长度0-M个字节的定长字节符串	值的长度+1个字节
  BINARY(M)	M	允许长度0-M个字节的定长字节符串

• 时间类型

  MySQL数据类型	所占字节	值范围
  date	3字节	日期，格式：2014-09-18
  time	3字节	时间，格式：08:42:30
  datetime	8字节	日期时间，格式：2014-09-18 08:42:30
  timestamp	4字节	自动存储记录修改的时间 格式: 2014-09-18 08:42:30
  year	1字节	年份 格式: 2014

  • DATETIME

    类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值，支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。（“支持”意味着尽管更早的值可能工作，但不能保证他们可以。）

  • DATE

    类型用在你仅需要日期值时，没有时间部分。MySQL检索并且以’YYYY-MM-DD’格式显示DATE值，支持的范围是’1000-01-01’到’9999-12-31’。

  • TIMESTAMP

    可以表示年月日时分秒，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。

  • TIME

    数据类型表示一天中的时间。MySQL检索并且以"HH:MM:SS"格式显示TIME值。支持的范围是’00:00:00’到’23:59:59’。

  --系统时间
  select NOW();--语句开始执行的时间
  select SYSDATE();--动态的实时时间
  
  --时间操作函数
  1.DATE_ADD(date,INTERVAL expr unit)
  select date_add(now(), interval 1 day); -- 当前时间add 1 day
  select date_add(now(), interval 1 hour); -- add 1 hour
  select date_add(now(), interval 1 minute); -- ...
  select date_add(now(), interval 1 second);
  select date_add(now(), interval 1 microsecond);
  select date_add(now(), interval 1 week);
  select date_add(now(), interval 1 month);
  select date_add(now(), interval 1 quarter);
  select date_add(now(), interval 1 year);
  select date_add(now(), interval -1 day); --减掉一天
  
  2.TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);-- date2-date1
  select timestampdiff(day,'2011-11-11','2012-12-12');
  select timestampdiff(hour,'2011-11-11 01:12:00','2012-12-12 00:12:00');
  
  3.TIMESTAMP();
  select TIMESTAMP('2008-08-08 08:00:08','01:01:01');--前面的时间加上后面的时间
  

  注意：

  1. 时间类型在web系统中用的比较少，很多时候很多人喜欢使用int来存储时间。插入时插入的是unix时间戳，因为这种方式更方便计算。在前端业务中用date类型的函数，再将unix时间戳转成人们可识别的时间。

  2. 上面的类型你可以根据实际情况实际进行选择,有些人为了在数据库管理中方便查看，也有人使用datetime

约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

• 主键约束(PIMARY KEY)

  主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

  每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。

  当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

  - 基本模式
  create table temp( 
  id int primary key,
  name varchar(20)
  );
  
  -- 组合模式
  create table temp(
  id int ,
  name varchar(20),
  pwd varchar(20),
  primary key(id, name)
  );
  
  -- 删除主键约束
  alter table temp drop primary key;
  
  -- 添加主键约束
  alter table temp add primary key(id,name);
  
  -- 修改主键约束
  alter  table temp modify id int primary key；
  
  
  

• 外键约束(FOREIGN KEY)

  外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

  -- 基本模式
  -- 主表
  create table temp(
  id int primary key,
  name varchar(20)
  );
  
  -- 副表
  create table temp2(
  id int,
  name varchar(20),
  classes_id int,
  foreign key(id) references temp(id)
  );
  
  
  -- 多列外键组合，必须用表级别约束语法
  -- 主表
  create table classes(
  id int,
  name varchar(20),
  number int,
  primary key(name,number)
  );
  
  -- 副表
  create table student(
  id int auto_increment primary key,
  name varchar(20),
  classes_name varchar(20),
  classes_number int,
  /*表级别联合外键*/
  foreign key(classes_name, classes_number) references classes(name, number) 
  );
  
  -- 删除外键约束
  alter table student drop foreign key student_id;
  
  -- 增加外键约束
  alter table student add foreign key(classes_name, classes_number) references classes(name, number);
  
  

• 唯一约束(UNIQUE)

  • 唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
  • 唯一约束不允许出现重复的值，但是可以为多个null。
  • 同一个表可以有多个唯一约束，多个列组合的约束。
  • 在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
  • 唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

  -- 创建表时设置，表示用户名、密码不能重复
      create table temp(
      id int not null ,
      name varchar(20),
      password varchar(10),
      unique(name,password)
  );
  
  -- 添加唯一约束
  alter table temp add unique (name, password);
  
  -- 修改唯一约束
  alter table temp modify name varchar(25) unique;
  
  -- 删除约束
  alter table temp drop index name;
  

• 非空约束和默认值Default

  非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
  Null类型特征：
  　　所有的类型的值都可以是null，包括int、float 等数据类型

  -- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
  create table temp(
             id int not null,
             name varchar(255) not null default  'abc',
             sex char null
  )；
  -- 增加非空约束
  alter table temp
  modify sex varchar(2) not null;
  
  -- 取消非空约束
  alter table temp modify sex varchar(2) null;
  
  -- 取消非空约束，增加默认值
  alter table temp modify sex varchar(2) default 'abc' null;
  
  

case when then else end

--语法
case :指定字段
when:当什么条件
then:满足上面的条件 就干什么
else:前面的条件都不满足
end:结束语

SELECT  SUM(population) '人口', 
        CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
        ELSE '其他' END '洲'
FROM    Table_A 
GROUP BY CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
        ELSE '其他' END;

NULL值处理

首先在mysql中，NULL表示的是“a missing unknown value”，而字符串’'是一个确定的值，这本质上就已经不一样了，NULL是指没有值，而”则表示值是存在的，只不过是个空值

• NULL作为where条件是is null 或者 is not null 而不是=null

• NULL值排序

  用order by ... asc时，null值会被放在最前面，而用order by ... desc时，null时会被放在最后面，相当于null是一个无穷小的值。

• MIN,ARG函数和COUNT计算,NULL值将会被忽略

• IFNULL函数

  上面使用avg等的时候结果不是正确的，是因为null值的 原因，那么我们在日常使用聚合函数的时候，可以使用ifnull函数

  Ifnull(a,b) 表示如果a为null 就赋值为b

  所以可以在计算中通过ifnull，根据你的逻辑来赋值计算，这儿用0

• NULL值加减乘除

  与null的四则运算的结果都为null,在计算时需要配合IFNULL函数使用