配置远程连接：win10下连接ubuntu中的mysql
在ubuntu中的相关操作：
1、修改mysql配置文件中的绑定地址修改为四个零
/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
2、进入mysql
grant all on *.* to admin@'%' identified by '123456' with grant option; #admin用户名，123456ubuntu中mysql密码
flush privileges;
grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option;
flush privileges;
use mysql
select user，host from user；
另外，也可确认下端口，默认是3306 show global variables like 'port';
3、重启mysql
service mysql restart
4、此时在win下使用Navicat就能连接
查看ubuntu的ip：ip addr show
5、远程连接
mysql -hip地址 -u用户 -p
登录后：
数据库：（文件夹操作）
看版本：select version（）；#5.7.20
显示所有数据库：show databases；
切换/选择数据库：use dbname
查看当前使用的db：select database（）；
创建db：create dababase dbname charset=utf8；
删除db：drop database dbname；
表：（文件操作）
创建表可以指定引擎engine=innodb
注：innodb支持事务，原子操作；myisam支持全局索引
显示当前数据库中所有的表：show tables；
删除表：drop table 表名；
清空表：delete from 表名；再次插入，自增列从删除前开始。如果要改变下一次自增值则，alter table 表名 auto_increment=值；
清空表：truncate table 表名；速度快，再次插入，自增列从1开始
例：创建表
mysql> create table students( id int auto_increment primary key not null, name varchar(10) not null, gender bit default 1, birthday datetime);
字段名 类型 约束
id行较多，当作固定写法记住即可
查看表的结构：desc students；
修改表：alter table 表名 add/change/drop 列名 类型；
建表没有主键：alter table class change id id int primary key;
例：alter table students add isDelete bit default 0；
mysql> alter table students change name sname char(20) not null; Query OK, 5 rows affected (1.51 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from students; +----+--------+--------+---------------------+----------+ | id | sname | gender | birthday | isDelete | +----+--------+--------+---------------------+----------+ | 1 | 鲤鱼 | | 1991-01-01 00:00:00 | | | 3 | ee | | 2017-01-01 00:00:00 | | | 4 | mike | | NULL | | | 5 | ldh | | 2016-05-09 00:00:00 | | | 6 | res | | 2016-08-09 00:00:00 | | +----+--------+--------+---------------------+----------+
改表名：rename table 原表名 to 新表名；
查看表的创建语句：show create table 表名；
数据行操作：
增加：语法：insert into 表名 其它；
第一种、全列插入
mysql> insert into students values(0,'ee',1,'1919-1-1',0); #按照列的顺序写，值数量和字段数量一致，id列由数据库自动生成，这里起到占位作用 Query OK, 1 row affected (0.20 sec) mysql> select * from students; +----+--------+--------+---------------------+----------+ | id | name | gender | birthday | isDelete | +----+--------+--------+---------------------+----------+ | 1 | 鲤鱼 | | 1991-01-01 00:00:00 | | | 2 | ly | | NULL | | | 3 | ee | | 1919-01-01 00:00:00 | | +----+--------+--------+---------------------+----------+
第二种、部分列插入
insert into students(gender,name) values(0,'mike');
值的顺序和前面的一致。
第三种、一次插入多行
a、全列插入多行：insert into 表名 values（），（），......；
b、部分列插入多行：insert into 表名（列1，列2，...） values（值1，值2，...），（值1，值2，...）....；
第四种、跨表插入
mysql> insert into class(caption) select tname from teacher where tid=2 or tid=3;
修改：
mysql> update students set gender=0,birthday='2017-1-1' where id=3; #where后是条件 Query OK, 1 row affected (0.13 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students; +----+--------+--------+---------------------+----------+ | id | name | gender | birthday | isDelete | +----+--------+--------+---------------------+----------+ | 1 | 鲤鱼 | | 1991-01-01 00:00:00 | | | 2 | ly | | NULL | | | 3 | ee | | 2017-01-01 00:00:00 | | | 4 | mike | | NULL | | +----+--------+--------+---------------------+----------+
删除：物理删除
delete from students where id=2;
逻辑删除：本质为修改
mysql> select * from students; #此时最后一列值为全为0 +----+--------+--------+---------------------+----------+ | id | name | gender | birthday | isDelete | +----+--------+--------+---------------------+----------+ | 1 | 鲤鱼 | | 1991-01-01 00:00:00 | | | 3 | ee | | 2017-01-01 00:00:00 | | | 4 | mike | | NULL | | +----+--------+--------+---------------------+----------+ mysql> update students set isDelete=1 where id=3; #id=3，最后一列值改为1 Query OK, 1 row affected (0.08 sec) mysql> select * from students where isDelete=0; #显示表，设置条件 +----+--------+--------+---------------------+----------+ | id | name | gender | birthday | isDelete | +----+--------+--------+---------------------+----------+ | 1 | 鲤鱼 | | 1991-01-01 00:00:00 | | | 4 | mike | | NULL | | +----+--------+--------+---------------------+----------+
备份和恢复：项目迁移时用到
备份
l@l:~$ sudo -s #获取管理员权限 [sudo] l 的密码： root@l:~# cd /var/lib/mysql #进入mysql目录 root@l:/var/lib/mysql# mysqldump -uroot -p lx >~/lx.sql; #指定备份的数据库名，备份到的位置+sql文件 Enter password: root@l:/var/lib/mysql# #此时家目录就有了lx.sql的备份文件
数据恢复：
root@l:/# exit #可以退出管理员权限了 exit l@l:~$ ls #有了lx.sql文件 Desktop Documents examples.desktop Music Public Videos
Downloads lx.sql Pictures Templates l@l:~$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> create database lxbak charset=utf8; #连接数据库后创建新的数据库，lxbak Query OK, 1 row affected (0.07 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lx | | lxbak | | mysql | | performance_schema | | sys | +--------------------+ mysql> exit #退出数据库 Bye l@l:~$ mysql -uroot -p lxbak < lx.sql #恢复 Enter password: l@l:~$ mysql -uroot -p #进入并查看 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use lxbak Database changed mysql> show tables; +-----------------+ | Tables_in_lxbak | +-----------------+ | students | +-----------------+ mysql> select * from students; +----+--------+--------+---------------------+----------+ | id | name | gender | birthday | isDelete | +----+--------+--------+---------------------+----------+ | 1 | 鲤鱼 | | 1991-01-01 00:00:00 | | | 3 | ee | | 2017-01-01 00:00:00 | | | 4 | mike | | NULL | | +----+--------+--------+---------------------+----------+
唯一索引：用于快速查找。约束不能重复（可以为空），主键不能重复并不能为空。如在创建表时：
- unique 约束名 （约束字段1，约束字段二）
外键：
- 一对一：外键+unique。如用户信息表和登录表，登录表中有权限的用户才设密码，外键用户+唯一约束。
- 一对多：通常情况
- 多对多：如用户表、主机表、主机用户关系表
mysql> create table class(cid int not null auto_increment primary key,caption char(20)) engine=innodb; Query OK, 0 rows affected (0.28 sec) mysql> insert into class values(0,'材料一班'),(0,'材料二班'),(0,'材料三班'); Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> create table student(
sid int not null auto_increment primary key,
sname varchar(10) not null,gender char(3) not null,
class_id int not null,
constraint fk_class_student foreign key (class_id) references class (cid)
); Query OK, 0 rows affected (0.34 sec) mysql> insert into student values (0,'章鱼','男',2),(0,'李杰','女',1),(0,'吴雨','女',2); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table teacher(tid int not null auto_increment primary key,tname char(10)) engine=innodb; Query OK, 0 rows affected (0.53 sec) mysql> create table course(
cid int not null auto_increment primary key,
cname char(10),
tearch_id int not null,
constraint fk_teacher_course foreign key (tearch_id) references teacher (tid)
); Query OK, 0 rows affected (0.27 sec) mysql> show tables; +--------------+ | Tables_in_lx | +--------------+ | class | | course | | student | | teacher | +--------------+ 4 rows in set (0.00 sec) mysql> insert into teacher values (0,'张老师'),(0,'任丘'),(0,'李三'); Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into course values (0,'高数',2),(0,'大化',1),(0,'物理',1); Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table score(
sid int not null auto_increment primary key,
student_id int not null,
course_id int not null,
number int not null,
constraint fk_score_student foreign key (student_id) references student (sid),
constraint fk_score_course foreign key (course_id) references course (cid)
); Query OK, 0 rows affected (0.51 sec) mysql> insert into score values (0,3,2,68),(0,2,2,90),(0,1,3,75); Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from class; +-----+----------+ | cid | caption | +-----+----------+ | 1 | 材料一班 | | 2 | 材料二班 | | 3 | 材料三班 | +-----+----------+ 3 rows in set (0.00 sec) mysql> select * from student; +-----+-------+--------+----------+ | sid | sname | gender | class_id | +-----+-------+--------+----------+ | 1 | 章鱼 | 男 | 2 | | 2 | 李杰 | 女 | 1 | | 3 | 吴雨 | 女 | 2 | +-----+-------+--------+----------+ 3 rows in set (0.00 sec) mysql> select * from teacher; +-----+--------+ | tid | tname | +-----+--------+ | 1 | 张老师 | | 2 | 任丘 | | 3 | 李三 | +-----+--------+ 3 rows in set (0.00 sec) mysql> select * from course; +-----+-------+-----------+ | cid | cname | tearch_id | +-----+-------+-----------+ | 1 | 高数 | 2 | | 2 | 大化 | 1 | | 3 | 物理 | 1 | +-----+-------+-----------+ 3 rows in set (0.00 sec) mysql> select * from score; +-----+------------+-----------+--------+ | sid | student_id | course_id | number | +-----+------------+-----------+--------+ | 1 | 3 | 2 | 68 | | 2 | 2 | 2 | 90 | | 3 | 1 | 3 | 75 | +-----+------------+-----------+--------+ 3 rows in set (0.00 sec)