MySQL数据库入门详细笔记

2022-10-09 15:12:38

ps:

中括号[]里的内容都是可以省略的，按需要添加

数据库操作

创建数据库

语法：create database [if not exists] 数据名 charset=gbk;

create database if not exists tow charset=gbk;
Query OK, 1 row affected

if not exists -> 判断是否重复
charset=gbk -> 简体中文
gb2312 -> 简体中文
utf8 -> 通用字符编码

显示当前时间、用户名、数据库版本

select now(), user(), version();
+---------------------+----------------+-----------+
| now()               | user()         | version() |
+---------------------+----------------+-----------+
| 2020-04-23 16:58:06 | root@localhost | 5.5.30    |
+---------------------+----------------+-----------+
1 row in set

查看数据库信息

语法：show create database 数据库名;

mysql> show create database tow;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| tow      | CREATE DATABASE `tow` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set

修改数据库编码

语法1：alter database 数据库名 default character set gb2312;
语法2：alter database 数据库名 character set gbk;

两种方法都可以更改各类编码

例一：

alter database tow default character set gb2312;
Query OK, 1 row affected

例二：

alter database tow character set gbk;
Query OK, 1 row affected

查询有哪些数据库

show databases;#
+--------------------+
| Database           |
+--------------------+
| information_schema |
| frist              |
| mysql              |
| performance_schema |
| tow                |
+--------------------+
5 rows in set

删除数据库

语法：drop database 数据库名;

drop database tow;
Query OK, 0 rows affected

选择数据库

语法：use 数据库名;

 use frist;
Database changed

表的操作

创建表

create table 表名(
    字段名 字段类型 [not null] [primary key] [null] [auto_increment] [comment],
    字段名 字段类型 [default] 	#末尾不需要加分号
)[engine=innodb];

[engine=innodb] -> 选择存储引擎；menory引擎将数据存放在内存中，重启服务后数据会丢失，但是读取速度快。
[not null] -> 不允许为空
[null] -> 允许为空
[auto_increment] -> 自动递增例如编号,默认从1开始如果有输入就会从你输入的数开始。
[primary key] ->主键
[comment] -> 备注
[default] -> 默认值
set nams gbk;

修改表

添加字段

语法：alter table 表名 add 字段名 字段类型;

 alter table teacher add addr varchar(50) default‘地址不详‘  comment‘地址‘;
Query OK, 0 rows affected

在第一个位置上添加字段

语法：alter table 表名 add 字段名 字段类型 first;

 alter table teacher add number int comment‘编号‘ first;
Query OK, 0 rows affected

在指定的字段后添加字段

语法：alter table 表名add 字段名 字段类型 after 指定的字段名;

alter table teacher add phone varchar(11) not null after name;
Query OK, 0 rows affected

删除字段

语法：alter table 表名drop 字段名;

alter table teacher drop number;
Query OK, 0 rows affected

修改字段名和类型

语法：alter table 表名 change 原字段名 新字段名 数据类型;

alter table teacher change addr dizhi char(100);
Query OK, 0 rows affected

alter table test change id id int auto_increment;
Query OK, 3 rows affected

修改字段类型不改字段名

语法：alter table 表名 modify 字段名 字段类型;

alter table teacher modify dizhi varchar(50);
Query OK, 0 rows affected

修改表名

alter table 表名 rename to 新表名;

alter table teacher rename to techer;
Query OK, 0 rows affected

查询有哪些表

show tables;
+-----------------+
| Tables_in_frist |
+-----------------+
| stu             |
| sudent          |
| teacher         |
+-----------------+
3 rows in set

删除表

语法：drop table [if exists]表名;

drop table if exists stu;
Query OK, 0 rows affected

没有stu表不加 if exists 的情况

drop table stu;
1051 - Unknown table ‘stu‘

没有stu表但是加了 if exists 后

drop table if exists stu;
Query OK, 0 rows affected

复制表

复制表结构和数据；不能复制表的主键，但能够复制表的数据

语法一：create table 新表名 select * from 需要复制的表名;
		create table 复制后存放的表名 as(select * from 需要复制的表名)；	#效果和上面的完全相同

mysql> create table stu1 select * from stu;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu1;
+----+--------+------+-------+
| id | name   | addr | score |
+----+--------+------+-------+
|  1 | 马云   | 杭州 |    77 |
|  2 | 马化腾 | 深圳 |    66 |
|  3 | 马斯特 | 美国 |    60 |
+----+--------+------+-------+
3 rows in set

mysql> desc stu1;	#主键不能被复制
+-------+-------------+------+-----+----------+-------+
| Field | Type        | Null | Key | Default  | Extra |
+-------+-------------+------+-----+----------+-------+
| id    | int(11)     | NO   |     | 0        |       |
| name  | varchar(20) | NO   |     | NULL     |       |
| addr  | varchar(50) | YES  |     | 地址不详 |       |
| score | int(11)     | YES  |     | NULL     |       |
+-------+-------------+------+-----+----------+-------+
4 rows in set

复制表的结构；只能复制表结构，不能复制表数据

语法二：create table 新表名 like 需要复制的表名;

mysql> create table stu2 like stu;
Query OK, 0 rows affected

mysql> select * from stu2;
Empty set

mysql> desc stu2;	#主键被复制了
+-------+-------------+------+-----+----------+----------------+
| Field | Type        | Null | Key | Default  | Extra          |
+-------+-------------+------+-----+----------+----------------+
| id    | int(11)     | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20) | NO   |     | NULL     |                |
| addr  | varchar(50) | YES  |     | 地址不详 |                |
| score | int(11)     | YES  |     | NULL     |                |
+-------+-------------+------+-----+----------+----------------+
4 rows in set

显示创建表的语句

语法：show create table 表名;

 show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

查看表结构

语法：desc 表名;	#缩写
	describe 表名;

describe test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set

修改表编码

（解决中文编码问题）

alter table 表名character set gbk;

alter table 表名 convert to character set utf8;

set names gbk;	#最简单的方法，修改整个数据库

最后面的都是编码名称，按需要填写，一般常用的也就 gbk/utf8/gb2312

数据操作

创建测试表

create table stu(
    -> id int auto_increment primary key comment‘主键‘,
    -> name varchar(20) not null,
    -> addr varchar(50) default‘地址不详‘,
    -> score int comment‘成绩‘
    -> );
Query OK, 0 rows affected

插入一条数据

语法：insert into 表名（字段名，字段名，…）values(字段值,…,字段值);

例一：

insert into stu (id,name,addr,score) values(19022100,‘tom‘,‘北京‘,88);
Query OK, 1 row affected

例二：插入的字段可以和表的字段顺序不一致只要一一对应即可

insert into stu(name,score,addr,id) values(‘berry‘,77,‘北京‘,2);
Query OK, 1 row affected

例三：可以只插入部分字段，但是非空字段必须插入

insert into stu(id,name,addr) values(19,‘ketty‘,‘上海‘);
Query OK, 1 row affected

例四：自动增长列的值可以不用也可以直接插入null，数据库会自动插入增长的数

insert into stu(name,addr) values(‘rose‘,‘北京‘);
Query OK, 1 row affected

例五：插入值的顺序和个数与表字段的顺序和个数一致，插入的字段可以省略

insert into stu values(null,‘大海‘,‘广西‘,100);
Query OK, 1 row affected

例七：通过default关键字插入默认值

 insert into stu values(null,‘toni‘,default,50);
Query OK, 1 row affected

查询表里的所有数据

语法：select * from 表名;

select * from stu;
+----------+------+------+-------+
| id       | name | addr | score |
+----------+------+------+-------+
| 19022100 | tom  | 北京 |    88 |
+----------+------+------+-------+
1 row in set

插入多条数据

语法：insert into 表名 values(字段值1,...,字段值n),....,(字段值1,...字段值n);

insert into stu values(null,‘马云‘,‘杭州‘,66),(null,‘马化腾‘,‘深圳‘,55);
Query OK, 2 rows affected

更新数据

语法：update 表名 set 字段值=值 [where 条件];

例一：把2号学生的地址改为湖北

update stu set addr = ‘湖北‘ where id = 2;
Query OK, 1 row affected

例二：把马云的成绩改为99

update stu set score = 99 where name = ‘马云‘;
Query OK, 1 row affected

例三：将马化腾的地址改为广东，成绩改为80

update stu set addr = ‘广东‘,score = 80 where name = ‘马化腾‘;
Query OK, 1 row affected

例四：将北京的学生成绩全部改为77

update stu set score = 60 where addr = ‘北京‘;
Query OK, 2 rows affected

例五：将学号为1，2的两位学生成绩改为65

update stu set score = 65 where id = 1 or id = 2;
Query OK, 2 rows affected

条件可以省略，但是如果省略就是更改所有数据

删除数据

语法：delete from 表名 [where 条件];

例一：删除学号是2的学生

delete from stu shere id = 2;

例二:删除小于等于65分的学生

 delete from stu where score <= 65;
Query OK, 4 rows affected

例三：删除表中所有记录

delete from stu;
Query OK, 3 rows affected

清空表

语法：truncate table 表名;

truncate table stu;
Query OK, 0 rows affected

delete from 和 truncate table 的区别：
- delete from :遍历表记录，一条一条的删除。
- truncate table：将原表销毁再创建一个结构相同的新表。这方法更高效

查询表

语法：select 列名 from 表名;

例一：

mysql> select id,name from stu;
+----+--------+
| id | name   |
+----+--------+
|  1 | 马云   |
|  2 | 马化腾 |
|  3 | 马斯特 |
+----+--------+
3 rows in set

mysql> select * from stu; # *号表示所有字段
+----+--------+------+-------+
| id | name   | addr | score |
+----+--------+------+-------+
|  1 | 马云   | 杭州 |    77 |
|  2 | 马化腾 | 深圳 |    66 |
|  3 | 马斯特 | 美国 |    60 |
+----+--------+------+-------+
3 rows in set

字符集

字符集：字符在保存和传输是对应的二进制编码集合。

创建测试数据表

create table test(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected

set names gbk;	#设置服务端 和返回值的编码格式为gbk

字符编码错误错误

1366 - Incorrect string value: ‘\xAC\xE5\x8C\x96\xE8\x85...‘ for column ‘name‘ at row 1

解决方法：将编码改为与数据库匹配的编码

set names 编码名称;

后续持续更新关注我第一时间获得更新情况