登陆MySQL数据库多种方式:
mysql –host localhost –user root –password
mysql -h localhost -u root -p
mysql -uroot -p
使用 \g 等于分号 ; 结束语句,还是执行:
![图片[1]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-50.png)
![图片[2]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-51.png)
引号等待输入,单引号与双引号成对使用
![图片[3]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-52.png)
按ESC键 清除命令, \c 取消所以命令
![图片[4]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-53.png)
使用 exit 或 quit 退出数据库
![图片[5]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-54.png)
查看数据库:show databases;
![图片[6]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-55.png)
新建账户,并授权普通账户访问某数据库权限;
grant all on discuzsql.* to "user"@"%" identified by "password";
创建新数据库: create database student;
删除数据库:drop database student;
创建表:create table abc;
删除表: drop table abc;
create table student(id int(10) primary key auto_increment, name varchar(30),age tinyint(2) );
![图片[7]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-56.png)
查看表结构: desc student;
![图片[8]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-57.png)
插入数据: insert into student (name,age) values(“zhangsan”,22);
查询表中的数据: select * from student;
![图片[9]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2019/12/image-58.png)
导出数据库: mysqldump -uroot -p discuzsql>d:\discuzsql.sql
导入数据库:
方法一:mysql -uroot -p discuzsql < d:\discuzsql.sql
方法二: 进入数据库 mysql >use discuz;
导入数据库 mysql > source d:\discuz.sql;
数据查询:
select * from stu where sname =”李四”;
![图片[10]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2020/02/image.png)
模糊查询:
select sname,sex from stu where sname like “李%”
字符串与字段连接查询
select concat(“姓名:”,sname, “性别:”,sex, “QQ:” , qq) from stu;
![图片[11]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2020/02/image-1.png)
起别名
select concat(“姓名:”,sname, “性别:”,sex, “QQ:” , qq) as stuinfo from stu;
![图片[12]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2020/02/image-4.png)
select concat(“姓名:”,sname, “性别:”,if(sex,”男”,”女”), “QQ:” , qq) as stuinfo from stu;
![图片[13]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2020/02/image-2.png)
select concat(“姓名:”,sname, “性别:”,if(sex,”男”,”女”), “QQ:” , qq) as stuinfo from stu where sex=0 and sname like “%玉%”;
![图片[14]-MySQL操作笔记-](https://fbi.kim/wp-content/uploads/2020/02/image-3.png)