Mysql在线安装及基本操作

 更新apt工具

sudo apt update  

安装MySQL

sudo apt install mysql-server

查看MySQL状态

sudo systemctl status mysql

pass@pass:~$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2024-03-19 12:27:01 UTC; 17s ago
   Main PID: 2649 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4557)
     Memory: 365.5M
     CGroup: /system.slice/mysql.service
             └─2649 /usr/sbin/mysqld

Mar 19 12:27:00 pass systemd[1]: Starting MySQL Community Server...
Mar 19 12:27:01 pass systemd[1]: Started MySQL Community Server.

 初始MySQL配置

sudo mysql_secure_installation

pass@pass:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

根据提示按照个人需求操作....

 以root用户登录，自定义密码

pass@pass:~$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2024, 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>

自定义密码步骤，修改密码等级操作

mysql> SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| validate_password.changed_characters_percentage | 0     |
| validate_password.check_user_name               | ON    |
| validate_password.dictionary_file               |       |
| validate_password.length                        | 8     |
| validate_password.mixed_case_count              | 1     |
| validate_password.number_count                  | 1     |
| validate_password.policy                        | LOW   |
| validate_password.special_char_count            | 1     |
+-------------------------------------------------+-------+
8 rows in set (0.01 sec)

mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.length =4;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

 刷新数据库，查看root用户

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> quit;
Bye

 

 Mysql的基本操作

连接mysql数据库

sudo mysql -uroot -ppass

参数 -u  用户  -p 密码

创建数据库

方法 1：

mysql> create database mydb character set utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

方法 2：mysqladmin 工具

pass@pass:~$ sudo mysqladmin -u root -p create db
Enter password:

删除数据库

方法 1

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

方法 2

pass@pass:~$ sudo mysqladmin -u root -p drop db
Enter password:

常见数据类型

数值类型

• int           4 B
• smallint   2 B
• bigint       8 B
• float         4 B
• double     8 B
• decimal

日期时间类型

• date
• time
• year
• datatime

字符串类型

• char             固定字符串
• varchar        变长字符串
• text              长文本
• blob             二进制数据

删除数据表

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mytable        |
| users          |
+----------------+
2 rows in set (0.01 sec)

mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

插入信息到数据表

mysql> insert into users (name, age, birthdate)
    -> values('pass',18,'2000-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
+----+------+-----+------------+
1 row in set (0.00 sec)

查询数据表数据

mysql> select * from users;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
|  2 | xxx  |  19 | 2001-01-01 |
+----+------+-----+------------+
2 rows in set (0.00 sec)

mysql> select name,age from users where age=18;
+------+-----+
| name | age |
+------+-----+
| pass |  18 |
+------+-----+
1 row in set (0.00 sec)

mysql> select * from users where name like 'x%';
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  2 | xxx  |  19 | 2001-01-01 |
+----+------+-----+------------+
1 row in set (0.00 sec)

mysql> select * from users order by age;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
|  2 | xxx  |  19 | 2001-01-01 |
+----+------+-----+------------+
2 rows in set (0.00 sec)

mysql> select * from users order by age desc;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  2 | xxx  |  19 | 2001-01-01 |
|  1 | pass |  18 | 2000-01-01 |
+----+------+-----+------------+
2 rows in set (0.00 sec)

mysql> select * from users limit 1;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
+----+------+-----+------------+
1 row in set (0.00 sec)

mysql> select * from users where birthdate in ('1990-01-01', '2000-01-01');
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
+----+------+-----+------------+
1 row in set (0.00 sec)

where 常用操作符

• =     !=    > =    <=
• 组合条件   where    or / and
• 模糊匹配  like   'x%'
• in 
• between and
• is null
• is not null

更新数据表

mysql> select * from users;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
|  2 | yyy  |  19 | 2001-01-01 |
+----+------+-----+------------+
2 rows in set (0.00 sec)

mysql> update users set name='xxx',birthdate='2024-03-25'  where age=19;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
|  2 | xxx  |  19 | 2024-03-25 |
+----+------+-----+------------+
2 rows in set (0.00 sec)

删除数据表信息

mysql> select * from users;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
|  2 | xxx  |  19 | 2024-03-25 |
+----+------+-----+------------+
2 rows in set (0.00 sec)

mysql> delete from users where age=19;
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+------+-----+------------+
| id | name | age | birthdate  |
+----+------+-----+------------+
|  1 | pass |  18 | 2000-01-01 |
+----+------+-----+------------+
1 row in set (0.00 sec)

mysql> delete from users;
Query OK, 1 row affected (0.00 sec)

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

like  字段模糊查询

• like  'x%'            匹配x开头的字段
• like  '_x%'          匹配第二个字母为x的字段
• like  'x%y_'        匹配x开头，经过0个多个字符，是y的字段

union 

union  用于连接两个以上的数据表组合到一个结果结合，去除重复数据，返回特定字段

union all 不去除重复数据

select stu1 from t1 union select stu2 from t2;

排序order  by

• order by  xxx asc     升序
• order by  xxx  desc  降序  

group by 分组

where  xxx  group by a,b

连接的运用 inner left  right

select xxx from t1 inner join  t2  on t1.xxx = t2.xxx;

select xxx from t1 left join  t2  on t1.xxx = t2.xxx;

select xxx from t1 right join  t2  on t1.xxx = t2.xxx;

正则表达式

where  xxx regexp ...   同  where  xxx rlike ... 

• '^xx'                  匹配xx开头的字段
• 'ok$'                 匹配ok结尾的字段
• 'xx'                    匹配xx包含的字段
• '^[abc]|xx$'        匹配abc开头或xx结尾的字段
• 'xx[0-9]+'           匹配xx后跟一个或多个数字的字段
• BINARY  'xx'    匹配xx关键字，区分大小写

alter  修改表名或数据表字段

添加

alter  table t1  add column age int;   为t1表增加一个名为age的年龄列

修改字段属性

alter  table t1  modify column age float;   为t1表的age修改为float

修改列名

alter  table t1  modify t1  t2 varchar(255);   为t1表的表名t1 修改为t2

删除

alter  table t1  drop column age;   为t1表删除一个名为age的年龄列

索引

创建索引(若指定唯一 unique index)

create  index   xxx on  t1  (name);  为name增加一个xxx索引

alter  table  t1  add index  (xxx); 

删除索引

drop index  xxx on t1;

alter  table  t1  drop index  xxx;