SQL - 语法

发表于:，更新于:，By Sally
大纲
  1. 1. 查询语句
  2. 2. 模糊查询&通配符(wildcards)like
  3. 3. 插入语句insert
  4. 4. 更新语句update
  5. 5. 删除语句delete
  6. 6. sql注入SQL Injection
  7. 7. 别名sql aliases
  8. 8. 连接查询join - Inner Join Left Join Right Join full Join
  9. 9. 联合union
  10. 10. 将一张表的数据拷贝到另一张表or其他数据库的表中
    1. 10.1. 将数据拷贝到一张新表，根据教程，使用select into，会出错👇
    2. 10.2. 于是乎，搜索得如下方法
    3. 10.3. 将数据拷贝到已经存在的一张表中inesrt into select
  11. 11. 创建/删除数据库&数据表
  12. 12. 只是删除表中的数据，而不是表本身truncate
  13. 13. sql约束(Constraints)
  14. 14. 创建/删除索引index
  15. 15. 修改表alter
  16. 16. 创建视图View
  17. 17. SQL Function
    1. 17.1. SQL Aggregate Function
    2. 17.2. SQL Scalar Function

SQL : Structured Query Language

1
2
3
4
5
# 所有表的信息
select table status from database_name;
# 查询库中表的个数，
select database_name; show tables; select found_rows();
select count(*) from information_schema.tables where table_schema='database_name';

查询语句

  • 查询指定列select
1
2
select column_name, column_name from table_name;
select * from table_name;
  • 查询不重复的值distinct
1
select distinct column_name, column_name from table_name;
  • 查询满足条件的where
1
2
3
4
5
6
7
8
9
10
11
12
13
select column_name, column_name from table_name where colomn_name operator value;

# operator
`=` `<>` `>` `<` `>=` `<=` `between` `like` `in` `or` `and` `like`

# operator : and | or
select column_name from table_name where condition and (condition or condition);

# operator : in
select column_name from table_name where column in (value1, value2, value3);

# operator : between
select column_name from table_name where column_name between value1 and value2;
  • 对查询结果排序order by : asc|desc
1
select column_name, column_name from table_name order by column_name asc|desc, column_name asc|desc;
  • 查询(返回)指定数量的结果
1
2
3
4
5
6
7
8
# Sql Server / Access
select top number|percent column_name from table;

# MySql
select column_name from table limit number;

# Oracle
select column_name from table_name where cownum <= number;

模糊查询&通配符(wildcards)like

  • % 匹配0个货多个字符
  • _ 匹配1个字符
  • [charlist] 匹配集合
  • [^charlist] or [!charlist]
1
select * from table_name where column_name like pattern;

插入语句insert

1
2
insert into table_name values(value1, value2, value3);
insert into table_name (column1, column2, column3) values (value1, value2, value3);

更新语句update

1
update table_name set column1=value1, column2=value2 where some_column=some_value;

the where clause specifies which record or records that should be updated. if you omit the where clause, all records will be updated!

删除语句delete

1
2
3
4
5
delete from table_name where some_column = some_value;

# delete all data
delete from table_name;
delete * from table_name;

the where clause specifies which record or records that should be updated. if you omit the where clause, all records will be updated!

sql注入SQL Injection

  • SQL Injection based on 1=1 is always true
1
2
3
4
5
select * from table_name where some_column = some_value or 1 = 1;

# 1 = 1 | "" = "" are always true
eg: select user_name, password from Users where user_id = 11 or 1 = 1;
eg: select * from Users where name = "" or "" = "" and password = "" or "";
  • SQL Injection based on batched SQL statements (批处理sql语句，分号分隔)
1
2
3
4
textUserId = getRequestString("UserId");
textSQL = "select * from Users where UserId = " + textUserId;
eg: if textUserId = 11; drop table table_name;
sql = select * from Users where UserId = 11; drop table table_name;

别名sql aliases

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 字段别名
select column_name as alias_name from table_name;

# 表别名
select column_name from table_name as alias_name;

# 字段别名demo
select column_name as a, column_name as b from table_name;

# 将几个字段合起来使用别名
select column_name, column_name + ',' + column_name + ',' + column_name as alias_name from table_name;
# 这个功能在mysql中，需要用关键字`concat`
select column_name, concat(column_name, ',', column_name, ',', column_name) as alias_name from table_name;

# 表别名demo
select a.id, a.name, b.score from A_table as a, B_table as b where a.id = b.id;

aliases can be useful when:

  • There are more than one table involved in a query
  • Functions are used in the query
  • Column names are big or not very readable
  • Two or more columns are combined together

连接查询join - Inner Join Left Join Right Join full Join

  • Inner Join : returns all rows when there is at least one match in both tables
  • Left Join : return all rows from the left table, and the mached rows from the right table
  • Right Join : return all rows from the right table, and the mached rows from the left table
  • Full Join : return all rows when there is a match in one of the tables
1
2
3
4
5
6
7
8
9
10
11
12
13
# Inner Join
select column_name from table1 inner join table2 on table1.column_name = table2.column_name;

# Left Join
select column_name from table1 left join table2 on table1.column_name = table2.column_name;
select column_name from table1 left outer join table2 on table1.column_name = table2.column_name;

# Right Join
select column_name from table1 right join table2 on table1.column_name = table2.column_name;
select column_name from table1 right outer join table2 on table1.column_name = table2.column_name;

# Full Join
select column_name from table1 full outer join table2 on table1.column_name = table2.column_name;

联合union

1
2
3
select column_name from table1 union select column_name from table2;
# 默认情况下，union查询的数据是去重复的(distinct)，使用union all可以查询所有(包括重复数据)
select column_name from table1 union all select column_name from table2;

notice that each select statement within the union must have the same number of columns.
the columns must also have similar data types.
also, the columns in each select statement must be in the same order.

将一张表的数据拷贝到另一张表or其他数据库的表中

将数据拷贝到一张新表，根据教程，使用select into，会出错👇

the select int statement copies data from one table and insert it into a new table.

会报错：ERROR 1327 (42000): Undeclared variable: apn_devices_backup (apn_devices_backup是新的表名)

1
2
select * into new_table [in externaldb] from table1;
select column_name into new_table [in externaldb] from table1;

the new table will be created with the column_names and types as defined in the select statement. you can apply new names using the as clause.

  • demo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 创建一张表的副本
select * into UsersBackup from Users;

# 将一张表拷贝到另个数据库的表中
select * into UsersBackup in 'Backup.mdb' from Users;

# 只拷贝个别字段到新的表中
select UserId, UserName, UserPassword into UsersBackup from Users;

# 拷贝符合条件的数据到新表
select * into UsersBackup from Users where id > 100;

# 拷贝多张表的数据到一张新表
select Users.UserName, Class.ClassName into UserClassBackup from Users left join Class on Users.id = Class.UserId;

# select into 语句可以创建一张相同模式的新的空表，只要查询语句返回空即可
select * into new_table from table1 where 1 = 0;

于是乎，搜索得如下方法

解决方法，虽然关键字select into不可用，但是以上demo的例子还是成立的。替换关键字即可。

1
2
# 方法1
create table new_table select * from origin_table;

将数据拷贝到已经存在的一张表中inesrt into select

the insert into select statement copies data from one table and inserts it into an existing table. any existing rows in the target table are unaffected.

1
2
insert into table2 select * from table1;
insert into table2 (column_name, column_name...) select column_name, column_name ... from table1 [where condition];

创建/删除数据库&数据表

1
2
3
4
5
6
7
8
9
10
11
# database
create database dbname;
drop database dbname;

# table
create tablle table_name (
  column_name1 data_type(size),
  column_name2 data_type(size),
  column_name3 data_type(size),
);
drop table table_name;

只是删除表中的数据，而不是表本身truncate

1
truncate table table_name;

sql约束(Constraints)

constraints can be specified when the table is created (inside the create table statement) or after the table is created (inside the alter table statement).

  • not null : indicates that a column cannot store null value
  • unique : ensures that each row for a column must have a unique value
  • primary key : a combination of a not null and unique. ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
  • foreign key : ensure the referential integrity of the data in one table to match values in another table
  • check : ensures that the value in a column meets a specific condition
  • default : specifies a default value for a column
  • auto increment

note that you can have more unique constraints per table, but only one primary key constraint per table

  • 创建表时添加约束 SQL unique constraint on create table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# SQL Server/ Oracle / Access
create table table_name (
  _id int not null  unique  primary key,   #=> 非空，唯一，主键
  firstName varchar(255),
  lastName varchar(255) not null,
  other_id int foreign key references Other_table(_id)  #=> 外键
);

# MySql
create table table_name (
  _id int not null;                #=>  非空，主键
  firstName varchar(255),
  lastName varchar(255) not null,
  other_id int,                    #=>  外键
  unique (_id),                    #=>  唯一
  primary key (_id),               #=>  主键
  foreign key (other_id) references Other_table(_id)    #=> 外键
);

# 多个列使用唯一约束 MySql/ SQL Server/ Oracle/ Access
create table table_name (
  _id int not null;
  firstName varchar(255),
  lastName varchar(255) not null,
  constraint uc_id unique (_id, lastName)     #=> 非空
  constraint pk_table primary key (_id, lastname)  #=> 主键 there is only one primary key (pk_table), however, the value of the primary key is made up of two columns(_id + lastName)
  constraint fk_table foreign key (other_id) references Other_table(_id)  #=> 外键
);
  • 修改表时添加约束 SQL unique constraint on alert table
1
2
alter table Persons add unique (_id); | alter table Persons add primary key (_id);
alter table Persons add constraint uc_id unique (_id, lastName); | alter table Persons add constraint pk_id primary (_id, lastName);
  • 删除约束
1
2
3
4
5
# MySql
alter table Persons drop index uc_id; | alter table Persons drop primary key; | drop table  Persons drop foreign key fk_table;

# SQL Server/ Oracle/ Access
alter table Persons drop constraint uc_id; | alter table Persons drop constraint pk_table; | drop table Persons drop constraint fk_table;
  • check 约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# MySql
create table Persons (
  _id int not null,
  check (_id > 0)
);

# Sql Server/ Oracle /Access
create table Persons (
  _id int not null check (_id > 0),
);

# check作用在多个列
create table Persons (
  _id int not null,
  lastName varchar(255),
  sex int,
  constraint chk_person check (_id > 0 and sex > 18)
);

# 修改表时添加check
alter table Persons add check (_id > 0);
alter table Persons add constraint chk_person check (_id > 0 and sex > 18)

# 删除check约束
alter table Persons drop check chk_person;  #=> mysql
alter table Persons drop constraint chk_person; #=> sql server/ oracle / access

创建/删除索引index

updating a table with indexes takes more time than updating a table without(because the indexes also need to update).

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建索引
create index index_name on table_name (column_name);
create unique index index_name on table_name (column_name);

eg:
create index PIndex on Persons (lastName);
create index PIndex on Persons (lastName, FirstName);

# 删除索引
drop index index_name on table_name;    #=> access
drop index table_name.index_name;       #=> sql server
drop index index_name;                  #=> db2/oracle
alter table table_name drop index index_name;  #=> mysql

修改表alter

  • add a column in a table
1
2
3
4
5
6
7
8
9
10
alter table table_name add column datatype;

# sql server/ access
alter table table_name alter column column_name datatype;

# mysql / oracle(prior version 10G)
alter table table_name modify column column_name datatype;

# oracle 10G and later
alter table table_name modify column_name datatype;
  • delete a column in a table (notice that some database systems don’t allow deleting a column)
1
2
3
4
alter table table_name drop column column_name;

eg:
alter table Persons drop column DateOfBirth;
  • change data type
1
alter table Persons alter column DateOfBirth year;

notice that the ‘DateOfBirth’ column is now of type year and is going to hold a year in a two-digit or four-digit format column is now of type year and is going to hold a year in a two-digit or four-digit format.

创建视图View

a view is a virtual table.

1
2
3
4
5
6
7
8
9
10
11
# 创建视图
create view view_name as select column_name(s) from table_name where condition;

# 查看视图
select * from view_name where condition;

# 更新视图
create or replace view view_name as select column_name(s) from table_name where condition;

# 删除视图
drop view view_name;

SQL Function

SQL Aggregate Function

  • avg() : returns ths average value
  • count() : returns the number of rows
  • first() : returns the first value
1
2
3
4
5
6
7
8
9
10
11
# access
select first(column_name) from table_name;

# sql server
select top 1 column_name from table_name order by column_name asc;

# mysql
select column_name from table_name order by column_name asc limit 1;

# oracle
select column_name from table_name where rownum <= 1 order by column_name asc;
  • last() : returns the last value
1
2
3
4
5
6
7
8
9
10
11
# access
select last(column_name) from table_name;

# sql server
select top 1 column_name from table_name order by column_name desc;

# mysql
select column_name from table_name order by column_name desc limit 1;

# oracle
select column_name from table_name order by column_name desc where rownum <= 1;
  • max() : returns the larget value
  • min() : returns the smallest value
  • sum() : returns the sum
  • group by
1
select column_name, sum(column_name) from table_name group by column_name;
  • having : the having clause was added to SQL because the where keyword cloud not be used with aggregate functions
1
select column_name, aggregate_function(column_name) from table_name where column_name operate value group by column_name having aggregate_function(column_name) operate value;

SQL Scalar Function

  • ucase() : converts a field to upper case
  • lcase() : converts a field to lower case
  • mid() : extract characters from a text field
1
2
3
4
select mid(column_name, start, length) as some_name from table_name;
- column_name : required. the field to extract characters from
- start       : required. specifies the starting position (start at 1)
- length      : optional. the number of characters to return. if omitted, the mid() function returns the rest of the text
  • len() : returns the length of a text field
1
2
3
select len(column_name) from table_name;
# 注意，在mysql中是length()函数
select length(column_name) from table_name;
  • round() : rounds a numeric field to the number of decimals specified
  • now() : returns the current system date and time
1
select column_name, now() from table_name;
  • format(): formats how a field is to be displayed
1
select format(column_name, format) from table_name;