MySQL常用命令

Mysql修改密码:

绕过密码授权表重置root密码
vim /etc/my.cnf 在[mysqld]添加
skip-grant-tables
然后service mysqld restart 
mysql (回车)
此时登陆mysql就不需要密码了,可以重置root密码

>use mysql;
>update user set password=password("新密码") where user="root";
>flush privileges;
>exit

[root@zabbix ~]# mysqladmin -u用户名 -p旧密码 password 新密码(知道密码的前提下,修改mysql密码)

Mysql 授权远程登录

mysql>GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql>flush privileges;

Mysql的增删改查:

增:

1.创建
create databases 数据库名;(创建新库)
use 数据库名;(进入库)
grant select,insert,update,delete,create,drop,alter on 数据库名.* to 数据库名@localhost identified by '密码';(创建数据库并分配用户)

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

例如,建立一个名为MyClass的表,

字段名	数字类型	数据宽度	是否为空	是否主键	   自动增加	      默认值
id	      int	       4	       否	   primary key	  auto_increment	 
name	    char	     20	         否	 	 	 
sex	     int	      4	          否	 	 	                              0
degree	  double	   16	       是	 	 	 

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
2.增加新字段

默认添加到表中的最后列
alter table <表名> add <新字段名> <数据类型>;
添加到第一列
alter table <表名> add <新字段名> <数据类型> first;
添加到指定列后面
alter table <表名> add <新字段名> <数据类型> after <指定列名>;

3.约束:
3.1 默认约束:指定某列的默认值

字段名 数据类型 default 默认值
举例:如果score没有指定值,默认为90

mysql> desc student_info;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int(10)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| sex    | char(1)     | YES  |     | NULL    |       |
| birth  | date        | YES  |     | NULL    |       |
| score  | int(2)      | YES  |     | 90      |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> 
3.2 唯一性约束

要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

mysql> select * from student_info;
+------------+-----------+------+------------+-------+
| stu_id     | name      | sex  | birth      | score |
+------------+-----------+------+------------+-------+
| 1303010301 | 李白      | 男   | 1993-04-01 |    90 |
| 1303010305 | 露娜      | 女   | 1995-01-10 |    90 |
| 1303010307 | 李逍遥    | 男   | 1995-01-10 |    93 |
| 1303010312 | 安琪拉    | 女   | 1995-08-10 |    92 |
+------------+-----------+------+------------+-------+
4 rows in set (0.00 sec)

mysql> alter table student_info add classroom varchar(5) 
unique after stu_id ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> select * from student_info;
+------------+-----------+-----------+------+------------+-------+
| stu_id     | classroom | name      | sex  | birth      | score |
+------------+-----------+-----------+------+------------+-------+
| 1303010301 | NULL      | 李白      | 男   | 1993-04-01 |    90 |
| 1303010305 | NULL      | 露娜      | 女   | 1995-01-10 |    90 |
| 1303010307 | NULL      | 李逍遥    | 男   | 1995-01-10 |    93 |
| 1303010312 | NULL      | 安琪拉    | 女   | 1995-08-10 |    92 |
+------------+-----------+-----------+------+------------+-------+
4 rows in set (0.00 sec)

mysql> 

在定义完所有列之后指定唯一约束,语法规则 alter table <表名> add constraint id unique (字段名);

3.3 非空约束

指定字段值不能为空
字段名 数据类型 not null

3.4 主键约束

主键分为两种类型:单字段主键和多字段联合主键
要求主键列的数据唯一,并且不允许为空;主键可以结合外键来定义不同数据之间的关系,并且加快数据库查询的速度。

alter table student_info add constraint id primary key (stu_id);

3.5 外键约束

外键用来在两个表之的数据之间建立连接,可以是一列或多列。一个表可以有一个或多个外键,外键可以为空值,若不为空值,则没一个外键值必须等于另一个表中主键的某个值。 其主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行。

constraint <外键名> foreign key (字段名1) references <主表名> (主键字段列)

mysql> create table stu_1
    -> (
    -> id int(11) primary key,
    -> name varchar(22) not null,
    -> location varchar(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table stu_2
    -> (
    -> id int(11) primary key,
    -> name varchar(25),
    -> class_id int(11),
    -> salary float,
    -> constraint stu_id foreign key(class_id) references stu_1(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 

注:子表的外键必须关联主表的主键,且关联字段的数据类型必须匹配,如果类型不匹配,则创建子键表时,会出现错误 "ERROR 1005(HY000): Can't create table 'database.tablename'(errno:150)"。

4. 设置表属性值自动增加

一个表中只能有一个字段使用 auto_increment 约束,且该字段必须为主键的一部分。

mysql> create table stu_class
    -> (
    -> id int primary key auto_increment,
    -> name varchar(25) not null,
    -> stu_id int,
    -> );

Query OK, 0 rows affected (0.04 sec)

mysql> insert into stu_class
-> (name,stu_id) values 
-> ('李白','1303010301'),
-> ('露娜','1303010305'),
-> ('安琪拉','1303010304');
Query OK, 1 row affected (0.04 sec)

mysql> select * from stu_class;
+----+-----------+------------+
| id | name      | stu_id     |
+----+-----------+------------+
|  1 | 李白      | 1303010301 |
|  2 | 露娜      | 1303010305 |
|  3 | 安琪拉    | 1303010304 |
+----+-----------+------------+
3 rows in set (0.00 sec)

mysql> 

删:

1.删整库整表一般用drop
> drop database drop_database;(删除存在的库)
2.删某表中的某记录用delete
> delete from  mysql.user where user='zabbixuser';
3.添加和删除表结构的某个字段
mysql> alter table customers add c_test int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          
|
+-----------+----------+------+-----+---------+----------------+
| c_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| c_name    | char(50) | NO   |     | NULL    |                
|
| c_address | char(50) | YES  |     | NULL    |                
|
| c_city    | char(50) | YES  |     | NULL    |                
|
| c_zip     | char(50) | YES  |     | NULL    |                
|
| c_contact | char(50) | YES  |     | NULL    |                
|
| c_email   | char(50) | YES  |     | NULL    |                
|
| c_test    | int(11)  | YES  |     | NULL    |                
|
+-----------+----------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> alter table customers drop c_test;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
4.删除表的外键约束

alter table <表名> drop foreign key <外键约束名>;

改:

语法:update 表名 set 字段=新值,… where 条件
mysql> update MyClass set name='Mary' where id=1;
1.改表字段数据类型:

1.例如修改表emp的ename字段定义,将varchar(10)修改为varchar(20)

格式:  alter table table_name modify 表中定义的字段 字段类型;
举例:  alter table emp modify ename varchar(20);

[数据库中ALTER 和 UPDATE的区别]

2.修改数据库表名

alter table <旧表名> rename <新表名>;

3.修改表字段名:

alter table table_name change <旧字段名> <新字段名> <新数据类型>;

4.修改字段到表的指定列之后

alter table <表名> modify <修改的字段> <数据类型> after <指定字段>;

5.修改表的存储引擎

alter table <表名> engine=<更改后的存储引擎名>;

查:

1.查看默认字符集
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> 

[如何修改默认字符集 >>>]

查询所有行:
命令: select <字段1,字段2,...> from < 表名 > where < 表达式 >
select host,user,password from mysql.user;(查看表中的指定值)

查询前几行数据:
例如:查看表 MyClass 中前2行数据
mysql> select * from MyClass order by id limit 0,2;

desc mysql.user;(查看某表的结构)
show columns from mysql.user;(同上)

查询不重复的记录: select distinct 表字段 from 表名;

多条件查询: select * from 表名 where 条件; where 后面的条件是一个字段的‘=’ 比较,除了‘=’外,还可以使用>、<、>=、<=、!=等比较运算符;多个条件之间还可以使用 or、and 等逻辑运算符进行多条件联合查询

[详细参考教程]

2.查看当前用户连接数

connection_id()返回mysql服务器当前连接的次数,每个连接都有各自唯一的ID。

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

processlist 命令的输出结果显示了有哪些线程在运行,不仅可以查看当前所有的连接数,还可以查看当前的连接状态,如果是root账号,能看到所有用户的当前连接。普通账号只能看到自己的占用的连接。show processlist 只列出当前100条,如果想列出全部的可以使用show full processlist 命令。

mysql> show processlist;show full processlist;
+----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host      | db      | Command | Time | State | Info             |
+----+------+-----------+---------+---------+------+-------+------------------+
|  2 | root | localhost | student | Query   |    0 | init  | show processlist |
+----+------+-----------+---------+---------+------+-------+------------------+
1 row in set (0.00 sec)

+----+------+-----------+---------+---------+------+-------+-----------------------+
| Id | User | Host      | db      | Command | Time | State | Info                  |
+----+------+-----------+---------+---------+------+-------+-----------------------+
|  2 | root | localhost | student | Query   |    0 | init  | show full processlist |
+----+------+-----------+---------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql> 
3.带 like 字符匹配查询

%表示任意数目的字符,_表示单字符

mysql> select * from student_info;
+------------+-----------+-----------+------+------------+-------+
| stu_id     | classroom | name      | sex  | birth      | score |
+------------+-----------+-----------+------+------------+-------+
| 1303010301 | NULL      | 李白      | 男   | 1993-04-01 |    90 |
| 1303010305 | NULL      | 露娜      | 女   | 1995-01-10 |    90 |
| 1303010307 | NULL      | 李逍遥    | 男   | 1995-01-10 |    93 |
| 1303010312 | NULL      | 安琪拉    | 女   | 1995-08-10 |    92 |
+------------+-----------+-----------+------+------------+-------+
4 rows in set (0.00 sec)

mysql> select stu_id,name,sex,score from student_info where birth like '1995%';
+------------+-----------+------+-------+
| stu_id     | name      | sex  | score |
+------------+-----------+------+-------+
| 1303010305 | 露娜      | 女   |    90 |
| 1303010307 | 李逍遥    | 男   |    93 |
| 1303010312 | 安琪拉    | 女   |    92 |
+------------+-----------+------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql> mysql> select stu_id,name,sex,score from student_info where name like '__';
+------------+--------+------+-------+
| stu_id     | name   | sex  | score |
+------------+--------+------+-------+
| 1303010301 | 李白   | 男   |    90 |
| 1303010305 | 露娜   | 女   |    90 |
+------------+--------+------+-------+
2 rows in set (0.00 sec)

mysql> 
4.带 IN 的查询
mysql> select c_id,c_name,c_email from customers where c_email is NULL;
+-------+----------+---------+
| c_id  | c_name   | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL    |
+-------+----------+---------+
1 row in set (0.00 sec)

mysql> 
5.查询不重复的结果

select distinct <字段名> from 表名;
·查看表结构信息:

1. desc tablename
2. show create table tablename \G; 比较全面,可查看引擎和字符等

排序和限制:
用关键字 ORDER BY 来实现 DESC 和 ASC 是排序顺序关键字,DESC 表示按照字段进行降序排列,ASC 则表示升序排列,如果不写此关键字默认是升序排列 SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] , field2 [DESC|ASC],……fieldn [DESC|ASC]]

mysql> select * from emp order by deptno;
comments powered by Disqus