MySql 学习笔记(一)

首先启动mysql

yl@3c22fb80710f ~ % mysql -uroot -p1234

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 10
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> 

退出mysql

mysql> quit;
Bye

这里只是退出，实际上并没有关闭，要在系统偏好里面关闭

1 操作数据库

1.1 查询

show databases

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

1.2 创建数据库

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

创建数据库，如果不存在则创建

mysql> create database if not exists db2;
Query OK, 1 row affected, 1 warning (0.00 sec)

1.3 删除数据库

mysql> drop database db2;
Query OK, 0 rows affected (0.01 sec)

删除数据库，如果存在则删除

mysql> drop database if exists db2;
Query OK, 0 rows affected (0.00 sec)

1.4 使用数据库

mysql> use db1;
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> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

2 DDL 操作表

创建 Create, 查询Retrieve, 修改Update, 删除Delete

2.1 查询表

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| stu           |
| student       |
| tb_student    |
+---------------+
3 rows in set (0.00 sec)

2.1 创建表

mysql> create table stu(
    -> id int,
    -> name varchar(50)
    -> );
Query OK, 0 rows affected (0.05 sec)

2.3 删除表

mysql> drop table if exists stu;
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.4 修改表

2.4.1 修改表名

mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.00 sec)

2.4.2 添加一列

mysql> alter table stu add height int;
Query OK, 0 rows affected (0.08 sec)

添加后查看

mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| gender   | char(1)     | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| score    | double(5,2) | YES  |     | NULL    |       |
| email    | varchar(64) | YES  |     | NULL    |       |
| tel      | varchar(15) | YES  |     | NULL    |       |
| status   | tinyint(4)  | YES  |     | NULL    |       |
| height   | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

2.4.3 修改数据类型

mysql> alter table stu modify height varchar(10);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

修改后查看

mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| gender   | char(1)     | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| score    | double(5,2) | YES  |     | NULL    |       |
| email    | varchar(64) | YES  |     | NULL    |       |
| tel      | varchar(15) | YES  |     | NULL    |       |
| status   | tinyint(4)  | YES  |     | NULL    |       |
| height   | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

2.4.4 修改列名和数据类型

mysql> alter table stu change height hei int;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

2.4.5 删除列

mysql> alter table stu drop hei;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

3 DML 操作数据

3.1 insert

3.1.1 给指定列添加数据

插入报错

mysql> alter mysql> insert into stu(id,names)values(3,'张三');
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'names' at row 1

alter table stu change names names char(10) character set utf8;

修改数据类型

mysql> alter table stu change names names char(10) character set utf8;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu(id,names)values(3,'张三');
Query OK, 1 row affected (0.02 sec)

查询所有

mysql> select * from stu;
+------+----------+--------+----------+-------+-------+------+--------+
| id   | names    | gender | birthday | score | email | tel  | status |
+------+----------+--------+----------+-------+-------+------+--------+
|    1 | zhangsan | NULL   | NULL     |  NULL | NULL  | NULL |   NULL |
|    1 | zhangsan | NULL   | NULL     |  NULL | NULL  | NULL |   NULL |
|    3 | 张三     | NULL   | NULL     |  NULL | NULL  | NULL |   NULL |
+------+----------+--------+----------+-------+-------+------+--------+
3 rows in set (0.00 sec)

3.1.2 给全部列添加数据

不需要指定列名

mysql> insert into stu values(4,'李四','女','1992-01-03',92.5,'lisi@google.com','13118983431',0);
Query OK, 1 row affected (0.02 sec)

3.2 update

|    5 | 王五     | 男     | 1994-01-03 | 92.50 | wangwu@google.com | 13118984431 |      0 |
update stu set names='二蛋' where id=5;
|    5 | 二蛋     | 男     | 1994-01-03 | 92.50 | wangwu@google.com | 13118984431 |      0 |

可以同时修改多个列名

|    3 | 张三     | NULL   | NULL       |  NULL | NULL              | NULL        |   NULL |

mysql> update stu set names='王五',gender='男' where id=3;
Query OK, 1 row affected (0.02 sec)

|    3 | 王五     | 男     | NULL       |  NULL | NULL              | NULL        |   NULL |

update语句没加where条件，则会把所有记录都改了

3.3 delete

mysql> delete from stu where names='zhangsan';
Query OK, 2 rows affected (0.03 sec)

执行命令后，以下两条数据被删除
|    1 | zhangsan | NULL   | NULL       |  NULL | NULL              | NULL        |   NULL |
|    1 | zhangsan | NULL   | NULL       |  NULL | NULL              | NULL        |   NULL |

不加条件会把所有的数据都删除了

4 DQL 查询数据

4.1 基础查询

4.1.1 查询多个字段

mysql> select names from stu;
+--------+
| names  |
+--------+
| 王五   |
| 李四   |
| 李四   |
| 二蛋   |
+--------+

查询所有数据
mysql> select * from stu;
+------+--------+--------+------------+-------+-------------------+-------------+--------+
| id   | names  | gender | birthday   | score | email             | tel         | status |
+------+--------+--------+------------+-------+-------------------+-------------+--------+
|    3 | 王五   | 男     | NULL       |  NULL | NULL              | NULL        |   NULL |
|    4 | 李四   | 女     | 1992-01-03 | 92.50 | lisi@google.com   | 13118983431 |      0 |
|    4 | 李四   | 女     | 1992-01-03 | 92.50 | lisi@google.com   | 13118983431 |      0 |
|    5 | 二蛋   | 男     | 1994-01-03 | 92.50 | wangwu@google.com | 13118984431 |      0 |
+------+--------+--------+------------+-------+-------------------+-------------+--------+
4 rows in set (0.00 sec)

4.1.2 去除重复记录

mysql> select distinct names from stu;
+--------+
| names  |
+--------+
| 王五   |
| 李四   |
| 二蛋   |
+--------+

4.1.3 起别名

mysql> select names as 姓名,gender as 性别 from stu;
+--------+--------+
| 姓名   | 性别   |
+--------+--------+
| 王五   | 男     |
| 李四   | 女     |
| 李四   | 女     |
| 二蛋   | 男     |
+--------+--------+

4.2 条件查询

mysql> select id, names, score from stu where score >= 60;
+------+--------+-------+
| id   | names  | score |
+------+--------+-------+
|    2 | 二蛋   | 93.50 |
|    3 | 李四   | 69.00 |
|    4 | 张飞   | 72.00 |
+------+--------+-------+

4.3 分组查询

mysql> select * from stu;
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
| id   | names     | gender | birthday   | score | email             | tel         | status |
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
|    1 | 王五      | 男     | 1994-04-06 | 35.90 | wangwu@qq.com     | 15211234532 |      0 |
|    2 | 二蛋      | 男     | 1994-08-04 | 93.50 | erdan@google.com  | 15899433243 |      1 |
|    3 | 李四      | 女     | 1994-05-09 | 69.00 | lisi@163.com      | 13698003282 |      1 |
|    4 | 张飞      | 男     | 1994-06-03 | 72.00 | zhangfei@qq.com   | 13522439985 |      1 |
|    5 | 孙二娘    | 女     | 1993-04-09 | 82.00 | sunerniang@qq.com | 13483231509 |      1 |
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
5 rows in set (0.00 sec)
-- 查询男女同学的平均分
mysql> select gender, avg(score) from stu group by gender;
+--------+------------+
| gender | avg(score) |
+--------+------------+
| 女     |  75.500000 |
| 男     |  67.133333 |
+--------+------------+
2 rows in set (0.00 sec)

查询男女同学的平均分，并统计人数

mysql> select gender, avg(score), count(*) from stu group by gender;
+--------+------------+----------+
| gender | avg(score) | count(*) |
+--------+------------+----------+
| 女     |  75.500000 |        2 |
| 男     |  67.133333 |        3 |
+--------+------------+----------+
2 rows in set (0.00 sec)

分数低于90分的才参与分组

mysql> select gender, avg(score), count(*) from stu where score < 90 group by gender;
+--------+------------+----------+
| gender | avg(score) | count(*) |
+--------+------------+----------+
| 女     |  75.500000 |        2 |
| 男     |  53.950000 |        2 |
+--------+------------+----------+
2 rows in set (0.00 sec)

分数低于90分参与分组，并且只展示人数大于2人的分组

mysql> select * from stu;
+------+-----------+--------+------------+-------+--------------------+-------------+--------+
| id   | names     | gender | birthday   | score | email              | tel         | status |
+------+-----------+--------+------------+-------+--------------------+-------------+--------+
|    1 | 王五      | 男     | 1994-04-06 | 35.90 | wangwu@qq.com      | 15211234532 |      0 |
|    2 | 二蛋      | 男     | 1994-08-04 | 93.50 | erdan@google.com   | 15899433243 |      1 |
|    3 | 李四      | 女     | 1994-05-09 | 69.00 | lisi@163.com       | 13698003282 |      1 |
|    4 | 张飞      | 男     | 1994-06-03 | 72.00 | zhangfei@qq.com    | 13522439985 |      1 |
|    5 | 孙二娘    | 女     | 1993-04-09 | 82.00 | sunerniang@qq.com  | 13483231509 |      1 |
|    6 | 王昭君    | 女     | 1992-01-23 | 70.00 | wangzhaojun@qq.com | 13489761523 |      1 |
+------+-----------+--------+------------+-------+--------------------+-------------+--------+
6 rows in set (0.00 sec)

mysql> select gender, avg(score) from stu where score < 90 group by gender having count(*) > 2;
+--------+------------+
| gender | avg(score) |
+--------+------------+
| 女     |  73.666667 |
+--------+------------+
1 row in set (0.00 sec)

4.4 排序查询

mysql> select * from stu;
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
| id   | names     | gender | birthday   | score | email             | tel         | status |
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
|    1 | 王五      | 男     | 1994-04-06 | 35.90 | wangwu@qq.com     | 15211234532 |      0 |
|    2 | 二蛋      | 男     | 1994-08-04 | 93.50 | erdan@google.com  | 15899433243 |      1 |
|    3 | 李四      | 女     | 1994-05-09 | 69.00 | lisi@163.com      | 13698003282 |      1 |
|    4 | 张飞      | 男     | 1994-06-03 | 72.00 | zhangfei@qq.com   | 13522439985 |      1 |
|    5 | 孙二娘    | 女     | 1993-04-09 | 82.00 | sunerniang@qq.com | 13483231509 |      1 |
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
5 rows in set (0.00 sec)

mysql> select avg(score) from stu;
+------------+
| avg(score) |
+------------+
|  70.480000 |
+------------+
1 row in set (0.00 sec)

4.5 分页查询

mysql> select * from stu;
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
| id   | names     | gender | birthday   | score | email             | tel         | status |
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
|    1 | 王五      | 男     | 1994-04-06 | 35.90 | wangwu@qq.com     | 15211234532 |      0 |
|    2 | 二蛋      | 男     | 1994-08-04 | 93.50 | erdan@google.com  | 15899433243 |      1 |
|    3 | 李四      | 女     | 1994-05-09 | 69.00 | lisi@163.com      | 13698003282 |      1 |
|    4 | 张飞      | 男     | 1994-06-03 | 72.00 | zhangfei@qq.com   | 13522439985 |      1 |
|    5 | 孙二娘    | 女     | 1993-04-09 | 82.00 | sunerniang@qq.com | 13483231509 |      1 |
+------+-----------+--------+------------+-------+-------------------+-------------+--------+
5 rows in set (0.00 sec)

mysql> select names, score from stu limit 0,3;
+--------+-------+
| names  | score |
+--------+-------+
| 王五   | 35.90 |
| 二蛋   | 93.50 |
| 李四   | 69.00 |
+--------+-------+

5 navicat的使用

1 navicat下载

2 安装后输入主机名和密码

3 使用navicat执行sql语句

执行示例

6 修改默认字符集

6.1 查看字符集

mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8                                                      |
| character_set_connection | utf8                                                      |
| character_set_database   | latin1                                                    |
| character_set_filesystem | binary                                                    |
| character_set_results    | utf8                                                      |
| character_set_server     | latin1                                                    |
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-5.7.24-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.00 sec)

使用如下命令改成utf8

alter database db1 character set utf8;

6.2 遇到Mysql启动失败问题

在文件中查看错误

/usr/local/mysql/data/mysqld.local.err

其中的error:

2022-09-26T03:22:05.408457Z 0 [ERROR] unknown variable 'character_set_database=utf8'
2022-09-26T03:22:05.408478Z 0 [ERROR] Aborting

修复方式:
删除行:

character_set_database=utf8