mysqlnavicate查询_MysqlNavicate基础操作与SQL语句版本
5.7.29
SQL数据的增删改查:此部分所有SQL语句在navicat中与mysql命令⾏执⾏效果⼀样,只是mysql服务端在命令⾏执⾏,⽽navicat只是在客户端的图形化打开操作。
⼀、进⼊数据库
.连接数据库:mysql -uroot -p
.查看数据库:show databases;
.进⼊指定数据库:use test;
.查看表:show tables;
.查看当前⽤户名:select user();
.查看当前所在数据库:select database();
.修改字符集:set names 'gbk';
.查看设计表:desc students;
.修改普通⽤户密码:update user set authentication_string=password('123456') where user='wzx';
mysql语句的执行顺序flush privileges;
.忘记root⽤户密码:进⼊mysql的配置⽂件,到my.ini的配置⽂件:
24小时qq再[mysqld]下加⼊:skip-grant-tables后再重新启动mysql服务:
再进⾏连接测试:
.登录后记得删除配置⽂件并重新设置root密码:update user set authentication_string=password('123456') where user='wzx'; flush privileges;
.修改命令结束符:delimiter //?;
.查看设计表语句:show create table students;
.给表字段添加索引:create index name_index on students(name(255));
.开启运⾏时间监控:set profiling=1;
.查看监控结果:show profiles;
.查看表索引:show index from students;
注:主键、unique唯⼀值约束时默认会创建索引。⼀个表中的⼀个字段理论上可以有多个索引。.删除表索引:drop index name_index on students;
.创建表数据时直接设置索引:
create table students(
id int primary key auto_increment ,
name varchar(10) not Null,
sex varchar(4) default "男",
age tinyint unsigned,
height decimal(5,2),
registration_time datetime
key (name)
);
⼆、创建数据库以及数据恢复与备份
.创建数据库:create database test0412 charset='utf8';
.删除数据库:drop database test0412;
windows下备份与恢复数据库:
.备份数据库:需要以管理员⾝份运⾏sql:mysqldump -uroot -p test > e:\test_back.sql;
.恢复数据:需要先创建数据库再导⼊数据库:
1:创建数据库:testback:create database testback charset='utf8';
2:导⼊数据库:mysql -uroot -p testback < e:\test_back.sql
linux下备份与恢复数据库:
备份数据库:mysqldump -h127.0.0.1 -uroot-proot test>/home/testback.sql
恢复数据库:mysql -h127.0.0.1 -uroot-proot-f test
三、创建表
1.1:直接创建新表
create table students(
id int primary key auto_increment ,
name varchar(10) not Null,
sex varchar(4) default "男",
age tinyint unsigned,
height decimal(5,2),
registration_time datetime
);
1.2:将需要备份数据表到新表
create table good_back select * from goods;
1.3:查询表数据后添加到新表
create table good_cate(
id int primary key auto_increment ,
goods_name varchar(30)
) select distinct good as goods_name from goods;
1.4:添加外键关系到已有数据表:
#### alter table 从表 add foreign key (从表字段) references 主表(主表字段);
alter table goods add foreign key (brand_id) references goods_brand(brand_id); 1.5:删除外键:
#### alter table 从表 drop foreign key (从表字段);
alter table goods drop foreign key (brand_id);
1.6:数据表时添加外键:
create table goods(
id int primary key auto_increment ,
goods_id int,
foreign key(goods_id) references goods_brand(goods_id)
) ;
四、增加值
2.1:按照数据库中字段顺序插⼊
什么是同步通信什么是异步通信INSERT into students value(0,"aa","⼥",21,3,"2019-02-12");
2.2:按照⾃⼰指定指定字段顺序插⼊
INSERT into students(sex,name,height,registration_time,age) value("保密","wqww",123,"2017-02-2",21);
2.3:在设计表中添加字段并给予说明
alter table students add new varchar(20) COMMENT '⽤户名';
2.4:⼀次性插⼊多个记录
insert into students values (0,'⽼夫3',20),(0,'⽼夫4',20),(0,'⽼夫5',20);
2.5:将查询结果插⼊:
insert into goos_cate(cate_name) select distinct cate from goods;
五、修改记录
3.1:静态数据修改
update students set name="明天会更⾼" where id=1;
java框架书籍推荐>splitter pdf3.2:动态数据修改
update students set age=age+20 where id=2;
3.3:表数据修改
update goods g inner join goods_cate c on g.cate=c.cate_name set g.cate=c.cate_id;
六、删除记录
delete from students where id=2;
七、删除表
drop table students1;
drop table if exists sutdents1;
⼋、查询
6.1:给字段起别名:select name as 姓名,age as 年龄 from students;
6.2:给表起别名:select s.name as 姓名,s.age as 年龄 from students as s;
6.3:去重:select distinct age,class from students;
6.4:逻辑运算:select * from students where not name='王昭君';
fpga三种编程语言6.5:模糊查询所有字符:select * from students where name like '王%';
6.6:模糊查询指定字符个数:select * from students where name like '王__';
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论