连接到mysql
mysql -h host -u user -p
退出
quit
查询版本号,当前日期,时间, 现在时刻,用户, 哪个数据库等 一般在mysql中以";“结尾的,多条语句用一个分号间隔开
select version(), current_date(), current_time(), sin(pi()), (20/4), now(), user(), database();
如果正在执行过程中想取消输入的命令 输入\c 取消;
select
user(),
\c
提示符 | 含义 |
mysql> | 准备好接收新命令 |
-> | 等待多行命令的下一行 |
'> | 等待下一行,等待以单引号(“'”)开始的字符串的结束 |
"> | 等待下一行,等待以双引号(“"”)开始的字符串的结束 |
`> | 等待下一行,等待以反斜点(‘`’)开始的识别符的结束 |
/*> | 等待下一行,等待以/*开始的注释的结束 |
显示数据库
show databases;
使用数据库 如果存在 尝试访问它。必须单行给出
use test
创建数据库 数据库名称是区分大小写的 (不像SQL关键字)
create database mydatabase;
显示表
show tables;
查看表的定义
desc mytable;
describe mytable;
从txt中导入到mysql中
表的定义
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(15) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
txt中的文件 (用tab键间隔开)
6 wu 22
7 zheng 24
8 wang 25
txt导入到mysql中
load data local infile 'c:/mysql.txt' into table user;
lines terminated by '\r\n'
插入一条新数据
insert into user values (1,'tom',23);
查询
一般查询语句
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
从一个表中索引所有记录
select * from user;
条件查询
select * from user where id = 1;
select name,age from user wherr id = 1;
select * from user where name = 'zhao' and age = 23;
select * from user where name = 'zhao' or age = 23;
select * from user where (name = 'zhao' and age = 23) or (name = 'wang' and age = 24);
select distinct age from user;
模糊查找
like: % 全部匹配 _匹配一个
regexp 匹配正则表达式 ^ : 开始 , $ : 结尾, . : 匹配任何单个字符
select * from user where name like 'z%';
select * from user where name like 'z____';
select * from user where name regexp '^s';
查询排序 升序(asc) 和 降序(desc)
select * from user order by age asc;
select * from user order by age desc;
查询 计数行
select count(*) from user;
select age, count(*) from user group by age;
两种方法清空表内容
1)
delete form testTable;
2)
把mysql.txt清空在导入到mysql中
更行表的记录
update user set name = 'jerry' where id = 1;
删除表的记录
delete from user where id = 1;
备份数据库:
mysqldump -uroot -p123456 database>backup20140101.sql
恢复数据库(database_name如果没有,是需要创建的,而且与备份中的数据库名一致才能导入)
mysqladmin -uroot -p123456 drop database_name
mysqladmin -uroot -p123456 create database_name
mysql -uroot -p123456 database_name<backup20140101.sql
mysqladmin 设置root密码
mysqladmin -u root password "XXXXXXX"
添加用户 wang和li 密码分别是"123456", "654321"。
create user
`wang`@`localhost` identified by `123456`,
`li`@`localhost` identified by `654321`;
删除用户
drop user li@localhost;
修改用户名
rename user `li`@`localhost` to `zhang`@`localhost`;
修改密码
set password for `root`@`localhost` = password('123456');
授予权限
grant select,update,delete on ta to cao@localhost identified by '123456' with grant option;
收回权限
revoke select on ta from li@localhost;
revoke all privileges, grant option from wang@localhost;
开通远程登录权限
grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'password' with grant option;
flush privileges;
改表设置远程登录
use mysql;
update user set host='%' where user = 'root';
select host, user from user;
开通后可以远程登录
mysql -h127.0.0.1 -uroot -ppassword
查询
create database if not exists demo;
use demo;
create table if not exists ta(
id int primary key,
name varchar(25) not null
);
insert into ta(id, name) values
(1, 'a1'),
(2, 'a2'),
(3, 'a3');
create table if not exists tb(
id int primary key,
name varchar(25) not null
);
insert into tb(id, name) values
(1, 'b1'),
(2, 'b2'),
(4, 'b4');
select * from ta as A inner join tb as B on A.id = B.id;
id, name, id, name
1, a1, 1, b1
2, a2, 2, b2
select * from ta left join tb on ta.id = tb.id;
id, name, id, name
1, a1, 1, b1
2, a2, 2, b2
3, a3, null, null