Mysql基础复习

好久没用sql，都忘得干干净净，翻阅以前的学习笔记，觉得有些可记录的点，放在这里以便备用查阅

# 一、环境搭建

mac安装MySQL

brew install mysql

# 启动

mysql.server start

#登录
mysql -uroot 

# 二、基础知识

# 1、数据库的连接

# 例子
mysql -u root -p 123456 -h 127.0.0.1 

• -u 用户名
• -p 密码
• -h host主机

# 2、库级知识

命令后面加上分号

• 显示数据库: show databases;
• 选择数据库: use dbname;
• 创建数据库: create database dbname charset utf8;
• 删除数据库: drop database dbname;

# 3、表级操作

# 3.1 显示库下面的表

show tables;

# 3.2 查看表的结构

desc tableName;

# 3.3 查看表的创建过程:

show create table  tableName;

# 3.4 创建表

create table tbName (
列名称1　列类型　[列参数]　[not null default ],
列名称N　列类型　[列参数]　[not null default ]
) engine myisam/innodb charset utf8/gbk

例子

create table user (
    id    int         auto_increment,
    name  varchar(20) not null default '',
    age   tinyint unsigned not null default 0,
    index id (id)
)engine=innodb charset=utf8;

# 注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,
# charset 常用的有utf8,gbk;

# 3.5 修改表

3.5.1 修改表之增加列

alter table tbName add 列名称１　列类型　[列参数]　[not null default ]　

#(add之后的旧列名之后的语法和创建表时的列声明一样)

3.5.2 修改表之修改列

alter table tbName change 旧列名  新列名  列类型　[列参数]　[not null default ]

# (注:旧列名之后的语法和创建表时的列声明一样)

3.5.3 修改表之减少列

alter table tbName drop 列名称;

3.5.4 修改表之增加主键

alter table tbName add primary key(主键所在列名);

例:alter table goods add primary key(id) 该例是把主键建立在id列上

3.5.5 修改表之删除主键

alter table tbName　drop primary key;

3.5.6 修改表之增加索引

alter table tbName add [unique|fulltext] index 索引名(列名);

3.5.7 修改表之删除索引

alter table tbName drop index 索引名;

3.5.8 清空表的数据

truncate tableName;

# 4、列类型讲解

# 4.1 列类型

• tinyint (0~255/-128~127)
• smallint (0~65535/-32768~32767)
• mediumint
• int
• bigint

参数解释

unsigned 无符号(不能为负) zerofill 0填充 M 填充后的宽度

• 举例:

tinyint unsigned;
tinyint(6) zerofill;   

# 4.2 数值型

• 浮点型:float double
• 格式:float(M,D) unsigned\zerofill;

# 4.3 字符型

• char(m) 定长
• varchar(m)变长
• text

# 4.4 日期时间类型

• year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
• date YYYY-MM-DD 如:2010-03-14
• time HH:MM:SS 如:19:26:32
• datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32
• timestamp YYYY-MM-DD HH:MM:SS

特性:不用赋值,该列会为自己赋当前的具体时间

# 5、增删改查基本操作

# 5.1 插入数据

insert into 表名(col1,col2,……) values(val1,val2……); # -- 插入指定列

insert into 表名 values (,,,,); # -- 插入所有列

insert into 表名 values	# -- 一次插入多行 
(val1,val2……),
(val1,val2……),
(val1,val2……);

# 5.2 修改数据

update tablename 

set 

col1=newval1,  
col2=newval2,
...
...
colN=newvalN
where 条件;

# 5.3 删除数据

delete from tablenaeme where 条件;

# 5.4 select 查询

1. 条件查询 where

• 条件表达式的意义，表达式为真，则该行取出
• 比较运算符 = ，!=，< > <= >=
• like , not like ('%‘匹配任意多个字符,’_'匹配任意单个字符) in, not in , between and
• is null , is not null

2. 分组 group by 一般要配合5个聚合函数使用 max, min, sum, avg, count
3. 筛选having
4. 排序order by
5. 限制limit

# 6、连接查询

# 6.1 左连接

.. left join .. on

table A left join table B on tableA.col1 = tableB.col2 ; 

例句:

select 列名 from table A left join table B on tableA.col1 = tableB.col2

# 6.2 右链接

right join

# 6.3 内连接

inner join

• 左右连接都是以在左边的表的数据为准,沿着左表查右表.
• 内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集

# 7、子查询

where 型子查询:内层sql的返回值在where后作为条件表达式的一部分

# 例句: select * from tableA where colA = (select colB from tableB where ...);

from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询

例句:select * from (select * from ...) as tableName where ....

# 8、字符集

• 客户端sql编码 character_set_client
• 服务器转化后的sql编码 character_set_connection
• 服务器返回给客户端的结果集编码character_set_results
• 快速把以上3个变量设为相同值: set names 字符集

存储引擎 engine=1\2

• Myisam 速度快 不支持事务 回滚
• Innodb 速度慢 支持事务,回滚

事务

• 开启事务 start transaction
• 运行sql;
• 提交,同时生效\回滚 commit\rollback

触发器

• 触发器 trigger
• 监视地点:表
• 监视行为:增 删 改
• 触发时间:after\before
• 触发事件:增删改

创建触发器语法

create trigger tgName
after/before insert/delete/update 
on tableName
for each row
sql; # -- 触发语句

• 删除触发器:

drop trigger tgName;

索引

• 提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.
• 索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.
• 值越分散的列，索引的效果越好

索引类型

• primary key主键索引
• index 普通索引
• unique index 唯一性索引
• fulltext index 全文索引

综合练习:

• 连接上数据库服务器
• 创建一个gbk编码的数据库
• 建立商品表和栏目表,字段如下:

商品表:goods

• goods_id　--主键,
• goods_name – 商品名称
• cat_id – 栏目id
• brand_id – 品牌id
• goods_sn – 货号
• goods_number – 库存量
• shop_price – 价格
• goods_desc　--商品详细描述

栏目表:category

• cat_id --主键
• cat_name – 栏目名称
• parent_id – 栏目的父id

建表完成后,作以下操作:

• 删除goods表的goods_desc 字段,及货号字段
• 并增加字段:click_count – 点击量
• 在goods_name列上加唯一性索引
• 在shop_price列上加普通索引
• 在clcik_count列上加普通索引
• 删除click_count列上的索引

对goods表插入以下数据:

# 三、查询知识

注:以下查询基于ecshop网站的商品表(ecs_goods)

在练习时可以只取部分列,方便查看.

# 3.1 基础查询 where的练习

查出满足以下条件的商品

# 3.1.1 主键为32的商品

select goods_id,goods_name,shop_price 
     from ecs_goods
     where goods_id=32;

# 3.1.2 不属第3栏目的所有商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id!=3;

# 3.1.3 本店价格高于3000元的商品

# 3.1.4 本店价格低于或等于100元的商品

# 3.1.5 取出第4栏目或第11栏目的商品(不许用or)

# 3.1.6 取出100<=价格<=500的商品(不许用and)