这里只演示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, 下篇专门整一整吧.
如对本文有疑问, 点击进行留言回复!!
网友评论