Mysql的使用,常用的SQL语句
- root用户登录mysql:
修改root 密码:
alter user 'root'@'localhost' identified by'new_pwd';
或:
update mysql.user set authentication_string = password('你的密码') where user='root';
flush privileges;
并创建一个普通用户
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.7.20 MySQL Community Server (GPL)
...................................
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
........................
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on test.* to 'user'@'localhost' identified by'new_pwd_for_user';
Query OK, 0 rows affected, 1 warning (0.00 sec)
通过命令grant all on test.* to '你的账户名'@'ip' identified by'你的密码';
创建创建一个账户,并把test库下所有表,所有权限授权给该账户, IP= localhost 意思只允许本地访问,%意思为允许任何一个地方访问,填写其他ip,就是允许某个远程Ip访问。
-
创建一个新数据库:my_test 并支持中文
create database my_test charset utf8; -
转移到该数据库下:use db
use my_test -
创建一个表:mytable 两个字段 name register_date
create table mytable(
id int not null auto_increment primary key,
name char(32) not null,
register_date DATE not null); -
向表中插入一条数据:insert into id字段已设置为主键自增,不用管
insert into mytable(name, register_date) values('lina', '2018-01-17');
mysql> create database my_test;
Query OK, 1 row affected (0.41 sec)
mysql> use my_test;
Database changed
mysql> create table mytable(
-> id int not null auto_increment primary key,
-> name char(32) not null,
-> register_date DATE not null);
Query OK, 0 rows affected (5.37 sec)
mysql> insert into mytable(name, register_date) values('lina', '2018-01-17');
Query OK, 1 row affected (0.58 sec)
-
查询数据:select
select * from table_name; 后面可以加条件语句 -
删数据:delete
delete from tb_name where condition;
如:delete from mytable where id=1 and name='alex' and id>2;
mysql> select * from mytable;
+----+-------+---------------+
| id | name | register_date |
+----+-------+---------------+
| 1 | lina | 2018-01-17 |
| 2 | alex | 2018-08-17 |
| 3 | david | 2015-08-17 |
| 4 | sevn | 2218-11-17 |
+----+-------+---------------+
4 rows in set (0.14 sec)
mysql> delete from mytable where name='lina' and name='alex';;
Query OK, 2 rows affected (0.29 sec)
mysql> select * from mytable;
+----+-------+---------------+
| id | name | register_date |
+----+-------+---------------+
| 3 | david | 2015-08-17 |
| 4 | sevn | 2218-11-17 |
+----+-------+---------------+
2 rows in set (0.00 sec)
mysql> delete from mytable where id>2;
-
删字段:drop
alter table mytable drop column register_date; -
查看表结构:desc
desc mytable
mysql> alter table mytable drop column register_date;
Query OK, 0 rows affected (2.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytable;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.22 sec)
- 删表:drop
drop table mytable;
查看库下所有表:show tables;
mysql> drop table mytable;
Query OK, 0 rows affected (0.30 sec)
mysql> show tables;
Empty set (0.04 sec)
- 删库:drop
drop database my_test;
小结:
只有删数据使用:delete
其他所有都用:drop
删库:drop db;
删表:drop tb_name;
删字段:alter table tb_name drop column name;