MariaDB [(none)]>help create database
MariaDB [(none)]> create database if not exists testdb character set utf8mb4 collate utf8mb4_general_ci;
MariaDB [(none)]> show character set;//查看支持的字符集,就是支持哪种语言
MariaDB [(none)]> show collation; //查看排序规则
MariaDB [(none)]> alter database testdb2 character set utf8; //修改数据库的字符集
MariaDB [(none)]> drop database if exists testdb2; //删除数据库
MariaDB [(none)]> show databases; //列出所有数据库
三种创建表的方法,三种插入数据的方法
MariaDB [(none)]> use testdb;
MariaDB [testdb]> create table students
->(id int(11) unsigned primary key auto_increment,
-> name varchar(20) not null,
-> gender enum('m','f') default 'm',
-> mobile char(11) not null
->); //创建表方式一
MariaDB [testdb]> desc students;
MariaDB [testdb]> insert into students(name,gender,mobile) // 插入数据第一种方式
-> values
->('zhangsan','m','13512612338'),
->('lisi','f','13872012688')
->;
MariaDB [testdb]> insert into students set name='wangwu',gender='f',mobile='13613818181'; //插入数据第二种方式
MariaDB [testdb]> create table students1(name varchar(20)not null,tel char(11) not null);
MariaDB [testdb]> insert students1 values('aa','13919219108'),('bb','18229292101');
MariaDB [testdb]> insert students(name,mobile)select name,tel from students1; //插入数据第三种方式将查询的结果作为插入的数据
MariaDB [testdb]> create table student2 like students1; //创建表方式二,只创建了表的结构,没有数据
MariaDB [testdb]> create table stu select * from students where id=1; //创建表方式三
字符集的问题
vim /etc/my.cnf
[mysqld]
character_set_server=utf8mb4
vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
default-character-set=utf8mb4
[root@ansible ~]# mysql --print-defaults
mysql would have been started with the following arguments:
--default-character-set=utf8mb4 --i-am-a-dummy
表的相关命令
MariaDB [(none)]> show engines; 查看支持的engine类型
MariaDB [(none)]> show tables from testdb; 查看 testdb 库有哪些表
MariaDB [(none)]> show columns from testdb.stu; 查看表结构
MariaDB [(none)]> show create table testdb.stu; 查看表创建命令
MariaDB [testdb]> show table status like 'stu'\G; 查看stu表状态
MariaDB [testdb]> show table status from testdb\G; 查看库testdb中所有表的状态
MariaDB [testdb]> delete from stu; 删除stu表的所有记录,记录日志
MariaDB [testdb]> truncate table stu; //删除stu表的所有记录,不生成事务日志
MariaDB [testdb]> drop table stu; //删除表的一切
MariaDB [testdb]> SHOW INDEXES FROM [db_name.]tbl_name; 查看表上的索引
表的防误删除,误修改的功能
[root@ansible ~]# mysql -U //临时生效
vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
i-am-a-dummy 或 safe-updates
修改表示例
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students drop primary key ;
ALTER TABLE students ADD UNIQUE KEY(name);
ALTER TABLE students ADD INDEX(age);
ALTER TABLE students drop primary key ;
DESC students;
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;
ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
Help ALTER TABLE 查看帮助