Users表
id | name |
---|---|
int | varchar(128) |
Book表
id | name | user_id |
---|---|---|
int | varchar(128) | int |
SQL基础
SQL
一般不区分大小写。- 条件语句中的文本使用单引号环绕;大多数数据库系统也支持双引号,数值类不使用引号。
- 逻辑判断使用:
and
和or
- order by:根据指定的列对结果集进行排序,默认按照升序对记录进行排序;降序使用
desc
关键字。
select id from users where name='李明' order by id desc,name asc
- update:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
- insert:
INSERT INTO 表名称 VALUES (值1, 值2,....)
- delete:
DELETE FROM 表名称 WHERE 列名称 = 值
- 数据库语言包括数据操作语言 (
DML
) 和 数据定义语言 (DDL
)。
SQL进阶
数据库操作语言 DML
- 规定要返回的记录的数目
SQL SERVER
:使用TOP
子句返回。MySQL
:使用limit
关键字。
select * from users limit 5;//mysql 返回五条数据
select top 5 * from users;//sqlServer 返回前五条数据
select top 5 percent * from users;//sqlServer 返回前5%的数据记录
- Like 操作符用于在 WHERE 子句中搜索列中的指定模式。
- 其中
%
替代一个或多个字符 _
仅替代一个字符。NOT
关键字选择没有匹配到的数据记录。[charlist]
字符列中的任何单一字符,mysql中无效[^charlist]或者[!charlist]
不在字符列中的任何单一字符,mysql中无效
- 其中
select * from users where name like '李%';//返回名字姓李的数据记录,姓名长度任意
select * from users where name like '李_';//返回名字姓李,且姓名长度为2的数据记录
select * from users where name not like '%李%';//返回姓名中不含李字的数据记录
select * from users where name like '[李张]%';//返回姓李或姓张的记录
select * from users where name like '[!李张]%';//返回不姓李或张的记录
- in操作符指条件符合给定的范围
select * from users where name in ('李明','张三');//返回姓名为张三和李明的数据记录。
- between…and… 条件为介于两个值之间,可以是数值、文本和日期。使用
not
关键字表示不在该区间。
select * from users where name between 'Andy' and 'Woody';//返回name字段字母序在Andy和Woody之间的记录,是否返回端点边界记录根据数据库的不同而不同。
- as取别名,可省略。包括对表名和列名取别名。
select id as '编号' from users;
- inner join内连接: 从两个或多个表中返回满足连接条件的所有行。
至少匹配一行数据返回结果集,否则为空集
select * from users,book where users.id=book.user_id;//等同于下面的语句
select * from users inner join book where users.id=book.user_id;
+----+-------+-----+--------------------+----+------+---------+
| id | name | age | email | id | name | user_id |
+----+-------+-----+--------------------+----+------+---------+
| 1 | Jone | 18 | test1@baomidou.com | 1 | 语文 | 1 |
| 2 | Jack | 20 | test2@baomidou.com | 2 | 数学 | 2 |
| 1 | Jone | 18 | test1@baomidou.com | 3 | 英语 | 1 |
| 3 | Tom | 28 | test3@baomidou.com | 4 | 地理 | 3 |
| 4 | Sandy | 21 | test4@baomidou.com | 5 | 事务 | 4 |
| 3 | Tom | 28 | test3@baomidou.com | 6 | 生物 | 3 |
+----+-------+-----+--------------------+----+------+---------+
- left join左连接:会从左表那里返回所有的行,即使在右表中没有匹配的行。也称为左外连接(left outer join)
select * from users left join book on users.id=book.user_id;//返回user表的所有行,
若某行在book表中没有匹配也返回,且该行的book表内容为空或NULL。
+----+--------+-----+--------------------+------+------+---------+
| id | name | age | email | id | name | user_id |
+----+--------+-----+--------------------+------+------+---------+
| 1 | Jone | 18 | test1@baomidou.com | 1 | 语文 | 1 |
| 1 | Jone | 18 | test1@baomidou.com | 3 | 英语 | 1 |
| 2 | Jack | 20 | test2@baomidou.com | 2 | 数学 | 2 |
| 3 | Tom | 28 | test3@baomidou.com | 4 | 地理 | 3 |
| 3 | Tom | 28 | test3@baomidou.com | 6 | 生物 | 3 |
| 4 | Sandy | 21 | test4@baomidou.com | 5 | 事务 | 4 |
| 5 | Billie | 24 | test5@baomidou.com | NULL | NULL | NULL |
+----+--------+-----+--------------------+------+------+---------+
- right join右连接:从右表返回所有的行,即使在左表中没有匹配的行。也称为右外连接(right outer join)
select * from users right join book on users.id=book.user_id;//与左外连接类似,若改为book right join user结果与左外连接一样
- full join 全连接:会从左表和右表那里返回所有的行。如果 “users” 中的行在表 “book” 中没有匹配,或者如果 “book” 中的行在表 “users” 中没有匹配,这些行同样会列出。Mysql不支持
select * from users full join book on users.id=book.user_id;
id name id name user_id
----------- ---------- ----------- ---------- -----------
1 张三 1 语文 1
1 张三 2 数学 1
2 李四 3 地理 2
3 李明 4 科学 3
4 王五 5 英语 4
5 钱数据 NULL NULL NULL
6 黄河 NULL NULL NULL
- union联合查询:用于合并两个或多个
SELECT
语句的结果集UNION
内部的SELECT
语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条SELECT
语句中的列的顺序必须相同。UNION
操作符选取不同的值。如果允许重复的值,使用UNION ALL
,但UNION ALL
命令会列出所有的值。UNION
结果集中的列名总是等于UNION
中第一个SELECT
语句中的列名。
select id '编号' from users union select id from book;
编号
-----------
1
2
3
4
5
6
select id '编号' from users union all select id from book;
编号
-----------
1
2
3
4
5
6
1
2
3
4
5
- select into从一个表中选取数据,然后把数据插入另一个表中;
IN
子句可用于向另一个数据库中拷贝表。若待插入表不存在将会自动创建该表。
select users.name user_name,book.name as book_name
into users_book
from users
full join book
on users.id=book.user_id;
"users_book表"
user_name book_name
---------- ----------
张三 语文
张三 数学
李四 地理
李明 科学
王五 英语
钱数据 NULL
黄河 NULL
数据库定义语言 DDL
- 创建数据库:
create database 数据库名
- 创建表:
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
- 属性约束
constraint
not null
:不能为空。unique
:唯一约束。
1. MySQL中应为unique(name),SqlServer中为name varchar(128) unique
2. 为多个列定义 UNIQUE 约束:CONSTRAINT uc_name UNIQUE (name,email)
3. 添加约束:alter table users add unique(name)
4. 添加多个约束:alter table users add constraint unc_name_email unique(name,email)
5. 删除约束:alter table users drop unique(name)
-
primary key
:定义主键。
规则与unique约束一致:如mysql中为primary key(id);SqlServer中为id int primary key;
-
foreign key
:外键约束constraint fk_useid foreign key(user_id) references Users(id)
check
:约束某列值的输入。constraint ck_name check (name>'Andy')
表示name字段的值大于Andy
;也可(Mysql中)写为name varchhar(128) check (name>'Andy')
default
:默认值
以上约束用法一致。
-
index索引创建:加快搜索速度,但更新时耗时长因为需要更新索引。
- 简单索引:允许使用重复值
create index index_name on table_name(column_name)
- 唯一索引:不允许使用重复值
create unique index index_name on table_name(column_name)
- 简单索引:允许使用重复值
create index idx_name on users(name);//以name创建索引
create index idx_name_order on users(name desc);//以name降序创建索引
create index idx_name_email on users(name,email);//创建多个索引
- drop:删除操作
1.删除索引
MySQL:alter table table_name drop index index_name;
SQLServer:drop index table_name.index_name;
2.删除数据库
drop database 数据库名
3.删除表
drop table 表名
4.不删除表,仅清除表中的数据
truncate table table_name;
- view视图创建:视图是基于基本表存在的一张面向用户的表,它并不实际存在。
1.创建视图
create view view_name as
select column_names from table_name;//select语句
2.查询视图
select * from view_name;
3.删除视图
drop view view_name;
- 创建用户、授权与收回以及密码设置:具体
MySQL本身带有user表为登录用户表
1.创建用户
create user 'user_name'@'host' identified by 'password';
//user_name:用户名 host:指定登录的ip地址 password:登录密码
create user 'user_name'@'%' //表示任意地址登陆,且未设置密码
2.授权用户
GRANT privileges ON databasename.tablename TO 'username'@'host'
GRANT ALL ON *.* TO ‘aaa’@‘%’;//表示给用户aaa授权,让aaa能给所有库所有表实行所有的权力。
- group by分组:将属于同一组(指定属性)的记录合并。
//按名字分类
select users.name,count(*) as nums
from users,book
where users.id=book.user_id
group by users.name;
name nums
---------- -----------
admin 2
bbb 1
ccc 1
ddd 1
- having子句对分组后的记录进行筛选
select users.name,count(*) as nums
from users,book
where users.id=book.user_id
group by users.name
having count(*)>1;
name nums
---------- -----------
admin 2
- 常用函数
1. avg(column):返回某列的平均值。
2. min(column):返回某列最小值。
3. max(column):返回某列最大值。
4. count(column):返回某列的行数(不包括NULL值)。
5. count(*):返回被选行数。
6. count(distinct column):返回相异的记录数。(sqlserver支持)
7. sum(column):返回某列的总和。
8. first(column):返回某列的第一行数据。(可以先使用order by子句进行排序)。
9. last(column):返回某列最后一行数据。
10. mid(column_name,start,length):从列名为column_name的记录中取出第start个字开始长度为length的文本。(start起始值为1)。
11. len(column):返回文本长度。
12. round(column_name,decimals):规定小数位。
13. now():返回当前时间。
14. format(column_name,format):对文本进行格式化。如format(now(),"YYYY-MM-DD"),日期格式输出7/1/2019
Users表
id | name |
---|---|
int | varchar(128) |
Book表
id | name | user_id |
---|---|---|
int | varchar(128) | int |
SQL基础
SQL
一般不区分大小写。- 条件语句中的文本使用单引号环绕;大多数数据库系统也支持双引号,数值类不使用引号。
- 逻辑判断使用:
and
和or
- order by:根据指定的列对结果集进行排序,默认按照升序对记录进行排序;降序使用
desc
关键字。
select id from users where name='李明' order by id desc,name asc
- update:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
- insert:
INSERT INTO 表名称 VALUES (值1, 值2,....)
- delete:
DELETE FROM 表名称 WHERE 列名称 = 值
- 数据库语言包括数据操作语言 (
DML
) 和 数据定义语言 (DDL
)。
SQL进阶
数据库操作语言 DML
- 规定要返回的记录的数目
SQL SERVER
:使用TOP
子句返回。MySQL
:使用limit
关键字。
select * from users limit 5;//mysql 返回五条数据
select top 5 * from users;//sqlServer 返回前五条数据
select top 5 percent * from users;//sqlServer 返回前5%的数据记录
- Like 操作符用于在 WHERE 子句中搜索列中的指定模式。
- 其中
%
替代一个或多个字符 _
仅替代一个字符。NOT
关键字选择没有匹配到的数据记录。[charlist]
字符列中的任何单一字符,mysql中无效[^charlist]或者[!charlist]
不在字符列中的任何单一字符,mysql中无效
- 其中
select * from users where name like '李%';//返回名字姓李的数据记录,姓名长度任意
select * from users where name like '李_';//返回名字姓李,且姓名长度为2的数据记录
select * from users where name not like '%李%';//返回姓名中不含李字的数据记录
select * from users where name like '[李张]%';//返回姓李或姓张的记录
select * from users where name like '[!李张]%';//返回不姓李或张的记录
- in操作符指条件符合给定的范围
select * from users where name in ('李明','张三');//返回姓名为张三和李明的数据记录。
- between…and… 条件为介于两个值之间,可以是数值、文本和日期。使用
not
关键字表示不在该区间。
select * from users where name between 'Andy' and 'Woody';//返回name字段字母序在Andy和Woody之间的记录,是否返回端点边界记录根据数据库的不同而不同。
- as取别名,可省略。包括对表名和列名取别名。
select id as '编号' from users;
- inner join内连接: 从两个或多个表中返回满足连接条件的所有行。
至少匹配一行数据返回结果集,否则为空集
select * from users,book where users.id=book.user_id;//等同于下面的语句
select * from users inner join book where users.id=book.user_id;
+----+-------+-----+--------------------+----+------+---------+
| id | name | age | email | id | name | user_id |
+----+-------+-----+--------------------+----+------+---------+
| 1 | Jone | 18 | test1@baomidou.com | 1 | 语文 | 1 |
| 2 | Jack | 20 | test2@baomidou.com | 2 | 数学 | 2 |
| 1 | Jone | 18 | test1@baomidou.com | 3 | 英语 | 1 |
| 3 | Tom | 28 | test3@baomidou.com | 4 | 地理 | 3 |
| 4 | Sandy | 21 | test4@baomidou.com | 5 | 事务 | 4 |
| 3 | Tom | 28 | test3@baomidou.com | 6 | 生物 | 3 |
+----+-------+-----+--------------------+----+------+---------+
- left join左连接:会从左表那里返回所有的行,即使在右表中没有匹配的行。也称为左外连接(left outer join)
select * from users left join book on users.id=book.user_id;//返回user表的所有行,
若某行在book表中没有匹配也返回,且该行的book表内容为空或NULL。
+----+--------+-----+--------------------+------+------+---------+
| id | name | age | email | id | name | user_id |
+----+--------+-----+--------------------+------+------+---------+
| 1 | Jone | 18 | test1@baomidou.com | 1 | 语文 | 1 |
| 1 | Jone | 18 | test1@baomidou.com | 3 | 英语 | 1 |
| 2 | Jack | 20 | test2@baomidou.com | 2 | 数学 | 2 |
| 3 | Tom | 28 | test3@baomidou.com | 4 | 地理 | 3 |
| 3 | Tom | 28 | test3@baomidou.com | 6 | 生物 | 3 |
| 4 | Sandy | 21 | test4@baomidou.com | 5 | 事务 | 4 |
| 5 | Billie | 24 | test5@baomidou.com | NULL | NULL | NULL |
+----+--------+-----+--------------------+------+------+---------+
- right join右连接:从右表返回所有的行,即使在左表中没有匹配的行。也称为右外连接(right outer join)
select * from users right join book on users.id=book.user_id;//与左外连接类似,若改为book right join user结果与左外连接一样
- full join 全连接:会从左表和右表那里返回所有的行。如果 “users” 中的行在表 “book” 中没有匹配,或者如果 “book” 中的行在表 “users” 中没有匹配,这些行同样会列出。Mysql不支持
select * from users full join book on users.id=book.user_id;
id name id name user_id
----------- ---------- ----------- ---------- -----------
1 张三 1 语文 1
1 张三 2 数学 1
2 李四 3 地理 2
3 李明 4 科学 3
4 王五 5 英语 4
5 钱数据 NULL NULL NULL
6 黄河 NULL NULL NULL
- union联合查询:用于合并两个或多个
SELECT
语句的结果集UNION
内部的SELECT
语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条SELECT
语句中的列的顺序必须相同。UNION
操作符选取不同的值。如果允许重复的值,使用UNION ALL
,但UNION ALL
命令会列出所有的值。UNION
结果集中的列名总是等于UNION
中第一个SELECT
语句中的列名。
select id '编号' from users union select id from book;
编号
-----------
1
2
3
4
5
6
select id '编号' from users union all select id from book;
编号
-----------
1
2
3
4
5
6
1
2
3
4
5
- select into从一个表中选取数据,然后把数据插入另一个表中;
IN
子句可用于向另一个数据库中拷贝表。若待插入表不存在将会自动创建该表。
select users.name user_name,book.name as book_name
into users_book
from users
full join book
on users.id=book.user_id;
"users_book表"
user_name book_name
---------- ----------
张三 语文
张三 数学
李四 地理
李明 科学
王五 英语
钱数据 NULL
黄河 NULL
数据库定义语言 DDL
- 创建数据库:
create database 数据库名
- 创建表:
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
- 属性约束
constraint
not null
:不能为空。unique
:唯一约束。
1. MySQL中应为unique(name),SqlServer中为name varchar(128) unique
2. 为多个列定义 UNIQUE 约束:CONSTRAINT uc_name UNIQUE (name,email)
3. 添加约束:alter table users add unique(name)
4. 添加多个约束:alter table users add constraint unc_name_email unique(name,email)
5. 删除约束:alter table users drop unique(name)
-
primary key
:定义主键。
规则与unique约束一致:如mysql中为primary key(id);SqlServer中为id int primary key;
-
foreign key
:外键约束constraint fk_useid foreign key(user_id) references Users(id)
check
:约束某列值的输入。constraint ck_name check (name>'Andy')
表示name字段的值大于Andy
;也可(Mysql中)写为name varchhar(128) check (name>'Andy')
default
:默认值
以上约束用法一致。
-
index索引创建:加快搜索速度,但更新时耗时长因为需要更新索引。
- 简单索引:允许使用重复值
create index index_name on table_name(column_name)
- 唯一索引:不允许使用重复值
create unique index index_name on table_name(column_name)
- 简单索引:允许使用重复值
create index idx_name on users(name);//以name创建索引
create index idx_name_order on users(name desc);//以name降序创建索引
create index idx_name_email on users(name,email);//创建多个索引
- drop:删除操作
1.删除索引
MySQL:alter table table_name drop index index_name;
SQLServer:drop index table_name.index_name;
2.删除数据库
drop database 数据库名
3.删除表
drop table 表名
4.不删除表,仅清除表中的数据
truncate table table_name;
- view视图创建:视图是基于基本表存在的一张面向用户的表,它并不实际存在。
1.创建视图
create view view_name as
select column_names from table_name;//select语句
2.查询视图
select * from view_name;
3.删除视图
drop view view_name;
- 创建用户、授权与收回以及密码设置:具体
MySQL本身带有user表为登录用户表
1.创建用户
create user 'user_name'@'host' identified by 'password';
//user_name:用户名 host:指定登录的ip地址 password:登录密码
create user 'user_name'@'%' //表示任意地址登陆,且未设置密码
2.授权用户
GRANT privileges ON databasename.tablename TO 'username'@'host'
GRANT ALL ON *.* TO ‘aaa’@‘%’;//表示给用户aaa授权,让aaa能给所有库所有表实行所有的权力。
- group by分组:将属于同一组(指定属性)的记录合并。
//按名字分类
select users.name,count(*) as nums
from users,book
where users.id=book.user_id
group by users.name;
name nums
---------- -----------
admin 2
bbb 1
ccc 1
ddd 1
- having子句对分组后的记录进行筛选
select users.name,count(*) as nums
from users,book
where users.id=book.user_id
group by users.name
having count(*)>1;
name nums
---------- -----------
admin 2
- 常用函数
1. avg(column):返回某列的平均值。
2. min(column):返回某列最小值。
3. max(column):返回某列最大值。
4. count(column):返回某列的行数(不包括NULL值)。
5. count(*):返回被选行数。
6. count(distinct column):返回相异的记录数。(sqlserver支持)
7. sum(column):返回某列的总和。
8. first(column):返回某列的第一行数据。(可以先使用order by子句进行排序)。
9. last(column):返回某列最后一行数据。
10. mid(column_name,start,length):从列名为column_name的记录中取出第start个字开始长度为length的文本。(start起始值为1)。
11. len(column):返回文本长度。
12. round(column_name,decimals):规定小数位。
13. now():返回当前时间。
14. format(column_name,format):对文本进行格式化。如format(now(),"YYYY-MM-DD"),日期格式输出7/1/2019