mysql数据库的安装到使用

安装mysql:

sudo apt-get install mysql-serversudo apt-get install libmysqlcient-dev

进入mysql:mysql -u root -p密码

数据放在数据表中，数据库，是一张一张的表
DBMS:数据库管理系统
数据的所有存储，检索，管理和处理实际上都是由DBMS完成的
mysql是一种DBMS，即一款数据库管理软件
当下流行的DBMS:

orecle:甲骨文公司
应用广泛，功能强大，分布式数据管理系统。收费
mysql:瑞典的mysqlAB公司

1.对mysql的操作

mysql - u root -p

2.显示所有的数据库

show databases;

3.选定要操作的数据库

use dbname;

4.显示所有的数据表

show tables;

5.查看当前使用的数据库

select database();

6.\c技术当前输入的语句

7.退出mysql:\q

2.新建数据库

create database 新建数据库名删除数据库 drop database xxx,students;

判断数据库是否存在，存在则删除
如果存在则删除，如果不存在则不删除。

drop database if exists students;

3.对表的操作
进入数据库，新建数据表:

    create table  表名  (列1 什么类型  [列的完整性约束])defaule charset=utf8;

显示表的创建结构

desc studentdesc 数据表的名字;

删除数据表的名字:
drop table 数据表名

drop table 表名;(默认当前数据库);

删除其它数据表:drop table students.student;

查看其它数据库中的表:
show mysql.tables;

插入:向数据表中插入数据记录(行数据)
insert [into] 表名 ([列名]) values(值列表)
//如果表名后面没有写列的名字，则默认是向所有的列添加值，添加值的时候，另外，字符或者字符串的值
要用”或着”“括起来

查看整张表的所有信息:

    select * from students;

向表中固定行插入数据

mysql> insert into students(id,name) values(111,'ccc');

向表中反向插入数据

mysql> insert into students(name,id) values('ccc','111');


一次插入多行数据

    mysql> insert into students(name,id) values('ccc','111')();

另外一张表的数据插到现在表
insert [into] 表名([列名]) select <列名> from * 原表名 where name=”磊哥”;

1.创建学生这个表

create table students1(id int,name varchar(20),sex varchar(10),age int)default charset=utf8;

2.向student1中插入数据

mysql> insert into student1 values(111,"东东",'f','26'),(222,"磊哥","m",'24'),(666,"东东","f",'23');

3.查看student1中的数据

mysql> select * from students1;

4.将表student1中的数据，筛选出来，年龄大小等于23的一行，插入到表student

mysql> insert into students select id,name,sex from students1   where  age=23;

数据的修改:

update 表名 set 列名 = 更新值 ，列名2=                      [where]

//修改整列

mysql> update students1 set sex='女';

修改单列:

mysql> update students1 set sex='女' where age=23 ;

更新多列

update students1 set sex='男',name='关磊' where id=222;

删除关键字，

delete from 表名 [where <删除文件>]

注:此语句为删除表中的行，如果不带where,会删除整张表。

“`
mysql> delete from students1 where age=26;

查询

mysql> select * from  students1;mysql> select id from  students1  ;mysql> select id from  students1 where id<3 ;mysql> select id from  students1 where id=111 ;mysql> select * from students order by id ;mysql> select * from students order by id  desc;

//asc升序，

更改表的结构(列)     为表添加一列;如果没有指定位置，在列尾添加

alter table 表名 add 列的名字，列的类型，[列的完整性约束] [after]

mysql> alter table students1 add ids int;mysql> alter table students1 add xxx int after ids ;

删除关键字:

alter table
输出xxx这一列
mysql> alter table students1 drop xxx;


设置默认值

alter table 表名 alter 列名 set de

更改列名和类型
alter table 表名 change 旧列名 新列名 列类型 [列的完整性约束] [alter 列名]

   alter table students1 change id num int not null after name;

注意:
当旧列名与新列名相同，可以改变该列的类型和约束条件，不同，则在改变旧列名的同时，
同时改变它的类型。

为表添加一个主键:把某一列设置为主键列，主键列中的每个成员相互不同，它存在的唯一的标志一行，
一张表中最多有一个主键，通常把顺序列作为主键。

选为主键的列必须是非空的。(not null)

且不含有相同的值

alter table student add primary key(num)

在表中添加一个值，第二次插入时，重复，不允许添加值进去。

 insert into students1 values("gggg",7,'c',default,default);Query OK, 1 row affected (0.05 sec) insert into students1 values("gggg",7,'c',default,default);ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'

删除主键:
“`
alter table students1 drop primary key;

设为主键的原则:非空主键    ```    alter table students change num num int auto increment     ```注意:整形,非空,主键列,此处的int 不能省略主键中不能含有0，因为子增长是从0开始的。// 子增长的条件:主键+整形+不含零

1.添加主键

mysql> alter table students1 add primary key(num);2alter table students change num num int auto_increment3.mysql> insert into students1(name,sex) values("gggg",'m');Query OK, 1 row affected (0.04 sec)//alter table students auto_incremnt=3;//自增的顺序把主键设为子增长;删除子增长    alter table students change num num int 删除子增长中的主键，必须先删除自增长。1.    alter table student2 change num num int;2.        alter table student2 drop primary key;        Query OK, 6 rows affected (0.62 sec)        Records: 6  Duplicates: 0  Warnings: 0

给数据库重命名mysql> alter table students1 rename student2;//创建一个表mysql> create table student3(          code int not null auto_increment,         name varchar(20) not null,          age int,          sex varchar(10),          score int,          grade int,          address varchar(20) default '未知',           major varchar(20),          primary key(code))         default charset=utf8;Query OK, 0 rows affected (0.27 sec)mysql> desc student3;+---------+-------------+------+-----+---------+----------------+| Field   | Type        | Null | Key | Default | Extra          |+---------+-------------+------+-----+---------+----------------+| code    | int(11)     | NO   | PRI | NULL    | auto_increment || name    | varchar(20) | NO   |     | NULL    |                || age     | int(11)     | YES  |     | NULL    |                || sex     | varchar(10) | YES  |     | NULL    |                || score   | int(11)     | YES  |     | NULL    |                || grade   | int(11)     | YES  |     | NULL    |                || address | varchar(20) | YES  |     | 未知    |                || major   | varchar(20) | YES  |     | NULL    |                |+---------+-------------+------+-----+---------+----------------+8 rows in set (0.00 sec)

//向表中插入数据mysql> insert into student3(name,age,sex,score,grade,address,major)    -> values('xxx',13,'m',78,1,'asdfg','sss'),    -> ('xxc',15,'w',88,2,'afdhd','nnn'),    -> ('cxx',9,'m',99,3,'sdsvfbf','vvv'),    -> ('ccc',45,'m',56,1,'cdfsff','sss'),    -> ('xbb',34,'w',66,1,'cdfsff','sss');Query OK, 5 rows affected (0.29 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from student3;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   |+------+------+------+------+-------+-------+---------+-------+5 rows in set (0.00 sec)

mysql> select code,name from student3;+------+------+                             | code | name |+------+------+|    1 | xxx  ||    2 | xxc  ||    3 | cxx  ||    4 | ccc  ||    5 | xbb  |+------+------+5 rows in set (0.00 sec)

学段计算mysql> select code,age+score from student3;+------+-----------+| code | age+score |+------+-----------+|    1 |        91 ||    2 |       103 ||    3 |       108 ||    4 |       101 ||    5 |       100 |+------+-----------+5 rows in set (0.03 sec)

mysql> select code,age+score  as xxx from student3;+------+------+| code | xxx  |+------+------+|    1 |   91 ||    2 |  103 ||    3 |  108 ||    4 |  101 ||    5 |  100 |+------+------+5 rows in set (0.00 sec)

mysql> select code as '学号',name as '姓名' from student3;+--------+--------+| 学号   | 姓名   |+--------+--------+|      1 | xxx    ||      2 | xxc    ||      3 | cxx    ||      4 | ccc    ||      5 | xbb    |+--------+--------+5 rows in set (0.00 sec)

//将数据库重新命名为s，并且将其中的值进行选择性输出。mysql> select s.code,s.name from student3 as s;+------+------+| code | name |+------+------+|    1 | xxx  ||    2 | xxc  ||    3 | cxx  ||    4 | ccc  ||    5 | xbb  |+------+------+5 rows in set (0.00 sec)

输出一个某个信息的所有可能。//去掉重复的:查询年纪的所有情况mysql> select  distinct grade  from student3;+-------+| grade |+-------+|     1 ||     2 ||     3 |+-------+3 rows in set (0.03 sec)

//查询年龄在20岁，以下的mysql> select name from student3  where age<=20;+------+| name |+------+| xxx  || xxc  || cxx  |+------+3 rows in set (0.00 sec)

mysql> select * from student3 order by age  desc;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   |+------+------+------+------+-------+-------+---------+-------+5 rows in set (0.00 sec)

年龄最大的前三个;select * from student3 order by age desc limit 3;年龄最大mysql> select * from student3 order by age desc limit 3;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   |+------+------+------+------+-------+-------+---------+-------+3 rows in set (0.00 sec)mysql> select * from student3 order by age limit 3;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   |+------+------+------+------+-------+-------+---------+-------+3 rows in set (0.01 sec)

年龄最大的第四个和第五个;//limit的用法，limit n当我们要取前几各个元素//limit的用法，limit m,n 从m开始偏移n个select * from student3 order by age  desc limit 3,2;mysql> select * from student3 order by age limit 3,2;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   |+------+------+------+------+-------+-------+---------+-------+2 rows in set (0.00 sec)contact:mysql>  select concat(name,'is','age','year old')from student3;+------------------------------------+| concat(name,'is','age','year old') |+------------------------------------+| xxxisageyear old                   || xxcisageyear old                   || cxxisageyear old                   || cccisageyear old                   || xbbisageyear old                   |+------------------------------------+5 rows in set (0.00 sec)mysql> select count(*) from student3;+----------+| count(*) |+----------+|        5 |+----------+1 row in set (0.03 sec)

//计算一班的平均成绩mysql> select avg(score) from student3 where grade=1;+------------+| avg(score) |+------------+|    66.6667 |+------------+1 row in set (0.03 sec)查询某一个专业的最高分和最低分mysql> select max(score) as '最高分',min(score) as '最低分' from student3 where major='sss';+-----------+-----------+| 最高分    | 最低分    |+-----------+-----------+|        78 |        56 |+-----------+-----------+1 row in set (0.02 sec)

查询每个专业的平均分mysql> select major,avg(score) as '平均成绩' from student3 group by major;+-------+--------------+| major | 平均成绩     |+-------+--------------+| nnn   |      88.0000 || sss   |      66.6667 || vvv   |      99.0000 |+-------+--------------+3 rows in set (0.00 sec)

//查询每个年纪的平均成绩mysql> select grade,avg(score) as '平均成绩' from student3 group by grade;+-------+--------------+| grade |   平均成绩   |+-------+--------------+|     1 |      66.6667 ||     2 |      88.0000 ||     3 |      99.0000 |+-------+--------------+3 rows in set (0.00 sec)

查询专业的平局成绩在70以上的专业;mysql> select major,avg(score) as '平均成绩' from student3 group by major having avg(score)>70;+-------+--------------+| major | 平均成绩     |+-------+--------------+| nnn   |      88.0000 || vvv   |      99.0000 |+-------+--------------+2 rows in set (0.00 sec)

求总分mysql> select sum(score) from student3;+------------+| sum(score) |+------------+|        387 |+------------+1 row in set (0.03 sec)20-----50mysql> select * from student3 where age between 20 and 50;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   |+------+------+------+------+-------+-------+---------+-------+2 rows in set (0.00 sec)

mysql> select * from student3 where age not between 20 and 50;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   |+------+------+------+------+-------+-------+---------+-------+3 rows in set (0.00 sec)

查询'nnn','vvv'专业学生的所有的信息mysql> select * from student3 where major='nnn' || major='vvv';+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   |+------+------+------+------+-------+-------+---------+-------+2 rows in set (0.00 sec)mysql> select * from student3 where major='nnn' or major='vvv';+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   |+------+------+------+------+-------+-------+---------+-------+2 rows in set (0.00 sec)mysql> select * from student3 where major in('nnn','vvv');+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   |+------+------+------+------+-------+-------+---------+-------+2 rows in set (0.00 sec)

查询学号为一的学生的所有信息mysql> select * from student3 where code=1;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   |+------+------+------+------+-------+-------+---------+-------+1 row in set (0.00 sec)mysql> select * from student3 where code like 1;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | mx    |    78 |     1 | asdfg   | sss   |+------+------+------+------+-------+-------+---------+-------+1 row in set (0.04 sec)

 查询名字义x开头的所有学生信息。mysql> select * from student3 where name like "x%";+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   |+------+------+------+------+-------+-------+---------+-------+3 rows in set (0.00 sec)mysql> select * from student3 where name like "%x";+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   |+------+------+------+------+-------+-------+---------+-------+2 rows in set (0.00 sec)

mysql> select * from student3 where name like '%x%';+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   |+------+------+------+------+-------+-------+---------+-------+4 rows in set (0.00 sec)mysql> show tables like 'stu%';+---------------------------+| Tables_in_students (stu%) |+---------------------------+| student2                  || student3                  || students                  |+---------------------------+3 rows in set (0.00 sec)性别m.并且分数小于70分得1的学生姓名。mysql> select name from student3 where sex='m' && score<70;+------+| name |+------+| ccc  |+------+1 row in set (0.00 sec)

性别 m   学号,姓名,年龄   查询结果按年龄的降序排序mysql> select name,age,code from student3 where sex='m' order by age desc;+------+------+------+| name | age  | code |+------+------+------+| ccc  |   45 |    4 || xxx  |   13 |    1 || cxx  |    9 |    3 |+------+------+------+3 rows in set (0.00 sec)

查询全体学生的信息，查询结果按年龄的升序排列，同一年纪的学生按学生分数的降序排序，同一分数按年龄的升序排列。mysql> select * from student3 order by grade,score desc,age asc;+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    6 | ppp  |   78 | m    |    78 |     1 | fgh     | vvv   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    7 | lll  |   56 | w    |    78 |     3 | ddd     | aaa   |+------+------+------+------+-------+-------+---------+-------+7 rows in set (0.00 sec)

内连接mysql> select s.id,s.name,s.age,c.course,c.score from stu as s inner join cour as c on s.id= c.num;+------+-----------+------+--------+-------+| id   | name      | age  | course | score |+------+-----------+------+--------+-------+|    1 | 张无忌    |   26 | 语文   |    78 ||    1 | 张无忌    |   26 | 数学   |    88 ||    2 | 乔峰      |   33 | 语文   |    90 ||    3 | 独孤      |   18 | 数学   |    99 ||    3 | 独孤      |   18 | 英语   |    88 |+------+-----------+------+--------+-------+5 rows in set (0.00 sec)

//左外连接:以左表为主mysql> select s.id,s.name,s.age,c.course,c.score from stu as s left join cour as c on s.id= c.num;+------+-----------+------+--------+-------+| id   | name      | age  | course | score |+------+-----------+------+--------+-------+|    1 | 张无忌    |   26 | 语文   |    78 ||    1 | 张无忌    |   26 | 数学   |    88 ||    2 | 乔峰      |   33 | 语文   |    90 ||    3 | 独孤      |   18 | 数学   |    99 ||    3 | 独孤      |   18 | 英语   |    88 ||    5 | 风清扬    |   22 | NULL   |  NULL |+------+-----------+------+--------+-------+6 rows in set (0.01 sec)

右外连接:以右表为主mysql> select s.id,s.name,s.age,c.course,c.score from stu as s right join cour as c on s.id= c.num;+------+-----------+------+--------+-------+| id   | name      | age  | course | score |+------+-----------+------+--------+-------+|    1 | 张无忌    |   26 | 语文   |    78 ||    1 | 张无忌    |   26 | 数学   |    88 ||    2 | 乔峰      |   33 | 语文   |    90 ||    3 | 独孤      |   18 | 数学   |    99 ||    3 | 独孤      |   18 | 英语   |    88 || NULL | NULL      | NULL | 美术   |    90 |+------+-----------+------+--------+-------+6 rows in set (0.00 sec)

子查询:将一个查询块嵌套在另一查询快的子查询注: select name,age, from stu where id=1 or id=2;注: select name,age, from stu where id(select );mysql> select  name,age from stu where id in(select num from cour where course='语文');+-----------+------+| name      | age  |+-----------+------+| 张无忌    |   26 || 乔峰      |   33 |+-----------+------+2 rows in set (0.03 sec)

// 复制一个表mysql> create table xxx select * from stu ;Query OK, 4 rows affected (0.61 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> show tables;+--------------------+| Tables_in_students |+--------------------+| cour               || stu                || student2           || student3           || students           || xxx                |+--------------------+6 rows in set (0.00 sec)mysql> select * from xxx;+------+-----------+------+------+| id   | name      | age  | sex  |+------+-----------+------+------+|    1 | 张无忌    |   26 | 男   ||    2 | 乔峰      |   33 | 男   ||    3 | 独孤      |   18 | 男   ||    5 | 风清扬    |   22 | 男   |+------+-----------+------+------+4 rows in set (0.00 sec)//选择性复制内容mysql> create table vvv select id,name from stu;Query OK, 4 rows affected (0.39 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from vvv;+------+-----------+| id   | name      |+------+-----------+|    1 | 张无忌    ||    2 | 乔峰      ||    3 | 独孤      ||    5 | 风清扬    |+------+-----------+4 rows in set (0.00 sec) ``` ```//创建一个已经存在的表的框架，//仅仅拷贝一个表的框架;删除创建的表mysql> drop table xxx;Query OK, 0 rows affected (0.19 sec)拷贝一个空的表mysql> create table xxx select * from stu where 0=1;Query OK, 0 rows affected (0.30 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from xxx;Empty set (0.00 sec)

//选择性的赋值表的一部分mysql> drop table xxx;Query OK, 0 rows affected (0.17 sec)mysql> create table xxx select id,name from stu where 0=1;Query OK, 0 rows affected (0.32 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from xxx;Empty set (0.00 sec)mysql> desc xxx;p+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

导出整个数据库1*******************************************mysqldump -u  root -p students >students.sql2*******************************************mysql> drop  database students;Query OK, 7 rows affected (1.32 sec)mysql> create database students;Query OK, 1 row affected (0.00 sec)mysql> use students;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed3*****************************************mysql -u root -p  students <students.sql

//可以从数据库中mysql> create database uuu;Query OK, 1 row affected (0.00 sec)mysql> use uuu;Database changedmysql> source students.sql;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show tables;+---------------+| Tables_in_uuu |+---------------+| cour          || stu           || student2      || student3      || students      || vvv           || xxx           |+---------------+7 rows in set (0.00 sec)

drop 删除表

导出数据库中的一个表:

 mysqldump -u root -p  students stu >students1.sql mysql -u root -p  students <students1.sql数据库里面用source students1.sql;

//将>左边执行文件的执行结果输出到右边的.txt文件中
./a.out >ps2.txt

 select concat(name,'is','age','year old')from student3;    order by age desc;