Mysql数据库操作指引（一）——入门知识
简介：MySql是一种开源的关系型数据库,本文主要为MySQL基础知识的总结，MySQL的基础知识很多，这里介绍的是常用的知识，通过实际操作进行检验。详细内容请参考下文。一、登陆MySQL数据库
执行指令# mysql –u root -p
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/046e1292f43d45bbb65104710773c296~tplv-tt-large.image?x-expires=1990622529&x-signature=CzhhFkK4xFpZqNeQS%2FIin9ZAZIQ%3D
二、Mysql数据库的创建和修改
1、创建一个名称为mydb001的数据库
create database mydb001;
show databases;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d85f336375374c5f940ba45d4913f342~tplv-tt-large.image?x-expires=1990622529&x-signature=hfXy3dk2F0DrMrXCp%2FkyL9m0hoY%3D
2、创建一个使用utf-8字符集的mydb002数据库
create database mydb002 character set utf8;
show databases;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/add8a26f57bb477bb379a4954a43dafe~tplv-tt-large.image?x-expires=1990622529&x-signature=ibrB0fm6MXxg%2BJx0W0WRnN9zCBw%3D
3、创建一个使用utf-8字符集，并带校对规则的mydb003数据库
create database mydb003 character set utf8 collate utf8_general_ci;
show databases;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/92e3cea7a8154b85a264ef3e200dfee9~tplv-tt-large.image?x-expires=1990622529&x-signature=Q%2BmLp2qc1IEvmC7o2kuwjCHfB6Q%3D
4、查看前面创建的mydb002数据库的定义信息
show create database mydb002;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/eacc825f22db4bb98f9f1805bd4b83b7~tplv-tt-large.image?x-expires=1990622529&x-signature=KDvA3eacYUiSZQveTPwjPnq3vyw%3D
5、删除前面创建的mydb001数据库
drop database mydb001;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1a972aa81570405ab871f522744182f0~tplv-tt-large.image?x-expires=1990622529&x-signature=wvZLEn9VrO8ta7GGJGXTL62Og0I%3D
6、查看服务器中的数据库，并把其中某一个库的字符集修改为gb2312
alter database mydb002 character set gb2312;
show create database mydb002;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/bea6c30a0cd34679b1bf2a11efe5322b~tplv-tt-large.image?x-expires=1990622529&x-signature=ZjW%2Fi99C0Mrx%2FtIyxl03r4bfn4g%3D
三、Mysql数据库创建表并对表结构进行修改
1、创建一个员工表
use mydb002;
create table employee
(
id int,
name varchar(40),
sex varchar(4),
birthday date,
entry_date date,
job varchar(40),
salary decimal(8,2),
resume text
);
show tables; 查看库的所有表
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f64b78b06f3141239de7554f5aa5dc56~tplv-tt-large.image?x-expires=1990622529&x-signature=cnisNz9sC5i0SN6sF999KtYp3b0%3D
show create table employee; 查看表的创建细节
desc employee; 看表结构
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/cbece78e5ba9416a947da4cf5480310c~tplv-tt-large.image?x-expires=1990622529&x-signature=%2FE%2BrB0q63XCAoUJzzJ0Ptb9g%2BDY%3D
2、在上面员工表的基本上增加一个image列
alter table employee add image blob;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/fa84c04237b34c07bc473cddcb96b511~tplv-tt-large.image?x-expires=1990622529&x-signature=CybN8947zZMJ%2BoZusyRdTDLnAq0%3D
3、修改job列，使其长度为60
alter table employee modify job varchar(60);
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/051c0ad49a2b419d971b6eb04b6b896c~tplv-tt-large.image?x-expires=1990622529&x-signature=6i6W4H6rbJlVchJ8fmNvhJAwMoM%3D
4、删除sex列
alter table employee drop sex;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/50f6a6dc54fb433697b3e80a0be56da1~tplv-tt-large.image?x-expires=1990622529&x-signature=Mq6ioMydUsz5%2F%2FYZRVqfDOWzsWM%3D
5、表名改为user
rename table employee to user;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ca6a8da3696a45778f227d47325d2b8f~tplv-tt-large.image?x-expires=1990622529&x-signature=f4AxMpE0VWVtQbAOLwP6fpji2Hg%3D
6、修改表的字符集为utf-8
alter table user character set utf8;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/eeac0b827d184d9b8cfc30687e2cf22d~tplv-tt-large.image?x-expires=1990622529&x-signature=8GtWYFldN%2FnvJ3g92Y8skAFd0cA%3D
7、列名name修改为username
alter table user change column name username varchar(40);
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/dd4ea30fe49e44b48bdbf48338a2f885~tplv-tt-large.image?x-expires=1990622529&x-signature=yk5WxjQB92fs%2F9DDHYbH02l2kbs%3D
8、删除表
drop table user;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f5a787a484414b0b918fd097f5c10f58~tplv-tt-large.image?x-expires=1990622530&x-signature=vqxpdtu02z37BgwX4%2F0JzT2iOfo%3D
四、Mysql数据表插入数据
1、使用insert语句向表中插入三个员工的信息。
(插入的字符和日期类型数据应该加上单引号)
insert into employee(id,name,birthday,entry_date,job,salary,resume) values(1,'aaa','2015-09-29','1980-09-09','bbb',90,'aaaaa');
select * from employee;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/b296ba31d0674560b32d290585579d29~tplv-tt-large.image?x-expires=1990622530&x-signature=mdtpNpXhitA1f5gHwg9ktpL4ryI%3D
2、插入数据的细节1(可以不用指明字段，只要插入的值和表的字段完全匹配就行)
insert into employee values(2, 'bbb', null,'1980-09-09','1980-09-09','bbb',90,'bbbbb');
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/820a7382eaca4d8f888d51db3ae2b8f3~tplv-tt-large.image?x-expires=1990622530&x-signature=kl5UrhhcondcFwaKu6TTbjja%2Fks%3D
3、插入数据的细节2(可以把每个插入的字段值都加上单引号，mysql拿到数据以后会自动去转换成相应的类型)
insert into employee values('3','ccc', 'null','1990-09-09','1990-09-09','ccc','00','ccccc');
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/5744b891aeb44daa9907c2b6dbbf8225~tplv-tt-large.image?x-expires=1990622530&x-signature=DnH%2FZoL0BFjcEb4EsKomwW68Ej8%3D
4、插入数据的细节3(插入中文)
要告诉mysql客户端采用gb2312编码
show variables like 'chara%';
set character_set_client=gb2312;
insert into employee(id, name) values('4','李四');
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/5c0a538f7fe245bc956e531cd606fe1e~tplv-tt-large.image?x-expires=1990622530&x-signature=Acl8iT%2BBxJWofYqeQjbY0EFXEQA%3D
要想查看时不乱码
show variables like 'chara%';
set character_set_results=gb2312;
select * from employee;
五、Mysql数据表更新数据
1、将所有员工薪水修改为5000元。
update employee set salary=5000;
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/43a11223851a40e9822ad6adae8a0371~tplv-tt-large.image?x-expires=1990622530&x-signature=jPeZVECwM62W%2B2RLF0Orq4VfNXI%3D
2、将姓名为’bbb’的员工薪水修改为3000元。
update employee set salary=3000 where name='bbb';
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/c37c8e177c544e04b591546d8491b2f8~tplv-tt-large.image?x-expires=1990622530&x-signature=Izn%2FjpKW%2Fb7PwCv7GuvagECpuuU%3D
3、将姓名为’bbb的员工薪水修改为4000元，job改为engineer。
update employee set salary=4000,job='engineer' where name='bbb';
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3b517535a05d47ef90fc0249f250e098~tplv-tt-large.image?x-expires=1990622530&x-signature=WFsSGsnLGw0evIO1iCqV5pwddtA%3D
4、将bbb的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='bbb';
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/868a92bf56cf4d7b9d584baeb9b8947c~tplv-tt-large.image?x-expires=1990622530&x-signature=C8wUc1E5bj01YLGuHuRl1IpSHeI%3D
六、Mysql数据表删除表中的记录
1、删除表中名称为’bbb’的记录。
delete from employee where name='bbb';
https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/29893f91574e4c80bc160211eccd248f~tplv-tt-large.image?x-expires=1990622530&x-signature=DHSfMcxd8hkyqg68TgsVVqon3NM%3D
2、删除表中所有记录。
delete from employee;
3、使用truncate删除表中记录。
truncate table employee;
4、delete 和truncate table的区别
delete是把表中的记录一条一条地删除，truncate是摧毁表结构，再重建表结构。 转发了 转发了 转发了
页:
[1]