登录MySQL
mysql -u user_name -p password
查看当前所有的数据库
show databases
进入数据库
use databases_name
查看当前数据库中存在的表
show tables
创建数据库
create database database_name;
删除数据库
drop database database_name;
创建表
create table table_name();
显示表的字段结构
desc table_name;
查询
select * from table_name;
更改表结构
A.默认在最后面添加
alter table table_name add column_name data_type;
B.在指定的位置添加
alter table table_name add column_new_name data_type after column_name2;
C.在最前面添加
alter table table_name add column_new_name data_type first;
删除表
drop table_name;
增删改查
insert into table_name value (column_value1,column_value2...);
delete from table_name where xxx(筛选条件);
update table_name set column_name=new_value where xxx(筛选条件);
select * from table_name order by column_name [ASC|DESC]
索引
CREATE UNIQUE INDEX index_name ON table_name(column_name);
ALTER INDEX index_name_old RENNAME TO index_name_new;
DROP INDEX index_name
对两个表的操作
(1)交集
select * from table_name1 INNER JOIN table_name2 on table_name1.column_name1 =table_name2.column_name2
(2)差集
select * from table_name1 LEFT JOIN table_name2 on table_name1.column_name1 =table_name2.column_name2
select * from table_name1 RIGHT JOIN table_name2 on table_name1.column_name1 =table_name2.column_name2
(2)并集
MySQL不能直接支持full join ,但是我们可以用其他方法实现这种功能
select * from table_name1 LEFT JOIN table_name2 on table_name1.column_name1 =table_name2.column_name2
union
select * from table_name1 RIGHT JOIN table_name2 on table_name1.column_name1 =table_name2.column_name2
第一次使用默认密码登陆,需要修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456;'