基础命令
MYSQL注释方式
-- 单行注释 /* 多行注释 哈哈哈哈哈 哈哈哈哈 */
连接数据库
mysql -u root -p12345678
退出数据库连接
使用exit;
命令可以退出连接
查询MYSQL版本
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.27 | +-----------+ 1 row in set (0.00 sec)
查看所有数据库
show databases;
使用数据库
如果想要操作数据库,需要使用use 数据库名;
来选择要操作的数据库
查看所选择数据库的所有表
show tables;
查看表的具体信息
使用describe 表名;
可以查看表中的字段信息
mysql> describe user; +--------------+---------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+-------------------+-----------------------------------------------+ | id | bigint | NO | PRI | NULL | auto_increment | | userName | varchar(256) | YES | | NULL | | | userAccount | varchar(256) | NO | UNI | NULL | | | userAvatar | varchar(1024) | YES | | NULL | | | gender | tinyint | YES | | NULL | | | userRole | varchar(256) | NO | | user | | | userPassword | varchar(512) | NO | | NULL | | | accessKey | varchar(512) | YES | | NULL | | | secretKey | varchar(512) | YES | | NULL | | | createTime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | updateTime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | isDelete | tinyint | NO | | 0 | | +--------------+---------------+------+-----+-------------------+-----------------------------------------------+ 12 rows in set (0.00 sec)
创建数据库
create database [if not exists] 数据库名称 character set 字符编码; mysql> create database practice character set utf8; Query OK, 1 row affected, 1 warning (0.01 sec) -- 已存在的数据库不能重复创建,不然会报错 mysql> create database practice character set utf8; ERROR 1007 (HY000): Can't create database 'practice'; database exists -- 使用if not exists,可以判断数据库不存在的时候才创建数据库,这样就不会报错 mysql> create database if not exists practice character set utf8; Query OK, 1 row affected, 2 warnings (0.01 sec)
查看创建数据库的语句
mysql> show create database practice; +----------+------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------------------------------------------+ | practice | CREATE DATABASE `practice` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
修改用户密码
数据表管理
创建数据表
如:
CREATE TABLE IF NOT EXISTS `student` ( `id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY, `name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名' ) COMMENT '学生表'
查看创建数据表的语句
mysql> use practice; Database changed mysql> show create table student; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生表' | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
查看表的结构
mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
修改表名
alter table 旧表名 rename as 新表名;
mysql> alter table student rename as teacher; Query OK, 0 rows affected (0.02 sec)
删除表
drop table if exists 表名;
mysql> show tables; +--------------------+ | Tables_in_practice | +--------------------+ | teacher | +--------------------+ 1 row in set (0.00 sec) mysql> drop table if exists teacher; Query OK, 0 rows affected (0.02 sec) mysql> show tables; Empty set (0.00 sec)
数据表字段管理
给指定表增加字段
alter table 表名 add 字段名 列属性;
mysql> alter table teacher add gender bigint; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> alter table teacher add age int(11); Query OK, 0 rows affected, 1 warning (0.02 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age | int | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
修改指定表的字段
修改列属性
alter table 表名 modify 字段名 列属性;
mysql> alter table teacher modify age varchar(11); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age | varchar(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
同时修改字段名和字段属性
alter table 表名 change 旧字段名 新字段名 列属性;
mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age | varchar(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table teacher change age age1 int(2); Query OK, 0 rows affected, 1 warning (0.07 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age1 | int | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
删除指定表的字段
mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age1 | int | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table teacher drop gender; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teacher; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | age1 | int | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
给表添加外键
创建表的时候增加外键
-- 创建专业表 CREATE TABLE IF NOT EXISTS `major` ( `id` BIGINT NOT NULL auto_increment COMMENT '专业id' PRIMARY KEY, `name` VARCHAR ( 30 ) NOT NULL COMMENT '专业名称' ) COMMENT '专业表' -- 创建学生表,并将学生表的major_id声明为外键,引用专业表的id字段 CREATE TABLE IF NOT EXISTS `student` ( `id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY, `name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名', `major_id` BIGINT NOT NULL COMMENT '专业id', key `FK_majorId` (`major_id`), CONSTRAINT `FK_majorId` FOREIGN KEY (`major_id`) REFERENCES `major`(`id`) ) COMMENT '学生表'
给已有表添加外键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列的字段名) REFERENCES 引用哪个表(哪个字段)
【案例】
ALTER TABLE `student` ADD CONSTRAINT `FK_majorId` FOREIGN KEY(`major_id`) REFERENCES `major`(`id`);
外键使用建议
当使用了外键时,如果需要删除被引用表的记录,需要先删除引用表的记录。如上面的例子所示,如果需要删除一个专业,需要先删除这个专业所对应的学生。
建议:使用外键约束会让开发者很痛苦,测试数据不方便,建议不使用外键
数据插入语句 INSERT
数据插入
insert into `表名` (`字段名1`,`字段名2`) values('字段值1','字段值2');
一次性插入多条数据
insert into `表名` (`字段名1`,`字段名2`) values('字段值11','字段值12'),('字段值21','字段值22'),('字段值31','字段值32');
案例
mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | major_id | bigint | NO | MUL | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc major; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) -- insert into `表名` (`字段名`) values('字段值'); mysql> insert into `major` (`name`) values('计算机科学'); Query OK, 1 row affected (0.01 sec) mysql> insert into `major` (`name`) values('工业工程'); Query OK, 1 row affected (0.01 sec) mysql> select * from major; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 计算机科学 | | 2 | 工业工程 | +----+-----------------+ 2 rows in set (0.00 sec) -- 插入多条数据 mysql> insert into `major` (`name`) values('自动化'),('机械工程'),('工业设计'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from major; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 计算机科学 | | 2 | 工业工程 | | 3 | 自动化 | | 4 | 机械工程 | | 5 | 工业设计 | +----+-----------------+ 5 rows in set (0.00 sec) mysql> insert into `student` (`name`,`major_id`) values('小明','1'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 小明 | 1 | +----+--------+----------+ 1 row in set (0.00 sec) -- 插入数据的时候,不一定要给表的所有字段赋值,只赋值部分字段也可以,前提是省略的字段可以为空 mysql> insert into `student` (`major_id`) values('1'); Query OK, 1 row affected (0.01 sec) -- 插入数据的时候,没有写入学生的名字,MYSQL会自动填入默认值“匿名” mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 小明 | 1 | | 2 | 匿名 | 1 | +----+--------+----------+ 2 rows in set (0.00 sec)
数据更新语句 UPDATE
没有指定匹配条件的话,会所有表的所有记录
mysql> select * from major; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 计算机科学 | | 2 | 工业工程 | | 3 | 自动化 | | 4 | 机械工程 | | 5 | 工业设计 | +----+-----------------+ 5 rows in set (0.00 sec) mysql> update major set name='智能制造'; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from major; +----+--------------+ | id | name | +----+--------------+ | 1 | 智能制造 | | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+--------------+ 5 rows in set (0.00 sec)
根据条件来修改记录
mysql> update major set name='智能科学与技术' where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from major; +----+-----------------------+ | id | name | +----+-----------------------+ | 1 | 智能科学与技术 | | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+-----------------------+ 5 rows in set (0.00 sec)
一次性修改多个字段的值
mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 小明 | 1 | | 2 | 匿名 | 1 | +----+--------+----------+ 2 rows in set (0.00 sec) mysql> update student set name='李华',major_id=2 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 李华 | 2 | | 2 | 匿名 | 1 | +----+--------+----------+ 2 rows in set (0.00 sec)
稍微高级一点的使用
update load_product,product set load_product.product_id = product.id where load_product.code=product.code