从命令行调用mysql程序:
[开始]--[运行]--cmd命令
获得用户账户列表
shell->mysql -u root -p -e "SELECT User, Host FROM User" mysql
Enter password: ******
登录数据库:
shell->mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
或者:
shell->mysql -u root -p******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
注:-p******和-p ******的区别
-p******表示使用******为密码
-p *******表示指定使用那个数据库(*******为数据库的名称)
从外核执行查询: (注意 -e 和 --execute= 的区别)
shell->mysql -u root -p --execute="SELECT id from pole3_production.channel_items"
Enter password: ******
shell->mysql -u root -p -e "SELECT id from pole3_production.channel_items"
Enter password: ******
该条命另执行完,仍然回到外核
注:--execute后面必须紧跟一个等号(=)
显示当前的数据库列表:
mysql>show databases;
使用末各数据库:
mysql>use databases
Database changed
显示当前选择了的数据库
mysql>select database();
创建数据库:
mysql>create database database_name;
创建表:
mysql>create table table_name (*** varchar(),**** varchar(),...);
查看当前表的创建信息
mysql> DESCRIBE table_name;
给表插入一条数据
mysql> INSERT INTO table_name
-> VALUES ('***','***','****','****',....);
显示当前数据库的所有表:
mysql>show tables;
导入数据库:(source命令)
mysql>use 数据库(先确定使用哪一个数据库来导入数据)
mysql>source d:\wcnc_db.sql (d:\wcnc_db.sql为数据库在本地的存放路径)
帮助:
mysql>help ******
mysql>\h *****
清除:
mysql>\c
退出
mysql>quit
bye
查询:
1.用分号隔开多条查询语句:
mysql>select version(); select now();
2.将mysql用作一个简单的计算器:
mysql>SELECT 7/4, (4+1)*5;
3.查询所有数据
mysql>SELECT * FROM table_name;
4.查询特殊行
mysql> SELECT * FROM table WHERE para='****';
组合条件查询(and 和 or,AND和OR可以混用,但AND比OR具有更高的优先级。)
5.查询特殊列:
mysql> SELECT id, para FROM table_name;
5.1.查询特殊列的同时筛选不一样的打印:
mysql> SELECT Distinct id FROM table_name;
6.查询结果分类排序
mysql>SELECT id, **** FROM pet ORDER BY ****;
mysql>SELECT id, **** FROM pet ORDER BY **** desc;(倒序排列)
[教程中特殊的例子]
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
7.查询并赋值
mysql>select year(now())- '2000' as age;
+------+
| age |
+------+
| 9 |
+------+
1 row in set (0.02 sec)
8.匹配:
8.1.字段以*开头的
mysql>select *****,**** from table_name where *** like '*%';
8.2.字段以*结尾的
mysql>select *****,**** from table_name where *** like '%*';
8.3.字段中包含*的
mysql>select *****,**** from table_name where *** like '%*%';
8.4.字段中包含n个字母的(一个'_'表示一个字符)
mysql>select title,user_id from channel_items where title like '___';
9.计数行
mysql>select count(*) from channel_items where title='home';
9.1.分组计数行 排序(COUNT( )和GROUP BY以各种方式分类你的数据)
mysql>select user_id, count(*) from channel_items group by user_id order by count(*) ;