MySQL笔记

安装、启动

Linux上安装与启动MySQL

MySQL 安装 | 菜鸟教程

初始化 MySQL：

mysqld --initialize

启动 MySQL：

systemctl start mysqld

查看 MySQL 运行状态：

systemctl status mysqld

注意：如果我们是第一次启动 mysql 服务，mysql 服务器首先会进行初始化的配置。

登录、退出

安装好mysql，进入命令提示符后，键入

mysql -uroot -p

输入根密码，即可登录。

在windows系统中，每次计算机启动时自动启动Mysql数据库服务器，输入命令net stop mysql可以终止它，输入net start mysql可以重启它。

退出mysql控制台 

exit;

用账号登录

mysql -uscott -ptiger

用户管理

MySQL必知必会，MySQL 管理 | 菜鸟教程

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表，只有在需要获得所有用户账号列表时才会访问。mysql数据库有一个名为user的表，它包含所有用户账号。 user表有一个名为user的列，它存储用户登录名。新安装的服务器可能只有一个用户。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customers          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
......
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select user from user;#用此行获取用户列表
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
3 rows in set (0.00 sec)

创建用户并设置权限

方法1：使用create user及grant语句

创建用户

create user username identified by 'password123';

创建用户账号时不一定需要密码,这里设置了密码。IDENTIFIED BY指定的口令为纯文本， MySQL
将在保存到user表之前对其进行加密。
在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

mysql> create user testuser identified by 'password123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user from user;
+---------------+
| user          |
+---------------+
| testuser      |
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
4 rows in set (0.00 sec)

mysql> show grants for testuser;#使用此行查看权限，下面输出表示没有任何权限
+--------------------------------------+
| Grants for testuser@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

用户定义为user@host。MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。可使用如下命令查看用户及其连接时可用的主机名(问题记录: 远程连接Linux中MySQL数据库失败的解决方法_linux mysql 无法远程连接-CSDN博客)：

mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | 123456  |
| %         | testuser      |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host='%' where user='root';#更改root的host
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | 541260028221  |
| %         | root          |
| %         | testuser      |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
5 rows in set (0.00 sec)

设置权限

为设置权限，使用GRANT语句。 GRANT要求你至少给出以下信息：

• 要授予的权限；
• 被授予访问权限的数据库或表；
• 用户名。

以下例子给出GRANT的用法：

#为testuser添加customers数据库中所有表的select权限
mysql> grant select on customers.* to testuser;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for testuser;#
+-------------------------------------------------+
| Grants for testuser@%                           |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'            |
| GRANT SELECT ON `customers`.* TO 'testuser'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

撤销权限

GRANT的反操作为REVOKE，用它来撤销特定的权限。被撤销的权限必须存在，否则会出错。

revoke select on customers.* from testuser;

GRANT和REVOKE可在几个层次上控制访问权限：

•  整个服务器，使用GRANT ALL和REVOKE ALL；
•  整个数据库，使用ON database.*；
•  特定的表，使用ON database.table；
•  特定的列；
•  特定的存储过程。

下表列出可以授予或撤销的权限： 

权限	说明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE

CREATE USER	使用CREATE USER、 DROP USER、 RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	使用CREATE VIEW
DELETE	使用DELETE
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用

SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown（用来关闭MySQL）
SUPER	使用CHANGE MASTER、 KILL、 LOGS、 PURGE、 MASTER 和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

使用GRANT和REVOKE，再结合表中列出的权限，你能对用户可以就你的宝贵数据做什么事情和不能做什么事情具有完全的控制。
未来的授权。在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。
简化多次授权。可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如下所示：

mysql> grant select,insert on customers.* to testuser;

创建用户及设置权限写在一起

以上先创建用户再设置权限，也可以将二者写在一起，以下命令会给指定数据库TUTORIALS添加用户 zara ，密码为 zara123 。

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

 报错

MySQL 8.0 解决：ERROR 1819 密码策略修改 - 知乎,完美解决ERROR 1819 (HY000): Your password does not satisfy the current policy requirements-CSDN博客

有时会报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，提示设置的密码不符合密码策略。查看密码策略：

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |#指定密码验证的文件路径；
| validate_password_length             | 8      |#密码最小长度，默认为8；
| validate_password_mixed_case_count   | 1      |#密码至少要包含大/小写字母的总个数；
| validate_password_number_count       | 1      |#密码至少要包含数字的个数；
| validate_password_policy             | MEDIUM |#密码的强度验证等级，默认为 MEDIUM；
| validate_password_special_char_count | 1      |#密码至少要包含特殊字符的个数；
+--------------------------------------+--------+
7 rows in set (0.00 sec)  

以上输出说明当前策略为MEDIUM，密码长度至少为8，且包含大（小）写字母（一种即可）、数字及特殊字符至少各一个。不同策略的验证内容如下：

• 0/LOW         #只验证长度；
• 1/MEDIUM    #验证长度、数字、大小写、特殊字符；
• 2/STRONG   #验证长度、数字、大小写、特殊字符、字典文件；

 修改密码验证策略为LOW尝试：

set global validate_password_policy=LOW;

此时可以设置密码长度不小于8即可。

如果想设置更短的长度，可以更改设置长度参数：

set global validate_password_length=6;#将密码最小长度设为6

 其他参数也可以照此设置。

 方法2：使用insert into user语句

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

 在添加用户时，请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 你可以在以上实例看到用户密码加密后为： 6f8c114b58f2ce9e.

注意：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。

注意：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。

注意：在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。

如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。

你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 'Y' 即可，用户权限列表如下：

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

 方法3：

重命名用户

rename user username1 to username2;

仅MySQL 5或之后的版本支持RENAME USER。为了在以前的MySQL中重命名一个用户，可使用UPDATE直接更新user表。 

更改密码（口令）

为了更改用户口令，可使用SET PASSWORD语句。新口令必须传递到Password()函数进行加密。

set password for testuser=Password('newpassword');

SET PASSWORD还可以用来设置你自己的口令：

set password =Password('newpassword');

在不指定用户名时， SET PASSWORD更新当前登录用户的口令。

删除用户 

drop user username;

自MySQL 5以来， DROP USER删除用户账号和所有相关的账号权限。在MySQL 5以前， DROP USER只能用来删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。 

数据库创建、使用

创建数据库

create database databaseName;

使用某数据库

use databaseName;

表创建、删除

 创建表

create table users(
id int(5) not null auto_increment comment 'ID',
name varchar(20) not null comment '姓名',
account char(8) not null comment '账号',
password varchar(20) not null comment '密码',
role tinyint(1) default 0 comment '身份（管理员1或普通用户0）',
primary key (id)
)engine=innodb default charset=utf8

表只能有一个自增列，且该自增列必须被定义为主键 。

删除表

drop table tablename;

字段查看、添加、修改

查看表字段

desc tablename;

添加表字段

#不指定字段位置
alter table tablename add column columnname varchar(20) default null comment '字段描述';
#指定字段位置
alter table tablename add column columnname varchar(20) default null comment '字段描述' after columnname1;

更改字段

#将表tablename中的列columnname数据类型改为varchar(20)
alter table tablename modify column columnname varchar(20); 
#为字段添加其他内容,已指定int,要添加auto_increment，修改时也要加上int
alter table tablename modify column columnname int auto_increment;

记录插入、删除、更改

插入记录

#按照所有字段顺序插入记录
insert into tablename(id,name,account,password,role) values(2,'张三','00000003','zhangsan',0);
#仅指定部分字段插入
insert into tablename(name,account,password,role) values('张三','00000003','zhangsan',0);
#省略字段名插入，值需要一一对应
insert into tablename values(2,'张三','00000003','zhangsan',0);
#插入多条记录,values后面用逗号隔开
insert into tablename values(2,'张三','00000003','zhangsan',0),(3,'李四','00000004','lisi',0)

 同时设置了自增(auto_increment)和非空(not null)的字段在插入记录时不需要指定；

删除记录

#删除字段名为'value'的记录,不加筛选会删除所有记录
delete from tablename where filedname='value';

更改记录

#将字段columnName3值为'value3'的columnName1,columnName2列分别设为'value1','value2'
update tableName set columnName1='value1',columnName2='value2' where columnName3='value3';
#不指定条件会修改所有记录
update tableName set columnName1='value1';

 查询

基础查询

#查询所有字段
select * from tablename;
#条件查询
select * from tablename where fieldname='fieldvalue';返回字段名fieldname值为'fieldvalue'的记录
#多条件查询多列
select fieldname1,filedname2 from tablename where fieldname1='fieldvalue1' and fieldname2='fieldvalue2';

模糊查询：like

MySQL LIKE 子句 | 菜鸟教程

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

​​

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

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

含变量的条件查询

含变量的条件查询可以将用户输入的多个条件值赋给mysql变量，值为空则不查（返回所有），非空则查。（SQL语句当查询条件为空时默认查询全部数据，不为空是则按照条件进行查询_sql不为空就查作为条件查询-CSDN博客）

以下以name（varchar(20)）和role(char(1))为查询条件，set语句用于设置变量值（以@开头）：

​

Java的mySQL

Java使用MySQL数据库需要 1.加载驱动程序 2.建立连接 3.创建语句 4.执行语句 几个步骤：

驱动器需要下载 后添加到路径中才能使用（可以放在src同级的lib文件夹内，然后Add to Build Path）：

​

//加载驱动程序
Class.forName("com.mysql.jdbc.Driver");//或Class.forName("om.mysql.cj.jdbc.Driver");
//建立连接，需要传入你的数据库名，访问账号及密码
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/Library","scott","tiger");
//创建语句
Statement statement=connection.createStatement();
//执行语句
//执行查询语句，返回ResultSet对象
ResultSet resultSet=statement.executeQuery("select userpassword,userrole from users where userAccount='"+account+"'");
//当resultSet非空时，可以用.get.String(index)方法读取器内容字符串，index从1开始，用next方法访问下一行
while(resultSet.next()) {
    TableItem tableItem=new TableItem(this, SWT.MULTI);
		for(int i=1;i<=headerStrArray.length;i++)
			tableItem.setText(i-1, resultSet.getString(i));
}
//执行插入、更改语句、删除语句，不需要返回对象
statement.execute("insert into records(operationDateTime,userAccount,userName,isbn,bookName,operationType,operationCount) values('"+returnDateTime+"','"+account+"','"+userName+"','"+isbn+"','"+bookName+"','归还',"+returnCount+")");
statement.execute("update states set returnedCount="+(thisUserReturnedCount+returnCount)+",unReturnedCount="+(thisUserUnReturnedCount-returnCount)+" where userAccount='"+account+"' and isbn='"+isbn+"'");
statement.execute("delete from books where isbn='"+textArrayList.get(1-1).getText()+"'");

注意，用statement.executteQuery()方法返回的resultSet结果不能交替使用，如下列语句会报错，因为第二次查询后第一次的结果已经关闭： 

ResultSet resultSet1=statement.executeQuery("select userpassword from users where userAccount='"+account+"'");
ResultSet resultSet2=statement.executeQuery("select userrole from users where userAccount='"+account+"'");
while(resultSet1.next())
    //to do

解决方法是先使用第一次的结果，再进行第二次查询。或者创建多个statement对象分别查询。

 获取ResultSet的长度

Java获取ResultSet的长度-CSDN博客

ResultSet没有size()方法，当非空时，可用如下代码获取其长度：

​

rs.last()可以不用设置可滚动结果集，直接到rs的最后一行，最后一行的行号就是结果集的长度。

若遇到此错误Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY，是因为默认参数只能调用next()方法，此时需要将

Statement statement=connection.createStatement();

改为

Statement statement=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

即可（解决Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.问题_gzg521的博客-CSDN博客）。括号里也可以是其他参数，只要不是ResultSet.TYPE_FORWARD_ONLY（默认）就好。

其他类型查询结果如下：

ResultSet.TYPE_FORWARD_ONLY：这是默认类型，游标只能在结果集中向前移动。 ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感。

ResultSet.TYPE_SCROLL_SENSITIVE：游标可以向前和向后移动，结果集对创建结果集后其他人对数据库所做的更改敏感。

基于并发性，有两种类型的ResultSet对象。

ResultSet.CONCUR_READ_ONLY：结果集是只读的，这是默认的并发类型。 ResultSet.CONCUR_UPDATABLE：可以使用ResultSet更新方法来更新行数据