MySQL
MySQL简介
- MySQL是一种关系数据库管理系统,是一种开源软件
- 由瑞典MySQL AB公司开发,2008年1月16号被Sun公司收购。2009年,SUN又被Oracle收购
- MySQL软件采用双授权政策,分为社区版和商业版。由于体积小、速度快、总体拥有成本低,尤其是开放源码特点,一般中小型网站的开发都选择MySQL作为网站数据库。
- 由于其社区版的性能卓越,搭配PHP和Apache可组成良好的开发环境。
- MySQL能够工作在众多不同的平台上
端口:3306
默认用户:root
字符集:默认字符集latin1,应设置为gbk或utf-8
安装时可以启动远程登录、创建匿名账户
可安装图形化操作界面,方便操作
安装mysql错误解决方案
手动删除mysql安装目录
重新运行配置向导MySQLInstanceConfig.exe
删除C:\ProgramData\MySQL目录
启动MySQL服务
net start mysql
连接MySQL
mysql -h127.0.0.1 -uroot –p
退出MySQL
exit
Quit
关闭MySQL服务
net stop mysql
MySQL列类型
数值类型:
SMALLINT: 2个字节
INT: 4个字节 // age int(10)
INTEGER:INT的同义词
BIGINT : 8个字节
FLOAT : 4个字节
DOUBLE : 8个字节 //score float(10,2)
MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如,INT(4))。显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示
字符串(字符)类型
CHAR:固定长度字符串 sex char(2)
VARCHAR:可变长度字符串 name varchar(20)
VARCHAR使用起来较为灵活,CHAR处理速度更快
TEXT:非二进制大对象(字符)
BLOB:二进制大对象(非字符)
日期/时间类型:
DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: YYYY-MM-DD HH:MM:SS
TIME:HH:MM:SS
YEAR:YYYY
主键自增
不使用序列,通过auto_increment
SQL语句语法
SQL语言包含4个部分:
数据定义语言(如create,drop,alter等语句)
数据查询语言(select语句)
数据操纵语言(insert,delete,update语句)
数据控制语言(如grant,revoke,commit,rollback等语句)
数据操纵语言针对表中的数据,而数据定义语言针对数据库或表
数据定义语言
create database school; /创建数据库/
show databases; /显示所有数据库select database()/
use school; /指定默认数据库/
create table student( /创建表/
id int(10) primary key auto_increment, /主键,自增/
name varchar(8),
sex char(1),
score float(6,2)
);
数据定义语言
show tables; /显示当前库中表清单/
/* 显示指定表结构show columns from student;*/
describe student;
show create table student; /显示建表sql语句/
drop table student; /删除表/
drop database school; /删除数据库/
数据操纵语言
insert into student values(“张三”, “t”,87.5);
insert into student values(null,”张三”,”t”,87.5);
//insert into student (name,sex,score) values(“张三”,”t”,87.5);
select * from student;
insert into student values(null,”李四”,”男”,89);
alter table student modify sex char(2);
insert into student values(null,”李四”,”男”,89);
select * from student;
数据操纵语言
update student set sex =”女”;
select * from student;
update student set sex=”男” where id=1;
select * from student;
update student set name=”王五” ,score=100 where id=2;
select * from student;
delete from student where name=”王五”;
select * from student;
delete from student;
select * from student;
数据查询语言(select语句)
select * from student;
select id,name,score from student;
select * from student where id<5 and sex=“男”;
select count(*) from student ;
select max(score) from student;
select id ,name,score from student order by score desc;
select * from stu where name like “张%”;
更改表结构语句
alter table student add birth date;
insert into student values(null,”赵六”,”男”,100, now() );
insert into student values(null,”赵六”,”男”,100, “1980-12-23”);
alter table student change birth birthday date ;
alter table student modify sex char(4) not null ;
alter table student modify sex char(2) after birthday;
alter table student rename as stu;
alter table stu drop birthday;
多表查询和外键关联
create table person( id int primary key, name varchar(6), sex char(2), age int(10) );
insert into person values(1,”张三”,”男”,45);
insert into person values(2,”李四”,”男”,32);
insert into person values(3,”王五”,”女”,32);
create table pet(
id int auto_increment ,
name varchar(6),
masterid int,
primary key(id),
constraint fk foreign key (masterid) references person(id)
);
多表查询和外键关联
insert into pet (name,masterid) values(“happy”,1);
insert into pet (name,masterid) values(“lucky”,1);
…………
select * from person p1,pet p2 where p1.id=p2.masterid;
select * from person p1,pet p2 where p1.id=p2.masterid and p1.id=1;
alter table pet drop foreign key fk;
alter table pet add constraint fk foreign key (masterid) references person(id) on delete cascade on update cascade;
update person set id=6 where id=2;
delete from person where id=6;
分页语句
select * from table limit (start-1)*limit,limit;
其中start是页码
limit是每页显示的条数。
导入导出
命令行操作
导入
方法1:mysql>source d:\dbname.sql
方法2:mysql -u root -p 数据库名 < dbname.sql
导出
导出整个数据库
mysqldump -u root -p 数据库名 > dbname.sql
只导出一个表
mysqldump -u root -p 数据库名 表名> dbname.sql
图形界面操作
Navicat
导入:运行SQL文件
导出:转储SQL文件
小结
简介
数据库列类型
SQL语句语法
导入导出
SQL语句
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set
mysql> create database school
-> ;
Query OK, 1 row affected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set
mysql> create table (
-> ;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(' at line 1
mysql> create table student(
-> sno int
-> );
1046 - No database selected
mysql> use school;
Database changed
mysql> show tables;
Empty set
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set
mysql> use school;
Database changed
mysql> create table student(
-> sno int(6) primary key auto_increment,
-> sname varchar(10) not null,
-> sex char(2),
-> age int(3),
-> score float(4,1),
-> enterdate date
-> );
Query OK, 0 rows affected
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set
mysql> desc student;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| sno | int(6) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | NULL | |
| sex | char(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| score | float(4,1) | YES | | NULL | |
| enterdate | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set
mysql> show columns from student;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| sno | int(6) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | NULL | |
| sex | char(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| score | float(4,1) | YES | | NULL | |
| enterdate | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set
mysql> show table from student;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from student' at line 1
mysql> show tables from student;
1049 - Unknown database 'student'
mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`sno` int(6) NOT NULL AUTO_INCREMENT,
`sname` varchar(10) NOT NULL,
`sex` char(2) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`score` float(4,1) DEFAULT NULL,
`enterdate` date DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> select * from student;
Empty set
mysql> insert into student values('张三','男',23,78.5,'1999-12-23');
1136 - Column count doesn't match value count at row 1
mysql> insert into student (sname,sex,age,score,enterdate)values('张三','男',23,78.5,'1999-12-23');
Query OK, 1 row affected
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
+-----+-------+-----+-----+-------+------------+
1 row in set
mysql> insert into student values(null,'李四','女',18,98.5,'2003/01/21');
Query OK, 1 row affected
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
+-----+-------+-----+-----+-------+------------+
2 rows in set
mysql> insert into student values (null,'王五','男',20,78,now()),(null,'赵六','女',21,100,sysdate());
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 2
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
| 3 | 王五 | 男 | 20 | 78 | 2018-02-02 |
| 4 | 赵六 | 女 | 21 | 100 | 2018-02-02 |
+-----+-------+-----+-----+-------+------------+
4 rows in set
mysql> update student set score = 87,age=21 where sno = 3;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
| 3 | 王五 | 男 | 21 | 87 | 2018-02-02 |
| 4 | 赵六 | 女 | 21 | 100 | 2018-02-02 |
+-----+-------+-----+-----+-------+------------+
4 rows in set
mysql> delete student where sno = 3;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where sno = 3' at line 1
mysql> delete from student where sno = 3;
Query OK, 1 row affected
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
| 4 | 赵六 | 女 | 21 | 100 | 2018-02-02 |
+-----+-------+-----+-----+-------+------------+
3 rows in set
mysql> insert into student values(null,'王五','男',21,98,now());
Query OK, 1 row affected
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
| 4 | 赵六 | 女 | 21 | 100 | 2018-02-02 |
| 5 | 王五 | 男 | 21 | 98 | 2018-02-02 |
+-----+-------+-----+-----+-------+------------+
4 rows in set
mysql> insert into student values(10,'王五2','男',21,98,now());
Query OK, 1 row affected
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
| 4 | 赵六 | 女 | 21 | 100 | 2018-02-02 |
| 5 | 王五 | 男 | 21 | 98 | 2018-02-02 |
| 10 | 王五2 | 男 | 21 | 98 | 2018-02-02 |
+-----+-------+-----+-----+-------+------------+
5 rows in set
mysql> insert into student values(10,'王五3','男',21,98,now());
1062 - Duplicate entry '10' for key 'PRIMARY'
mysql> insert into student values(null,'王五3','男',21,98,now());
Query OK, 1 row affected
mysql> select * from student;
+-----+-------+-----+-----+-------+------------+
| sno | sname | sex | age | score | enterdate |
+-----+-------+-----+-----+-------+------------+
| 1 | 张三 | 男 | 23 | 78.5 | 1999-12-23 |
| 2 | 李四 | 女 | 18 | 98.5 | 2003-01-21 |
| 4 | 赵六 | 女 | 21 | 100 | 2018-02-02 |
| 5 | 王五 | 男 | 21 | 98 | 2018-02-02 |
| 10 | 王五2 | 男 | 21 | 98 | 2018-02-02 |
| 11 | 王五3 | 男 | 21 | 98 | 2018-02-02 |
+-----+-------+-----+-----+-------+------------+
6 rows in set
mysql>
SQL语句2:
mysql> create table master(
-> mid int(4) primary key auto_increment,
-> name varchar(10),
-> money int(4)
-> );
1046 - No database selected
mysql> use school;
Database changed
mysql> create table master(
-> mid int(4) primary key auto_increment,
-> name varchar(10) not null,
-> money int(4)
-> );
Query OK, 0 rows affected
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| master |
| student |
+------------------+
2 rows in set
mysql> insert into master values (null,'zhangsan',1000),(null,'lisi',3000),(null,'wangwu',2000);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from master;
+-----+----------+-------+
| mid | name | money |
+-----+----------+-------+
| 1 | zhangsan | 1000 |
| 2 | lisi | 3000 |
| 3 | wangwu | 2000 |
+-----+----------+-------+
3 rows in set
mysql> create table pet(
-> pid int(4) primary key auto_increment,
-> nickname varchar(10),
-> color varchar(5),
-> masterid int(4)
-> );
Query OK, 0 rows affected
mysql> alter table pet add constraints fk_pet_masterid foreign key (masterid) references master(mid);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fk_pet_masterid foreign key (masterid) references master(mid)' at line 1
mysql> alter table pet add constraint fk_pet_masterid foreign key (masterid) references master(mid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table pet drop constraint fk_pet_masterid;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint fk_pet_masterid' at line 1
mysql> alter table pet drop foreign key fk_pet_masterid;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table pet add constraint fk_pet_masterid foreign key (masterid) references master(mid) on delete set null on update cascade ;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into pet values (null,'lucky','red',1 ),(null,'happy','black',2),(null,'monkey','white',1),(null,'donkey','gray',3);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from pet;
+-----+----------+-------+----------+
| pid | nickname | color | masterid |
+-----+----------+-------+----------+
| 1 | lucky | red | 1 |
| 2 | happy | black | 2 |
| 3 | monkey | white | 1 |
| 4 | donkey | gray | 3 |
+-----+----------+-------+----------+
4 rows in set
mysql> select * from master;
+-----+----------+-------+
| mid | name | money |
+-----+----------+-------+
| 1 | zhangsan | 1000 |
| 2 | lisi | 3000 |
| 3 | wangwu | 2000 |
+-----+----------+-------+
3 rows in set
mysql> delete master where mid = 1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where mid = 1' at line 1
mysql> delete from
master where mid = 1;
Query OK, 1 row affected
mysql> select * from master;
+-----+--------+-------+
| mid | name | money |
+-----+--------+-------+
| 2 | lisi | 3000 |
| 3 | wangwu | 2000 |
+-----+--------+-------+
2 rows in set
mysql> select * from pet;
+-----+----------+-------+----------+
| pid | nickname | color | masterid |
+-----+----------+-------+----------+
| 2 | happy | black | 2 |
| 4 | donkey | gray | 3 |
+-----+----------+-------+----------+
2 rows in set
mysql> update master set mid = 10 where mid = 2;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from master;
+-----+--------+-------+
| mid | name | money |
+-----+--------+-------+
| 3 | wangwu | 2000 |
| 10 | lisi | 3000 |
+-----+--------+-------+
2 rows in set
mysql> select * from pet;
+-----+----------+-------+----------+
| pid | nickname | color | masterid |
+-----+----------+-------+----------+
| 2 | happy | black | 10 |
| 4 | donkey | gray | 3 |
+-----+----------+-------+----------+
2 rows in set
mysql> insert into master values (null,'zhaoliu',1000);
Query OK, 1 row affected
mysql> select * from master;
+-----+---------+-------+
| mid | name | money |
+-----+---------+-------+
| 3 | wangwu | 2000 |
| 4 | zhaoliu | 1000 |
| 10 | lisi | 3000 |
+-----+---------+-------+
3 rows in set
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set
mysql> select * from help_category;
+------------------+-----------------------------------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+-----------------------------------------------+--------------------+-----+
| 1 | Geographic | 0 | |
| 2 | Polygon properties | 35 | |
| 3 | Numeric Functions | 39 | |
| 4 | WKT | 35 | |
| 5 | Plugins | 36 | |
| 6 | Control flow functions | 39 | |
| 7 | MBR | 35 | |
| 8 | Transactions | 36 | |
| 9 | Help Metadata | 36 | |
| 10 | Account Management | 36 | |
| 11 | Point properties | 35 | |
| 12 | Encryption Functions | 39 | |
| 13 | LineString properties | 35 | |
| 14 | Miscellaneous Functions | 39 | |
| 15 | Logical operators | 39 | |
| 16 | Functions and Modifiers for Use with GROUP BY | 36 | |
| 17 | Information Functions | 39 | |
| 18 | Storage Engines | 36 | |
| 19 | Bit Functions | 39 | |
| 20 | Comparison operators | 39 | |
| 21 | Table Maintenance | 36 | |
| 22 | User-Defined Functions | 36 | |
| 23 | Data Types | 36 | |
| 24 | Compound Statements | 36 | |
| 25 | Geometry constructors | 35 | |
| 26 | GeometryCollection properties | 1 | |
| 27 | Administration | 36 | |
| 28 | Data Manipulation | 36 | |
| 29 | Utility | 36 | |
| 30 | Language Structure | 36 | |
| 31 | Geometry relations | 35 | |
| 32 | Date and Time Functions | 39 | |
| 33 | WKB | 35 | |
| 34 | Procedures | 36 | |
| 35 | Geographic Features | 36 | |
| 36 | Contents | 0 | |
| 37 | Geometry properties | 35 | |
| 38 | String Functions | 39 | |
| 39 | Functions | 36 | |
| 40 | Data Definition | 36 | |
+------------------+-----------------------------------------------+--------------------+-----+
40 rows in set
mysql> select * from help_category limit 11,5;
+------------------+-----------------------------------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+-----------------------------------------------+--------------------+-----+
| 12 | Encryption Functions | 39 | |
| 13 | LineString properties | 35 | |
| 14 | Miscellaneous Functions | 39 | |
| 15 | Logical operators | 39 | |
| 16 | Functions and Modifiers for Use with GROUP BY | 36 | |
+------------------+-----------------------------------------------+--------------------+-----+
5 rows in set
mysql> select * from help_category limit 0,5;
+------------------+--------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+--------------------+--------------------+-----+
| 1 | Geographic | 0 | |
| 2 | Polygon properties | 35 | |
| 3 | Numeric Functions | 39 | |
| 4 | WKT | 35 | |
| 5 | Plugins | 36 | |
+------------------+--------------------+--------------------+-----+
5 rows in set
mysql> select * from help_category limit 10,5;
+------------------+-------------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+-------------------------+--------------------+-----+
| 11 | Point properties | 35 | |
| 12 | Encryption Functions | 39 | |
| 13 | LineString properties | 35 | |
| 14 | Miscellaneous Functions | 39 | |
| 15 | Logical operators | 39 | |
+------------------+-------------------------+--------------------+-----+
5 rows in set
mysql> select * from master;
+-----+---------+-------+
| mid | name | money |
+-----+---------+-------+
| 3 | wangwu | 2000 |
| 4 | zhaoliu | 1000 |
| 10 | lisi | 3000 |
+-----+---------+-------+
3 rows in set
mysql> select * from pet;
+-----+----------+-------+----------+
| pid | nickname | color | masterid |
+-----+----------+-------+----------+
| 2 | happy | black | 10 |
| 4 | donkey | gray | 3 |
+-----+----------+-------+----------+
2 rows in set
mysql> select * from master m join pet p on m.mid = p.masterid;
+-----+--------+-------+-----+----------+-------+----------+
| mid | name | money | pid | nickname | color | masterid |
+-----+--------+-------+-----+----------+-------+----------+
| 3 | wangwu | 2000 | 4 | donkey | gray | 3 |
| 10 | lisi | 3000 | 2 | happy | black | 10 |
+-----+--------+-------+-----+----------+-------+----------+
2 rows in set
mysql> select * from master m left join pet p on m.mid = p.masterid;
+-----+---------+-------+------+----------+-------+----------+
| mid | name | money | pid | nickname | color | masterid |
+-----+---------+-------+------+----------+-------+----------+
| 3 | wangwu | 2000 | 4 | donkey | gray | 3 |
| 4 | zhaoliu | 1000 | NULL | NULL | NULL | NULL |
| 10 | lisi | 3000 | 2 | happy | black | 10 |
+-----+---------+-------+------+----------+-------+----------+
3 rows in set
导入导出:
命令行操作
导入
方法1:mysql>source d:\dbname.sql
方法2:mysql -u root -p 数据库名 < dbname.sql
导出
导出整个数据库
mysqldump -u root -p 数据库名 > dbname.sql
只导出一个表
mysqldump -u root -p 数据库名 表名> dbname.sql
图形界面操作
Navicat
导入:运行SQL文件
导出:转储SQL文件