学习笔记一:sql语句

建库建表

show databases;

create database db1 character set utf8;

use mydata;

SELECT DATABASE;

show create DATAbase 数据库名；#查看一个数据库的定义信息

修改数据库字符集
alter database db1 character set utf8;
show create database db1

删库
drop database db1；

建表
use db1;
create table db1_1(cid int, cname varchar(10));

创建一个表结构与db1相同的表db2
create table db2 like db1;

增删改查

查看表结构
desc 表名;

查看表
show tables;

查看创建表的sql语句
show create table category；

删表
drop table if exists test1;
drop table test1;

修改表名
rename table test1 to test2;

表中添加列
alter table test1 add name varchar(20);

修改表中的列的数据类型或长度
alter table test1 modify name char(50);

修改表中的列名
alter table test1 change name address varchar(30);

删除列
alter table test1 drop address;

表中插入数据
create table student(sid int, sname varchar(20), age int)

insert table student(sid,sname,age) values(001, “孙倩”, 18),(002, “孙俪”, 17),(003, “马超”, 19)；
insert table student values(004, “孙悟空”, 20);#插入全部字段值

insert table student(sname) values(“孙千”) #插入指定字段值

更改数据
update student set sname=“马冬梅”, age=17 where sid=002;

删除数据
delete from student; #删除所有数据（有多少条记录 就执行多少次删除操作）

delete from student where age=18;

truncate table student;#先删除整张表，再重新创建一张一模一样的表

查询

#执行顺序 from- where- group by-having- select-order by
select 字段 from 表 where 条件 ORDER BY 字段名;

#模糊查询
select 字段 from 表 where 字段 like ‘%精%’;#查询含有’精’字的信息

select 字段 from 表 where 字段 like ‘精%’;
#查询以’精’字开头的信息

select 字段 from 表 where 字段 like ‘_精%’;
#查询第二个字为’精’字的信息

#聚合函数–纵向查询，对某列的值进行计算，聚合函数会忽略控制
#count() sum() max() min() avg()
#!!! group by 的字段必须出现在前面select 的位置
#前面select的位置，除了group by的字段、聚合函数，不能出现其他的字段
#where与having的区别：where进行分组前的过滤，where后面不能写聚合函数；having是分组后的过滤，having后面可以写聚合函数

select 聚合函数(字段名) from 表名;

#指定行数查询limit

select 字段 from 表名 limit offset,length; #offset起始行数，若省略默认为0
#length 返回的行数
SELECT * FROM emp LIMIT 3 , 6; 查询emp表中 从第4条开始,查询6条

sql约束

主键primary key 唯一unique 非空not null 外键foreign key 、（默认值 自增）

#创建带主键的表
方式一
REATE TABLE emp2(
– 设置主键 唯一 非空
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1) not NULL
);

方式二
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
– 指定主键为 eid字段
PRIMARY KEY(eid)
);

#创建表未指定主键
alter table emp2 add primary key(eid);

#删除主键约束
alter table emp2 drop primary key;

#修改/自定义主键自增的起始值
REATE TABLE emp2(
– 设置主键 唯一 非空
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)auto_increment=100;

#DELETE和TRUNCATE对自增长的影响：
#DELETE只是删除表中所有数据，对自增没影响
#TRUNCATE是将整张表删除掉，然后创建一个新的表自增的主键，重新从1开始

#外键约束
#外键：数据表A中有一列，这一列指向了另外一张表B的主键；
#从表：外键所在的表，被约束的表；外键所指向的表叫主表；

#创建外键
1、建表时添加外键
#[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
constraint emp_dept_fk foreign key(dep_id) references department(id));

2、已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

3、删除外键
alter table employee drop foreign key emp_dept_fk;

#外键约束事项：
1、从表的外键类型必须与主表的外键类型一致，否则创建失败
2、添加数据时，要先添加主表的数据
3、删除数据时，要先删除从表的数据

#主表从表同时删除数据–级联删除on delete cascade
#建从表时添加级联操作
CREATE TABLE employee(
eid INT PRIMARY KEY auto_increemnt,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dep_fk FOREIGN KEY(dept_id) REFERENCES department(id)
on DELETE CASCADE);

多表查询

1、交叉查询—会产生笛卡尔积，基本不用
select 字段 from 表1, 表2;
SELECT * FROM category , products;

2、内连接查询
#隐式内连接
SELECT * FROM products,category WHERE category_id = cid;
#显示内连接
SELECT * FROM products p INNER JOIN category c WHERE p.category_id = c.cid;

3、外连接
#左外连接
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

#右外连接
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

4、合并查询
4.1、union
#union内部的select语句必须有相同数量的列，列也必须要相似的数据类型，每条select语句中的列的顺序必须相同，列的名称不必相同
SELECT Id,NAME,Amount,Date
FROM customers
LEFT JOIN orders
on customers.Id = orders.Customers_Id
UNION
SELECT Id,NAME,Amount,Date
from customers
RIGHT JOIN orders
on customers.Id = orders.Customers_Id;

4.2、UNION ALL
与union 运算符规则一致，但是查询结合组合在一起包含重复行

5、子查询
5.1、子查询的结果作为条件：where/from/exists型 子查询
select 字段 from where 字段=(select子查询);

5.2、子查询结果作为表
select 查询字段 from (子查询) 表别名 where 条件;
#注意：子查询作为一张表时要起别名，否则无法访问表中的字段

5.3、子查询结果是单列多行
select 查询字段 from 表 where 字段 in(子查询)；

sql函数

1、数学函数
ABS(X) 返回x的绝对值

FLOOR(X) 返回x的绝对值
ROUND(X)
RAND()返回0～1的随机数
PI() 返回圆周率的值
MOD(N,M) 返回N除以M以后的余数

2、字符串函数
CONCAT(str1,str2,…) 字符串拼接
LEFT(str,len) 返回从字符串s开始的n最左字符
TRIM(s) 除掉字串中s的字头或字尾处空格
REPLACE(s,s1,s2)字符串s2替代字符串s重的字符串s1
SUBSTRING(s,n,len) 截取字符串s中第n个位置开始，长度为len的字符串
MID(str,pos,len) 同SUBSTRING
REVERSE(s) 将字符串s的顺序翻转过来

3、日期时间函数
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW()
MONTH(date)
YEAR(date)
DAY(date)

4、条件判断函数
IF()
CASE WHEN

SELECT CASE WHEN 10>5 THEN 10
ELSE 5
END AS “最大值”

5、系统信息函数
VERSION() 数据库的版本号
DATABASES() 当前数据库名
USER() 当前用户名

窗口函数

函数名() over(PARTITION by 要分列的组 order by 要排序的列 rows between 数据范围)
计算的行数范围：
rows between 2 preceding and current row#取当前行和前面两行

rows between unbounded preceding and current row# 包括本行和之前所有的行

rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行

rows between current row and unbounded following# 包括本行和之后所有的行

1专有窗口函数
rank()
dense_rank()
row_number()

2聚合类窗口函数
sum()
count()
avg()
max()
min()

3排序函数
row_number() over(…)
rank() over(…)
dense_rank() over(…)
7.4结果分组
ntile(n) over(…)
#ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
#NTILE不支持ROWS BETWEEN，
7.5偏移函数
lag(…) over(…)
lead(…) over(…)
#Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为 独立的列

MySQL索引

1、主键索引
建表时添加主键索引
CREATE TABLE test_index(
did INT primary key,
dname VARCHAR(20),
hobby VARCHAR(30)
);
修改表结构 添加主键索引
alter table 表名 add primary key(列名)；

2唯一索引—保证数据记录的唯一性
建表时添加唯一索引
CREATE TABLE test_index(
did INT primary key,
dname VARCHAR(20),
hobby VARCHAR(30),
UNIQUE hobby_index (hobby)
);
使用create语句创建: 在已有的表上创建索引
create unique index 索引名 on 表名(列名);

修改表结构添加索引
alter table 表名 add unique 索引名(列名)；

3普通索引----普通索引的唯一任务是加快对数据的访问速度
使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名)；

修改表结构添加索引
alter table 表名 add index 索引名(列名)；

4删除索引
later table table_name drop index index_name;

视图

create view view_name as select语句；

正则表达式：



在 where like 的条件查询中，SQL 提供了四种匹配方式。

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

事务测试
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)

mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)

mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)

mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)

mysql> select * from runoob_transaction_test;
±-----+
| id |
±-----+
| 5 |
| 6 |
±-----+
2 rows in set (0.01 sec)

mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入
±-----+
| id |
±-----+
| 5 |
| 6 |
±-----+
2 rows in set (0.01 sec)

使用保留点 SAVEPOINT

savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。

ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。

使用 SAVEPOINT

SAVEPOINT savepoint_name; // 声明一个 savepoint

ROLLBACK TO savepoint_name; // 回滚到savepoint
删除 SAVEPOINT

保留点再事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放。

MySQL5 以来，可以用:

RELEASE SAVEPOINT savepoint_name; // 删除指定保留点

ALTER删除，添加或修改表字段
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：
mysql> ALTER TABLE testalter_tbl DROP i;如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:
mysql> ALTER TABLE testalter_tbl ADD i INT;执行以上命令后，i 字段会自动添加到数据表字段的末尾。
如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
修改字段类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;mysql> ALTER TABLE testalter_tbl CHANGE j j INT;ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。
mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。
修改字段默认值
你可以使用 ALTER 来修改字段的默认值，尝试以下实例：
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：
注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。
mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql> SHOW TABLE STATUS LIKE ‘testalter_tbl’\G 修改表名
如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl; alter其他用途：
修改存储引擎：修改为myisam
alter table tableName engine=myisam;删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;

用查询直接创建临时表的方式：

CREATE TEMPORARY TABLE 临时表名 AS
(
SELECT * FROM 旧的表名
LIMIT 0,10000
);

完整复制表的方法:
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
第一、只复制表结构到新表
create table 新表 select * from 旧表 where 1=2或者create table 新表 like 旧表
第二、复制表结构及数据到新表
create table新表 select * from 旧表

处理重复数据
可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( ‘Jay’, ‘Thomas’);

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( ‘Jay’, ‘Thomas’);

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);
统计重复数据
以下我们将统计表中 first_name 和 last_name的重复记录数：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
你也可以使用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
删除重复数据
如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
select 列名1，count（1） as count
from 表名
group by 列名1
having count>1 and 其他条件

select 列名1，列名2，count（1） as count
from 表名
group by 列名1，列名2
having count>1 and 其他条件
原理：先按照要查询出现重复数据的列，进行分组查询。count > 1 代表出现 2 次或 2 次以上。