SQL语句之增删改查及用户权限

sql MySQL

Word count: 1.6kReading time: 7 min

 2020/10/13   Share

什么是SQL

SQL(发音为S-Q-L或sequel)是Structured Query Language(结构化查询语言)的缩写，是一种专门用来与数据库沟通的语言。

检索(查)

初步检索

单个列的检索
1
2
SELECT prod_name
FROM Products;
从Products表中获取prod_name列的信息
多个列的检索
1
2
SELECT prod_name,prod_id,prod_price
FROM Products;
从Products中获取prod_name,prod_id,prod_price等列的信息
检索所有列
1
2
SELECT *
FROM Products;
给定通配符(*)则返回所有的列
只返回不同结果
1
2
SELECT DISTINCT *
FROM Products;
注释使用

单行注释

1 2	SELECT prod_name --注释君~ FROM Products;

多行注释

/*SELECT prod_name,prod_id
FROM Products;*/
SELECT prod_name
FROM Products;

排序

按列排序

1
2
3

SELECT prod_name(,prod_price,prod_name)
FROM Products
ORDER BY prod_price(,prod_name);

按一列或者多列来排序

按列位置来排序

1
2
3

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2,3;

指定排序方向
默认是升序排序(ASC),降序是DESC

1
2
3

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC;

过滤数据

WHERE子句

1
2
3

SELECT prod_name,prod_price
FROM Products
WHERE prod_price = 3.49;

WHERE子句操作符

示例

1
2
3

SELECT prod_name,prod_price
FROM Products
WHERE prod_price < 3.49;

范围值检查

1
2
3

SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

高级数据过滤

AND操作符

1
2
3

SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = "DLL01" AND prod_price <= 4;

OR操作符

1
2
3

SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = "DLL01" OR prod_price <= 4;

在前一个条件满足时不计算后一个条件

组合使用

SELECT prod_name,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
	AND prod_price >= 10

如果不使用括号，则AND操作符的优先级比OR高

IN操作符

SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;

IN操作符比OR操作符执行的更快

NOT操作符

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

使用通配符进行过滤

LIKE表示模糊查询

百分号(%)通配符
%表示任意字符出现任意次数
1
2
3
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
该语句表示搜索Fish开头的词，也可以用在句中’Fi%h’

下划线(_)通配符
_只能匹配一个字符

1
2
3

SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'F_sh';

方括号([])通配符
方括号通配符指定一个字符集，必须匹配指定位置的一个字符

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

表示J或M开头的字符
Tips:
^表示非

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

表示非J或M开头的字符

创建计算字段

拼接字段

1
2
3

SELECT concat('(',prod_name,prod_id,')') as prod //使用别名
FROM Products
ORDER BY prod_name;

执行算术计算

1
2
3

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

函数

文本处理函数

有趣的SOUNDEX函数
SOUNDEX可以匹配所有发音类似的字符串
1
2
3
SELECT cust_name,cust_concat
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
日期和时间处理函数
TIME(),DATATIME(),TIMESTAMP(),DATE(),YEAR()
数值处理函数

改(更新&插入)

改数据

INSERT插入数据

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address
                      )
VALUES('10000000006',
        'Toy Land',
        '123 Any Street'
        )

插入检索出的数据

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address
                      )
SELECT cust_id,
       cust_name,
       cust_email
FROM CustNew;

从一个表复制到另一个表

1 2	CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新数据

UPDATE Customers
SET cust_contact = 'Sam Roberts'
    cust_email = 'sam@tony.com'
WHERE cust_id = '1000000006';

当需要删除值时可以SET为NULL

改表

加列

1 2	ALTER TABLE Vendors ADD vend_phone CHAR(20);

删除列

1 2	ALTER TABLE Vendors DROP COLUMN vend_phone

重命名表
DB2、MariaDB、MySQL、Oracle和PostgreSQL使用RENAME，SQL Server使用sp_rename存储过程，SQLite使用ALTER TABLE语句

删

从表中删除一行

1 2	DELETE FROM Customers WHERE cust_id = '100000000006';

删除表
1
DROP TABLE CustCopy

增(创建表、数据库)

创建表

CREATE TABLE Products
(
	prod_id		CHAR(10)	NOT NULL	DEFAULT 1,
	vend_id		CHAR(10)	NOT NULL,
	prod_name	CHAR(10)	NOT NULL,
	prod_price	CHAR(10)	NOT NULL,
	prod_desc	CHAR(10)	NOT NULL
)engine=innodb default charset utf8;

DEFAULT一般用于日期或者时间戳

键约束等内容参考SQL必知必会

用户权限

1、创建新用户

　　通过root用户登录之后创建

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" ;　　//　　创建新用户，用户名为testuser，密码为123456 ；

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，可以在本地访问mysql

　　>> grant all privileges on *.* to testuser@"%" identified by "123456" ;　　　//　　设置用户testuser，可以在远程访问mysql

　　>> flush privileges ;　　//　　mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效

　　2、设置用户访问数据库权限

　　>> grant all privileges on test_db.* to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，只能访问数据库test_db，其他数据库均不能访问 ；

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，可以访问mysql上的所有数据库 ；

　　>> grant all privileges on test_db.user_infor to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，只能访问数据库test_db的表user_infor，数据库中的其他表均不能访问 ；

　　3、设置用户操作权限

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" WITH GRANT OPTION ;　　//设置用户testuser，拥有所有的操作权限，也就是管理员 ；

　　>> grant select on *.* to testuser@localhost identified by "123456" WITH GRANT OPTION ;　　//设置用户testuser，只拥有【查询】操作权限 ；

　　>> grant select,insert on *.* to testuser@localhost identified by "123456"  ;　　//设置用户testuser，只拥有【查询\插入】操作权限 ；

　　>> grant select,insert,update,delete on *.* to testuser@localhost identified by "123456"  ;　　//设置用户testuser，只拥有【查询\插入】操作权限 ；

　　>> REVOKE select,insert ON what FROM testuser　　//取消用户testuser的【查询\插入】操作权限 ；

　　4、设置用户远程访问权限

1	>> grant all privileges on . to testuser@“192.168.1.100” identified by "123456" ;　　//设置用户testuser，只能在客户端IP为192.168.1.100上才能远程访问mysql ；

　　5、关于root用户的访问设置

　　设置所有用户可以远程访问mysql，修改my.cnf配置文件，将bind-address = 127.0.0.1前面加“#”注释掉，这样就可以允许其他机器远程访问本机mysql了；

　　>> grant all privileges on *.* to root@"%" identified by "123456" ;　　　//　　设置用户root，可以在远程访问mysql

　　>> select host,user from user; 　　//查询mysql中所有用户权限

　　关闭root用户远程访问权限

　　>> delete from user where user="root" and host="%" ;　　//禁止root用户在远程机器上访问mysql

　　>> flush privileges ;　　//修改权限之后，刷新MySQL的系统权限相关表方可生效

Mysql8.0修改用户密码