首页 > > 网络编程 > Mysql >

常用MySQL语法

2018-07-13 02:45:57来源：博客园阅读 ()

一、进入MySQL与退出MySQL

1.进入MySQL步骤：先打开CMD命令行；命令：C:\Users\admin> mysql -h(域名,可填或不填) -u(账号) -p(密码)；

连接成功时会跳出以下命令：

Connection id: 9 【这个表示：连接次数】
Current database: 【这个表示：当前操作的数据库名称】
Current user: root@localhost 【这个表示：当前登录的用户用户名@地址】
SSL: Not in use 【这个表示：是否在使用加密链接 SSL加密】
Using delimiter: ; 【这个表示：命令分隔符，结束时以分号结束】
Server version: 5.5.48 MySQL Community Server (GPL) 【这个表示：当前mysql数据库的版本型号】
Protocol version: 10 【这个表示：协议版本】
Connection: 127.0.0.1 via TCP/IP 【这个表示：连接信息】
Server characterset: utf8 【这个表示：mysql数据库管理系统的安装编码】
Db characterset: utf8 【这个表示：数据库编码】
Client characterset: utf8 【这个表示：当前客户端的默认编码】
Conn. characterset: utf8 【这个表示：当前数据库链接使用的编码】
TCP port: 3306 【这个表示：端口号】
Uptime: 7 min 11 sec 【这个表示：数据库的启动时间[距离上一次重启的时间]】

2、退出MySQL：quit或exit (回车) 或者 Ctrl+C

二、MySQL基础的操作

1.创建数据库命令：mysql> create database <数据库名>

2.显示所有的数据库命令：mysql> show databases (注意：最后有个s)

3.删除数据库命令：mysql> drop database <数据库名>

4.进入数据库命令：mysql> use <数据库名>

5.转换编码命令：mysql> set names gbk

6.查看当前使用的数据库命令：mysql> select database();

7.当前数据库所有的表信息命令：mysql> show tables (注意：最后有个s);

8.导出数据库命令：mysqldump -h(域名) -u(帐号) -p(密码) 数据库名 > 本地路径+文件名; (注意：导出数据库前必须先要退出MySQL控制台。还有导出的文件后缀名一般都是使用 .sql)

9.导入数据库命令：首先登入MySQL控制台C:\Users\admin> mysql -h(域名,可填或不填) -u(账号) -p(密码); 在进入数据库mysql> use <数据库>; 在导入数据库mysql> source E:/school.sql (注意：source 后面是你sql文件所在的绝对路径)

10.在MySQL的表中增加字段命令：mysql> alter table <表名> add column userid smallint not null primary key auto_increment; 这样，就在这张表dbname中添加了一个字段userid，类型为smallint

11.删除表命令：mysql> drop table student

12.清空表命令：mysql> delete from student

13.更新表命令：mysql> update student set `name`='小A' where `id`=1

三、表操作，操作前必须连接某个数据库

1.建表命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

例如：

mysql> create table Student(

-> id smallint unsigned not null primary key auto_increment comment '注释id',

-> name char(20) not null comment '注释name',

-> sex tinyint null default "1" comment '注释sex[男为1，女为2]',

->) engine = innoDB;

2.获取表结构命令：desc <表名> 或者 show columns from <表名> 还有 show create table <表名>

例如：

mysql> desc Student

mysql> show columns from Student

mysql> show create table Student

3.删除表命令：drop table <表名>

例如：

mysql> drop table Student

4.添加新数据命令：insert into <表名> ( 字段名1 , ...字段名n ) values ( 值1 , ...值n )。或者 insert into <表名> values( 要输入全部的数据结构 )，(注意：如需添加多条数据请用逗号隔开)

例如：

mysql> insert into Student values(1, '小A' , 2), (2, '小B', 1), (3, '小C', 2)

5.查询表中数据命令：select <字段1，字段2，...字段n> from <表名> where <条件>。或者 select * from <表名>

例如：

mysql> select * from Student

6.删除表中数据命令：delete from <表名> where <条件>。或者 delete from <表名>

例如：

mysql> delete from Student

7.修改表中的数据命令：update <表名> set 字段=新值, … where <条件>

例如：

mysql> update Student set name='小D' where id=3

8.更改表名命令：rename table <旧的表名> to <新的表名>

例如：

mysql> rename table Student to Class

9.用文本方式将数据装入数据库表中

例如：D:/mysql.txt

mysql> load data local infile "D:/mysql.txt" into table Student

四、数据类型

1.字符串类型

CHAR(M)型：固定长度的字符串，最大长度为255个字节

VARCHAR(M)型：可变长度，最多不超过65 535字节，如在创建时指定VARCHAR（n），则可存储0~n个字节

TINYTEXT型：可变长度的字符串，支持最大长度255个字节

TEXT型：可变长度的字符串，支持最大长度65 535个字节

MEDIUMTEXT型：可变长度的字符串，支持最大长度16 777 215个字节

LONGTEXT型：可变长度的字符串，支持最大长度4 294 967 295个字节

2.整型与浮点型　　　　有符号[正负]　　　　　　　　　　　　　　　　　　　　无符号unsingn

TINYINT型：　　　　-128 ~ 127　　　　　　　　　　　　　　　　　　　　 0 ~ 255 (就是2的8次方-1)

SMALLINT型：　　　 -32768 ~ 32767　　　　　　　　　　　　　　　　　　 0 ~ 65535 (就是2的16次方-1)

MEDIUMINT型：　　-8388608 ~ 8388607　　　　　　　　　　　　　　　　 0 ~ 16777215 (就是2的24次方-1)

INT型：　　　　　　 -2147483648 ~ 2147483647　　　　　　　　　　　　 0 ~ 4294967296 (就是2的32次方-1)

BIGINT型：　　　　 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615 (就是2的64次方-1)

3.日期和时间类型

DATE型： YYYY-MM-DD 格式表示的日期值 / 取值范围 1000-01-01 ~ 9999-12-31

TIME型： hh:mm:ss 格式表示的时间值 / 取值范围 -838:59:59-838:59:59

DATETIME型： YYYY-MM-DD hh:mm:ss 格式表示日期与时间 / 取值范围 1000-01-01 00：00：00 ~ 9999-12-31

TIMESTAMP型： YYYYMMDDhhmmss 格式表示的时间戳 / 取值范围 19700101080001——20380119111407

YEAR型： YYYY 格式的年份 / 取值范围 1901~2155

五、字段约束

我们仅仅是定义了字段的数据类型还不够，我们还需要一些附加的属性来约束或者规范字段

UNSIGNED 只能用于设置数据类型，不允许出现负数，最大存储长度会增加一倍，ZEROFILL 只能用于设置数值类型,在数值之前自动用0补齐不足的位数，AUTO_INCREMENT 如果给某个数据表的一个整数数据列定义可选的AUTO_INCREMENT属性，那么当用户向这个数据表插入一个新记录时，MySQL就会自动地把这个整数数据列的当前最大取值加上1之后赋值给新记录中的这个整数字段; 使用AUTO_INCREMENT属性需要注意几个问题：这个属性必须与NOT NULL 、PRIMARY KEY 或者 UNIQUE 属性同时使用; 每个数据表最多只能有一个AUTO_INCREMENT数据列; MySQL的这种ID值自动生成机制只在用户使用INSERT命令插入新记录、并且没有为ID字段明确地给出一个值或NULL时才起作用。如果用户给出了一个具体的值并且这个值在ID列里出现过，MySQL就将使用这个ID值生成一条新记录;

NULL和NOT NULL 默认为NULL，即插入值时没有在此字段插入值，默认为NULL值，如果指定了NOT NULL，则必须在插入值时在此字段填入值 DEFAULT 可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值主键(PRIMARY KEY) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。外键（foreign key）是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）

六、MySQL 运算符

1.比较运算符

= 　　等于
> 　　大于
< 　　小于
>= 　大于等于
<= 　小于等于
<> 　不等于
!> 　不大于
!< 　不小于

%　　匹配任意多个字符

_　　匹配任意一个字符

2.逻辑运算符

AND 如果组合的条件都是 TRUE , 返回 TRUE

OR 如果组合的条件其一是 TRUE , 返回 TRUE

NOT 如果条件是 FALSE , 返回 TRUE

七、聚集函数

SUM ( ) 求和
AVG ( ) 平均值
COUNT ( ) 表达式中记录的数目
COUNT (* ) 计算记录的数目
MAX 最大值
MIN 最小值
VAR 方差
STDEV 标准误差
FIRST 第一个值
LAST 最后一个值

八、查询字句

1.用ORDER BY子句排序结果（ASC表示升序，为默认值，DESC为降序）

ORDER子句按一个或多个（最多16个）字段排序查询结果，可以是升序（ASC）也可以是降序（DESC），缺省是升序

ORDER子句通常放在SQL语句的最后

ORDER子句中定义了多个字段，则按照字段的先后顺序排序

例如：对学生的年龄进行排序

SELECT * FROM student ORDER BY age DESC;

2.LIMIT 子句对查询出的结果做限制，往往我们不想一次取出所有的数据

LIMIT有两个参数：一个参数、表示取得行数！两个参数、第一个参数表示从第几行去，第二个参数表示取多少行

3.LEFT JOIN ON 连表查询

连表查询有三种方式，分别是：左连和右连还有内连

左连: 以左边的主表为主，如果主表没有数据，则不会返回结果，即便是从表有数据
left join `表名` on 连表条件

右连：以右边的从表为主，如果从表没有数据，则不会返回结果，即便是主表有数据
right join `表名` on 连表条件

内连：以两张表为主，必须两张表都有数据的情况，才会返回结果。
inner join `表名` on 连表条件

九、MySQL的函数

mysqli_connect 连接数据库，参数是访问地址、账号、密码、数据库名

mysqi_connect_errno 返回数据库连接的错误状态码

mysqli_connect_error 返回数据库连接的错误信息

mysqli_close 关闭数据库连接，参数是数据库连接

mysqli_query 对数据库执行一次sql语句的执行

mysqli_num_rows 取得结果集中行的数目

mysqli_fetch_array 从结果集中取得一行作为关联数组，或数字数组，或二者兼有，第二个参数索引数组MYSQLI_NUM 关联数组 MYSQLI_ASSOC 两者皆有MYSQLI_BOTH

mysqli_fetch_assoc 从结果集中取得一行作为关联数组

mysqli_fetch_row 从结果集中取得一行作为枚举数组

mysqli_fetch_object 从结果集中取得一行作为对象

mysqli_free_result 释放结果内存

十、事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据库中的数据库更新处理的提交和取消进行管理。事务处理的终止指令包括COMMIT(提交处理) 和ROLLBACK(取消处理)两种。 DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四种特性。通常将这四种特性的首字母结合起来，统称为ACID特性。

例:给A君减10元，给B君加10元。 START TRANSACTION; UPDATE user SET money=money-10 WHERE name = 'A君'; UPDATE user SET money=money+10 WHERE name = 'B君'; COMMIT;

（注）、以下是在网络看到使用MySQL的一些管理心得

在windows中MySql以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start"命令，注意启动者应具有管理员权限。
刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、 root帐户设置密码，可用如下命令进行：
use mysql;
delete from User where User="";
update User set Password=PASSWORD('newpassword') where User='root';
如果要对用户所用的登录终端进行限制，可以更新User表中相应用户的Host字段，在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令：
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
上面命令参数是常用参数的一部分，详细情况可参考文档。此处的mydb是要登录的数据库的名称。
在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过 User表来实现的，添加新用户常用的方法有两个，一是在User表插入相应的数据行，同时设置相应的权限；二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下：
grant all on mydb.* to NewUserName@HostName identified by "password" ;
grant usage on *.* to NewUserName@HostName identified by "password";
grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";
grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password";
若要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段，也可以使用REVOKE操作。
下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释：
全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
Alter: 修改已存在的数据表(例如增加/删除列)和索引。
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。