mysql常用操作

数据库的操作:
DDL(Data D dfinition Languages) 数据定义语言 create/drop/alter
DML(Data Manipulation Language) 数据操作语言 insert/delete/select
CRUD : create read update delete
DCL(Data Control Language) 数据控追语句 grant

查看数据库:
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+

创建数据库:
mysql> create database if not exists python_ai_basic default charset utf8 collate utf8_general_ci;
mysql> show databases;
default charset utf8:数据库默认编码
utf8_general_ci:数据库的排序规则

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| python_ai_basic |
+--------------------+

删除数据库:
drop database python_ai_basic;

三层编码:
1.数据库层面的编码
2.表层面的编码
3.字段层面的编码

查看数据库的编码:
show create database python_ai_basic;
+-----------------+--------------------------------------------------------------------------+
| Database | Create Database |
+-----------------+--------------------------------------------------------------------------+
| python_ai_basic | CREATE DATABASE `python_ai_basic` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------------+--------------------------------------------------------------------------+

使用数据库:
use python_ai_basic;
mysql> use python_ai_basic;
Database changed

创建表:
create table customers(
id int not null auto_increment primary key ,
name char(20) not null,
address varchar(50),
city varchar(50) null,
age int null,
love varchar(50) default 'my-data'
)engine=InnoDB default charset=utf8;
1.auto_increment:表示自增
2.char:字符的静态扩充 所占空间是固定的
3.varchar:字符的动态扩充 所占的空间是动态的
4. engine=InnoDB 表示表的存储引擎是InnoDB
5.charset=utf8 表示表的字符编码是utf8
查看表结构:
mysql> desc customers;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
| city | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| love | varchar(50) | YES | | my-data | |
+---------+-------------+------+-----+---------+----------------+

查看创建表的语句:
mysql> show create table customers;
customers | CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`address` varchar(50) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`love` varchar(50) DEFAULT 'my-data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

删除表:
drop tabel customers;
插入数据:
选择字段插入: insert into customers (name,address,city,age) values('张三','杭州市滨江去','杭州','18');
mysql> select * from customers;
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
+----+--------+--------------------+--------+------+---------+
一次性插入多条数据:这种插入数据的方式比一条一条插入性能要快很多
insert into cur (name,address,city,age) values
('李四','北京市昌平区','北京',23),
('王五','陕西省西安市','西安',34),
('马六','河南省郑州市','郑州',25);
mysql> select * from cur;
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
| 2 | 李四 | 北京市昌平区 | 北京 | 23 | my-data |
| 3 | 王五 | 陕西省西安市 | 西安 | 34 | my-data |
| 4 | 马六 | 河南省郑州市 | 郑州 | 25 | my-data |
+----+--------+--------------------+--------+------+---------+
删除表:
delete删除出再插入:
mysql> delete from coustomers;
insert into customers (name,address,city,age) values('张三','杭州市滨江去','杭州','18');
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 2 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
+----+--------+--------------------+--------+------+---------+
delete再删除出再插入
mysql> delete from coustomers;
insert into customers (name,address,city,age) values('张三','杭州市滨江去','杭州','18');
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 3 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
+----+--------+--------------------+--------+------+---------+
truncate删除表再插入:
mysql> truncate customers;
insert into customers (name,address,city,age) values('张三','杭州市滨江去','杭州','18');
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
+----+--------+--------------------+--------+------+---------+
truncate再删除表再插入:
mysql> truncate customers;
insert into customers (name,address,city,age) values('张三','杭州市滨江去','杭州','18');
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
+----+--------+--------------------+--------+------+---------+
查看插入的id:
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+

delete和truncate的区别:
1.事物上的区别:truncate是不可以回滚的并且删除id(表结构不变数据清空),delete是可以被回滚的并且不删除id
原因:truncate删除的是整个表的数据,是隐式提交的;那么delete是一行一行的删除
2.truncate删除后将会重置id,delete不会重置
3.truncate不能触发任何删除的触发器
查看数据库所有的表:
show tables:
表的重命名:
mysql> rename table customers to cur;
+---------------------------+
| Tables_in_python_ai_basic |
+---------------------------+
| cur |
+---------------------------+
更新表:
mysql> update cur set age= 34 where name='马六';
更新前:
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
| 2 | 李四 | 北京市昌平区 | 北京 | 23 | my-data |
| 3 | 王五 | 陕西省西安市 | 西安 | 34 | my-data |
| 4 | 马六 | 河南省郑州市 | 郑州 | 25 | my-data |
+----+--------+--------------------+--------+------+---------+
更新后:
+----+--------+--------------------+--------+------+---------+
| id | name | address | city | age | love |
+----+--------+--------------------+--------+------+---------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data |
| 2 | 李四 | 北京市昌平区 | 北京 | 23 | my-data |
| 3 | 王五 | 陕西省西安市 | 西安 | 34 | my-data |
| 4 | 马六 | 河南省郑州市 | 郑州 | 34 | my-data |
+----+--------+--------------------+--------+------+---------+
增加列:
mysql> alter table cur add col varchar(30) default 'hhhh';
+----+--------+--------------------+--------+------+---------+------+------+
| id | name | address | city | age | love | col | coll |
+----+--------+--------------------+--------+------+---------+------+------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data | NULL | hhhh |
| 2 | 李四 | 北京市昌平区 | 北京 | 23 | my-data | NULL | hhhh |
| 3 | 王五 | 陕西省西安市 | 西安 | 34 | my-data | NULL | hhhh |
| 4 | 马六 | 河南省郑州市 | 郑州 | 34 | my-data | NULL | hhhh |
+----+--------+--------------------+--------+------+---------+------+------+
删除列:
mysql> alter table cur drop column coll;
+----+--------+--------------------+--------+------+---------+------+
| id | name | address | city | age | love | col |
+----+--------+--------------------+--------+------+---------+------+
| 1 | 张三 | 杭州市滨江去 | 杭州 | 18 | my-data | NULL |
| 2 | 李四 | 北京市昌平区 | 北京 | 23 | my-data | NULL |
| 3 | 王五 | 陕西省西安市 | 西安 | 34 | my-data | NULL |
| 4 | 马六 | 河南省郑州市 | 郑州 | 34 | my-data | NULL |
+----+--------+--------------------+--------+------+---------+------+
创建表时设置外键:
mysql> create table customer_1 (
-> cur_id int not null,
-> name varchar(40) unique,
-> constraint fk_id foreign key (cur_id) references cur (id)
-> );
删除外键:
alter table customer_1 drop foreign key fk_id;

表存在时添加外键:
alter table customer_1 add constraint fk_id foreign key (cur_id()) references cur(id);
fk_id:外键的名称
cur_id: 添加外键的字段
cur:外键关联的表
id:外键关联的字段
外键级联关联:
mysql> alter table customer_1 add constraint fk_id foreign key (cur_id) references cur(id) on update cascade on delete cascade;
设置bin_log:
到vi /etc/mysql/mysql.conf.d/mysqld.cnf中讲log_bin这一项的注释放开去掉前面的"#"
service mysql restart 重启mysql

mysql> show variables like '%log_bin%';

 

存储引擎：MyIsam和InnoDB
1.InnoDB支持事物，安全性高；而MyIsam不支持事物
2.InnoDB数据和索引都存储在一个文件，查询速度慢；而MyIsam数据和索引分开存储，查询速度快
3.InnoDB支持行级锁，并发操作强；MyIsam支持表级所，并发操作弱

MySQL的约束：

1. primary key : 主键约束 不可以为空 不能重复
2. unique: 唯一约束 不能重复 可以为空
3. default： 设置默认
4.  not null: 非空约束
5. foreign key : 外键约束