2018-08-06数据库命令
1.show databases; //显示所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
2.mysql> use mysql; //切换当前数据库
Database changed
3.mysql> show tables; //显示当前数据库所有表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
4.mysql> desc user; //显示当前表的所有字段
+------------------------+-----------------------------------+------+-----+-----
------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----
------------------+-------+
| Host | char(60) | NO | PRI |
| |
| User | char(32) | NO | PRI |
| |
| Select_priv | enum('N','Y') | NO | | N
| |
| Insert_priv | enum('N','Y') | NO | | N
| |
| Update_priv | enum('N','Y') | NO | | N
.....................
5. mysql> select Host,User from user; //查询表记录
+-----------+---------------+
| Host | User |
+-----------+---------------+
| % | test |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
6.mysql -h IP地址 -u test -p //登录数据库
mysql -h92.168.37.17 -u test -p
>mysql
7.GRANT all privileges ON *.* TO ‘test’@’%’ IDENTIFIED BY ‘123456’;
创建一个访问mysql数据库账号
账号名:test
密码:123456
权限:所有数据库所有表
GRANT all privileges ON 数据库.表 TO ‘test’@’%’ IDENTIFIED BY ‘123456’;
1.mysql> create database wisezone1803; //创建数据库wisezone1803
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wisezone1803 |
+--------------------+
1.1删除数据库
DROP DATABASE 数据库名;
2. 创建学生表 student
CREATE TABLE student
(
number int(4) COMMENT '学号',
name varchar(20) COMMENT '姓名',
age int(3) COMMENT '年龄',
);
CREATE TABLE coures(
id int(4) COMMENT '课程编号',
name varchar(20) COMMENT '课程名称',
time int(3) COMMENT '课时'
);
2.1删除表
mysql> DROP TABLE coures;
3.插入一条记录
mysql> INSERT INTO student (number,name,age)VALUES(1001,'小明',23);
4.查询语句
SELECT * FROM student;
+--------+--------+------+
| number | name | age |
+--------+--------+------+
| 1001 | 小明 | 23 |
+--------+--------+------+
SELECT number,name FROM student;
+--------+--------+
| number | name |
+--------+--------+
| 1001 | 小明 |
| 1002 | 张三 |
+--------+--------+
5. 修改记录
mysql> UPDATE student SET name='张三' WHERE number=1001;
mysql> SELECT number,name,age FROM student;
+--------+--------+------+
| number | name | age |
+--------+--------+------+
| 1001 | 张三 | 23 |
| 1002 | 李四 | 24 |
+--------+--------+------+
6.删除记录
mysql> DELETE FROM student WHERE name='李四';
