数据库基本操作语句

数据库命令大全

数据库表基础语句

数据库连接与断开

连接：mysql -h主机地址 -u⽤户名 －p⽤户密码 （注:u与root可以不⽤加空格，其它也⼀样）

断开：exit （回车）

数据库的基本操作语句

创建授权：grant select on 数据库.* to ⽤户名@登录主机 identified by "密码"

修改密码：mysqladmin -u⽤户名 -p旧密码 password 新密码

删除授权: revoke select,insert,update,delete om . fromtest2@localhost;

显⽰数据库：show databases;

显⽰数据表：show tables;

创建库：create database 库名;

删除库：drop database 库名;

使⽤库：use 库名;

创建库是否存在，不存在则创建：create database if not exists db1;

查看所有数据库：show databases;

查看某个数据库的定义信息：show create database db1;

修改数据库字符信息：alter database db1 character set utf8;

删除数据库：drop database db1;

表的基本操作语句

创建表：create table 表名 (字段设定列表);

显⽰表结构：describe 表名;

删除表：drop table 表名;

修改表：alter table t1 rename t2

查询表：select * from 表名;

清空表：delete from 表名;

备份表: mysqlbinmysqldump -h(ip) -uroot -p(password) databasenametablename > tablename.sql

恢复表: mysqlbinmysql -h(ip) -uroot -p(password) databasenametablename < tablename.sql（操作前先把原来表删除）

创建表：

create table student(
 
id int,
 
name varchar(32),
 
age int ,
 
score double(4,1),
 
birthday date,
 
insert_time timestamp
 
);

查看表结构：desc 表名;

查看创建表的SQL语句：show create table 表名;

修改表名：alter table 表名 rename to 新的表名;

删除表：drop table 表名;

drop table if exists 表名 ;

DML(Data Manipulation Language)数据操作语言

增加： insert into

写全所有列名：insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

不写列名（所有列全部添加）:insert into 表名 values(值1,值2,...值n);

插入部分数据：insert into 表名(列名1,列名2) values(值1,值2);

删除: delete

删除表中数据：delete from 表名 where 列名 = 值;

删除表中所有数据：delete from 表名;

删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）：truncate table 表名;

修改： update

不带条件的修改(会修改所有行)：update 表名 set 列名 = 值;

带条件的修改：update 表名 set 列名 = 值 where 列名=值;

DQL(Data Query Language)数据查询语言;

表列基本操作语句

增加列：ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADDINDEX (c);

修改列：ALTER TABLE t2 CHANGE b c CHAR(20);

删除列：ALTER TABLE t2 DROP COLUMN c;

添加一列：alter table 表名 add 列名 数据类型;

修改列： alert table 表名 change 旧列名 新列名 数据类型;

删除列：alter table 表名 drop 列名;

数据库基础语句

备份数据库：mysql\bin\mysqldump -h(ip) -uroot -p(password) databasename> database.sql

恢复数据库：mysql\bin\mysql -h(ip) -uroot -p(password) databasename< database.sql

复制数据库：mysql\bin\mysqldump --all-databases >all-databases.sql

修复数据库：mysqlcheck -A -o -uroot -p54safer

⽂本数据导⼊： load data local infile "⽂件名" into table 表名;

数据导⼊导出：mysql\bin\mysqlimport database tables.txt

第⼀招：mysql服务的启动和停⽌

net start mysql

net stop mysql

第⼆招：登陆mysql

语法如下：

mysql -u⽤户名-p⽤户密码

键⼊命令mysql -uroot -p，回车后提⽰你输⼊密码，输⼊12345，然后回车即可进⼊到mysql中了，

mysql的提⽰符是：“ mysql> ”

注意：如果是连接到另外的机器上，则需要加⼊⼀个参数-h机器IP

第三招：增加新⽤户

格式：grant 权限 on 数据库.* to ⽤户名@登录主机 identified by "密码"

如：增加⼀个⽤户user1密码为password1，让其可以在本机上登录，并对所有数据库有查询、插⼊、修改、删除的权限。⾸先⽤以root ⽤户连⼊mysql，然后键⼊以下命令：

grant select,insert,update,delete on . touser1@localhost Identified by "password1";

如果希望该⽤户能够在任何机器上登陆mysql，则将localhost改为"%"。

如果你不想user1有密码，可以再打⼀个命令将密码去掉。

grant select,insert,update,delete on mydb.* touser1@localhost identified by"";

第四招： 操作数据库详解

登录到mysql中，然后在mysql的提⽰符下运⾏下列命令，每个命令以分号结束。

1、 显⽰数据库列表。

show databases;

缺省有两个数据库：mysql和test，mysql库存放着mysql的系统和⽤户权限信息，我们改密码和新增⽤户，实际上就是对这个库进⾏操 作。

2、 显⽰库中的数据表：

use mysql; show tables;

3、 显⽰数据表的结构：

describe 表名;

**4、 建库与删库： **

create database 库名;

drop database 库名;

**5、 建表： **

use 库名;

create table 表名(字段列表);

drop table 表名;

6、 清空表中记录：

delete from 表名;

**7、 显⽰表中的记录： **

select * from 表名;

第五招：导出和导⼊数据

1、导出数据：

mysqldump --opt test > mysql.test 即将数据库test数据库导出到mysql.test⽂件，后者是⼀个⽂本⽂件

如：mysqldump -u root -p123456 --databases dbname >mysql.dbname 就是把数据库dbname导出到⽂件mysql.dbname中。

2、 导⼊数据:

mysqlimport -u root -p123456 < mysql.dbname。 不⽤解释了吧。

3、将⽂本数据导⼊数据库:

⽂本数据的字段数据之间⽤tab键隔开：

use test; load data local infile "⽂件名" into table 表名;

1：使⽤SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

2: 创建⼀个数据库：

MYSQLDATA mysql> CREATE DATABASE MYSQLDATA;

3:选择你所创建的数据库：

mysql> USE MYSQLDATA; (按回车键出现Database changed时说明操作成功！)

4:查看现在的数据库中存在什么表：

mysql> SHOW TABLES;

5:创建⼀个数据库表：

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sexCHAR(1));

6:显⽰表的结构：

mysql> DESCRIBE MYTABLE;

7:往表中加⼊记录 ：

mysql> insert into MYTABLE values ("hyq","M");

8:⽤⽂本⽅式将数据装⼊数据库表中（例如D:/mysql.txt）：

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTOTABLE MYTABLE;

9:导⼊.sql⽂件命令（例如D:/mysql.sql）：

mysql>use database; mysql>source d:/mysql.sql;

10:删除表 ：

mysql>drop TABLE MYTABLE;

11:清空表：

mysql>delete from MYTABLE;

12:更新表中数据：

mysql>update MYTABLE set sex="f" where name='hyq';

13：备份数据库：

mysqldump -u root库名>xxx.data

14： 例2：连接到远程主机上的MYSQL

假设远程主机的IP为：110.110.110.110，⽤户名为root,密码为abcd123。

则键⼊以下命令：

mysql-h110.110.110.110 -uroot -pabcd123 ；注:u与root可以不⽤加空格，其它也⼀样）

4、退出MYSQL命令：

exit

(⼀) 连接MYSQL：

格式： mysql -h主机地址 -u⽤户名－p⽤户密码

1：例1：连接到本机上的MYSQL ⾸先在打开DOS窗⼝，然后进⼊mysql安装⽬录下的bin⽬录下

例如： D:\mysql\bin，再键⼊命令mysql -uroot-p，回车后提⽰你输 密码，如果刚安装好MYSQL，超级⽤户root是没有密码的，故直接回车即可进⼊到MYSQL中了，

MYSQL的提⽰符是：

mysql>

2：例2：连接到远程主机上的MYSQL 假设远程主机的IP为：10.0.0.1，⽤户名为root,密码为123。

则键⼊以下命令：

mysql -h10.0.0.1 -uroot-p123（注：u与root可以不⽤加空格，其它也⼀样）

3：退出MYSQL命令 exit （回车）

(⼆) 修改密码：

格式：mysqladmin -u⽤户名 -p旧密码password 新密码

1、例1：给root加个密码123。

⾸先在DOS下进⼊⽬录C:\mysql\bin，然后键⼊以下命令： mysqladmin -uroot -password123 注：因为开始时root没有密码，所以-p旧密码⼀项就可以省略了。

2、例2：再将root的密码改为456 mysqladmin -uroot -pab12password 456

(三) 增加新⽤户：

（注意：和上⾯不同，下⾯的因为是MYSQL环境中的命令，所以后⾯都带⼀个分号作为命令结束符）

格式：grant select on 数据库.* to⽤户名@登录主机 identified by "密码"

例1、增加⼀个⽤户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插⼊、修改、删除的权限。

⾸先⽤以root⽤ 户连⼊MYSQL，

然后键⼊以下命令： grantselect,insert,update,delete on . to test1@"%" Identified by"abc";

但例1增加的⽤户是⼗分危险的，你想如某个⼈知道test1的密码，那么他就可以在internet上的任何⼀台电脑上登录你的mysql数据库并 对你的数据可以为所欲为了，解决办法见例2。

例2、增加⼀个⽤户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进⾏查询、插⼊、修改、删除的操作 （localhost指本地主机，即MYSQL数据库所在的那台主机），这样⽤户即使⽤知道test2的密码，他也⽆法从internet上直接访问数据 库，只能通过MYSQL主机上的web页来访问了。

grantselect,insert,update,delete on mydb.* to test2@localhost identifiedby "abc";

如果你不想test2有密码，可以再打⼀个命令将密码消掉：

grantselect,insert,update,delete on mydb.* to test2@localhost identifiedby "";

(四) 显⽰命令

1、显⽰数据库列表：

show databases;

刚开始时才两个数据库：mysql和test。mysql库很重要它⾥⾯有MYSQL的系统信息，我们改密码和新增⽤户，实际上就是⽤这个库进⾏ 操作。

**2、显⽰库中的数据表： **

use mysql； //打开库 show tables;

**3、显⽰数据表的结构： **

describe 表名;

4、建库：

create database 库名;

5、建表： use 库名；

create table 表名(字段设定列表)；

**6、删库和删表: **

drop database 库名; drop table 表名；

7、将表中记录清空：

delete from 表名;

8、显⽰表中的记录：

select * from 表名;

MySQL导⼊导出命令 ：

1.导出整个数据库

mysqldump -u ⽤户名 -p 数据库名 > 导出的⽂件名

mysqldump -u wcnc -p smgp_apps_wcnc >wcnc.sql

2.导出⼀个表

mysqldump -u ⽤户名 -p 数据库名 表名> 导出的⽂件名

mysqldump -u wcnc -p smgp_apps_wcnc users>wcnc_users.sql

3.导出⼀个数据库结构

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc>d:wcnc_db.sql

-d 没有数据 --add-drop-table 在每个create语句之前增加⼀个drop table

4.导⼊数据库

常⽤source 命令 ，进⼊mysql数据库控制台

如mysql -u root -p 　mysql>use 数据库

然后使⽤source命令，后⾯参数为脚本⽂件(如这⾥⽤到的.sql)

mysql>source d:wcnc_db.sql

（注：如果写成sourced:\wcnc_db.sql，就会报语法 使⽤load data 批量导⼊数据,这种做法可以瞬间导⼊数据,⽤处⾮常⼤! 复制代码

代码如下:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS ##字段操作,设置每个字段的分隔符  
                                                                                                             [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES ##⾏操作,从某⼀个字符开始,到某个字符 
                                                                                                             [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] ##⾏操作,忽略某⾏ 
                                                                                                             [(col_name_or_user_var,...)] ##字段操作,写⼊的字段与数据对应 
                                                                                                             [SET col_name = expr,...)] 

⽰例:

load data infile '/test/test.file' intotable 'test' fields terminated by "\t" (fieldsOne,fieldsTwo);

意思是：载⼊/test/test.file到表test中,使⽤\t分割字段,写⼊fieldsOne和fieldsTwo中,默认以换⾏符作为⼀个⾏分割!

基础关键字语句

BETWEEN...AND （在什么之间）和 IN( 集合)

例子如下：

查询年龄大于等于20 小于等于30：

SELECT * FROM student WHERE age >= 20 && age <=30;

SELECT * FROM student WHERE age >= 20 AND age <=30;

SELECT * FROM student WHERE age BETWEEN 20 AND 30;

查询年龄22岁，18岁，25岁的信息

SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25

SELECT * FROM student WHERE age IN (22,18,25);

is null(不为null值) 与 like（模糊查询）、distinct（去除重复值）

查询英语成绩不为null

SELECT * FROM student WHERE english IS NOT NULL;

_:单个任意字符

%：多个任意字符

查询姓马的有哪些？ like

SELECT * FROM student WHERE NAME LIKE '马%';

查询姓名第二个字是化的人

SELECT * FROM student WHERE NAME LIKE "_化%";

查询姓名是3个字的人

SELECT * FROM student WHERE NAME LIKE '___';

查询姓名中包含德的人

SELECT * FROM student WHERE NAME LIKE '%德%';

关键词 DISTINCT 用于返回唯一不同的值。

语法：SELECT DISTINCT 列名称 FROM 表名称

SELECT DISTINCT NAME FROM student ;

排序查询 order by

语法：order by 子句

order by 排序字段1 排序方式1 ， 排序字段2 排序方式2...

注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

例子如下

SELECT * FROM person ORDER BY math; --默认升序

SELECT * FROM person ORDER BY math desc; --降序

聚合函数：将一列数据作为一个整体，进行纵向的计算。

1.count：计算个数

2.max：计算最大值

3.min：计算最小值

4.sum：计算和

5.avg：计算平均数

分组查询 ：grout by

语法：group by 分组字段;

注意：分组之后查询的字段：分组字段、聚合函数

按照性别分组。分别查询男、女同学的平均分

SELECT sex , AVG(math) FROM student GROUP BY sex;

按照性别分组。分别查询男、女同学的平均分,人数

SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

分页查询

1. 语法：limit 开始的索引,每页查询的条数;

2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

3. limit 是一个MySQL"方言"

每页显示3条记录

SELECT * FROM student LIMIT 0,3; -- 第1页

SELECT * FROM student LIMIT 3,3; -- 第2页

SELECT * FROM student LIMIT 6,3; -- 第3页

内连接查询：

4. 从哪些表中查询数据

5. 条件是什么

6. 查询哪些字段

隐式内连接：使用where条件消除无用数据

查询员工表的名称，性别。部门表的名称

SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

SELECT
 
t1.name, -- 员工表的姓名
 
t1.gender,-- 员工表的性别
 
t2.name -- 部门表的名称
 
FROM
 
emp t1,
 
dept t2
 
WHERE
 
t1.`dept_id` = t2.`id`;

显示内连接

语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件

例子如下：

SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;

外连接查询

左外连接 -- 查询的是左表所有数据以及其交集部分。

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

例子：

查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称

SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;

右外连接 -- 查询的是右表所有数据以及其交集部分。

语法：

select 字段列表 from 表1 right [outer] join 表2 on 条件；

例子：

SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

子查询：查询中嵌套查询

查询工资最高的员工信息

查询最高的工资是多少 9000

SELECT MAX(salary) FROM emp;

查询员工信息，并且工资等于9000的

SELECT * FROM emp WHERE emp.salary = 9000;

一条sql就完成这个操作。这就是子查询

SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行单列的：

子查询可以作为条件，使用运算符in来判断

查询'财务部'和'市场部'所有的员工信息

SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';

SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

子查询

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列的：

子查询可以作为一张虚拟表参与查询

查询员工入职日期是2011-11-11日之后的员工信息和部门信息

子查询

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

普通内连接

SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11'

DCL(Data Control Language)数据控制语言

管理用户

添加用户

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

删除用户

语法：DROP USER '用户名'@'主机名';

权限管理

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

SHOW GRANTS FOR 'lisi'@'%';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

给张三用户授予所有权限，在任意数据库任意表上

GRANT ALL ON . TO 'zhangsan'@'localhost';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

REVOKE UPDATE ON db3.account FROM 'lisi'@'%';