MySql入门学习（二）

坚持日志No.2

SQL 入门

一、什么是SQL

结构化查询语言（Structured Query Language）简称SQL，SQL语句就是对数据库进行操作的一种语言。

注意：

1. SQL语句可以分单行或者多行书写，以分号结尾。
2. 可使用空格跟缩进增强语句的可读性。
3. MySQL不区分大小写。
4. 注释有3种：
   单行注释，–后面紧跟的空格不能省略
   # 单行注释
   /* 多行注释 */

---

二、SQL分类

• DDL（Data Definition Language）数据定义语言
  用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter 等

• DML（Data Manipulation Language）数据操作语言
  用来对数据库中表的数据进行增删改。关键字：insert，delete，update 等

• DQL（Data Query Language）数据查询语言
  用来查询数据库中表的记录（数据）。关键字：select等

• DCL（Data Control Language）数据控制语言
  用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

---

三、表的创建

1.创建数据库

语法：

create  database 数据库名 [default charset 编码格式 collate 排序规则];

-- 创建数据库     create创建     database 数据库      bookdb你取得数据库的名字
create database bookdb;


-- 给数据库指定字符集和排序规则
create database studb default charset utf8  collate utf8_general_ci;


-- 不要修改数据库的名称
-- 可以修改数据库字符集
alter database bookdb charset utf8;


-- 如果数据库不存在则创建，存在则不创建。
create database  if not exists bookdb;


-- 创建数据库，不存在则创建，并指定制定字符集
create database if not exists bookdb character set utf8;


-- 显示所有数据库
show databases;


-- 查看某个数据库的创建以及字符集
show create database bookdb;


-- 使用数据库
use bookdb;


-- 查询当前正在使用的数据库
select database();

2.创建表

语法：

create table 表名 (
  	列名  列类型类型  [约束]  [comment '备注1' ] ,
  	列名  列类型类型  [约束]  [comment '备注2' ] ,
  	列名  列类型类型  [约束]  [comment '备注3' ] ,
  	...
  	列名  列类型类型  [约束]  [comment '备注N' ] 
  );

3.常用数据类型

​ MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值（整型/小数）、日期/时间和字符串(字符)类型。

整型

类 型	大 小	范围（有符号）	范围（无符号）
tinyint(m)	1byte	(-128，127)	(0，255)
smallint(m)	2byte	(-32 768，32 767)	(0，65 535)
mediumint(m)	3byte	(-8 388 608，8 388 607)	(0，16 777 215)
int(m)或integer(m)	4byte	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
bigint(m)	8byte	(-9223372036854775808，9223372036854 775 807)	(0，18446744073709551 615)

• 参数m只是当位数小于m时候显示的宽度，对数字大小没有限制，意义不大。

• ZEROFILL表示显示长度不够的用0占位。

• 要看到截图中的效果需要在命令行中输出。

• 如果实际插入的值超过了类型的限制则报错，无法插入。

• 如果参数m不给出会有默认值，每种类型的默认值不同。

• 默认是有符号的，如果要定义无符号的需要加上关键字UNSIGNED

• 定义了ZEROFILL则直接是有无符号的，ZEROFILL跟有符号冲突，只能选择1个。

浮点型

类 型	大 小	含 义
float(m,d)	4byte	单精度浮点型，m总个数，d小数位
double(m,d)	8byte	双精度浮点型，m总个数，d小数位

m表示整个小数的总长度，d表示小数位的长度

• 也可以不给（m,d）值，直接使用float跟double，则默认会按照实际的精度（由实际的硬件跟操作系统决定）来显示。
• （m,d）会采取四舍五入，因此会出现精度丢失的问题。
• （m,d）的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。

定点数

​浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(P,D)

• P表示有效数字的精度，P的范围为[1，65]
• D是表示小数点后的位数，D的范围是[0,30]，MySQL要求D<=P
• DECIMAL(6,2) 表示此列最多可以存储6位数字，小数位数为2位; 因此，此列的范围是从-9999.99到9999.99
• DECIMAL(P)相当于DECIMAL(P,0)
• DECIMAL在不指定精度时，默认整数为10，小数为0

日期和时间类型

类 型	大 小	含 义
date	3	表示日期，包含年月日
time	3	表示时间，包含时分秒
year	1	表示年份
datetime	8	表示日期，包含年月日，时分秒
timestamp	4	表示日期，包含年月日，时分秒，自动更新

• %Y表示年，%m表示月，%d表示天，%H表示小时，%i表示分，%s表示秒。
• year、time、date用法类似

字符

类 型	含 义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符
blob	二进制形式的长文本数据
mediumblob	二进制形式的中等长度文本数据
longblob	二进制形式的极大文本数据

• char(n)若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。定长，浪费空间，速度快。
• varchar(n)是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4)存入3个字符将占用4个字节。不定长，节省空间。
• text不用指定长度n，text是实际字符数+2个字节。text类型不能有默认值。
• blob都是存储二进制数据，存储的数据只能整体读出。

枚举

​实现将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个。

语法：enum（可能出现的元素列表）

  -- 定义sex列，类型为枚举，值只能是列表中的某一个值，可以为null
  create table t(
       sex enum('男','女') 
  );
  

4.常用约束

约束

​ 对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

• 主键约束：primary key
• 非空约束 : not null
• 唯一约束 : unique
• 默认值约束：default
• 外键约束 : foreign key

主键约束

​主键，又称主码，是表中一列或多列的组合。主键要求主键列的数据唯一，并且不允许为空，主键能够唯一地表识表中的一条记录。一个表只能有一个主键，但是可以是多列（复合主键）。

• 使用primary key

  -- 方式1: 直接在主键字段后标识
  create table t(
  	id int primary key ,
  	name varchar(10)
  );
  
  -- 方式2： 后面专门定义主键约束，如果是复合主键（多列）只能使用这种方式
  create table t(
  	id int,
  	name varchar(10) ,
  	primary key(id)
  );
  
  -- 方式3：在已存在的表添加主键约束
  alter table t modify id int primary key;
  alter table t add constraint id_pri primary key (id);
  
  
  -- 删除主键约束
  alter table t drop primary key;
  -- 错误写法，不会真的删除主键
  alter table t modify id int;
  
  

非空约束

​定义表的时候限定为非空约束后则此字段不能有null值。

• 使用not null

  -- 创建表的时候给指定列添加非空约束
  create table t(	 
  	stu_name varchar(10) not null
  );
  
  -- 在表已经存在的基础上添加非空约束
  alter table t modify stu_name varchar(10) not null;
  
  -- 删除非空约束
  alter table t modify stu_name varchar(10);
  
  

唯一约束

​限定列数据不能有重复的值，null可以重复。

• 使用unique

  -- 创建表的时候给指定列加上唯一约束
  create table t(	 
  	stu_name varchar(10) unique 
  );
  
  -- 在表存在的前提下添加唯一约束
  alter table t modify stu_name varchar(10) unique;
  alter table t add constraint name_unique unique(stu_name);
  
  
  -- 删除列上的唯一约束
  alter table t drop index stu_name;
  -- 通过约束名删除列上的唯一约束
  alter table t drop index name_unique;
  
  

默认值约束

默认值约束用来指定某列的默认值。

• 使用default

  -- 创建表的时候给指定列加上默认值约束
  create table t(	 
   sex char (2) default '女'
  );
    
  -- 在表存在的前提下添加默认值约束
  alter table t add modify sex char(2) default '女';
  alter table 表名 alter column 列名 set default ' ';
  
  -- 删除默认值约束
  alter table t add modify sex char(2);
  
  

外键约束

​让表与表产生关系，从而保证数据的正确性。

• 使用foreign key

  -- 先创建学生表，父表
  create table student(
  s_id int primary key ,
  	s_name varchar(10) 
  );
  
  -- 再创建子表
  create table score (
  	s_id int ,
  	s_name VARCHAR(10),
  	s_socre int ,
  	user_id int ,
      -- 指定字段user_id是外键，指向父表student表中的s_id字段，外键必须指向父表的主键
  	-- constraint s_foreign 这里可以加上，给这个外键取个名字  
      FOREIGN KEY(user_id) references student(s_id)
   ); 
   
  -- 创建表后再添加外键约束
   alter table score add constraint score_foreign foreign key (user_id) references student(s_id);  
   
   --  删除外键约束
   alter table score drop foreign key score_foreign;
   
  

  • 创建完父表与字表的外键约束关系后，则子表的外键字段值如果不为null的话，则必须是主表中的存在值
  • 建立好外键约束关系后，如果子表外键值使用了主表的一个值，则默认情况下主表的这个值不能删除或者改成别的值。如果不要这个默认操作需要在定义外键的时候设置。
  • 级联删除、级联更新

    • 当删除主表的数据时，子表的行为可以设置级联删除跟级联更新，也可以设置null删除，设置null更新。

      create table score (
      	s_id int ,
      	s_name VARCHAR(10),
      	s_socre int ,
      	user_id int ,
          -- 设置外键时，级联删除跟级联更新
      	FOREIGN KEY(user_id) references student(s_id) on delete cascade on update cascade 
       );
       
       -- 设置外键时，级联删除跟级联更新
       alter table score add constraint score_foreign foreign key (user_id) references student(s_id) on update cascade on delete cascade 
       
       create table score (
      	s_id int ,
      	s_name VARCHAR(10),
      	s_socre int ,
      	user_id int ,
          -- 设置外键时，set null删除跟set null更新
      	FOREIGN KEY(user_id) references student(s_id) on delete set null on update set null );
       
        -- 设置外键时，set null删除跟set null更新
       alter table score add constraint score_foreign foreign key (user_id) references student(s_id) on update set null on delete set null  
       
      

自动增长

​ 当某列是数值时，我们可以给这列设置，让它自动增长。一般跟数值类型的主键一起使用。关键字auto_increment。

• 使用

  -- 创建表的时候直接定义自增长
  create table student(
  	s_id int primary key auto_increment,
  	s_name varchar(10) 
  ) auto_increment=10000 -- 设置自动增长从10000开始
  
  -- 设置自动增长从10000开始
  alter table student  auto_increment=10000;
  
  -- 给已经存在的表字段添加自增长
  alter table student s_id int auto_increment;
  
  -- 删除自增长，不会删除主键
  alter table student modify s_id int;
  
  

  • 添加自动增长以后，也同样可以对字段进行主动赋值。
  • 如果手动赋值跳跃性，则后面会直接根据最大值++。

四、表的删除和更新

1.查询表

• 查询某个数据中所有的表

  show tables;
  

• 查询表结构

  desc student;
  

• 查询表的创建SQL

  show create table student;
  

2.修改表

• 修改表名

  alter table 表名 rename to 新的表名;

  alter table student rename to s1;
  

• 修改表的字符集

  alter table 表名 character set 字符集的名称;

  alter table student character set utf8;
  

• 添加一列

  alter table 表名 add column 列名 列类型 [约束];

  alter table 表名 add (列名 列类型 [约束] , 列名 列类型 [约束] , ...... );

  alter table student add  column stu_height int  not null; 
  alter table student add (stu_weight int);
  

  alter table 表名 add column 列名 列类型 [约束] [FIRST]/[AFTER 列名];

    alter table s1 add  column stu_height int  not null  first;
    alter table s1 add  column stu_height int  not null  after stu_age;
  

• 修改列名称跟类型

  alter table 表名 change 原列名 新列名 新数据类型;

  alter table student change  stu_weight  stu_weight1  double;
  

3.删除表

• 删除列

  alter table 表名 drop 列名;

  alter table student drop column stu_weight;
  

• 删除表

  drop table 表名;

  drop table if exists 表名;

  drop table student;
  drop table if exists student;
  

• 删除数据库

  drop database 数据库名;

  drop  database studb;