mysql基本使用
在centos7安装
## mysql版本 mysql-5.7.18 # 下载mysql在源 wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm # 安装源 yum install -y epel-release-latest-7.noarch.rpm # 安装mysql yum install -y mysql-community-server # 启动 systemctl start mysqld # 获取mysql的默认密码 注：mysql5.7以后root的密码不在是为空，而是会随机生成一个在启动的时候存入在/var/log/mysqld.log中 grep 'temporary password' /var/log/mysqld.log # 登陆 mysql -uroot -p Enter password: ********* # 使用alter修改密码(密码必须有大小写数字特殊字符组成) mysql> alter user 'root'@'localhost' identified by 'Abc123@'; # 刷新 mysql> flush privileges; # 退出 mysql> exit;
mysql用户设置
# 设置一个用户可以'test',密码是 '123456',可以远程登录 grant all on students.* to 'test'@'%' identified by '123456'; # all 表示把所有的权限，也可以选择如，select,insert,update,delete,drop,create 等等这些权限单独的授予 # students.* 表示 ‘.’前面表示库，‘*’ 代表所有的库，‘.’ 后面的表示表名，‘*’ 代表前面库的所有的表 # ‘%’ 代表任何一个主机都可以使用'test'用户远程登陆这数据库，这里可以是一个IP，也可以是一个网段
mysql常用操作
1. 创建数据库
# 查看所有数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) # 创建数据库 testdb 并且支持utf8 mysql> create database testdb charset "utf8"; Query OK, 1 row affected (0.01 sec) # 查看数据支持的字符集 mysql> show create database testdb; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
2. 创建表
# 语法格式如下：
CREATE TABLE table_name (column_name column_type);
# 例子
mysql> create table students( # table 后跟表名
-> id INT NOT NULL AUTO_INCREMENT, # AUTO_INCREMENT 表示此为自增ID，即每增加一条数据不需要指定ID，就会自动增加1
-> name CHAR(32) NOT NULL, # NOT NULL 表示此字段在插入的时候不能为空
-> age INT NOT NULL, # INT 表示此字段只能存储INT类型的数据
-> date DATE, # DATE 为日期格式类型
-> PRIMARY KEY (stu_id) # PRIMARY KEY 是设置主键的语句，主键在表里是唯一的，不可设置多个字段为主键
-> );Query OK, 0 rows affected (0.04 sec)
# 查看库下的所有的表
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| students |
+------------------+
1 row in set (0.01 sec)
# 查看表结构
mysql> desc students;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| date | date | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
3. 增加数据 (行)
# 语法
insert into table_name (filed1,filed2...filedN) values(value1,value2...valueN);
mysql> insert into students(name,age,date) values('李明',32,'20160402');
Query OK, 1 row affected (0.00 sec)
4. 查询数据
# 语法 select [条件] from tablename where [filed] 条件； # ‘*’ 表示查找所有的字段 mysql> select * from students; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 1 | 张三 | 22 | 2017-05-02 | | 2 | 李明 | 32 | 2016-04-02 | | 3 | 王五 | 32 | 2016-09-02 | | 4 | 崔真 | 25 | 2017-10-02 | | 5 | 李蛋 | 26 | 2017-11-11 | +----+--------+-----+------------+ 5 rows in set (0.00 sec) ## 只查询某些字段 mysql> select id,name from students; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | | 2 | 李明 | | 3 | 王五 | | 4 | 崔真 | | 5 | 李蛋 | +----+--------+ 5 rows in set (0.00 sec) # 统计表中有多少行 coumt(*) mysql> select count(*) from students; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) ### where子句 ### # 注：使用主键来作为 WHERE 子句的条件查询是非常快速的。 # 查询 id 大于3的且显示所有字段的行 mysql> select * from students where id >3; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 4 | 崔真 | 25 | 2017-10-02 | | 5 | 李蛋 | 26 | 2017-11-11 | +----+--------+-----+------------+ 2 rows in set (0.00 sec) # 查询 age大于25且只显示name和age字段的行 mysql> select name,age from students where age > 25; +--------+-----+ | name | age | +--------+-----+ | 李明 | 32 | | 王五 | 32 | | 李蛋 | 26 | +--------+-----+ 3 rows in set (0.00 sec)
# limit 查询多少行 mysql> select * from students; +--------+--------+-----+------------+ | stu_id | name | age | date | +--------+--------+-----+------------+ | 1 | 张三 | 22 | 2017-05-02 | | 2 | 李明 | 32 | 2016-04-02 | | 4 | 崔真 | 25 | 2017-10-02 | | 5 | 李蛋 | 26 | 2017-11-11 | +--------+--------+-----+------------+ 4 rows in set (0.00 sec) mysql> select * from students limit 2; +--------+--------+-----+------------+ | stu_id | name | age | date | +--------+--------+-----+------------+ | 1 | 张三 | 22 | 2017-05-02 | | 2 | 李明 | 32 | 2016-04-02 | +--------+--------+-----+------------+ 2 rows in set (0.00 sec)
4.1. where 和使用 'like'查询
# where 查询中的 like 用法 selece file1,file2...fileN from table_name where fileN like 条件 ## 查询name字段末尾带有‘蛋’的字符 mysql> select * from students where name like '%蛋'; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 5 | 李蛋 | 26 | 2017-11-11 | +----+--------+-----+------------+ 1 row in set (0.00 sec) ## 查询date字段中末尾带有6的字符 mysql> select * from students where date like '%6'; Empty set, 1 warning (0.00 sec) ## 查询date字段中带有6的字符 mysql> select * from students where date like '%6%'; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 2 | 李明 | 32 | 2016-04-02 | | 3 | 王五 | 32 | 2016-09-02 | +----+--------+-----+------------+ 2 rows in set, 1 warning (0.00 sec) ### % 表示任一匹配一个或多个字符 ###
4.2. 查询结果排序 (order by)
## 查询结果排序 order by # 语法 desc是反序，asc是正序 select filed1,filed2,...,filedN from students order by filedN desc|asc; # 把查询的结果以 id 反序排序显示 mysql> select * from students where age like '%2' order by id desc; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 3 | 王五 | 32 | 2016-09-02 | | 2 | 李明 | 32 | 2016-04-02 | | 1 | 张三 | 22 | 2017-05-02 | +----+--------+-----+------------+ 2 rows in set (0.00 sec) # 以 id 反序显示 mysql> select * from students order by id desc; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 5 | 李蛋 | 26 | 2017-11-11 | | 4 | 崔真 | 25 | 2017-10-02 | | 3 | 王五 | 32 | 2016-09-02 | | 2 | 李明 | 32 | 2016-04-02 | | 1 | 张三 | 22 | 2017-05-02 | +----+--------+-----+------------+ 4 rows in set (0.00 sec) # 把查询的结果以 id 正序排序显示 mysql> select * from students where age like '%2' order by id asc; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 1 | 张三 | 22 | 2017-05-02 | | 2 | 李明 | 32 | 2016-04-02 | | 3 | 王五 | 32 | 2016-09-02 | +----+--------+-----+------------+ 2 rows in set (0.01 sec)
5. 修改数据 (行)
# 语法 update UPDATE table_name SET field1=new-value1, field2=new-value2 where 条件; # 把 id 为2 的行的age字段修改为18 mysql> select * from students where id = 2; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 2 | 李明 | 32 | 2016-04-02 | +----+--------+-----+------------+ 1 row in set (0.01 sec) mysql> update students set age=18 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students where id = 2; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 2 | 李明 | 18 | 2016-04-02 | +----+--------+-----+------------+ 1 row in set (0.00 sec) # 把 id 为2 的行的age和name字段修改 mysql> select * from students where id = 3; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 3 | 王五 | 32 | 2016-09-02 | +----+--------+-----+------------+ 1 row in set (0.00 sec) mysql> update students set name='天王',age=30 where id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students where id = 3; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 3 | 天王 | 30 | 2016-09-02 | +----+--------+-----+------------+ 1 row in set (0.00 sec)
6. 删除表数据(行)
# 删除表中的某一行 mysql> select * from students; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 1 | 张三 | 22 | 2017-05-02 | | 2 | 李明 | 32 | 2016-04-02 | | 3 | 王五 | 32 | 2016-09-02 | | 4 | 崔真 | 25 | 2017-10-02 | | 5 | 李蛋 | 26 | 2017-11-11 | +----+--------+-----+------------+ 5 rows in set (0.00 sec) mysql> delete from students where id = 3; Query OK, 1 row affected (0.00 sec) mysql> select * from students; +----+--------+-----+------------+ | id | name | age | date | +----+--------+-----+------------+ | 1 | 张三 | 22 | 2017-05-02 | | 2 | 李明 | 32 | 2016-04-02 | | 4 | 崔真 | 25 | 2017-10-02 | | 5 | 李蛋 | 26 | 2017-11-11 | +----+--------+-----+------------+ 4 rows in set (0.00 sec)
6.1 清空表中的所有数据
# 语法 truncate table_name; # 清空表数据 truncate mysql> select * from students; +----+---------------+-----+------------+ | id | name | age | date | +----+---------------+-----+------------+ | 1 | feixiang | 22 | 2017-05-11 | | 2 | cuixiang | 22 | 2017-03-12 | | 3 | zhangshan | 22 | 2016-06-12 | | 4 | liming | 25 | 2015-06-12 | | 5 | wangli | 20 | 2017-11-22 | | 6 | zhangshanfeng | 20 | 2017-01-02 | +----+---------------+-----+------------+ 6 rows in set (0.00 sec) # 清空表中所有的数据 mysql> truncate students; Query OK, 0 rows affected (0.01 sec) mysql> select * from students; Empty set (0.00 sec) ## 另一种清空表数据的方式 delete from table_name #效率上truncate比delete上快
7 增、删、改、表中的字段名
7.1
语法： alter table table_name add flied 类型描述; # 增加字段phone (add) mysql> desc students; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | age | int(11) | NO | | NULL | | | date | date | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 4 rows in set (0.02 sec) mysql> alter table students add phone int(11) not null; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | age | int(11) | NO | | NULL | | | date | date | YES | | NULL | | | phone | int(11) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
7.2
# 语法 alter table table_name drop 字段名; # 删除字段phone (drop) mysql> alter table students drop phone; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | age | int(11) | NO | | NULL | | | date | date | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
7.3
# 修改字段 (change) mysql> desc students; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | age | int(11) | NO | | NULL | | | date | date | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) # 语法 alter table table_name change filed_old filed_new 字段类型 not null; # 把id 字段名修改为 stu_id mysql> alter table students change id stu_id int(10) not null auto_increment; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | stu_id | int(10) | NO | PRI | NULL | auto_increment | | name | char(32) | NO | | NULL | | | age | int(11) | NO | | NULL | | | date | date | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
8. 外键
### 外键 ### mysql[testdb]> desc user; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | | NULL | | | password | varchar(64) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) ## 创建外键 mysql[testdb]> create table user_info( -> id int(10) not null auto_increment, -> u_id int(10) not null, -> name varchar(64) not null, -> age int(2) not null, -> addr varchar(64), -> primary key (id), -> foreign key (u_id) references user(id) # 创建外键，user_info的u_id字段与user表的id字段关联 -> ); Query OK, 0 rows affected (0.17 sec mysql[testdb]> desc user_info; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | u_id | int(10) | NO | MUL | NULL | | | name | varchar(64) | NO | | NULL | | | age | int(2) | NO | | NULL | | | addr | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) # 先往user_info表里写入数据 mysql[testdb]> insert into user_info(u_id,name,age,addr) values(1,'张三',25,'上海徐汇'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`user_info`, CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`)) ## 报错的意思，就是u_id的字段值在user表中是不存在的，所以要写入user_info数据是需要确认user里的数据是否存在，产生关联； # 往user表里写入数据 mysql[testdb]> insert into user(name,password) values('user01','123456'); Query OK, 1 row affected (0.00 sec) mysql[testdb]> insert into user(name,password) values('user02','654321'); Query OK, 1 row affected (0.00 sec) mysql[testdb]> select * from user; +----+--------+----------+ | id | name | password | +----+--------+----------+ | 1 | user01 | 123456 | | 2 | user02 | 654321 | +----+--------+----------+ 2 rows in set (0.00 sec) # 再往user_info表中写入数据 mysql[testdb]> insert into user_info(u_id,name,age,addr) values(1,'张三',25,'上海徐汇'); Query OK, 1 row affected (0.00 sec) mysql[testdb]> insert into user_info(u_id,name,age,addr) values(2,'李四',29,'北京东城'); Query OK, 1 row affected (0.00 sec) mysql[testdb]> select * from user_info; +----+------+--------+-----+--------------+ | id | u_id | name | age | addr | +----+------+--------+-----+--------------+ | 2 | 1 | 张三 | 25 | 上海徐汇 | | 3 | 2 | 李四 | 29 | 北京东城 | +----+------+--------+-----+--------------+ 2 rows in set (0.00 sec ## 使用外键查询 # 语法 select [filed1,filed2,...filedN] from table_name INNER JOIN table_name2 on table_name.filed = table_name2.filed [where tablename.id = 条件]; mysql[testdb]> select * from user inner join user_info on user.id=user_info.u_id; +----+--------+----------+----+------+--------+-----+--------------+ | id | name | password | id | u_id | name | age | addr | +----+--------+----------+----+------+--------+-----+--------------+ | 1 | user01 | 123456 | 2 | 1 | 张三 | 25 | 上海徐汇 | | 2 | user02 | 654321 | 3 | 2 | 李四 | 29 | 北京东城 | +----+--------+----------+----+------+--------+-----+--------------+ 2 rows in set (0.00 sec) # 查询特定的字段 mysql[testdb]> select user.id,user.name,user.password,user_info.name,user_info.age,user_info.addr from user inner join user_info on user.id=user_info.u_id; +----+--------+----------+--------+-----+--------------+ | id | name | password | name | age | addr | +----+--------+----------+--------+-----+--------------+ | 1 | user01 | 123456 | 张三 | 25 | 上海徐汇 | | 2 | user02 | 654321 | 李四 | 29 | 北京东城 | +----+--------+----------+--------+-----+--------------+ 2 rows in set (0.00 sec) # 支持where子句查询 select user.id,user.name,user.password,user_info.name,user_info.age,user_info.addr from user inner join user_info on user.id=user_info.u_id where user.id =2; +----+--------+----------+--------+-----+--------------+ | id | name | password | name | age | addr | +----+--------+----------+--------+-----+--------------+ | 2 | user02 | 654321 | 李四 | 29 | 北京东城 | +----+--------+----------+--------+-----+--------------+ 1 row in set (0.00 sec)
# 外键用于关联两个表 # 两张表的数据需要会保持一致性 # 删除时，只有先删除关联的表中的数据，才能删除被关联的表中的数据 # user表是被user_info关联的表 mysql[testdb]> select * from user; +----+--------+----------+ | id | name | password | +----+--------+----------+ | 1 | user01 | 123456 | | 2 | user02 | 654321 | +----+--------+----------+ 2 rows in set (0.00 sec) mysql[testdb]> select * from user_info; +----+------+--------+-----+--------------+ | id | u_id | name | age | addr | +----+------+--------+-----+--------------+ | 2 | 1 | 张三 | 25 | 上海徐汇 | | 3 | 2 | 李四 | 29 | 北京东城 | +----+------+--------+-----+--------------+ 2 rows in set (0.00 sec) # 删除关联表中的数据 mysql[testdb]> delete from user_info where id=2; Query OK, 1 row affected (0.01 sec) mysql[testdb]> select * from user_info; +----+------+--------+-----+--------------+ | id | u_id | name | age | addr | +----+------+--------+-----+--------------+ | 3 | 2 | 李四 | 29 | 北京东城 | +----+------+--------+-----+--------------+ 1 row in set (0.00 sec) # 删除被关联表中的数据 mysql[testdb]> delete from user where id = 2; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`user_info`, CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`)) # 因为 user表中的id字段值为2在 # 因为user表中id为1的值所对应的user_info表中的值已经被删除了，所以可以删除 mysql[testdb]> delete from user where id = 1; Query OK, 1 row affected (0.01 sec)
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | COURSE | | SC | | STUDENT | +----------------+ 3 rows in set (0.00 sec) mysql> select * from STUDENT; +-------+----------+------+------+-------+ | Sno | Sname | Ssex | Sage | Sdept | +-------+----------+------+------+-------+ | 95001 | liyong | f | 20 | CS | | 95002 | liu | m | 19 | IS | | 95003 | wangemin | m | 18 | MA | | 95004 | zhangli | f | 19 | IS | +-------+----------+------+------+-------+ 4 rows in set (0.00 sec) mysql> select * from sc; ERROR 1146 (42S02): Table 'test.sc' doesn't exist mysql> select * from STUDENT; +-------+----------+------+------+-------+ | Sno | Sname | Ssex | Sage | Sdept | +-------+----------+------+------+-------+ | 95001 | liyong | f | 20 | CS | | 95002 | liu | m | 19 | IS | | 95003 | wangemin | m | 18 | MA | | 95004 | zhangli | f | 19 | IS | +-------+----------+------+------+-------+ 4 rows in set (0.00 sec) mysql> select * from SC; +-------+-----+-------+ | Sno | Cno | Grade | +-------+-----+-------+ | 95001 | 1 | 92 | | 95001 | 2 | 85 | | 95001 | 3 | 88 | | 95002 | 2 | 90 | | 95002 | 3 | 80 | +-------+-----+-------+ 5 rows in set (0.00 sec) mysql> select * from COURSE; +-----+--------------+------+--------+ | Cno | Cname | Cpno | Sredit | +-----+--------------+------+--------+ | 1 | shujuku | 5 | 4 | | 2 | shuxue | NULL | 2 | | 3 | xinxixitiong | 1 | 4 | | 4 | caozuoxitong | 6 | 3 | | 5 | shujujiegou | 7 | 4 | | 6 | shujuchuli | NULL | 2 | | 7 | Pascalyuyan | 6 | 4 | +-----+--------------+------+--------+ 7 rows in set (0.00 sec) # 查询所有姓'liu'的学生的姓名，学号和性别并且按学号倒序(ASC正序，DESC反序) mysql> select Sname,Ssex,Sno from STUDENT where Sname like 'liu%' order by Sno DESC; +-------+------+-------+ | Sname | Ssex | Sno | +-------+------+-------+ | liu | m | 95002 | +-------+------+-------+ 1 row in set (0.00 sec) # 查询选修了3门以上的课程的学生学号，并且显示平均分 mysql> select AVG(Grade),Sno,count(*) as count from SC group by Sno having count>2; +------------+-------+-------+ | AVG(Grade) | Sno | count | +------------+-------+-------+ | 88.3333 | 95001 | 3 | +------------+-------+-------+ 1 row in set (0.00 sec) # 查询选修了程序名为“xinxixitiong”的学生学号和姓名 mysql> select Cname,STUDENT.Sno,STUDENT.Sname from COURSE inner join SC on COURSE.Cno=SC.Cno inner join STUDENT on SC.Sno=STUDENT.Sno where Cname='xinxixitiong'; +--------------+-------+--------+ | Cname | Sno | Sname | +--------------+-------+--------+ | xinxixitiong | 95001 | liyong | | xinxixitiong | 95002 | liu | +--------------+-------+--------+ 2 rows in set (0.00 sec)
二、事务
1.
- begin; # 开始事务操作
- 执行sql写操作；
- commit; # 提交事务
- rollback; # 回滚事务
三、django sqlalchemy
1. 在models.py中
class Server_Info(models.Model): """server information""" ip = models.GenericIPAddressField(max_length=15, db_index=True) port = models.IntegerField(max_length=5) username = models.CharField(max_length=10) password = models.CharField(max_length=20) class Meta: db_table = "server_information"
2. 在数据库中生效
python manage.py makemigrations python manage.py migrate
3. 基本使用
models.TB.objects.create(name='name') // 创建数据 models.TB.objects.all() // 获取所有数据 models.TB.objects.all()[1:2] // 切片 models.TB.objects.values() // 获取值 models.TB.objects.values_list // 获取值列表 models.TB.objects.filter(id=1) // 查找 models.TB.objects.filter(id=1).count() // 查找后求合 models.TB.objects.fileter(id=1).delete() // 删除 models.TB.objects.fileter(id=1).update(name='rename') // 修改 models.TB.objects.filter(id__gt=1) // 查找 models.TB.objects.filter(id__lt=1) models.TB.objects.filter(id__gte=1) models.TB.objects.filter(id__lte=1)
浙公网安备 33010602011771号