Mysql 库表操作初识

mysql 库表操作初识

终端登录mysql

这里只演示win下, cmd 终端. 至于怎么在win下, linux, mac安装, 感觉这是一个入门级的百度搜索问题, 安装都搞不定, 确实有点尴尬, 好尴尬呀.

-- win + r 输入cmd 进入控制台
c:\users\chenjie> mysql  -u root -p  -- 回车
enter password: ********  -- 回车

-- 成功进入了mysql客户端, 如果登录失败, 要么就是密码不对, 或者是服务没有开启.
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 19
server version: 5.7.17-log mysql community server (gpl)

copyright (c) 2000, 2016, 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.

-- 打印 "hello,world!" 测试
mysql> select "hello, world!";
+---------------+
| hello, world! |
+---------------+
| hello, world! |
+---------------+
1 row in set (0.00 sec)

-- test
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-10-06 14:11:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate() as "今日日期";
+--------------+
| 今日日期     |
+--------------+
| 2019-10-06   |
+--------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| null       |
+------------+
1 row in set (0.05 sec)

-- 查看所有的数据库
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| cj                 |
| flask_db           |
| from_pdm           |
| mysql              |
| new_house          |
| performance_schema |
| python_test_1      |
| sql_advance        |
| stock_db           |
| sys                |
| test1              |
+--------------------+
19 rows in set (0.04 sec)

mysql> show variables like 'port';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.10 sec)

-- 查看数据库编码
mysql> show variables like "character%";
+--------------------------+----------------------------------+
| variable_name            | value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | c:\appserv\mysql\share\charsets\ |
+--------------------------+----------------------------------+
8 rows in set (0.05 sec)

-- 查看某用户的权限
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| grants for root@localhost                                           |
+---------------------------------------------------------------------+
| grant all privileges on *.* to 'root'@'localhost' with grant option |
| grant proxy on ''@'' to 'root'@'localhost' with grant option        |
+---------------------------------------------------------------------+
2 rows in set (0.10 sec)

-- 查看数据库当前连接数, 并发数
mysql> show status like 'threads%';
+-------------------+-------+
| variable_name     | value |
+-------------------+-------+
| threads_cached    | 1     | -- 此线程中的空闲线程数
| threads_connected | 4     |
| threads_created   | 5     | -- 最近一次启动服务,做创建的线程数
| threads_running   | 1     | -- 当前激活线程数 
+-------------------+-------+
4 rows in set (0.05 sec)

-- 查看数据文件存放路径
mysql> show variables like "%datadir%";
+---------------+------------------------+
| variable_name | value                  |
+---------------+------------------------+
| datadir       | c:\appserv\mysql\data\ |
+---------------+------------------------+
1 row in set (0.05 sec)

-- 查看数据库最大连接数
mysql> show variables like '%max_connections%';
+-----------------+-------+
| variable_name   | value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.03 sec)
...

-- 退出数据库
mysql> quit;
bye
c:\users\chenjie>

数据库操作

增删改查, 注意这里讲的数据库指的是schema哈.

-- 前提是已经登录,进入终端
-- 先查看有哪些
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| cj                 |
| flask_db           |
| from_pdm           |
| mysql              |
| new_house          |
| performance_schema |
| python_test_1      |
| sql_advance        |
| stock_db           |
| sys                |
| test1              |
+--------------------+
19 rows in set (0.04 sec)

-- 新增一个新数据库 test
mysql> create database test charset=utf8;
query ok, 1 row affected (0.05 sec)

-- 选择使用test
mysql> use test;
database changed
-- 查看当前所在的数据库
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.05 sec)

mysql> show create database test;
+----------+---------------------------------------------------------------+
| database | create database                                               |
+----------+---------------------------------------------------------------+
| test     | create database `test` /*!40100 default character set utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.08 sec)

-- 查看库中有哪些表
mysql> show tables;
empty set

-- 删除数据库(千万慎重!!!, 也不要给权限, 删了就真的没了)
mysql> drop database test;
query ok, 0 rows affected (0.20 sec)

-- 物理删除哦, 太危险了, 删库跑路...
mysql> use test;
1049 - unknown database 'test'

表结构操作

包括创建,修改, 删除表, 字段增删改, 还是以经典的学生表为例.

创建表

-- 先建个test库
drop database if exist test;
create database test charset=utf8;
use test;
show tables
-- 查看表
mysql> show tables;
empty set

-- 创建学生表, 说实话, 我现在见到学生表,选课表, 选课..也想吐...
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default "",
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男', '女', "gg", "?"),
    class_id tinyint unsigned default 1
);

-- 创建班级表
create table classes(
    id tinyint unsigned primary key auto_increment not null,
    name varchar(20) default ''
);

-- out
mysql> show tables;
+----------------+
| tables_in_test |
+----------------+
| classes        |
| students       |
+----------------+
2 rows in set (0.06 sec)

-- 查看表字段及约束
mysql> desc students;
+----------+--------------------------+------+-----+---------+----------------+
| field    | type                     | null | key | default | extra          |
+----------+--------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned         | no   | pri | null    | auto_increment |
| name     | varchar(20)              | yes  |     |         |                |
| age      | tinyint(3) unsigned      | yes  |     | 0       |                |
| height   | decimal(5,2)             | yes  |     | null    |                |
| gender   | enum('男','女','中性','未知') | yes  |     | null    |                |
| class_id | tinyint(3) unsigned      | yes  |     | 1       |                |
+----------+--------------------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)

-- 查看表创建语句
mysql> show create table classes;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table   | create table                                                                                                        
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| classes | create table `classes` (
  `id` tinyint(3) unsigned not null auto_increment,
  `name` varchar(20) default '',
  primary key (`id`)
) engine=innodb default charset=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

修改表-字段-增改删

-- 练习 alter tabe ...
-- add, modify, 
mysql> desc classes;
+-------+---------------------+------+-----+---------+----------------+
| field | type                | null | key | default | extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | no   | pri | null    | auto_increment |
| name  | varchar(20)         | yes  |     |         |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

-- 表增加字段 add
mysql> alter table classes add slogan varchar(30);
query ok, 0 rows affected (0.54 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc classes;
+--------+---------------------+------+-----+---------+----------------+
| field  | type                | null | key | default | extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | tinyint(3) unsigned | no   | pri | null    | auto_increment |
| name   | varchar(20)         | yes  |     |         |                |
| slogan | varchar(30)         | yes  |     | null    |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

-- 表修改字段 modify(不重命名); change(重命名)
mysql> alter table classes modify slogan int;
query ok, 0 rows affected (0.48 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc classes;
+--------+---------------------+------+-----+---------+----------------+
| field  | type                | null | key | default | extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | tinyint(3) unsigned | no   | pri | null    | auto_increment |
| name   | varchar(20)         | yes  |     |         |                |
| slogan | int(11)             | yes  |     | null    |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

-- 修改表字段-重命名 change 旧名 新名 类型约束
mysql> alter table classes change slogan yg_slogan varchar(50);
query ok, 0 rows affected (0.52 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc classes;
+-----------+---------------------+------+-----+---------+----------------+
| field     | type                | null | key | default | extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | tinyint(3) unsigned | no   | pri | null    | auto_increment |
| name      | varchar(20)         | yes  |     |         |                |
| yg_slogan | varchar(50)         | yes  |     | null    |                |
+-----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

-- 删除字段: drop 列名
mysql> alter table classes drop yg_slogan, drop name;

query ok, 0 rows affected (0.52 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc classes;
+-------+---------------------+------+-----+---------+----------------+
| field | type                | null | key | default | extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | no   | pri | null    | auto_increment |
+-------+---------------------+------+-----+---------+----------------+
1 row in set (0.04 sec)

-- 删除表: drop table tb_name1, tb_name2...

mysql> drop table classes, students;
query ok, 0 rows affected (0.22 sec)

mysql> show tables;
empty set

表修改-增删改数据

-- 还是用student表, 重写创建回来
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default "",
    gender enum("男", "女", "中性", "未知"),
    age tinyint unsigned default 0,
    height decimal(5,2),
    class_id int unsigned default 1
);

新增数据 inset

-- 全字段插入
insert into students values (null, "youge", "男", 23, 174.5, 1);

select * from students;
query ok, 1 row affected (0.08 sec)

+----+-------+--------+-----+--------+----------+
| id | name  | gender | age | height | class_id |
+----+-------+--------+-----+--------+----------+
|  1 | youge | 男     |  23 | 174.50 |        1 |
+----+-------+--------+-----+--------+----------+
1 row in set (0.05 sec)

-- 部分列插入
insert into students(name, gender, age) values ("郭靖", 1, 28);

mysql> select * from students;
+----+-------+--------+-----+--------+----------+
| id | name  | gender | age | height | class_id |
+----+-------+--------+-----+--------+----------+
|  1 | youge | 男     |  23 | 174.50 |        1 |
|  2 | 郭靖  | 男     |  28 | null   |        1 |
+----+-------+--------+-----+--------+----------+
2 rows in set (0.07 sec)

-- 多行插入
insert into students values 
    (null, "黄蓉", 2, 24, 165.32, 2),
    (null, "杨康", 1, 25, 180, 1),
    (123, "华筝", 2, 23, 162.3, 2);
    
mysql> select * from students;
+-----+-------+--------+-----+--------+----------+
| id  | name  | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
|   1 | youge | 男     |  23 | 174.50 |        1 |
|   2 | 郭靖  | 男     |  28 | null   |        1 |
|   3 | 黄蓉  | 女     |  24 | 165.32 |        2 |
|   4 | 杨康  | 男     |  25 | 180.00 |        1 |
| 123 | 华筝  | 女     |  23 | 162.30 |        2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.07 sec)

修改数据 update

-- 全部修改 (这也跟 删表删库 一样危险, 更新不加 where,就全改掉了)
update students set 
    age := 24,  -- := 才是mysql的"赋值", "=" 在update, set时一样
    class_id = 2;

mysql> select * from students;
+-----+-------+--------+-----+--------+----------+
| id  | name  | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
|   1 | youge | 男     |  24 | 174.50 |        2 |
|   2 | 郭靖  | 男     |  24 | null   |        2 |
|   3 | 黄蓉  | 女     |  24 | 165.32 |        2 |
|   4 | 杨康  | 男     |  24 | 180.00 |        2 |
| 123 | 华筝  | 女     |  24 | 162.30 |        2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.07 sec)

-- 按条件修改 where
update students set age:=26 where id=4;

mysql>select * from students;
+-----+-------+--------+-----+--------+----------+
| id  | name  | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
|   1 | youge | 男     |  24 | 174.50 |        2 |
|   2 | 郭靖  | 男     |  24 | null   |        2 |
|   3 | 黄蓉  | 女     |  24 | 165.32 |        2 |
|   4 | 杨康  | 男     |  26 | 180.00 |        2 |
| 123 | 华筝  | 女     |  24 | 162.30 |        2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.08 sec)

删除数据 delete, 分逻辑删除和物理删除

-- 逻辑删除: 新增一个标志列, 默认我0表不删, 1表删除
alter table students add is_delete bit default 0;
-- update 标记要删的数据
update students set is_delete := 1 where id in(1,2);

mysql> select * from students;
+-----+-------+--------+-----+--------+----------+-----------+
| id  | name  | gender | age | height | class_id | is_delete |
+-----+-------+--------+-----+--------+----------+-----------+
|   1 | youge | 男     |  24 | 174.50 |        2 | 1         |
|   2 | 郭靖  | 男     |  24 | null   |        2 | 1         |
|   3 | 黄蓉  | 女     |  24 | 165.32 |        2 | 0         |
|   4 | 杨康  | 男     |  26 | 180.00 |        2 | 0         |
| 123 | 华筝  | 女     |  24 | 162.30 |        2 | 0         |
+-----+-------+--------+-----+--------+----------+-----------+
5 rows in set (0.11 sec)

-- 其实就是过滤而已,并未真正删除
mysql> select * from students where is_delete = 0;
+-----+------+--------+-----+--------+----------+-----------+
| id  | name | gender | age | height | class_id | is_delete |
+-----+------+--------+-----+--------+----------+-----------+
|   3 | 黄蓉 | 女     |  24 | 165.32 |        2 | 0         |
|   4 | 杨康 | 男     |  26 | 180.00 |        2 | 0         |
| 123 | 华筝 | 女     |  24 | 162.30 |        2 | 0         |
+-----+------+--------+-----+--------+----------+-----------+
3 rows in set (0.06 sec)

-- 物理删除: delete ...; truncate tb_anme, 保留表结构
delete from students where id in (1,2,3,666);

mysql> select * from students;
+-----+------+--------+-----+--------+----------+-----------+
| id  | name | gender | age | height | class_id | is_delete |
+-----+------+--------+-----+--------+----------+-----------+
|   4 | 杨康 | 男     |  26 | 180.00 |        2 | 0         |
| 123 | 华筝 | 女     |  24 | 162.30 |        2 | 0         |
+-----+------+--------+-----+--------+----------+-----------+
2 rows in set (0.07 sec)

-- 全删, 保留表结构
truncate students;

mysql> truncate students;
query ok, 0 rows affected (0.24 sec)

mysql> select * from students;
empty set

关于查询 select, 下篇专门整一整吧.

小结

• mysql 在不同平台(win, linux)下的安装, 都是自己手动熟悉一遍
• 终端登录: mysql -u 用户名 -p 密码; 退出: quit;
• 常用命令
  • select database();
  • shwo databases;
  • use 数据库名;
  • show tables;
  • desc 表名;
• 库操常用
  • create database 库名 charset=utf8;
  • use 库名;
    
  • show create database 库名;
  • drop database 库名; (删库跑路)
• 增删表
  • create table 表名 (字段名, 类型, 约束, ....);
  • 类型: 数值, 字符串, 时间日期, 枚举...
  • 约束: primary key; auto_increment; not null; default; unique; foreign key...
  • 主键约束: id int unsigned primary key auto_increment not null;
  • desc 表名; show create table 表名;
  • drop table 表名;
• 增删改字段
  • alter table 表名 add 字段名 类型 [约束];
  • alter table 表名 modify 字段名 类型 [约束];
  • alter table 表名 change 现字段名 新字段名 类型 [约束];
  • alter table 表名 drop 字段1, drop 字段2....;
• 表数据-增
  • insert into 表名 values (col1, co12 ......), ( ...), (...);
  • insert into 表名(col1, col2) values (val1, val2) ...;
• 表数据-改
  • update 表名 set 字段1=值1, 字段2=值2 ....
  • update 表名 set 字段1=值1, 字段2=值2 .... where .....
• 表数据-删
  • delete from 表名 where .....;
  • truncate 表名; (会保留表结构)
  • alter table 表名 add isdelete bit default ( );
  • update 表名 set isdelete := 1 where ...;