连接数据库 mysql -u root -p
C:\Users\Administrator>mysql -u root -p
Enter password: ******
mysql> exit;#退出命令
展示数据库 show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| java2304 |
| kaijiu |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
创建数据库 create database 数据库名;
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| java2304 |
| kaijiu |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
删除数据库 drop database 数据库名;
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| java2304 |
| kaijiu |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
选中数据库 use 数据库名;
mysql> use test;
Database changed
查看当前数据库下面有多少表 show tables;
mysql> show tables;
Empty set (0.00 sec)
创建表 1
#语法格式: create table 表名字 (字段1 数据类型,字段2 数据类型,…);
mysql> create table testtable1 (id int,name varchar(32),sex int);
Query OK, 0 rows affected (0.04 sec)、
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| testtable1 |
+----------------+
1 row in set (0.00 sec)
查看表的内容 desc 表名;
mysql> desc testtable1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除表 drop table 表名;
mysql> drop table testtable1;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
Empty set (0.00 sec)
创建表2
mysql> create table person1 (
-> id int,
-> name varchar(32),
-> age int
-> );
查看创建数据库时的信息 show create database 数据库名;
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database
|
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
查看创建表时的信息 show create table 表名;
mysql> show create table person;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`id` int(11) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改创建好的表的字段 alter
删除age字段 alter table 表名 drop 字段;
mysql> alter table person drop age;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
添加age字段 alter table 表名 add 字段 数据类型;
mysql> alter table person add age int;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
在指定的字段的后面添加一个字段 name的字段的后面添加一个字段 name的字段的后面添加一个sex字段
语法格式: alter table 表名 add 字段 数据类型 after 字段;
mysql> alter table person add sex boolean after name;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改一个字段的数据类型 name数据类型 varchar 改为char类型
语法格式: alter table 表名 modify 字段 修改后的数据类型;
mysql> alter table person modify name char(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#varchar 和char的区别:
#varchar 和char 都是用来字符串类型的数据的
#char(32) 定长的 存入数据的时候,不管你的数据多长,都是开辟32长度的
#varchar(32) 变长的 存入数据的额时候,随着你的数据长短,而开辟长度
字段和数据类型一起修改 alter table person change sex gender int;
mysql> alter table person change sex gender int;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
添加一个字段
//数据类型 text 文本 字符串 不限制大小
mysql> alter table person add info text;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| info | text | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#加薪资字段 小数
# float(8,2);
# double(8,2);
# decimal(8,2);
#最大长度是8位 其中小数占2位 999999.99
mysql> alter table person add salary decimal(8,2);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| info | text | YES | | NULL | |
| salary | decimal(8,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
表字段的数据类型:
int
tinyint(0) boolean
varchar
char
text
double
float
decimal
往表中添加数据1语法格式:
INSERT INTO 表名称 VALUES (值1, 值2,....)
mysql> insert into person values(1,"灰太狼",1,12,"草原发明家",6.6);
Query OK, 1 row affected (0.02 sec)
查看表中的所有内容 select * from 表名;
mysql> select * from person;
+------+-----------+--------+------+-----------------+--------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+--------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
+------+-----------+--------+------+-----------------+--------+
1 row in set (0.00 sec)
往表中添加数据2语法格式:
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)(开发中用的)
mysql> insert into person (id,name,salary)values(4,"美太狼",10000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from person;
+------+-----------+--------+------+-----------------+----------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+----------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
| 2 | 美羊羊 | NULL | NULL | NULL | 66.66 |
| 3 | 小辉辉 | NULL | NULL | NULL | 8.00 |
| 4 | 美太狼 | NULL | NULL | NULL | 10000.00 |
+------+-----------+--------+------+-----------------+----------+
4 rows in set (0.00 sec)
//一次添加多个数据
mysql> insert into person (id,name,salary)values(
-> 2,"美羊羊",66.66),
-> (3,"小辉辉",8);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from person;
+------+-----------+--------+------+-----------------+--------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+--------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
| 2 | 美羊羊 | NULL | NULL | NULL | 66.66 |
| 3 | 小辉辉 | NULL | NULL | NULL | 8.00 |
+------+-----------+--------+------+-----------------+--------+
3 rows in set (0.00 sec)
删除数据
DELETE FROM 表名称 WHERE 列名称 = 值
mysql> select * from person;
+------+-----------+--------+------+-----------------+----------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+----------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
| 2 | 美羊羊 | NULL | NULL | NULL | 66.66 |
| 3 | 小辉辉 | NULL | NULL | NULL | 8.00 |
| 4 | 美太狼 | NULL | NULL | NULL | 10000.00 |
| 5 | 美太狼 | NULL | NULL | NULL | 10000.00 |
| 6 | 美太狼 | NULL | NULL | NULL | 10000.00 |
| 7 | 美太狼 | NULL | NULL | NULL | 10000.00 |
+------+-----------+--------+------+-----------------+----------+
7 rows in set (0.00 sec)
mysql> delete from person where id=7;
Query OK, 1 row affected (0.02 sec)
mysql> select * from person;
+------+-----------+--------+------+-----------------+----------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+----------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
| 2 | 美羊羊 | NULL | NULL | NULL | 66.66 |
| 3 | 小辉辉 | NULL | NULL | NULL | 8.00 |
| 4 | 美太狼 | NULL | NULL | NULL | 10000.00 |
| 5 | 美太狼 | NULL | NULL | NULL | 10000.00 |
| 6 | 美太狼 | NULL | NULL | NULL | 10000.00 |
+------+-----------+--------+------+-----------------+----------+
6 rows in set (0.00 sec)
修改数据 语法格式
UPDATE 表名称 SET 列名称 = 新值,列名称=新值,... WHERE 列名称 = 某值
mysql> update person set name="懒羊羊",gender=1,age=6,info="爱吃",salary=888 where id=6;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from person;
+------+-----------+--------+------+-----------------+--------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+--------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
| 2 | 羊浩 | 0 | 18 | 屁王 | 9.90 |
| 3 | 马羊羊 | 1 | 8 | 终极篮球 | 998.00 |
| 4 | 沸羊羊 | 1 | 34 | 终极篮球 | 3.00 |
| 5 | 美洋洋 | 0 | 3 | rap | 88.00 |
| 6 | 懒羊羊 | 1 | 6 | 爱吃 | 888.00 |
+------+-----------+--------+------+-----------------+--------+
6 rows in set (0.00 sec)
事务处理
#1.开启事务,默认是关闭的 换句话说其实就是将sql语句自动提交关闭掉!!!
#set autocommit = 0;
mysql> update person set salary=salary+100 where id =4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from person;
+------+-----------+--------+------+-----------------+--------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-----------------+--------+
| 1 | 灰太狼 | 1 | 12 | 草原发明家 | 6.60 |
| 2 | 羊浩 | 0 | 18 | 屁王 | 9.90 |
| 3 | 马羊羊 | 1 | 8 | 终极篮球 | 998.00 |
| 4 | 沸羊羊 | 1 | 34 | 终极篮球 | 103.00 |
| 5 | 美洋洋 | 0 | 3 | rap | 88.00 |
| 6 | 懒羊羊 | 1 | 6 | 爱吃 | 888.00 |
+------+-----------+--------+------+-----------------+--------+
6 rows in set (0.00 sec)