MySQL基础命令

注：个人笔记，日常补修

• MySQL基础命令
  • 命令

命令

进入mysql

mysql -uroot -p

Enter password:

#展示数据库
show databases;
#创建数据库
create database xx;
create database XX(数据库名) charset = (字符编码，例如utf8);
create database if not exists XX(数据库名); 
create database `XX`(数据库名);(注意，这里``是反引号，在键盘tab上面)

show create database XX(数据库名);
#两个show的不同
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| pymysql            |
+--------------------+
2 rows in set (0.02 sec)

mysql> show create database pymysql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| pymysql  | CREATE DATABASE `pymysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.02 sec)

alter database XX(数据库名) character set utf8;#更改编码
#删除数据库
drop database XX(数据库名);
drop database if exists XX (数据库名字);	
#使用use
use XX(数据库名);
#创建表
create table XX(表名)(
字段1名 数据类型，
字段2名 数据类型,
.......
字段n名 数据类型
);
#show 
show tables;
show create table XX(表名);
show create table XX(表名) \G;
#展示表的结构
describe(或desc) XX(表名);

drop table XX(表名);
drop table XX,XX,XX......; 
#插入数据
insert into XX（表名） (字段1,字段2,字段3......) values (值1,值2,值3.......);
#展示表的内容( * 为选中所有列 )
select * from XX（表名）;

select * from XX（表名）where 字段1 = 值1 and 字段2 = 值2 and ......;
eg:select * from stu where score > 90 or score < 60;

select * from XX（表名） order by 字段x asc;（默认就是升序）
select * from XX（表名） order by 字段x desc;
select * from XX（表名） where 字段x = 值x oreder by 字段x asc(desc);
select * from XX（表名） limit n;
select * from XX（表名） limit n,m;
select * from XX（表名） order by 字段x asc(desc) limit n;
delete from XX（表名） where 字段x = 值x;

update XX（表名） set 字段1 = 值1,字段2 = 值2 where 条件;
update XX（表名） set 字段1 = 值1;

聚合函数
sum(); 求和 select sum(字段x) from XX（表名）;
avg(); 求平均值 select avg(字段x) from XX（表名）;
max(); 求最大值 select max(字段x) from XX（表名）;
min(); 求最小值 select min(字段x) from XX（表名）;
count(); 记录数 select count(字段x) from XX（表名）;/select max(*) from XX（表名）;
在某字段条件下使用函数 select 函数名() from XX（表名） where 字段x = 值x;

新建表单

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
birthdate DATE,
is_active BOOLEAN DEFAULT TRUE
);

可小写

插入数据

INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', '[email protected]', '1990-01-01', true);

desc users;

+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| birthdate | date | YES | | NULL | |
| is_active | tinyint(1) | YES | | 1 | |
+-----------+--------------+------+-----+---------+----------------+

mysql> select * from users;
+----+----------+---------------+------------+-----------+
| id | username | email | birthdate | is_active |
+----+----------+---------------+------------+-----------+
| 1 | xfk | [email protected] | 2024-03-19 | 1 |
+----+----------+---------------+------------+-----------+

如果你要插入所有列的数据，可以省略列名

INSERT INTO users
VALUES (NULL,'test', '[email protected]', '1990-01-01', true);

一次多行：

INSERT INTO users (username, email, birthdate, is_active)
VALUES
('test1', '[email protected]', '1985-07-10', true),
('test2', '[email protected]', '1988-11-25', false),
('test3', '[email protected]', '1993-05-03', true);

中文问题(一般不会遇到)：

show variables like '%character%';

+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.3/charsets/ |
+--------------------------+--------------------------------+

mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%character%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.3/charsets/ |
+--------------------------+--------------------------------+