显示数据位数32还是64:
mysql -V / mysql --version
登录数据库:
mysql -u root -p
输入登录密码:
创建数据库:
create DATABASE RUNOOB;
例子:
mysql> create database tmysql;
Query OK, 1 row affected (0.02 sec)
显示所有数据库:
show databases;
例子:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runoob |
| sakila |
| sys |
| tmysql |
| world |
删除数据库:
drop database RUNOOB;
例子:
mysql> drop database tmysql;
Query OK, 1 row affected (0.09 sec)
使用数据库:
use RUNOOB;
例子:
mysql> use tmysql;
Database changed
显示当前使用数据;
select database();
创建表
mysql> create table student(
-> stuid INT NOT NULL,
-> stuname VARCHAR(20) NOT NULL,
-> stusex VARCHAR(4),
-> PRIMARY KEY(stuid)) ENGINE=InnoDB DEFAULT CHARSET = utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)
显示所有表
show tables;
例如:
mysql> show tables;
+------------------+
| Tables_in_tmysql |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
显示表的详情:
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuid | int | NO | PRI | NULL | |
| stuname | varchar(20) | NO | | NULL | |
| stusex | varchar(4) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
显示表的创建sql语句:
mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`stuid` int NOT NULL,
`stuname` varchar(20) NOT NULL,
`stusex` varchar(4) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
修改表名:
mysql> alter table student rename student1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_tmysql |
+------------------+
| student1 |
+------------------+
1 row in set (0.02 sec)
更改表的属性:
mysql> alter table student modify stusex varchar(6);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改表的属性名:
mysql> alter table student change stuid stuId int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuId | int | NO | PRI | NULL | |
| stuname | varchar(20) | NO | | NULL | |
| stusex | varchar(6) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
添加表的属性:
mysql> alter table student add studept varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuId | int | NO | PRI | NULL | |
| stuname | varchar(20) | NO | | NULL | |
| stusex | varchar(6) | YES | | NULL | |
| studept | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
删除表的属性:
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuid | int | NO | PRI | NULL | |
| stuname | varchar(20) | NO | | NULL | |
| stusex | varchar(6) | YES | | NULL | |
| studept | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> alter table student drop studept;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除表:
drop table student;
例子:
mysql> drop table student;
Query OK, 0 rows affected (0.03 sec)
插入数据:
mysql> INSERT INTO stu
-> (stuid ,stdname)
-> VALUES
-> (20213,'小王');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu (stuid, stdname) values (20212,'小刘');
Query OK, 1 row affected (0.01 sec)
查询表:
mysql> SELECT * FROM stu;
+-------+---------+
| stuid | stdname |
+-------+---------+
| 20211 | 小明 |
| 20212 | 小刘 |
| 20213 | 小王 |
+-------+---------+
3 rows in set (0.00 sec)
mysql> select * from stu where stuid = 20211;
+-------+---------+
| stuid | stdname |
+-------+---------+
| 20211 | 小明 |
+-------+---------+
1 row in set (0.00 sec)
mysql> select * from stu where stuid between 20211 and 20213;
+-------+---------+
| stuid | stdname |
+-------+---------+
| 20211 | 小明 |
| 20212 | 小刘 |
| 20213 | 小王 |
+-------+---------+
3 rows in set (0.01 sec)
mysql> select * from student;
+-------+---------+--------+---------+
| stuId | stuname | stusex | studept |
+-------+---------+--------+---------+
| 1 | 小明 | 男 | cs |
| 2 | 小李 | 男 | cf |
| 3 | 小赵 | 男 | cf |
+-------+---------+--------+---------+
3 rows in set (0.01 sec)
mysql> select * from student where stuId between 1 and 3 and studept ='cf';
+-------+---------+--------+---------+
| stuId | stuname | stusex | studept |
+-------+---------+--------+---------+
| 2 | 小李 | 男 | cf |
| 3 | 小赵 | 男 | cf |
+-------+---------+--------+---------+
2 rows in set (0.01 sec)
mysql> select * from student where studept in ('cf','cs');
+-------+---------+--------+---------+
| stuId | stuname | stusex | studept |
+-------+---------+--------+---------+
| 1 | 小明 | 男 | cs |
| 2 | 小李 | 男 | cf |
| 3 | 小赵 | 男 | cf |
+-------+---------+--------+---------+
3 rows in set (0.00 sec)
修改数据表项:
mysql> update student set studept = 'cf' where stuId =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+-------+---------+--------+---------+
| stuId | stuname | stusex | studept |
+-------+---------+--------+---------+
| 1 | 小明 | 男 | cf |
| 2 | 小李 | 男 | cf |
| 3 | 小赵 | 男 | dq |
| 4 | we*user | 男 | jk |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)
删除数据表项:
mysql> delete from student where stuId =1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+-------+---------+--------+---------+
| stuId | stuname | stusex | studept |
+-------+---------+--------+---------+
| 2 | 小李 | 男 | cf |
| 3 | 小赵 | 男 | dq |
| 4 | we*user | 男 | jk |
+-------+---------+--------+---------+
3 rows in set (0.00 sec)
like语句:
mysql> select * from student where studept like 'c%';
+-------+---------+--------+---------+
| stuId | stuname | stusex | studept |
+-------+---------+--------+---------+
| 2 | 小李 | 男 | cf |
+-------+---------+--------+---------+
1 row in set (0.00 sec)
连接、左连接、右链接:
mysql> select stuId,stuname,student.studept,deptId,deptName from student inner join dept on student.studept = dept.deptName;
+-------+---------+---------+--------+----------+
| stuId | stuname | studept | deptId | deptName |
+-------+---------+---------+--------+----------+
| 2 | 小李 | cf | 3 | cf |
+-------+---------+---------+--------+----------+
1 row in set (0.00 sec)
mysql> select stuId,stuname,student.studept,deptId,deptName from student left join dept on student.studept = dept.deptName;
+-------+---------+---------+--------+----------+
| stuId | stuname | studept | deptId | deptName |
+-------+---------+---------+--------+----------+
| 2 | 小李 | cf | 3 | cf |
| 3 | 小赵 | dq | NULL | NULL |
| 4 | we*user | jk | NULL | NULL |
+-------+---------+---------+--------+----------+
3 rows in set (0.00 sec)
mysql> select stuId,stuname,student.studept,deptId,deptName from student right join dept on student.studept = dept.deptName;
+-------+---------+---------+--------+----------+
| stuId | stuname | studept | deptId | deptName |
+-------+---------+---------+--------+----------+
| NULL | NULL | NULL | 1 | cs |
| NULL | NULL | NULL | 2 | gc |
| 2 | 小李 | cf | 3 | cf |
+-------+---------+---------+--------+----------+
3 rows in set (0.00 sec)
NULL或者NOT NULL:
mysql> select * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| 菜鸟教程 | NULL |
| Google | NULL |
| FK | 20 |
+---------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from runoob_test_tbl where runoob_count is null;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程 | NULL |
| Google | NULL |
+---------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from runoob_test_tbl where runoob_count is not null;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| FK | 20 |
+---------------+--------------+
2 rows in set (0.00 sec)
mysql> select runoob_author,ifnull(runoob_count,0) from runoob_test_tbl;
+---------------+------------------------+
| runoob_author | ifnull(runoob_count,0) |
+---------------+------------------------+
| RUNOOB | 20 |
| 菜鸟教程 | 0 |
| Google | 0 |
| FK | 20 |
+---------------+------------------------+
4 rows in set (0.00 sec)
mysql> select runoob_author,ifnull(runoob_count,0) as count from runoob_test_tbl;
+---------------+-------+
| runoob_author | count |
+---------------+-------+
| RUNOOB | 20 |
| 菜鸟教程 | 0 |
| Google | 0 |
| FK | 20 |
+---------------+-------+
4 rows in set (0.00 sec)
MYSQL 事务处理主要有两种方法:
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
mysql> select * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| 菜鸟教程 | NULL |
| Google | NULL |
| FK | 20 |
+---------------+--------------+
4 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_test_tbl (runoob_author,runoob_count) values ('学习网站',30);
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| 菜鸟教程 | NULL |
| Google | NULL |
| FK | 20 |
+---------------+--------------+
4 rows in set (0.00 sec)