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 ...;
耐心和恒心, 总会获得回报的.
