MySQL基础

MySQL基础

一、sql概述

1.什么是sql?

• SQL：Structure Query Language。（结构化查询语言）,通过sql操作数据库(操作数据库,操作表,操作数据库)
• SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准
• 各数据库厂商(mysql,oracle,sql server)都支持ISO的SQL标准。
• 各数据库厂商在标准的基础上做了自己的扩展。 各个数据库自己特定的语法
  

2.sql的语法

• 每条语句以分号结尾(命令行里面需要, 今天需要)，如果在navicat中(后天可以)不是必须加的。
• SQL在window中不区分大小写，关键字中认为大写和小写是一样的

3.sql的分类

• Data Definition Language (DDL数据定义语言) 如：操作数据库，操作表
• DataManipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改
• Data QueryLanguage(DQL 数据查询语言)，如：对表中的查询操作
• Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

二、对数据库的CRUD

1.创建数据库

create database 数据库名 [character set 字符集][collate  校对规则]     注: []意思是可选的意思

字符集(charset)：是一套符号和编码。eg：utf8(不是utf-8)； gbk

2.查看数据库

2.1查看所有的数据库

show databases; 

2.2查看数据库的定义结构【了解】

show create database 数据库名;

3.删除数据库

drop database 数据库名;

4.修改数据库的字符集【了解】###

alter database 数据库名 character set 字符集;

5.其它操作

• 切换数据库, 选定哪一个数据库

use 数据库名;   		-- 注意: 在创建表之前一定要指定数据库. use 数据库名

• 查看正在使用的数据库

select database();

三、对表的CRUD

1.创建表

1.1语法

	create table 表名(
		列名(字段) 类型 [约束]， 
		列名 类型 [约束]，
		列名 类型 [约束]
	);

1.2 类型

整数: 一般用int

小数: 默认精度 float, double

​ 指定精度 float(m,d); eg: float(4,2) 总位数为4位, 小数占2位; 表达范围 -99.99~99.99

字符串: varchar(最大长度) : 可变长度; char(最大长度): 固定长度

​ eg: varchar(10): 最多可以存放10个字符 , ‘张三三’—>分配3个字符的空间

​ char(10): 最多可以存放10个字符, ‘张三三’—>分配10个字符空间

​ 一般情况下我们用 varchar(n),但是char(n)的效率高.

​ 如果当前的数据的长度是可以确定的,不变的可以使用char; eg: 身份证号码 18位, 手机号码 11位

文件: 一般不会使用blob,longblob. 不会存文件的内容到数据库里面去, 存文件的路径

日期: date: 日期类型(年月日)

​ datetime: 时间类型(年月日 时分秒)

​ timestamp: 时间戳, 自动的会把当前的系统时间帮我们赋值;

​ eg: ordertime timestamp, 不需要管时间了, 直接赋值null, 自动的会把当前的系统时间赋值

1.3 约束

• 即规则,规矩 限制;
• 作用：保证用户插入的数据保存到数据库中是符合规范的
  

约束种类:

• not null; 非空 ; eg: username varchar(40) not null 也就意味着username列的数据不可以为null

• unique；唯一约束, 后面的数据不能和前面重复; eg: cardNo varchar(40) unique;

• primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的 ;

  • auto_increment: 自动增长,必须是设置了primary key之后,才可以使用

    ​ eg: id int primary key auto_increment, id不需要你收到维护了, 插入null就好了,

    ​ 自动的给你赋值, 默认情况下每次+1

id列:

1. 给id设置为int类型, 添加主键约束, 自动增长
2. 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

1.4练习

• -创建一张学生表(含有id字段,姓名字段,性别字段. id为主键自动增长)

create table student(
	id int primary key auto_increment,
	name varchar(40) not null,
	sex int
);

2.查看表

2.1查看所有的表

	show tables;

2.2查看表的定义结构

  desc  表名；

3.修改表

3.1语法

- 增加一列
		alter table 表 add 字段 类型 约束；
- 修改列的类型约束
		alter table 表 modify 字段 类型 约束 ；
- 修改列的名称、类型、约束
		alter table 表 change 旧列 新列 类型 约束;
- 删除一列
		alter table 表名 drop 列名；
- 修改表名
		rename table 旧表名 to 新表名； 

3.2练习

• 给学生表增加一个grade字段

alter table student add grade varchar(20);

• 给学生表的sex字段改成字符串类型

alter table student modify sex varchar(10); 

• 给学生表的grade字段修改成class字段

alter table student change grade class varchar(40);

• 把class字段删除

alter table student drop class;

• 把学生表修改成老师表(了解)

rename table student to teacher;

4.删除表

• 语法

  drop table 表名；

四、对表里面数据的CRUD【重点】

• 准备工作:

  创建一张商品表(商品id,商品名称,商品价格,商品数量.)

create table product(
	pid int primary key auto_increment,  -- 只有设置了auto_increment id列才可以赋值为null
	pname varchar(40),
	price double,
	num int
);

1.插入数据

• 方式一:

insert into 表(列，列..) values(值，值..)；-- 插入几个列, 就列出几个列,没有插入数据的列自动的赋值为null;
eg:
insert into product(pid,pname) values(null,'Mac'); 
-- 在上面这个例子里面, 如果product里面的price这个列设置了not null, 上面这个语句能成功执行吗?  不可以

• 方式二

	insert into 表 values(值，值....);           -- 注:插入所有的列

eg:

	insert into product values(null,'iPhonex',8000,10);
	insert into product values(null,'苹果电脑',8000,100);
	insert into product values(null,'iPhone7',6000,200);
	insert into product values(null,'iPhone6s',4000,1000);
	insert into product values(null,'iPhone6',3500,100);
	insert into product values(null,'iPhone5s',3000,100);
	
	insert into product values(null,'方便面',4.5,1000);
	insert into product values(null,'咖啡',11,200); 
	insert into product values(null,'矿泉水',3,500);

注意：

• 方式一没有赋值的列,系统自动赋为null(前提是当前列没有设置not null 约束)
• 列名与列值的类型、个数、顺序要一一对应。
• 值不要超出列定义的长度。
• 插入的日期和字符串，使用引号括起来。

命令行插入中文数据报错:

• 关闭服务, net stop mysql

• 在数据库软件的安装目录下面, 修改配置文件 my.ini中客户端的编码为gbk
  

• 重新打开命令行,开启服务, net start mysql

2.更新记录

2.1语法

	update 表 set 列 = 值 ,列= 值 [where 条件]

2.2练习

• 将所有商品的价格修改为5000元

update product set price = 5000;

• 将商品名是Mac的价格修改为18000元

update product set price = 18000 where pname = 'Mac';

• 将商品名是Mac的价格修改为17000,数量修改为5

update product set price = 17000, num = 5 where pname = 'Mac';

• 将商品名是方便面的商品的价格在原有基础上增加2元

update product set price = price + 2 where pname = '方便面';

3.删除记录

3.1delete

	delete from 表 [where条件];    -- 注意: 删除数据用delete,不用truncate

3.2truncate

	truncate table 表;

delete 和truncate区别【面试可能会问到】

• DELETE 删除表中的数据，表结构还在;删除后的数据可以找回,一条一条的删除.
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

3.3练习

• 删除表中名称为’Mac’的记录

delete from product where pname = 'Mac';

• 删除价格小于5001的商品记录

delete from product where price < 5001;

• 删除表中的所有记录

delete from product;

4.查询记录【重点】

4.1基本查询语法

	select  [*] [列名 ,列名] [列名 as 别名 ...] [distinct 字段] from 表名 [where 条件] 

4.2简单查询

4.2.1 查询所有的列的记录

select * form 表

4.2.2查询某张表特定列的记录

select 列名,列名,列名... from 表

4.2.3 去重查询

SELECT DISTINCT 字段名 FROM 表名;   -- 要数据一模一样才能去重

4.2.4 别名查询

select 列名 as 别名 ,列名  from 表   -- 列别名  as可以不写
select 别名.* from 表 as 别名       -- 表别名(多表查询)

4.2.5运算查询(+,-,*,/等)

• 把商品名，和商品价格+10查询出来

	select pname ,price + 10 from product;

注意：

• 运算查询字段,字段之间是可以的
• 字符串等类型可以做运算查询，但结果没有意义

4.3条件查询

4.3.1语法

SELECT xx FROM 表名 WHERE 条件;  -- 取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

1. between...and..    闭区间(包含临界值)  

   ​	eg:price  between 3000 and 6000  查询价格在3000到6000之间(包含3000和6000) 

2.  like 模糊查询;  需要和占位符一起用; _占一位;  %占0~n位

   ​	eg:  查询姓张的用户   			username like  '张%';  张  张三 张三三 张三三三
   ​	查询姓张的用户 (名字是两个字的)		 username like  '张_';

4.3.2练习

• 查询商品价格>3000的商品

select * from product where price > 3000;

• 查询id=1的商品

select * from product where pid = 1;

• 查询id不等于1的商品

select * from product where pid <> 1;

• 查询价格在3000到6000之间的商品

select * from product where price between 3000 and 6000;

• 查询id为1，5，7，15的商品

select * from product where pid = 1;
select * from product where pid = 5;
select * from product where pid = 7;
select * from product where pid = 15;

select * from product where pid in (1,5,7,15);

• 查询商品名以iPho开头的商品(iPhone系列)

select * from product where pname like 'iPho%';

• 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and…)

select * from product where price > 3000 and num > 20;

• 查询pid=1或者价格小于3000的商品

select * from product where pid = 1 or price < 3000;