当前位置:   article > 正文

数据库基本操作语句

数据库基本操作语句

数据库命令大全

数据库表基础语句

数据库连接与断开

连接: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(操作前先把原来表删除)

创建表:

  1. create table student(
  2. id int,
  3. name varchar(32),
  4. age int ,
  5. score double(4,1),
  6. birthday date,
  7. insert_time timestamp
  8. );

查看表结构: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 批量导⼊数据,这种做法可以瞬间导⼊数据,⽤处⾮常⼤! 复制代码

代码如下:

  1. LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS ##字段操作,设置每个字段的分隔符
  2. [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES ##⾏操作,从某⼀个字符开始,到某个字符
  3. [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] ##⾏操作,忽略某⾏
  4. [(col_name_or_user_var,...)] ##字段操作,写⼊的字段与数据对应
  5. [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 开始的索引,每页查询的条数;

  1. 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数

  1. limit 是一个MySQL"方言"

每页显示3条记录

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

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

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

内连接查询:

  1. 从哪些表中查询数据

  1. 条件是什么

  1. 查询哪些字段

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

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

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

  1. SELECT
  2. t1.name, -- 员工表的姓名
  3. t1.gender,-- 员工表的性别
  4. t2.name -- 部门表的名称
  5. FROM
  6. emp t1,
  7. dept t2
  8. WHERE
  9. 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'@'%';

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/922557
推荐阅读
相关标签
  

闽ICP备14008679号