数据库操作:
- 查看数据库列表:
MariaDB [(none)]> show databases;
- 创建数据库:
MariaDB [(none)]> create database wangdb;
- 删除数据库:
MariaDB [(none)]> drop database wyjdb;
- 创建表:
MariaDB [wangdb]> create table student (id tinyint unsigned primary key,name varchar(20) not null,age tinyint unsigned,sex char(1) default "m" );
MariaDB [wangdb]> CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR (20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));
注:id 和 name 同为主键,构成复合主键
表操作:
- 添加字段
MariaDB [wangdb]> alter table students add phone char(11) after name;
- 删除字段
当被删除的字段有索引,在字段删除后,索引也失效
MariaDB [wangdb]> alter table student drop phone;
建立索引
MariaDB [wangdb]> alter table student add index(age);
- 指定创建索引名(ageindex)
MariaDB [wangdb]> create index ageindex on student(age);
- 查看索引
MariaDB [wangdb]> show indexes from student\G;
- 删除索引
MariaDB [wangdb]> drop index ageindex on student;
DML语句:
- INSERT
MariaDB [wangdb]> insert into student(id,name,age,sex)values(1,'wang',18,'m');
MariaDB [wangdb]> insert into student values(3,'zhao',18,'m');
MariaDB [wangdb]> insert into student values(4,'ma',18,'m'),(5,'ma',20,'f');
MariaDB [wangdb]> create table emp select * from student; 根据原有的student表模板创建新表emp
MariaDB [wangdb]> insert into emp select * from student;
前提条件是emp表已经存在了,并且表结构和select语句执行结果是匹配的。
UPDATE:
MariaDB [wangdb]> update emp set age=21 where name='zhao';
MariaDB [wangdb]> update emp set age=25 sex='m' where id=5 ;
DELETE:
MariaDB [wangdb]> delete from emp where id=4;
清空表:
MariaDB [wangdb]> delete from emp;
MariaDB [wangdb]> truncate table emp;
delete清空表没有truncate 快,因为delete 清空需要记录日志,truncate不需要记录日志。
DQL语句
- DQL:SELECT
- 正序排序
- 倒序排序
- 跳过一个显示两个
- 显示l开头的姓名 %:通配符(任意长度的任意字符 )
- 显示l开头的姓名 _:通配符(任意单个字符; )
- RLIKE:正则表达式模式匹配
用户账户:
- 用户账户:’user’@’host’
user: 用户名
host: 允许用户通过哪些主机远程连接mysqld服务 IP、网络地址、主机名、通配符(%和_) - 创建用户:
CREATE USER ‘username’@’host’ [IDENTIFIED BY ‘password’]; - 查看当前用户:
SELECT user(); - 查看用户:
SELECT User,Host,Password FROM user; 删除用户:DROP USER ‘username’@’host’;
示例:删除默认的空用户
DROP USER ”@’localhost’;更改口令
1)SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
2)UPDATE user SET password=PASSWORD('magedu') WHERE User='root' ;
注意:上面修改表的命令不会马上生效,需执行FLUSH PRIVILEGES生效
3) /usr/local/mysql/bin/mysqladmin -u root –poldpassword password 'newpassword‘