mysql常用命令查看链接_mysql 常用命令,连接数据库,查看建表语句,批量导入数据,批量更新数据,连接查询...

1.

1)MySQL 连接本地数据库,从cmd中进入mysql命令编辑器: root root分别为用户名和密码

mysql -uroot -proot

2)MySQL 连接本地数据库，用户名为“root”，密码“123”(注意：“-p”和“123” 之间不能有空格)

C:\>mysql -h localhost -u root -p123

2、MySQL 连接远程数据库(192.168.0.201)，端口“3306”，用户名为“root”，密码“123”

C:\>mysql -h192.168.0.201-P3306-u root -p123

3.查看mysql建表语句

命令：SHOW CREATE TABLE

showcreate tableemployees;| employees | CREATE TABLE`employees` ( `emp_no`int(11) NOT NULL, `birth_date` dateNOT NULL, `first_name`varchar(14) NOT NULL, `last_name`varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` dateNOT NULL,PRIMARY KEY(`emp_no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

４.删除mysql 中的表

命令：drop table

mysql> drop tableemployees;

Query OK,0 rows affected (0.15 sec)

５.仅查询employess表中的last_name,first_name和birthd_date三个数据

mysql> select concat(last_name,' ',first_name) as name,birth_date as birthday from employees;

６.

从其他数据库中指定的表中导入数据，employees.employees 导入前10条数据

mysql> insert into employees select * from employees.employees limit 10;

7.批量更新数据

UPDATE `sys_invitation` SET `is_known`='0' WHERE (`to_id`='99996145289621');

8.连接查询

1).原生查询

select *,count(o.order_id) from customers as c ,orders as o where c.customer_id=o.customer_id and c.city='shanghai' group by c.customer_id having count(o.order_id)>0;

2).左连接

select *,count(o.order_id) from customers as c left join orders as o on c.customer_id=o.customer_id where c.city='shanghai' group by c.customer_id having count(o.order_id)>0;

可把left join 改为inner join或者right join.

这里依赖两张表orders和customers.

orders建表语句:

CREATE TABLE`orders` (

`order_id`varchar(10) NOT NULL,

`customer_id`varchar(10) NOT NULL,PRIMARY KEY(`order_id`)

) ENGINE=InnoDB

插入数据:

mysql> insert into orders select 01332,111;

mysql> insert into orders select 01333,112;

mysql> insert into orders select 01334,113;

mysql> insert into orders select 01335,114;

mysql> insert into orders select 01336,111;

mysql> insert into orders select 01337,112;

mysql> insert into orders select 01338,115;

查看数据:

mysql> select * fromorders;+----------+-------------+

| order_id | customer_id |

+----------+-------------+

| 1332 | 111 |

| 1333 | 112 |

| 1334 | 113 |

| 1335 | 114 |

| 1336 | 111 |

| 1337 | 112 |

| 1338 | 115 |

+----------+-------------+

7 rows in set (0.00 sec)

customers建表语句:

create table customers(customer_id varchar(10) not null, city varchar(10), PRIMARY KEY(customer_id) )ENGINE=INNODB;

customers插入数据:

mysql> insert into customers select 112,'shanghai';

mysql> insert into customers select 113,'shanghai';

mysql> insert into customers values(114,'beijing');

mysql> insert into customers values(115,'beijing');

mysql> insert into customers select 116,'hangzhou';

查看数据:

mysql> select * fromcustomers;+-------------+----------+

| customer_id | city |

+-------------+----------+

| 111 | shanghai |

| 112 | shanghai |

| 113 | shanghai |

| 114 | beijing |

| 115 | beijing |

| 116 | hangzhou |

+-------------+----------+

6 rows in set (0.00 sec)

9.更改数据库名称

alter table tb1 rename tb2;

将表tb1名称更改为tb2,使用rename命令

10.查看mysql表大小和记录数

SHOW TABLE STATUS FROM 数据库名 LIKE数据表名;usetestdb;

showtable status from testdb like 'xuexi30';

11.

-- 修改表编码

ALTER TABLE `user` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci