Free Magisk Free Magisk
    / Java / 185浏览

    mysql note1

    MySQL - Note(1)

    一、配置环境变量

    C:\Users\Administrator>mysql
'mysql' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

C:\Users\Administrator>

    原因是没有把mysql.exe配置到Path中

    C:\Users\Administrator>path
PATH=C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\Program Files\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;E:\Git\cmd;E:\nodejs\;C:\Program Files\Java\jdk1.8.0_271\bin;E:\Python3.9\Scripts\;E:\Python3.9\;C:\Users\Administrator\AppData\Local\Microsoft\WindowsApps;E:\Bandizip\;E:\Microsoft VS Code\bin;C:\Users\Administrator\AppData\Roaming\npm

C:\Users\Administrator>

    二、启动MySQL服务

    使用window自带的net指令来启动服务与关闭服务

    • net start 服务名
    • net stop 服务名

    服务名在哪里可以看到:services.msc(服务列表中可以看到)

    image-20220117163137313

    # 打开服务列表窗口,查看服务名
C:\Users\Administrator>services.msc

# 启动服务
C:\Users\Administrator>net start mysql8
请求的服务已经启动。

请键入 NET HELPMSG 2182 以获得更多的帮助。

# 停止服务
C:\Users\Administrator>net stop mysql8
mysql8 服务正在停止.
mysql8 服务已成功停止。

C:\Users\Administrator>net start mysql8
mysql8 服务正在启动 ..
mysql8 服务已经启动成功。

C:\Users\Administrator>

    三、登录MySQL

    登录MySQL使用的指令是:mysql -u用户名 -h主机地址 -P端口号 -p密码.如果数据库是安装在本机的,可以省略-h和-p.简化为mysql -u用户名 -p密码.

    C:\Users\Administrator>mysql -uroot -hlocalhost -P3306 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 退出mysql使用exit
mysql> exit
Bye

C:\Users\Administrator>

    使用简化的指令

    C:\Users\Administrator>mysql -uroot -p123456
# 因为上面把密码明文显示在命令行不安全,所以提出警告!!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

    四、查看有哪些库

    使用指令show来查看数据库的一些信息

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

mysql>

    这四个库都是系统库,千万千万别删了!

    五、创建我们自己的库

    创建数据库使用指令:create,具体语句如下(通过? 指令来查看指令的详细内容)

    mysql> ? create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html

mysql>

    mysql> create database if not exists freeme character set utf8mb4 collate utf8mb4_general_ci ;
# 看到这个表示SQL语句执行成功   1 row affected 一行受影响  (0.12 sec) 执行这条语句的耗时
Query OK,   1 row affected (0.12 sec)

# 再次查看,可以看到多了一条记录出来!
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| freeme               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

    六、查看建库语句

    mysql> show create database freeme;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| freeme     | CREATE DATABASE `freeme` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

    七、切换当前使用的数据库

    ​ 默认使用的是mysql

    mysql> use freeme;
Database changed
mysql>

    八、查看当前库下有那些表

    mysql> show tables;
Empty set (0.05 sec)

mysql>

    九、创建数据表

    也可以使用?来查看语句该怎么写

    基本的建表语句,语法如下

    create table if not exists 
(
    字段名称  字段数据类型 完整性约束 注释,
    字段名称  字段数据类型 完整性约束 注释,
    字段名称  字段数据类型 完整性约束 注释,
    字段名称  字段数据类型 完整性约束 注释
)存储引擎 编码格式;

    Tips:最后一个字段的末尾是不加逗号的

    现在来创建一个学生表

    Java类中字段名 Java类型 数据表中字段名 数据表中数据类型
    stuId Integer stu_id int|integer
    stuAge Integer stu_age int|Integer
    stuGender String stu_gender char(3) 固定长度的字符串
    stuName String stu_name varchar(长度) 可变长度的字符串

    Tips:mysql是不区分大小写的,所有不认识驼峰式.一般使用_来表示,后面的单词是大写

    mysql> create table if not exists stu(
    ->     stu_id int auto_increment primary key comment '学生编号',
    ->     stu_age int comment '学生年龄',
    ->     stu_gender char(1) default '男' comment '学生性别',
    ->     stu_name varchar(20) not null comment '学生姓名'
    -> )comment '学生表';
Query OK, 0 rows affected (0.26 sec)

# 再次查看当前库下有那些表
mysql> show tables;
+----------------+
| Tables_in_freeme |
+----------------+
| stu            |
+----------------+
1 row in set (0.00 sec)

mysql>

    十、提示符介绍

    #  > 是主要提示符 简称 主提示符 用于接收一条新的指令
mysql> show
# -> 是次要提示符 简称 次提示符 用于接收指令的剩余部分
    -> tables
# 每一条语句都以分号结尾    
    -> ;
Empty set (0.00 sec)

mysql>

    十一、查看表结构

    mysql> desc stu;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| stu_id     | int         | NO   | PRI | NULL    | auto_increment |
| stu_age    | int         | YES  |     | NULL    |                |
| stu_gender | char(1)     | YES  |     | 男      |                |
| stu_name   | varchar(20) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

    desc是describe的缩写

    mysql> describe stu;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| stu_id     | int         | NO   | PRI | NULL    | auto_increment |
| stu_age    | int         | YES  |     | NULL    |                |
| stu_gender | char(1)     | YES  |     | 男      |                |
| stu_name   | varchar(20) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

    还可以通过show语句来查看见表语句

    mysql> show create table stu;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `stu_id` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `stu_age` int DEFAULT NULL COMMENT '学生年龄',
  `stu_gender` char(1) COLLATE utf8mb4_general_ci DEFAULT '男' COMMENT '学生性别',
  `stu_name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表'                     |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql>

    十二、查看表中数据

    查看数据使用关键字:select,语法如下

    select 字段1,字段2,...,字段n from 表名;
    mysql> select stu_id,stu_age,stu_gender,stu_name from stu;
Empty set (0.00 sec)

mysql>

    十三、向表中插入数据

    向表中插入数据使用关键字:insert,语法如下

    insert into 表名(字段1,字段2,...,字段n) values (值1,值2,...,值n);
    mysql> insert into stu (stu_id,stu_age,stu_gender,stu_name) values (0,19,'男','刘一');
Query OK, 1 row affected (0.08 sec)

mysql>

    如果给所有字段插入数据,可以省略表名后面的字段列表

    insert into 表名 values (值1,值2,...,值n);

    值的顺序要和建表时提供的字段顺序保持一致!

    mysql> insert into stu values (0,20,'女','陈二');
Query OK, 1 row affected (0.09 sec)

mysql>

    如果遇到字段有默认值的,可以在赋值时,不提供该字段的值

    mysql> insert into stu values (0,20,'陈二');
# 列的数量和值的数量不匹配 stu后面没有字段列表,表示给所有字段赋值,那有4个字段,values后面的值列表中只有3个值,所以不匹配
ERROR 1136 (21S01): Column count doesn't match value count at row 1
    mysql> insert into stu (stu_id,stu_age,stu_name) values (0,20,'陈二');
Query OK, 1 row affected (0.07 sec)

mysql>

    查看下表中的数据

    mysql> select stu_id,stu_age,stu_gender,stu_name from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 刘一     |
|      2 |      20 | 女         | 陈二     |
|      3 |      20 | 男         | 陈二     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql>

    如果查询的是全部字段,可以省略字段列表使用*代替.开发时严禁用*.

    mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 刘一     |
|      2 |      20 | 女         | 陈二     |
|      3 |      20 | 男         | 陈二     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql>

    十四、修改表中的数据

    修改数据使用关键字:update,它的语法格式

    # 更改全部数据
update 表名 set 字段 = 新的值; 
    mysql> update stu set stu_name = '张三';
Query OK, 3 rows affected (0.07 sec)
# Rows matched 3 表示有3行匹配  Changed 3 表示修饰了3行数据
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 张三     |
|      2 |      20 | 女         | 张三     |
|      3 |      20 | 男         | 张三     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql>

    本意是修改ID为3的记录,而不是修改全部,所以在update的后面追加条件子句: where

    update 表名 set 字段 = 新的值 where 条件;
    mysql> update stu set stu_name = '刘一' where stu_id = 1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update stu set stu_name = '陈二' where stu_id = 2;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update stu set stu_name = '张三' where stu_id = 3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 刘一     |
|      2 |      20 | 女         | 陈二     |
|      3 |      20 | 男         | 张三     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql> update stu set stu_age = 22 where stu_age >= 20;
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 刘一     |
|      2 |      22 | 女         | 陈二     |
|      3 |      22 | 男         | 张三     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql>

    Tips: where的后面本质上根的是布尔值!回想Java中哪些运算可以产生布尔值!(关系,逻辑),只要where后面的结果为真,则执行修改!

    # SQL注入式攻击!
mysql> update stu set stu_age = 23 where stu_age >= 20 or 1 = 1;
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      23 | 男         | 刘一     |
|      2 |      23 | 女         | 陈二     |
|      3 |      23 | 男         | 张三     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql>

    十五、删除表中数据

    删除表中数据有两种写法

    • delete 不会重置自动增长,删除后新增,自动增长继续!
    • truncate 重置自动增长

    delete语句

    delete的删除语法如下

    delete from 表名; //--  删除表中所有数据
    mysql> delete from stu;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from stu;
Empty set (0.00 sec)

mysql>

    delete from 表名 where 条件;  // 删除表中满足条件的数据
    mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      23 | 男         | 刘一     |
|      2 |      23 | 女         | 陈二     |
|      3 |      23 | 男         | 张三     |
|      4 |      22 | 女         | 李四     |
+--------+---------+------------+----------+
4 rows in set (0.00 sec)

mysql> delete from stu where stu_id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      23 | 男         | 刘一     |
|      2 |      23 | 女         | 陈二     |
|      4 |      22 | 女         | 李四     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql> insert into stu values(0,19,'男','王五');
Query OK, 1 row affected (0.00 sec)

# stu_id 因为是自动增长的,不会停止,继续涨!
mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      5 |      19 | 男         | 王五     |
+--------+---------+------------+----------+
1 row in set (0.00 sec)

mysql>

    truncate语句

    一次性删除表中的所有数据,一般不会用于删除某条记录,都是用于清空表中数据.

    mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      23 | 男         | 刘一     |
|      2 |      23 | 女         | 陈二     |
|      3 |      23 | 男         | 张三     |
|      4 |      22 | 女         | 李四     |
|      6 |      19 | 男         | 王五     |
+--------+---------+------------+----------+
5 rows in set (0.00 sec)

mysql> truncate table stu;
Query OK, 0 rows affected (0.37 sec)

mysql> select * from stu;
Empty set (0.00 sec)

mysql> insert into stu values(0,19,'男','赵六');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 赵六     |
+--------+---------+------------+----------+
1 row in set (0.00 sec)

mysql>

    十六、别名机制

    在查询时,我们希望看到字段名时中文,而不是字段名本身,那就可以使用别名机制.别名可以使用在字段后,表名后等

    select 字段 as 别名 from 表名 as 别名
    # 不使用别名
mysql> select stu_id from stu;
+--------+
| stu_id |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

# 在字段的后面使用别名
mysql> select stu_id as '学生编号' from stu;
+--------------+
| 学生编号     |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

# 给表加别名  什么时候能用到:多表联查,两张表有同名字段,子查询时需要使用别名机制!!
mysql> select s.* from stu s;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 赵六     |
+--------+---------+------------+----------+
1 row in set (0.00 sec)

mysql> select * from stu s;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 赵六     |
+--------+---------+------------+----------+
1 row in set (0.00 sec)

mysql>

    只要给表起了别名，表名就无效了只能使用别名，继续使用表名会报错的.

    #这样写会报错
select stu.stu id from stu s；

    别名机制中的as一般是省略的

    mysql> select stu_id '学生编号',stu_name '学生姓名' from stu;
+--------------+--------------+
| 学生编号     | 学生姓名     |
+--------------+--------------+
|            1 | 赵六         |
+--------------+--------------+
1 row in set (0.00 sec)

mysql>

    Tips:省略了as才是主流写法!!

    十七、批量插入

    在插入时,value有两种写法

    • value
    • values (居多!!)
    mysql> insert into stu value(0,19,'男','孙琪');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu values(0,20,'女','周八');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 赵六     |
|      2 |      19 | 男         | 孙琪     |
|      3 |      20 | 女         | 周八     |
+--------+---------+------------+----------+
3 rows in set (0.00 sec)

mysql>

    values后面的小括号是可以重复的

    insert into 表名 values (),(),();

    每一个小括号内,放的就是一组数据,比如上面有3个小括号,表示同时插入3条记录

    mysql> insert into stu values(0,20,'女','吴九'),(0,19,'女','郑十'),(0,22,'男','铁蛋');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+--------+---------+------------+----------+
| stu_id | stu_age | stu_gender | stu_name |
+--------+---------+------------+----------+
|      1 |      19 | 男         | 赵六     |
|      2 |      19 | 男         | 孙琪     |
|      3 |      20 | 女         | 周八     |
|      4 |      20 | 女         | 吴九     |
|      5 |      19 | 女         | 郑十     |
|      6 |      22 | 男         | 铁蛋     |
+--------+---------+------------+----------+
6 rows in set (0.00 sec)

mysql>