Mysql入门

最新推荐文章于 2022-02-19 20:50:31 发布

呈序员

最新推荐文章于 2022-02-19 20:50:31 发布

阅读量225

点赞数

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_41127779/article/details/82594243

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

注释

–这是一行注释
#这是一行注释
/*
多行注释
*/

登陆

cmd输入：
mysql -uroot

创建数据库

create database zk; –zk为数据库名字
use zk； //进入该数据库

创建表

CREATE TABLE Customers
(
cust_id char(10) NOT NULL primary key, –主键
cust_name char(50) NOT NULL references 表名(cust_name), –外键
cust_address char(50) NULL , –默认是NULL，不输入也可以
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) unique, –唯一性约束，每一行都不一样
cust_price decimal(8,2) NOT NULL default 1.0; –有初始值
);

约束

非空约束(not null)
唯一性约束(unique)
主键约束(primary key) PK
外键约束(foreign key) FK
检查约束(目前MySQL不支持、Oracle支持)

查看表的结构

describe Customers；4

更新表的结构

更新表的结构比较复杂，规则不统一，最好在设计表的时候，设计好
alter table

新增一列

alter table venders
add vender_phone char(20);

删除一列

alter table venders
drop column vender_phone;

删除表

drop table venders;

重命名表/列

ALTER TABLE test RENAME TO test1;
alter table test change t_name t_name_new varchar(20);

设置主键和外键

ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders 
FOREIGN KEY (order_num) REFERENCES Orders (order_num);          
--REFERENCES表示OrderItems中的order_num都来自Orders (order_num)，FK_OrderItems_Orders外键id

插入数据

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000001’, ‘Village Toys’, ‘200 Maple Lane’, ‘Detroit’, ‘MI’, ‘44444’, ‘USA’, ‘John Smith’, ‘sales@villagetoys.com’);

查询列

select 列名1,列名2 from 表名；
select * from 表明;//查询所有列

检索不同的值

select distinct 列名 from 表名;

检索前5行

Mysql:
select 列名 from 表名 limit 5; –其他软件不一样
select 列名 from 表名 limit 5 offsset 5; –从第5行开始的前5行数据

排序检索

select 列名 from 表名 order by 列名1; –按列名1排序，确保order by子句在select语句中的最后
select 列名 from 表名 order by 列名1,列名2; –先按列名1排序。只有列名1一样时，才按列名2排序
select 列名1,列名2,列名3 from 表名 order by 2,3; –按相对位置排序，即先按列名2排序。只有列名2一样时，才按列名3排序

倒序

select 列名 from 表名 order by 列名1 desc; –按列名1**倒序**排序
select 列名 from 表名 order by 列名1 desc,列名2; –按列名1**倒序**排序
select 列名 from 表名 order by 列名1 desc,列名2 desc; –按列名1，列名2**倒序**排序

where 数据过滤

select 列名 from 表名 where 列名1<10；–筛选列名1小于10
<> –不等于
!= –不等于,access不支持
!< –不小于
between a and b –在两只之间
is null –是null
如果和数值型比较，不用加”
如果和字符串比较，加”,如：
select 列名 from 表名 where id <> ‘167’;

操作符，也叫逻辑操作符（类似&&），连接多个where子句

and 且，优先级高于or
or 或者
not 非
in 在。。里
() 要多用
where A or B and C； –满足A或者（B且C）
where (A or B) and C； –满足(A或者B)且C
where id in (A,B); –相当于where id==A or id==B
where not id==A; –相当于where id!=A;

通配符

like去匹配类似的字符串


 %                          任意字符任意次
 [JM]                       该位置字符是J或者M的字符串
 [^JM]                      该位置字符不是J且不含M的字符串    
 -                          某个字符出现一次

select 列名 from 表名
where 列名1 like '[^JM%]';            --匹配开头字母不是J且不是M的字符串

计算字段

字段、列、属性类似

拼接字段

大部分可以用+
也有用 $||$ 和 $concat()$ 函数的

select NAME+'('+ID+')' from...

select NAME ||'(' || ID || ')' from...

select concat( NAME, '(', ID, ')' )  from...

别名 AS

计算得到的新的字段，默认没有字段名字，得取名字

select concat( NAME, '(', ID, ')' )  as 别名 from...

算术计算（加减乘除）

select price * number as 总价 from ...

不加from，select还可以简单的处理表达式，如：

select trim(' abc ');               --输出abc，trim()函数是去掉字符串首尾的空格
select 2*3;             --输出6

使用函数处理数据

优点：简单、性能高
缺点：不可移植（不同的DBMS函数一般都不同）
常用函数：

RTRIM()             --去字符串的首尾空格
LEFT()                  --左侧字符
LOWER()         --小写
UPPER()             --大写
SOUNDEX()       --将字符串转为声音的值
DATEPART(yy, 列名)     --输出时间的年分的前两个字符

汇总数据

AVG()                   --返回平均值
MAX()                   --返回最大值
MIN()                   --返回最小值
COUNT(列名)               --返回列名不为NULL的行数
COUNT(*)                --返回所有行的行数
SUM()                   --求和
DISTINCT                --返回不同的值

分组数据

分组

group by        --分组计算，对每一个组计算一次

筛选

having和where的区别：
having是筛选组数据
where是筛选行数据
例子：

select 列名, count(*) from 表名
group by 列名
having count(*) > 3;

排序

order by

select 列名, count(*) from 表名
group by 列名
having count(*) > 3
order by 列名;

子句顺序

select
from
where
group by
having
order by

例子：

select 列名, count(*)
from 表名
where 列名1>4
group by 列名2
having ...
order by 列名3;

子查询

其实就是嵌套查询，例如

select ... from ...
where id in
(select ... from ... where...)

作为子查询的select只能查询单列，不然会出错
子查询的缺点很明显：
1.很难看，逻辑不清晰
2.不利于调试

等值联结（内联结）

我们习惯把一类数据放在一个表里，但是会有重复的数据，重复数据多绝对不是一件好事（修改不方便）。所以我们把相关表关联，同相同的数据。

select 表1.列名1,列名2,列名3           --列名2，列3在两个表里只有一个，可以不加表名
from 表1,表2
where 表1.列名1 = 表2.列名1;          --此处的列名一定要完全限定列名，**必须确定到哪一张表**，因为有多张表

上述代码，如果去掉where语句。那么输出的是两个表的笛卡儿积。输出的行数是表1行数*表2行数，这种联结也叫做叉联结
另一种等值联结代码：（使用join on语句）

select 列名1,列名2,列名3
from 表1 inner join 表2
on 表1.列名1 = 表2.列名1;

多表等值联结

select 列名1,列名2,列名3
from 表1,表2,表3
where 表1.列名1 = 表2.列名1
and 表1.列名1 = 表3.列名1;

高级联结

自联结

就是自己和自己联结，但是得给这张表取两个名字（理解为复用）

select c1.* 
from customers as c1 inner join customers as c2 
on c2.cust_contact='Jim Jones' and c1.cust_name=c2.cust_name;

自然联结

自然联结就是使每个列只返回一次，每个内联结都是自然联结

外联结

和内联结对比：
1.内联结只返回表1和表2都有且满足条件的数据
2.外联结不仅返回内联结的数据还会返回在条件中，（左/右）表有的但是另一个表没有的数据

mysql> select customers.cust_id,orders.order_num 
from customers left outer join orders 
on customers.cust_id=orders.cust_id;

+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
--最后的结果中1000000002在左表中有cust_id，右表中没有关联，所有没有order_num

left outer join on          --包含左表有，右表没有关联的数据
right outer join on         --包含右表有，左表没有关联的数据
full outer join on          --包含左/右表有，右/左表没有关联的数据

联结和聚集函数结合使用

mysql> select customers.cust_id,count(orders.order_num) 
from customers inner join orders 
on customers.cust_id=orders.cust_id 
group by customers.cust_id;
+------------+-------------------------+
| cust_id    | count(orders.order_num) |
+------------+-------------------------+
| 1000000001 |                       2 |
| 1000000003 |                       1 |
| 1000000004 |                       1 |
| 1000000005 |                       1 |
+------------+-------------------------+

union（组合查询）

将select检索出来的数据合并，默认去重复
union all取消去重复

select...
union
select...
union
select...
order by ...                    --order只能放在最后一个select的最后部分

插入数据

方便的不安全的插入方法

insert into customers
values('1000000006',
        ...,
        'USA');

这种插入方法不安全，仅仅按给的顺序去一列一列插入。
此外，如果列的顺序一旦改变，那么很容易出错

安全的插入方式

insert into customers(cust_id,
                       ...,
                      cust_country)
values('1000000006',
        ...,
        'USA');

有的列可以省略，前提是：该列可以为空，或者该列有默认值

使用检索出来的数据插入

insert into customers(cust_id,
                       ...,
                      cust_country)
select cust_id,
        ...,
    cust_country
from custnew;

这种方法可以多行插入，检索出来几行就插入几行
但是，这种方法其实也是按照查询出来的列的顺序进行插入的，可能会出错

将已存在的表的数据复制到新的列表

create table custnew as
select * from customers;                --这里支持select的所有操作

更新和删除

更新

**1.一定要带上where，不然更新所有的行
2.可能需要一定的安全权限**

update customers
set cust_email='123@hotmail.com',
    cust_country='china'
where cust_id='1000000001';

删除

加上where，删除某一行或某几行，否则删除所有行

delete from customers
where cust_id='1000000001';

注意

在有外键的表中，插入或者删除可能会报错，比如
1.插入一个没有供应商（外键）的产品
2.删除一个在其他表里还有订单的产品

视图

1.视图类似于检索数据再创建一张表
2.但是，与表不同的是，视图不包含任何的数据，只包含查询，每次需要视图中的数据时，需要重新查询一次，性能较低
3.用法和表相同

create view viewname as
select ...

存储过程

类似与自己写函数

创建

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;　　#将语句的结束符号恢复为分号

调用

call delete_matches(57);

事务处理

事务具有原子性：要么全完成，要么全部不完成
1.事务：一组SQL指令
2.回退：回退到上一个状态，和撤销指令（只能撤销insert、update、delete）
3.提交：将为存储的结果存储
4.保留点：备份一个状态，用于回退

隐式提交

一般的SQL语句，都是直接执行并提交。这就是所谓的隐式提交；
而，事务是没有这种隐式提交。所以，事务处理需要，隐式提交。
使用commit语句

开始事务

start transaction
...

start transaction
...
commit transaction      --提交事务

回退

delete from orders；
rollback；               --撤销指令

保留点

保留点越多越好，方便回退

savepoint point1;               --创建还原点

rollback transaction point1;                --回退到point1还原点

例子

start transaction
insert into customers (cust_id,cust_name)
values('1000000006','TT');
savepoint point1;
insert into customers (cust_id,cust_name)
values('1000000007','TTk');
if @@error <> 0
rollback transaction point1;
commit transtion

使用游标

创建一个表,再 insert 些数据 ;代码,初始化

create table test.level (name varchar(20));
...

判断是否有useCursor存储过程

drop procedure if exists useCursor;

创建存储过程

CREATE PROCEDURE useCursor()
BEGIN

局部变量的定义 declare

declare tmpName varchar(20) default '' ;  
declare allName varchar(255) default '' ;           --游标用到的变量，必须放cursor之前不然报错
declare cur1 CURSOR 
FOR 
SELECT name FROM test.level ;

MySQL 游标异常后捕捉；
并设置循环使用变量 tmpname 为 null 跳出循环。

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;

开游标

OPEN cur1;

抓游标的数据赋值给变量，游标向下走一步

FETCH cur1 INTO tmpName;

循环体这很明显把MySQL 游标查询出的 name 都加起并用 ; 号隔开

WHILE ( tmpname is not null) DO 
set tmpName = CONCAT(tmpName ,";") ; 
set allName = CONCAT(allName ,tmpName) ; 
FETCH cur1 INTO tmpName;
END WHILE;

关闭游标

CLOSE cur1;

选择数据

select allName ;

结束存储过程

END;
调用存储过程：

call useCursor();

例子

drop procedure if exists zk;
delimiter $$
create procedure zk()
begin
declare temp text default '';
 declare tmpName text default '' ;
declare allName text default '' ;
 declare cur cursor for
 select cust_id from customers where cust_id in ('1000000001','1000000002');
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET temp = null;
 open cur;
 fetch cur into temp;
while(temp is not null) do
set temp = concat(temp,';');
set allname = concat(allname,temp);
fetch cur into temp;
end while;
 close cur;
 select allname;
 end;$$
delimiter ;
call zk();
+------------------------+
| allname                |
+------------------------+
| 1000000001;1000000002; |
+------------------------+

索引

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 [USING index_type]
 ON table_name (index_col_name,...)

删除索引

ALTER TABLE table_name
DROP INDEX index_name;

查看索引

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

触发器

参考https://www.cnblogs.com/lyhabc/p/3802704.html

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：

· INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

· UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。

· DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。
例如：

CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @SUM=@SUM+new.amount;

呈序员

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql入门

注释–这是一行注释 #这是一行注释 /* 多行注释 */登陆cmd输入： mysql -uroot创建数据库create database zk; –zk为数据库名字 use zk； //进入该数据库创建表CREATE TABLE Customers ( cust_id char(10) NOT NULL , ...
复制链接

扫一扫