MySQL的基础操作指令

MySQL的数据类型

数值列类型

mysql提供了五种整型： tinyint、smallint、mediumint、int和bigint。int为integer的缩写。这些类型在可表示的取值范围上是不同的。

整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。各种类型的存储量需求也是不同的。取值范围较大的类型所需的存储量较大。

mysql 提供三种浮点类型： float、double和decimal。

create table t1() 在当前数据库中表名称不允许重复，如果重复则报错

• create table if not exists t1 如果t1表不存在才执行创建操作，如果已存在则不执行任何操作

无符号的写法：

• create table t13(id tinyint unsigned);

定点的浮点数

• create table t14(id numeric(8,2)); 表示总位宽为8位，小数为2位，其中的正负号和小数点不进行计算
• 小数处理采用的是四舍五入

完整性约束

• primary key 用于声明主键，非空唯一。一个表只能定义一个主键，但是主键允许使用多个列构成(复合主键)
  • create table t17(id int primary key);
  • create table t18(
    • id int, name varchar(20),
    • primary key(id,name)); 表示id和name的组合不允许为空【任何一个都不允许为null】，不允许重复，不是其中某个列不允许为空，不允许重复
  • 4种用户定义约束
    • null允许为空，默认/not null不允许为空 id int not null表示id列不允许为空，如果插入null值则报错

default设置默认值，如果试图插入null，则加入null，默认值无效；如果不针对当前列进行操作，则默认值生效

• create table t1(id int default 1,name varchar(20));
• Insert into t1 values(null,‘yan’); 此时id插入null值。如果insert into t1(name) values(‘yan’)则默认值生效

check检查型约束，可以定义，语法create table t1(id int check(id>5)); 要求插入的数据必须满足条件id>5，但是在mysql中无效

unique唯一性约束，表示列中的数据不允许重复，否则报错，拒绝操作
create table t1(id int unique); 允许为null，null表示不确定的值

数值列属性

• AUTO_INCREMENT以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异
• create table t16(id int primary key auto_increment); 只有不插入数据或者插入null值时才会自动生成自增的值

字符串列类型

在mysql中使用单引号表示字符串类型，事实上不区分单双引号，但是一般建议使用单引号，允许使用\转义字符

类型名	参数	说明
char(n)	0-255	定长字符串
varchar(n)	0-65535	可变长字符串
—	—	—

create table t1(id char(2)); 表示id为字符串类型，允许最大存储2个字符，不区分中英文

char(2)表示存储的字符个数为2，如果实际长度大于2则报错；如果实际存放的字符数不足，则自动末尾补充空格。

varchar(2)表示存储的字符个数最大为2，存放字符数不足时按照实际长度存放

如果存放字符串末尾是空格时，会自动裁剪到满足长度限制为止，注意不能裁剪开头的和中间的空格

等值判定时空格不参与

char与varchar的区别

1、char是定长数据，如果char(10)但里面只保存了1个字符，则它的实际长度仍然是10。Mysql对查询的结果进行了处理 ，所以使用length无法判断它所占用的长度；varchar(10)时，如果里面只保存了1个字符，则它的长度为1，所以varchar被称了可变长字符
2、char的取值范围为0-255；varchar的取值范围为0-65535。如果需要存储的字符串的长度跟所有值的平均长度相差不大，适合用char，如MD5。 对于经常改变的值，char优于varchar，原因是固定长度的行不容易产生碎片。
3、对于很短的列，char优于varchar，原因是varchar需要额外一个或两个字节存储字符串的长度
4、char和varchar后面如果有空格，char会自动去掉空格后存储，varchar虽然不会去掉空格，但在进行字符串比较时，会去掉空格进行比较

大对象类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求
TINYTEXT可变长度最多 255 个字符；TEXT可变长度最多 65535 个字符；MEDIUMTEXT可变长度最多 16M个字符；LONGTEXT可变长度，最多4G个字符

set和枚举

enum枚举类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值只能是枚举值中的任意一个值
create table t1(id enum(‘a’,’b’,’c’));
set集合类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值可以是集合中的任意多个值
create table t1(id set(‘a’,’b’,’c’));

日期和时间列类型


date类型只能存放日期，datetime可以存放日期和时间

datetime一般供用户使用，而timestamp可以存放1970-2038年的日期和时间，一般供系统使用

默认值的设置问题

需求：需要当不添加数据时，自动填充服务器的系统当前时

位类型bit
bit 表示1个二进制的位 bit(8) 表示8个二进制的位，其中n的取值范围为1-64

boolean类型

---

定义数据类型就是定义列，数据类型决定数据的特性
数据类型主要分为字符串类型，浮点书类型和定点数类型，日期类型以及二进制类型
数据类型在不同的存储引擎上表现不同
根据所采用的数据类型，需求和数据特性选择数据类型

删除操作drop

删除数据库 DROP DATABASE <数据库名>;
drop database test;

删除数据表 DROP TABLE <表名>;
drop table t_users;

基本表删除数据、表上的索引都删除
表上的视图往往仍然保留，但无法引用

删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述（标准中没有，认为表建立后就永久存在）
show create table t1;

修改基本表alter

修改表 ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；

说明：
<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的完整性约束条件
MODIFY子句：用于修改列名和数据类型

基本练习

1、创建一个数据库test，如果数据库已经存在则删除
drop database if exists test;
create database test default character set utf8;
2、切换当前数据库
select database(); 查看当前数据库
use test;
3、创建一个用于存储学生信息的学生表 student
编号id，姓名name，成绩score
show tables; 查看当前库中的所有表
drop table if exists student; 如果已经存在student表则执行删除操作
create table if not exists student(
id bigint primary key auto_increment,
name varchar(10) not null,
score numeric(4,1) default 0
)engine=innodb default charset utf8;
4、向Student表增加“入学时间”列，其数据类型为日期型
alter table student add scome date;
不论基本表中原来是否已有数据，新增加的列一律为空值。所以如果表中已经有数据，则注意not null约束的问题
5、将年龄的数据类型改为半字长整数
alter table student add age int; 向表中添加一个int类型的列age
desc student; 查看当前的表结构
alter table student modify age smallint;
6、删除学生姓名必须取唯一值的约束
alter table student modify name varchar(10) unique;

alter table student drop name; 删除指定列name
alter table student add name varchar(10); 重新添加列name

对表的基本操作 DML+DQL

增加数据:
Insert into 表名称 values(值的列表)
Insert into tb_users values(1,’yan’,’123’) 注意和表的列定义一一对应，当值不确定时也必须写null
Insert into 表名称(列名称,…) values(对应的值,…) 注意一一对应，注意数据类型。如果没有出现的列则自动插入null

插入数据总结
字符类型和日期类型的数据前后要添加’’单引号
没有指定列时，要给出完整数据，且要对应顺序：
Insert into student values(1,’Tom’,’1’);
优化写入，指定列：
Insert into stud(id,name)values(1,’jack’);

修改数据
update 表名称 set 列名称=值, … 将指定表中指定列的值全部修改为指定值
update stud set name=‘Jack’; 将stud表中的所有行的name列值修改为jack
update 表名称set 列名称=值, … where 条件 只修改满足条件的指定行上列值
update stud set name=‘Jack’where id=1; 将stud表中的id值为1的行的name列值修改为jack

同时修改两个值：update stud setname=‘Rose’,id=22 where id=1;

在原值上面进行累加操作：update product set price=price1.1 －所有商品的价格上涨10%
update product set price=price1.1 where cname=‘食品’－只有列名为cname值为食品的商品，价格上涨10%

删除数据
delete from 表名称; 删除表中的所有数据,表结构仍旧存在,类似truncate table 表名称
delete from 表名称 where 条件; 根据条件删除数据，例如delete from tb_users where id>3则会删除所有id>3的行
delete from stud 全部删除
delete from stud where id=1; 删除指定的行
特殊写法： delete from stud where 1=1 delete from stud where 1=2

---

• 也可以使用 TRUNCATE TABLE stud; 它与delete的区别是不记录操作日志，即无法恢复数据
  从执行效果上看 truncate table stud和delete from stud一致，都是删除了stud表中的所有数据，但是执行是有区别的

---

truncate table不是DML语句，没有事务的概念，所以直接执行删除。不能撤销操作
delete from属于DML语句，有事务，所以在删除前需要记录日志，以供事务执行回滚撤销操作。
一般针对大量数据的删除操作，使用truncate执行效率高

查询数据
SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … FROM <表名或视图名>[，<表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

select * from 表名称 查询指定表中的所有行的所有列数据，* 用于指代所有列。事实上一般建议写列名称，而不是*号
select * from tb_users;
Select 列名称,… from 表名称 查询指定表中的所有行的指定列的数据，没有指定的列不查询
select username,password from tb_users; 只获取username和password两个列的值，而id列的值不获取

select * 或者指定列 from 表名称 where 条件; 只查询满足条件的行
条件：
比较 >大于 >=大于等于 <小于 <=小于等于 !=不等于 =等于比较，不是赋值
select * from stud where age>=18; 查询年龄大于等于18岁的学生
select * from stud where age=18; 注意=不是赋值，是等值判断，查询年龄为18岁的学生
特殊比较 =null空值判断
在数据库中null表示不确定的值，所以判断不能使用=号，只能通过is null判空或者is not null非空进行判断

select * from student where name is null;
select * from student where name is not null;

注意：’’空字符串和null不是一样的 is null可以获取id=6，但是不能获取id=7

from语句在mysql中不是必须的【数据库不同有所区别】


字符串的比较
允许进行大小的比较，但是一般使用相等和不等于的判定较多
select * from tb_users where username=’zhangsan’ and password=’123456’

模糊查询 like _ 和%， 例如查询所有姓张的学生
使用通配符 _ 或者%，必须使用like或者not like运算符，不能使用=号
_ 表示一个任意字符
查询所有姓张的同学 select * from stud where name like ‘张%’ 其中%表示任意个数的任意字符
查询所有姓张的同学，并且名字为2个字符 select * from stud where name like ‘张_’ 其中_表示1个任意字符
查询所有名字中有书字的同学 select * from stud where name like ‘%书%’
查询所有名字以书字结尾的同学 select * from stud where name like ‘%书’

多条件查询
and与 or或 not非
查询所有的姓张的男生 select * from stud where name like ‘张%’ and sex=1
查询姓张或者年龄在18岁以下的学生 select * from stud where name like ‘张%’ or age<18
查询姓张或者年龄在18岁以下的男生 select * from stud where (name like ‘张%’ or age<18) and sex=1
查询所有年龄不在18岁以下的学生 select * from stud where not(age<18)

术语
元组是关系数据库中的基本概念，是事物特征的组合，可以描述一个具体的事物。
关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，元组的集合称为关系，每列就是一个属性。 在二维表里，元组也称为行，属性为列。

字段：某一个事物的一个特征或者说是属性
列：字段的另一种称谓

表：记录的属组合，表示同一类事物的组合。
主键：能唯一标识信息的事物
域：属性的取值范围

关系模式：关系的描述，表示为：关系名（属性列表） 例如：学生（学号，姓名，性别，年龄，系别）
关系的约束：实体完整性约束，参照完整性约束，用户自定义完整性约束【域完整性约束】