MySql常用语法
0. 创建用户
:CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
1. MySql安装后设置密码
- step 1
SET PASSWORD = PASSWORD('your new password');
- step 2
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
- step 3
flush privileges;
2. root账户密码重置
- 跳过密码
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
- 登陆mysql
sudo /usr/local/mysql/bin/mysql -u root
- 密码重置
UPDATE mysql.user SET authentication_string=PASSWORD(新密码) WHERE User=root;
- 刷新
FLUSH PRIVILEGES;
- 退出
\q;
3. SHOW命令
- 查看所有表
SHOW TABLES FROM ddicar_v3;
- 查看所有数据库的所有表
SHOW TABLES ;
- 查看表的详细状态
SHOW TABLE STATUS [FROM db_name];
- 查看表的所有字段
SHOW COLUMNS FROM users;
- 查看表的所有字段详细信息
SHOW FULL COLUMNS FROM users;
- 查看表的某个字段详细信息
show columns from tabler_name where field = 'column_name';
- 查看关于timeout的配置
show global variables like '%timeout%';
- 查看数据库使用情况
SHOW PROCESSLIST;
4. SELECT命令
- 查看当前所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
- 查看当前使用数据库
SELECT database();
- 查看当前用户
select user();
5. 使用grant命令创建用户(赋权命令)
- 创建并授权
database:要授权的数据库,所有可用*
代替
table:要授权的表,所有可用*
代替
permission:要授权的权限,select
(查),insert(增),update(改),delete(删)
grant permission on database.table to 'millions'@'localhost' identified by 'password';
- 刷新系统权限表
flush privileges;
- 例1:增加一个test2用户,密码为123456,只能在192.168.2.12上登录,并对数据库student有查询,增加,修改和删除的功能。需要在mysql的root用户下进行
mysql>grant select,insert,update,delete on student.* to test2@192.168.2.12 identified by “123456″;
mysql>flush privileges;
- 例2:授所有权限
grant all privileges on *.* to joe@localhost identified by '1';
flush privileges;
6. INSERT 命令
- 插入一条数据
value可以写null
INSERT INTO table_name(colum_name1,colum_name2) values(value1,value2)
7. UPDATE命令
- 更新一条数据
UPDATE table_name set colum_name WHERE colum = value;
8. UPDATE命令
- 删除一条数据
DELETE FROM table_name WHERE colum=value;
9.查看和设置数据传输大小
- max_allowed_packet:指代mysql服务器端和客户端在一次传送数据包的过程当中数据包的大小
- 查看
mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 4194304 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
- 设置(这个值过大过小都不好,建议4M)
set global max_allowed_packet = 4194304;
如果错误,请指正或微信留言yytlxb123