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 -h 192.168.0.201 -P 3306 -u root -p123
3.查看mysql建表语句
命令:SHOW CREATE TABLE <table_name>
show create table employees;
| employees | CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
4.删除mysql 中的表
命令:drop table <表名>
mysql> drop table employees; Query OK, 0 rows affected (0.15 sec)
5.仅查询employess表中的last_name,first_name和birthd_date三个数据
mysql> select concat(last_name,' ',first_name) as name,birth_date as birthday from employees;
6.
从其他数据库中指定的表中导入数据,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 * from orders; +----------+-------------+ | 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 * from customers; +-------------+----------+ | 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 数据表名; use testdb; show table status from testdb like 'xuexi30';
11.
-- 修改表编码
ALTER TABLE `user` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci