Mysql基本操作

来源:互联网 发布:阿里云字体图标 编辑:程序博客网 时间:2024/03/29 10:03

1、数据库的启动和停止

Cmd命令:
net stop MySQL 开启数据库
net start mysql  关闭数据库
cls 清屏
mysql  -hlocalhost -uroot -proot 连接数据库
set path 显示环境变量中的各路径
exit 退出mysql服务器

2、数据库操作

show databases 显示所有的数据库
show create database cao  显示数据库名为cao的数据库
create database if not exists cao utf8创建数据库cao
drop database if exists cao 删除cao这个数据库
alter database cao charset utf8 修改数据库的字符集
use cao;打开cao这个数据库

3、数据表操作

show tables (from cao) 显示数据库的数据表操作,省略[from db_name],则显示当前数据库中的所有表
show create table cao   显示创建的数据表cao
desc cao 显示数据表的结构
create table if not exists student(
id int not null primary key auto_increment , (id 整型,不空,主键,自动增长)
name varchar(6) not null,
sex char(1) not null default '男'
);
drop table student;删除student 表
set  names gbk  在cmd模式下显示汉字
添加新字段
alter table student  add age tinyint not null;给student 表添加一个 age 字段
alter table student add (
bonus float(2,6) not null,
city varchar(7) not null
);添加多个字段
删除字段
alter table student drop  name,drop city;
修改字段(modify)  modify只能修改字段的类型和属性,不能修改字段名称
alter table student modify name char(3) not null;
同时修改字段名称和属性(change)
alter table student change name username varchar(3) not null;
修改表的存储引擎
alter table student engine myisam;
修改表的字符集
alter table student charset utf8;
修改表的数据表名
alter table student rename student1;
复制表结构和数据
create table student select * from  student1;复制的数据表没有主键和自动增长,需要修改id字段,添加主键和自动增长
alter table student modify id int not null primary key auto_increment;

4、MySQL数据类型

整型:
tinyint 0-255(无符号范围)
smallint 0-65535
mediumint 0-16777215
int 2的32次方(到亿)
big int

lint(4),表示整数长度为4,并不是说最大只能插入4位整数。

        如果位数多于4位,并且值不大于int所表示的最大值的范围,数值正常插入。

        如果位数小于4位,那么在数值的左边用空格填充到4位。

unsigned扩展属性:无符号

浮点型
float(M,D)
double(M,D)
decimal()

l  浮点数表示的范围,要比整数大的多。

l  Float和double表示的一般是近似值;而decimal表示的是精确的。

l  Float和double都可以省略参数,当然小括号也不要了。Float小数精确到5位,多于5位四舍五入;而double小数精确到15位数,多于15位四舍五入。

l  M代表总位数,包括小数点和负数;D代表小数位数。

l  decimal一直是精确的,如果不带参数,默认为decimal(10,0)。小数位数的取值是0-30位小数。

字符型

char(0-255) 定长字符串,对于整存整取的读取速度和写入速度非常快。
varchar(0-65535)  变长字符串。存取速度没有char()
enum() 枚举类型,与表单中的单选项一样,只能选一项,不能不选

l 格式:enum(value1,value2,value3,)

l 说明:最多可以列举65535个不同的值。多个值之间用逗号隔开。

set()集合:相当于表单中的多选,可以同时选择多个,也可以一个不选。

文本型

tinytext() 0-255 小段文本
text ()   0-65535 

5、数据的增删改查操作

插入数据(INSERT)

lINSERT INTO table_name(字段1,字段2,字段3,) VALUES(1,2,3,)

INSERT INTO student (name,sex,age ) values ('张三','男',23);

更新数据(UPDATE)

 UPDATE table_name SET col1=value1,col2=value2, [WHERE条件]

UPDATE student set name="李四",sex="女" WHERE id=1;

删除数据(DELETE)

DELETE FROMtable_name [WHERE条件]
DELETE FROM student where name="张三";

查询数据(SELECT)

SELECT [select选项]字段列表 FROM表名 [WHERE] [GROUPBY][HAVING][ORDER BY][LIMIT]
SELECT * FROM student where id>10;

 ALL:显示所有符合条件的记录,包含重复行,默认值为ALL

 DISTINCT:去除重复的行、过滤重复的行;

select distinct name from student;

字段列表

(1)“*”:列出所有字段,与数据表的字段个数一样。如:select * from student

(2)col1,col2,col3,…:列出部分字段,不分先后顺序;如:selectname,sex,age from student

(3)字段取别名:别名一般都是比较短的、方便记忆的名称

语法:SELECT col_name AS aFROM table_name

FROM子句

FROM之后,可以跟一张表,也可以跟随多张表,多个表名之间用逗号隔开

语法:SELECT 字段列表 FROM table1 AS 别名,table2 AS 别名,table3AS别名 [WHERE条件]

提示:如果省略where条件,查询出来的结果是“笛卡尔积”。

提示:AS关键字,可以给表起一个别名;表的别名,就可以在字段列表中来使用。

例如:
  select a.id,a.name,b.age from studenta as a ,studentb as b where a.id =b.uid;

WHERE子句

SELECT 字段列表 FROM table_nameWHERE逻辑表达式逻辑运算符逻辑表达式

 逻辑表达式的结果一定是布尔值(true、false);

 如果为TRUE,则显示该记录;如果为FALSE,则不显示该记录;

逻辑运算符:and(&&) 、or(||)

GROUP BY分组子句

分组是为了统计信息。分组一般要与聚合函数搭配使用。

l  :SELECT 字段列表 FROM table_name GROUP BY col_name [ASC | DESC] ,col_name [ASC | DESC]

l  参数:

u  字段列表:只能是聚合函数分组字段其它普通字段不允许。如果有普通字段,则记录的内容是组中的第1条记录内容,这样一来,可能匹配不上。

u  col_name:分组的字段名称。

u  [ASC | DESC]:对分组的值进行排序,ASC为升序(默认),DESC为降序。

常用的聚合(统计)函数:聚合函数可以单独使用,不用搭配group by子句

l  count():计数函数。可以对NULL值进行统计。如:count(*)全部记录、count(city)对指定字段统计

l  sum():求和函数。将忽略NULL值,不会对NULL值统计。如:sum(salary)对工资列求和

l  avg():求平均值函数。将忽略NULL值。如:avg(salary)对工资列求平均

l  max():求最大值函数。将忽略NULL值。如:max(salary)对工资列求最大值

l  min():求最小值函函数。将忽略NULL值。如:min(salary)对工资列求最小值

 举例:
select name ,city,sum(salary) from student group by city;
HAVING子句

l  having功能与where相似,都是记录过滤(条件判断)  having一般与group by联合使用

l  语法:SELECT 字段列表 FROM table_name [GROUP BY分组] HAVING条件判断

例如: select city,count(*)  as 总数量,avg(salary) as 平均工资 from student group by city having 平均工资>2000;
ORDER BY排序子句

l  描述:对字段排序。可以同时对多列排序,如果第1列有相同的值,则按第2列排序;

l  语法:SELECT 字段列表 FROM student ORDER BY col_name [ASC | DESC] ,col_name [ASC | DESC]

LIMIT限制输出子句

l  限制最后显示的记录数。

l  语法:SELECT 字段列表 FROM table_nameLIMIT [startrow,] pagesize

l  参数:

u  startrow:表示起始行号。默认为0。从第0行起。如果是0行,可以省略。

u  pagesize:表示每页取多少条记录。

工资按降序排序,并筛选出前5条记录

select * from student where salary>100 order by id asc limit 0,5;

MySQL运算符

比较运算符:=、>、>=、<、<=、!=

l  提示:比较运算符的结果,一定是布尔值;

l  举例:

SELECT *FROM student WHERE id=5;

 SELECT *FROM student WHERE id>5;

 SELECT *FROM student WHERE id!=5;

IS NULL 或IS NOT NULL

l  描述:判断字段的值是不是NULL。

l  举例:SELECT * FROMstudent WHERE city is null;

l  举例:SELECT * FROMstudent WHERE city is not null;

IN 和 NOT IN

l  描述:判断字段的值,是否在一个枚举的范围内。

l  举例:SELECT * FROMstudent WHERE id in(1,5,7,9); //相当于 id=1 or id=5 or id=7

举例:SELECT* FROM student WHERE city in(‘河南省’ , ‘山东省’ , ‘山西省’); //city= ‘河南省’ or city = ‘山东省

between and 和not between and

l  描述:在……之间,或不在……之间

注意:只能用于数值型的字段,第2个值,不能比第1个值小。

l  举例:SELECT * FROMstudent WHERE id between 10 and 20;//相当于id>=10 and id<=20

l  举例:SELECT * FROMstudent WHERE age between 30 and 35;

like和 not like

l  描述:模糊查询。

l  语法:字段名 [NOT] LIKE pattern

l  参数:pattern表示模糊查询的匹配规则。

l  通配符:

u  “%”:匹配任意多个字符(0-N);

n 姓张的:“张%”

n 含有“玲”字的:“%玲%”

n 以“燕”结尾:“%燕”

u  “_”:匹配任意一个字符。

n 查询“张?军”:“张_军”

n 查询“张军?”:“张军_”

select * from student where name like "%国%";

连接查询

概念

至少连接两张表

l  将两张表的左右拼接在起,生成一个新的结果集

l  取两张表的部分字段,或全部字段,然后,左右拼接在一起,生成一个新的结果果。

语法格式

l  语法:SELECT 字段列表FROM table1 [AS别名] [INNER | LEFT | RIGHT ] JOIN table2 [AS别名][ON条件][WHERE条件]

l  说明:

u  table1为左表,table2为右表;

u  inner join:内连接(最常用的),又称为“等值连接”。

u  left join:左连接。以左表为准,来匹配右表的数据。

u  right join:右连接。以右表为准,来匹配左表的数据。

u  [ON条件]:指定左右两张表关联的条件。如果省略,返回“笛卡尔积”结果集。

[where条件]:对最终的结果集,进一步筛选
例如:
select a.name,a.city,b.bonus from table1 as a inner join table2 as b on a.id=b.id where b.id>18;

子查询

1、概述

l  将一个查询的结果,当成另一个查询的条件来使用。

l  里面的查询称为“子查询”,外边的查询称为“主查询”“父查询”。

l  子查询一定要放在小括号内。

例如:
select * from student where salary>(select avg(salary) from student) and id<50;

标量子查询

l  概述:返回的结果是11的值。要与比较运算符(>、<、>=、<=、=、!=)配合使用,其它运算符不能使用。

l  语法:SELECT * FROM table_name WHERE col_name 比较运算符(标量子查询)

l  提示:标量子查询一定要用在WHERE子句之后。

注意:子查询的结果是一行一列的,多余一行则会报错。所以,需要加limit限制条件

列子查询

l  描述:返回N1的数据(只有一个字段)。

l  注意:列子查询中,可以使用inanyall运算符,还可以搭配比较运算符

1、使用in的列子查询

l  描述:返回N行1列的数据。判断字段的值,是不是在返回的N行1列中。

l  语法:SELECT 字段列表 FROM table_name WHEREcol_name in(subquery)

l  举例:SELECT *FROM student WHERE id in(1,3,5);但是(1,3,5)来自于子查询。

2、使用any的列子查询(或关系)

l  描述:any字面含义是:任意一个。any一般要与比较运算符配合使用。

l  语法:SELECT 字段列表FROM table_name WHERE字段名称比较运算符 any(subquery)

l  举例:SELECT * FROMstudent WHERE id>any(1,5,7);其中(1,5,7)是由子查询产生的结果

l  说明:也就是记录的每个ID值,要与子查询返回的列表一一比对,只要有一个条件满足,则返回TRUE

举例说明:查询id>any(3,8,13)的学生

3、使用all的列子查询(并且关系

l  描述:all字面意思“所有”。主查询字段值,要与子查询的每个值都要匹配才能算条件成立。如果条件为TRUE,则主查询中的记录,就留下;如果只要有一值不匹配,则条件为FALSE,则就不会留在最终结果集中。

l  语法:SELECT 字段列表 FROM table1 WHERE字段比较运算符all(子查询)

l  举例:SELECT * FROMstudent WHERE id >all(5,9,50);  其中(5,9,50)是由子查询产生的。

举例说明:查询id>all(1,3,5)的学生

行子查询

l  描述:子查询返回1行N列的数据。

l  语法:SELECT 字段列表 FROM table1 WHERE (col1,col2)比较运算符 (SELECT col1,col2 FROM table2)

l  说明:

u  如果是单列的话,与标量子查询相同。

u  如果是单列的话,小括号不需要。

u  如果是多列的话,必须要加小括号。

u  WHERE之后的列数,一定要与子查询返回的列数相同。

u  关系相当于:col1 = col1and col2=col2

子查询返回的一定是一行,不能是多行。如果是多行,请使用limit限制。

举例说明:查询salary=12300city=‘北京市’的学生

表子查询(FROM之后)

l  描述:返回的是多行多列的数据。

l  语法:SELECT 字段列表FROM(SELECT id,name,age FROMstudent) AS别名WHERE条件

l  说明:

u  表子查询,一般用在FROM子句之后;

u  原来FROM之后跟的是表名,而现在不写表名了,而是子查询了。

u  子查询返回一个虚拟表,必须给虚拟表定义别名,否则,多表联查时,条件没法写了。

举例说明:查询“总工资”>40000的学生

数据的备份和恢复

数据备份

l  描述:数据备份,使用mysqldump客户端程序进行的。在CMD模式下使用(不用登录MySQL)。

l  说明:mysqldump.exe 客户端程序的路径:C:\wamp\MySQL5\bin

l  语法:mysqldump.exe –h主机名 –u用户名 –p密码 [-d] dbname [tablename] [ > |< ]c:\file.sql

l  参数:

u  -h:主机名,默认是localhost

u  -u:用户名,默认是root。

u  -p:密码。

u  [-d]可选。如果加上-d参数,则导出数据表的结构,不含数据;如果不加-d参数,则结构和数据全部导出。

u  Dbname:要导出的数据库名称。

u  [tablename] 如果省略,则导出某个库中的所有数据表;如果加上,导出指定库中的指定表。

u  “>”导出符号;“<”导入符号。

u  “c:\file.sql”指定要导出的文件的路径和文件名。

导出数据库表结构
mysqldump.exe -hlocalhost -uroot -proot  -d itcast> C:my.sql    末尾不加分号
导出数据表结构和数据
mysqldump.exe -hlocalhost -uroot -proot   itcast> C:my.sql    末尾不加分号(不加d)

恢复数据

l  描述:使用mysql.exe客户端程序,来实现数据的恢复。

l  提示:都是在CMD模式下操作的,不用登录MySQL客户端。

注意:在导入时,必须先创建一个空的数据库。

导入数据库数据
mysql.exe -hlocalhost -uroot -proot   itcast <C:my.sql  itcast 是新建的空的数据库    
还可以:
 use cao;
source  /var/www/shop.sql 将shop.sql数据表导入到cao这个数据库下

创建用户

l  语法:CREATE USER ‘username’ @ ‘hostname’  [ IDENTIFIED BY密码 ]

l  说明:

u  数据库用户的格式:用户名@主机名。

u  主机名:不能省略,但可以使用通配符“%”代替,“%”代表任何主机。

u  举例

n ‘yao’ @ ‘localhost’ //只能本机登录;

n ‘yao’ @ ‘%’  //任何地方都可以登录;

n ‘yao’ @ ‘192.168.10.23’  //只能从指定主机登录

[identified by]可选。如果省略,该用户没有密码。如果加上的话,就是对密码进行加密。

提示:现在创建的用户,没有任何的权限。

例如:
CREATE USE 'yao'@'%' IDENTIFIED BY "123456";

删除用户:必须具有删除用户权限,才能删除用户

l  语法:DROP USER ‘user’ @ ‘host’

DROP USER 'yao'@'%"; 

5、用户授权

语法:grant 权限列表 ON 对象  TO user@host [withgrant option]

l  说明:

u  权限列表

n ALL:所有权限,除了with grant option 权限之外。

n select , insert , update ,delete , alter , show……

u  对象:这些权限可以操作哪些数据库,哪些数据表。

n “*.*”所有数据库的所有表;

n “dbname.*”指定数据库下的所有表;

u  [with grant option]是否需要给某个用户分配一个“分配权限”的权限。一般情况下,该权限为最高权限,只有超级管理员才能拥用。

grant all on *.* to 'yao"@"%";

6、撤消用户授权

l  语法:revoke 权限列表 ON 对象 FROM user@host

l  举例:revoke all ON*.* FROM ‘yao’ @ ‘%’

revoke all on *.* FROM ‘yao’ @ ‘%’
0 0
原创粉丝点击