SQL(MySql)菜鸟教程知识

增 insert into

insert into websites (name, url, alexa, county) values (‘百度’, ‘https://www.baidu.com/’, ‘4’, ‘CN’,);

删 delete from…where

delete from websites where name=‘Facebook’ and country=‘USA’
delete * from table_name #不删除表对得情况下，删除全部行。
不加where会全部删除

改 update…set…where

update websites set alexa=‘5000’, country=‘USA’ where name=‘菜鸟教程’;
不加where会全部修改
MySql中设置set sql_safe_updates=1;开启参数，必须携带where，否则会报错。

查 select

distinct

where

=， <>， >， <， >=，<=，between and，like，in
like：%:多个 ，_：一个， ^[A-H]：以A到H开头的，^{^A-H]：不以A-H开头的
between and：包前不包后

and or

order by

desc：降序， esc：升序默认
多列排序，desc或asc只对紧跟着的列名有效

limit

as 别名

select name, concat(url, ‘,’ ,alexa, ‘,’, country) as site_info from websites;
将三列结合在一起 创建site_info别名

inner join…on 表中存在至少一个匹配返回行

left join…on 从左表返回所有行，即使右表没有匹配，没匹配字段显示null

right join…on 从右表返回所有行，即使左表没有匹配，没匹配字段显示null

MySQL不支持full join，但可以通过左连接+union+右连接实现。

union：select语句列数量，类型，顺序需要一样。

select…into…from…从一个表复制信息到另一个表

select * into scorebak from score where neza=‘neza’ 要求scorebak不存在

insert into…select…from…

insert into scorebak select * from score where neza=‘neza’ 要求表scorebak必须存在

数据库

增：create database dbname;

删：drop database database_name

表

增：

create table table_name
(column_name1 data_type(size),
…
);

删：

drop table table_name
truncate table table_name #仅删除表内数据，不删除表本身

改：

alter table Persons add DateOfBirth date
alter table Persons drop column DateOfBirth
alter table table_name modify column column_name datatype

约束

not null，unique，primary key，foreign key，check，default，auto-increment
create table Persons( P_Id int NOT NULL, LastName varchar(255), ... UNIQUE (P_Id)
命名unique约束，并定义多个列的unique约束
create table Persons(P_Id int NOT NULL, LastName varchar(255) NOT NULL, ... , CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
表已创建添加约束:
alter table Persons add UNIQUE (P_Id)
alter table Persons add constraint uc_PersonID UNIQUE (P_Id,LastName)
撤销约束：
alter table Persons drop index uc_PersonID
alter table Persons drop constraint uc_PersonID

---

primary key (P_Id) CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) alter table Persons add PRIMARY KEY (P_Id) alter table Persons add constraint pk_PersonID PRIMARY KEY (P_Id,LastName) alter table Persons drop PRIMARY KEY alter table Persons drop constraint pk_PersonID

---

外键：foreignkey (P_Id) references Persons(P_Id) alter table Orders add foreign key (P_Id) references Persons(P_Id) alter table Orders drop foreign key fk_PerOrders

---

check：限制列中的值的范围 CHECK (P_Id>0) constartint chk_Person CHECK (P_Id>0 and City='Sandnes') alter table Persons add CHECK (P_Id>0) alter table Persons add constraint chk_Person CHECK (P_Id>0 and City='Sandnes) alter table Persons drop CHECK chk_Person

---

删除外键需要知道外键名称， select constraint_name from information_schema.referential_constraints where constraint_schema=<'db_name'> and tabke_name = <'table_name'>;

---

default: CIty varchar(255) default 'Sandnes' alter table Persons alter City set default 'SANDENS; alter table Persons alter City drop default

---

alter table Persons auto_increment=100

索引

create index PIndex on Persons(LastName,FirstName)
alter table table_name drop index index_name

视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

SELECT * FROM [Current Product List]
drop view view_name

日期

now()：日期+时间 curdate()：日期 curtime()：时间

NULL

is null，not null
select ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) from Products

函数

avg，count，max，min，sum，group by

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

having:
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

exists:
用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
SELECT Websites.name, Websites.url
FROM Websites
WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

ucase:把字段的值转换为大写。
lcase:把字段的值转换为小写。
mid:用于从文本字段中提取字符。
SELECT MID(name,1,4) AS ShortTitle
FROM Websites;

length()
round(数值,保留几位) 四舍五入，默认不保留
now() 当前系统的日期和时间
format(now(),’%Y-%m-%d’)

其他

MySQL添加联合唯一索引

三种方式：
方式一(unique index)：ALTER TABLE t_user_product_info ADD UNIQUE INDEX(user_id, product_id);
方式二(unique index indexname)：ALTER TABLE t_user_product_info ADD UNIQUE INDEX idx_user_product_id(user_id, product_id);
方式三(unique key)：ALTER TABLE t_user_product_info ADD UNIQUE KEY key_user_product_id(user_id, product_id);

向表中添加重复信息会报错，如果想要覆盖现在的数据，可以使用一下方法插入数据：
INSERT INTO…ON DUPLICATE KEY UPDATE

INSERT INTO t_user_product_info (id, user_id, product_id, validate_time, created_time, updated_time)
VALUES(2, 134678, '766001510010', '2019-11-16 10:22:00', '2019-12-15 10:22:00', NULL) ON DUPLICATE KEY UPDATE id = VALUES(id), user_id = VALUES(user_id), product_id = VALUES(product_id), validate_time = VALUES(validate_time), created_time = VALUES(created_time), updated_time = VALUES(updated_time)

我们需要创建联合唯一索引的表中已经存在重复的记录，我们可以采用以下方式的代码，它会删除重复的记录（仅保留一条），然后建立联合唯一索引。

ALTER IGNORE TABLE t_user_product_info ADD UNIQUE INDEX(user_id, product_id);